IBM Support

Enabling query acceleration with IBM DB2 Analytics Accelerator for ODBC and JDBC applications without modifying the applications

Product Documentation


Abstract

This document describes how to enable or disable query acceleration for Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC) data sources without having to modify the ODBC or JDBC application.

Content

Background:

IBM DB2 Analytics Accelerator for z/OS provides a DB2 for z/OS DSNZPARM called QUERY_ACCELERATION that determines the default acceleration setting for queries. You can use the CURRENT QUERY ACCELERATION special register to override this DSNZPARM setting for specific sessions or connections. The following options are available for acceleration:
  • NONE (default) - Do not consider any queries for acceleration.
  • ENABLE - Enable queries for acceleration if they meet basic acceleration criteria, and if the DB2 for z/OS optimizer determines that these queries benefit from an acceleration.
  • ENABLE WITH FAILBACK - Same as ENABLE, but if the accelerated queries fail, try to run these on DB2 for z/OS instead (native processing).
  • ELIGIBLE - Enable queries that meet basic acceleration criteria, even if the DB2 for z/OS optimizer determines that these queries do not benefit from an acceleration.
  • ALL - Try to accelerate all queries. Queries that cannot run on the accelerator end abruptly with an error message.

In most cases, setting the QUERY_ACCELERATION DSNZPARM to ENABLE is all that is required to obtain a significant benefit from IBM DB2 Analytics Accelerator. However, in some cases, based on business requirements, you might have to configure different settings for different applications. If you have access to the application source code, or if the application provides an appropriate interface, you can set the special register after opening the database session or connection and before issuing SQL statements. However, it is preferable to control acceleration without having to modify your applications. This document describes an alternative approach that can be used for ODBC or JDBC applications. It allows you to set special registers at the session or connection level without having to modify the application itself.

ODBC application query acceleration:

IBM DB2 Connect is typically used to connect to a DB2 for z/OS database from a distributed platform, such as Windows or Linux. You can also use the driver of the DB2 command-line interface (CLI) that is provided with DB2 Connect to define ODBC data sources for DB2 for z/OS databases. Starting with DB2 Connect Version 10.1 with Fix Pack 2 (PTFs IP23387-IP23396, depending on the platform), such ODBC data sources can be configured for use with any valid DB2 for z/OS special register. However, DB2 Connect Version 10.5 with Fix Pack 4 includes APAR IT04215(<SPECIALREGISTERS> IN DB2DSDRIVER.CFG is not affecting for 1st SQL after connection). Therefore an upgrade to this version is recommended. This document provides an example that defines two ODBC data sources using the DB2 Connect CLI driver. Query acceleration is enabled for one of these data sources, and disabled for the other. The data sources are defined in the db2dsdriver.cfg file of DB2 CLI driver, which is an XML-based configuration file. For the steps in this example, a Windows workstation is used.

ODBC configuration step 1: configuring the DB2 CLI data sources

The first step is to locate the db2dsdriver.cfg file. From the command prompt, enter:
 
  • C:\Users\IBM_ADMIN>db2cli validate
     
If the configuration file has not already been created, the following is output is displayed, which tells you in which directory the configuration file is expected:
 
  • db2dsdriver.cfg Schema Validation :
    Note: The validation operation utility could not find the
    configuration file named db2dsdriver.cfg.
    The file is searched at C:\ProgramData\IBM\DB2\DB2COPY1\cfg\db2dsdriver.cfg

Normally, you find a sample configuration file called db2dsdriver.cfg.sample in the very same directory. Save this file as db2dsdriver.cfg, and run the db2cli validate command again. The output message changes as shown:
 
  • db2dsdriver.cfg Schema Validation :
    Success: The schema validation operation completed successfully.
    The configuration file C:\ProgramData\IBM\DB2\DB2COPY1\cfg\db2dsdriver.cfg is valid

Only one underlying database exists, and all applications are supposed to connect to it. To differentiate between data sources to be enabled for query acceleration, and those not to be enabled, the 'alias' feature of the DB2 CLI driver is used. In the example, the DB2 for z/OS host is 9.10.11.12, and the DB2 subsystem name is MYDB2. The listening port for TCP/IP connections on the DB2 for z/OS host is 9999. A db2dsdriver.cfg configuration file that accommodates all these details looks as follows:
 
  • <configuration>
    • <dsncollection>
      • <dsn alias="noaccel" name="mydb2" host="9.10.11.12" port="9999">
        • <specialregisters>
          • <parameter name="CURRENT QUERY ACCELERATION" value="NONE"/>
          </specialregisters>
        </dsn>
        <dsn alias="eligaccel" name="mydb2" host="9.10.11.12" port="9999">
        • <specialregisters>
          • <parameter name="CURRENT QUERY ACCELERATION" value="ELIGIBLE"/>
          </specialregisters>
        </dsn>
      </dsncollection>
      <databases>
      • <database name="mydb2" host="9.10.11.12" port="9999">
        </database>
      </databases>
    </configuration>

