Mainframe Utility: INDEXCOL

Return to Mainframe Utilities Page

Module


/**********************************************************************
/* UTILITY: INDEXCOL                                                  *
/* AUTHOR: DAVID LEIGH                                                *
/* FUNCTION: THIS UTILITY PROMPTS FOR INPUT OF A DB2 INDEX NAME AND AN*
/*           ASSOCIATED CREATOR ID.  I THEN DETERMINES THE LENGTH OF  *
/*           THE INDEX.                                               *
/**********************************************************************
PROC 0 CREATOR(USSTRD00) +
       UTILITY(INDEXCOL) +
       HELP +
       DEBUG

/*** CHECK THE DEBUG SWITCH ***/
IF &DEBUG = DEBUG THEN +
    CONTROL MSG LIST CONLIST SYMLIST NOFLUSH NOPROMPT ASIS
ELSE +
    CONTROL NOMSG NOLIST NOFLUSH NOPROMPT ASIS

IF &HELP = HELP THEN GOTO HELPSEC
                                                                              02
/**********************************************************************
/* GET THE USER'S FULL NAME                                           *
/**********************************************************************
SET SYSOUTTRAP = 1000
ACF
LIST *
END
SET SYSOUTTRAP = 0
SET SYSDVAL = &STR(&SYSNSUB(1,&SYSOUTLINE1))
READDVAL A B NAME1 NAME2 NAME3 NAME4 NAME5
SET FULLNAME = &STR(&NAME1 &NAME2 &NAME3 &NAME4 &NAME5)
                                                                              02
/**********************************************************************
/* ESTABLISH SOME PROCESSING VARIABLES                                *
/**********************************************************************
SET DB2FILE = &STR(&SYSUID..TEMP.INDEXCOL.WORK)
SET SCANALL = ALL
SET ZTDMARK = &STR(**** ALL ROWS SHOWN ****)
SET LP = &STR((
SET RP = &STR()

/**********************************************************************
/* GET A LIST OF INDEXES FOR THE CURRENT CREATOR                      *
/**********************************************************************
INDEX_LIST: +
SET SCREATOR = &STR(&CREATOR)
SET ZEDSMSG =
SET ZEDLMSG = &STR(*** EXTRACTING A LIST OF INDEXES FOR CREATOR +
                   "&CREATOR" FROM DB2 ***)
ISPEXEC CONTROL DISPLAY LOCK
ISPEXEC DISPLAY PANEL(MSGPANEL)

FREE DD(SYSREC00 SYSIN SYSPRINT SYSPUNCH)
IF &DEBUG = DEBUG THEN +
    DO
        ALLOC DD(SYSPRINT) DA(*)
        ALLOC DD(SYSPUNCH) DA(*)
    END
ELSE +
    DO
        ALLOC DD(SYSPRINT) DUMMY
        ALLOC DD(SYSPUNCH) DUMMY
    END

DELETE '&DB2FILE'
ALLOC DD(SYSREC00) DSN('&DB2FILE') +
      NEW CATALOG +
      UNIT(SYSDA) VOLUME(WRK$$$) +
      SPACE(1,1) TRACKS RELEASE DSORG(PS)

ALLOC DD(SYSIN) +
      NEW +
      UNIT(SYSDA) VOLUME(WRK$$$) +
      SPACE(1,1) TRACKS RELEASE +
      RECFM(F B) LRECL(80) BLKSIZE(23440) DSORG(PS)

OPENFILE SYSIN OUTPUT
SET SYSIN = &STR(SYSADM2.INDEXCOL WHERE C LIKE '&CREATOR')
PUTFILE SYSIN
CLOSFILE SYSIN

DSN SYSTEM(DSNT)
    RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
         LIB('SYS4.DSN.DSNT.RUNLIB.LOAD')
END

FREE DD(SYSREC00 SYSIN SYSPRINT SYSPUNCH)

SET ZEDSMSG =
SET ZEDLMSG = &STR(*** LOADING THE LIST INTO AN ISPF TABLE ***)
ISPEXEC CONTROL DISPLAY LOCK
ISPEXEC DISPLAY PANEL(MSGPANEL)
ISPEXEC EDIT DATASET('&DB2FILE') MACRO(INDEXCM1)

/**********************************************************************
/* DISPLAY THE PANEL IF NO INPUT WAS PASSED                           *
/**********************************************************************
REDISPLAY: +
ISPEXEC TBDISPL TEMPNDXC PANEL(INDEXCOL)
IF &LASTCC > 7 THEN +
    GOTO FINISH
ELSE +
    IF &STR(&SCREATOR) ¬= &STR(&CREATOR) THEN GOTO INDEX_LIST

/**********************************************************************
/* PROCESS ANY SELECTED ROWS                                          *
/**********************************************************************
IF &ZTDSELS ¬= &STR(0000) THEN +
    DO
        ISPEXEC CONTROL DISPLAY SAVE
        DO WHILE &ZTDSELS ¬= &STR(0000)
            SELECT (&STR(&SEL))
                /************ EXCLUDE THIS LINE ************/
                WHEN (X) DO
                    SET SEL = X
                    SET SCANALL = SCAN
                    ISPEXEC TBMOD TEMPNDXC
                    ISPEXEC TBVCLEAR TEMPNDXC
                    SET SEL = X
                    ISPEXEC TBSARG TEMPNDXC NAMECOND(SEL NE)
                    SET ZTDMARK = &STR(**** ROW(S) EXCLUDED ****)
                END
                OTHERWISE DO
                    SET SEL =
                    ISPEXEC TBMOD TEMPNDXC
                    SET ZEDSMSG =
                    SET ZEDLMSG = &STR("X" IS THE ONLY +
                                       VALID LINE COMMAND)
                    ISPEXEC SETMSG MSG(UTLZ001)
                END
            END
