StaticBinder utility, as used with annotated methods

If you are using the annotated-method programming style and a DB2® database, you can use the pureQuery StaticBinder utility to bind the SQL statements in your annotated methods to that data source. You can also use the StaticBinder utility to generate DBRM files.

To understand the conventions that are used in the diagrams, see How to read syntax diagrams.

Overview

Applications that use embedded SQL that is statically bound into DB2 packages have inherent advantages in the areas of performance, reliability, security, monitoring, and administration.

Before running the StaticBinder utility, run the pureQuery Generator utility to generate the implementation classes for the interfaces that contain the SQL statements that you want to bind. When the pureQuery Generator utility is run, all of the information that is necessary to create a static DB2 package is collected and stored in the generated implementation class. This information includes the SQL statements and all applicable data type information from both the Java™ class definitions and any column or parameter metadata from the target database. The information also includes the root package names and, optionally, the collection IDs and versions for the DB2 packages.

The StaticBinder utility reads this information from the implementation classes when it creates and binds DB2 packages. When you run the StaticBinder utility, on the command line, you can specify either the implementation classes to read from or the interfaces that correspond to those classes. If you use an options file, you can specify one or more interfaces; the StaticBinder utility reads the information from the corresponding implementation classes.

By default, the pureQuery StaticBinder utility creates four packages or DBRM files, one for each of the four DB2 isolation levels. The pureQuery StaticBinder utility identifies the isolation level by appending the following numbers to the root names of the packages or DBRM files:
1
For isolation level Uncommitted Read (UR)
2
For isolation level Cursor Stability (CS)
3
For isolation level Read Stability (RS)
4
For isolation level Repeatable Read (RR)

If you use the -forceSingleBindIsolation option when you run the Generator utility, however, the number for the isolation level is not appended to the name of the created package.

If you use the -isolationLevel option when you perform a bind or specify the isolation level in the bind options string, only the package or DBRM file for the isolation level that you specify is created. The name follows the convention that the StaticBinder uses when creating packages or DBRM files for all four isolation levels.

Using options files

An options file lists the interface or interfaces that you want the pureQuery StaticBinder utility to process, and the options that tell the StaticBinder utility how to process the interfaces. You can set default options that apply to all of the interfaces that you list in an options file. You can also set options for interfaces so that you can override the default options and set other options that are specific to individual interfaces.

In the command to run the StaticBinder utility, you can specify the file to use with the -optionsFile option.

For example, a simple options file for the pureQuery StaticBinder utility might look like this:
defaultOptions = -bindOptions "QUALIFIER qual1" -traceFile C:\logs\staticbinder.txt -traceLevel ALL -url jdbc:db2://SRVR01:50000/DB01 -username user01 -password pass01
com.myCompany.MyInterfaceA = -bindOptions "QUALIFIER qual2"
com.myCompany.MyInterfaceB
com.myCompany.MyInterfaceC = -url jdbc:db2://SRVR01:50001/DB02 -username user02 -password pass02
In this example, the line that begins with defaultOptions specifies the qualifier for the DB2 packages and sets the default connection URL. The line also specifies the file in which to log messages. The next line specifies an interface with a qualifier that overrides the default qualifier. The next line specifies an interface for which all of the default options apply. The last line specifies an interface to bind against a different database.
You can also include comments in options files by prefacing each line of a comment with a # symbol. If you need this symbol to appear in the value of one of the options, enclose the value in double-quotation marks, like this:
-pkgVersion "ver#1"
When you run the StaticBinder utility from a command line and use an options file, pureQuery recognizes options in the following order of precedence:
  1. Options on the command line
  2. Options for individual interfaces in the specified options file
  3. Default options in the specified options file

Authorization

