DB2 Server for VSE & VM: Database Services Utility

Package Procedures

This section describes SQL preprocessing the PACKAGE commands.

Preprocessing

SQL statements in an application program are preprocessed (that is, analyzed and converted) by the system before the program is compiled (or assembled).

DB2 Server for VSE & VM preprocessors do the following:

Generate a modified version of the source code
Convert SQL statements into a package and save the package in the application server
Verify that the current user has authority to access the data and, if so, grant the user the privilege to use the package generated
Update the database catalogs.

The preprocessor action is shown graphically below.

Figure 47. Preprocessing

A package contains code for the SQL statements used in the program. The access path is based on available data statistics and applicable table indexes.

A package is available when its program needs it; moreover, because it is stored in a database, a package is monitored by database manager security mechanisms and change-management facilities.

You must preprocess an application program that switches between application servers on every application server that it accesses. To avoid distributing the program source code and preprocessing it on multiple systems, distribute packages to the connected (local or remote) application servers using the Database Services Utility.

To prevent you from unintentionally running an updated program against an old package, when you preprocess the package, a consistency token is generated and stored in both the program and the package. If the SQL request is to succeed when you run the program, the consistency token, which is based on a timestamp, must match the one in the package.

Each time that you preprocess a program, a consistency token is generated. You can choose to generate a blank consistency token. If you are running the program against multiple application servers, the package for that program, which is stored in all the application servers, must have the same consistency token as the program. If the consistency tokens do not match, the program cannot be run on the application server, or an error may occur. To ensure that the consistency tokens match, preprocess the program once in a DB2 Server for VSE & VM environment and distribute the package to other application servers using the UNLOAD PACKAGE command and the RELOAD PACKAGE command.

The PROGRAM command is a synonym for PACKAGE. The RELOAD or UNLOAD PROGRAM, and RELOAD or UNLOAD PACKAGE are therefore equivalent commands.

The UNLOAD PACKAGE and RELOAD PACKAGE commands are complementary: UNLOAD PACKAGE copies a package to a sequential file and RELOAD PACKAGE reads the package back into an application server.

To ensure that only authorized users manipulate packages in the database, only owners of programs and database administrators are entitled to unload or reload packages.
Keep Interconnected Databases at the Same Level

If you move a package between application servers at different release levels, and a facility of the database manager used by the reloaded package is not available on the new application server, an error occurs. The error occurs when the unloaded package is dynamically preprocessed again during the RELOAD.

Keep Interconnected Databases at the Same Level
If you move a package between application servers at different release levels, and a facility of the database manager used by the reloaded package is not available on the new application server, an error occurs. The error occurs when the unloaded package is dynamically preprocessed again during the RELOAD.

When RELOAD processing is completed, the system updates the TIMESTAMP column of the SYSACCESS catalog table to the date and time of the RELOAD.

Using the UNLOAD PACKAGE Command

The UNLOAD PACKAGE command generates output records that contain:

Preprocessing information
Each SQL statement used in the associated program
Information about its corresponding host variables.

In using the UNLOAD PACKAGE command, you must be either the owner of the program whose package you are unloading or a database administrator. Supply the following information to the Database Services Utility:

Name of the command
Identity of the package to be unloaded
Optionally, the name of the application server containing the package
Identity of the output file.

Note: The UNLOAD PACKAGE command is not supported if you are using DRDA flow.

Unloading a Package

Your system must have database switching capability to access other application servers.

To unload a package for backup or to transfer to another application server, proceed as follows:

In VSE

Provide the following Database Services Utility command:
```
UNLOAD PACKAGE (owner.package-name)
```
where owner is the name of the owner of the associated package, and package-name is the name of the package. If you omit owner, the database manager uses your user ID but still checks to ensure that you have the RUN privilege for the named package.
If the package resides in an application server other than the one you are accessing, leave one space and type:
```
FROM (server-name)
```
where server-name is the name of the other application server.
On the same record, leave one space and type:
```
OUTFILE (ddname)
```
where ddname identifies the sequential output file on tape. Use the same ddname in a TLBL statement. If the output file is on DASD, specify PDEV(DASD) after the ddname and use the same ddname in a DLBL statement. Do not use SYSPCH as the ddname, because the output file content may be invalid and may cause the RELOAD PACKAGE command to fail.
Submit the job for processing.

An example of the UNLOAD PACKAGE command is:

UNLOAD PACKAGE(MARCY.PROG3) OUTFILE(PROGOUT3) FROM(server-name)

where PROG3 is the name of the package, MARCY is the owner, PROGOUT3 is the output data file, and server-name is the name of the other application server.

In VM