NEXT_LINE:  IF &ZTDSELS > &STR(0001) THEN +
                DO
                    ISPEXEC CONTROL DISPLAY RESTORE
                    ISPEXEC TBDISPL TEMPNDXC
                    ISPEXEC CONTROL DISPLAY SAVE
                END
            ELSE +
                DO
                    SET ZTDSELS = &STR(0000)
                    ISPEXEC CONTROL DISPLAY RESTORE
                    IF &STR(&ZCMD) >     THEN GOTO SCROLL
                END
        END
    END

/******************************/
/* PROCESS "PRIMARY" COMMANDS */
/******************************/
IF &STR(&ZCMD) =      THEN GOTO SCROLL
SET SYSDVAL = &STR(&ZCMD)
READDVAL ZCMD OPT1 OPT2 OPT3 OPT4 OPT5 OPT6 OPT7 OPT8 OPT9 OPT10

SELECT (&ZCMD)
    WHEN (DEBUG) DO
        IF &OPT1 = &STR(ON) THEN +
            DO
                SET DEBUG = DEBUG
                SET ZEDSMSG = &STR(DEBUG ON)
                SET ZEDLMSG = &STR(THE DEBUG SWITCH HAS BEEN SET ON)
                ISPEXEC SETMSG MSG(UTLZ000)
                CONTROL MSG LIST CONLIST SYMLIST NOFLUSH NOPROMPT ASIS
                SET ZCMD =
                GOTO SCROLL
            END
        IF &OPT1 = &STR(OFF) THEN +
            DO
                SET DEBUG = DEBUG
                SET ZEDSMSG = &STR(DEBUG OFF)
                SET ZEDLMSG = &STR(THE DEBUG SWITCH HAS BEEN SET OFF)
                ISPEXEC SETMSG MSG(UTLZ000)
                CONTROL NOMSG NOLIST NOFLUSH NOPROMPT ASIS
                SET ZCMD =
                GOTO SCROLL
            END
        END
    WHEN (E ¦ ED ¦ EDI ¦ EDIT) ISPEXEC EDIT DATASET('&DB2FILE')
    WHEN (F ¦ FI ¦ FIN ¦ FIND) DO
        SET ZEDSMSG = &STR("&OPT1" NOT FOUND)
        SET ZEDLMSG = &STR(THE STRING "&OPT1" WAS NOT FOUND)
        ISPEXEC SETMSG MSG(UTLZ001)
        ISPEXEC TBTOP TEMPNDXC
        ISPEXEC TBSKIP TEMPNDXC
        DO WHILE &LASTCC = 0
            IF &SYSINDEX(&SYSCAPS(&STR(&OPT1)),&STR(&INDEX)) > 0 +
                THEN DO
                    SET ZEDSMSG = &STR("&OPT1" FOUND)
                    SET ZEDLMSG = &STR(THE STRING "&OPT1" WAS FOUND)
                    ISPEXEC SETMSG MSG(UTLZ000)
                    IF &STR(&SEL) = X THEN +
                        DO
                            SET SEL =
                            ISPEXEC TBMOD TEMPNDXC
                        END
                    GOTO FIND_CONTINUE
                END
            ISPEXEC TBSKIP TEMPNDXC
        END
