How to specify DB2 optimization level for SQL transform?

dataservices job design
1

I have a BODS 4.2 job that uses an SQL Transform to execute a query against a DB2 datastore. Our DBA has told me that for mysterious DB2 reasons the query would run much faster if I set the DB2 optimization level to a different value for this particular query. He gave me this SQL statement to run before the select statement, to change the optimization level just for the scope of the current session:
set current query optimization = 3;

That works great in RapidSQL where I simply run this statement in front of the select statement, but I haven’t figured out how I can do it in BODS.
It doesn’t work to put that statement at the start of the “SQL text” in the SQL transform, because that only supports a single statement, which must be SELECT.
I can easily execute that set statement in a script using the SQL function, but the problem is that I don’t see any way to run that script and the SQL transform in the same DB2 session. I think BODS establishes a new connection for each step.
I see the BODS datastore has options called “Additional Connection Parameters” and “Additional Session Parameters”. Is there any way those can be used to specify query optimization level?

voldal (BOB member since 2013-05-13)

2

I was able to figure out an approach that accomplishes what I needed by experimenting with the Datastore that the SQL Transform uses. The datastore has an Advanced property called “Additional session parameters”. It’s behavior is not documented, but based on the behresults I got it seems that it is not for parameters, but rather for command(s) you want to have run within the session after the database connection is established. So I set that property to this DB2 command, and got the optimization results I needed:
set current query optimization = 3;

voldal (BOB member since 2013-05-13)