Issue the SQLINIT command to initialize the user machine to the application server where the package to be unloaded resides. If you have already done this, proceed to Step 2.
Create a control file to contain the command you construct in the following steps. See Working with a Control File in DB2 Server for VM for detailed information on creating a control file.
Enter the command and the package name. Type:
```
UNLOAD PACKAGE (owner.package-name)
```
where owner is the name of the owner of the associated package, and package-name is the name of the package. If you omit owner, the system uses your user ID but still checks to ensure that you have the RUN privilege for the named package.

If the package resides in an application server other than the one you are logged on to, leave one space and type:

FROM(server-name)

where server-name is the name of the other application server.
Note: The use of FROM always ignores any preceding CONNECT operations and uses the VM user ID as a default. In some situations, the user ID received at the target application server is different from your VM user ID. For example, an entry in the COMDIR might change the user ID, or the target system might change it. If you find this procedure unacceptable, issue the explicit CONNECTs as required, and use the UNLOAD command without the FROM parameter.

On the same line, leave one space and type:
```
OUTFILE(ddname)
```
where ddname identifies the output data file.
Store the control file.
In CMS, specify the necessary FILEDEF statements. When you specify the FILEDEF statement for the output data file, use the same ddname you assigned to the OUTFILE in this procedure. For general information about FILEDEF statements, see Using File Definitions. For command specific information, see Using File Definitions with DB2 Server for VM UNLOAD and RELOAD PACKAGE Commands.
Specify an SQLDBSU EXEC statement; if you did not specify FILEDEFs for the control and message files, use the default values in the SQLDBSU EXEC. For more information on the SQLDBSU EXEC, see Using the SQLDBSU EXEC.

An example of the UNLOAD PACKAGE command is:

UNLOAD PACKAGE(MARCY.PROG3) FROM(PAYROLL) OUTFILE(PROGOUT3)

where PROG3 is the name of the package, MARCY is the owner, the package is in the PAYROLL database, and the output data file is PROGOUT3.

Using the RELOAD PACKAGE Command

|For a VM application requestor, the RELOAD PACKAGE can be used |against a DB2 Server for VM application server or any non-DB2 Server for VM |application server that uses DRDA flow. For a VSE application |requestor, the RELOAD PACKAGE can be used against a DB2 Server for VSE running |on the same VSE system as the Database Services Utility, a VM Database via |Guest Sharing, or any non-DB2 Server for VSE application server that uses DRDA |flow.

The following tables show the different package migration scenarios:

Table 4. Different Reload Package Scenarios in DB2 Server for VM

Unloaded From Application Server Release Unloaded Using DBSU Release Reloading Using DBSU Release Reloading To Result
Application Server Release Using Protocol
2.2 2.2, 3.1 or later 2.2, 3.1 or later 2.2, 3.1 or later SQLDS No Error
3.1 or later 2.2, 3.1 or later 2.2, 3.1 or later 2.2 SQLDS Error
3.1 or later 2.2 2.2 3.1 or later SQLDS No Error
3.1 or later 2.2 3.1 or later 3.1 or later SQLDS Error
3.1 or later 3.1 or later 2.2,3.1 or later 3.1 or later SQLDS No Error
3.1 or later 3.1 or later 3.1 or later 3.1 or later SQLDS No Error
2.2 2.2,3.1 or later 3.3 or later 3.3 or later DRDA Error
2.2 2.2,3.1 or later 3.3 or later Non DB2 for VM DRDA Error
3.1 or later 2.2 3.3 or later 3.3 or later DRDA Error
3.1 or later 2.2 3.3 or later Non DB2 for VM DRDA Error
3.1 or later 3.1 or later 3.3 or later 3.3 or later DRDA No Error
3.1 or later 3.1 or later 3.3 or later Non DB2 for VM DRDA No Error
2.2, 3.1 or later 2.2, 3.1 or later 3.3 or later 2.2, 3.1 or 3.2 DRDA Error

|

Unloaded From Application Server Release	Unloaded Using DBSU Release	Reloading Using DBSU Release	Reloading To	Result
Application Server Release	Using Protocol
2.2	2.2, 3.1 or later	2.2, 3.1 or later	2.2, 3.1 or later	SQLDS	No Error
3.1 or later	2.2, 3.1 or later	2.2, 3.1 or later	2.2	SQLDS	Error
3.1 or later	2.2	2.2	3.1 or later	SQLDS	No Error
3.1 or later	2.2	3.1 or later	3.1 or later	SQLDS	Error
3.1 or later	3.1 or later	2.2,3.1 or later	3.1 or later	SQLDS	No Error
3.1 or later	3.1 or later	3.1 or later	3.1 or later	SQLDS	No Error
2.2	2.2,3.1 or later	3.3 or later	3.3 or later	DRDA	Error
2.2	2.2,3.1 or later	3.3 or later	Non DB2 for VM	DRDA	Error
3.1 or later	2.2	3.3 or later	3.3 or later	DRDA	Error
3.1 or later	2.2	3.3 or later	Non DB2 for VM	DRDA	Error
3.1 or later	3.1 or later	3.3 or later	3.3 or later	DRDA	No Error
3.1 or later	3.1 or later	3.3 or later	Non DB2 for VM	DRDA	No Error
2.2, 3.1 or later	2.2, 3.1 or later	3.3 or later	2.2, 3.1 or 3.2	DRDA	Error

