DB2 Version 9.7 for Linux, UNIX, and Windows

SET CURRENT PACKAGE PATH statement

The SET CURRENT PACKAGE PATH statement assigns a value to the CURRENT PACKAGE PATH special register. It is not under transaction control.

Invocation

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

Authorization

None required.

Syntax


                             .-=-.   
>>-SET CURRENT PACKAGE PATH--+---+------------------------------>

   .-,------------------------.   
   V                          |   
>----+-schema-name----------+-+--------------------------------><
     +-CURRENT PACKAGE PATH-+     
     +-+-CURRENT PATH-+-----+     
     | '-CURRENT_PATH-'     |     
     +-+-CURRENT USER-+-----+     
     | '-CURRENT_USER-'     |     
     +-SESSION_USER---------+     
     +-SYSTEM_USER----------+     
     +-USER-----------------+     
     +-host-variable--------+     
     '-string-constant------'

Description

schema-name

Identifies a schema. The name must not be a delimited identifier that is empty or that contains only blanks (SQLSTATE 42815).

CURRENT PACKAGE PATH

The value of the CURRENT PACKAGE PATH special register before this statement executes.

CURRENT PATH

The value of the CURRENT PATH special register.

CURRENT USER

The value of the CURRENT USER special register.

SESSION_USER

The value of the SESSION_USER special register.

SYSTEM_USER

The value of the SYSTEM_USER special register.

USER

The value of the USER special register.

host-variable

Contains one or more schema names, separated by commas. The host variable must:

Be a character-string variable (CHAR or VARCHAR). The actual length of the contents of the host variable must not exceed the length of the CURRENT PACKAGE PATH special register.
Not be the null value. If an indicator variable is provided, its value must not indicate a null value.
Contain an empty or blank string, or one or more schema names separated by commas.
Be padded on the right with blanks if the actual length of the host variable is greater than the content.
Not contain CURRENT PACKAGE PATH, CURRENT PATH, CURRENT_PATH, CURRENT USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, PATH, or USER.
Not contain a delimited identifier that is empty or that contains only blanks.

string-constant

Specifies a character string constant that contains zero, one, or more schema names that are separated by commas. The string constant must:

Have a length that does not exceed the maximum length of the CURRENT PACKAGE PATH special register.
Not contain CURRENT PACKAGE PATH, CURRENT PATH, CURRENT_PATH, CURRENT USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, PATH, or USER.
Not contain a delimited identifier that is empty or that contains only blanks.

Rules

If the same schema appears more than once in the list, the first occurrence of the schema is used (SQLSTATE 01625).
The number of schemas that can be specified is limited by the total length of the CURRENT PACKAGE PATH special register. The special register string is built by taking each specified schema name and removing trailing blanks, delimiting the name with double quotation marks, and separating the schema names with commas. The length of the resulting list cannot exceed the maximum length of the special register (SQLSTATE 0E000).
A schema name that does not conform to the rules for an ordinary identifier (for example, a schema name that contains lowercase characters or characters that cannot be specified in an ordinary identifier), must be specified as a delimited schema name, and must not be specified within a host variable or string constant.
To indicate that the current value of a special register (specified as a single keyword) is to be used in the package path, specify the name of the special register as a keyword. If the name of the special register is specified as a delimited identifier instead (for example, "USER"), it is interpreted as a schema name of that value ('USER').
The following rules are used to determine whether a value specified in a SET CURRENT PACKAGE PATH statement is a variable or a schema name:
- If name is the same as a parameter or SQL variable in the SQL procedure, name is interpreted as a parameter or SQL variable, and the value in name is assigned to the package path.
- If name is not the same as a parameter or SQL variable in the SQL procedure, name is interpreted as a schema name, and the value in name is assigned to the package path.

Notes

Transaction considerations: The SET CURRENT PACKAGE PATH statement is not a commitable operation. ROLLBACK has no effect on the CURRENT PACKAGE PATH special register.
Existence checking of schemas: No validation that the specified schemas exist is made at the time that the CURRENT PACKAGE PATH special register is set. For example, a schema that is misspelled is not detected, which could affect the way subsequent SQL operates. At package execution time, authorization to a matching package is checked, and if this authorization check fails, an error is returned (SQLSTATE 42501).
Contents of host variable or string constant: The contents of a host variable or a string constant are interpreted as a list of schema names. If multiple schema names are specified, they must be separated by commas. Each schema name in the list must conform to the rules for forming an ordinary identifier, or be specified as a delimited identifier. The contents of the host variable or string constant are not folded to uppercase.
Restrictions specific to embedded SQL for COBOL applications: A maximum of ten literal (non-host variable) values can appear on the right side of a SET CURRENT PACKAGE PATH statement. Such values can have a maximum length of 130 (non-delimited) or 128 (delimited).

Examples

Example 1: Set the CURRENT PACKAGE PATH special register to the following list of schemas: MYPKGS, 'ABC E', SYSIBM

   SET CURRENT PACKAGE PATH = MYPKGS, 'ABC E', SYSIBM

The following statement sets a host variable to the value of the resulting list:

   SET :hvpklist = CURRENT PACKAGE PATH

The value of the host variable is: "MYPKGS", "ABC E", "SYSIBM".

Example 2: Set the CURRENT PACKAGE PATH special register to the following list of schemas: "SCH4","SCH5", where :hvar1 contains 'SCH4,SCH5'.

   SET CURRENT PACKAGE PATH :hvar1

The value of the CURRENT PACKAGE PATH special register after this statement executes is: "SCH4","SCH5".

Example 3: Set the CURRENT PACKAGE PATH special register to the following list of schemas: "SCH1","SCH#2","SCH3","SCH4","SCH5", where :hvar1 contains 'SCH4,SCH5'.

   SET CURRENT PACKAGE PATH = SCH1,'SCH#2',"SCH3",:hvar1

The value of the CURRENT PACKAGE PATH special register after this statement executes is: "SCH1","SCH#2","SCH3","SCH4","SCH5".

Example 4: Clear the CURRENT PACKAGE PATH special register.

   SET CURRENT PACKAGE PATH = ''

Example 5: Temporarily append the "SCH_PROD" schema (contained in the :prodschema host variable) and the "SCH_PROD2" schema (contained in the :prod2schema host variable) to the end of the CURRENT PACKAGE PATH special register for execution of the SUMMARIZE procedure. Then, switch the CURRENT PACKAGE PATH special register back to its previous value.

   SET :oldCPP = CURRENT PACKAGE PATH

   SET CURRENT PACKAGE PATH = CURRENT PACKAGE PATH,:prodschema,:prod2schema

   CALL SUMMARIZE(:V1,:V2)

   SET CURRENT PACKAGE PATH = :oldCPP

Example 6: Set the CURRENT PACKAGE PATH special register to a list of delimited schema names: "MY.SCHEMA" (imbedded period), "OLD SCHEMA" (imbedded blank). Use a single host variable containing both delimited identifiers:

   hv = '"MY.SCHEMA", "OLD SCHEMA"'

   SET CURRENT PACKAGE PATH = :hv

or use a single string constant containing both delimited identifiers:

   SET CURRENT PACKAGE PATH = '"MY.SCHEMA", "OLD SCHEMA"'

or use a list of delimited schemas:

   SET CURRENT PACKAGE PATH = 'MY.SCHEMA', 'OLD SCHEMA'