ODBC configuration step 2: validating DB2 CLI connections

Two aliases have been defined to connect to a single DB2 subsystem called mydb2. These aliases are called noaccel and eligaccel. The only difference between the two aliases is the parameter specification in the SPECIALREGISTERS section. The noaccel alias sets CURRENT QUERY ACCELERATION = NONE, whereas the eligaccel alias sets CURRENT QUERY ACCELERATION = ELIGIBLE. If you have common settings that you want to apply to both aliases, you can add lines to the common database-wide section at the bottom of the file, that is, to:

  • '<database name="mydb2"...>'

See your IBM DB2 Connect documentation for a complete description of the db2dsdriver.cfg file. The next step is to verify the validity of the entries in the file by using the db2cli utility:
 
  • C:\Users\IBM_ADMIN>db2cli validate -dsn noaccel -connect -user username -passwd password

  •  
Replace username and password with a valid user name and password for your DB2 subsystem. In the output, look for the following information:
 
  • [ List of SPECIALREGISTERS(unverified) specified for DSN : noaccel ]
    Parameter Value
    -------------------------------------------------------
    CURRENT QUERY ACCELERATION NONE

  •  
The Connection Section shows whether the connection attempt was successful:
 
  • Connection Section :
    -----------------------------------------------------------
    Connecting to: noaccel
    Connect Status: success
    End Connection Section
    -----------------------------------------------------------

  •  
The next step is to validate the eligaccel alias:
 
  • C:\Users\IBM_ADMIN>db2cli validate -dsn eligaccel -connect -user username -passwd password

  •  
Again check the SPECIALREGISTERS setting. It is supposed to look like this:
 
  • [ List of SPECIALREGISTERS(unverified) specified for DSN : eligaccel ]
    Parameter Value
    -------------------------------------------------------
    CURRENT QUERY ACCELERATION ELIGIBLE

  •  
Also verify that the connection attempt was successful:
 
  • Connection Section :
    -----------------------------------------------------------
    Connecting to: eligaccel
    Connect Status: success
    End Connection Section
    -----------------------------------------------------------

ODBC configuration step 3: declaring DB2 CLI ODBC DSNs

To declare the aliases as ODBC data sources on Windows, use the db2cli registerdns command as shown:
 
  • C:\Users\IBM_ADMIN>db2cli registerdsn -add noaccel

  •  
And:
 
  • C:\Users\IBM_ADMIN>db2cli registerdsn -add eligaccel

On UNIX platforms, you must manually update the .odbc.ini file to create the ODBC data sources. This file is usually located in each user's home directory. In the present case, the content of this file would have to look as in the following example:
 
  • [NOACCEL]
    Driver=/u/thisuser/sqllib/lib/libdb2.a
    Description=Data Source with query acceleration disabled
    [ELIGACCEL]
    Driver=/u/thisuser/sqllib/lib/libdb2.a
    Description=Data Source with query acceleration enabled

  •  
See your ODBC driver manager documentation for the exact placement and the syntax that is used in the ODBC data source configuration file.

At this point, both ODBC data sources have been fully configured and are operational. Applications that are not supposed to use query acceleration can use the noaccel ODBC data source, and applications that benefit from query acceleration can use the eligaccel ODBC data source. The applications themselves do not need to be modified – all that they need to connect to the ODBC data source is the data source name (DSN). The DB2 CLI driver automatically sets the CURRENT QUERY ACCELERATION special register to the value that is defined in the db2dsdriver.cfg file. This also allows you to disable or enable query acceleration easily and quickly in the future, either by configuring the application to use a different ODBC DSN, or by updating the db2dsdriver.cfg file of the DB2 CLI driver.

ODBC DSN application samples

1) Here is an example of a C application that uses an ODBC DSN. Whether SQL queries are accelerated is determined entirely by the ODBC DSN definitions:
 
  • snprintf((char *)connectString, sizeof(connectString), "DSN=%s;UID=%s;PWD=%s;", dsn, userName, password);
    SQLDriverConnect(dbc, NULL, connectString, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

2) Here is a similar example of a Perl ODBC application:
 
  • my $dbh = DBI->connect("dbi:ODBC:$dsn", $user, $pw,
    {PrintError => 1, RaiseError => 1});


