IBM InfoSphere Optim pureQuery Runtime for Linux, UNIX, and Windows Version 3.1.1 support for .NET

Product documentation


Abstract

This document describes the enhancements in .NET support for IBM InfoSphere Optim pureQuery Runtime for Linux, UNIX, and Windows Version 3.1.1 with IBM Data Server Driver Package Version 9.7 Fix Pack 6 and later.

Content

InfoSphere Optim pureQuery Runtime pureQuery for Linux, UNIX, and Windows provides enhancements in the following categories:

IBM Data Server Driver Package V 9.7 Fix Pack 6 supports additional pureQuery Runtime keywords when you enable .NET database applications with pureQuery client optimization. The keywords enhance your ability to control the capture of SQL statements and the execution of the statements issued by the application. The keywords can be specified in the connection string of the application or externally in a configuration file.

The IBM Data Server Driver also adds support for accessing data from a pureQueryXML file that is stored in an SQL Management Repository.

pureQuery Runtime keywords


For DB2® .NET applications with pureQuery client optimization enabled, you can specify pureQuery Runtime keywords to enable pureQuery capabilities, such as enabling existing the .NET application to execute SQL statements statically. Executing SQL statements statically avoids preparing SQL statements at run time and can improve application security and performance.

This table lists the supported pureQuery Runtime keywords supported for .NET applications with pureQuery client optimization enabled:

pureQuery Runtime keyword Description
allowDynamicSQL Specifies whether to allow an application to run dynamically any SQL statements that are not captured in a pureQueryXML file or that are captured but not bound.
capturedOnly Specifies whether to run only the SQL statements that are in the pureQueryXML file.
captureMode Specifies whether to capture information about SQL statements.
enableDynamicSQLReplacement Specifies whether to run alternative SQL statements that were added to a pureQueryXML file and both the original statements and the alternative statements were not bound.
executionMode Specifies whether to run statically the SQL statements that have been previously captured and stored by pureQuery Runtime.
maxNonParmSQL Specifies the maximum number of non-parameterized SQL statements to capture. This option applies only when the value of captureMode is ON. When captureMode is OFF, pureQuery ignores this parameter.
propertiesGroupId Specifies a runtime group ID in a repository that is created in a database. pureQuery Runtime uses the runtime group ID and the connection information that specifies the repository to retrieve pureQuery runtime configuration and pureQueryXML file information.
pureQueryXml Specifies the fully-qualified or relative path to and name of a file that is to be created by the process of capturing SQL statements or that already exists as a result of that process.
pureQueryXmlRepository Specifies the repository that contains the pureQueryXML file data that is used with a .NET application enabled with pureQuery client optimization.
repositoryRequired Specifies the pureQuery client optimization behavior if errors occur during retrieving pureQuery data. If the pureQuery runtime properties and pureQueryXML data cannot be retrieved from the location specified by the pureQueryXmlRepository property.
sqlLiteralSubstitution Specifies whether to perform literal substitution. pureQuery Runtime attempts to replace literal values in SQL statements with parameter markers and run the parameterized versions of the statements.
traceFile Specifies the file to write the log into. You can use an absolute path or a path that is relative to the directory in which the pureQuery Log utility runs.
traceLevel Specifies the level of detail to write to the log file.

The pureQuery Keywords supported by .NET applications are the same as the keywords supported by ODBC/CLI applications. For information about pureQuery keywords supported by DB2 ODBC/CLI applications, see pureQuery keywords supported by CLI applications.

Capturing SQL statements and running SQL statements


When you configure a .NET application to use pureQuery client optimization, the .NET client driver can enable pureQuery capabilities, such as enabling existing DB2® .NET applications to execute SQL statements statically.

