ADMIN_UPDATE_SYSPARM stored procedure

Environment

ADMIN_UPDATE_SYSPARM must run in a WLM-established stored procedure address space. At least one library in this WLM procedure STEPLIB DD concatenation must not be APF-authorized. TCB=1 is also required. By default, the SQL procedure processor (DSNTPSMP) and ADMIN_UPDATE_SYSPARM share the WLM environment.

Authorization

To issue the CALL statement, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges:

• The EXECUTE privilege on the ADMIN_UPDATE_SYSPARM stored procedure
• Ownership of the stored procedure
• SYSADM authority

The ADMIN_UPDATE_SYSPARM stored procedure internally calls the following stored procedures:

• ADMIN_COMMAND_DB2, to issue the DB2 DISPLAY GROUP and SET SYSPARM commands
• ADMIN_INFO_SYSPARM, to obtain the current subsystem parameters settings

The owner of the package or plan that contains the CALL ADMIN_UPDATE_SYSPARM statement must also have the following authority and privilege:

• Authorization to run these stored procedures and issue the specified DB2 commands
• MONITOR1 privilege

The ADMIN_UPDATE_SYSPARM caller also needs authorization from an external security system, such as RACF®, to complete the following tasks:

• Browse the data set that is pointed to by the ZPMDFLTS DD statement in the WLM startup procedure
• Update the library where the sample JCL that assembles and link-edits the new subsystem parameters load module will be saved
• Update the load module library where the new subsystem parameters load module will be saved

Syntax

The following syntax diagram shows the SQL CALL statement for running this stored procedure:

>>-CALL--SYSPROC.ADMIN_UPDATE_SYSPARM--(--+-db2_member-+-,------>
                                          '-NULL-------'     

>--+-parameter_module-+-,--+-module_library-+-,----------------->
   '-NULL-------------'    '-NULL-----------'     

>--+-source_library-+-,--+-load-+-,--return-code--)------------><
   '-NULL-----------'    '-NULL-'                     

Option descriptions

db2_member

Specifies the name of a single data sharing group member on which the SET SYSPARM command with the LOAD option is to be run.

If this parameter is null, the SET SYSPARM command is run on the connected DB2 subsystem.

This is an input parameter of type VARCHAR(8).

In a data-sharing environment, some additional requirements apply. For example, if ADMIN_UPDATE_SYSPARM is run on member A to update a subsystem parameter setting on member B, the WLM environment for ADMIN_UPDATE_SYSPARM needs to be able to access the following resources:

• The library that is used to store the new subsystem parameters load module for member B
• The library that is used to store the sample JCL that assembles and link-edits the new subsystem parameters load module for member B.

parameter_module

Specifies the name of the subsystem parameters load module that has the new values assembled and linked into it.

If this parameter is null, the default is the last named subsystem parameters load module that was loaded into storage.

This is an input parameter of type VARCHAR(8).

module_library

Specifies the name of the library where the stored procedure saves the new subsystem parameters load module. If the load parameter is set to 'Y', this module library is also used to load the subsystem parameters load module.

If this parameter is null, the default is the SDSNEXIT_NAME library that is specified in the data set that is pointed to by the ZPMDFLTS DD statement in the WLM startup procedure.

This is an input parameter of type VARCHAR(44).

source_library

Specifies the name of the library where the stored procedure saves a sample JCL that assembles and link-edits the new subsystem parameters load module offline. This JCL contains the updated subsystem parameters.

This parameter must specify a partitioned data set (PDS) or a partitioned data set extended (PDSE) with record format (RECFM) F or FB and record length (LRECL) 80.

If this parameter is null, the default is the SRCLIB_NAME library that is specified in the data set that is pointed to by the ZPMDFLTS DD statement in the WLM startup procedure.

The name of the library member where the sample JCL is saved is the same as the library member name of the new subsystem parameters load module. This JCL is not used by ADMIN_UPDATE_SYSPARM to assemble and link-edit the subsystem parameters load module. This JCL is generated and saved so that it can be referenced for any subsequent subsystem parameter modification by the DB2 system programmer.

This is an input parameter of type VARCHAR(44).

load

Specifies whether to issue SET SYSPARM with the LOAD option to load the new subsystem parameters load module into storage. Possible values are:

Y

Issue SET SYSPARM with the LOAD option.

N

Do not issue SET SYSPARM with the LOAD option.

If this parameter is null, the default is 'N'.

If at least one modified subsystem parameter cannot be changed online or if SET SYSPARM synchronous support is not available (PM40501 is not installed), SET SYSPARM with the LOAD option is not issued even if load is set to 'Y'. Instead, a value of 4 is returned in the output parameter return-code.

