DB2 Version 9.7 for Linux, UNIX, and Windows

db2sqljcustomize - SQLJ profile customizer

db2sqljcustomize processes an SQLJ profile, which contains embedded SQL statements.

By default, db2sqljcustomize produces four DB2® packages: one for each isolation level. db2sqljcustomize augments the profile with DB2-specific information for use at run time.

Authorization

The privilege set of the process must include one of the following authorities:

DBADM authority
If the package does not exist, the BINDADD privilege, and one of the following privileges:
- CREATEIN privilege
- IMPLICIT_SCHEMA authority on the database if the schema name of the package does not exist
If the package exists:
- ALTERIN privilege on the schema
- 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.

Command syntax


>>-db2sqljcustomize--+--------+--------------------------------->
                     '- -help-'   

>--+--------------------------------------------------------------------------+-->
   +- -url--jdbc:db2://server-+---------+-/database-+-----------------------+-+   
   |                          '-:--port-'           |   .-----------------. | |   
   |                                                |   V                 | | |   
   |                                                '-:---property=value;-+-' |   
   '- -datasource--JNDI-name--------------------------------------------------'   

>--+-----------------+--+----------------------+---------------->
   '- -user--user-ID-'  '- -password--password-'   

   .- -automaticbind--YES-.                                 
>--+----------------------+--+--------------------------+------->
   '- -automaticbind--NO--'  +- -pkgversion--AUTO-------+   
                             '- -pkgversion--version-id-'   

>--+-------------------------------------+---------------------->
   '- -bindoptions--"--options-string--"-'   

>--+--------------------+--+-------------------------------+---->
   '- -storebindoptions-'  '- -collection--collection-name-'   

   .- -onlinecheck--YES-.                                    
>--+--------------------+--+-----------------------------+------>
   '- -onlinecheck--NO--'  '- -qualifier--qualifier-name-'   

>--+----------------------------------+--+---------------+------>
   +- -rootpkgname--package-name-stem-+  '- -longpkgname-'   
   '- -singlepkgname--package-name----'                      

   .- -staticpositioned--NO--.   
>--+-------------------------+---------------------------------->
   '- -staticpositioned--YES-'   

>--+------------------------------------------------------------------------------+-->
   |                         .- -tracelevel--TRACE_SQLJ-------------------------. |   
   '- -tracefile--file-name--+--------------------------------------------------+-'   
                             |               .-,------------------------------. |     
                             |               V                                | |     
                             '- -tracelevel----+-TRACE_NONE-----------------+-+-'     
                                               +-TRACE_CONNECTION_CALLS-----+         
                                               +-TRACE_STATEMENT_CALLS------+         
                                               +-TRACE_RESULT_SET_CALLS-----+         
                                               +-TRACE_DRIVER_CONFIGURATION-+         
                                               +-TRACE_CONNECTS-------------+         
                                               +-TRACE_DRDA_FLOWS-----------+         
                                               +-TRACE_RESULT_SET_META_DATA-+         
                                               +-TRACE_PARAMETER_META_DATA--+         
                                               +-TRACE_DIAGNOSTICS----------+         
                                               +-TRACE_SQLJ-----------------+         
                                               +-TRACE_XA_CALLS-------------+         
                                               +-TRACE_TRACEPOINTS----------+         
                                               '-TRACE_ALL------------------'         

>--+--------------------+--------------------------------------->
   '- -zosDescProcParms-'   

>--+------------------------------------+--+-----------+-------->
   '- -zosProcedurePath--procedure-path-'  '- -genDBRM-'   

>--+---------------------------+-------------------------------->
   '- -DBRMDir--directory-name-'   

   .-----------------------------.   
   V                             |   
>----+-serialized-profile-name-+-+-----------------------------><
     '-file-name.grp-----------'

Command parameters

-help

Specifies that the SQLJ customizer describes each of the options that the customizer supports. If any other options are specified with -help, they are ignored.

-url