FIND_CONTINUE: +
    END
    WHEN (R ¦ RE ¦ RES ¦ RESE ¦ RESET) DO
        SET SCANALL = ALL
        SET ZEDSMSG =
        SET ZEDLMSG = &STR(ALL ROWS SHOWN)
        ISPEXEC SETMSG MSG(UTLZ000)
        ISPEXEC TBTOP TEMPNDXC
        ISPEXEC TBVCLEAR TEMPNDXC
        SET SEL = X
        ISPEXEC TBSARG TEMPNDXC NAMECOND(SEL EQ)
        ISPEXEC TBSCAN TEMPNDXC
        DO WHILE &LASTCC = 0
            SET SEL =
            ISPEXEC TBMOD TEMPNDXC
            SET SEL = X
            ISPEXEC TBSCAN TEMPNDXC
        END
        ISPEXEC TBTOP TEMPNDXC
        ISPEXEC TBVCLEAR TEMPNDXC
        SET ZTDMARK = &STR(**** ALL ROWS SHOWN ****)
        GOTO SCROLL
    END
    OTHERWISE DO
        SET ZEDLMSG = &STR(VALID PRIMARY COMMANDS:  +
                           "EDIT", +
                           "FIND", +
                           "DEBUG ON¦OFF", +
                           "RESET")
        ISPEXEC SETMSG MSG(UTLZ001)
    END
END

/*****************************/
/* MAINTAIN THE TOP OF TABLE */
/*****************************/
SCROLL: +
IF &STR(&ZCMD) =     THEN +
    DO
        ISPEXEC TBTOP TEMPNDXC
        ISPEXEC TBSKIP TEMPNDXC NUMBER(&ZTDTOP)
        ISPEXEC VGET (ZVERB ZSCROLLN)
        IF &ZVERB = &STR(UP) THEN +
            ISPEXEC TBSKIP TEMPNDXC NUMBER(-&ZSCROLLN)
        IF &ZVERB = &STR(DOWN) THEN +
            ISPEXEC TBSKIP TEMPNDXC NUMBER(&ZSCROLLN)
    END

GOTO REDISPLAY

/**********************************************************************
/* GET OUT NOW                                                        *
/**********************************************************************
FINISH: EXIT
            


Documentation


 INDEXCOL accesses the DB2 catalog to determine all the columns in all the
 indexes for a given creator id.  It then adds up the bytes in each index and
 loads the results into an ISPF table and displays them in a scrolling panel.
 At the time of this writing no tool gives this information though it can be
 useful when doing catalog statistics updates for EXPLAIN "what-iffing" with
 Candle's !DB-Explain product.

 Syntax:

 COMMAND ===> tso indexcol {creator(creatorid)}

 The default creator id is "USSTRD00" (UNISTAR development).

 When the list is presented on your screen you have a few primary commands and
 one line command to choose from.

 Line command:

 "X" - This command on any line will exclude that line from the current display.
 This may be useful if you are wanting to keep a display of multiple index
 lengths on one screen but there are many screens worth of lines which you do
 not want to see.

 Primary Commands:

 "DEBUG ON³OFF" - This dynamically turns on or off debugging messages while
                  INDEXCOL is executing.

 "EDIT" - This command will bring you into an edit session on the work file
          which INDEXCOL created while preparing the scrollable list screen.  It
          contains all the individual columns, their length, and sequence, and
          total length in one big file.  This may be useful to see the details
          for a given index.

 "FIND xxx" - This command will position your display to the first line where a
              given string is found.  It is a good tool to position you to a
              particular set of indexes for a particular table since the index
              names all begin with the subject area prefix.

 "RESET" - This command will reset all the excluded lines (if there are any) so
           that all lines are displayed again.
            


Leave a Reply

Your email address will not be published. Required fields are marked *