Saturday, November 29, 2008

Catalog mainframe server/subsystem/database

Many a times we need to catalogue the mainframe DB2 to be accessed from Command Line Processor.
Here are the syntax through which you can catalog the mainframe db2 subsystem.
CATALOG TCPIP NODE ZOS1 REMOTE MAINFRAME SERVER 800 OSTYPE MVS
CATALOG DB DSN1 AS DSN1 AT NODE ZOS1 AUTHENTICATION SERVER
CATALOG DCS DB DSN1 AS DB2DSN1

After this you can logon by providing userid and password:
db2 connect to dsn1 user USR01 using password01

Cheers..
Prakash
IBM Certified DB2 DBA

Thursday, November 27, 2008

CCSID Mismatch after applying PTF for DB2 V7 to V8 migration

To begin with the DB2 migration from V7 to v8, we need to apply PTFs. After applying V7 PTF, we got the following error which accessing SPUFI.
DSNESP06
DSNE345I WARNING: DB2 DATA CORRUPTION CAN RESULT
FROM THIS SPUFI SESSION BECAUSE THE
CCSID USED BY THE TERMINAL IS NOT THE
SAME AS THE CCSID USED BY SPUFI

- TERMINAL CCSID:
- SPUFI CCSID: 500
NOTIFY THE DB2 SYSTEM ADMINISTRATOR.
PRESS: ENTER to continue END to return


To get rid of the problem, we have changed one default as below:After getting the first screen, press enter. You will see:Enter the following to control your SPUFI session:
1 SQL TERMINATOR .. ===> ; (SQL Statement Terminator)
2 ISOLATION LEVEL ===> CS (RR=Repeatable Read, CS=Cursor Stability)
3 MAX SELECT LINES ===> 30000 (Maximum number of lines to be returned from a SELECT)
4 ALLOW SQL WARNINGS===> NO (Continue fetching after sqlwarning)
5 CHANGE PLAN NAMES ===> yes (Change the plan names used by SPUFI) Output data set characteristics:
6 RECORD LENGTH ... ===> 4092 (LRECL=Logical record length)
7 BLOCK SIZE ...... ===> 4096 (Size of one block)
8 RECORD FORMAT ... ===> VB (RECFM=F, FB, FBA, V, VB, or VBA)
9 DEVICE TYPE ..... ===> SYSDA (Must be DASD unit name) Output format characteristics:
10 MAX NUMERIC FIELD ===> 33 (Maximum width for numeric fields)
11 MAX CHAR FIELD .. ===> 80 (Maximum width for character fields)
12 COLUMN HEADING .. ===> NAMES (NAMES, LABELS, ANY or BOTH)
PRESS: ENTER to process END to exit
HELP for more information'
Change the default from “NO” to “YES” for point 5 of plan names Then you will arrive at:
CURRENT SPUFI DEFAULTS - PANEL 2 SSID: DSNX ===> Enter the following to control your SPUFI session:
1 CS ISOLATION PLAN ===> DSNESPCS (Name of plan for CS isolation level)
2 RR ISOLATION PLAN ===> DSNESPRR (Name of plan for RR isolation level) Indicate warning message status:
3 BLANK CCSID WARNING ===> no (Show warning if terminal CCSID is blank)

As from the first screen, it is confirmed that terminal CCSID is coming as blank. This is the most simple solution to avoid this. Root cause: Prior to V7, Spufi CCSID comparision was not incorporated, but when you install the PTF, the SPUFI is enabled to compare it. That’s why you will get this. But all the problem is not clear until we apply the same PTFs on our prod LPAR.

Now I am going to explain, if the terminal CCSID is not Blank, how to solve the problem.The error screen is as below:
DSNESP06
DSNE345I WARNING: DB2 DATA CORRUPTION CAN RESULT
FROM THIS SPUFI SESSION BECAUSE THE
CCSID USED BY THE TERMINAL IS NOT THE
SAME AS THE CCSID USED BY SPUFI

