Special Register Values

The Special Register Values menu (see the following figure) displays when you select option 8 from the User Parms menu. It lets you specify values for the following: Current Path, Current Schema, Current Package Path Special Registers, and MQT Special Registers.

Special Register Values Menu

File-AID for Db2 ------------- Special Register Values-------------------------
OPTION ===>                                                   SSID ===> DSN

                 1   CURRENT PATH
                 2   CURRENT SCHEMA
                 3   CURRENT PACKAGE PATH
                 4   MQT SPECIAL REGISTERS
                 5   CURRENT QUERY ACCELERATION

The Current Path and Current Package Path information is stored in a work file, named userid.FADB2.USERDATA or hlq.userid.FADB2.USERDATA.

Important

The name of the work file depends on what you specified in Option 2 in the User Parms menu. See Specify Work File Dataset Info Area for details about the name of this file.

The work file is a PDS, which contains one member for path and one member for package path, for each subsystem that you access. The members are named ssidP for path, and ssidPP for package path. The PDS is automatically allocated the first time you enter the Current Path or Current Package Path function; the members are created similarly.

Current Path

The Current Path screen (see the following figure) displays when you select option 1 from the Special Register Values menu. It lets you specify a list of schema (creator) names to be added to the CURRENT PATH Special Register. CURRENT PATH is used in the resolution of unqualified references to user-defined functions, distinct types, and stored procedures.

Current Path screen

EDIT ---- TSOID01.FADB2.USERDATA(DB01P) - 01.00 -------------- Columns 001 072
COMMAND ===> SCROLL ===> PAGE
****** ***************************** Top of Data ******************************
=NOTE=
=NOTE= ENTER SCHEMAS TO BE USED FOR CURRENT PATH SPECIAL REGISTER.
=NOTE= ONE OR MORE SCHEMAS MAY BE ENTERED PER LINE, SEPARATED BY
=NOTE= BLANK(S) OR A COMMA. QUOTES ARE PERMITTED BUT NOT REQUIRED
=NOTE= UNLESS THE SCHEMA NAME CONTAINS BLANKS
=NOTE=
000001 "SYSIBM","SYSFUN","SYSPROC","TSOID01"
****** **************************** Bottom of Data ****************************

The initial value of the CURRENT PATH special register is one of the following:

The last saved CURRENT PATH values in the userid.FADB2.USERDATA or hlq.userid.FADB2.USERDATA, depending on what you specified in Option 2 in the User Parms menu. See Specify Work File Dataset Info Area for more information.
The value of the PATH bind option.
"SYSIBM", "SYSFUN", "SYSPROC", "CURRENT SQLID" if the PATH bind option was not specified or if you blank out this field.

You may also enter SYSTEM PATH which translates into "SYSIBM", "SYSFUN", "SYSPROC".

The total length of the string of all schema names, including delimiters, in the CURRENT PATH register cannot exceed 2048 characters. Remember also that SYSTEM PATH expands into "SYSIBM", "SYSFUN", "SYSPROC" for the SET CURRENT PATH statement.

When you enter values, the following validation rules apply:

If quotes are used, they must be entered in pairs on the same line.
Values entered between quotes are processed as is.
Leading and trailing blanks are dropped from entries without quotes.
Values are enclosed in quotes only if they are entered that way.
Blanks and/or commas are valid delimiters between values.
Schema is validated by Db2 when you execute the SET CURRENT PATH command.

Current Schema

The Current Schema screen (see the following figure) displays when you select option 2 from the Special Register Values menu. It lets you qualify unqualified database object names for dynamic SQL. Use Current Schema only to qualify unqualified SQL. Current Schema is set to the same value as Current SQLID at initialization time. When you exit this screen, a SET SCHEMA command will be issued using the values that you specified.

Current Schema Screen

File-AID for Db2 ----------------- CURRENT SCHEMA ----------------- SSID: DSN
COMMAND ===>

SQLID: TSOID01
Note: CURRENT SCHEMA will be used to qualify unqualified objects in dynamic SQL

Enter Identifier to be used for CURRENT SCHEMA special register:
Name: USER
-------------------------------------------------------------------------

Current Package Path

The Current Package Path screen (see the following figure) displays when you select option 3 from the Special Register Values menu. It lets you specify a value that identifies the path used to resolve references to packages used to execute SQL statements.

Current Package Path Screen

