Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Catalog Manager for Db2 13.1.

Testing stored procedures

Use the Call Stored Procedure panel to invoke procedures you need to test. You can use the panel to enter data for all the IN (P) and INOUT (B) parameters before invoking the stored procedure. You can also use the panel to select commit and output options. 

To call a stored procedure

  1. Generate a list of stored procedures (NP or PR).
    For information, see Generating-lists-in-Catalog-Manager.

  2. In the C column, type CALL next to the stored procedure you want to invoke and press Enter. 
    The Call Stored Procedure panel is displayed.

                                Call Stored Procedure                    1 to 1 of 1
     Command ===>                                                  Scroll ===> PAGE
                                                                                   
    Call Procedure . . . . . .  Y         Y to call procedure                      
    Commit . . . . . . . . . .  R         C-Commit, R-Rollback, N-None             
    Output format. . . . . . .  C         C/R C-Column format, R-Row format        
                                                                                   
    Schema . . . . . . . . . .  SYSPROC                                            
     Name . . . . . . . . . . .  ADMIN_INFO_SYSPARM                                 
                                                                                   
                              Parm      Parm                                        
     Parm name            P/B type      len    Parm data                            
    -------------------------------------------------------------------------------
     DB2_MEMBER            P  VARCHAR   8                                           
    ******************************* Bottom of data ********************************
  3. In the Call Procedure field, type Y to invoke the stored procedure.

  4. In the Commit field, type one of the following values:

    Value

    Description

    C

    The testing process performs a COMMIT when the stored procedure returns an SQLCODE 0. If the stored procedure returns an error, the testing process performs a ROLLBACK.

    R

    The testing process performs a ROLLBACK after the stored procedure finishes executing.

    N

    The testing process performs neither a COMMIT or ROLLBACK after the stored procedure finishes executing.

  5. In the Output format field, type one of the following values to select the order in which the results of the stored procedure are written to the SQL output data set:

    Value

    Description

    C

    Column format writes all data for a row on a single line with headings above each column of data

    R

    Row format writes each column of data on a new line with headings to the left of the data

    The Output format field defaults to the value entered on the SQL Select and Call panel. For more information, see Setting-SQL-SELECT-and-Call-options.

  6. In the parameter fields listed at the bottom of the panel, enter appropriate values for each parameter. 
  7. Press Enter.
    The results are written to the SQL output data set and displayed in a ISPF Browse panel.

Result

You can format the results of the called stored procedure in columns or rows. Regardless of the output format selected on the Call Stored Procedure panel the output of the parameters is always in Row format. If the called stored procedure returns an error, the SQL Error Message panel displays the details of the SQLCODE returned before displaying the results of the stored procedure.

Click here for an example of the Column output format.
 BROWSE    MVSSXS1.BMCCAT.SQL                       Line 0000000000 Col 001 080
 Command ===>                                                  Scroll ===> PAGE
********************************* Top of Data **********************************
Procedure Input Parameters                                                      
  1 DB2_MEMBER                          (NULL)                                  
  2 RETURN_CODE                         (NULL)                                  
  3 MSG                                 (NULL)                                  
                                                                               
CALL PROCEDURE "SYSPROC".ADMIN_INFO_SYSPARM SQLCODE=466 SQLSTATE=0100C          
                                                                               
Procedure Result Parameters                                                     
  1 DB2_MEMBER                          (NULL)                                  
  2 RETURN_CODE                         '0'                                     
  3 MSG                                 (NULL)                                  
                                                                               
DESCRIBE PROCEDURE "SYSPROC".ADMIN_INFO_SYSPARM SQLCODE=0                       
PROCEDURE "SYSPROC".ADMIN_INFO_SYSPARM RETURNED 1 RESULT SET(S)                 
                                                                               
RESULT SET 1 for cursor DB2_SYSPARM_CSR                                         
DESCRIBE PROCEDURE "SYSPROC".ADMIN_INFO_SYSPARM RESULT SET 1 SQLCODE=0          
                                                                               
RESULT SET 1                                                                    
---------+---------+---------+---------+---------+---------+---------+---------+
    ROWNUM MACRO    PARAMETER                                INSTALL_PANEL INSTA
---------+---------+---------+---------+---------+---------+---------+---------+
         1 DSN6SYSP AUDITST                                  DSNTIPN       AUDIT
         2 DSN6SYSP CONDBAT                                  DSNTIPE       MAX R
         3 DSN6SYSP CTHREAD                                  DSNTIPE       MAX U
         4 DSN6SYSP DLDFREQ                                  DSNTIPL1      LEVEL
         5 DSN6SYSP PCLOSEN                                  DSNTIPL1      RO SW
         6 DSN6SYSP IDBACK                                   DSNTIPE       MAX B
         7 DSN6SYSP IDFORE                                   DSNTIPE       MAX T
         8 DSN6SYSP CHKTYPE                                  DSNTIPL1      CHECK
         9 DSN6SYSP CHKFREQ                                  DSNTIPL1      RECOR
        10 DSN6SYSP CHKLOGR                                  DSNTIPL1      RECOR
        11 DSN6SYSP CHKMINS                                  DSNTIPL1      MINUT
        12 DSN6SYSP MON                                      DSNTIPN       MONIT
        13 DSN6SYSP MONSIZE                                  DSNTIPN       MONIT
Click here for an example of the Row output format.
 BROWSE    MVSSXS1.BMCCAT.SQL                       Line 0000000000 Col 001 080
 Command ===>                                                  Scroll ===> PAGE
********************************* Top of Data **********************************
Procedure Input Parameters                                                      
  1 DB2_MEMBER                          (NULL)                                  
  2 RETURN_CODE                         (NULL)                                  
  3 MSG                                 (NULL)                                  
                                                                               
CALL PROCEDURE "SYSPROC".ADMIN_INFO_SYSPARM SQLCODE=466 SQLSTATE=0100C          
                                                                               
Procedure Result Parameters                                                     
  1 DB2_MEMBER                          (NULL)                                  
  2 RETURN_CODE                         '0'                                     
  3 MSG                                 (NULL)                                  
                                                                               
DESCRIBE PROCEDURE "SYSPROC".ADMIN_INFO_SYSPARM SQLCODE=0                       
PROCEDURE "SYSPROC".ADMIN_INFO_SYSPARM RETURNED 1 RESULT SET(S)                 
                                                                               
RESULT SET 1 for cursor DB2_SYSPARM_CSR                                         
DESCRIBE PROCEDURE "SYSPROC".ADMIN_INFO_SYSPARM RESULT SET 1 SQLCODE=0          
                                                                               
                                                                               
RESULT SET 1 ROW 1                                                              
  1 ROWNUM                              1                                       
  2 MACRO                               'DSN6SYSP'                              
  3 PARAMETER                           'AUDITST'                               
  4 INSTALL_PANEL                       'DSNTIPN'                               
  5 INSTALL_FIELD                       'AUDIT TRACE'                           
  6 INSTALL_LOCATION                    '1'                                     
  7 VALUE                               '00000000000000000000000000000000'      
  8 ADDITIONAL_INFO                     'ONLINE=N'                              
                                                                               
RESULT SET 1 ROW 2                                                              
  1 ROWNUM                              2                                       
  2 MACRO                               'DSN6SYSP'                              
  3 PARAMETER                           'CONDBAT'                               
  4 INSTALL_PANEL                       'DSNTIPE'                               
  5 INSTALL_FIELD                       'MAX REMOTE CONNECTED'                  


Related topics

Setting-SQL-SELECT-and-Call-options

Setting-data-set-options



 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*