As one of the core developer of DB2 Connect CLI team, I got an opportunity to work on supporting session global variables feature through IBM Data Server Driver configuration file. Idea behind this blog is to spread some of the benefits and usage to help application development community understand it better to leverage the same.
Though focus of this blog would remain CLI centric, similar concept exists in other client drivers like IBM DB2 .NET provider and IBM DB2 JDBC driver (aka JCC).
IBM Data Server Driver configuration file (by default named as db2dsdriver.cfg) is catching its popularity among the customers due to its capability of allowing different DSNs and database properties configuration in a central repository manner. In addition, being in XML format, it takes a less of an effort for any user to get used to such configuration files. In DB2 Connect V10.5, CLI added new capability to db2dsdriver.cfg by allowing users to set session global variables
Before I go deep into the feature explanation, let me begin with answering few basic questions:
What are session global variables?
A global variable is a named memory variable that is retrieved or modified through SQL statements.
Global variables enable applications to share relational data among SQL statements, without the need for additional application logic to support this data transfer.
The value of a session global variable is uniquely associated with each session that uses this particular global variable
What is the existing method of setting session global variables from client applications?
Once the global variables are created using the CREATE VARIABLE, their values can be set/modified by using the SQL SET statement.
Limitations using existing method of setting session global variables:
Setting session global variables programmatically expects modification of the application source code and recompile each time global variable needs to be added/removed. Also, this needs to be taken care in all impacted application programs.
What is the newer mechanism CLI provides to address above situation?
To overcome the drawbacks stated above, CLI has introduced a unique section of session global variables viz. <sessionglobalvariables> in the configuration file db2dsdriver.cfg. Once the global variables have been already created using CREATE VARIABLE and these variables exist at the server, the section <sessionglobalvariables> allows users to specify a list of global variables that they like to configure. Based on the need, <sessionglobalvariables> section can be added at a DSN level or a database level or even globally.
During each connection to a given DSN or a database, CLI reads through db2dsdriver.cfg and processes <sessionglobalvariables> section in the following manner:
- read each global variable name and its value from <sessionglobalvariables> section of a given DSN or a database
- “without scanning/interpreting” form a chain of session global variables to be sent to the connected data server.
- upon the first SQL of the connection, flow global variables to the server
- server will process each special registers of the chain (along with the 1st SQL of the connection) and set it appropriately at the server.
As we can see from the above logical flow, with this feature, CLI has no dependency to know the global variables to validate. It will simply flow the entries from <sessionglobalvariables> section to the server and let server do necessary validations. Another benefit we can see here is because flow of the sessionglobalvariables is chained together along with 1st SQL statement of the connection, network trips to set the global variables is saved significantly now.
Illustrating usage of <sessionglobalvariables >
Having given some background, I can now proceed with the working of this feature. Let’s begin with adding <sessionglobalvariables> section to existing new db2dsdriver.cfg configuration file:.
Sample db2dsdriver.cfg file
<dsn alias="sample" name="sample" host="xyz.torolab.ibm.com" port="21169"/>
<parameter name="GV_MYJOB " value="Engineer"/>
<parameter name="GV_MYDATE" value="2012-12-12"/>
<parameter name="GV_EMPNO" value="07089"/>
<dsn alias="sample2" name="sample2" host="xyz.torolab.ibm.com" port="21169"/>
<database name="sample" host="xyz.torolab.ibm.com" port="21169">
<parameter name="GV_MYJOB " value="Doctor"/>
<database name="sample2" host="xyz.torolab.ibm.com" port="21169">
<parameter name="GV_MYJOB " value="Scientist"/>
<parameter name="GV_MYDATE" value="CURRENT DATE"/>
Interpreting <sessionglobalvariables> section from above sample:
1. Session Global Variables applicable across all DSNs/databases ( residing under global <parameters> section)
GV_MYDATE = current date value in CURRENT DATE special register
2. Session Global Variables applicable for DSN = sample
GV_ENPNO = 07089
GV_MYDATE = 2012-12-12
3. Session Global Variables applicable for database = sample2
GV_MYDATE = current date value in CURRENT DATE special register
The above configured session global variables for relevant DSNs/databases come into effect with the first SQL statement given post connection. It is at this point the global variables values are set at the server
Code snippet from application
// connect to a data source
cliRC = SQLDriverConnect(hdbc,
// Execute a SQL statement
cliRC = SQLExecDirect(hstmt, (SQLCHAR *)"CREATE TABLE employee (
hiredate DATE))", SQL_NTS);
In the above application logic, "CREATE TABLE" is the first SQL statement post connection. Along with this SQL statement, the effective session global variables list (as listed in the db2dsdriver.cfg) is formed and these global variables get set at the server. In case any global variable setting has resulted in any warning or an error, those will be chained to the result of 1st SQL’s response. Application can call SQLGetDiagRec() API to retrieve any warning or error details to diagnose the problem.
// Execute SQL statement
cliRC = SQLExecDirect(hstmt, (SQLCHAR *)"INSERT INTO employee VALUES(GV_EMPNO, 'John', GV_MYJOB, “2012-10-31”)", SQL_NTS);
Assuming we are connected to sample dsn, the following values gets inserted in the table
Where I cannot use this new feature?
If application logic desires to change the values of session global variables in between, then setting session global variables programmatically is the only way. New feature is useful only as initial value of the global variable for the connection.
In summary, as an application user, one can get below benefits with the new feature:
1. Savings in time and network utilization by reduction in network flows
Reduction in network round trips between client and servers
Moreover by chaining set of global variables along with 1st SQL of the connection saves another network round trip by using piggyback mechanism.
2. Centralized maintenance:
Using central configuration method for db2dsdriver.cfg, users can now have much controlled manner to add/remove/edit the session global variables for their applications. Also, with flexibility of using <sessionglobalvariables> under DSN, database or global level, user can tune their need quite easily.
Here are some of the links that can be referred to :-
mentions details on global variables.
mentions about IBM data server driver configuration file example
mentions about the IBM data server driver configuration file structure
mentions about IBM data server driver configuration file restrictions