Error message RTN=106 RCODE=0133 DB2 ssid IS NOT DEFINED IN THE PARMLIB
Article ID: 54648
Updated On:
Products
Issue/Introduction
Using Option 1) Quick EXPLAIN , the following error was displayed:
RTN=106,RCODE=0133, DB2 SSID ssid IS NOT DEFINED IN THE PARMLIB
The output of the requested option displayed the following:
.CALL EXPLAIN
.DATA
RULESSID = (ssid)
ACM = (N,xxxxxxx)
VERSION = (LATEST)
STRATEGY = (TST1,,xxxxxxx)
PLANTAB = (ROLLBACK)
CASE = (UPPER)
LINES = (55)
PROCDDF = (N)
FLOATFMT = (SCI)
REPTFMT = (OLD)
PROCVIEW = (Y)
EXPLTYPE = (FUTURE)
PLANEXPL = (PACKAGE)
ISOLATE = (CS)
TARGET = (ssid(@DEFAULT))
PERFTIE = (N)
REPORT = (SUMMARY,SUPPRPTS,ACCESS,PREDICATE/SHORT,DEPENDENCY/SHORT,
DEPENDENCY,STATISTICS,SQLRULE,PHYSRULE/SHORT,PREDRULE,
PLANRULE/SHORT,PLANRULE)
SRCPACK = (TST1,LOCAL,BATCH,xxxxxxxx,,,)
.ENDDATA
Where did the (ssid) value come from for the RULESSID parameter?
Environment
Release: r20
Component: Plan Analyzer for Db2 for z/OS
Resolution
The (ssid) subsystem id is populated from the Explain Options screen.
To access this screen from the Plan Analyzer Main Menu, select Option 1) Quick EXPLAIN.
Option 1 displays the following:
----------------- PPA Quick Explain ---------------- yyyy/mm/dd hh:mm
COMMAND ===>
---------------------------------------------------------------------- authid
Location ===> LOCAL DB2 SSID ===> ssid Target SSID ===> ssid
Specify the name of the plan or package that is to be explained or enter
selection criteria to view a selection list.
Plan ===> > Package ===>
Collid ===> xxxxxxx > Package ===> xxxxxxx >
Selection List ===> Y ( Y - Display plan or package selection list )
( N - Execute explain with selection criteria )
Change Options ===> N ( Y or N )
Profile ===> profname
************************************************************* *************************************************************
Change the "Change Options ===> N" parameter from "N" to "Y".
The "PPA Quick Explain - Explain Options" screen displays:
-------- PPA Quick Explain - Explain Options ------- yyyy/mm/dd hh:mm
COMMAND ===>
---------------------------------------------------------------------- authid
Database Options ===> Y Historical Database Options
Primary AUTHID ===> authid Secondary AUTHID ===> authid
Update SQL Qualifiers ===> Y (Override Schemas & SQL/View Qualifiers)
Rule Set SSID ===> ssid (Subsystem where Rule Sets are stored)
PLAN_TABLE Option ===> C
Explain Type ===> C Package Copy ===> C
Non-Catalog Isolation ===> CS Optimization Hint ===> >
Process Views (Y, N) ===> Y Parallelism Degree ===>
Target SSID ===> ssid Target Rule Set ===> rulename
Uppercase Output ===> Y Access Path Filter ===> N
Reports ===> Y Search Conditions ===> Y
Catalog Statistics ===> N Search Filters ===> N
************************************************************* *************************************************************
Change the Subsystem parameter in the "Rule Set SSID" to the Subsystem where the Rule Sets are stored.
Rule Set SSID ===> ssid
Press PF3 and the RULESSID will be populated on the "PPA Batch Processor Card Display" with the Subsystem that you specified.
.CALL EXPLAIN
.DATA
RULESSID = (ssid)
ACM = (N,authid)
STRATEGY = (ssid,,authid)
PLANTAB = (COMMIT)
PRIAUTH = (authid)
SECAUTH = (authid1)
CASE = (UPPER)
LINES = (55)
PROCDDF = (Y)
FLOATFMT = (ENG)
SPEXPL = (Y)
PROCVIEW = (Y)
EXPLTYPE = (CURRENT)
PKGCOPY = (CURRENT)
ISOLATE = (CS)
TARGET = (ssid(authid))
DATABASE = (N,Y,xxxxxxxx,A,Y)
REPORT = (ACCESS/SHORT,COST,CVCOST,DEPENDENCY/SHORT,PLANRULE,
PREDICATE,PREDRULE/SHORT,RI,SQLRULE,SUMMARY,SUPPRPTS)
COMPOPTS = (ABNBEE NY,,A,+05,+05,+05)
CVCCOPTS = (S,ALL)
TYPE = (SELECT,INSERT,UPDATE,DELETE,MERGE)
SRCPACK = (ssid,LOCAL,xxxxxxx,xxxxxxx,'VERS'(
'2020-09-18-06.50.10.735013'))
.ENDDATA
DSN is the installation default for the product.
This parameter is populated from one of three places:
1) hlq.CDBAPARM, member PPA, parameter RULESSID (DSN) where DSN is replaced with the SSID.
RULESSID (DSN) /* SUBSYSTEM WHERE RULE SETS STORED */
/* */
2) the users own PPA Profile stored in the users ISPF profile dataset
3) Manage Explain Profiles for the given user profile.
Rule Set SSID ===> DSN (Subsystem where Rule Sets are stored)
This type of error message RTN=106,RCODE=0133, DB2 SSID ssid IS NOT DEFINED IN THE PARMLIB
can be seen in a variety of situations where Plan Analyzer is attempting to connect to an ssid where that ssid
is not defined in the parmlib.
In this case it is the default ssid "DSN" that was seen but it can also be seen for other ssid values that are not defined
in the associated hlq.CDBAPARM(SETUPxx) member. If it is an ssid that should be correct then check that the problem
is not caused by the wrong suffix of SETUP which would then point to the wrong SETUPxx member.
Using the STATUS command may help to see the current SETUPxx member being used.
Additional Information
Feedback