- TERMINAL CCSID: 37
- SPUFI CCSID: 500
NOTIFY THE DB2 SYSTEM ADMINISTRATOR.
PRESS: ENTER to continue END to return

As per IBM recommendation we need to bind 2 additional packages and 2 plans to get rid of this:
BIND PACKAGE (SPCS0037)
ACTION (REPLACE)
MEMBER (DSNESM68) LIBRARY ('D710.SDSNDBRM')
OWNER (TSGDSN3)
QUALIFIER (TSGDSN3)
SQLERROR (NOPACKAGE)
VALIDATE (RUN)
ISOLATION (CS)
CURRENTDATA (YES)
KEEPDYNAMIC (NO)
NOREOPT (VARS)
DEGREE (1)
DBPROTOCOL (DRDA)

ENCODING (37)
EXPLAIN (NO);


BIND PACKAGE (SPRR0037)
ACTION (REPLACE)
MEMBER (DSNESM68) LIBRARY ('D710.SDSNDBRM')
OWNER (TSGDSN3)
QUALIFIER (TSGDSN3)
SQLERROR (NOPACKAGE)
VALIDATE (RUN)
ISOLATION (RR)
CURRENTDATA (YES)
KEEPDYNAMIC (NO)
NOREOPT (VARS)
DEGREE (1)
DBPROTOCOL (DRDA)

ENCODING (37)
EXPLAIN (NO);

BIND PLAN (SPCS0037)
OWNER (TSGDSN3)
QUALIFIER(TSGDSN3)
ACTION (REPLACE)
RETAIN
PKLIST (SPCS0037.DSNESM68 )
VALIDATE (RUN)
ISOLATION (CS)
ACQUIRE (USE)
RELEASE (COMMIT)
NODEFER (PREPARE)
DBPROTOCOL (PRIVATE)
CACHESIZE (256)
CURRENTDATA (YES)
DISCONNECT (EXPLICIT)
SQLRULES (DB2)
DEGREE (1)
NOREOPT (VARS)
KEEPDYNAMIC (NO)

ENCODING (37)
EXPLAIN (NO);

BIND PLAN (SPRR0037)
OWNER (TSGDSN3)
QUALIFIER(TSGDSN3)
ACTION (REPLACE)
RETAIN
PKLIST (SPRR0037.DSNESM68 )
VALIDATE (RUN)
ISOLATION (RR)
ACQUIRE (USE)
RELEASE (COMMIT)
NODEFER (PREPARE)
DBPROTOCOL (PRIVATE)
CACHESIZE (256)
CURRENTDATA (YES)
DISCONNECT (EXPLICIT)
SQLRULES (DB2)
DEGREE (1)
NOREOPT (VARS)
KEEPDYNAMIC (NO)

ENCODING (37)
EXPLAIN (NO);
After Bind and getting the first screen and pressing enter there, you will get the screen as before.You go to modify as before: change CHANGE PLAN NAMES from NO to YES In the following screen, you need to change the default DB2 supplied Plan name as below:
CURRENT SPUFI DEFAULTS - PANEL 2 SSID: DSNX
===> Enter the following to control your SPUFI session:
1 CS ISOLATION PLAN ===> SPCS0037 (Name of plan for CS isolation level)
2 RR ISOLATION PLAN ===> SPRR0037 (Name of plan for RR isolation level) Indicate warning message status:
3 BLANK CCSID WARNING ===> no (Show warning if terminal CCSID is blank)

