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

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

• Support for pureQuery Runtime keywords
• Capturing SQL statements and running SQL statements
• Support for SQL Management Repository

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.

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:

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.

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.

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.

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.