DBInputTable component setting DB session to READ_ONLY

CloverDX Platform
1

How can I control the DBInputTable component to not set the DB session to READ_ONLY?

I’m using the Vertica 6.1 JDBC driver to connect to a Vertica 6.1 instance.

2

More detail…

I’m trying to get a CloverETL graph to support gathering optimizer statistics in Vertica 6.1. Vertica does this by calling their meta-function analyze_statistics(‘<table_name>’), but the only mechanism Vertica supports to call a function of any kind, meta-functions included, is in a select statement. Specifically in this case the SQL statement is:

select analyze_statistics(‘<table_name>’);

Apparently, this select statement results in a DBInputTable error because that component sets the session to read-only mode.

Also this statements results in a DBExecute error in that component because it returns a result set in addition to SQL result code.

These two component behaviors leave no way in CloverETL to gather optimizer statistics in a graph.

Any help getting this to run in a graph without error would be greatly appreciated.

3

Hi starsailor,

thank you for the report. An issue has been created in our bugtracker:
https://bug.javlin.eu/browse/CLO-3287

As a workaround, please create a copy of the connection and set the JDBC specific attribute to “Generic ODBC” or “Hive”. These JDBC specifics do not set the read-only flag on the connection.

Note that you should not use this new connection for any other purpose but meta-function execution, because other functionality may not work due to incorrect JDBC specific.

4

Hi Milan,

Thank you for the workaround. If I understand what you are saying correctly, you are basically instructing me to configure a different connection to the same Vertica instance setting “the JDBC specific attribute to “Generic ODBC” or “Hive”” and then use that connection to only execute the select statements that gather statistics that require a read/write session on that new connection. Then, I should execute all the remaining database statements on the original connection.

If my understanding is correct, that should work out just fine because the Vertica statistics gathering using the statement:

select analyze_statistics(‘<table_name>’);

will gather the table statistics and commit the changes to the database catalog. Any other sessions should see the result of that commit to the catalog and the Vertica optimizer should then be able to construct query plans considering the newly collected statistics.

I’ll comments on the result here once I test it.