The privilege set of the user that invokes the utility must include one of the following authorities:
  • SYSADM authority
  • DBADM authority
  • If the package does not exist, the BINDADD privilege, and one of the following privileges:
    • CREATEIN privilege
    • DB2 for z/OS®: PACKADM authority on the collection or on all collections
    • DB2 Database for Linux®, UNIX®, and Windows®: IMPLICIT_SCHEMA authority on the database if the schema name of the package does not exist
  • If the package exists:
    • DB2 for z/OS: The BIND privilege on the package
    • DB2 Database for Linux, UNIX, and Windows: ALTERIN privilege on the schema and BIND privilege on the package
The user also needs all privileges that are required to compile any static SQL statements in the application. Privileges that are granted to groups are not used for authorization checking of static statements.

DB2 Database for Linux, UNIX, and Windows: If the user has SYSADM authority, but no explicit privileges to complete the bind, the DB2 database manager grants explicit DBADM authority automatically.

Syntax for the command to run the StaticBinder utility if you are binding from an archive

You can use this command to run the StaticBinder utility on an archive that contains your application. The archive must contain the interfaces and implementation classes to supply to the StaticBinder utility. Use an options file with the extension .bindProps that lists the interfaces and implementation classes.

The StaticBinder utility creates a package for each interface or implementation class that you specify. If you did not use the option rootPkgName when you ran the Generator utility, the root name for each package is either of these two root names:
  • If your database does not support long package names: The root name is the first seven characters in the name of the corresponding interface. If two or more interfaces have names that start with the same seven characters, the package that is created for the second of those interfaces overrides the package that is created for the first, the package that is created for the third of those interfaces overrides the package that is created for the second, and so on.
  • If your database supports long package names: The root name is the full name of the corresponding interface.
Read syntax diagramSkip visual syntax diagram
                                        (1)   
>>-java--com.ibm.pdq.tools.StaticBinder------------------------->

    (2)                                                                 
>-------- -url--jdbc--:--db2--:--//--server--+---------+--/--database-->
                                             '-:--port-'                

>-- -username--user-ID-- -password--password-------------------->

>-- -archive-- -filename--+-.ear-+------------------------------>
                          +-.jar-+   
                          +-.war-+   
                          '-.zip-'   

>--+------------------------------------------------------------------------+-->
   |                  (3)                                                   |   
   +-| DBRM options |-------------------------------------------------------+   
   +- -bindOptions-- -"--bind-options--"--+-------------------------------+-+   
   |                                      '- -verifyPackages--+-DETAIL--+-' |   
   |                                                          '-SUMMARY-'   |   
   '- -verifyPackages--+-DETAIL--+------------------------------------------'   
                       '-SUMMARY-'                                              

>--+-----------------------------+--+----------------+---------->
   |                   .-FALSE-. |  |            (4) |   
   '- -differenceOnly--+-TRUE--+-'  '-| -grant |-----'   

>--+--------------------------+--+-----------------------+-----><
   '- -isolationLevel--+-CS-+-'  |                   (5) |   
                       +-RR-+    '-| Trace options |-----'   
                       +-RS-+                                
                       '-UR-'                                

Notes:
  1. You can specify the options in any order.
  2. You do not need to use the -url, -username- and -password options if you are creating DBRM files only.
  3. For the syntax, see the description of these options.
  4. For the syntax, see the description of this option.
  5. For the syntax, see the description of these options.

Syntax for the command to run the StaticBinder utility if you are not using an options file

You can use this command to specify the name of the interfaces or implementation classes and to specify the options for creating a DB2 package or DBRM file.

The StaticBinder utility creates a package for each interface or implementation class that you specify. If you did not use the option rootPkgName when you ran the Generator utility, the root name for each package is either of these two root names:
  • If your database does not support long package names: The root name is the first seven characters in the name of the corresponding interface. If two or more interfaces have names that start with the same seven characters, the package that is created for the second of those interfaces overrides the package that is created for the first, the package that is created for the third of those interfaces overrides the package that is created for the second, and so on.
  • If your database supports long package names: The root name is the full name of the corresponding interface.
Read syntax diagramSkip visual syntax diagram
                                        (1)   
>>-java--com.ibm.pdq.tools.StaticBinder------------------------->

    (2)                                                                 
