Bind the application's SQL to a database package
In the current release, the pureQuery for .NET technology leverages the
db2cap utility, which was introduced as
part of DB2's CLI/ODBC Static Profiling feature, to bind the captured
SQL to one or more database packages. This utility is included in the
following products:
- IBM DB2 Connect
- IBM Data Server client
- IBM Data Server Runtime Client
- DB2 for Linux, UNIX, and Windows
The db2cap utility accepts the following
program arguments:
db2cap [-h | -?] bind capture-file -d db-alias [-u userid [-p password]] |
capture-file identifies the pureQuery SQL
capture file. db-alias identifies the
target database where the db2cap utility
creates the database package based on the information that is stored
in capture-file. Note that in order to bind
a package to a remote database, that database has to be catalogued
locally.
db2cap uses the
userid and
password credentials to connect to the
target database and perform the bind operation. The bind operation
will fail unless the specified user holds the following privileges:
- Access privileges to any database objects referenced by SQL
statements recorded in the capture file
- Sufficient authority to set bind options such as OWNER and
QUALIFIER if they are different from
userid
- BINDADD authority if the package is being bound for the first
time; otherwise, BIND authority
Bind the captured SQL to a database package
To bind the captured SQL to a database package:
- Open a DB2 command window.
- Invoke the
db2cap utility and specify
the capture file name as well as the target database name.
db2cap bind c:\myapp.xml -d GSDB |
The bind operation should complete successfully.
Figure 8.
db2cap bind utility
output
- While
db2cap processes the capture
file, it updates it. Take a look at what has changed. Open the
file that you've specified as the
pureQueryXml property value. A
consistency token has been added, uniquely identifying the version
of the package. Each SQL has been assigned a section number
that enables the runtime to statically execute the SQL.
Figure 9. Updated pureQuery SQL capture
file
Now that you've created a database package you can verify that
it contains the SQL that you've captured.
Verify the database package
Run the following query against the GSDB database to retrieve a
list of statements that are in the package you've just created.
SELECT PKGSCHEMA,PKGNAME,TEXT
FROM SYSCAT.STATEMENTS
WHERE PKGSCHEMA = 'NULLID' AND PKGNAME LIKE 'TOOT%' |
If you are using the testconn20.exe application, this query should
return two rows:
NULLID TOOT1 DECLARE SQLCURCAPCS0 CURSOR WITH HOLD FOR SELECT *
FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
NULLID TOOT1 CALL SYSIBM.SQLTABLES(:H00002 :H00001,:H00003 :H00001,:H00004
:H00001,:H00005 :H00001,:H00006 :H00001)
|
You've now successfully created the database package that the IBM Data
Server Provider will use to statically execute these statements.
| 