Here are other info that may be necessary while in New Function mode:
*************************************************************
*** Further instructions for DB2 Version 8 customers *** *************************************************************
In V8 new-function mode only, plans for SPUFI require an additional package for the DSNTIAP DBRM. Thus, if your DB2 is now running in V8 new-function mode, you also need to use a command like the following to bind
DSNTIAP DBRM under CCSID 1047:
BIND PACKAGE(TIAP1047) MEMBER(DSNTIAP) -
ACTION(REPLACE) ISOLATION(CS) ENCODING(1047) -
LIBRARY('prefix.SDSNDBRM') and then include the TIAP1047 collection id when you BIND the plans for SPUFI:
BIND PLAN(SPCS1047) -
PKLIST(SPCS1047.DSNESM68, -
TIAP1047.DSNTIAP) -
ISOLATION(CS) ENCODING(1047) ACTION(REPLACE) ...
BIND PLAN(SPRR1047) -
PKLIST(SPRR1047.DSNESM68, -
TIAP1047.DSNTIAP) -
ISOLATION(RR) ENCODING(1047) ACTION(REPLACE)
If your DB2 is currently running in V8 compatibility mode or in enabling-new-function mode, you need to bind DSNTIAP as indicated above after DB2 enters new-function mode.

2. After you have created the new packages and plans, grant access on them to the target user group.

3. Instruct the users to display the CURRENT SPUFI DEFAULTS panel (DSNESP02) and specify YES in the CHANGE PLAN NAMES field. When they do this, SPUFI displays CURRENT SPUFI DEFAULTS - PANEL 2 (DSNESP07). Instruct users to specify SPCS1047 for CS ISOLATION PLAN and SPRR1047 for RR ISOLATION PLAN. These users can now update SPUFI without receiving the DSNE345Imessage
.

Cheers....
Prakash Singh
IBM Certified Database Administrator (DB2 on Mainframe)

Saturday, November 22, 2008

Knowing Tapes are still on Rack SIL0 command

While refreshing from production to test we need backup tape of production.
How do you confirm that it is still on the rack or already valuted out:
Here is the command you need to fire on console:

/F SLS0,D VOL 800165

800165 is the volume number of the tape which you require.
the output is like:
RESPONSE=MVS1 SLS0600I VOLUME 800165 ; 00:00:04:02:23, UNSELECTED,RESPONSE=SCRATCH

Which means it is in the rack.

If you get any out like:Here I have searched for volume 100165

RESPONSE=MVS1 SLS0603I VOLUME(S) 100165 NOT IN ACS
which means it is not in the rack.

It's a proactive thing which we can follow to prevent any failure which is resulting from using tapes in current job.

Cheers...
Prakash
IBM Certified DB2 DBA.

Friday, November 21, 2008

To know the z/OS version and last IPL info

As a DBA, sometimes we need to check the oprating system version for compatability of some of the DB2 related software while installation and upgrade.

To find this there is spool/console command:

/D IPLINFO

The output is like below:


RESPONSE=MVS1

IEE254I 05.39.50 IPLINFO DISPLAY 651

SYSTEM IPLED AT 18.36.54 ON 10/25/2008

RELEASE z/OS 01.07.00 LICENSE = z/OS

USED LOAD00 IN SYS1.IPLPARM ON 8326

ARCHLVL = 2 MTLSHARE = N

IEASYM LIST = 00

IEASYS LIST = 00

IODF DEVICE 8326


Cheers..
Prakash
IBM Certified DB2 DBA

Thursday, November 20, 2008

DB2 Version and other info

How do you know the current DB2 version?
or
How will you confirm which phase(while migrating from V7 to V8 - Compatibility mode, Enable New Function mode and New Function mode) of migration currently going on for your DB2?

There is a simple command which can display all these information.
Go to command option of the DB2 and execute below command

-DIS GROUP
The output will be like:
DSN7100I + DSN7GCMD,
*** BEGIN DISPLAY OF GROUP(........) GROUP LEVEL(...),

GROUP ATTACH NAME(....),
--------------------------------------------------------------------
DB2 DB2 SYSTEM IRLM,
MEMBER ID SUBSYS CMDPREF STATUS LVL NAME SUBSYS IRLMPROC
-------- --- ---- -------- -------- --- -------- ---- --------
........ 0 DSN2 + ACTIVE 710 MVS1 TRLM DSN2TRLM
--------------------------------------------------------------------
*** END DISPLAY OF GROUP(........),
DSN9022I + DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION,

