A scheduled job
executing REBUILD INDEX utility on an Index in Production failed and that ‘Restricted’
the access to a Table in Production, which resulted in Production outage. My
(DBA) team came to know about that only after someone from Application team
contacted us saying that they their Application is unable to access the Table.
We fixed the error quickly and application worked properly after that.
But, this incident
lead to the question ‘How can DBA team monitor Restricted Tablespace/Index
effectively and fix the issue even before Application team notices that?’
I came up with the
Process Automation for Monitoring for Restricted Tablespace/Index in Production
environment. The Process Automation includes the following steps, executed
through JCL.
è Step 1:
Following –DIS
commands will be executed (using PGM=IKJEFT01)
-DIS
DATABASE(dbname) SPACENAM(*) RESTRICT(REORP) LIMIT(*)
-DIS
DATABASE(dbname) SPACENAM(*) RESTRICT(RECP) LIMIT(*)
-DIS
DATABASE(dbname) SPACENAM(*) RESTRICT(RBDP) LIMIT(*)
Note: We want to monitor only 3 restricted statuses as they are more common in
our Production environment.
è Step 2:
I coded a REXX
routine that’ll parse through the output of –DIS commands to find the details
of Tablespace/Index that are Restricted and writes the output to a GDG Dataset.
è Step 3:
I coded a SMTP
card, when executed, will send Text message to our team members’ mobile phones
and email to our group email box.
I coded a
condition in the JCL so that, the SMTP card will be executed only when the GDG
Dataset (created in Step 2) has Restricted Tablespace/Index details in that.
I scheduled the
JCL (that executes the 3 Steps) to be executed every 15 minutes in Production.
This Process
Automation is not complicated to design and implement and will definitely be
very helpful in proactive monitoring for issues in Production environment.
Below REXX code
can be used for Step 2 of the Process Automation.
/*REXX*/
EOF = 0
CALL INIT_PARA
CALL
TERM_PARA
INIT_PARA:
DO UNTIL EOF
"EXECIO 1 DISKR SYSUT1"
IF RC <> 0 THEN EOF = 1
ELSE
DO
PULL REC
DBRES = SUBSTR(REC,1,10)
DBRES = STRIP(DBRES,"B")
IF DBRES = 'DSNT362I' THEN CALL RESDB
ELSE NOP
END
END
RETURN;
TERM_PARA:
"EXECIO 0 DISKR SYSUT1 (FINIS"
"EXECIO 0 DISKW SYSUT2 (FINIS"
EXIT 0
RESDB:
MSG = 'RESTRCITED TS/IX FOUND IN DATABASES'
PUSH MSG
"EXECIO 1 DISKW SYSUT2"
RETURN;
For more details about –DIS command for
Restrict statuses, below link can be used for reference.
No comments:
Post a Comment