|Table 5. Different Reload Package Scenarios in DB2 Server for VSE

Unloaded From Application Server Release Unloaded Using DBSU Release Reloading Using DBSU Release Reloading To Result
Application Server Release Using Protocol
2.2 2.2, 3.1 or later 2.2, 3.1 or later 2.2, 3.1 or later SQLDS No Error
3.1 or later 2.2, 3.1 or later 2.2, 3.1 or later 2.2 SQLDS Error
3.1 or later 2.2 2.2 3.1 or later SQLDS No Error
3.1 or later 2.2 3.1 or later 3.1 or later SQLDS Error
3.1 or later 3.1 or later 2.2,3.1 or later 3.1 or later SQLDS No Error
3.1 or later 3.1 or later 3.1 or later 3.1 or later SQLDS No Error
2.2 2.2,3.1 or later 7.1 7.1 DRDA Error
2.2 2.2,3.1 or later 7.1 Non DB2 for VSE DRDA Error
3.1 or later 2.2 7.1 7.1 DRDA Error
3.1 or later 2.2 7.1 Non DB2 for VSE DRDA Error
3.1 or later 3.1 or later 7.1 7.1 DRDA No Error
3.1 or later 3.1 or later 7.1 Non DB2 for VSE DRDA No Error
2.2, 3.1 or later 2.2, 3.1 or later 7.1 7.1 DRDA Error

Unloaded From Application Server Release	Unloaded Using DBSU Release	Reloading Using DBSU Release	Reloading To	Result
Application Server Release	Using Protocol
2.2	2.2, 3.1 or later	2.2, 3.1 or later	2.2, 3.1 or later	SQLDS	No Error
3.1 or later	2.2, 3.1 or later	2.2, 3.1 or later	2.2	SQLDS	Error
3.1 or later	2.2	2.2	3.1 or later	SQLDS	No Error
3.1 or later	2.2	3.1 or later	3.1 or later	SQLDS	Error
3.1 or later	3.1 or later	2.2,3.1 or later	3.1 or later	SQLDS	No Error
3.1 or later	3.1 or later	3.1 or later	3.1 or later	SQLDS	No Error
2.2	2.2,3.1 or later	7.1	7.1	DRDA	Error
2.2	2.2,3.1 or later	7.1	Non DB2 for VSE	DRDA	Error
3.1 or later	2.2	7.1	7.1	DRDA	Error
3.1 or later	2.2	7.1	Non DB2 for VSE	DRDA	Error
3.1 or later	3.1 or later	7.1	7.1	DRDA	No Error
3.1 or later	3.1 or later	7.1	Non DB2 for VSE	DRDA	No Error
2.2, 3.1 or later	2.2, 3.1 or later	7.1	7.1	DRDA	Error

Notes:

|You cannot reload a portable package created under SQL/DS Version 2 |Release 2 using the DRDA flow because it does not have the necessary |information required for RELOAD PACKAGE command processing using DRDA |flow.
|Backward migration is also not possible; that is, you cannot |reload DB2 Server for VSE & VM Version 7 Release 1 or later |portable package with SQL/DS Version 2 Release 2.
Modifiable packages created using Extended dynamic statements cannot be reloaded using DRDA flow.
FORTRAN, and any other packages created using Extended dynamic statements that were originally preprocessed using SQLDS protocol, cannot be reloaded using DRDA flow.
FORTRAN, and any other packages created using Extended dynamic statements that were originally preprocessed using DRDA flow, cannot be reloaded using SQLDS protocol.

In using the RELOAD PACKAGE command, you must be either the owner of the program whose package you are trying to reload or a database administrator. DB2 Server for VSE & VM authorization checking grants the owner the RUN privilege after the following information is supplied to the Database Services Utility:

The name of the command
The identity of the package to be reloaded
The replacement method (NEW or REPLACE) to use
- If REPLACE, the disposition of previous package user privileges (KEEP or REVOKE)
Optionally, in VSE only, the identity of additional application servers where the package is to be reloaded
The identity of the input file.