Cheers..

Prakash
IBM Certified DB2 Administrator

Sunday, November 16, 2008

Conditional Restart DB2 DSNJU003

This is a very intresting problem we had some times back.
In Test sub-system, suddenly DB2 crashed, when we tried to make it UP, it was tried to read the archive logs indefintely.
After investigation, we found that one of the user has inserted 100 of millions of rows in a very huge table causing 100s of logs filled up leading to DB2 crash.

After another round of investigation we found that DB2 was trying to recover to a RBA which is not recorded in BSDS. We knew that DB2 will definitely abend after trying to read many archive logs.

Then we think of conditional restart for this by modifying the BSDS.
To do this we need to shutdown the DB2.

Before modifying the BSDS. Create a personal VSAM BSDS to copy one of the BSDS into it as a backup. Here is the BSDS VSAM defination.

//TSG10BDS JOB (ACCT),'CFTUTIL',CLASS=O

//DSNTIC PROC

//* ***************************************************************** */

//* DIRECTORY/CATALOG AMS INVOCATION INSTREAM JCL PROCEDURE */

//* ***************************************************************** */

//DSNTIC EXEC PGM=IDCAMS,COND=(2,LT)

//SYSPRINT DD SYSOUT=*

//SYSUDUMP DD SYSOUT=*

//DSNTIC PEND

//DSNTDBL EXEC DSNTIC

//SYSIN DD *

DEFINE CLUSTER -

( NAME(DSN3.BSDS01.COPY) -

VOLUMES(DSN366) -

REUSE -

SHAREOPTIONS(2 3) ) -

DATA -

( NAME(DSN3.BSDS01.COPY.DATA) -

RECORDS(180 20) -

RECORDSIZE(4089 4089) -

CONTROLINTERVALSIZE(4096) -

FREESPACE(0 20) -

KEYS(4 0) ) -

INDEX -

( NAME(DSN3.BSDS01.COPY.INDEX) -

RECORDS(5 5) -

CONTROLINTERVALSIZE(1024) ) CATALOG(DSN3)

====



