Capturing SQL statements from a file with the db2cli command

Use the DB2® command db2cli to run or prepare SQL statements that are in a file and capture the statements in a pureQueryXML file.

Before you begin

Before you run the SQL statements with the db2cli command, create the tables that the SQL statements reference.

You must also have pureQuery client optimization enabled for the database driver that connects to the database.

About this task

To control how an application runs SQL statements with pureQuery client optimization, you capture the statements in a pureQueryXML file. The primary method of capturing of SQL statement is by enabling the application with pureQuery client optimization and capturing SQL statements as the application runs. As an alternate method of capturing SQL statements, you can run the db2cli command.

Procedure

To run SQL statements on a DB2 database and capture them in a pureQueryXML file:

  1. Create text file that contains SQL statements.
  2. Run the db2cli command to validate the SQL statements
    Run the command and specify the database connection and SQL file with the -prepareonly option. The following command validates the SQL statements in the file s.sql with the SAMPLE database.
    db2cli execsql –prepareonly –dsn sample –inputsql a.sql

    When you specify the -prepareonly option, the db2cli command prepares the SQL statements without running them.

  3. If necessary, correct any errors that were sent to the output, and run the command again.
  4. Change the pureQuery keywords to support capturing SQL statements.

    pureQuery client optimization captures the SQL statements in a pureQueryXML file when you run the db2cli command with the SQL file. To capture the SQL statements, set the following pureQuery Runtime keywords in the db2cli.ini file.

    captureMode=ON
    pureQueryXml=Sample1Cptr.pdqxml

    If you use a db2dsdriver.cfg file, set the keywords as attributes for the database entry:

    <database name="sample", host="serv1.mynet.com", port="50001">
    		<parameter name="captureMode" value="ON"/>
    		<parameter name="pureQueryXml" value="Sample1Cptr.pdqxml"/>
    	</database>
  5. Run the command db2cli to capture SQL statements.
    Run the command and specify the database connection and SQL file. The following command runs SQL statements in the file s.sql on the SAMPLE database. The statements are captured in the pureQueryXML file Sample1Cptr.pdqxml.
    db2cli execsql –dsn sample –inputsql a.sql

    You can also use the -prepareonly option to capture the SQL statements.

  6. Return the pureQuery keywords to their original values.

What to do next

Use the Java-based pureQuery utilities Configure and StaticBinder to enable CLI applications that are enabled with pureQuery client optimization to run SQL statements statically.


Feedback