Reloading a Package into an Application Server in Which Its Application Does Not Exist

To reload a package ported from another application server or from backup, proceed as follows:

In VSE

Provide the following Database Services Utility command:
```
RELOAD PACKAGE (ownerpackage-name)
```
where owner is the name of the owner of the associated package, and package-name is the name of the package. If you omit owner, the database manager uses your user ID but still checks to ensure that you have the RUN privilege for the named package.
Specify the replacement method. Because the application associated with the package to be loaded does not exist for the application server (or application servers) being loaded, leave one space, and type:
```
NEW
```
On the same record, enter the names of any additional application servers onto which the package is to be reloaded. Leave one space and put:
```
TO (server-name)
```
where server-name is the name of the other application server. If the package is to be reloaded onto several application servers, leave one space, then type:
```
TO (application server1,application server2,application server3...)
```
On the same record, identify the input file. Leave one space, and type:
```
INFILE (ddname)
```
where ddname identifies the sequential input file on tape. Use the same ddname in a TLBL statement. If the input file is on DASD, specify PDEV(DASD) after the ddname and use the same ddname in a DLBL statement.
Submit the job to run.

An example of the RELOAD PACKAGE command is:

RELOAD PACKAGE(MARCY.PROG3) NEW INFILE(PROGOUT3) TO(server-name)

where PROG3 is the name of the package, MARCY is the owner, and server-name is the name of the other application server. The input data file PROGOUT3 is on tape.

In VM

Issue the SQLINIT command to initialize the user machine to the application server where the package is to be reloaded. If you have already done this, proceed to Step 2.
Create a control file to contain the command you construct in the following steps. See Working with a Control File in DB2 Server for VM for detailed information on creating a control file.
Enter the command and package name. Type:
```
RELOAD PACKAGE (owner.package-name)
```
where owner is the name of the owner of the associated package, and package-name is the name of the package. If you omit owner, the database manager uses your user ID as the owner.
Indicate that you are loading a new package since the application associated with the package does not exist on the application server (or application servers) being loaded, by leaving one space and typing:
```
NEW
```
On the same line, enter the names of any additional application servers to be reloaded. Leave one space; then type:
```
TO(server-name)
```
where server-name is the name of the other application server. If several application servers are to be reloaded, leave one space; then type:
```
TO(application server1,application server2,application server3...)
```
Notes:
1. Your system must have database switching capability to access other application servers.
2. The use of TO means that any preceding CONNECT operations are not used, and TO uses the VM user ID as a default. In some situations, the user ID received at the target database is different from your VM user ID. For example, an entry in the COMDIR may change the user ID, or the target system may change it. If you do not want to use the TO clause procedure, issue the explicit CONNECT command as required, and use the RELOAD command without a TO clause. If the TO clause is not specified, the package is reloaded onto the currently connected application server only.
On the same line, enter the identity of the input file. Leave one space; then type:
```
INFILE(ddname)
```
where ddname identifies the input data file.
Store the control file.
In CMS, specify the necessary FILEDEF statements. When you specify the FILEDEF statement for the input data file, use the same ddname you assigned to the INFILE in this procedure. For general information about FILEDEF statements, see Using File Definitions. For command specific information, see Using File Definitions with DB2 Server for VM UNLOAD and RELOAD PACKAGE Commands.
Specify an SQLDBSU EXEC statement; if you did not specify FILEDEFs for the control and message files, use the default values in the SQLDBSU EXEC. For more information on the SQLDBSU EXEC, see Using the SQLDBSU EXEC.

An example of the RELOAD PACKAGE command is:

RELOAD PACKAGE(MARCY.PROG3) NEW TO(HOLIDAY) INFILE(PROGOUT3)

where PROG3 is the name of the package, and MARCY is the owner. The package is created in the HOLIDAY database and the input data file is PROGOUT3.

Reloading a Package into an Application Server to Update an Existing Application

Your system must have the capability to switch to other application servers.

To reload a package into an application server where a package with the same name already exists, proceed as in Reloading a Package into an Application Server in Which Its Application Does Not Exist, but type:

REPLACE

REPLACE REVOKE

where REPLACE indicates that the existing package is to be replaced by the input package with previous user privileges intact, and REPLACE REVOKE indicates that the existing package is to be replaced by the input package with previous user privileges revoked. An example of the RELOAD PACKAGE command is:

RELOAD PACKAGE(MARCY.PROG3) REPLACE REVOKE TO(PAYROLL) INFILE(PROGOUT3)

where PROG3 is the name of the package, and MARCY is the owner. The package exists in the PAYROLL database; therefore, you use REPLACE to replace it and REVOKE to revoke user privileges on the package. The input data file is PROGOUT3.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]