IDUG Content Committee

 View Only

Consistency of zParms in Db2 Data Sharing Groups

By Emil Kotrc posted 11 days ago

  

Consistency of zParms in Db2 Data Sharing Groups

Written by: Emil Kotrc, Broadcom

I really never paid too much attention to the consistency of zParms in a data sharing group until recently. If you plan to prepare for Db2 13 migration and you are about to activate the very last function level of Db2 12 - V12R1M510, you need to make sure that all your packages that are still in use were last rebound with Db2 11 at least. The activation process of this function level runs a query, which relies on the LASTUSED column of SYSPACKAGE catalog table. More on that in this blog.

However, the LASTUSED column might not provide accurate information in case when there is no data collected. The collection is driven by a zParm DISABLE_EDMRTS, by default it is NO and the statistics are collected, but you may have set it to YES for some reasons. Now back to the original point: what if you have a different setting of this zParm on different members? Nothing super terrible happens, but the information might not be accurate and in the worst case, the activation process succeeds even if there are old packages in use by a member where the LASTUSED statistics is not collected. You might experience some autobind issues during the migration, but other than that, all should be good.

That is for this particular zParm. However, there are other zParms that you should treat with care in a data sharing environment. There are actually four categories of zParms in respect of a data sharing group:

  1. zParms that must be different on each member
  2. zParms that must be the same on every member
  3. zParms with other recommendations
  4. The rest of the zParms

This is all very well documented in the IBM manuals, but let's take a brief look into these individual categories.

Note. Please note that despite the fact I mainly speak about the zParms, it is not exclusively about zParms only. There are other parameters involved such as DECP, IRLM, and others. Also, if you want to check these parameters fully you may need to check other sources than zParms, such as BSDS for port numbers, DECP for application defaults, etc.

1. Parameters that must be different

The list of such parameters comprises mainly of the parameters required for logging and subsystem identification. As we know each member in the data sharing group has its own logging environment (active and archive logs and the bootstrap datasets). The documentation lists the following parameters:

Active Logs: COPY 1 PREFIX
Active Logs: COPY 2 PREFIX
Archive Logs: COPY 1 PREFIX (ARCPFX1 parameter)
Archive Logs: COPY 2 PREFIX (ARCPFX2 parameter)
Bootstrap Data Sets : COPY 1 NAME
Bootstrap Data Sets : COPY 2 NAME
COMMAND PREFIX
Db2 NETWORK LUNAME
MEMBER IDENTIFIER
MEMBER NAME
PARAMETER MODULE
PROC NAME
RESYNC PORT
SUBSYSTEM NAME
SUBSYSTEM SEQUENCE
SUBSYSTEM NAME (IRLM)
WORK FILE DB

2. Parameters that must be the same

This is much longer list:

APPL COMPAT LEVEL
AUTH EXIT CACHE REFR
AUTH EXIT CHECK
CATALOG ALIAS
DEL CF STRUCTS
DBADM CREATE AUTH
Db2 LOCATION NAME
DESCRIBE FOR STATIC
DIRECTORY AND CATALOG DATA
DIRECTORY AND CATALOG INDEXES
DRDA PORT
EVALUATE UNCOMMITTED
EXTENDED SECURITY
GROUP ATTACH
GROUP NAME
INDEX SPACE ALLOCATION
IRLM XCF GROUP NAME
INSTALL DD CONTROL SUPT
LIKE_BLANK_INSIGNIFICANT in macro DSN6SPRM
MAX UTILS PARALLELISM
MINIMUM DIVIDE SCALE
PAD INDEXES BY DEFAULT
PERCENT FREE FOR UPDATE
REAL TIME STATS
REVOKE DEP PRIV
SECURE PORT
SITE TYPE
SKIP UNCOMM INSERTS
STATISTICS FEEDBACK
STATISTICS HISTORY
STATISTICS ROLLUP
SYSTEM ADMIN 1
SYSTEM ADMIN 2
SYSTEM OPERATOR 1
SYSTEM OPERATOR 2
SECURITY ADMIN 1
SEC ADMIN 1 TYPE
SECURITY ADMIN 2
SEC ADMIN 2 TYPE
SEPARATE SECURITY
TABLE SPACE ALLOCATION
TEMPLATE TIME
TCP/IP ALREADY VERIFIED
TRACKER SITE
UNICODE IFCIDS
VARY DS CONTROL INTERVAL