>-------- -url--jdbc--:--db2--:--//--server--+---------+--/--database-->
                                             '-:--port-'                

>-- -username--user-ID-- -password--password-------------------->

>--+------------------------------------------------------------------------+-->
   |                  (3)                                                   |   
   +-| DBRM options |-------------------------------------------------------+   
   +- -bindOptions-- -"--bind-options--"--+-------------------------------+-+   
   |                                      '- -verifyPackages--+-DETAIL--+-' |   
   |                                                          '-SUMMARY-'   |   
   '- -verifyPackages--+-DETAIL--+------------------------------------------'   
                       '-SUMMARY-'                                              

>--+-----------------------------+--+----------------+---------->
   |                   .-FALSE-. |  |            (4) |   
   '- -differenceOnly--+-TRUE--+-'  '-| -grant |-----'   

>--+--------------------------+--+-----------------------+------>
   '- -isolationLevel--+-CS-+-'  |                   (5) |   
                       +-RR-+    '-| Trace options |-----'   
                       +-RS-+                                
                       '-UR-'                                

                .------------------------.   
                V                        |   
>-- -interface----Java-package.interface-+---------------------><

Notes:
  1. You can specify the options in any order.
  2. You do not need to use the -url, -username- and -password options if you are creating DBRM files only.
  3. For the syntax, see the description of these options.
  4. For the syntax, see the description of this option.
  5. For the syntax, see the description of these options.

Syntax for the command to run the StaticBinder utility if you are using an options file

You can use the command and an options file to specify the names of interfaces and to specify the options for creating DB2 packages or DBRM files that are based on each of those interfaces.

Attention: Although you can specify implementation classes on a command line when you are not using an options file, you cannot specify implementation classes when you are using an options file.
Read syntax diagramSkip visual syntax diagram
                                        (1)   
>>-java--com.ibm.pdq.tools.StaticBinder------------------------->

>--+---------------------------------------------------------------------------------------------------------+-->
   '- -url--jdbc--:--db2--:--//--server--+---------+--/--database-- -username--user-ID-- -password--password-'   
                                         '-:--port-'                                                             

>--+-----------------------------------------------+------------>
   |              .------------------------------. |   
   |              V                              | |   
   '- -interface----Java-package.interface.class-+-'   

>-- -optionsFile--file-name--+-----------------------+---------->
                             |                   (2) |   
                             '-| Trace options |-----'   

>--+-------------------------------+---------------------------><
   '- -verifyPackages--+-DETAIL--+-'   
                       '-SUMMARY-'     

Notes:
  1. You can specify the options in any order.
  2. For the syntax, see the description of these options.

Syntax for specifying default options in an options file for the StaticBinder utility

This syntax diagram shows the default options that you can set for all of the interfaces that you list in an options file.

Read syntax diagramSkip visual syntax diagram
                     (1)   
>>-defaultOptions--=-------------------------------------------->

>--+---------------------------------------------------------------------------------------------------------------+-->
   |  (2)                                                                                                          |   
   '------- -url--jdbc--:--db2--:--//--server--+---------+--/--database-- -username--user-ID-- -password--password-'   
                                               '-:--port-'                                                             

>--+---------------------------------------------+-------------->
   '-+-----------------------------------------+-'   
     '-+- -bindOptions-- -"--bind-options--"-+-'     
       |                  (3)                |       
       '-| DBRM options |--------------------'       

>--+----------------+--+--------------------------+------------->
   |            (4) |  '- -isolationLevel--+-CS-+-'   
   '-| -grant |-----'                      +-RR-+     
                                           +-RS-+     
                                           '-UR-'     

>--+-----------------------+-----------------------------------><
   |                   (5) |   
   '-| Trace options |-----'   

Notes:
  1. You can specify the options in any order.
  2. You do not need to use the -url, -username- and -password options if you are creating DBRM files only.
  3. For the syntax, see the description of these options.
  4. For the syntax, see the description of this option.
  5. For the syntax, see the description of these options.

Syntax for specifying options for individual interfaces in an options file for the StaticBinder utility

