Db2

View Only

Expand all | Collapse all

DSNACCOX and Exceptions Part 2

1. DSNACCOX and Exceptions Part 2

Sandeep Jain

Posted Wed March 27, 2024 04:45 AM

DSNACCOX and Exceptions Part 2

Exceptions

DSNACCOX allows use of an exceptions table called EXCEPT_TBL with a creator name (schema) matching the id used by DSNACCOX and the results table, DB2DBA is being used throughout.

Mechanism

The exception table must contain the first three columns, DBNAME, NAME and QUERYTYPE and have a primary index on DBNAME and NAME. Additional columns may and have been added and additional indexing may be used. These additions are for our use and ignored by DSNACCOX.

When DSNACCOX is running, where DBNAME and NAME match a row in EXCEPT_TBL the contents of column QUERYTYPE are copied into the results table RESULTS column INEXCEPTTABLE. The contents of QUERYTYPE (and therefore INEXCEPTTABLE) are free format and for our use only.

Columns

As stated above, the first three columns are required, and all other columns are to suit our requirements.

COLUMN	TYPE	LENGTH	REMARKS
DBNAME	CHAR	8	Required, first column of primary key
NAME	CHAR	8	Required, second column of primary key
QUERYTYPE	CHAR	40	Required, contents are free format data for our use
ENVIRONMENT	CHAR	2	Two characters, e.g. P1, D1, D2, R1 etc.
OBJECTTYPE	CHAR	2	TS or IX
TSTYPE	CHAR	1	TYPE taken from SYSIBM.SYSTABLESPACE
TBTYPE	CHAR	1	TYPE taken from SYSIBM.SYSTABLES
IXTYPE	CHAR	1	TYPE taken from SYSIBM.SYSINDEXES
CSOBJECT	CHAR	1	Y = CS, N = Not CS object
PARTS	SMALLINT		0 if not partitioned or number of partitions
HOWPART	CHAR	8	Indication of partitioning type
CPYFLAG	CHAR	7	Copy types (I, F or N) for days of week MTWTFSS
RUNFLAG	CHAR	7	Runstats (Y or N) for days of week MTWTFSS
EXTFLAG	CHAR	7	Alter extents (Y or N) for days of week MTWTFSS
REOFLAG	CHAR	7	Reorg (Y or N) for days of week MTWTFSS
NOSTATS	CHAR	1	Y = Not to collect statistics, else N
NOEXTENT	CHAR	1	Y = Not to alter PRIQTY/SECQTY, else N
NOREORG	CHAR	1	Y = Not to reorganise, else N
ACTIVE	CHAR	1	Y = Between start and end dates (below), else N
STARTDATE	DATE		Start date of exception
ENDDATE	DATE		End date of exception
UPDATEDBY	CHAR	8	Last updated by user-id (SQLID)
LASTUPDATED	TIMESTAMP		Timestamp when last updated
REMARKS	VARCHAR	64	Remarks generated by ISPF dialogue

The above columns are used and maintained by an ISPF dialogue, so changes to the table layout will require changes to the dialogue.

Dialogue

Setting up

An ISPF dialogue is available to select, insert, update and delete exceptions. Before first use, modify your USER panel adding a suitably named option, for example,

Add to body: + £X% - $DSNACCOX exception table dialogue
Add to &ZSEL: X,'CMD(%DSNXCEPT) NEWAPPL(XCPT) PASSLIB'

Option X has been used here, however any letter(s) or number(s) may be used but must match the &ZSEL entry.

Primary Menu

To start dialogue use option U (your user panel) and select the exceptions (e.g. U;X). The first screen presented looks like this:

-------------------------- DSNACCOX Exceptions - Main-------------- SQLcode: 0

Command ===> Scroll ===> CSR

Cmd: TIDY - Tidy up errors or COPY - Copy current entries to new environment

Row: S - Select details, I - Insert object, U - Update or D - Delete a row

DB2 ssid ===> DBxz

Environment ===> XX Use ZZ if not CS

MTWTFSS MTWTFSS MTWTFSS MTWTFSS

C DBname ObjName Ob A Querytype ImgCopy Runstat Extents Reorg

- -------- -------- -- - ---------------------- ------- ------- ------- -------

_ CSXXDC3 EXPLAIN8 TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ CSXXDC3 FLHS0049 TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ CSXXDC3 FLHS0059 TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ CSXXDC3 FLHS0068 TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNAADDR TS Y NOREORG STATS NOEXTENT IIIIIIF NNNNYNN NNNNNNN NNNNNNN

