ADMIN_COMMAND_DSN stored procedure
The SYSPROC.ADMIN_COMMAND_DSN stored procedure executes a 
BIND, REBIND, FREE, or DCLGEN
DSN subcommand and returns the output from the DSN subcommand execution.
Environment
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:
Option descriptions
- DSN-subcommand
- Specifies the DSN subcommand to be executed. If the DSN subcommand passed to the stored procedure is not BIND, REBIND, FREE, or DCLGEN, 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:
| 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 |
