I’m trying to set a session option in Vertica in a DBExecute component and can’t find a way to get it to work. Specifically, I’m trying to set a session option with the following SQL statement:
SELECT add_vertica_options(‘EE’, ‘ENABLE_JOIN_SPILL’);
so I can do a large CTAS statement that does some monster joins. However, the select returning 1 row seems to cause the DBExecute to fail regardless of how I configure the component even if I want to ignore the result set.
I’ve searched the forum and found several other cases of explanations of selects not working in DBExecute. If that is the case and there’s not way to ignore the result set, then is there another way to manage DB session configuration that depends on using a select to invoke a function that does the configuration. Vertica isn’t the only DB that uses select and function to do session level configuration.
Hi starsailor,
You should use DBInputTable for SELECT statements. However, in order to set the option on session level, you will need to update your DB connection. On the advanced tab of the database connection dialog, there is a threadSafeConnection attribute. By default this attribute is set to true. It means that each component has its own separate connection. If you turn off the threadSafeConnection, a connection instance (and the session) will be shared by all components.
You may also set Vertica options already within the database connection. All you need to do is just add a new Custom JDBC property ConnSettings which you assign the select statement (add_vertica_options) as a value.
vertica_connection.png
More information on Vertica’s JDBC Connection properties, refer to https://my.vertica.com/docs/5.1.6/HTML/ … #13173.htm.
For more information on DB connection settings in Clover, refer to our documentation: http://doc.cloveretl.com/documentation/ … izard.html.
