ADMIN_UTL_SCHEDULE stored procedure

Example

The following C language sample shows how to invoke ADMIN_UTL_SCHEDULE:

 #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_UTL_SCHEDULE parameters                          */
   short int maxparallel;              /* Max parallel               */
   short int ind_maxparallel;          /* Indicator variable         */
   char optimizeworkload[9];           /* Optimize workload          */
   short int ind_optimizeworkload;     /* Indicator variable         */
   char stoponcond[9];                 /* Stop on condition          */
   short int ind_stoponcond;           /* Indicator variable         */
   char utilityidstem[9];              /* Utility ID stem            */
   short int ind_utilityidstem;        /* Indicator variable         */
   float shutdownduration;             /* Shutdown duration          */
   short int ind_shutdownduration;     /* Indicator variable         */
   long int numberofobjects;           /* Number of objects          */
   short int ind_numberofobjects;      /* Indicator variable         */
   long int utilitiesexec;             /* Utilities executed         */
   short int ind_utilitiesexec;        /* Indicator variable         */
   long int highestretcd;              /* DSNUTILU highest ret code  */
   short int ind_highestretcd;         /* Indicator variable         */
   long int paralleltasks;             /* Parallel tasks             */
   short int ind_paralleltasks;        /* Indicator variable         */
   long int retcd;                     /* Return code                */
   short int ind_retcd;                /* Indicator variable         */
   char errmsg[1332];                  /* Error message              */
   short int ind_errmsg;               /* Indicator variable         */

   /* Temporary table SYSIBM.UTILITY_OBJECTS columns                 */
   long int objectid;                  /* Object id                  */
   long int stmtid;                    /* Statement ID               */
   char type[11];                      /* Object type (e.g. "INDEX") */
   char qualifier[129];                /* Object qualifier           */
   short int ind_qualifier;            /* Object qualifier ind. var. */
   char name[129];                     /* Object name (qual. or unq.)*/
   short int part;                     /* Optional partition         */
   short int ind_part;                 /* Partition indicator var    */
   char restart[9];                    /* DSNUTILU restart parm      */
   char utname[21];                    /* Utility name               */

   /* Temporary table SYSIBM.UTILITY_STMT columns                    */
   long int stmtid2;                   /* Statement ID               */
   long int stmtseq;                   /* Utility stmt sequence      */
   char utstmt[4001];                  /* Utility statement          */

   /* Result set locators                                            */
   volatile SQL TYPE IS RESULT_SET_LOCATOR *rs_loc1;
   volatile SQL TYPE IS RESULT_SET_LOCATOR *rs_loc2;

   /* First result set row                                           */
   long int objectid1;                 /* Object id                  */
   long int textseq;                   /* Object utility output seq   */
   char text[255];                     /* Object utility output      */

   /* Second result set row                                          */
   long int objectid2;                 /* Object id                  */
   long int utilretcd;                 /* DSNUTILU return code       */
   EXEC SQL END DECLARE SECTION;

   /******************************************************************/
   /* Set up the objects to be sorted                                */
   /******************************************************************/
   long int objid_array[4] = {1, 2, 3, 4};
   long int stmtid_array[4] = {1, 1, 1, 1};
   char type_array[4][11] = {"TABLESPACE", "TABLESPACE",
                             "TABLESPACE", "TABLESPACE"};
   char qual_array[4][129] = {"QUAL01", "QUAL01",
                              "QUAL01", "QUAL01"};
   char name_array[4][129] = {"TBSP01", "TBSP02",
                              "TBSP03", "TBSP04"};
   short int part_array[4] = {0, 0, 0, 0};
   char restart_array[4][9] = {"NO", "NO",
                               "NO", "NO"};
   char utname_array[4][21]= {"RUNSTATS TABLESPACE",
                              "RUNSTATS TABLESPACE",
                              "RUNSTATS TABLESPACE",
                              "RUNSTATS TABLESPACE"};

   int i = 0;                          /* Loop counter               */

   /******************************************************************/
   /* Set up utility statement                                       */
   /******************************************************************/
   stmtid2 = 1;
   stmtseq = 1;
   strcpy(utstmt,
    "RUNSTATS TABLESPACE &OBJECT. TABLE(ALL) SAMPLE 25 INDEX(ALL)");

   /******************************************************************/
   /* Assign values to input parameters                              */
   /* Set the indicator variables to 0 for non-null input parameters */
   /* Set the indicator variables to -1 for null input parameters    */
   /******************************************************************/
   maxparallel = 2;
   ind_maxparallel = 0;
   strcpy(optimizeworkload, "YES");
   ind_optimizeworkload = 0;
   strcpy(stoponcond, "AUTHORIZ");
   ind_stoponcond = 0;
   strcpy(utilityidstem, "DSNADMUM");
   ind_utilityidstem = 0;
   numberofobjects = 4;
   ind_numberofobjects = 0;
   ind_shutdownduration = -1;

   /******************************************************************/
   /* Clear temporary table SYSIBM.UTILITY_OBJECTS                   */
   /******************************************************************/
   EXEC SQL DELETE FROM SYSIBM.UTILITY_OBJECTS;

   /******************************************************************/
   /* Insert the objects into the temporary table                    */
   /* SYSIBM.UTILITY_OBJECTS                                         */
   /******************************************************************/
   for (i = 0; i < 4; i++)
   {
     objectid = objid_array[i];
     stmtid = stmtid_array[i];
     strcpy(type, type_array[i]);
     strcpy(qualifier, qual_array[i]);
     strcpy(name, name_array[i]);
     part = part_array[i];
     strcpy(restart, restart_array[i]);
     strcpy(utname, utname_array[i]);
     EXEC SQL INSERT INTO SYSIBM.UTILITY_OBJECTS
                     (OBJECTID, STMTID, TYPE,
                      QUALIFIER, NAME, PART,
                      RESTART, UTILITY_NAME)
              VALUES (:objectid, :stmtid, :type,
                      :qualifier, :name, :part,
                      :restart, :utname);
   };

   /******************************************************************/
   /* Clear temporary table SYSIBM.UTILITY_STMT                      */
   /******************************************************************/
   EXEC SQL DELETE FROM SYSIBM.UTILITY_STMT;

   /******************************************************************/
   /* Insert the utility statement into the temporary table          */
   /* SYSIBM.UTILITY_STMT                                            */
   /******************************************************************/
   EXEC SQL INSERT INTO SYSIBM.UTILITY_STMT
                   (STMTID, STMTSEQ, UTSTMT)
            VALUES (:stmtid2, :stmtseq, :utstmt);

   /******************************************************************/
   /* Call stored procedure SYSPROC.ADMIN_UTL_SCHEDULE               */
   /******************************************************************/
   EXEC SQL CALL SYSPROC.ADMIN_UTL_SCHEDULE
                        (:maxparallel       :ind_maxparallel,
                         :optimizeworkload  :ind_optimizeworkload,
                         :stoponcond        :ind_stoponcond,
                         :utilityidstem     :ind_utilityidstem,
                         :shutdownduration  :ind_shutdownduration,
                         :numberofobjects   :ind_numberofobjects,
                         :utilitiesexec     :ind_utilitiesexec,
                         :highestretcd      :ind_highestretcd,
                         :paralleltasks     :ind_paralleltasks,
                         :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, :rs_loc2)
              WITH PROCEDURE SYSPROC.ADMIN_UTL_SCHEDULE;

     /* Associate a cursor with the first result set                 */
     EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :rs_loc1;

     /* Associate a cursor with the second result set                */
     EXEC SQL ALLOCATE C2 CURSOR FOR RESULT SET :rs_loc2;

     /* Perform fetches using C1 to retrieve all rows from the       */
     /* first result set                                             */
     EXEC SQL FETCH C1 INTO :objectid1, :textseq, :text;
     while(SQLCODE==0)
     {
       EXEC SQL FETCH C1 INTO :objectid1, :textseq, :text;
     }

     EXEC SQL CLOSE C1;

     /* Perform fetches using C2 to retrieve all rows from the       */
     /* second result set                                            */
     EXEC SQL FETCH C2 INTO :objectid2, :utilretcd;
     while(SQLCODE==0)
     {
       EXEC SQL FETCH C2 INTO :objectid2, :utilretcd;
     }

     EXEC SQL CLOSE C2;
   }

   return(retcd);
 }