ADMIN_INFO_SYSPARM stored procedure

The SYSPROC.ADMIN_INFO_SYSPARM stored procedure returns the system parameters, application defaults module, and IRLM parameters of a connected Db2 subsystem, or member of its data sharing group.

Environment

Begin general-use programming interface information.

ADMIN_INFO_SYSPARM runs in a WLM-established stored procedures address space, where NUMTCB=1 is required.

Authorization

To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges on each package that the stored procedure uses:
  • The EXECUTE privilege on the package for DSNADMIZ
  • Ownership of the package
  • PACKADM authority for the package collection
  • SYSADM authority
The user who calls this stored procedure must have MONITOR1 privilege.

Syntax

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

Read syntax diagramSkip visual syntax diagramCALLSYSPROC.ADMIN_INFO_SYSPARM(db2-memberNULL,return-code,message)

Option descriptions

db2-member
Specifies the name of the Db2 data sharing group member that you want to get the system parameters, DSNHDECP or a user-specified application defaults module, and IRLM parameters from.

Specify NULL for this parameter if you are retrieving the system parameters, DSNHDECP values, and IRLM parameters from the connected Db2 subsystem.

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

return-code
Provides the return code from the stored procedure. The following values are possible:
0
The call completed successfully.
12
The call did not complete successfully. The message output parameter contains messages that describe the IFI error or SQL error that is encountered by the stored procedure.

This is an output parameter of type INTEGER.

message
Contains messages that describe the IFI error or SQL error that was encountered by the stored procedure. If an error did not occur, a message is not returned.

This is an output parameter of type VARCHAR(1331).

Example

