Package schemas provide
a method for logically grouping packages. Different approaches exist
for grouping packages into schemas.
Some implementations use one schema per environment (for example,
a production and a test schema). Other implementations use one schema
per business area (for example, stocktrd and onlinebnk schemas),
or one schema per application (for example, stocktrdAddUser and onlinebnkAddUser).
You can also group packages for general administration purposes, or
to provide variations in the packages (for example, maintaining backup
variations of applications, or testing new variations of applications).
When multiple schemas are used for packages, the database manager must determine in which schema
to look for a package. To accomplish this task, the database manager uses the value of the CURRENT
PACKAGESET special register. You can set this special register to a single schema name to indicate
that any package to be invoked belongs to that schema. If an application uses packages in different
schemas, a SET CURRENT PACKAGESET statement might have to be issued before each package is invoked
if the schema for the package is different from that of the previous package.
Note: Only Db2® for z/OS® Version 9.1 has a
CURRENT PACKAGESET special register, which allows you to explicitly set the value (a single schema
name) with the corresponding SET CURRENT PACKAGESET statement. Although Db2 has a SET CURRENT
PACKAGESET statement, it does not have a CURRENT PACKAGESET special register. This means that
CURRENT PACKAGESET cannot be referenced in other contexts (such as in a SELECT statement) with
Db2.
Db2 for IBM® i does
not provide support for CURRENT PACKAGESET.
The
Db2
database server has more flexibility when it can consider a list of schemas during package
resolution. The list of schemas is similar to the SQL path that is provided by the CURRENT PATH
special register. The schema list is used for user-defined functions, procedures, methods, and
distinct types.
Note: The SQL path is a list of schema names that Db2 should consider when
trying to determine the schema for an unqualified function, procedure, method, or distinct type
name.
If you need to associate multiple variations of a package (that
is, multiple sets of BIND options for a package) with a single compiled
program, consider isolating the path of schemas that are used for
SQL objects from the path of schemas that are used for packages.
The CURRENT PACKAGE PATH special register allows you to specify a list of package schemas. Other
Db2 family
products provide similar capability with special registers such as CURRENT PATH and CURRENT
PACKAGESET, which are pushed and popped for nested procedures and user-defined functions without
corrupting the runtime environment of the invoking application. The CURRENT PACKAGE PATH special
register provides this capability for package schema resolution.
Many installations use more than one schema for packages. If you
do not specify a list of package schemas, you must issue the SET CURRENT
PACKAGESET statement (which can contain at most one schema name) each
time you require a package from a different schema. If, however, you
issue a SET CURRENT PACKAGE PATH statement at the beginning of the
application to specify a list of schema names, you do not need to
issue a SET CURRENT PACKAGESET statement each time a package in a
different schema is needed.
For example, assume that the following packages exist, and, using the following list, that you
want to invoke the first one that exists on the server: SCHEMA1.PKG1, SCHEMA2.PKG2, SCHEMA3.PKG3,
SCHEMA.PKG, and SCHEMA5.PKG5. Assuming the current support for a SET CURRENT PACKAGESET statement in
Db2 (that
is, accepting a single schema name), a SET CURRENT PACKAGESET statement have to be issued before
trying to invoke each package to specify the specific schema. For this example, five SET CURRENT
PACKAGESET statements need to be issued. However, using the CURRENT PACKAGE PATH special register, a
single SET statement is sufficient. For example:
SET CURRENT PACKAGE PATH = SCHEMA1, SCHEMA2, SCHEMA3, SCHEMA, SCHEMA5;
Note: In Db2, you can set the CURRENT PACKAGE PATH special register in the db2cli.ini file, by using the
SQLSetConnectAttr API, in the SQLE-CLIENT-INFO structure, and by including the SET CURRENT PACKAGE
PATH statement in embedded SQL programs. Only Db2 for z/OS, Version 8 or
later, supports the SET CURRENT PACKAGE PATH statement. If you issue this statement against a
Db2 server
or against Db2 for IBM i, -30005 is
returned.
You can use multiple schemas to maintain several variations of
a package. These variations can be a very useful in helping to control
changes made in production environments. You can also use different
variations of a package to keep a backup version of a package, or
a test version of a package (for example, to evaluate the impact of
a new index). A previous version of a package is used in the same
way as a backup application (load module or executable), specifically,
to provide the ability to revert to a previous version.
For example, assume the PROD schema includes the current packages
used by the production applications, and the BACKUP schema stores
a backup copy of those packages. A new version of the application
(and thus the packages) are promoted to production by binding them
using the PROD schema. The backup copies of the packages are created
by binding the current version of the applications using the backup
schema (BACKUP). Then, at runtime, you can use the SET CURRENT PACKAGE
PATH statement to specify the order in which the schemas should be
checked for the packages. Assume that a backup copy of the application
MYAPPL has been bound using the BACKUP schema, and the version of
the application currently in production has been bound to the PROD
schema creating a package PROD.MYAPPL. To specify that the variation
of the package in the PROD schema should be used if it is available
(otherwise the variation in the BACKUP schema is used), issue the
following SET statement for the special register:
SET CURRENT PACKAGE PATH = PROD, BACKUP;
If you need to revert to the previous version of the package, the
production version of the application can be dropped with the DROP
PACKAGE statement, which causes the old version of the application
(load module or executable) that was bound using the BACKUP schema
to be invoked instead (application path techniques could be used here,
specific to each operating system platform).
Note: This example assumes
that the only difference between the versions of the package are in
the BIND options that were used to create the packages (that is, there
are no differences in the executable code).
The application does not use the SET CURRENT PACKAGESET statement to select the schema it wants.
Instead, it allows
Db2 to pick up the
package by checking for it in the schemas listed in the CURRENT PACKAGE PATH special register.
Note: The Db2 for z/OS
precompile process stores a consistency token in the DBRM (which can be set using the LEVEL option),
and during package resolution a check is made to ensure that the consistency token in the program
matches the package. Similarly, the Db2 bind process
stores a timestamp in the bind file. Db2 also supports a
LEVEL option.
Another reason for creating several versions of a package in different
schemas could be to cause different BIND options to be in affect.
For example, you can use different qualifiers for unqualified name
references in the package.
Applications are often written with unqualified table names. This supports multiple tables that
have identical table names and structures, but different qualifiers to distinguish different
instances. For example, a test system and a production system might have the same objects created in
each, but they might have different qualifiers (for example, PROD and TEST). Another example is an
application that distributes data into tables across different
Db2 systems, with each
table having a different qualifier (for example, EAST, WEST, NORTH, SOUTH; COMPANYA, COMPANYB;
Y1999, Y2000, Y2001). With
Db2 for z/OS, you specify the
table qualifier using the QUALIFIER option of the BIND command. When you use the QUALIFIER option,
users do not have to maintain multiple programs, each of which specifies the fully qualified names
that are required to access unqualified tables. Instead, the correct package can be accessed at
runtime by issuing the SET CURRENT PACKAGESET statement from the application, and specifying a
single schema name. However, if you use SET CURRENT PACKAGESET, multiple applications will still
need to be kept and modified: each one with its own SET CURRENT PACKAGESET statement to access the
required package. If you issue a SET CURRENT PACKAGE PATH statement instead, all of the schemas
could be listed. At execution time,
Db2 could choose the
correct package.
Note: Db2 also supports a
QUALIFIER bind option. However, the QUALIFIER bind option only affects static SQL or packages that
use the DYNAMICRULES option of the BIND command.