Please advice me that how to set up the query band when Connection Pool is done and transaction cannot explicitly BeginTransaction, EndTransaction .
Wish to set it for each SQL,how can we do the process?
Thanks & Best regards
I think you have two options:
Use the 'for transaction' and run each SQL as a multi-statement-request (MSR):
set query_band = 'qbname=qbvalue;'; for transaction;your-sql-here;
Use the 'for session' option:
set query_band = 'qbname=qbvalue;' for session; your-sql-here;
This second option will be fine providing no other session uses that connection between the 'set query_band' and 'your sql'. - but I don't think it can.
- NOTE: if you're on TD14.10 or higher then you can use the 'VOLATILE' option which should help make this run a little bit more efficiently.
There is another option if you want to truly automate it.
This can be accomplished by using startup parameters.
And yes, this will be either a call to a stored procedure or a macro.
Replace procedure UpdQryBand ()
DECLARE UpdQryBandStr VARCHAR(200);
SET UpdQryBandStr = 'SET QUERY_BAND = ' || '''' || 'WebSessionPool=' || session || '-' || user || ';' || '''' || ' FOR SESSION;';
CALL DBC.SysExecSQL(UpdQryBandStr );
Then modify the user in question's startup parameter as
MODIFY USER <someWebUser> AS STARTUP = 'call UpdQryBand ();';
Bear in mind, there will be additional call which means there will be additional CPU and response time penalties.
You will have to account for any TASM throttles that may impact this.
The user in question should have execute procedure on this newly created macro.
You can possibly pass some parameters to this procedure to make it very personal.
Personally, while this feature is good but it comes at cost as it is a SQL that DBS has to process.
I haven't tested this code but I had used something like this in past when Dinosaurs weren't extict.