EDIT ---- TSOID01.FADb2.USERDATA(D801PP) - 01.00 ------------- Columns 001 072
COMMAND ===> SCROLL ===> PAGE
****** ***************************** Top of Data ******************************
=NOTE=
=NOTE= ENTER COLLECTION IDS TO BE USED FOR THE CURRENT PACKAGE
=NOTE= PATH SPECIAL REGISTER. ONE OR MORE COLLECTION IDS MAY BE
=NOTE= ENTERED PER LINE, SEPARATED BY BLANK(S) OR A COMMA. QUOTES
=NOTE= ARE PERMITTED BUT NOT REQUIRED UNLESS THE COLLECTION ID
=NOTE= NAME CONTAINS BLANKS.

=NOTE= IMPORTANT NOTE: IF YOU ENTER A LIST OF COLLECTION IDS, YOU
=NOTE= MUST INCLUDE THE COLLECTION ID ASSOCIATED WITH THE PLAN
=NOTE= FILE-AID FOR Db2 WAS BOUND WITH. SPECIFY COLLECTION
=NOTE= ID FDxxxxxx
=NOTE=
************************************ Bottom of Data ****************************

When you enter values, the following validation rules apply:

If quotes are used, they must be entered in pairs on the same line.
Values entered between quotes are processed as is.
Leading and trailing blanks are dropped from entries without quotes.
Values are enclosed in quotes only if they are entered that way.
Blanks and/or commas are valid delimiters between values.
Collection ID is validated by Db2 when you issue the SET CURRENT PACKAGE PATH command.

MQT Special Registers

The MQT Special Registers screen (see the following figure) displays when you select option 4 from the Special Register Values menu. This screen lets you specify the Current Refresh Age and the Current Maintained Table Types for Optimization.

MQT Special Registers

File-AID for Db2 ----------- MQT Special Registers ---------------- SSID: DSN
COMMAND ===>

CURRENT REFRESH AGE                             ===> 0
                                                      (0, ANY)

CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION ===> SYSTEM
                                                     (SYSTEM, USER, ALL, NONE)

CURRENT REFRESH AGE

This is a timestamp duration (DECIMAL 20,6) since the last REFRESH TABLE statement was executed. Enter 0 or ANY. A value of 0 means that only tables kept current with updates can be used for automatic rewrite. There is no guarantee that the table will be kept current, so a value of 0 says to not use any of the tables. A value of ANY means all materialized query tables are considered for rewrite. The refresh age of a user-maintained table is not maintained.

CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION

This specifies which types of tables are used for automatic query rewrite, system-maintained, user-maintained or both. Specify a value of SYSTEM, USER, ALL or NONE.

Current Query Acceleration

The Current Query Acceleration screen (see the following figure) displays when you select option 5 from the Special Register Values menu. On this screen you can set CURRENT QUERY ACCELERATION Special Register value from the listed set values.

File-AID for Db2 will issue a SET CURRENT QUERY ACCELERATION statement with the selected value. Refer to the IBM Db2 Analytics Accelerator for z/OS documentation for more detailed information.

Current Query Acceleration

File-AID for Db2 --------- Current Query Acceleration ------------- SSID: DSN
COMMAND ===>

Enter value to be used for CURRENT QUERY ACCELERATION special register.

CURRENT QUERY ACCELERATION ===> 2

            Valid values: 1 = NONE
                           2 = ENABLE
                           3 = ENABLE WITH FAILBACK
                           4 = ELIGIBLE
                           5 = ALL
                       blank = Use subsystem default: NONE

CURRENT QUERY ACCELERATION

Enter a value from the listed Valid values.

The specified value is used even when switching the subsystem. The value is saved in the user profile and used for the next session. Blank out the value to use the subsystem's default QUERY ACCELERATION status which is displayed on the screen.

Valid values

1 (NONE)
Query acceleration will not be done.

2 (ENABLE)
Query acceleration will be done only if Db2 determines that it is advantageous to do so.

3 (ENABLE WITH FAILBACK)
Query acceleration will be done only if Db2 determines that it is advantageous to do so. An accelerator error during the PREPARE or first OPEN for the query will execute the query without the accelerator. An accelerator error during a FETCH or a subsequent OPEN, will return the error to the user and the query will not execute.

4 (ELIGIBLE)
Query acceleration will be done if queries are eligible for acceleration. Cost information is not used when Db2 determines eligibility. Non-eligible queries are executed by Db2. If an accelerator failure occurs while a query is running or if the accelerator returns an error, Db2 returns a negative SQLCODE.

5 (ALL)
Query acceleration will be done if queries are eligible for acceleration. Cost information is not used when Db2 determines eligibility. Non-eligible queries are executed by Db2. If an accelerator failure occurs while a query is running or if the accelerator returns an error, Db2 returns a negative SQLCODE.

blank (default)
Use the subsystem's default QUERY ACCELERATION status as displayed.