This is an input parameter of type CHAR(1).

return-code

Provides the return code from the stored procedure. Possible values are:

0

The call completed successfully.

Message DSNA658I is written to the JES SYSLOG, but not to the JES job log of ssnmMSTR.

The following information is returned in the result table of the stored procedure:

• Message DSNA658I
• Assembler listing
• Link-edit listing
• SET SYSPARM command with LOAD option messages (if applicable)

4

• The stored procedure modified the value of at least one DB2 subsystem parameter. However, the stored procedure did not complete a required or requested action after the subsystem parameter values were changed:
  • The SET SYSPARM command with the LOAD option was not issued because of one of the following reasons:
    • At least one modified subsystem parameter cannot be changed online.
    • Synchronous processing of the SET SYSPARM command is not supported.
  • The stored procedure encountered an error while it was writing a DSNA658I message to the JES SYSLOG.

  For more information, see message DSNA666I.

• The cleanup processing that occurs when the stored procedure completes successfully did not complete successfully because of one of the following reasons:
  • The backup of the library member that the stored procedure replaced with the new JCL that assembles and link-edits the new subsystem parameters load module was not deleted.
  • The backup of the library member that the stored procedure replaced with the new subsystem parameters load module was not deleted.

  For more information, see message DSNA658I.

Message DSNA658I is written to the JES SYSLOG (if applicable), but not to the JES job log of ssnmMSTR.

The following information is returned in the result table of the stored procedure:

• Message DSNA658I
• Message DSNA666I or DSNA658I (depending on which reason caused return_code to be set to 4)
• Assembler listing
• Link-edit listing
• SET SYSPARM command with LOAD option messages (if applicable)

12

The call did not complete successfully.

Message DSNA669I is returned in the result set.

Objects that were replaced by the stored procedure are restored. For more information, see Backup copies.

If the new subsystem parameters load module was loaded into storage, the stored procedure will reload the subsystem parameters load module that was loaded previously.

995

The call did not complete successfully because of a REXX programming violation.

Message DSNA669I is displayed in the WLM job log.

996

The call did not complete successfully because of one of the following global temporary table failures:

• The created global temporary table SYSIBM.UPDSYSPARM_MSG does not exist.
• The user does not have authority to use the created global temporary table SYSIBM.UPDSYSPARM_MSG.

Message DSNA669I is displayed in the WLM job.

997

The call did not complete successfully because of one of the following DSNREXX failures:

• The DSNREXX package was not found.
• DB2 cannot access a DSNREXX environment. DB2 REXX Language Support is not available.

Message DSNA669I is displayed in the WLM job log.

998

There was an error that occurred while the result set was being returned.

The result set is written to the WLM job log. However, the assembler listing and the link-edit listing are not written to the WLM job log unless the call did not complete successfully because the assembly failed or the link-edit failed. If the assembly failed, the assembler listing is written to the WLM job log. If the link-edit failed, the link-edit listing is written to the WLM job log.

This is an output parameter of type INTEGER.

ADMIN_UPDATE_SYSPARM input row

In addition to the input parameters, the stored procedure reads from the created global temporary table SYSIBM.SYSPARM_SETTINGS to retrieve the subsystem parameters to be modified. The following table shows the format of the created global temporary table SYSIBM.SYSPARM_SETTINGS:

Backup copies

When the stored procedure completes successfully, the backup copies are deleted.

When the stored procedure does not complete successfully, the stored procedure restores the replaced members to their state before the stored procedure was run by using their respective backup copies. If the stored procedure is unable to restore a member from the backup, the user must complete this task.

If a member exists with the same name as the backup copy the stored procedure is creating, the stored procedure terminates processing.

Example