3) There are other applications that allow you to import data from ODBC data sources, like Microsoft Excel. Using Microsoft Excel, you can
import data from external sources, such as DB2 for z/OS, into your spreadsheet. When you open the Microsoft Query Wizard, a list of configured ODBC DSN names is displayed. You can select a DSN name from this list to establish a connection to the underlying DB2 subsystem and execute a SQL query. If the CURRENT QUERY ACCELERATION special register has been set to ELIGIBLE for this DB2 subsystem in the db2dsdriver.cfg configuration file (eligaccel in the sample configuration above), DB2 for z/OS "considers" an acceleration of the query.

As you can see, the configuration of query acceleration does not affect the applications, as these just use the ODBC DSN names. The data source connections or sessions are connected by the ODBC driver.

JDBC application query acceleration:

Java applications do not use ODBC. The special registers must be set in a different way. The IBM Data Server Driver for JDBC and SQLJ V10.1 Fix Pack 2 (PTFs IP23387-IP23396, depending on the platform) allows you to specify DB2 for z/OS special registers as part of the connection URL.

The first step is to verify that the JDBC/JCC driver meets the minimum requirements, that is, 3.65.77 for JDBC 3.0 support, or 4.15.82 for JDBC 4.0 support. To do that, run the following command:
 
  • C:\Users\IBM_ADMIN>java com.ibm.db2.jcc.DB2Jcc -version

  •  
You see an output message that is similar to the following:
 
  • IBM DB2 JDBC Universal Driver Architecture 3.65.77

  •  
In this case, the output shows that the minimum requirements for the JDBC 3.0 driver are met. If you use the JDBC 4.0 driver, the output should be similar to this:

  • IBM Data Server Driver for JDBC and SQLJ 4.15.82

If your JDBC driver meets these requirements, you can simply specify the special register keyword and value as part of the connection URL.

In the following example, the DB2 for z/OS host is 9.10.11.12, and the DB2 subsystem name is MYDB2. The listening port for TCP/IP connections on the DB2 for z/OS host is 9999. Hence the correct connection URL to disable query acceleration is:
 
  • jdbc:db2://9.10.11.12:9999/MYDB2:user=username;password=password;specialRegisters=CURRENT QUERY ACCELERATION=NONE;

  •  
To enable query acceleration, specify a connection URL similar to the following:
 
  • jdbc:db2://9.10.11.12:9999/MYDB2:user=username;password=password;specialRegisters=CURRENT QUERY ACCELERATION=ELIGIBLE;

  •  
If you want to include archived data in your accelerated queries, you must set a second special register named CURRENT GET_ACCEL_ARCHIVE to the value YES. An example of a connection URL that sets both special registers is:
  • jdbc:db2://9.10.11.12:9999/MYDB2:user=username;password=password;specialRegisters=CURRENT QUERY ACCELERATION=ELIGIBLE,
    CURRENT GET_ACCEL_ARCHIVE=YES;
See your IBM Data Server Driver documentation for a full description of the connection URL syntax.

Since most JDBC-enabled applications allow you to specify a connection URL for a particular data source, you can switch query acceleration on or off by adding the required special register setting to the connection URL. The JDBC driver applies the special register setting when the database connection or session is established.

JDBC application samples

See the following example of a JDBC application that uses a connection URL to connect to a data source:
 
  • // Load the Driver class.
    Class.forName("com.ibm.db2.jcc.DB2Driver");
    // Create connection using the static getConnection method
    // Assumes that 'String connectionURL' has been set to the // URL specified by the user
    java.sql.Connection con = java.sql.DriverManager.getConnection (connectionURL);

Similar to the ODBC examples in C and Perl, the JDBC application is unaware of acceleration-specific settings. It simply uses the connection URL. The connection or session is configured by the JDBC driver.

IBM WebSphere Application Server also uses JDBC to connect to data sources. But instead of specifying the special register as part of the connection URL, you must define a custom property for a JDBC data source. You can use the administrative console of WebSphere Application Server to define custom properties. The custom property is named queryAcceleration and is of the type java.lang.String. It can be set to the same values as the CURRENT QUERY ACCELERATION special register.
The queryAcceleration custom property is supported by IBM WebSphere Application Server V7.0.0.0 and higher.

Conclusion:

ODBC or JDBC applications can be easily configured to enable or disable query acceleration with IBM DB2 Analytics Accelerator, all without having to modify the applications themselves. This is particularly important in connection with third-party software, where the source code is not available. For ODBC applications, the DB2 CLI driver is used to configure the required special registers as part of the data source definitions. The ODBC DSN (data source name) is passed as input to the application. For JDBC applications, the special register settings can be added to the connection URL that is passed to the JDBC application.

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS4LQ8","label":"Db2 Analytics Accelerator for z\/OS"},"ARM Category":[{"code":"a8m0z000000073IAAQ","label":"Commands"}],"ARM Case Number":"","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"5.1.0;7.1.0;7.5"}]

Document Information

Modified date:
24 August 2020

UID

swg27038078