Binding SQLJ applications to access multiple database servers

After you prepare an SQLJ program to run on one Db2 database server, you might want to port that application to other environments that access different database servers. For example, you might want to move your application from a test environment to a production environment.

Procedure

The general steps for enabling access of an existing SQLJ application to additional database servers are:

Bind packages on each database server that you want to access.
Do not re-customize the serialized profiles. Customization stores a new package timestamp in the serialized profile, which makes the new serialized profile incompatible with the original package.
You can use one of the following methods to bind the additional Db2 packages:
- Run the db2sqljbind command against each of the database servers.
- Run the Db2 BIND PACKAGE command with the COPY option to copy the original packages to each of the additional database servers.
You might need a different qualifier for unqualified Db2 objects on each of the database servers. In that case, you need to specify a value for the QUALIFIER bind option when you bind the new packages. If you use the db2sqljbind command, you specify the QUALIFIER option in the -bindoptions parameter, not in the -qualifier parameter. The -qualifier parameter applies to online checking only.
Specify the package collection for the Db2 packages.
By default, when an SQLJ application runs, the Db2 database server looks for packages using the collection ID that is stored in the serialized profile. If the collection ID for the additional Db2 packages that you create is different from the collection ID in the serialized profile, you need to override the collection ID that is in the serialized profile. You can do that in one of the following ways:
- Specify the collection ID with the pkList DataSource property or the db2.jcc.pkList global property.
- Follow these steps:
  1. Bind a plan for the application that includes the following packages:
    
    The package collection that you bound in the previous step
    
    The IBM® Data Server Driver for JDBC and SQLJ packages
  2. Specify the plan name in the planName DataSource property or the db2.jcc.planName global property.
  Binding a plan might simplify authorization for the application. You can authorize users to execute the plan, rather than authorizing them to execute each of the packages in the plan.

Example

An existing SQLJ application was customized and bound using the following db2sqljcustomize invocation:

db2sqljcustomize -url jdbc:db2://system1.svl.ibm.com:8000/ZOS1
 -user user01 -password mypass 
 -rootPkgName WRKSQLJ 
 -qualifier WRK1  
 -collection MYCOL1
 -bindoptions "CURRENTDATA NO QUALIFIER WRK1 " 
 -staticpositioned YES  WrkTraceTest_SJProfile0.ser

In addition to accessing data at the location that is indicated by URL jdbc:db2://system1.svl.ibm.com:8000/ZOS1, you want to use the application to access data at the location that is indicated by jdbc:db2://system2.svl.ibm.com:8000/ZOS2. On the ZOS2 system, Db2 objects have a qualifier of WRK2, and the packages need to be in collection MYCOL2. You therefore need to bind packages at location ZOS2, change the default qualifier to WRK2, and specify the MYCOL2 collection for the packages. Use one of the following methods to bind the packages:

Run Db2 BIND with COPY to copy each of the packages (one for each isolation level) from the ZOS1 system to the ZOS2 system:

BIND PACKAGE (ZOS2.MYCOL2) OWNER(USER01) QUALIFIER(WRK2) -
 COPY(MYCOL.WRKSQLJ1) CURRENTDATA(NO)
BIND PACKAGE (ZOS2.MYCOL2) OWNER(USER01) QUALIFIER(WRK2) -
 COPY(MYCOL.WRKSQLJ2) CURRENTDATA(NO)
BIND PACKAGE (ZOS2.MYCOL2) OWNER(USER01) QUALIFIER(WRK2) -
 COPY(MYCOL.WRKSQLJ3) CURRENTDATA(NO)
BIND PACKAGE (ZOS2.MYCOL2) OWNER(USER01) QUALIFIER(WRK2) -
 COPY(MYCOL.WRKSQLJ4) CURRENTDATA(NO)

Run the db2sqljbind command to create Db2 packages on ZOS2 from the serialized profile on ZOS1:

db2sqljbind -url jdbc:db2://system2.svl.ibm.com:8000/ZOS2 
 -user user01 	-password mypass 
 -bindoptions "COLLECTION MYCOL2 QUALIFIER WRK2"
	-staticpositioned YES WrkTraceTest_SJProfile0.ser

After you bind the packages, you need to ensure that when the application runs, the Db2 database server at ZOS2 can find the packages. The collection ID in the serialized profile is MYCOL1, so the Db2 database server looks in MYCOL1 for the packages. When you run the application against the ZOS2 system, you need to access packages in MYCOL2.

For applications that use IBM Data Server Driver for JDBC and SQLJ type 2 connectivity, use one of the following methods to tell the database server to look in MYCOL2 as well as MYCOL1:

Specify "MYCOL1.*,MYCOL2.*" in the pkList DataSource property:
```
pkList = MYCOL1.*,MYCOL2.*
```
Bind a plan for the application that includes the packages in MYCOL2 and the IBM Data Server Driver for JDBC and SQLJ packages:
```
BIND PLAN(WRKSQLJ) PKLIST(MYCOL1.*,MYCOL2.*,JDBCCOL.*)
```
Then specify WRKSQLJ in the planName DataSource property:
```
planName = WRKSQLJ
```

For applications that use IBM Data Server Driver for JDBC and SQLJ type 4 connectivity, specify "MYCOL1,MYCOL2" in the currentPackagePath DataSource property.