The following C language example shows how to invoke ADMIN_UPDATE_SYSPARM.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/******************* DB2 SQL Communication Area ********************/
EXEC SQL INCLUDE SQLCA;  
int main( int argc, char *argv[] )    /* Argument count and list   */
{
 /****************** DB2 Host Variables ****************************/
EXEC SQL BEGIN DECLARE SECTION;    
/* SYSPROC.ADMIN_UPDATE_SYSPARM parameters                         */
char pmember[9];                    /* Data sharing group member   */
char pmodname[9];                   /* Subsystem parameters load   */
                                    /* module                      */
char pmodlib[45];                   /* Subsystem parameters load   */
                                    /* module library              */
char psrclib[45];                   /* Sample JCL library          */
char pload[2];                      /* Perform -SET SYSPARM with   */
                                    /* LOAD option                 */
long int pretcd;                    /* Return code                 */
short int ind_pmember;              /* Indicator variable          */
short int ind_pmodname;             /* Indicator variable          */
short int ind_pmodlib;              /* Indicator variable          */
short int ind_psrclib;              /* Indicator variable          */
short int ind_pload;                /* Indicator variable          */
short int ind_pretcd;               /* Indicator variable          */
/* Temporary table SYSIBM.SYSPARM_SETTINGS columns                 */
long int zrownum;                   /* Sequence number of the      */
                                    /* table row                   */
char zmacro[9];                     /* Macro containing the        */
                                    /* subsystem parameter to be   */
                                    /* modified                    */
char zparam[41];                    /* Name of the subsystem       */
                                    /* parameter to be modified    */
char znew_value[2049];              /* New value of the subsystem  */
                                    /* parameter to be modified    */
/* Result set locators                                             */
volatile SQL TYPE IS RESULT_SET_LOCATOR *rs_loc1;                    
/* Result set row                                                  */
long int rownum;                     /* Sequence number of the     */
                                     /* table row                  */
char type[13];                       /* Type of information found  */
                                     /* in corresponding TEXT col  */
char text[1332];                     /* Message or listing         */
EXEC SQL END DECLARE SECTION;    
/*******************************************************************/
/* Clear temporary table SYSIBM.SYSPARM_SETTINGS                   */
/*******************************************************************/
EXEC SQL DELETE FROM SYSIBM.SYSPARM_SETTINGS;    
/*******************************************************************/
/* Clear temporary table SYSIBM.UPDSYSPARM_MSG                     */
/*******************************************************************/
EXEC SQL DELETE FROM SYSIBM.UPDSYSPARM_MSG;    
/*******************************************************************/
/* Insert the subsystem parameters to be modified into the          */
/* created global temporary table SYSIBM.SYSPARM_SETTINGS          */
/*******************************************************************/
zrownum = 1;    
strcpy(zmacro, "DSN6FAC");    
strcpy(zparam, "IDTHTOIN");    
strcpy(znew_value, "600");    
EXEC SQL INSERT INTO SYSIBM.SYSPARM_SETTINGS                    
          ( ROWNUM, MACRO, PARAMETER, NEW_VALUE)             
      VALUES (:zrownum, :zmacro, :zparam, :znew_value);    
/*******************************************************************/
/* Set procedure input parameters                                  */
/*******************************************************************/
ind_pmember = -1;    
ind_pmodname = -1;    
ind_pmodlib = -1;    
ind_psrclib = -1;    
ind_pload = -1;    
ind_pretcd = -1;    

/*******************************************************************/
/* Call stored procedure SYSPROC.ADMIN_UPDATE_SYSPARM              */
/*******************************************************************/
EXEC SQL CALL SYSPROC.ADMIN_UPDATE_SYSPARM (                         
           :pmember :ind_pmember,                         
           :pmodname :ind_pmodname,                         	
           :pmodlib :ind_pmodlib,                         
           :psrclib :ind_psrclib,                         
           :pload :ind_pload,                         
           :pretcd :ind_pretcd);    
/*******************************************************************/
/* Retrieve result set when the SQLCODE from the call is +446,     */
/* which indicates that result sets were returned                  */
/*******************************************************************/
  if (SQLCODE == +466)               /* Result sets were returned  */
  {      
   /* Establish a link between the result set and its locator      */
   EXEC SQL ASSOCIATE LOCATORS (:rs_loc1)               
       WITH PROCEDURE SYSPROC.ADMIN_UPDATE_SYSPARM;     
   /* Associate a cursor with the result set                       */
   EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :rs_loc1;      
   /* Perform fetches using C1 to retrieve all rows from the       */
   /* result set                                                   */
   EXEC SQL FETCH C1 INTO :rownum, :type, :text;      
   while(SQLCODE==0)      
   {        
    EXEC SQL FETCH C1 INTO :rownum, :type, :text;      
   }    

   EXEC SQL CLOSE C1;
  }    
  return;   
} 

Output

This stored procedure returns the return-code output parameter, which is described in Option descriptions.

In addition to the preceding output, the stored procedure returns one result set that contains successful, warning, or error messages that are generated by the stored procedure. The stored procedure also returns (if applicable) the assembler and link-edit listings, and output from the SET SYSPARM with LOAD option command.

The following table shows the format of the result set that is returned in the created global temporary table SYSIBM.UPDSYSPARM_MSG: