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:
- Create text file that contains SQL statements.
- 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.
- If necessary, correct any errors that were sent to the
output, and run the command again.
- 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>
- 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.
- 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.