This syntax diagram shows the options that you can set for each interface that you list in an options file.

Read syntax diagramSkip visual syntax diagram
                             (1)   
>>-Java-package.interface--=------------------------------------>

>--+---------------------------------------------------------------------------------------------------------------+-->
   |  (2)                                                                                                          |   
   '------- -url--jdbc--:--db2--:--//--server--+---------+--/--database-- -username--user-ID-- -password--password-'   
                                               '-:--port-'                                                             

>--+---------------------------------------------+-------------->
   '-+-----------------------------------------+-'   
     '-+- -bindOptions-- -"--bind-options--"-+-'     
       |                  (3)                |       
       '-| DBRM options |--------------------'       

>--+----------------+--+--------------------------+------------><
   |            (4) |  '- -isolationLevel--+-CS-+-'   
   '-| -grant |-----'                      +-RR-+     
                                           +-RS-+     
                                           '-UR-'     

Notes:
  1. You can specify the options in any order.
  2. You do not need to use the -url, -username- and -password options if you are creating DBRM files only.
  3. For the syntax, see the description of these options.
  4. For the syntax, see the description of this option.

Running the StaticBinder utility from an application

Use the bind() method of the com.ibm.pdq.tools.StaticBinder class to bind your application's SQL statements into DB2 packages at run time. The method returns a value of FALSE if the bind operation fails, and a value of TRUE if the bind operation runs successfully.

The method takes two parameters. The first is a String array for passing arguments to the StaticBinder utility. The second is a PrintWriter object that the StaticBinder utility can print messages and exceptions to.

Here is an example of a call to the bind() method:

StaticBinder binder = new StaticBinder ();
PrintWriter  out = new PrintWriter(
	  new FileWriter("BinderOutput.txt"));
String[] argsArray = {"-user","username","-password","password", 
	  "-url","JDBC-URL","-interface","interface-class"};
Boolean check=binder.bind(argsArray, out);

Descriptions of options

-archive
Use this option if you deployed your application in an archive file to a DB2 server and want to bind SQL statements by running the StaticBinder on that server. Supported types of archive are .ear, .jar, .war, and .zip files.
-bindOptions "string-of-bind-options"
These options have the same function as the DB2 precompile and bind options with the same names. If you are preparing your program to run on a DB2 for z/OS system, specify DB2 for z/OS options. If you are preparing your program to run on a DB2 for Linux, UNIX, and Windows system, specify DB2 for Linux, UNIX, and Windows options.

The syntax of the string is "option_1 value_1 option_2 value_2".

For a list and descriptions of these options, see BIND command.
The REOPT bind option can be useful in pureQuery code. With this option, you can use parameter markers in SQL statements, and still optimize DB2 access paths. See the following links for information about using this option with supported DB2 databases:
DBRM options
Use these options to generate DBRM files, rather than create packages. These options apply only if you are using DB2 for z/OS.

After the StaticBinder utility generates the DBRM files, you must copy the files to a data set. The default DBRM data set name is prefix.DBRMLIB.DATA, where prefix is the high-level qualifier that is specified in the TSO profile for the user. prefix is usually your user ID in TSO.

If the DBRM data set does not already exist, you must create it. The DBRM data set requires space to hold all the SQL statements, with additional space for each host variable name and some header information. The header information requires approximately two records for each DBRM, 20 bytes for each SQL record, and 6 bytes for each host variable. For an exact format of the DBRM, see the DBRM mapping macro, DSNXDBRM in library prefix.SDSNMACS.

The following syntax diagram describes the options for generating DBRM files.

Read syntax diagramSkip visual syntax diagram
>>- -generateDBRM--+-TRUE--+-- -outputDBRMPath--path-----------><
                   '-FALSE-'                           

-generateDBRM
Specifies whether to generate DBRM files, instead of create packages. If generateDBRM is TRUE, then by default the StaticBinder utility generates four DBRM files, one for each isolation level: CS, RR, RS, UR. However, you can generate only one DBRM file if you specify the -isolationLevel option.