Specifies the URL for the data source for which the profile is to be customized. A connection is established to the data source that this URL represents if the -automaticbind or -onlinecheck option is specified as YES or defaults to YES. The variable parts of the -url value are:

server

The domain name or IP address of the z/OS® system on which the DB2 subsystem resides.

port

The TCP/IP server port number that is assigned to the DB2 subsystem. The default is 446.

-url

server

The domain name or IP address of the operating system on which the database server resides.

port

The TCP/IP server port number that is assigned to the database server. The default is 446.

database

A name for the database server for which the profile is to be customized.

If the connection is to a DB2 for z/OS server, database is the DB2 location name that is defined during installation. All characters in this value must be uppercase characters. You can determine the location name by executing the following SQL statement on the server:

SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1;

If the connection is to a DB2 for Linux, UNIX, and Windows server, database is the database name that is defined during installation.

If the connection is to an IBM® Cloudscape server, the database is the fully-qualified name of the file that contains the database. This name must be enclosed in double quotation marks ("). For example:

"c:/databases/testdb"

property=value;

A property for the JDBC connection.

property=value;

A property for the JDBC connection.

-datasource JNDI-name

Specifies the logical name of a DataSource object that was registered with JNDI. The DataSource object represents the data source for which the profile is to be customized. A connection is established to the data source if the -automaticbind or -onlinecheck option is specified as YES or defaults to YES. Specifying -datasource is an alternative to specifying -url. The DataSource object must represent a connection that uses IBM Data Server Driver for JDBC and SQLJ type 4 connectivity.

-user user-ID

Specifies the user ID to be used to connect to the data source for online checking or binding a package. You must specify -user if you specify -url. You must specify -user if you specify -datasource, and the DataSource object that JNDI-name represents does not contain a user ID.

-password password

Specifies the password to be used to connect to the data source for online checking or binding a package. You must specify -password if you specify -url. You must specify -password if you specify -datasource, and the DataSource object that JNDI-name represents does not contain a password.

-automaticbind YES|NO

Specifies whether the customizer binds DB2 packages at the data source that is specified by the -url parameter.

The default is YES.

The number of packages and the isolation levels of those packages are controlled by the -rootpkgname and -singlepkgname options.

Before the bind operation can work, the following conditions need to be met:

TCP/IP and DRDA® must be installed at the target data source.
Valid -url, -username, and -password values must be specified.
The -username value must have authorization to bind a package at the target data source.

-pkgversion AUTO|version-id

Specifies the package version that is to be used when packages are bound at the server for the serialized profile that is being customized. db2sqljcustomize stores the version ID in the serialized profile and in the DB2 package. Run-time version verification is based on the consistency token, not the version name. To automatically generate a version name that is based on the consistency token, specify -pkgversion AUTO.

The default is that there is no version.

-bindoptions options-string

Specifies a list of options, separated by spaces. These options have the same function as 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.

Notes on bind options:

Specify ISOLATION only if you also specify the -singlepkgname option.
The value for STATICREADONLY is YES for servers that support STATICREADONLY, and NO for other servers. When you specify STATICREADONLY YES, DB2 processes ambiguous cursors as if they were read-only cursors. For troubleshooting iterator declaration errors, you need to explicitly specify STATICREADONLY NO, or declare iterators so that they are unambiguous. For example, if you want an iterator to be unambiguously updatable, declare the iterator to implement sqlj.runtime.ForUpdate. If you want an iterator to be read-only, include the FOR READ ONLY clause in SELECT statements that use the iterator.

Important: Specify only those program preparation options that are appropriate for the data source at which you are binding a package. Some values and defaults for the IBM Data Server Driver for JDBC and SQLJ are different from the values and defaults for DB2.

-storebindoptions

Specifies that values for the -bindoptions and -staticpositioned parameters are stored in the serialized profile. If db2sqljbind is invoked without the -bindoptions or -staticpositioned parameter, the values that are stored in the serialized profile are used during the bind operation. When multiple serialized profiles are specified for one invocation of db2sqljcustomize, the parameter values are stored in each serialized profile. The stored values are displayed in the output from the db2sqljprint utility.

-collection collection-name

The qualifier for the packages that db2sqljcustomize binds. db2sqljcustomize stores this value in the customized serialized profile, and it is used when the associated packages are bound. If you do not specify this parameter, db2sqljcustomize uses a collection ID of NULLID.

-onlinecheck YES|NO

Specifies whether online checking of data types in the SQLJ program is to be performed. The -url or -datasource option determines the data source that is to be used for online checking. The default is YES if the -url or -datasource parameter is specified. Otherwise, the default is NO.

-qualifier qualifier-name

Specifies the qualifier that is to be used for unqualified objects in the SQLJ program during online checking. This value is not used as the qualifier when the packages are bound.

-rootpkgname|-singlepkgname

Specifies the names for the packages that are associated with the program. If -automaticbind is NO, these package names are used when db2sqljbind runs. The meanings of the parameters are:

-rootpkgname package-name-stem

Specifies that the customizer creates four packages, one for each of the four DB2 isolation levels. The names for the four packages are:

package-name-stem1: For isolation level UR
package-name-stem2: For isolation level CS
package-name-stem3: For isolation level RS
package-name-stem4: For isolation level RR

If -longpkgname is not specified, package-name-stem must be an alphanumeric string of seven or fewer bytes.

If -longpkgname is specified, package-name-stem must be an alphanumeric string of 127 or fewer bytes.

-singlepkgname package-name

Specifies that the customizer creates one package, with the name package-name. If you specify this option, your program can run at only one isolation level. You specify the isolation level for the package by specifying the ISOLATION option in the -bindoptions options string.

If -longpkgname is not specified, package-name must be an alphanumeric string of eight or fewer bytes.

If -longpkgname is specified, package-name must be an alphanumeric string of 128 or fewer bytes.

Using the -singlepkgname option is not recommended.

Recommendation: If the target data source is DB2 for z/OS, use uppercase characters for the package-name-stem or package-name value. DB2 for z/OS systems that are defined with certain CCSID values cannot tolerate lowercase characters in package names or collection names.

If you do not specify -rootpkgname or -singlepkgname, db2sqljcustomize generates four package names that are based on the serialized profile name. A serialized profile name is of the following form:

program-name_SJProfileIDNumber.ser

The four generated package names are of the following form:

Bytes-from-program-nameIDNumberPkgIsolation

Table 1 shows the parts of a generated package name and the number of bytes for each part.

The maximum length of a package name is maxlen. maxlen is 8 if -longpkgname is not specified. maxlen is 128 if -longpkgname is specified.

Table 1. Parts of a package name that is generated by db2sqljcustomize
Package name part	Number of bytes	Value
Bytes-from-program-name	m=min(Length(program-name), maxlen–1–Length(IDNumber))	First m bytes of program-name, in uppercase
IDNumber	Length(IDNumber)	IDNumber
PkgIsolation	1	1, 2, 3, or 4. This value represents the transaction isolation level for the package. See Table 2.

Table 2 shows the values of the PkgIsolation portion of a package name that is generated by db2sqljcustomize.

Table 2. PkgIsolation values and associated isolation levels
*PkgNumber* value	Isolation level for package
1	Uncommitted read (UR)
2	Cursor stability (CS)
3	Read stability (RS)
4	Repeatable read (RR)

Example: Suppose that a profile name is ThisIsMyProg_SJProfile111.ser. The db2sqljcustomize option -longpkgname is not specified. Therefore, Bytes-from-program-name is the first four bytes of ThisIsMyProg, translated to uppercase, or THIS. IDNumber is 111. The four package names are:

THIS1111
THIS1112
THIS1113
THIS1114

Example: Suppose that a profile name is ThisIsMyProg_SJProfile111.ser. The db2sqljcustomize option -longpkgname is specified. Therefore, Bytes-from-program-name is ThisIsMyProg, translated to uppercase, or THISISMYPROG. IDNumber is 111. The four package names are:

THISISMYPROG1111
THISISMYPROG1112
THISISMYPROG1113
THISISMYPROG1114

Example: Suppose that a profile name is A_SJProfile0.ser. Bytes-from-program-name is A. IDNumber is 0. Therefore, the four package names are:

A01
A02
A03
A04

Letting db2sqljcustomize generate package names is not recommended. If any generated package names are the same as the names of existing packages, db2sqljcustomize overwrites the existing packages. To ensure uniqueness of package names, specify -rootpkgname.

-longpkgname

Specifies that the names of the DB2 packages that db2sqljcustomize generates can be up to 128 bytes. Use this option only if you are binding packages at a server that supports long package names. If you specify -singlepkgname or -rootpkgname, you must also specify -longpkgname under the following conditions:

The argument of -singlepkgname is longer than eight bytes.
The argument of -rootpkgname is longer than seven bytes.

-staticpositioned NO|YES

For iterators that are declared in the same source file as positioned UPDATE statements that use the iterators, specifies whether the positioned UPDATEs are executed as statically bound statements. The default is NO. NO means that the positioned UPDATEs are executed as dynamically prepared statements.

-zosDescProcParms

Specifies that db2sqljcustomize queries the DB2 catalog at the target data source to determine the SQL parameter data types that correspond to the host variables in CALL statements.

-zosDescProcParms applies only to programs that run on DB2 for z/OS data servers.

If -zosDescProcParms is specified, and the authorization ID under which db2sqljcustomize runs does not have read access to the SYSIBM.SYSROUTINES catalog table, db2sqljcustomize returns an error and uses the host variable data types in the CALL statements to determine the SQL data types.

Specification of -zosDescProcParms can lead to more efficient storage usage at run time. If SQL data type information is available, SQLJ has information about the length and precision of INOUT and OUT parameters, so it allocates only the amount of memory that is needed for those parameters. Availability of SQL data type information can have the biggest impact on storage usage for character INOUT parameters, LOB OUT parameters, and decimal OUT parameters.

When -zosDescProcParms is specified, the DB2 data server uses the specified or default value of -zosProcedurePath to resolve unqualified names of stored procedures for which SQL data type information is requested.

If -zosDescProcParms is not specified, db2sqljcustomize uses the host variable data types in the CALL statements to determine the SQL data types. If db2sqljcustomize determines the wrong SQL data type, an SQL error might occur at run time. For example, if the Java™ host variable type is String, and the corresponding stored procedure parameter type is VARCHAR FOR BIT DATA, an SQL run-time error such as -4220 might occur.

-zosProcedurePath procedure-path

Specifies a list of schema names that DB2 for z/OS uses to resolve unqualified stored procedure names during online checking of an SQLJ program.

-zosProcedurePath applies to programs that are to be run on DB2 for z/OS database servers only.

The list is a String value that is a comma-separated list of schema names that is enclosed in double quotation marks. The DB2 database server inserts that list into the SQL path for resolution of unqualified stored procedure names. The SQL path is:

SYSIBM, SYSFUN, SYSPROC, procedure-path, qualifier-name, user-ID

qualifier-name is the value of the -qualifier parameter, and user-ID is the value of the -user parameter.

The DB2 data server tries the schema names in the SQL path from left to right until it finds a match with the name of a stored procedure that exists on that database server. If the DB2 data server finds a match, it obtains the information about the parameters for that stored procedure from the DB2 catalog. If the DB2 data server does not find a match, SQLJ sets the parameter data without any DB2 catalog information.

If -zosProcedurePath is not specified, the DB2 data server uses this SQL path:

SYSIBM, SYSFUN, SYSPROC, qualifier-name, user-ID

If the -qualifier parameter is not specified, the SQL path does not include qualifier-name.

-genDBRM

Specifies that db2sqljcustomize generates database request modules (DBRMs). Those DBRMs can be used to create DB2 for z/OS packages.

-genDBRM applies to programs that are to be run on DB2 for z/OS database servers only.

If -genDBRM and -automaticbind NO are specified, db2sqljcustomize creates the DBRMs but does not bind them into DB2 packages. If -genDBRM and -automaticbind YES are specified, db2sqljcustomize creates the DBRMs and binds them into DB2 packages.

One DBRM is created for each DB2 isolation level. The naming convention for the generated DBRM files is the same as the naming convention for packages. For example, if -rootpkgname SQLJSA0 is specified, and -genDBRM is also specified, the names of the four DBRM files are:

SQLJSA01
SQLJSA02
SQLJSA03
SQLJSA04

-DBRMDir directory-name

When -genDBRM is specified, -DBRMDir specifies the local directory into which db2sqljcustomize puts the generated DBRM files. The default is the current directory.

-DBRMdir applies to programs that are to be run on DB2 for z/OS database servers only.

-tracefile file-name

Enables tracing and identifies the output file for trace information. This option should be specified only under the direction of IBM Software Support.

-tracelevel

If -tracefile is specified, indicates what to trace while db2sqljcustomize runs. The default is TRACE_SQLJ. This option should be specified only under the direction of IBM Software Support.

serialized-profile-name|file-name.grp

Specifies the names of one or more serialized profiles that are to be customized. The specified serialized profile must be in a directory that is named in the CLASSPATH environment variable.

A serialized profile name is of the following form:

program-name_SJProfileIDNumber.ser

You can specify the serialized profile name with or without the .ser extension.

program-name is the name of the SQLJ source program, without the extension .sqlj. n is an integer between 0 and m-1, where m is the number of serialized profiles that the SQLJ translator generated from the SQLJ source program.

You can specify serialized profile names in one of the following ways:

List the names in the db2sqljcustomize command. Multiple serialized profile names must be separated by spaces.
Specify the serialized profile names, one on each line, in a file with the name file-name.grp, and specify file-name.grp in the db2sqljcustomize command.

If you specify more than one serialized profile name, and if you specify or use the default value of -automaticbind YES, db2sqljcustomize binds a single DB2 package from the profiles. When you use db2sqljcustomize to create a single DB2 package from multiple serialized profiles, you must also specify the -rootpkgname or -singlepkgname option.

If you specify more than one serialized profile name, and you specify -automaticbind NO, if you want to bind the serialized profiles into a single DB2 package when you run db2sqljbind, you need to specify the same list of serialized profile names, in the same order, in db2sqljcustomize and db2sqljbind.

If you specify one or more file-name.grp files, and you specify -automaticbind NO, when you run db2sqljbind, you must specify that same list of files, and in the same order in which the files were customized.

You cannot run db2sqljcustomize on individual files, and then group those files when you run db2sqljbind.

Output

When db2sqljcustomize runs, it creates a customized serialized profile. It also creates DB2 packages, if the automaticbind value is YES.

Examples

   db2sqljcustomize -user richler -password mordecai
     -url jdbc:db2:⁄server:50000⁄sample -collection duddy
     -bindoptions "EXPLAIN YES" pgmname_SJProfile0.ser

Usage notes

Online checking is always recommended: It is highly recommended that you use online checking when you customize your serialized profiles. Online checking determines information about the data types and lengths of DB2 host variables, and is especially important for the following items:

Predicates with java.lang.String host variables and CHAR columns
Unlike character variables in other host languages, Java String host variables are not declared with a length attribute. To optimize a query properly that contains character host variables, DB2 needs the length of the host variables. For example, suppose that a query has a predicate in which a String host variable is compared to a CHAR column, and an index is defined on the CHAR column. If DB2 cannot determine the length of the host variable, it might do a table space scan instead of an index scan. Online checking avoids this problem by providing the lengths of the corresponding character columns.
Predicates with java.lang.String host variables and GRAPHIC columns
Without online checking, DB2 might issue a bind error (SQLCODE -134) when it encounters a predicate in which a String host variable is compared to a GRAPHIC column.
Column names in the result table of an SQLJ SELECT statement at a remote server:
Without online checking, the driver cannot determine the column names for the result table of a remote SELECT.

Customizing multiple serialized profiles together: Multiple serialized profiles can be customized together to create a single DB2 package. If you do this, and if you specify -staticpositioned YES, any positioned UPDATE or DELETE statement that references a cursor that is declared earlier in the package executes statically, even if the UPDATE or DELETE statement is in a different source file from the cursor declaration. If you want -staticpositioned YES behavior when your program consists of multiple source files, you need to order the profiles in the db2sqljcustomize command to cause cursor declarations to be ahead of positioned UPDATE or DELETE statements in the package. To do that, list profiles that contain SELECT statements that assign result tables to iterators before profiles that contain the positioned UPDATE or DELETE statements that reference those iterators.

Using a customized serialized profile at one data source that was customized at another data source: You can run db2sqljcustomize to produce a customized serialized profile for an SQLJ program at one data source, and then use that profile at another data source. You do this by running db2sqljbind multiple times on customized serialized profiles that you created by running db2sqljcustomize once. When you run the programs at these data sources, the DB2 objects that the programs access must be identical at every data source. For example, tables at all data sources must have the same encoding schemes and the same columns with the same data types.

Using the -collection parameter: db2sqljcustomize stores the DB2 collection name in each customized serialized profile that it produces. When an SQLJ program is executed, the driver uses the collection name that is stored in the customized serialized profile to search for packages to execute. The name that is stored in the customized serialized profile is determined by the value of the -collection parameter. Only one collection ID can be stored in the serialized profile. However, you can bind the same serialized profile into multiple package collections by specifying the COLLECTION option in the -bindoptions parameter. To execute a package that is in a collection other than the collection that is specified in the serialized profile, include a SET CURRENT PACKAGESET statement in the program.

Using the VERSION parameter: Use the VERSION parameter to bind two or more versions of a package for the same SQLJ program into the same collection. You might do this if you have changed an SQLJ source program, and you want to run the old and new versions of the program.

To maintain two versions of a package, follow these steps:

Change the code in your source program.
Translate the source program to create a new serialized profile. Ensure that you do not overwrite your original serialized profile.
Run db2sqljcustomize to customize the serialized profile and create DB2 packages with the same package names and in the same collection as the original packages. Do this by using the same values for -rootpkgname and -collection when you bind the new packages that you used when you created the original packages. Specify the VERSION option in the -bindoptions parameter to put a version ID in the new customized serialized profile and in the new packages.
It is essential that you specify the VERSION option when you perform this step. If you do not, you overwrite your original packages.

When you run the old version of the program, DB2 loads the old versions of the packages. When you run the new version of the program, DB2 loads the new versions of the packages.

Binding packages and plans on DB2 for z/OS: You can use the db2sqljcustomize -genDBRM parameter to create DBRMs on your local system. You can then transfer those DBRMs to a DB2 for z/OS system, and bind them into packages there. If you plan to use this technique, you need to transfer the DBRM files to the z/OS system as binary files, to a partitioned data set with record format FB and record length 80. When you bind the packages, you need to specify the following bind option values:

ENCODING(EBCDIC): The IBM Data Server Driver for JDBC and SQLJ on DB2 for z/OS requires EBCDIC encoding for your packages.
DYNAMICRULES(BIND): This option ensures consistent authorization rules when SQLJ uses dynamic SQL. SQLJ uses dynamic SQL for positioned UPDATE or DELETE operations that involve multiple SQLJ programs.

EXTENDEDINDICATOR bind option behavior: If the EXTENDEDINDICATOR bind option is not specified in the -bindoptions options string, and the target data server supports extended indicators, bind operations use EXTENDEDINDICATOR(YES). If EXTENDEDINDICATOR(NO) is explicitly specified, and the application contains extended indicator syntax, unexpected behavior can occur because the IBM Data Server Driver for JDBC and SQLJ treats extended indicators as NULL values.