_ DBCSXXNA SNACACT TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNACOLC TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNACOLD TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNACSMH TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNACUST TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNADCEV TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNADCON TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNADSRV TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNAEACO TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNAHZDN TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

_ DBCSXXNA SNALYSC TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN

It shows at the top the DB2 group name and environment which may be changed by overtyping. The main body shows the database and name of the object and type (TS or IX) followed by the availability, start of the QUERYTYE column and then the image copy, runstats, extents and reorg flags.

The dialogue uses row command S, I, U and D, allowing rows to be selected, inserted, updated and deleted.

Insert

Insert row command, is a two-step process. Initially enter details shown in red (object type, database, name, with status (CS?) and environment, and if required overtype the active start and end dates. By default, exceptions are active from date of setting up until 31/12/9999.

On pressing enter a row is inserted into the exceptions table and an update screen is displayed to complete the new object processing.

------------------------- DSNACCOX Exceptions - Inser Step 1 Basic details

Command ===>

Insert Basic Details - Supply required details in red then press enter

Database Tablespace or index

Object TS DBCSXX44 S44COLG___________

Query Type ERROR___________________________________

CS EN TS TB IX

Status Y XX __ __ __

Parts 32___ INDEX___

M T W T F S S Stop housekeeping?

Image Copy I I I I I I F Image copies always run

Runstats _ _ _ _ _ _ _ _ Runstats not to be run!

Extents _ _ _ _ _ _ _ _ Alter space not to run!

Reorg N N N N N Y N N Reorgs are not to run!

Active from 28 02 2024 to 31 12 9999 Y

Updated By ABC1234_

Updated On 2024-02-28-16.15.35.973844

Remarks...

INSERT INCOMPLETE_____________________________________________

make any changes to the days of week when to or not to run utilities etc. and press enter to see the effect of the changes. When ready use PF3 to exit and save the details.

------------------------- DSNACCOX Exceptions - Update -------- Change details

Command ===>

Update details - Press enter to see updates then PF3 to apply changes

Database Tablespace or index

Object TS DBCSXX44 S44COLG___________

Query Type NOSTATS_________________________________

CS EN TS TB IX

Status Y XX L_ T_ __

Parts 32___ INDEX___

M T W T F S S Stop housekeeping?

Image Copy I I I I I I F Image copies always run

Runstats N Y N N N N N N Runstats are to be run

Extents N N N N N N N Y Alter space not to run!

Reorg N N N N N Y N N Reorgs are to be run

Active from 28 02 2020 to 31 12 9999 Y

Updated By ABC1234_

Updated On 2020-02-28-16.15.35.973844

Remarks...

INSERTED NEW OBJECT___________________________________________

Press enter to refresh then PF3 to save or CAN to cancel

Select

Row command S selects rows and display the full detail of the exception.

------------------------- DSNACCOX Exceptions - Select ------------ SQLcode: 0

Command ===>

Details of selected row - Press enter to see potential updates then PF3 to exit

Object TS DBCSXX44 S44COLG___________

Query Type STATS NOEXTENT__________________________

CS EN TS TB IX

Status Y XX L_ T_ __

Parts 32___ INDEX___

MTWTFSS Stop housekeeping?

Image Copy IIIIIIF Image copies always run

Runstats NYNNNNN N Runstats are to be run

Extents NNNNNNN Y Alter space not to run!

Reorg NNNNNYN N Reorgs are to be run

Active from 28.02.2020 to 31.12.9999 Y

Updated By ABC1234_

Updated On 2020-02-28-16.34.21.146539

Remarks...

AUTO update by TIDY command

Update

U row command allows rows to be updated, see fields using red font on the screen (not highlighted below).

------------------------- DSNACCOX Exceptions - Update -------- Change details

Command ===>

Update details - Press enter to see updates then PF3 to apply changes

Database Tablespace or index

Object TS DBCSXX44 S44COLG___________

Query Type STATS NOEXTENT__________________________

CS EN TS TB IX

Status Y XX L_ T_ __

Parts 32___ INDEX___

M T W T F S S Stop housekeeping?

Image Copy I I I I I I F Image copies always run

Runstats N Y N N N N N N Runstats are to be run

Extents N N N N N N N Y Alter space not to run!

Reorg N N N N N Y N N Reorgs are to be run

Active from 28 02 2024 to 31 12 9999 Y

Updated By ABC1234_

Updated On 2024-02-28-16.34.21.146539

Remarks...

UPDATED OBJECT________________________________________________

Press enter to refresh then PF3 to save or CAN to cancel

Delete

Typing D next to an exception allows a row to be deleted. On pressing enter a confirmation screen is displayed, press enter again to delete or PF3 to cancel and keep the row. Confirmation screen:

------------------------- DSNACCOX Exceptions - Delet ENTER or PF3 to keep

Command ===>

Delete Object

Object TS DBCSXX44 S44COLG

Query Type STATS NOEXTENT

Environment XX

No. of rows 1

=======================================================

= =

= WARNING - Deleting row(s) from exceptions table =

= =

= Press ENTER key to confirm deletion =

= =

= or =

= =

= Use PF3 or type CAN to cancel the request =

= =

=======================================================

After pressing enter to confirm deletion, the dialogue return to the main screen with a confirmatory message (highlighted).

-------------------------- DSNACCOX Exceptions - Main --- Row has been deleted

Command ===> Scroll ===> CSR

Cmd: TIDY - Tidy up errors or COPY - Copy current entries to new environment

Row: S - Select details, I - Insert object, U - Update or D - Delete a row

DB2 ssid ===> DBXZ

Environment ===> XX Use ZZ if not CS

MTWTFSS MTWTFSS MTWTFSS MTWTFSS

C DBname ObjName Ob A Querytype ImgCopy Runstat Extents Reorg

- -------- -------- -- - ---------------------- ------- ------- ------- -------

Page commands TIDY and COPY are available.

Tidy command displays exceptions where there is an error or mismatched parameters with a facility to automatically or manually correct the problems.
Copy command allows exceptions in one environment to be copied into another, with any pre-existing entries are ignored.

Tidy

The Tidy command checks for inconsistencies (object does not exist, flags and availability) and reports them on the following screen. Since there are no problems nothing is shown.

-------------------------- DSNACCOX Exceptions - Tidy -------- Empty list, PF3

Command ===> Scroll ===> CSR

The following exceptions are incorrectly setup, take action to correct.

Use command AUTO to automatically try and correct errors or use row command

S Select details, U Update and correct or D Delete object from list.

DB2 ssid ===> DBXZ

Environment ===> XX

Error MTWTFSS MTWTFSS MTWTFSS MTWTFSS Available dates

C DBname ObjName Status ImgCopy Runstat Extents Reorg A Start End

- -------- -------- ------- ------- ------- ------- ------- - -------- --------

******************************* Bottom of data ********************************

Auto

If object were listed, they may be corrected manually (update and delete available) or automatically using the AUTO command. Auto rebuilds the flags and updates rows in the exceptions table, normally this resolves issues.

Copy

Use the copy command to copy exceptions in one environment into another. It should be noted that any pre-existing entries in the target environment are not update or replaced. Example XX to D1:

-------------------------- DSNACCOX Exceptions - Copy -------------------------

Command ===>

Copy an exception list

Supply new target environment and press enter key to copy all exceptions used

by the source environment into the target environment. Any pre-existing target

exceptions will be unchanged.

SOURCE

Environment XX

TARGET

Environment XY

To cancel, use PF3

After the target environment name has been supplied, press enter and upon completion the target exception will be displayed. When copying, the database name of CS objects will be renamed to reflect the new environment.

To change the source environment, return to the main list and overtype the environment name which will redisplay the exceptions list, then repeat copy command.

Housekeeping

DSNACCOX produces recommendation for housekeeping where objects are in too many extents, in a restricted state, where image copies, statistics and reorganisation are required. Further jobs are required to generate suitable housekeeping.

Recommendations

If for example housekeeping for objects in R1 database DBCSR180, where are in a restricted state or an image copy or reorg are required, then a job like this may be run.

//ABC1234X JOB (DBA),'DSNACCOX',CLASS=1,MSGCLASS=X,MSGLEVEL=(1,1),

// NOTIFY=&SYSUID,REGION=0M,SCHENV=ANYSYSTEM,TIME=1440

//*

// SET SSID=DBXZ <-- DB2 SSID

// SET ENV=R1 <-- ENVIRONMENT XY, XX ETC. (## BELOW)

// SET QTYPE='REORG COPY RESTRICT'

// SET SCHEMA=DB2DBA <-- OVERRIDE SCHEMA, DEFAULT DSNACC

// SET DBNAME='DBCSXY80' <-- DBNAME, USING DEFAULT OF 'DBCS##%'

// SET OBJNAME='' <-- TS/IX NAMED, USING DEFAULT '%'

//*

//*--------------------------------------------------------------------

//* EXECUTE STORED PROCEDURE DSNACCOX

//*--------------------------------------------------------------------

//*

//JSTEP010 EXEC PGM=IKJEFT01,REGION=0M,

// PARM=('%DSNACCOX SSID(&SSID) ENV(&ENV) QTYPE(&QTYPE)',

// 'SCHEMA(&SCHEMA),DB(&DBNAME),OBJ(&OBJNAME)')

//STEPLIB DD DSN=DBS.&SSID..RUNLIB.LOAD,DISP=SHR

//SYSPRINT DD SYSOUT=*

//SYSTSPRT DD SYSOUT=*

//SYSPROC DD DSN=DBA.UTIL.EXEC,DISP=SHR

//IN01 DD DSN=DBA.UTIL.CNTLCARD(&ENV.ACCOX),DISP=SHR

//SYSTSIN DD DUMMY

After processing the results header table will contain a row for this run. The query needs to specify the environment (R1) and use a QTYPE matching that used in the job above.

Note – The keywords must be in the same sequence with one space between keywords.

SELECT MAX(LASTUPDATED) LASTUPDATED

FROM DB2DBA.RESULTS_HEADER

WHERE ENVIRONMENT = 'XY'

AND QTYPE = 'REORG COPY RESTRICT'

AND RUNOK = 'Y'

WITH UR;

---------+---------+---------+---------+----

LASTUPDATED

---------+---------+---------+---------+----

2020-03-04-17.03.20.000000

The query above returns the associated timestamp (2020-03-04-17.03.20.000000) which matches the LASTUPDATED timestamp in the results table.

Generation

This query above may then be used as a subquery when selecting rows from the results table, see below.

SELECT HKFLAG

, CHAR(DBNAME,8) DBNAME

, CHAR(NAME,18) NAME

, PARTITION PART

, OBJECTTYPE TYP

, IMAGECOPY COPY

, RUNSTATS RUN

, EXTENTS EXT

, REORG REO

, OBJECTSTATUS STATUS

, INEXCEPTTABLE

FROM DB2DBA.RESULTS R

WHERE DB2GROUP = 'DBXZ'

AND ENVIRONMENT = 'XY'

AND LASTUPDATED = (SELECT MAX(LASTUPDATED)

FROM DB2DBA.RESULTS_HEADER

WHERE ENVIRONMENT = R.ENVIRONMENT

AND QTYPE = 'REORG COPY RESTRICT'

AND RUNOK = 'Y')

WITH UR;

Notes on generating jobs:

QTYPE of "ALL" may be used so DSNACCOX is only run once per day and followed by separate generate jobs, producing image copies, reorgs and runstats jobs.
A better way may be to run separate DSNACCOX and generate HK jobs for QTYPE "COPY" and then then "REORG EXTENTS RESTRICT" and finally "RUNSTATS" so that recommendations are more current. This is because typically image copies would be run early evening and any reorgs later with Runstats later still.

The process of generating housekeeping jobs can be complicated due to the variety of object types and sizes and the impact due to job elapse times and load on the system.

In the past I check recommendations to run Runstats with the days since statistics last collected (age) and size of objects, with results in descending age order so that objects are not forgotten. When processing the list, a running total of object sizes is used to limit the number of jobs.

Flowchart

Here is a high-level flowchart showing the running of DSNACCOX and generation of housekeeping jobs.

The starting point is DSNACCOX run to generate a list of recommended housekeeping which may be one or many types (COPY, EXTENTS, RESTRICT, RUNSTATS, REORG or ALL). The end point is reading the results table and checking for the housekeeping you are interested in. The exceptions keywords will already be in the results but of the day of the week flags are required, will need to select the object from the exceptions table. The flags CPYFLAG, RUNFLAG, EXTFLAG and REOFLAG are CHAR (7) columns representing days of the week from Monday (byte 1) to Sunday (byte 7).

To prevent an excessive number of objects being processed (not image copies), keep a track of the size of objects (partitions) and a running total, ending the housekeeping generation after exceeding the limits (e.g. assuming object size is proportional to elapsed time).

+-----------------------------+

| Setup and run job DSNACCOX. |

+--------------+--------------+

| DSNACCOX generates |<--- Db2 Catalog

| recommendations and check |<--- EXCEPT_TBL

| for exceptions, writing |

| RESULTS and updates the |\

| RESULTS_HEADER | \

+--------------+--------------+ \

| > RESULTS and

+--------------+--------------+ / RESULTS_HEADER

| Query results (1 row per | /

| object/partition) |/

+--------------+--------------+

| Check column INEXCEPTTABLE |

| for NO or keyword list |

+--------------+--------------+

| If required check columns |

| CPYFLAG, RUNFLAG, EXTFLAG |<--- EXCEPT_TBL

| or REOFLAG in EXCEPT_TBL |

+--------------+--------------+

|---------<--------+

| |

+--------------+--------------+ |

| Generate housekeeping | |

| parameters where appropriate| |

+--------------+--------------+ |

| |

+--------------+--------------+ |

| Keep track of object sizes. | |

+--------------+--------------+ |

| |

+--------------+--------------+ |

| Check if still within limits| |

+---------+----------+--------+ |

No| |Yes |

Or end| +------>-----+

+---------+-------------------+

| Schedule housekeeping jobs. |

+-----------------------------+

------------------------------
Sandeep Jain
------------------------------

2. RE: DSNACCOX and Exceptions Part 2

Chad Breiner

Posted Wed May 15, 2024 09:16 PM

Hello Sandeep, where can I find the ISPF dialog members for this as well as the REXX DSNACCOX program you referenced?

------------------------------
Chad Breiner
------------------------------

Original Message

Original Message:
Sent: Wed March 27, 2024 04:44 AM
From: Sandeep Jain
Subject: DSNACCOX and Exceptions Part 2

DSNACCOX and Exceptions Part 2

Exceptions

DSNACCOX allows use of an exceptions table called EXCEPT_TBL with a creator name (schema) matching the id used by DSNACCOX and the results table, DB2DBA is being used throughout.

Mechanism

Columns

As stated above, the first three columns are required, and all other columns are to suit our requirements.

COLUMN	TYPE	LENGTH	REMARKS
DBNAME	CHAR	8	Required, first column of primary key
NAME	CHAR	8	Required, second column of primary key
QUERYTYPE	CHAR	40	Required, contents are free format data for our use
ENVIRONMENT	CHAR	2	Two characters, e.g. P1, D1, D2, R1 etc.
OBJECTTYPE	CHAR	2	TS or IX
TSTYPE	CHAR	1	TYPE taken from SYSIBM.SYSTABLESPACE
TBTYPE	CHAR	1	TYPE taken from SYSIBM.SYSTABLES
IXTYPE	CHAR	1	TYPE taken from SYSIBM.SYSINDEXES
CSOBJECT	CHAR	1	Y = CS, N = Not CS object
PARTS	SMALLINT		0 if not partitioned or number of partitions
HOWPART	CHAR	8	Indication of partitioning type
CPYFLAG	CHAR	7	Copy types (I, F or N) for days of week MTWTFSS
RUNFLAG	CHAR	7	Runstats (Y or N) for days of week MTWTFSS
EXTFLAG	CHAR	7	Alter extents (Y or N) for days of week MTWTFSS
REOFLAG	CHAR	7	Reorg (Y or N) for days of week MTWTFSS
NOSTATS	CHAR	1	Y = Not to collect statistics, else N
NOEXTENT	CHAR	1	Y = Not to alter PRIQTY/SECQTY, else N
NOREORG	CHAR	1	Y = Not to reorganise, else N
ACTIVE	CHAR	1	Y = Between start and end dates (below), else N
STARTDATE	DATE		Start date of exception
ENDDATE	DATE		End date of exception
UPDATEDBY	CHAR	8	Last updated by user-id (SQLID)
LASTUPDATED	TIMESTAMP		Timestamp when last updated
REMARKS	VARCHAR	64	Remarks generated by ISPF dialogue

The above columns are used and maintained by an ISPF dialogue, so changes to the table layout will require changes to the dialogue.

Dialogue

Setting up

An ISPF dialogue is available to select, insert, update and delete exceptions. Before first use, modify your USER panel adding a suitably named option, for example,

Add to body: + £X% - $DSNACCOX exception table dialogue
Add to &ZSEL: X,'CMD(%DSNXCEPT) NEWAPPL(XCPT) PASSLIB'