SET CURRENT PACKAGESET

Invocation

SET CURRENT PACKAGESET is an executable statement that can only be embedded in an application program. It cannot be dynamically prepared.

Authorization

No special authorization is required.

Syntax

SET CURRENT PACKAGESET = 
    { USER | string-constant | host-variable }

Parameters:

`USER`	The value contained in the USER special register.
`string-constant`	When used with DB2, this string constant value must be a character string not longer than 128 bytes, and enclosed in quotation marks (see DB2 documentation for allowed values).
`host-variable`	The CURRENT PACKAGESET special register value can be assigned to a host variable in your program using the SET `Host Variable` command. When used with DB2, this host variable value must be a character string not longer than 128 bytes (see DB2 documentation for allowed values).

Description

In DB2, each program is bound with a default SQLID. Likewise, each package accessed by a program can be bound with a different default SQLID. The AuthID values comprising these various default SQLIDs must somehow be retrieved into the CURRENT SQLID special register within XDB Server whenever package access changes during program execution. The contents of the CURRENT SQLID special register provides the implicit AuthID qualifier support necessary for one-part table names, and this register value must be updated to match the default SQLID of the currently executing package.

The XDB Server does not directly support binding, but emulates this functionality by retrieving default package SQLIDs into the CURRENT SQLID special register. This retrieval needs to happen whenever a SET CURRENT PACKAGESET command switches package access within an executing program. Therefore, the value of the CURRENT SQLID special register in XDB Server can change depending on the name of the executing program, and the contents of the CURRENT PACKAGESET special register.

XDB Server Precompiler

To take advantage of the packageset functionality above, the program must be precompiled using the PKGSET directive. This directive causes the program name to be passed to the engine for all SQL calls within any program precompiled with this directive. The engine, in turn, uses the program name to look up package information in the system tables.

System Tables

Records must also be manually added to the SYSIBM.SYSPACKLIST and SYSIBM.SYSPACKAGE tables for all programs requiring packageset functionality. These tables will be automatically created with the CREATE LOCATION command. Alternatively, they will be created when a package command is issued. To create these system tables in an existing location, issue the following command from SQLWizard:

SET CURRENT PACKAGESET = 'test'

The records for these tables currently need to be added manually (using SQLWizard or Migrate.). Ideally, these records can be imported directly from DB2. The XDB Server software does not add these records automatically at precompile and compile time. Because these are system tables, the user who edits the records must be a super user. The only record fields that must be added to the SYSIBM.SYSPACKAGE table are the LOCATION, COLLID and QUALIFIER column values. All other data is inconsequential. Likewise, the only field values that must be added to the SYSIBM.SYSPACKLIST table are the LOCATION and COLLID column values.

The SET CURRENT PACKAGESET command functional support consists of the following steps:

When a program precompiled with the PKGSET directive accesses the engine the following query is performed:

SELECT x.qualifier, x.collid, y.seqno 
    FROM sysibm.syspackage x, sysibm.syspacklist y 
    WHERE y.name IN ('*', program_name) 
      AND y.location IN (' ', current_location) 
      AND x.collid = y.collid 
      AND x.name = program_name 
    ORDER BY y.seqno

If the CURRENT PACKAGESET special register is not empty, the following predicate will be added to the query above:
```
AND x.collid = current_packageset_special_register
```
If a record matches the criteria, the CURRENT SQLID special register is updated with the QUALIFIER column contents of the first record retrieved by the query (lowest SEQNO column value). This new value becomes the implicit AuthID for one-part table names.
Once a value is retrieved for a particular program name and package combination, it is cached. Subsequent calls check the cache prior to checking the catalog using the above query.
If either the program name (due to a subroutine call) or CURRENT PACKAGESET special register (due to a SET CURRENT PACKAGESET COMMAND) changes, an automatic lookup is triggered and the CURRENT SQLID is changed accordingly.

special register with stored procedures and user-defined functions

The initial value of the CURRENT PACKAGESET special register in a stored procedure or user-defined function is the value of the COLLID parameter with which the stored procedure or user-defined function was defined. If the routine was defined without a value for the COLLID parameter, the value of the special register is inherited from the calling program. A stored procedure or user-defined function can use the SET CURRENT PACKAGESET statement to change the value of the special register. This allows the routine to select the version of the DB2 package that is used to process the SQL statements in a called routine that is not defined with a COLLID value.

Example:

The following example assigns a value of 'Staff' to the CURRENT PACKAGESET special register.

SET CURRENT PACKAGESET = 'Staff'