The following C language sample shows how to invoke ADMIN_INFO_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_INFO_SYSPARM parameters                          */
   char          db2_member[9];        /* Data sharing group member  */
   short int     ind_db2_member;       /* Indicator variable         */
   long int      retcd;                /* Return code                */
   short int     ind_retcd;            /* Indicator variable         */
   char          errmsg[1332];         /* Error message              */
   short int     ind_errmsg;           /* Indicator variable         */
   /* Result set locators                                            */
   volatile SQL TYPE IS RESULT_SET_LOCATOR *rs_loc1;
   /* Result set row                                                 */
   long int      rownum;               /* Sequence number of the     */
                                       /* table row (1,...,n)        */
   char          macro[9];             /* Macro that contains the    */
                                       /* system parameter, or       */
                                       /* DSNHDECP parameter, or the */
                                       /* name of the IRLM procedure */
                                       /* that z/OS invokes if IRLM  */
                                       /* is automatically started   */
                                       /* by DB2                     */
   char          parameter[41];        /* Name of the system         */
                                       /* parameter, DSNHDECP        */
                                       /* parameter, or IRLM         */
                                       /* parameter                  */
   char          install_panel[9];     /* Name of the installation   */
                                       /* panel where the parameter  */
                                       /* value can be changed when  */
                                       /* installing or migrating DB2*/
   short int     ind_install_panel;    /* Indicator variable         */
   char          install_field[41];    /* Name of the parameter on   */
                                       /* the installation panel     */
   short int     ind_install_field;    /* Indicator variable         */
   char          install_location[13]; /* Location of the parameter  */
                                       /* on the installation panel  */
   short int     ind_install_location; /* Indicator variable         */
   char          value[2049];          /* Value of the parameter     */
   char          additional_info[201]; /* Reserved for future use    */
   short int     ind_additional_info;  /* Indicator variable         */

   EXEC SQL END DECLARE SECTION;
   /******************************************************************/
   /* Set the db2_member indicator variable to -1 to get the DB2     */
   /* subsystem parameters, DSNHDECP values, and IRLM parameters of  */
   /* the connected DB2 subsystem.                                   */
   /******************************************************************/
   ind_db2_member = -1;
   /******************************************************************/
   /* Call stored procedure SYSPROC.ADMIN_INFO_SYSPARM               */
   /******************************************************************/
   EXEC SQL CALL SYSPROC.ADMIN_INFO_SYSPARM
                        (:db2_member :ind_db2_member,
                         :retcd      :ind_retcd,
                         :errmsg     :ind_errmsg);
   /******************************************************************/
   /* 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_INFO_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, :macro, :parameter,
                       :install_panel          :ind_install_panel,
                       :install_field          :ind_install_field,
                       :install_location :ind_install_location,
                       :value,
                       :additional_info        :ind_additional_info;
     while(SQLCODE==0)
     {
       EXEC SQL FETCH C1
                  INTO :rownum, :macro, :parameter,
                       :install_panel          :ind_install_panel,
                       :install_field          :ind_install_field,
                       :install_location :ind_install_location,
                       :value,
                       :additional_info        :ind_additional_info;
     }

     EXEC SQL CLOSE C1;
   }
   return(retcd);
 }

Output

This stored procedure returns the following output parameters, which are described in Option descriptions:

  • return-code
  • message

In addition to the preceding output, the stored procedure returns one result set that contains the parameter settings.

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

Table 1. Result set row for ADMIN_INFO_SYSPARM result set
Column name Data type Contents
ROWNUM
INTEGER
NOT NULL
Sequence number of the table row, from 1 to n.
MACRO
VARCHAR(8)
NOT NULL
Macro that contains the system parameter, the dsnhdecp parameter, or the name of the IRLM procedure that z/OS® invokes if IRLM is started automatically by Db2.
PARAMETER
VARCHAR(40)
NOT NULL
Name of the system parameter, dsnhdecp parameter, or IRLM parameter.
INSTALL_PANEL VARCHAR(8) Name of the installation panel where the parameter value can be changed when installing or migrating Db2.
INSTALL_FIELD VARCHAR(40) Name of the parameter on the installation panel.
INSTALL_LOCATION VARCHAR(12) Location of the parameter on the installation panel.
VALUE
VARCHAR(2048)
NOT NULL
The value of the parameter.1
ADDITIONAL_INFO VARCHAR(200) Specifies whether a parameter can be updated online. If the value is null, the stored procedure could not retrieve the information for the parameter.
  1. See Parameter values that are returned in bit format for information on how to interpret entries in the VALUE column that are in bit format.
Start of change

Parameter values that are returned in bit format

For certain subsystem parameters, the content of the VALUE column in the returned result set is a bit string.

For subsystem parameters AUDITST, MON, and SMFACCT, the 32 bits of the string correspond to the 32 trace classes. The bit string has the following values:

  • If the subsystem parameter value is NO, all bits are 0.
  • If the subsystem parameter value is YES, the first bit is 1, and all other bits are 0.
  • If the subsystem parameter value is *, all bits are 1.
  • Otherwise, the bits that are 1 correspond to the trace classes that are on. For example, if the AUDITST subsystem parameter specification is AUDITST=(1,3,5), the first, third and fifth bits of the bit string are 1, and all other bits are 0:
    10101000000000000000000000000000

For subsystem parameters ROUTCDE and ARCWRTC, the 16 bits of the string correspond to the 16 route codes. The bit string has the following values:

  • The bits that are 1 correspond to the route codes that are on. For example, if the ROUTCDE subsystem parameter specification is ROUTCDE=(1,3), the first and third bits of the bit string are 1, and all other bits are 0:
    1010000000000000

For subsystem parameter SMFSTAT, the 32 bits of the string correspond to the 32 trace classes. The bit string has the following values:

  • If the subsystem parameter value is NO, all bits are 0.
  • If the subsystem parameter value is YES, the first, third, fourth, fifth, and sixth bits are 1, and all other bits are 0.
  • If the subsystem parameter value is *, all bits are 1.
  • Otherwise, the bits that are 1 correspond to the trace classes that are on. For example, if the SMFSTAT subsystem parameter specification is SMFSTAT=(1,3,5), the first, third and fifth bits of the bit string are 1, and all other bits are 0:
    10101000000000000000000000000000

For subsystem parameter TRACSTR, the 32 bits of the string correspond to the 32 trace classes. The bit string has the following values:

  • If the subsystem parameter value is NO, all bits are 0.
  • If the subsystem parameter value is YES, the first, second, and third bits are 1, and all other bits are 0.
  • If the subsystem parameter value is *, all bits are 1.
  • Otherwise, the bits that are 1 correspond to the trace classes that are on. For example, if the TRACSTR subsystem parameter specification is TRACSTR=(1,3,5), the first, third and fifth bits of the bit string are 1, and all other bits are 0:
    10101000000000000000000000000000
End of change

End general-use programming interface information.