I really haven't checked what happens if you try to run a data sharing group with some of these parms with different values, not sure if Db2 actually does some checking for some of them or you would experience unexpected behavior. Please share your experience in the comments.

3. Parameters with other recommendations

Here is the list of zParms that are provided with some recommendations whether they should be the same or not used

DEALLOC PERIOD
DEFAULT 4-KB BUFFER POOL FOR USER DATA
DEFAULT 8-KB BUFFER POOL FOR USER DATA
DEFAULT 16-KB BUFFER POOL FOR USER DATA
DEFAULT 32-KB BUFFER POOL FOR USER DATA
DEFAULT BUFFER POOL FOR USER LOB DATA
DEFAULT BUFFER POOL FOR USER XML DATA
DEFAULT BUFFER POOL FOR USER INDEXES
DEVICE TYPE 1
DEVICE TYPE 2
DISABLE EDM RTS
READ COPY2 ARCHIVE
READ TAPE UNITS
RETAINED LOCK TIMEOUT
START IRLM CTRACE

As you can see the DISABLE EDM RTS is listed here and IBM recommends to have it the same on all members. This is the zParm I mentioned at the beginning and you should have it the same on all members for consistency.

4. All the remaining zParms

In general the values of most parameters do not need to be unique with the exceptions listed above. However, I believe it is still good to at least review the values of all zParms across members, which leads us to the next section.

How to check consistency of zParms

OK, so now we learned that some parameters must be unique and some must be the same. How to actually check the consistency and values of zParms across all members in the data sharing group? Well, there are some tools that can help you with that (some of them with no additional cost - let me know if you are interested), but you can also do it on your own pretty easily. You can check your DSNTIJUZ jobs or you can for instance display your existing zParms.

In one of my prior blogs I have shown a simple REXX script that externalizes the zParms, DECP, IRLM and some other parms via the ADMIN_INFO_SYSPARM stored procedure. If you run this on all your members, save the outputs, and compare, that could do the work. For instance, I was just running SUPERC to compare the zParms using:

//STEP3    EXEC PGM=ASMFSUPC,REGION=4M,          
//         PARM='LINECMP DELTAL LONGLN NOPRTCC'
//STEPLIB  DD DSN=ASMA.SASMMOD2,DISP=SHR         
//NEWDD    DD DISP=SHR,DSN=zparms1
//OLDDD    DD DISP=SHR,DSN=zparms2
//OUTDD    DD SYSOUT=*                           
//SYSIN    DD *                                  
  DPLINE 'ARCPFX'                                
  DPLINE 'IRLM'                                  
  DPLINE 'SSID'                                  
  DPLINE 'DSNZPARM'                              
  DPLINE 'MEMBNAME'                              

However, this does not scale well if your group has more than two or three members, because you need to run a pairwise compare or use some advanced compare tools.

So I rather took a different approach that would work with any group:

  • I created a table ZPARMST with just three columns: PARM, VALUE, SSID
CREATE TABLE ZPARMST(PARM VARCHAR(50), VALUE VARCHAR(50), SSID CHAR(4));
  • Updated my REXX script to insert the values into this table and ran it on every member. See the appendix for the full REXX. Keep in mind that you need to make sure to execute it on every member! Otherwise some data will be missing.
  • I did the analysis using the following query:
SELECT * FROM ZPARMST WHERE (PARM, VALUE) IN (                                                                     
  SELECT PARM, VALUE FROM ZPARMST                                                                                  
  WHERE                                                                                                                    
  PARM NOT IN ('MEMBNAME', 'DSNZPARM', 'ARCPFX1', 'ARCPFX2',                                                               
	       'IRLMPRC', 'IRLMSID')                                                                                       
  GROUP BY PARM, VALUE                                                                                                     
  HAVING COUNT(*) < (SELECT COUNT(DISTINCT SSID) FROM ZPARMST)                                                     
)                                                                                                                          
ORDER BY PARM, SSID, VALUE;                                                                                                 

The SQL will return only the zParms that have a different value at least on one member and it will list the values of such parameters on all the members. Some zParms that must be unique by nature are excluded.

In my case, the group has three members and I have seen the following results:

   +----------------------------------------------------------------------------------------------------------------+ 
   |                        PARM                        |                       VALUE                        | SSID | 
   +----------------------------------------------------------------------------------------------------------------+ 
 1_| ACCEL                                              | NO                                                 | DSN1 | 
 2_| ACCEL                                              | AUTO                                               | DSN2 | 
 3_| ACCEL                                              | COMMAND                                            | DSN3 | 
 4_| ACCELMODEL                                         | NO                                                 | DSN1 | 
 5_| ACCELMODEL                                         | YES                                                | DSN2 | 
 6_| ACCELMODEL                                         | YES                                                | DSN3 | 
 7_| EDMDBDC                                            | 0000015000                                         | DSN1 | 
 8_| EDMDBDC                                            | 0000020000                                         | DSN2 | 
 9_| EDMDBDC                                            | 0000015000                                         | DSN3 | 
10_| EDMSTMTC                                           | 0000030000                                         | DSN1 | 
11_| EDMSTMTC                                           | 0000050000                                         | DSN2 | 
12_| EDMSTMTC                                           | 0000030000                                         | DSN3 | 
13_| PRIQTY                                             | 0000006000                                         | DSN1 | 
14_| PRIQTY                                             | 0000001000                                         | DSN2 | 
15_| PRIQTY                                             | 0000001000                                         | DSN3 | 
16_| QUERY_ACCELERATION                                 | NONE                                               | DSN1 | 
17_| QUERY_ACCELERATION                                 | NONE                                               | DSN2 | 
18_| QUERY_ACCELERATION                                 | ELIGIBLE                                           | DSN3 | 
19_| UTILS_USE_ZSORT                                    | NO                                                 | DSN1 | 
20_| UTILS_USE_ZSORT                                    | YES                                                | DSN2 | 
21_| UTILS_USE_ZSORT                                    | NO                                                 | DSN3 | 
   +----------------------------------------------------------------------------------------------------------------+ 

None of the zParms listed above is recommended to be the same nor must be the same, but I think it is good to see the differences on different members. The rest of the zParms that are not reported are either the same on all members or ignored by the query.

What do you use to check your zParms across the data sharing group? Please share in the comments if you have any good tricks or ideas.

Appendix. REXX to INSERT zParms into a table

//REXXGEN  EXEC PGM=IEBGENER                                       
//SYSIN    DD  DUMMY                                               
//SYSPRINT DD  SYSOUT=*                                            
//SYSUT2   DD  DSN=&&TEMPPDS(ZPARMS),                              
//             DISP=(,PASS),                                       
//             UNIT=SYSDA,SPACE=(TRK,(1,1,1),RLSE),                
//             DCB=(RECFM=FB,LRECL=80)                             
//SYSUT1   DD *                                                    
 /* REXX */                                                        
  parse arg ssid                                                   

  address tso "SUBCOM DSNREXX"                                     
  if rc then do                                                    
    rc = rxsubcom('ADD','DSNREXX','DSNREXX')                       
    if rc then exit                                                
  end                                                              

  address dsnrexx                                                  
  connect ssid                                                     
  if sqlcode \= 0 then call sqlerror                               

  proc = 'SYSPROC.ADMIN_INFO_SYSPARM' /* procedure to execute */   
  empty = ''                                                       
  emptI = -1                                                       
  msg = left(' ', 1331, ' ')          /* output message */         
  msg_ind = ''                        /* indicator not null */     

  /* call the procedure */                                         
  execsql "call :proc (:empty :emptI, :rc, :msg :msg_ind)"         

  /* stored procedure returns +466: number of result sets */       
  if sqlcode <  0 then call sqlerror                               
  /* to get the output from the call,                              
     we need to associate the locator */                           

  execsql "DESCRIBE PROCEDURE",                                    
	  " :proc INTO :sqlda"                                     
  if sqlcode \= 0 then call sqlerror                               

  execsql "ASSOCIATE LOCATOR (:result) ",                          
	  "WITH PROCEDURE :proc"                                   
  if sqlcode \= 0 then call sqlerror                               

  /* allocate cursor for the result set */                         

  execsql "ALLOCATE C101 CURSOR FOR RESULT SET :result"              
  if sqlcode \= 0 then call sqlerror                                 

  cursor = 'C101'                                                    
  execsql "DESCRIBE CURSOR :cursor INTO :sqlda"                      
  if sqlcode \= 0 then call sqlerror                                 

  do until(sqlcode \= 0)                                             
    execsql "FETCH C101 INTO " ,                                     
      ":rownum, :macro, :parameter, :install_panel" ,                
      ", :install_field, :install_location, :valux, :additional_info"
    if sqlcode = 0 then do                                           
      say left(rownum,3) left(macro,8) left(install_panel,8) ,       
	  left(install_field,24) ,                                   
	  left(install_location,3) ,                                 
	  left(additional_info,8) ,                                  
	  left(parameter,24) ,                                       
	  valux                                                      
      insert = "INSERT INTO ZPARMST VALUES(" ,               
	       "'" || parameter || "'," ,                            
	       "'" || valux     || "'," ,                            
	       "'" || ssid      || "')"                              
      execsql insert                                                 
      if sqlcode \= 0 then call sqlerror                             
    end                                                              
  end                                                                

  execsql "CLOSE CURSOR :CURSOR"                                     
  execsql "COMMIT"                                                   
  address dsnrexx "DISCONNECT"                                       
  rc = rxsubcom('DELETE','DSNREXX','DSNREXX')                        
  exit                                                               

sqlerror:                                                            
  say 'SQLCODE ='sqlcode                                             
  say 'SQLSTATE='sqlstate                                            
  say 'SQLERRMC='sqlerrmc                                            
  say 'SQLERRP ='sqlerrp                                             
  say 'SQLERRD ='sqlerrd.1','sqlerrd.2','sqlerrd.3,                  
			||','sqlerrd.4','sqlerrd.5','sqlerrd.6       
  say 'SQLWARN ='sqlwarn.0','sqlwarn.1','sqlwarn.2,                  
		  ||','sqlwarn.3','sqlwarn.4','sqlwarn.5,            
		  ||','sqlwarn.6','sqlwarn.7','sqlwarn.8,            
		  ||','sqlwarn.9','sqlwarn.10                        
  exit 8                                                             
/*                                                                   
//*                                                                  
//RUNREXX  EXEC  PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)                
//STEPLIB  DD  DISP=SHR,DSN=prefix.SDSNEXIT                      
//         DD  DISP=SHR,DSN=prefix.SDSNLOAD
//SYSEXEC  DD  DSN=&&TEMPPDS,DISP=(OLD,DELETE)                       
//SYSTSPRT DD  SYSOUT=*                                              
//SYSPRINT DD  SYSOUT=*     
//SYSUDUMP DD  SYSOUT=*     
//SYSTSIN  DD  *            
%ZPARMS  dsn1
/*                          

Author: Emil Kotrč, emil.kotrc@broadcom.com

Created: 2023-01-12 Thu 09:24

0 comments
399 views

Permalink