To configure a .NET application with pureQuery client optimization, you first configure your .NET application to capture SQL statements issued by your application. Then you configure the application to control the execution of SQL statements that are issued by the application.
To configure a .NET application with pureQuery client optimization:

  1. Set the pureQuery keywords in your .NET application to capture SQL statements issued by your application.
    You set the keywords in the connection object of your application or the db2dsdriver.cfg file. The settings in the connection string take precedence over the settings in the db2dsdriver.cfg file.
    1. Set the value of the captureMode keyword to ON.
    2. Set the value of the executionMode keyword to DYNAMIC.
      The default value for executionMode keyword is DYNAMIC. Having the keyword in the file is reminder of the default value.
    3. Set the value of the pureQueryXML keyword to the path and file name of the pureQueryXML file that stores the captured SQL data.
      The pureQueryXML file must have the extension .xml or .pdqxml. The following example db2dsdriver.cfg configuration file specifies the pureQuery Runtime keywords:
      <configuration>
       <dsncollection>
           <dsn alias="alias1" name="name1"
                host="server1.net1.com" port="50001">
             <parameter name="captureMode" value="on"/>
             <parameter name="executionMode" value="dynamic"/>
             <parameter name="pureQueryXml"
                value="testclico.pdqxml"/>
           </dsn>
       </dsncollection>
       <databases>
           <database name="name1" host="server1.net1.com"
                port="50001">
             <parameter name="CommProtocol" value="TCPIP"/>
           </database>
       </databases>
      </configuration>
  2. Run your application to capture SQL statements in the pureQueryXML file.
    If you move the pureQueryXML file for processing, ensure that the pureQueryXML file is not being written to when you retrieve the file. If you retrieve the file while it is being written to, the contents of the file might not be valid or the application might not be able to update the file, which causes an I/O error.
  3. Required to execute the SQL statements statically: Configure the pureQueryXML file, and then use the SQL statements in the file to create packages on the application database and bind the packages to the database.
    You process the pureQueryXML file on a computer that has the pureQuery Runtime installed and is configured to run the Java based pureQuery utilities Configure and StaticBinder.
    1. If necessary, copy the pureQueryXML file to the computer that is configured to run the pureQuery utilities.
    2. Run the pureQuery Configure utility to configure the pureQueryXML file with package name and collection ID.
      At a command prompt, run the Configure utility to add information to the pureQueryXML file such as the package name and collection ID that is used by the pureQuery StaticBinder utility. You can also specify other options to manage the SQL statements in the file.
    3. Run the pureQuery StaticBinder utility with the configured pureQueryXML file to create packages on the target DB2 server and bind the packages.
      At a command prompt, run the StaticBinder utility to bind your file with the database. You run the StaticBinder utility with options that specify the database and the package information.
      If necessary, replace the pureQueryXML file on the computer where the .NET application runs with the processed file.
  4. Update the configuration of your .NET application to control the execution of the SQL statements.
    Locate the db2net.ini configuration file or the db2dsdriver.cfg configuration file and update the keywords and values. The following list describes example configurations:
    • To configure your application to run SQL statements statically, update the following keywords and values:
      • Set the captureMode keyword value to OFF.
      • Set the executionMode keyword value to STATIC.
      • Set the value of the pureQueryXML keyword to the path and file name of the pureQueryXML that contains the captured SQL statements.
      With the keyword settings, your .NET application runs SQL statements statically. The following example db2dsdriver.cfg configuration file specifies the pureQuery Runtime keywords:
      <configuration>
       <dsncollection>
           <dsn alias="alias1" name="name1" host="server1.net1.com"
                 port="50001">
             <parameter name="captureMode" value="off"/>
             <parameter name="executionMode" value="static"/>
             <parameter name="pureQueryXml"
                 value="testclico.pdqxml"/>
           </dsn>
       </dsncollection>
       <databases>
           <database name="name1" host="server1.net1.com"
                 port="50001">
             <parameter name="CommProtocol" value="TCPIP"/>
           </database>
       </databases>
      </configuration>
    • To configure your application to run the application SQL statements dynamically and restrict the application to run only statements in the pureQueryXML file, update the following keywords and values:
      • Set the value of the captureMode keyword to OFF.
      • Set the value of the capturedOnly keyword to TRUE.
      • Set the value of the executionMode keyword to DYNAMIC.
      • Set the value of the pureQueryXml keyword to the path and file name of the pureQueryXML that contains the captured SQL statements.
      With the keyword settings, your .NET application runs SQL statements dynamically. Only the SQL statements in the pureQueryXML file are run. If your application issues an SQL statement that is not in the pureQueryXML file, an error is returned. The following example db2dsdriver.cfg configuration file specifies the pureQuery Runtime keywords:
      <configuration>
       <dsncollection>
           <dsn alias="alias1" name="name1" host="server1.net1.com"
                 port="50001">
             <parameter name="captureMode" value="off"/>
             <parameter name="capturedOnly" value="TRUE"/>
             <parameter name="executionMode" value="dynamic"/>
             <parameter name="pureQueryXml" value="testclico.pdqxml"/>
           </dsn>
       </dsncollection>
       <databases>
           <database name="name1" host="server1.net1.com"
                 port="50001">
             <parameter name="CommProtocol" value="TCPIP"/>
           </database>
       </databases>
      </configuration>
  5. Run or restart the application to use the updated information in the configuration file and pureQueryXML file.

NOTE: pureQuery Runtime checks for a valid pureQuery license if any pureQuery Runtime keywords are specified in a configuration file. If a valid pureQuery license is not found, pureQuery Runtime ends and returns the error code SQL8029N.

SQL Management Repository


You can configure .NET applications with pureQuery client optimization to store and manage data from a pureQueryXML file. For information about the SQL Management repository. see Overview of the SQL Management repository and runtime groups.

The following limitations apply when creating and managing a repository for use by a .NET application with pureQuery client optimization:

  • To create and manage a repository, use the Java based pureQuery ManageRepository utility.
  • A .NET application is enabled with pureQuery client optimization can retrieve only pureQueryXML file data from a repository. Retrieval of pureQuery Runtime configuration information is not supported.
  • A .NET application is enabled with pureQuery client optimization cannot update pureQueryXML data that is stored in a repository.
  • If you specify the captureMode keyword with the value ON to capture SQL data, you cannot specify a repository and runtime group with the keywords pureQueryXmlRepository and propertiesGroupId.
  • The pureQueryXml keyword cannot be specified when the pureQueryXmlRepository and propertiesGroupId keywords are specified.
  • When the value of the repositoryRequired keyword is set to atStartup and pureQuery Runtime cannot retrieve the pureQueryXML file from the repository, pureQuery Runtime throws an exception.

Rate this page:

(0 users)Average rating

Document information


More support for:

InfoSphere Optim pureQuery Runtime for Linux, UNIX and Windows

Software version:

3.1.1

Operating system(s):

AIX, HP-UX, Linux, Linux pSeries, Linux zSeries, Solaris, Windows

Reference #:

7023946

Modified date:

2012-06-01

Translate my page

Machine Translation

Content navigation