The root name of the generated DBRM files is the root package name that you specify when you run the Generator utility. If this name is longer than seven characters, the StaticBinder utility throws an exception.

-outputDBRMPath path
Specifies the directory in which to save the generated DBRM file. The default value is the directory from which you run the StaticBinder utility.
-differenceOnlyTRUE|FALSE
Specifies not to replace DB2 packages that have collection names, package names, and consistency tokens that match these values for the corresponding statement sets within the pureQueryXML file that you run the StaticBinder utility on.

For example, suppose that you run the StaticBinder utility on a pureQueryXML file named capture.pdqxml. The utility creates the packages MYPKGA, MYPKGB, and MYPKGC. You then modify the statement set MYPKGA in capture.pdqxml and run the Configure utility on this file, with the -cleanConfigure option at its default value of FALSE. The Configure utility assigns a new consistency token to the statement set because the set has changed. When you run the StaticBinder utility on capture.pdqxml again to bind the new version of MYPKGA, you specify -differenceOnly TRUE. The utility rebinds only MYPKGA and does not rebind the other two packages.

The default value is FALSE.
-grant "grantees(firstID,secondID,...)"
Specifies the list (in parentheses) of comma-separated grantees to whom you want to grant the EXECUTE privilege on packages that the StaticBinder utility creates. If you do not specify this option, the StaticBinder utility does not grant this privilege.
Read syntax diagramSkip visual syntax diagram
                             .-,--------------------.          
                             V                      |          
>>- -grant-- "--grantees--(----+-authorization-ID-+-+--) - "---><
                               '-PUBLIC-----------'            

grantees
The comma-separated list of authorization IDs to which you want to grant the EXECUTE privilege. Ensure that the authorization IDs that you list are valid for the DB2 database that you are using.

For DB2 Database for Linux, UNIX, and Windows: You can use the USER, GROUP, and ROLE keywords. For information about these keywords, see GRANT (Package Privileges) statement.

For DB2 for z/OS: You can use the ROLE keyword. For information about this keyword, see GRANT (package privileges).

Restrictions:

  • You cannot use the -grant option when the value of the -generateDBRM option is TRUE.
  • In an options file, you can use the -grant option either in the defaultOptions line or for one or more individual entries in the file. You cannot use -grant in both locations in a single options file.
-interface java-package.interface-class
The name of the interface or interfaces for which the pureQuery Generator utility generated an implementation class that contains SQL statements that you want to bind into a DB2 package. The name must include the Java package in which the class is located. The pureQuery StaticBinder utility uses the combination of the name of the interface and the suffix Impl to determine the implementation class to use.
You can use -interface together with -optionsFile if you want the StaticBinder utility to take these two actions:
  • Bind only the SQL statements that are in the specified interface or interfaces
  • Combine options on the command line with options in the options file for the specified interface or interfaces

If the options file does not contain entries for the interfaces, the StaticBinder utility uses the bind options that are on the command line and in the defaultOptions entry in the options file.

-isolationLevel CS|RR|RS|UR
Specifies that you want to generate a single DB2 package or DBRM file that is for a particular isolation level, rather than one DB2 package or DBRM file for each of the four isolation levels.

The isolation level applies to all of the SQL statements that are in the package. If you set an isolation level through the Connection.setTransactionIsolation() method of the IBM® Data Server Driver for JDBC and SQLJ, pureQuery ignores that isolation level for statements executed statically.