Then we did run the following JCL after finding a suitable RBA (this should be multiple of 4K, otherwise DSNJ003 will not update the BSDS and give you the error:

//TSGDSN3A JOB ,
// CLASS=L,MSGCLASS=O,NOTIFY=TSGDSN3,MSGLEVEL=(1,1)
//CONREST EXEC PGM=DSNJU003
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=DSN3.BSDS01,DISP=SHR
//SYSIN DD *
CRESTART CREATE,ENDRBA=006A7F46D000,FORWARD=NO,BACKOUT=NO
/*

As we have 2 BSDS in place we have copied the same info to other BSDS also. we tried to update the info in BSDS02. but while starting DB2 it gave us synchronisation error. so always update one BSDS and Repro the same into other BSDS.


//TSG10 JOB 1111,'BSDS REC',CLASS=A,MSGCLASS=O
//*
//* ****************************************************
//* *
//* * ***** DB2 MUST BE DOWN WHEN THIS JOB IS RUN. *****
//* *
//* REPRO BSDS01 TO BSDS02
//*
//STEP02 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=O
//IFILE1 DD DSN=DSN3.BSDS01,
// DISP=SHR
//OFILE1 DD DSN=DSN3.BSDS02,
// DISP=SHR
//SYSIN DD *
REPRO INFILE (IFILE1) -
OUTFILE(OFILE1) -
REUSE
/*

To see the content of DSBS we printed it by:
//TSG10PRN JOB 1111,'BSDS REC',CLASS=L,MSGCLASS=O
//*
//* **************************************************
//* *
//* * ***** DB2 MUST BE DOWN WHEN THIS JOB IS RUN. ***
//* *
//* **************************************************
//*
//* PRINT THE UPDATED BSDS
//*
//PRNTBSDS EXEC PGM=DSNJU004
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=DSN3.BSDS01,DISP=SHR
//*

Then when we started the subsystem, it started successfully pretty quick:
by giving below message:
STC03816 DSNR014I ! EXCLUDED RURE TABLE 056 T CON-ID CORR-ID AUTHID PLAN S URID DAY TIME
- -------- ------------ -------- -------- - ------------ --- --------
B BATCH DVF26CV DVF26 DSNTEP2 A 006762C6D8C1 221 09:15:50 STC03816 *15 DSNJ245I ! CONDITIONAL RESTART RECORD INDICATES TRUNCATION AT RBA 006A7F46D000.
REPLY Y TO CONTINUE, N TO CANCEL
DSN3MSTR STC03816

Then everything seems right for this test subsystem...
Cheers...
Prakash
IBM Certified DB2 DBA

Saturday, November 15, 2008

Resource Limit Specification Table DSNRLST01

There might be some userid/Plan which used to submit the very high cost sqls those used to take many hours CPU time.

To restrict these for running indefinite time, mainframe is having governor kind of stuff called resource limit specification table DSNRLST01. you need add record for culprit userid/plan to avoid this kind of scenario.

Cheers..

Prakash

IBM Certified DB2 DBA

Friday, November 14, 2008

DSNUTILB - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E40002'

We got a call from development team that they are getting ABEND S04E while running DSNUTILB. When I looked in the reson code.. it is REASON=X'00E40002'.
The complete error message is "DSNU016I DSNUTILB - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E40002'"
Initial investigation shows the tablespace is not accessable enven if all the index and tablespace is in RW status. When I try to execute a query like "Select count(*) from T1FHHD; it excuted fine. But when I tried to run a query like: "Select * from T1FHHD fetch first 1 row only". It gave me below message in DSN2MSTR
STC00720 DSNI014I + DSNIRNXT DATA IN USE DURING ABEND 219 REASON 00C90101
ERQUAL 53CB
TYPE 00000302
NAME DSPS02 .SFHHD .X'00000002'
CONNECTION-ID=TSO
CORRELATION-ID=TSGDBT1
LUW-ID=*
STC00720 DSNI014I + DSNIRNXT DATA IN USE DURING ABEND 220 REASON 00C90101
ERQUAL 53CB
TYPE 00000302
NAME DSPS02 .SFHHD .X'00000001'
CONNECTION-ID=TSO
CORRELATION-ID=TSGDBT1
LUW-ID=*
From this we can determine that there is a data corruption in page 1 and 2 of the tablespace. Since it is test system, we dopped/recreated and refreshed the data from production and told the development team to rerun the job.

Again they came back with same error.
Now my focus directed to the APF authentication Library as the reson code tells: 00E40002
Explanation: The caller of the database services portion of the utilitywas in the wrong protect key.
If a JOBLIB or STEPLIB is being used in the batch utility job, check thatall application program libraries included in the concatenation are authorized.
Check that the batch utility program DSNUTILB was included in the MVS program properties table during the installation of DB2. The entry for DSNUTILB in the MVS program properties table was provided by MVS.
The steplib coded for the step as below:
STEPLIB DD DSN=TSYS.STEST.BATCH.LOADLIB,DISP=SHR
DD DSN=SYS1.DSN2.SDSNLOAD,DISP=SHR
DD DSN=SYS1.SDSNLOAD,DISP=SHR

When I looked into SYS1.PARMLIB(PROG00), I could not find TSYS.STEST.BATCH.LOADLIB in it. So this Library is not APF authorised. If one of the concatenated pds is not authorized, then whole steplib will not be authorized.

There are 2 options for this:
Option 1:
To modify SYS1.PARMLIB(PROG00)
by putting this line:APF ADD DSNAME(TSYS.STEST.BATCH.LOADLIB) VOLUME(SSD001) -- code appropriate volume.
After that you need to refresh the parmlib by giving spool command /set prog=00
you can have a look at SYS1.PARMLIB(SCHED00)
PPT PGMNAME(DSNUTILB) /* DB2 UTILITY */ CANCEL /* CAN BE CANCELLED */
KEY(7) /* PROTECTION KEY */
NOPRIV /* NOT PRIVILEGED */
SWAP /* SWAPPABLE */
DSI /* DATA SET INTEGRITY */
PASS /* NO PASSWORD BYPASS */
NOSYST /* SYSTEM TASK , NOT TIMED */
AFF(NONE) /* NO PROCESSOR AFFINITY */

This is to verify whether we have KEY(7) which otherwise will give protect key issue. Check this SYS1.IPLPARM(LOAD00) whether you are looking into correct parm: This will look like below:You will definitly find "PARMLIB SYS1.PARMLIB" as first occurence.
IODF 40 MVS1 PROD 00
NUCLEUS 1
NUCLST 00
SYSCAT MVSC70133CCATALOG.ICFM.VMVSC70
PARMLIB SYS1.PARMLIB
PARMLIB CPAC.PARMLIB
PARMLIB SYS1.IBM.PARMLIB
IEASYM 00
SYSPLEX SOMPLEXP

Option 2:
We can remove the TSYS.STEST.BATCH.LOADLIB from the STEPLIB as it does not include load of IBM supplied utility DSNUTILB.So that we will not get the true APF authorization error with 00E40002.

Conclusion:
Option 2 is better one as it does not require intervention of system programmer and pretty quick to achieve.

Cheers...
Prakash
IBM Certified DB2 DBA

Thursday, November 13, 2008

Job seems not executing for longer period of time

Hi,
Sometimes after submitting the database refresh job, you find that, this job is not getting CPU and idle for many times..

One of the reason may be.. the underlying VSAM datasets of the target tablespaces/indexspaces are migrated due to not being accessed for long time. DB2 will try to HRECALL the required datasets. In the same time you can recall the datasets so that your job will have the datasets and move forward.

Cheers..
Prakash
IBM Certified DB2 DBA

Tuesday, November 11, 2008

How to Revoke SYSADM previlege without cascading effect

Installation system admin have given SYSADM authority to a userid (USR10). Through that userid, the user created many objects, had bind many packages and given many privileges.
Now when
Installation system admin tried to revoke the SYSADM auth from that user, the mainframe screen seems to be hanged and locked for hours. Even the subsystem might be crashed.

How to Approach this problem..

We made the Userid(USR10) as Installation SYSADM id through one job which make the change in Z-Parm of DB2 subsystem.
Here is the member:
D710.DSN4.SDSNSAMP(DSNTIJUZ) -- second qualifier is the subsystem name.
and parameters are
SYSADM=USR10, -- put userid over here from which you want to revoke the SYSADM auth.
SYSADM2=USR10,

you need to stop/start the subsystem to make it effect. After that Shoot the REVOKE statement. This will be executed in a second. After this do not forgot to change the SYSADM to it's initial value.

Catch is When you revoke SYSADM from a installation sysadm, you won't have cascading effect.



Regards,
Prakash
IBM Certified DB2 DBA

Monday, November 10, 2008

Access the commands of DB2 from command line prompt

Do you know, we can access the commands of DB2 from command line prompt.

Just type following command on command line of Mainframe:

TSO DSN S(subsystem_Name) then press enter.

After that you will a DSN prompt at page below.
You can type commands like:
-DIS THREAD(*)

and when you want to exit just type END

Regards,
Prakash
IBM Certified DB2 DBA

Sunday, November 9, 2008

DB2 DBA Z/OS

I am creating this blog exclusively for DB2 Database Administrators on mainframe platform. The idea is to share my knowledge that I gained during my practical work experience till date.

I will share some interesting facts and real time problem scenario with solution.

Your suggestions and comments are always welcome.


Happy Blogging.

Prakash C. Singh
IBM Certified DB2 DBA