As one of the core developer of DB2 Connect CLI team, I got an opportunity to work on supporting generic special registers feature. 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 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.1 Fixpack 2, CLI added new capability to db2dsdriver.cfg by allowing users to set special registers generically.
Before I go deep into the feature explanation, let me begin with answering few basic questions:
What are special registers?
A special register is a storage area that is defined for an application process by the database manager. It is used to store information that can be referenced in SQL statements.
To know more about special registers with examples, refer to the following link:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0008404.html
What is the existing method of setting special registers from client applications?
There are set of special registers which can be set (or updatable) by the client applications. Application can modify such special registers programmatically using “SET” SQL statements. There are few special registers for which DB2 CLI provides connection level keywords. Application can set these keywords either via db2dsdriver.cfg or db2cli.ini configuration files.
Limitations using existing method of setting special registers:
Setting special registers programmatically expects modification of the application source code and recompile each time special register needs added/removed/modified. Also, this needs to be taken care in all impacted application programs.
Using special registers which can be set as CLI keywords can be a better approach than former, but with limited list of such keywords, applications do not get complete solution. CLI can be enhanced to support requested special registers as a keywords, however with data server introducing new special registers at each release, this remains an ongoing solution. This expects users to upgrade their client drivers to be able to get newer special register support as keyword.
What is the newer mechanism CLI provides to address above situation?
To overcome the drawbacks of both the above approaches, it was desired to have a more generic solution to be developed. As a result, CLI has introduced a unique section of special registers viz. <specialregisters> in the configuration file db2dsdriver.cfg. This section allows users to specify a list of special registers that they like to configure. Based on the need, <specialregisters> 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 <specialregisters> section in the following manner:
- read each special register name and its value from <specialregisters> section of a given DSN or a database
- “without scanning/interpreting” form a chain of special registers to be sent to the connected data server.
- upon the first SQL of the connection, flow chained special registers 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 special registers to validate. It will simply flow the entries from <specialregisters> section to the server and let server do necessary validations. Another benefit we can see here is because flow of the special registers is chained together along with 1st SQL statement of the connection, network trips to set the special registers is saved significantly now.
When server upgrade occurs and user application likes to set newly supported special registers, with this new feature of CLI, all user needs to do is to add that special register in their <specialregisters> section! As we can see, no driver upgrade is needed here in order to use newer special registers.
Illustrating usage of <specialregisters>
Having given some background, I can now proceed with the working of this feature. Let’s begin with adding <specialregisters> section to existing / new db2dsdriver.cfg configuration file:.
Sample db2dsdriver.cfg file
<configuration>
<dsncollection>
<dsn alias="sample" name="sample" host="xyz.ibm.com" port="21169"/>
<specialregisters>
<parameter name="CURRENT SCHEMA" value="'MYSCHEMA'"/>
<parameter name="CURRENT DEGREE" value="'ANY'"/>
</specialregisters>
</dsn>
<dsn alias="sample2" name="sample2" host="xyz.torolab.ibm.com" port="21169"/>
</dsn>
</dsncollection>
<databases>
<database name="sample" host="xyz.torolab.ibm.com" port="21169">
</database>
<database name="sample2" host="xyz.torolab.ibm.com" port="21169">
<specialregisters>
<parameter name="CURRENT SCHEMA" value="'MYSCHEMA1'"/>
<parameter name="CURRENT DEGREE" value="'ANY'"/>
</specialregisters>
</database>
</databases>
<parameters>
<specialregisters>
<parameter name="CURRENT DEFAULT TRANSFORM GROUP" value="'MYSTRUCT2'"/>
<parameter name="CURRENT LOCALE LC_MESSAGES" value="'en_CA'"/>
</specialregisters>
</parameters>
</configuration>
Interpreting <specialregisters> section from above sample:
1. Special Registers applicable across all DSNs/databases ( residing under global <parameters> section)
CURRENT DEFAULT TRANSFORM GROUP = 'MYSTRUCT2'
CURRENT LOCALE LC_MESSAGES = 'en_CA'"
2. Special Registers applicable for DSN = sample
CURRENT SCHEMA = 'MYSCHEMA'
CURRENT DEGREE = 'ANY'
CURRENT DEFAULT TRANSFORM GROUP = 'MYSTRUCT2'
CURRENT LOCALE LC_MESSAGES = 'en_CA'"
3. Special Registers applicable for database = sample2
CURRENT SCHEMA = 'MYSCHEMA1'
CURRENT DEGREE = 'ANY'
CURRENT DEFAULT TRANSFORM GROUP = 'MYSTRUCT2'
CURRENT LOCALE LC_MESSAGES = 'en_CA'"
The above configured special registers for relevant DSNs/databases come into effect with the first SQL statement given post connection. It is at this point the special register settings are applied at the server.
Code snippet from application
// connect to a data source
cliRC = SQLDriverConnect(hdbc,
(SQLHWND)NULL,
"dsn=sample",
SQL_NTS,
NULL,
0,
NULL,
SQL_DRIVER_NOPROMPT);
// Execute a SQL statement
cliRC = SQLExecDirect(hstmt, (SQLCHAR *)"INSERT INTO myTab VALUES(10)", SQL_NTS);
…
In the above application logic, "INSERT" is the first SQL statement post connection. Along with this SQL statement, the effective special registers list (as listed in the db2dsdriver.cfg) is formed and these special registers get set at the server. In case any special register setting at server 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.
Where I cannot use this new feature?
To enable client info properties, it’s not recommended to use <specialregisters> section. Existing mechanism either via CLI keywords or environment/connection level attributes can be used instead.
If application logic desires to set special registers during the connection (not at initial phase of the connection), or if they like to change the special registers in between, then setting special registers programmatically is the only way. New feature is useful only as initial value of the special register 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 since most optimal DRDA protocol is used while flowing special registers set information to the server.
Moreover by chaining set of special registers along with 1st SQL of the connection saves another network round trip by using piggyback mechanism.
2. Less maintenance and upgrade of the driver:
The new approach avoids necessity of driver level upgrade just to exploit any new server special register. All users need to do is add the new special register entry in the <specialregisters> section to the existing drivers’s db2dsdriver.cfg file (minimal driver level requirement is V10.1 Fixpack 2). Knowing many big organization having thousands of client drivers installed at each workstation, this saving brings lot of relief to them.
3. Centralized maintenance:
Using central configuration method for db2dsdriver.cfg, users can now have much controlled manner to add/remove/edit the special registers for their applications. Also, with flexibility of using <specialregisters> under DSN, database or global level, user can tune their need quite easily.
References:
Here are some of the links that can be referred to :-
-
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0008404.html
mentions details on Special Registers
-
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.swg.im.dbclient.config.doc/doc/r0061061.html
mentions about IBM data server driver configuration file restrictions
-
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.swg.im.dbclient.config.doc/doc/r0061060.html
mentions about IBM data server driver configuration file example
-
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.swg.im.dbclient.config.doc/doc/r0061059.html
mentions about the IBM data server driver configuration file structure
100