UR
Specifies Uncommitted Read as the isolation level.
The StaticBinder utility adds 1 to the name of the created package or DBRM file, unless you used the -forceSingleIsolationLevel option when you ran the Generator utility.
CS
Specifies Cursor Stability as the isolation level.
The StaticBinder utility adds 2 to the name of the created package or DBRM file, unless you used the -forceSingleIsolationLevel option when you ran the Generator utility.
RS
Specifies Read Stability as the isolation level. Read Stability ensures that the execution of SQL statements in the package is isolated from other application processes for rows that are read and changed by the application.
The StaticBinder utility adds 3 to the name of the created package or DBRM file, unless you used the -forceSingleIsolationLevel option when you ran the Generator utility.
RR
Specifies Repeatable Read as the isolation level.
The StaticBinder utility adds 4 to the name of the created package or DBRM file, unless you used the -forceSingleIsolationLevel option when you ran the Generator utility.
Java-package.interface (at the beginning of a line in an options file)
Specifies that the options on the line apply only to the SQL statements in the specified interface. These options override options that you specify as defaults.
You must qualify the name of the interface with the name of the Java package to which the interface belongs.
-optionsFile file-name
The name of the file, including its absolute or relative path, that lists the interfaces that contain the SQL statements that you want to bind.
-password password
The password to use to connect to the data source.
Trace options
You can specify the file to log messages in and the level of information to log.
Read syntax diagramSkip visual syntax diagram
>>-+------------------------+--+---------------------------+---><
   '- -traceFile--file-name-'  |               .-OFF-----. |   
                               '- -traceLevel--+-ALL-----+-'   
                                               +-SEVERE--+     
                                               +-WARNING-+     
                                               +-INFO----+     
                                               +-CONFIG--+     
                                               +-FINE----+     
                                               +-FINER---+     
                                               '-FINEST--'     

-traceFile file-name
Specifies the absolute or relative path and name of the file to use for logging information about the operation.
If the file already exists, pureQuery appends new messages to the existing content of the file. As the default, the entries are written to System.err.
-traceLevel OFF|SEVERE|WARNING|INFO|CONFIG|FINE|FINER|FINEST|ALL
Specifies the type of information to log. The default level is OFF. If you do not specify a file in which to write the log entries and you set this option to any value other than OFF, the entries are written to System.err.
-url connection-URL
The Type 4 JDBC URL for connecting to the database.
-username user ID
The user ID to use to connect to the data source.
-verifyPackages DETAIL|SUMMARY
Specifies whether the StaticBinder utility generates a report of the packages that exist and do not exist for the SQL statements that are associated with the interface or implementation class. When you use this option, the StaticBinder utility does not bind packages.

For example, suppose that you ran the Generator utility on an interface named OrdersData that declares methods for querying and updating the table ORDERS. The Generator utility creates the implementation class OrdersDataImpl.

When you ran the utility, you supplied values for the -collection, -pkgVersion, and -rootPkgName options, and the utility stored these values in the implementation class. You run the StaticBinder utility, specifying the name of this class, and the utility creates DB2 packages.

At a later time, you want to see a list of the packages that the StaticBinder utility created from the implementation class. When you run the utility, you can use the -verifyPackages option, specifying the value DETAIL, and again supply the name of the class.

If the values for the -collection, -pkgVersion, and -rootPkgName options are the same as when you ran the StaticBinder utility the previous time, the utility finds the packages and lists them.

However, if you ran the Generator utility on the OrdersData interface after you first ran the StaticBinder utility and you changed any of the values for -collection, -pkgName, and -rootPkgName, the StaticBinder utility would not find any packages that matched the new values of these options. In its report, the StaticBinder utility would say that the packages that you were looking for do not exist.

The -verifyPackages option works on the premise that, after you generated the implementation class and ran the StaticBinder utility on that class or its associated interface, you did not run the Generator utility again on the interface and supply different values for -collection, -pkgVersion, and -rootPkgName.

You can specify this option together with the -bindOptions option. However, the StaticBinder utility will not bind packages. Use -bindOptions only to specify the collection for the packages that you want to verify if you used this option to specify the collection when you created the packages.

DETAIL
Produces a report that explains the following information, which is based on the values of -collection, -pkgVersion, and -rootPkgName:
  • Which packages do not exist.
  • Which packages do exist. For each package, the report lists the name, consistency token, timestamp, and the isolation level.
  • The number of packages that exist or do not exist.
SUMMARY
Produces a report that enumerates the packages that exist and do not exist, given the values of the values of -collection, -pkgVersion, and -rootPkgName.

Feedback