Error message RTN=106 RCODE=0133 DB2 ssid IS NOT DEFINED IN THE PARMLIB
search cancel

Error message RTN=106 RCODE=0133 DB2 ssid IS NOT DEFINED IN THE PARMLIB

book

Article ID: 54648

calendar_today

Updated On:

Products

Plan Analyzer for DB2 for z/OS

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

Manage Explain Profiles

Installing

Expert System Rules