ADMIN_COMMAND_DSN stored procedure

The SYSPROC.ADMIN_COMMAND_DSN stored procedure executes a Start of changeBIND, REBIND, FREE, or DCLGENEnd of change DSN subcommand and returns the output from the DSN subcommand execution.

Environment

Begin general-use programming interface information.

ADMIN_COMMAND_DSN runs in a WLM-established stored procedures address space. TCB=1 is also 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:

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

To execute the DSN subcommand, you must use a privilege set that includes the authorization to execute the DSN subcommand.

Syntax

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

Read syntax diagramSkip visual syntax diagramCALLSYSPROC.ADMIN_COMMAND_DSN(DSN-subcommand,message)

Option descriptions

DSN-subcommand
Specifies the DSN subcommand to be executed. If the DSN subcommand passed to the stored procedure is not Start of changeBIND, REBIND, FREE, or DCLGENEnd of change, an error message is returned. The DSN subcommand is performed using the authorization ID of the user who invoked the stored procedure.

ADMIN_COMMAND_DSN does not support three-part names if a wildcard character is specified in the package name.

This parameter is case sensitive. You must specify DSN-subcommand in uppercase characters.

This is an input parameter of type VARCHAR(32704) and cannot be null.

message
Contains messages if an error occurs during stored procedure execution.

The stored procedure might not return a result set if message is not blank. Even if message is not blank, the stored procedure might return a result set if the error described in message occurred after the stored procedure executed at least one DSN subcommand, and the stored procedure can successfully insert the DSN subcommand output message in the result set and open the result set cursor.

A blank message does not mean that the DSN subcommand completed successfully. The calling application must read the result set to determine if the DSN subcommand was successful or not.

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

Example

The following C language sample shows how to invoke ADMIN_COMMAND_DSN:

 #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_COMMAND_DSN parameters                           */
   
   char        subcmd[32705];          /* BIND, REBIND, FREE, or     */
                                       /* DCLGEN DSN subcommand      */
   char        errmsg[1332];           /* Error message              */

   /* 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        text[256];              /* DSN subcommand output row  */
   EXEC SQL END DECLARE SECTION;

   /******************************************************************/
   /* Set input parameter to execute a REBIND PLAN DSN subcommand    */
   /******************************************************************/
   strcpy(subcmd, "REBIND PLAN (DSNACCOB) FLAG(W)");

   /******************************************************************/
   /* Call stored procedure SYSPROC.ADMIN_COMMAND_DSN                */
   /******************************************************************/
   EXEC SQL CALL SYSPROC.ADMIN_COMMAND_DSN (:subcmd, :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_COMMAND_DSN;

     /* 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, :text;
     while(SQLCODE==0)
     {
       EXEC SQL FETCH C1 INTO :rownum, :text;
     }

     EXEC SQL CLOSE C1;
   }

   return;
 }

Output

This stored procedure returns an error message, message, if an error occurs.

The stored procedure returns one result set that contains the DSN subcommand output messages.

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

Table 1. Result set row for ADMIN_COMMAND_DSN result set
Column name Data type Contents
ROWNUM INTEGER Sequence number of the table row, from 1 to n
TEXT VARCHAR(255) DSN subcommand output message line

End general-use programming interface information.