ADMIN_UTL_SORT stored procedure

Example

The following C language sample shows how to invoke ADMIN_UTL_SORT:

 #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_SORT parameters                              */
   short int maxparallel;              /* Max parallel               */
   short int ind_maxparallel;          /* Indicator variable         */
   short int maxperjob;                /* Max per job                */
   short int ind_maxperjob;            /* Indicator variable         */
   char optimizeworkload[9];           /* Optimize workload          */
   short int ind_optimizeworkload;     /* Indicator variable         */
   char batchexecution[9];             /* Batch execution            */
   short int ind_batchexecution;       /* Indicator variable         */
   long int numberofobjects;           /* Number of objects          */
   short int ind_numberofobjects;      /* Indicator variable         */
   short int parallelunits;            /* Parallel units             */
   short int ind_parallelunits;        /* Indicator variable         */
   long int maxobjects;                /* Maximum objects per        */
                                       /* parallel unit              */
   short int ind_maxobjects;           /* Indicator variable         */
   long int maxseqs;                   /* Maximum jobs per unit      */
   short int ind_maxseqs;              /* 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_SORT_OBJ columns                */
   long int objectid;                  /* Object 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 utname[21];                    /* Utility name               */

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

   /* Result set row                                                 */
   long int resobjectid;               /* Object id                  */
   short int unit;                     /* Execution unit value       */
   long int unitseq;                   /* Job seq within exec unit   */
   long int unitseqpos;                /* Pos within exec unit or    */
                                       /* step within job            */
   char exclusive[2];                  /* Exclusive execution flag   */
   EXEC SQL END DECLARE SECTION;

   /******************************************************************/
   /* Set up the objects to be sorted                                */
   /******************************************************************/
   long int objid_array[4] = {0, 1, 2, 3};
   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 utname_array[4][21]= {"RUNSTATS TABLESPACE",
                              "RUNSTATS TABLESPACE",
                              "RUNSTATS TABLESPACE",
                              "RUNSTATS TABLESPACE"};

   int i = 0;                          /* Loop counter               */

   /******************************************************************/
   /* 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;
   ind_maxperjob = -1;
   strcpy(optimizeworkload, "YES");
   ind_optimizeworkload = 0;
   strcpy(batchexecution, "NO");
   ind_batchexecution = 0;
   numberofobjects = 4;
   ind_numberofobjects = 0;

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

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

   /******************************************************************/
   /* Call stored procedure SYSPROC.ADMIN_UTL_SORT                   */
   /******************************************************************/
   EXEC SQL CALL SYSPROC.ADMIN_UTL_SORT
                        (:maxparallel       :ind_maxparallel,
                         :maxperjob         :ind_maxperjob,
                         :optimizeworkload  :ind_optimizeworkload,
                         :batchexecution    :ind_batchexecution,
                         :numberofobjects   :ind_numberofobjects,
                         :parallelunits     :ind_parallelunits,
                         :maxobjects        :ind_maxobjects,
                         :maxseqs           :ind_maxseqs,
                         :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_UTL_SORT;

     /* 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 :resobjectid, :unit,
                            :unitseq, :unitseqpos, :exclusive;
     while(SQLCODE==0)
     {
       EXEC SQL FETCH C1 INTO :resobjectid, :unit,
                              :unitseq, :unitseqpos, :exclusive;
     }

     EXEC SQL CLOSE C1;
   }

   return(retcd);
 }