SET CURRENT PACKAGE PATH

The SET CURRENT PACKAGE PATH statement assigns a value to the CURRENT PACKAGE PATH special register.

Invocation

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

Authorization

None required.

Syntax

                                  .-,----------------------------.   
                            .-=-. V  (1)                         |   
>>-SET CURRENT PACKAGE PATH-+---+-------+-collection-id--------+-+-><
                                        +-+-SESSION_USER-+-----+     
                                        | '-USER---------'     |     
                                        +-CURRENT PACKAGE PATH-+     
                                        +-CURRENT PATH---------+     
                                        +-host-variable--------+     
                                        '-string-constant------'     
Notes:
  1. SESSION_USER (or USER), CURRENT PACKAGE PATH, and CURRENT PATH can each be specified only once on the right side of the statement.

Description

The value of CURRENT PACKAGE PATH is replaced by the values specified.

collection-id
Identifies a collection. collection-id must not be a delimited identifier that is empty or contains only blanks.
SESSION_USER or USER
Specifies the value of the SESSION_USER (USER) special register.
CURRENT PACKAGE PATH
Specifies the value of the CURRENT PACKAGE PATH special register before the execution of the SET CURRENT PACKAGE PATH statement.
CURRENT PATH
Specifies the value of the CURRENT PATH special register.
host-variable
Specifies a host variable that contains one or more collection IDs, separated by commas. The host variable must:
  • Have a data type of CHAR or VARCHAR. The actual length of the contents of the host variable must not exceed the maximum length of the CURRENT PACKAGE PATH special register.
  • Not be the null value if an indicator variable is provided.
  • Contain an empty or blank string, or one or more collection IDs that are separated by commas.
  • Be padded on the right with blanks if the host variable is fixed-length, or if the actual length of the host variable is longer than the content.
  • Not contain a delimited identifier that is empty or contains only blanks.
string-constant
Specifies a string constant that contains one or more collection IDs, separated by commas. The string constant must:
  • Have a length that does not exceed the maximum length of the CURRENT PACKAGE PATH special register.
  • Contain an empty or blank string, or one or more collection IDs separated by commas.
  • Not contain a delimited identifier that is empty or contains only blanks.

Notes

Contents of host variable or string constant: The contents of a host variable or string constant are interpreted as a list of collection IDs if the value contains at least one comma. If multiple collection IDs are specified, they must be separated by commas. Each collection ID in the list must conform to the rules for forming an ordinary identifier or be specified as a delimited identifier.

Checking for the existence of collections: No validation that the collections exist is made at the time that the CURRENT PACKAGE PATH special register is set. For example, a collection ID that is misspelled is not detected, which could affect the way subsequent SQL operates. At package execution time, authorization to the specific package is checked, and if this authorization check fails, an error is issued.

Resulting contents of the special register: The special register string is built by taking each collection ID specified and removing trailing blanks, delimiting with double quotation marks, doubling any double quotation marks within the collection ID as necessary, and then separating each collection ID by a comma. If the same collection ID appears more than once in the list, the first occurrence of the collection is used, and a warning is issued. The length of the resulting list cannot exceed the length of the special register. For example, assume that the following statements are issued: 
SET CURRENT PACKAGE PATH = MYPKGS, "ABC E", SYSIBM
SET :HVPKLIST = CURRENT PACKAGE PATH
These statements result in the value of the host variable being set to: "MYPKGS", "ABC E", "SYSIBM".

A collection ID that does not conform to the rules for an ordinary identifier must be specified as a delimited collection ID and must not be specified within a host variable or string constant.

Considerations for keywords: A difference exists between specifying a single keyword, such as SESSION_USER, as a single keyword or as a delimited identifier. To indicate that the current value of a special register that is specified as a single keyword should be used in the package path, specify the name of the special register as a keyword. If you specify the name of the special register as a delimited identifier, it is interpreted as a collection ID of that value. For example, assume that the current value of the SESSION_USER special register is SMITH and that the following statement is issued: 
SET CURRENT PACKAGE PATH = SYSIBM, SESSION_USER, "USER"
The result is that the value of the CURRENT PACKAGE PATH special register is set to: "SYSIBM, "SMITH", "USER".

Specifying a collection ID in an SQL procedure: Because a host variable (SQL variable) in an SQL procedure does not begin with a colon, DB2® uses the following rules to determine whether a value that is specified in a SET PACKAGE PATH = name statement is a variable or a collection ID:

  • If name is the same as a parameter or SQL variable in the SQL procedure, DB2 uses name as a parameter or SQL variable and assigns the value in name to the package path.
  • If name is not the same as a parameter or SQL variable in the SQL procedure, DB2 uses name as a collection ID and assigns and the value in name is the package path.

DRDA classification: The SET CURRENT PACKAGE PATH statement is executed by the database server and, therefore, is classified as a non-local SET statement in DRDA. The SET CURRENT PACKAGE PATH statement requires a new level of DRDA support. If SET CURRENT PACKAGE PATH is issued when connected to the local server, the SET CURRENT PACKAGE PATH special register at the local server is set. Otherwise, when SET CURRENT PACKAGE PATH is issued when connected to a remote server, the SET CURRENT PACKAGE PATH special register at the remote server is set.

Examples

Example 1: Set the CURRENT PACKAGE PATH special register to the list of collections COLL4 and COLL5, where :hvar1 contains the value COLL4,COLL5: 
   SET CURRENT PACKAGE PATH :hvar1;
The value of CURRENT PACKAGE PATH is set to the following two collection IDs: "COLL4","COLL5".
Example 2: Set the CURRENT PACKAGE PATH special register to the list of collections: COLL1, COLL#2, COLL3, COLL4, and COLL5, where :hvar1 contains the value COLL4,COLL5: 
  SET CURRENT PACKAGE PATH = "COLL1","COLL#2","COLL3", :hvar1;
The value of CURRENT PACKAGE PATH is set to the following five collection IDs: "COLL1,"COLL#2","COLL3","COLL4","COLL5".
Example 3: Clear the CURRENT PACKAGE PATH special register. 
  SET CURRENT PACKAGE PATH = ' ';
Example 4: In preparation of calling a stored procedure that is named SUMARIZE, temporarily add two collections, COLL_PROD1" and "COLL_PROD2, to the end of the CURRENT PACKAGE PATH special register (the values of the collections are in host variables :prodcoll1 and prodcoll2, respectively). Because the stored procedure SUMARIZE is not defined with a COLLID value and is defined with INHERIT SPECIAL REGISTERS, the stored procedure will inherit the value of CURRENT PACKAGE PATH. When the stored procedure returns, set the value of the CURRENT PACKAGE PATH special register back to its original value. 
  SET :oldCPP = CURRENT PACKAGE PATH;
  SET CURRENT PACKAGE PATH = CURRENT PACKAGE PATH, :prodcoll1, :prodcoll2;
  CALL SUMARIZE(:V1,:V2); 
  SET CURRENT PACKAGE PATH = :oldCPP;