Search code examples
sqlrnetezzarodbc

Using Netezza tuning parameters with RODBC

I am trying to pass some tuning parameters to my Netezza database in my SQL code but RODBC and ODBC packages does not like it.

I have tried running this query directly in Netezza and it works fine but when it's encapsulated using sqlQuery it returns no data.

My query looks as follows:

system.time(test <- as.data.table(sqlQuery(netezza_prod_conn, 
                                                  "set JIT_SAMPLE_GROWTH_POWER = 0.54;
                                                  set PREBROADCAST_LIMIT = 30;
                                                  SELECT
                                                  NOW()")))

I believe it's related to the semi colons and when it finishes the first query which returns nothing it never moves on to the next piece.

Is there a workaround to include multiple SQL statements with sqlQuery?

Solution

  • It seems to me that this is more of a ‘rodbc’ package specific question than the other 3 categories, but on all the places that I have googled the ‘solution’ to your question is to split the string on ‘;’ and then execute each as a separate sqlQuery

    I can confirm that as long at you keep the connection open against Netezza it will not ‘forget’ your tuning settings once it executes the last statement.