DB2 DBA

Termination of REORG TABLESPACE

2010-12-25T17:05:00.000+05:30

Hello my blog readers...

It is truely a concern when the REORG of the table terminated. Here is one of the most important facts regarding Reorg failure and termination which I have collected from IBM manual and shared with you for your benefit.

You can terminate the REORG TABLESPACE utility.

If you terminate REORG TABLESPACE with the TERM UTILITY command during the UNLOAD phase, objects have not yet been changed, and you can rerun the job.

If you terminate REORG TABLESPACE with the TERM UTILITY command during the RELOAD phase, the behavior depends on the SHRLEVEL option:

•For SHRLEVEL NONE, the data records are not erased. The table space and indexes remain in RECOVER-pending status. After you recover the table space, rerun the REORG job.

•For SHRLEVEL REFERENCE or CHANGE, the data records are reloaded into shadow objects, so the original objects have not been affected by REORG. You can rerun the job.

If you terminate REORG with the TERM UTILITY command during the SORT, BUILD, or LOG phases, the behavior depends on the SHRLEVEL option:

•For SHRLEVEL NONE, the indexes that are not yet built remain in RECOVER-pending status. You can run REORG with the SORTDATA option, or you can run REBUILD INDEX to rebuild those indexes.

•For SHRLEVEL REFERENCE or CHANGE, the records are reloaded into shadow objects, so the original objects have not been affected by REORG. You can rerun the job.

If you terminate a stopped REORG utility with the TERM UTILITY command during the SWITCH phase, the following conditions apply:

•All data sets that were renamed to their shadow counterparts are renamed to their original names, so that the objects remain in their original state, and you can rerun the job.

•If a problem occurs in renaming the data sets to the original names, the objects remain in RECOVER-pending status, and you cannot rerun the job.

If the SWITCH phase does not complete, the image copy that REORG created is not available for use by the RECOVER utility. If you terminate an active REORG utility during the SWITCH phase with the TERM UTILITY command, during the rename process, the renaming occurs, and the SWITCH phase completes. The image copy that REORG created is available for use by the RECOVER utility.

The REORG-pending status is not reset until the UTILTERM execution phase. If the REORG utility abnormally terminates or is terminated, the objects remain in REORG-pending status and RECOVER-pending status, depending on the phase in which the failure occurred.

The following table lists the restrictive states that REORG TABLESPACE sets according to the phase in which the utility terminated.

Table 1. Restrictive states that REORG TABLESPACE sets. Phase Effect on restrictive status

UNLOAD No effect.

RELOAD SHRLEVEL NONE:

•Places table space in RECOVER-pending status at the beginning of the phase and resets the status at the end of the phase.

•Places indexes in RECOVER-pending status.

•Places the table space in COPY-pending status. If COPYDDN is specified and SORTKEYS is ignored, the COPY-pending status is reset at the end of the phase. SORTKEYS is ignored for several catalog and directory table spaces

SHRLEVEL REFERENCE or CHANGE has no effect.

SORT No effect.

BUILD SHRLEVEL NONE resets RECOVER-pending status for indexes and, if the utility job includes both COPYDDN and SORTKEYS, resets COPY-pending status for table spaces at the end of the phase. SHRLEVEL REFERENCE or CHANGE has no effect.

SORTBLD No effect during the sort portion of the SORTBLD phase. During the build portion of the SORTBLD phase, the effect is the same as for the BUILD phase.

LOG No effect.

SWITCH No effect. Under certain conditions, if TERM UTILITY is issued, it must complete successfully; otherwise, objects might be placed in RECOVER-pending status.

Recovering a failed REORG job

If you terminate REORG SHRLEVEL NONE in the RELOAD phase, all SYSLGRNX records associated with the reorganization are deleted. Use the RECOVER TABLESPACE utility to recover to the current point in time. This action recovers the table space to its state before the failed reorganization.

Example 1: Recovering a table space. The following control statement specifies that the RECOVER utility is to recover table space DSN8D81A.DSN8S81D to the current point in time.

RECOVER TABLESPACE DSN8D81A.DSN8S81D

Cheers.. and Merry Christmas!!!

Regards,
Prakash C. Singh
IBM Certified DB2 DBA.

DB2 EXIT Routines

2010-09-19T19:48:00.001+05:30

Hi,

Here is one topic for you in controlling access to DB2 Subsystems:

There are two very important IBM supplied default Exit routines.

DB2 provides 2 exit points for authorization routines:

1) Connection Processing - DSN3@ATH

2) Sign-on Processing - DSN3@SGN

DB2 also provides a third exit point - DSNX@XAC which gives you the flexibility to furnish your own access control routines or use RACF (or equivalent) to perform system authorization checking.

DB2 passes 3 possible functions while invoking authorization routine

a) Initialization - DB2 Startup
b) Authorization check
c) Termination - DB2 Shutdown

There are certain situations where Exit routines may not be called

1) If the user is a Install SYSADM or Install SYSOPR
2) Grant statement is executed
3) If previous invocation indicated the routine should not be called again
4) DB2 security has been disabled (AUTH (DSNZPARM) - Use Protection is set to NO or in DSNTIPP panel)

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

Backup Failed with REASON 00C200E1

2010-08-08T13:33:00.000+05:30

Hello All,

Here is another classic example of backup failure with REASON 00C200E1.

You will get below message:

DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DSQDBCTL
DSNUGUTC - COPY TABLESPACE DSQDBCTL.DSQTSCT1 COPYDDN DSQTSCT1
DSNUGBAC - RESOURCE UNAVAILABLE
   REASON 00C200E1
   TYPE 00000220
   NAME QMF220.DSNDBC.DSQDBCTL.DSQTSCT1.I0001.A001
DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E4010A'

If you do a QW for the above reason code, then you see

00C200E1
  Explanation: The buffer manager (BM) subcomponent of DB2 is unable to
  open a data set that is required to be accessed by the requesting
  function. An error was returned by the media manager CONNECT function,
  which was invoked to open a VSAM data set. DB2 message DSNB204I was issued
  to inform the operator of the error condition. This reason code and the
  data set name are recorded in the cursor table (CT) and made available to
  the user in the SQLCA or in messages.

  This abend reason code is issued by the following CSECT: DSNB1OST

  System Action: A 'resource not available' code is returned to the user.
  Abend status is recorded in SYS1.LOGREC.

  Operator Response: Notify the system programmer.

  System Programmer Response: Refer to system message IEC161I and DB2
  message DSNB204I for assistance in determining the cause of the error.
  Correct the situation, and notify the user to invoke the application

As you see, you need to search for IEC161I.
2 places you can able to see this, one is System LOG another place is DSN1DBM1.

It is logical to see in DBM1 as it manages buffer and the error says "he buffer manager (BM) subcomponent of DB2 is unable to open a data set"

After you look into the DBM1 started task, you may find following error messages:

ICH408I USER(SYS00 ) GROUP(SYS1 ) NAME(STC ) 093
  QMF220.DSNDBC.DSQDBCTL.DSQTSCT1.I0001.A001 CL(DATASET ) VOL(DSN101)
  RESOURCE NOT PROTECTED
  ACCESS INTENT(UPDATE ) ACCESS ALLOWED(NONE )
IEC161I 040(056,006,IGG0CLFT)-002,DSN1DBM1,DSN1DBM1,SYS30435,,, 094
IEC161I QMF220.DSNDBC.DSQDBCTL.DSQTSCT1.I0001.A001

Now it is confirmed that it is not a DB2 issue at all rather than RACF issue.

As you get the clue, you can contact the mainframe System admin/RACF admin to resolve the issue and rerun the job.

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

Know the power of REPAIR utility in DB2

2010-07-24T21:18:00.000+05:30

Hello my blog readers...

In this blog, I am going to explain how you can utilize REPAIR utility the most.

Everybody know REPAIR utility is popularly used for resetting the pending states of Index and Tablespaces.

Example 1:
Everybody know REPAIR utility is popularly used for resetting the pending states of Index and Tablespaces.

//STEP3 EXEC DSNUPROC,UID='SSTRR',
// UTPROC='',SYSTEM='DSN2'
//SYSIN DD *
  REPAIR OBJECT
  SET INDEX (ALL) TABLESPACE DTSG10.SSTRR NORBDPEND
  SET TABLESPACE DBTEST1.STEST PART 1 NOAUXCHKP
  SET TABLESPACE DBTEST1.STEST PART 4 NOCHECKPEND
/*

Example 2:
Suppose you want to delete a row corresponding to a ROWID mentioned in the referential violation constraint.

REPAIR
  LOCATE TABLESPACE DTSG10.TS1 RID (X'0000000503')
   DELETE

Example 3:
Replacing damaged data and verifying replacement.

* Repair the specified page of table space DTSG10.STEST1, as indicated by the LOCATE clause.
* Verify that, at the specified offset (50), the damaged data (0A00) is found, as indicated by the VERIFY clause.
* Replace the damaged data with the desired data (0D11), as indicated by the REPLACE clause.
* Initiate a dump beginning at offset 50, for 4 bytes, as indicated by the DUMP clause. You can use the generated dump to verify the replacement.

//STEP1 EXEC DSNUPROC,UID='IUIQU1UH',UTPROC='',SYSTEM='DSN'

//SYSIN DD *

REPAIR OBJECT

LOCATE TABLESPACE DSN8D81A.DSN8S81D PAGE X'02'

VERIFY OFFSET 50 DATA X'0A00'

REPLACE OFFSET 50 DATA X'0D11'

DUMP OFFSET 50 LENGTH 4

Example 4:

Reporting whether catalog and directory DBDs differ. The following control statement specifies that REPAIR is to compare the DBD for DTSG10 in the catalog with the DBD for DTSG10 in the directory.

REPAIR DBD TEST DATABASE DTSG10

Example 5:

Reporting differences between catalog and directory DBDs. The following control statement specifies that the REPAIR utility is to report information about the inconsistencies between the catalog and directory DBDs for DTSG10. Run this job after you run a REPAIR job with the TEST option (as shown in example 4), and the condition code is not 0. In this example, SYSREC is the output data set, as indicated by the OUTDDN option.

REPAIR DBD DIAGNOSE DATABASE DSN8D2AP OUTDDN SYSREC

Example 6:

Repairing a table space with an orphan row. After running DSN1CHKR on table space SYSDBASE, assume that you receive the following message:

DSN1812I ORPHAN ID = 20 ID ENTRY = 0190 FOUND IN

PAGE = 0000000024

From a DSN1PRNT of page X'0000000024' and X'0000002541', you identify that RID X'0000002420' has a forward pointer of X'0000002521'.

Repair the table space by taking the following actions:

1. Submit the following control statement, which specifies that REPAIR is to set the orphan's backward pointer to zeros:

REPAIR OBJECT LOG YES

LOCATE TABLESPACE DSNDB06.SYSDBASE RID X'0000002420'

VERIFY OFFSET X'0A' DATA X'0000002422'

REPLACE OFFSET X'0A' DATA X'0000000000'

Setting the pointer to zeros prevents the next step from updating link pointers while deleting the orphan. Updating the link pointers can cause DB2 to abnormally terminate if the orphan's pointers are incorrect.

2. Submit the following control statement, which deletes the orphan:

REPAIR OBJECT LOG YES

LOCATE TABLESPACE DSNDB06.SYSDBASE RID X'00002420'

VERIFY OFFSET X'06' DATA X'00002521'

DELETE

Example 7:

Updating version information. The control statement specifies that REPAIR is to update the version information in the catalog and directory for table spaces STEST1, STEST2, and STEST3.

REPAIR VERSIONS example control statement

//STEP1 EXEC DSNUPROC,UID='JUKQU3AS.REPAIR',TIME=1440,

// UTPROC='',

// SYSTEM='SSTR',DB2LEV=DB2A

//SYSIN DD *

REPAIR VERSIONS TABLESPACE DTSG10.STEST1

REPAIR VERSIONS TABLESPACE DTSG10.STEST1 2

REPAIR VERSIONS TABLESPACE DTSG10.STEST1 3

So arm the knowledge of REPAIR Utility and set for the adventure.

Cheers..

Prakash C Singh.

IBM Certified DB2 DBA

How to display the key corresponding to RID Value

2010-07-05T16:53:00.001+05:30

Hi,

When you run CHECK DATA utility to verify the violating the RI constraints, you may come across the following error message:

DSNUKERK - ROW (RID=X'0000000202') HAS NO PARENT FOR TSG10.TES3.CTES3A

Here the hexadecimal value X'0000000202' is the Row Identifier of the key of the child table which violates the RI constraint CTES3A.

If someone tell you to find out the record corresponding key to the RID value, then you can follow this approach.

There is one stand alone utility DSN1PRNT by the help of which you can print the VSAM file data.

RID value is always associated with the index of the table.

Hence by searching the RID value in underlying index dataset( for example DSN2.DSNDBD.DTSG10.X1TES300.I0001.A001) of violating child table, we can get hold of the key.

Here is the sample JCL: Note: always search hexadecimal value in two double quotes.

//TSG10P JOB ,
// CLASS=L,MSGCLASS=O,NOTIFY=TSG10,MSGLEVEL=(1,1)
//****************************************************************
//*
//* JCL TO PRINT HEXADECIMAL DUMP OF DB2 IMAGE COPY
//*
//****************************************************************
//*
//STEP1 EXEC PGM=DSN1PRNT,
// PARM='FORMAT,VALUE(''0000000202'')'
//SYSUT1 DD DSN=DSN2.DSNDBD.DTSG10.X1TES300.I0001.A001,DISP=SHR
//SYSPRINT DD SYSOUT=*
//*

The subset of the output looks like below:
===
UNIQUE KEYS FOLLOW:
KEY ENTRY: IPKMAP(XI)='0038'X
KEY:
8001
RID:
0000000201
KEY ENTRY: IPKMAP(XI)='003F'X
KEY:
8002
RID:
4000000202
KEY ENTRY: IPKMAP(XI)='0046'X
KEY:
8005
RID:
4000000203

DSN1994I DSN1PRNT COMPLETED SUCCESSFULLY, 00000005 PAGES PROCESSED

Note:

1) To find out the violating rows you can create the exception table and track those and at the same time you can bring the table to RW mode from Check pending status.

2) Or you can use the REPAIR utility for DUMP/DELETE the violating rows By LOCATE TABLESPACE command.

So do not be afraid of seeing the RID hex values, just play with it by the available utilities

Cheers...
Prakash C Singh
IBM Certified DB2 DBA

Get ready for DB2 X

2010-04-18T10:56:00.000+05:30

Hey...now-a-days SPEED is the new buzz around the world.

So DB2 for z/OS leads the race in RDBMS category with announcement of beta version of DB2 10 or DB2 X on 9th, February 2010.

Here are some major points in DB2 X for z/OS

1) This version gives you the best CPU reductions for transactions and batches (i.e. saving more money)

2) Second major benefit is Scalability (Enhanced query parallelism)

3) More on demand enhancement improves availability (more online changes for Data definitions, utilities and Subsystem)

4) DBAs will be happy to find improved database performance, scalability, and availability

5) Reduced memory management, so growth is much simpler (10 times more users by avoiding memory constraints)

6) To support regulatory compliance, DBA to get more flexible security (More granularity)

7) Warehousing continues to evolve, with key trends matching System z and DB2 for z/OS strengths of performance, scalability, reliability, stability, availability, resilience, and security (On the fly Data compression)

8) SQL, pureXML, and web services extend usability and application portability for this platform

9) More concurrency for Catalog, Utilities and SQL

10) Improved productivity for Database admin, System admin and application programmers

Cheers...
Prakash C Singh
IBM Certified Database Administrator

Modelling your production system

2010-03-29T22:26:00.000+05:30

Hello Blog readers,

Most of the times it is a real challenge to refresh data from production environment to test env with limited time and resources. But it is very important to know the future access path to be produced in production env while testing in development environment. This is basically needed to know the future performance problem associated due to access path chosen by the Optimizer.

Here is the extract of the DB2 administration guide which will guide you how to generate the exact access path in test system with respect to production system.

SELECT DISTINCT 'UPDATE SYSIBM.SYSTABLESPACE SET NACTIVEF='
CONCAT STRIP(CHAR(NACTIVEF))
CONCAT',NACTIVE='CONCAT STRIP(CHAR(NACTIVE))
CONCAT ' WHERE NAME=''' CONCAT TS.NAME
CONCAT ''' AND DBNAME ='''CONCAT TS.DBNAME CONCAT''';'
FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TBL
WHERE TS.NAME = TSNAME
AND TBL.CREATOR IN (table creator_list)
AND TBL.NAME IN (table_list)
AND (NACTIVEF >=0 OR NACTIVE >=0);

==
SELECT 'UPDATE SYSIBM.SYSTABLES SET CARDF='
CONCAT STRIP(CHAR(CARDF))
CONCAT',NPAGES='CONCAT STRIP(CHAR(NPAGES))
CONCAT',PCTROWCOMP='CONCAT STRIP(CHAR(PCTROWCOMP))
CONCAT ' WHERE NAME='''CONCAT NAME
CONCAT ''' AND CREATOR ='''CONCAT CREATOR CONCAT''';'
FROM SYSIBM.SYSTABLES WHERE
CREATOR IN (creator_list)
AND NAME IN (table_list)
AND CARDF >= 0;
==
SELECT 'UPDATE SYSIBM.SYSINDEXES SET FIRSTKEYCARDF='
CONCAT STRIP(CHAR(FIRSTKEYCARDF))
CONCAT ',FULLKEYCARDF='CONCAT STRIP(CHAR(FULLKEYCARDF))
CONCAT',NLEAF='CONCAT STRIP(CHAR(NLEAF))
CONCAT',NLEVELS='CONCAT STRIP(CHAR(NLEVELS))
CONCAT',CLUSTERRATIO='CONCAT STRIP(CHAR(CLUSTERRATIO))
CONCAT',CLUSTERRATIOF='CONCAT STRIP(CHAR(CLUSTERRATIOF))
CONCAT' WHERE NAME='''CONCAT NAME
CONCAT ''' AND CREATOR ='''CONCAT CREATOR CONCAT''';'
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR IN (creator_list)
AND TBNAME IN (table_list)
AND FULLKEYCARDF >= 0;
==
SELECT 'UPDATE SYSIBM.SYSCOLUMNS SET COLCARDF='
CONCAT STRIP(CHAR(COLCARDF))
CONCAT',HIGH2KEY= X''' CONCAT HEX(HIGH2KEY)
CONCAT''',LOW2KEY= X''' CONCAT HEX(LOW2KEY)
CONCAT''' WHERE TBNAME=''' CONCAT TBNAME CONCAT ''' AND COLNO='
CONCAT STRIP(CHAR(COLNO))
CONCAT ' AND TBCREATOR =''' CONCAT TBCREATOR CONCAT''';'
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR IN (creator_list)
AND TBNAME IN (table_list)
AND COLCARDF >= 0;

SYSTABSTATS and SYSCOLDIST require deletes and inserts.
Delete statistics from SYSTABSTATS on the test subsystem for the specified tables
by using the following statement:
DELETE FROM (TEST_SUBSYSTEM).SYSTABSTATS
WHERE OWNER IN (creator_list)
AND NAME IN (table_list);

Use INSERT statements to repopulate SYSTABSTATS with production statistics that
are generated from the following statement:

SELECT 'INSERT INTO SYSIBM.SYSTABSTATS'
CONCAT '(CARD,NPAGES,PCTPAGES,NACTIVE,PCTROWCOMP'
CONCAT ',STATSTIME,IBMREQD,DBNAME,TSNAME,PARTITION'
CONCAT ',OWNER,NAME,CARDF) VALUES('
CONCAT STRIP(CHAR(CARD)) CONCAT ' ,'
CONCAT STRIP(CHAR(NPAGES)) CONCAT ' ,'
CONCAT STRIP(CHAR(PCTPAGES)) CONCAT ' ,'
CONCAT STRIP(CHAR(NACTIVE)) CONCAT ' ,'
CONCAT STRIP(CHAR(PCTROWCOMP)) CONCAT ' ,'
CONCAT '''' CONCAT CHAR(STATSTIME) CONCAT ''' ,'
CONCAT '''' CONCAT IBMREQD CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(DBNAME) CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(TSNAME) CONCAT ''' ,'
CONCAT STRIP(CHAR(PARTITION)) CONCAT ' ,'
CONCAT '''' CONCAT STRIP(OWNER) CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(NAME) CONCAT ''' ,'
CONCAT STRIP(CHAR(CARDF)) CONCAT ');'
FROM SYSIBM.SYSTABSTATS
WHERE OWNER IN (creator_list)
AND NAME IN (table_list);

==
Delete statistics from SYSCOLDIST on the test subsystem for the specified tables
by using the following statement:
DELETE FROM (TEST_SUBSYSTEM).SYSCOLDIST
WHERE TBOWNER IN (creator_list)
AND TBNAME IN (table_list);

Use INSERT statements to repopulate SYSCOLDIST with production statistics that
are generated from the following statement:

SELECT 'INSERT INTO SYSIBM.SYSCOLDIST '
CONCAT '(FREQUENCY,STATSTIME,IBMREQD,TBOWNER'
CONCAT ',TBNAME,NAME,COLVALUE,TYPE,CARDF,COLGROUPCOLNO'
CONCAT ',NUMCOLUMNS,FREQUENCYF) VALUES( '
CONCAT STRIP(CHAR(FREQUENCY)) CONCAT ' ,'
CONCAT '''' CONCAT CHAR(STATSTIME) CONCAT ''' ,'
CONCAT '''' CONCAT IBMREQD CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(TBOWNER) CONCAT ''' ,'
CONCAT '''' CONCAT STRIP(TBNAME) CONCAT ''','
CONCAT '''' CONCAT STRIP(NAME) CONCAT ''' ,'
CONCAT 'X''' CONCAT STRIP(HEX(COLVALUE)) CONCAT ''' ,'
CONCAT '''' CONCAT TYPE CONCAT ''' ,'
CONCAT STRIP(CHAR(CARDF)) CONCAT ' ,'
CONCAT 'X'''CONCAT STRIP(HEX(COLGROUPCOLNO)) CONCAT ''' ,'
CONCAT CHAR(NUMCOLUMNS) CONCAT ' ,'
CONCAT STRIP(CHAR(FREQUENCYF)) CONCAT ');'
FROM SYSIBM.SYSCOLDIST
WHERE TBOWNER IN (creator_list)
AND TBNAME IN (table_list);

Note about SPUFI:

If you use SPUFI to execute the preceding SQL statements, you might need to increase the default maximum character column width to avoid truncation.
Asterisks (*) appear in the examples to avoid having the semicolon interpreted as the end of the SQL statement. Edit the result to change the asterisk to a semicolon.

Access path differences from test to production: When you bind applications on the
test system with production statistics, access paths should be similar but still may
be different to what you see when the same query is bound on your production
system.

The access paths from test to production could be different for the following possible reasons:

The processor models are different.
The number of processors are different. (Differences in the number of processors can affect the degree of parallelism that is obtained.)
The buffer pool sizes are different.
The RID pool sizes are different.
Data in SYSIBM.SYSCOLDIST is mismatched. (This mismatch occurs only if some of the previously mentioned steps mentioned are not followed exactly).
The service levels are different.
The values of optimization subsystem parameters, such as STARJOIN, NPGTHRSH, and PARAMDEG (MAX DEGREE on installation panel DSNTIP8) are different.
v The use of techniques such as optimization hints and volatile tables are different.

Tools to help: If your production system is accessible from your test system, you
can use DB2 PM EXPLAIN on your test system to request EXPLAIN information
from your production system. This request can reduce the need to simulate a
production system by updating the catalog.

You can also use the DB2 Visual Explain feature to display the current
PLAN_TABLE output or the graphed access paths for statements within any
particular subsystem from your workstation environment. For example, if you have
your test system on one subsystem and your production system on another
subsystem, you can visually compare the PLAN_TABLE outputs or access paths
simultaneously with some window or view manipulation. You can then access the
catalog statistics for certain referenced objects of an access path from either of the
displayed PLAN_TABLEs or access path graphs.

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

Fileaid for DB2 Setup Error - FDBA623 CAF error 00F30034

2010-02-21T11:29:00.000+05:30

Hi,

There are certain times after installation of Fileaid for DB2, users get this kind of error.

"FDBA623 CAF error 00F30034 - Plan name unauthorized."

Normally as part of installation/upgrade of Fileaid for DB2, DBA does the bind of the new plan.

As a security measure, DB2 does not allow the users to allow the access to the plan unless Execute privilege is granted to it explicitly.

Hence if the DBA forgets to grant Execute privilege, the uses will get the message like "FDBA623 CAF error 00F30034 - Plan name unauthorized." when one tries to connect to fileaid for DB2.

As a best practice for DBA, it is recommended that as soon as the bind finishes, he should give the require privileges to the users.

In this Example Granting Execute privilege for plan FDPN610 and FDOM610 to Public could have avoided the error message mentioned in subject line.

Cheers..
Prakash C Singh
IBM Certified DB2 DBA

Display current threads when you get DB2 Not Operational message

2010-02-07T21:25:00.000+05:30

Hi Friends,

Here is one typical problem you usually get when you tried to Stop DB2 subsystem. You just fired the command to stop the DB2 subsystem, but DB2 is not coming down.

After having a thought...you may assume there are certain active dedicated threads from a started task (DB2 related application or any job). The interesting fact is when you try to DISPLAY Threads through DB2 Panels to execute DB2 commands, it will give you a Message like DB2 Not Operational for the Subsystem.

Here is the trick:
If you know the Character for DB2 subsystem (You can get it from Zparms), you can fire the Display command from console.

For example in spool you can type

/+DIS THD(*)

Here '+' is the character for the Subsystem.

Now you can close the started task or kill the job to proceed further.

Regards,

Prakash Singh

IBM Certified DB2 DBA

Tape backup File not found for DSN1COPY job

2009-10-27T11:55:00.000+05:30

Hi,

Here is one of the common problems that many of you might have faced.

Problem: One of your development guys ask to restore the table to a old backup image. Now you got the tape file name from the SYSIBM.SYSCOPY, but when you put this file name in 3.4, you are not able to find the file. This is just a JCL problem rather than a Database problem.

Solution:

You can still use this file (like in job for DSN1COPY), if it is not overwritten by any other job. You cannot find this file as this might have uncatalogued by the policy you have in your system. You can use this file by providing additional information in JCL DD statement.

The additional information are:
1) Unit info
2) VOLUME Serial Number
3) File sequence number i.e LEBEL parameter

Now get your job done!!!

Cheers!!
Prakash C Singh
IBM Certified Database Administrator

DSNE106E PLAN DSNESPCS NOT AUTHORIZED FOR SUBSYSTEM

2009-09-12T17:36:00.000+05:30

Hello My Blog readers....

Here is another classic problem and it's solution for you...

What to do when many people are getting the error message like "DSNE106E PLAN DSNESPCS NOT AUTHORIZED FOR SUBSYSTEM DSNA AND AUTH ID TSK10"

Here you go..

Spufi uses 2 plans for execution of SQL statements on demand.
One is DSNESPRR and other one is DSNESPCS.
DSNESPRR plan uses Repeatable read where as DSNESPCS uses Cursor stability isolation level.

DSNESPRR plan is the default for Spufi.

RR isolation is highest in restriction level and provides least concurrency. This may a disaster in production environment if someone accesses the table thru DSNESPRR in spufi and went for a break without coming out from the output panel. This may lock the Table.

Solution:
1) Never allow people to use RR isolation level in Spufi. (this is the Best Practice)
2) Rebind DSNESPRR with an isolation level of CS, thereby eliminating the chance that people will use spufi with RR
3) FREE DSNESPRR plan so that nobody will access this plan.

Note: To Resolve the authorization problem mentioned at the begining of the blog.

Always grant EXECUTE on DSNESPCS to PUBLIC.

Cheers..
Prakash C. Singh
IBM Certified DB2 DBA

Permission Required for DB2 Subsystem

2009-08-29T19:47:00.000+05:30

All of us must have known that we require different level of access for different objects in the database. But did you think what is the access required to connect to a DB2 subsystem. What I am saying here is; you need to have permission to connect to a DB2 subsystem first and then you need second level of privileges to access different objects in the database.

Here all that are happening when an ID is tried to access any table.
1) RACF verifies whether this ID is authorized for DB2 resources.
2) There is a resource class for DB2 called DSNR which is contained in the RACF descriptor table.
3) There should be a profile defined as a member of class DSNR.
4) The ID or the group contains this ID should be included in the profile.
5) If the ID is included, then the thread is connected to DB2 Subsystem
6) Once connected to DB2, it checks in catalog tables to verify the intended access.

Following commands can be executed by RACF admin to let the user run batch jobs:
PERMIT DSN1.BATCH CLASS(DSNR) ID(TSG10) ACCESS(READ)
For taking away the access:
PERMIT DSN1.BATCH CLASS(DSNR) ID(TSG10) ACCESS(NONE)

This can be done through RACF pannels too...

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

Creating New Index on BIG Table

2009-07-21T10:38:00.000+05:30

Hi,

As always Prakash promised to back with the solutions with respect to practical scenario.
Here is another one for you.

Task: You are asked to create an Index in an existing table which is very huge.

Problem: You might face this issue. When you fired this SQL through batch spufi, it will run for minutes and at last it will abend with -904 return code saying 4K page is not available.
Investigation: You will find many messages in DSN?MSTR and DSN?DBM1 whcih says the temporary tablespace DSNDB07. unable to extend itself as it might reached it's limit of 2GB.

Solution: There are some fact you must know while creating index in a big table.
When you are creating the index, by default it will try to build the index instantly. CREATE INDEX statement uses RDS Sort to sort the keys. RDS Sort is very efficient for smaller tables. But when there is a huge table you must defer the creation of index by specifying DEFER YES in CREATE INDEX statement so that index for the table is registered in the DB2 catalog but the new index is in Rebuild pending status giving warning while creation of index.
REBUILD INDEX uses the the EXTERNAL SORT which outperforms the RDS Sort if the table size is significant. Even the performance of REBUILD INDEX is improved with parallel partition key extract and parallel index build.

By deferring the index creation and rebuilding the index, your index creation is over in few minutes and you will not get any extend failure message in MSTR started task.

Cheers..
Prakash C. Singh
IBM Certified DB2 DBA

Understanding DSN1CHKR Utility

2009-07-14T18:56:00.000+05:30

Hello Everybody!!

Sometimes it is worth to verify the intigrity of DB2 directory and Catalog table spaces. DSN1CHKR utility is designed for this.
This utility checks the specified tablespace for

1) Broken Links
2) Broken Hash Chanins
3) Orphan records

This is a service aid and a diagonistic tool which executes outside the control of DB2.

Restriction:
You should not DSN1CHKR in a tablspace while it is active under DB2. Tablespace should be in STOPPED status before running this utility.

Privilege Required:
RACF auth required on the specified tablespace. No other privileges required to run this utility

Recommendation:
Copy the stopped tablespace into a similar VSAM dataset with DB2 naming convention and then run the utility on the copy.

Never run this utility on following tablespaces:

DSNDB06.SYSCOPY
DSNDB06.SYSDDF
DSNDB06.SYSGPAUT
DSNDB06.SYSPKAGE
DSNDB06.SYSSTATS
DSNDB06.SYSSTR
DSNDB06.SYSUSER
DSNDB01.SCT02
DSNDB01.SPT01
DSNDB01.SYSLGRNX
DSNDB01.SYSUTILX

Sample Job Step:

//**********************************************************************000
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************000
//**********************************************************************000
//DH000701 EXEC PGM=DSN1CHKR,PARM='FORMAT' 000
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 000
//SYSPRINT DD SYSOUT=(2,,CRTL) 000
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB01.DBD01.I0001.A001,DISP=SHR 000
//**********************************************************************000
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************001
//**********************************************************************001
//DH000702 EXEC PGM=DSN1CHKR,PARM='FORMAT' 001
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 001
//SYSPRINT DD SYSOUT=(2,,CRTL) 001
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSDBASE.I0001.A001,DISP=SHR 001
//**********************************************************************001
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************001
//**********************************************************************001
//DH000703 EXEC PGM=DSN1CHKR,PARM='FORMAT' 001
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 002
//SYSPRINT DD SYSOUT=(2,,CRTL) 002
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSDBAUT.I0001.A001,DISP=SHR 002
//**********************************************************************002
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************002
//**********************************************************************002
//DH000704 EXEC PGM=DSN1CHKR,PARM='FORMAT' 002
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 002
//SYSPRINT DD SYSOUT=(2,,CRTL) 002
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSGROUP.I0001.A001,DISP=SHR 002
//**********************************************************************003
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************003
//**********************************************************************003
//DH000705 EXEC PGM=DSN1CHKR,PARM='FORMAT' 003
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 003
//SYSPRINT DD SYSOUT=(2,,CRTL) 003
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSPLAN.I0001.A001,DISP=SHR 003
//**********************************************************************003
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************003
//**********************************************************************003
//DH000706 EXEC PGM=DSN1CHKR,PARM='FORMAT' 004
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 004
//SYSPRINT DD SYSOUT=(2,,CRTL) 004
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSVIEWS.I0001.A001,DISP=SHR 004
//*

This is what IBM specified for the Sample JCL:
//YOUR JOBCARD
//*
//JOBCAT DD DSNAME=DSNCAT1.USER.CATALOG,DISP=SHR
//STEP1 EXEC PGM=IDCAMS
//********************************************************************
//* ALLOCATE A TEMPORARY DATA SET FOR SYSDBASE *
//********************************************************************
//SYSPRINT DD SYSOUT=A
//SYSUDUMP DD SYSOUT=A
//SYSIN DD *
DELETE -
(TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001) -
CATALOG(DSNCAT)DEFINE CLUSTER -
( NAME(TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001) -
NONINDEXED -
REUSE -
CONTROLINTERVALSIZE(4096) -
VOLUMES(XTRA02) -
RECORDS(783 783) -
RECORDSIZE(4089 4089) -
SHAREOPTIONS(3 3) ) -
DATA -
( NAME(TESTCAT.DSNDBD.TEMPDB.TMPDBASE.I0001.A001)) -
CATALOG(DSNCAT)
/*
//STEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//********************************************************************
//* STOP DSNDB06.SYSDBASE *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=A
//SYSPRINT DD SYSOUT=A
//SYSTSIN DD *
DSN SYSTEM(V61A)
-STOP DB(DSNDB06) SPACENAM(SYSDBASE)END
/*
//STEP3 EXEC PGM=DSN1COPY,PARM=(CHECK)
//********************************************************************
//* CHECKSYSDBASE AND RUN DSN1COPY *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=A
//SYSUT1 DD DSN=DSNCAT.DSNDBC.DSNDB06.SYSDBASE.I0001.A001,DISP=SHR
//SYSUT2 DD DSN=TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001,DISP=SHR
/*
Example 2:
To Run DSN1CHKR on an actual table space. STEP1 stopsdatabase DSNDB06 with the STOP DATABASE command. STEP2 runsDSN1CHKR on the target table space; its output is identical to the output inExample 1. STEP3 restarts the database with the START DATABASE command.
//STEP4 EXEC PGM=IKJEFT01,DYNAMNBR=20
//********************************************************************
//* START DSNDB06.SYSDBASE *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=A//SYSPRINT DD SYSOUT=A
//SYSTSIN DD *DSN SYSTEM(V61A)
-START DB(DSNDB06) SPACENAM(SYSDBASE)END
/*
//STEP5 EXEC PGM=DSN1CHKR,PARM='MAP=RID(00000201,06,00000B01,06)',
// COND=(4,LT)
//********************************************************************
//* CHECKLINK S OF SYSDBASE *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=A
//SYSUT1 DD DSN=TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001,DISP=SHR
/*

Thanks for reading this blog

Cheers
Prakash C. Singh
IBM Certified DB2 DBA

Calculating size of Database or Table in DB2 for z/OS

2009-07-09T20:01:00.000+05:30

Hi All,

Sometimes people around you ask simple question:
What is the size of a particular database or a particular table?

How will you calculate???

Option 1: Get the total number of rows multiplied by row length.. What if you have variable columns...
Option 2: Get the total number of pages allocated for individual tables and multiply by 4K. What if there are large number of tables in a databases??
Option 3: List the underlying VSAM dataset of tablespace and indexspace. Get the Hi Used RBA and Low Used RBA and calculate the difference. Same Constrains as avove option.

Here you go ...
IBM provided a stand alone alone utility which will calculate the space for you within minutes.
The Utility name is STOSPACE. All you need to give the input is the Storage Group of the underlying dataset of spacific table or index. This will update the catalogue tables of DB2 regarding space information from where you can get by firing simple Select query.

Authorization required:
STOSPACE privilege SYSCTRL or SYSADM authority

This JCL step should be like:
//*---------------------------------------------------------------------
//* STOSPACE UTILITY TO UPDATE INFORMATION IN DSN1 CATALOG
//*---------------------------------------------------------------------
//DH011510 EXEC DSNUPROC,SYSTEM=DSN1,UID='TSG11510',UTPROC=''
//*
//SYSPRINT DD SYSOUT=*
//DSNUPROC.SYSIN DD *
STOSPACE STOGROUP (GDSN1)
//*

After Executing the utility, you may get following messages in SYSPRINT
Information you get in SYSPRINT
DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DH011510
DSNU050I DSNUGUTC - STOSPACE STOGROUP(GDSN1)
DSNU640I - DSNUGSPC - DATA SET= 0 OF TABLESPACE= SPRODDET IN DATABASE= DISSUES2 HAS BEEN MIGRATED BY HSM
DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

After successful execution of STOSPACE utility you can query the catalogue tables as below and add the tablespace and index's space value to calculate the size of a database.
Calculating all index space in a database:
SELECT DBNAME, SUM(SPACE)
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = 'DTSG10'
GROUP BY DBNAME;

Calculating all index space in a database:
SELECT DBNAME, SUM(SPACE)
FROM SYSIBM.SYSINDEXES
WHERE DBNAME = 'DTSG10'
GROUP BY DBNAME;

You can query SYSIBM.SYSSTOGROUP to findout total space in KB aquired by a particular storage group.

More about STOSPACE Utility:

STOSPACE output stored in columns as mentioned below:

The output from STOSPACE consists of updated values in the columns and tables in the following list. In each case, an amount of space is given in kilobytes (KB).
SPACE - in SYSIBM.SYSINDEXES shows the amount of space that is allocated to indexes. If the index is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSTABLESPACE shows the amount of space that is allocated to table spaces. If the table space is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSINDEXPART shows the amount of space that is allocated to index partitions. If the partition is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSTABLEPART shows the amount of space that is allocated to table partitions. If the partition is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSSTOGROUP shows the amount of space that is allocated to storage groups. STATSTIME in SYSIBM.SYSSTOGROUP shows the timestamp for the time at which STOSPACE was last executed.

Note: If the value is too large to fit in the SPACE column, the SPACEF column is updated.

Concurrency and compatibility for STOSPACE:
STOSPACE does not set a utility restrictive state on the target object.
STOSPACE can run concurrently with any utility on the same target object. However, because STOSPACE updates the catalog, concurrent STOSPACE utility jobs or other concurrent applications that update the catalog might cause timeouts and deadlocks.

Terminating or restarting STOSPACE:
You can terminate a STOSPACE utility job with the TERM UTILITY command if you have submitted the job or have SYSOPR, SYSCTRL, or SYSADM authority.
You can restart a STOSPACE utility job, but it starts from the beginning again.

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

Performance Consideration of SQL and Index for DB2 on z/OS

2009-06-28T14:33:00.000+05:30

Hi,
Here is Prakash Back ... This is with interesting one which is related to Performance which is one of my favorite topic. This post is also has the link with my previous post as well related to Explain the SQL statements.
Here are some eye openers:
1) Matching Index scan: (MATCHCOLS > 0)
This comes into picture when our predicates match either leading index key columns or all of them hence give us the filtering criteria for specific index and data pages. If it is able to filter high number of records, then this type of access path is efficient.

There are 3 types of predicates:
1. Index Predicates
a. Matching predicates
b. Index screening predicates
2. Stage 1 predicates - this applied first to filter out most of the records enhancing query performance
3. Stage 2 predicates - This is applied to the rows returned from stage 1 predicates

From DB2 point of view a. first all equal predicates/ IS NULL, b. Then all Range Predicates/ IS NOT NULL c. then at last all other predicates are applied to the SQL Query.
Therefore developer should code most rstrictive predicates first.

Index Screening:
This predicates are specified on index-key columns but are not part of matching columns. In other words, they are not leading columns of the index. They used to search the index first before going to the data pages.

Nonmatching Index Scan: (ACCESSTYPE = I and MATCHCOLS = 0)
No matching columns are in index, hence all the index keys must be examined.
Sometimes it provides an efficient access path if the path is index only and index is smaller than the tablespace.

IN-list index scan: (ACCESSTYPE = N)
This is a special case of matching index scan. This is equivalent to matching equal predicate.
Exception:noncorrelated IN Subquery or in MX access or list prefetch.

Multiple-index access: (ACCESSTYPE is M, MX, MI, MU)
Multiple index access is table access by more than one index.
RID lists are constructed for each if the index and final list is retrieved after AND/OR operation. This type of access is extention to List Prefetch.

One-Fetch access: (ACCESSTYPE = I1)
This is required to retrive only 1 row. For example Max/Min column function. Most efficient access path.

Index Only Access: (INDEXONLY = Y)
If all the information in a query is available in index itself, it only access the index.

Equal Unique Index (MATCHCOLS = Number of Index Columns)
This guarantees the excat 1 row retrieval. This is next most efficient access path to One-Fetch access.

Other Considerations:
Avoiding Sort:
1) DISTINCT sort can be avoided by by using Unque index
2) ORDER BY, GROUP BY sort can be avoided by ordering index by ASC/DESC order
3) OPTIMIZE FOR n ROWS can eliminate most expensive sort with ordered index

Below information are spefically for Dynamic SQL:
Dynamic SQL performance is key to many applications.

Few things that can be considered are:
1) Reorganize DB2 Catalogue
2) Reorganize and Runstat the application tablr and index space
3) Use dynamic statement caching
4) SET CURRENT DEGREE = 'ANY' for parallelism

Query Parallelism:
This is less than 1% additional CPU overhead for long running queries and less than 10% for short running queries.

I/O and CPU Parallelism: (PARALLELISM_MODE = I or O)
The preffered method is CPU parallelism. It can be decided at both Bind time (DEGREE = ANY) and Runtime. If it is not chosen at Bind time, it can be chosen at runtime. Even if it is chosen at bind time, it may not be used at Runtime. It all depends on the current environment when the query will run.

Queries best suited for Parallelism:
1. Long running, read only queries
2. Tablespace scan
3. Joins
4. Nested loops
5. Merge scans
6. Hybrid Join
7. Sorts
8. Aggregate functions

Prallelism should not considered for:
1. Queries that materialized views
2. Queries using direct row access
3. Queries that perform materialization because of nested table expressions
4. Queries performing a merge-scan join of more than one column

Sysplex Parallelism not to be considered:
1) Queries with list prefetch and multiple index access
2) Queries accessing LOB data

Other Considerations:
1) Can not be considered if system is already CPU constrained
2) Can not be used when a CURSOR is defined WITH HOLD.

Sysplex Query Parallelism: (PARALLELISM_MODE = X)
1. Complex query to run across multiple members in a data sharing group of multi tasking env
2. Best used with ISOLATION level UR to avoid excess lock propagation

The information gives some eye sight regarding Performace.

See all you in my next blog .. Till then happy tunning...

Cheers....
Prakash C. Singh
IBM certified DB2 DBA.

How to EXPLAIN your SQL query

2009-04-25T20:04:00.000+05:30

Hi,

In continuation of my discussion regarding Plan and DSN_STATEMNT table, I am now giving you the code thru which you can EXPLAIN your SQL statements and find the parameters populated in both of the tables mentioned above. This will help you in your analysis of performance regarding SQL you are going to run.

Consideration:
1) You should have all the objects (Referenced in your SQL) created in the same env where you will Explain the SQL.
2) You tables must be runstated before running Explain
3) You can replace host variables with any Char/Int Literals in the SQL statements.
4) Make sure your PLAN_TABLE and DSN_STATEMNT_TABLE present in the same Env.

Here is one example of SQL code regarding How to Explain your query.
DELETE FROM PLAN_TABLE WHERE QUERYNO = 99999;
COMMIT;

EXPLAIN PLAN SET QUERYNO = 99999 FOR

SELECT B.STORE_NO, B.STOCK_ITEM, B.DELIVERY_DATE
FROM V1FOGH01 B

WHERE DELIVERY_DATE = (SELECT MAX(DELIVERY_DATE)
FROM V1FOGH01 A
WHERE A.STORE_NO = B.STORE_NO
AND A.STOCK_ITEM = B.STOCK_ITEM
AND A.QTY_TYPE ='7'
AND A.DELIVERY_DATE <= CURRENT DATE)
AND B.QTY_TYPE ='7';

SELECT * FROM PLAN_TABLE
WHERE QUERYNO = 99999
ORDER BY TIMESTAMP,QUERYNO,QBLOCKNO,PLANNO,MIXOPSEQ;

SELECT * FROM DSN_STATEMNT_TABLE
WHERE QUERYNO = 99999;
========

I have Added the Select statement so that immediately you can view the result. You can run this statement in Batch as well as Online (Spufi).

I have already discussed some important columns of PLAN_TABLE and DSN_STATEMNT_TABLE. So Enjoy while analysing the values you got for these 2 tables.

Regards,
Prakash C Singh
IBM Certified DB2 DBA.

How to read DSN_STATEMNT_TABLE

2009-03-15T22:35:00.000+05:30

Hi,

While analyzing the SQL queries, it is really worth to calculate the cost of the query. This cost is populated into DSN_STATEMNT_TABLE by the explain SQL statement.

Here are some important column info

1) QUERYNO:A no. identifying the statement being explained.

2) STMT_TYPE:

SELECT - Select

INSERT - Insert

UPDATE - Update

DELETE - Delete

SELUPD - Select with FOR UPDATE OF

DELCUR - DELETE WHERE CURRENT OF CURSOR

UPDCUR - UPDATE WHERE CURRENT OF CURSOR

3) COST_CATEGORY:

A - DB2 has enough information to calculate cost without using default values

B - DB2 forced to use default values. See REASON column why DB2 choose default values

4) PROCMS:The estimated processor cost in milliseconds for SQL statements. This is the most important column to look at while analyzing the SQL query

5) PROCSU:The estimated processor cost in Service Units for SQL statements. This is also worth to look at.

6) REASON:Gives the reason why it is in cost category B.

Having ClauseHost Variables - Also Parameter Markers or Special Registers

REFERENTIAL CONSTRAINTS - CASCADE and SET NULL exist for DELETE statement

TABLE CARDINALITY - Cardinality statistics missing

UDF TRIGGERS - defined on target table of INSERT, UPDATE or DELETE statement

Cheers...

Prakash C Singh

IBM Certified DB2 DBA

How to read PLAN_TABLE after EXPLAIN

2009-03-15T16:23:00.000+05:30

Hi,

Here are some information you should be looking at various columns of the PLAN_TABLE. This will help you analysing the SQL queries and it's various attributes.

1) QUERYNO: A number identifying the statement being explained.

2) METHOD:
0 - First table accessed, continuation of previous table accessed or not used
1 - Nested Loop Join
2 - Merge Scan Join
3 - Sorts required by ORDER BY, GROUP BY, SELECT DISTINCT, uNION
4 - Hybrid Join

3) ACCESSTYPE:
I - By an Index
I1 - One fetch Index Scan
M - Multiple index scan
MX - By Index mentioned in ACCESSNAME
MI - Intersection of Multiple indexes
MU - Union of multiple indexes
N - Index scan when matching predicated in IN keyword
R - Tablespace scan
RW - Work file scan of a materialized user defined table funtion
T - By a spare index - Star join work files
V - By buffers for an INSERT statement within a SELECT
Blank - NA

4) INDEXONLY:
Whether access to an Index alone is enough to carry out the step

5) TSLOCKMODE:
IS - Intent Share Lock
IX - Intent Exclusive lock
S - Share Lock
U - Update Lock
X - Exclusive Lock
SIX - Share with Intent Exclusive lock
N - UR Isolation: No Lock
NS - For CS, RS, RR an S Lock
NIS - For CS, RS, RR an IS Lock
NSS - For CS, RS an IS Lock and for RR an S Lock
SS - For UR, CS, RS an IS Lock and for RR an S Lock

6) PREFETCH:
S - Pure Sequential
L - thru a page List
D - Optimizer expects dynamic prefetch
Blank - Unknown at bind time or NA

7)ACCESS_DEGREE:
Number of Parallel tasks or operations activated by a Query
0 - if there is a host variable

8) PARALLELISM_MODE:
I - Query I/O parallelism
C - Query CP parallelism
X - Sysplex query parallelism

9) PAGE_RANGE:
Whether the table qualifies for page-range(scan only the partitions those are needed)
Y - Yes
Blank - No

10) JOIN_TYPE:
F - Full Outer Join
L - Left Outer Join
S - Star Join
Blank - Inner Join or No join
Note: Right Outer Join always converted to Left Outer Join

11) WHEN_OPTIMIZE:
Blank: At bind time, using a default filter factor for any host variables, parameter markers or special registers
B - Above facts + Bind option REOPT (ALWAYS) or REOPT (ONCE) must be specified.
R - At Runtime, using input variables, parameter markers or special registers. Bind option REOPT (ALWAYS) or REOPT (ONCE) must be specified.

12) QBLOCK_TYPE:
SELECT - Select
INSERT - Insert
UPDATE - Update
DELETE - Delete
SELUPD - Select with FOR UPDATE OF
DELCUR - DELETE WHERE CURRENT OF CURSOR
UPDCUR - UPDATE WHERE CURRENT OF CURSOR
CORSUB - Correlated Subquery
NCOSUB - NonCorrelated Subquery
TABLEX - Table Expression
TRIGGER - WHEN caluse on CREATE TRIGGER
UNION - Union
UNIONA - Union All

13) PRIMRY-ACCESSTYPE:
D - Direct Row access
Blank - No Direct Row access

14) TABLE_TYPE:
B - Buffers for an INSERT stament within a SELECT
C - Common Table Expression
F - Table Function
M - Materialized Qery Table
Q - Temp intermediate table(Not Materialized), name of the viewor nested table expression, Contains a UNION ALL where materialization was vertual not actual.
RB - Recursive Common Table Expression
T - Table
W - Work file (Materialized)

15) TABLE_ENCODE:
A - ASCII
E - EBCDIC
U - Unicode
M - when multiple CCSID is in one table

We will be discussing more on these on my post regarding Performance.

Cheers,
Prakash C Singh
IBM Certified DB2 DBA

EXPLAIN Tables (PLAN_TABLE ,DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE)

2009-03-08T12:48:00.000+05:30

Just mangaed to get some time to post this information.

This is helpful while creating Explain tables to anayse a badly performing query.

I am currently working on DB2 V7 product.

There is one member in the installation library of DB2 production where you will extract the defination of EXPLAIN tables (PLAN_TABLE ,DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE). Also you can see the defination of the Optimizer hint tables too.

Here is the member you should look for D710.DSN1.SDSNSAMP(DSNTESC)

For the benefit of many I am putting the defination of these tables here

CREATE TABLE SCHEMANM.PLAN_TABLE
( "QUERYNO" INTEGER NOT NULL,
"QBLOCKNO" SMALLINT NOT NULL,
"APPLNAME" CHAR(8) NOT NULL,
"PROGNAME" CHAR(8) NOT NULL,
"PLANNO" SMALLINT NOT NULL,
"METHOD" SMALLINT NOT NULL,
"CREATOR" CHAR(8) NOT NULL,
"TNAME" CHAR(18) NOT NULL,
"TABNO" SMALLINT NOT NULL,
"ACCESSTYPE" CHAR(2) NOT NULL,
"MATCHCOLS" SMALLINT NOT NULL,
"ACCESSCREATOR" CHAR(8) NOT NULL,
"ACCESSNAME" CHAR(18) NOT NULL,
"INDEXONLY" CHAR(1) NOT NULL,
"SORTN_UNIQ" CHAR(1) NOT NULL,
"SORTN_JOIN" CHAR(1) NOT NULL,
"SORTN_ORDERBY" CHAR(1) NOT NULL,
"SORTN_GROUPBY" CHAR(1) NOT NULL,
"SORTC_UNIQ" CHAR(1) NOT NULL,
"SORTC_JOIN" CHAR(1) NOT NULL,
"SORTC_ORDERBY" CHAR(1) NOT NULL,
"SORTC_GROUPBY" CHAR(1) NOT NULL,
"TSLOCKMODE" CHAR(3) NOT NULL,
"TIMESTAMP" CHAR(16) NOT NULL,
"REMARKS" VARCHAR(254) NOT NULL,
"PREFETCH" CHAR(1) NOT NULL WITH DEFAULT,
"COLUMN_FN_EVAL" CHAR(1) NOT NULL WITH DEFAULT,
"MIXOPSEQ" SMALLINT NOT NULL WITH DEFAULT,
"VERSION" VARCHAR(64) NOT NULL WITH DEFAULT,
"COLLID" CHAR(18) NOT NULL WITH DEFAULT,
"ACCESS_DEGREE" SMALLINT,
"ACCESS_PGROUP_ID" SMALLINT,
"JOIN_DEGREE" SMALLINT,
"JOIN_PGROUP_ID" SMALLINT,
"SORTC_PGROUP_ID" SMALLINT,
"SORTN_PGROUP_ID" SMALLINT,
"PARALLELISM_MODE" CHAR(1),
"MERGE_JOIN_COLS" SMALLINT,
"CORRELATION_NAME" CHAR(18),
"PAGE_RANGE" CHAR(1) NOT NULL WITH DEFAULT,
"JOIN_TYPE" CHAR(1) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" CHAR(8) NOT NULL WITH DEFAULT,
"IBM_SERVICE_DATA" VARCHAR(254) NOT NULL WITH DEFAULT,
"WHEN_OPTIMIZE" CHAR(1) NOT NULL WITH DEFAULT,
"QBLOCK_TYPE" CHAR(6) NOT NULL WITH DEFAULT,
"BIND_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"OPTHINT" CHAR(8) NOT NULL WITH DEFAULT,
"HINT_USED" CHAR(8) NOT NULL WITH DEFAULT,
"PRIMARY_ACCESSTYPE" CHAR(1) NOT NULL WITH DEFAULT,
"PARENT_QBLOCKNO" SMALLINT NOT NULL WITH DEFAULT,
"TABLE_TYPE" CHAR(1)
)
IN DBNAME.TABLESPC
CCSID EBCDIC;

CREATES THE V7.1 SAMPLE DSN_FUNCTION_TABLE

CREATE TABLE SCHEMANM.DSN_FUNCTION_TABLE
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"QBLOCKNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" CHAR(8) NOT NULL WITH DEFAULT,
"PROGNAME" CHAR(8) NOT NULL WITH DEFAULT,
"COLLID" CHAR(18) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" CHAR(8) NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"SCHEMA_NAME" CHAR(8) NOT NULL WITH DEFAULT,
"FUNCTION_NAME" CHAR(18) NOT NULL WITH DEFAULT,
"SPEC_FUNC_NAME" CHAR(18) NOT NULL WITH DEFAULT,
"FUNCTION_TYPE" CHAR(2) NOT NULL WITH DEFAULT,
"VIEW_CREATOR" CHAR(8) NOT NULL WITH DEFAULT,
"VIEW_NAME" CHAR(18) NOT NULL WITH DEFAULT,
"PATH" VARCHAR(254) NOT NULL WITH DEFAULT,
"FUNCTION_TEXT" VARCHAR(254) NOT NULL WITH DEFAULT
)
IN DBNAME.TABLESPC
CCSID EBCDIC;

CREATES THE V7.1 SAMPLE DSN_STATEMNT_TABLE

CREATE TABLE SCHEMANM.DSN_STATEMNT_TABLE
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" CHAR(8) NOT NULL WITH DEFAULT,
"PROGNAME" CHAR(8) NOT NULL WITH DEFAULT,
"COLLID" CHAR(18) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" CHAR(8) NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"STMT_TYPE" CHAR(6) NOT NULL WITH DEFAULT,
"COST_CATEGORY" CHAR(1) NOT NULL WITH DEFAULT,
"PROCMS" INTEGER NOT NULL WITH DEFAULT,
"PROCSU" INTEGER NOT NULL WITH DEFAULT,
"REASON" VARCHAR(254) NOT NULL WITH DEFAULT
)
IN DBNAME.TABLESPC
CCSID EBCDIC;

We will discuss more on this in my next post.

Cheers,
Prakash C Singh
IBM Certified DB2 DBA

Printing zPARMs of DB2 using IBM-supplied stored procedure DSNWZP

2008-12-23T11:48:00.000+05:30

There are times where it is needed to check the Zparm values of DB2. It required to verify the change(before and after) you have made or just to check the existing values.

If you have any tool, then you can easily verify it. One more place is there to look at these values is D710.SDSNSAMP(DSNTIJUZ). This job actually sets the parameter values. you can get the latest info if the member is used in Edit mode while submitting this job last time. Otherwise you need to configure a IBM supplied Stored procedure "DSNWZP" for this.

You can find this in SDSNSAMP:

The new job DSNTEJ6Z prepare and invoke the sample program DSN8ED7 which is a sample caller of the stored procedure DSNWZP, a DB2 provided stored procedure that returns the current settings of your DB2 subsystem parameters.

DSN8ED7 formats the results from DSNWZP in a report format and prints it.

Please note that before running DSN8ED7 the stored procedure DSNWZP must exist on your DB2 subsystem. Installation job DSNTIJSG creates and binds the DB2 provided stored procedure DSNWZP.

Calling DSNWZP strored procedure from ReXX.
===================================
Here is the sample code I got from a very good post at IDUG. After execution of this rexx it will open a target dataset to show the zPARM values automatically in a formatted manner.
/****************************** REXX ********************************/
/** */
/** EXEC : DISPZPRM */
/*I PURPOSE : REXX TO CALL THE DSNWZP STORED PROCEDURE AND PARSE */
/*I THE OUTPUT FIELD INTO A READABLE FILE. THROW YOU */
/*I INTO EDIT OF THE OUPUT DATASET SO YOU CAN FIND / SORT */
/*I OR DO WHATEVER YOU WANT. */
/** */
/**********************************************************************/
ARG SSID
IF SSID = '' THEN SSID = 'DSN2'
PROCNAME = 'DSNWZP '
STATUS = MSG('OFF')
ADDRESS TSO
"FREE DDNAME(RPTOUT)"
CALL LISTDSI ( ZPARM.TXT DIRECTORY NORECALL )
IF SYSREASON = 9 THEN "HDELETE ZPARM.TXT WAIT "
ELSE "DELETE ZPARM.TXT "
"ALLOC FI(RPTOUT) DA(ZPARM.TXT)
NEW LRECL(500) SP(5 2) CYL RECFM(F B) DSORG(PS) "
"FREE DDNAME(RPTOUT)"
ADDRESS TSO "SUBCOM DSNREXX"
IF RC = 1
THEN S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT " SSID
IF SQLCODE <> 0
THEN CALL SQLERROR

/* SET UP SQLDA FOR CALL TO DSNWZP */

INSQLDA.SQLD = 1
INSQLDA.1.SQLTYPE = 449 /* VARCHAR */
INSQLDA.1.SQLLEN = 32000
INSQLDA.1.SQLIND = 0
INSQLDA.1.SQLDATA = ' '

/* CALL PROCEDURE DSNWZP */

ADDRESS DSNREXX
"EXECSQL CALL :PROCNAME USING DESCRIPTOR :INSQLDA "

IF SQLCODE = 0 THEN DO

ADDRESS ISPEXEC
"LMINIT DATAID(DATAIDX) DATASET(ZPARM.TXT) ENQ(MOD) "
"LMOPEN DATAID("DATAIDX") OPTION(OUTPUT) "

RESULT = INSQLDA.1.SQLDATA
PARMCOUNT = 1
I = POS(X2C(404025),RESULT)
DO WHILE ( I <> 0)
RESULT = SUBSTR(RESULT,4)
NI = POS(X2C(404025),RESULT)
IF NI = 0 THEN NI = LENGTH(RESULT) + 1
ZPARM.PARMCOUNT = SUBSTR(RESULT,1,NI-1)
ZPARM.0 = PARMCOUNT
RESULT = SUBSTR(RESULT,NI)
I = POS(X2C(404025),RESULT)
END

DO I = 1 TO ZPARM.0
RECVAL = ZPARM.I
CALL PRCS_LINE
ADDRESS ISPEXEC
"LMPUT DATAID("DATAIDX") MODE(INVAR)
DATALOC(RECVAL) DATALEN(500) "
END
ADDRESS ISPEXEC
"LMCLOSE DATAID("DATAIDX")"
"EDIT DATAID("DATAIDX")"
"LMFREE DATAID("DATAIDX")"
ADDRESS TSO
"DELETE ZPARM.TXT "
END
ELSE DO
SAY 'UNEXPECTED SQLCODE ON CALL TO DSNWZP '
SAY 'SQL CODE = ' SQLCODE
END
RETURN 0

/* TAKE THE VALUE AND SPACE IT OUT AND REARRANGE IT FOR DISPLAY */
/* IF YOU DO NOT LIKE THE ORDER SIMPLY CHANGE THE LAST LINE */
PRCS_LINE:
ORECVAL = RECVAL
L = POS('/',RECVAL)
W1 = SUBSTR(LEFT(RECVAL,L-1),1,9)
WR = SUBSTR(RECVAL,L+1)
L = POS('/',WR)
W2 = SUBSTR(LEFT(WR,L-1),1,9)
WR = SUBSTR(WR,L+1)
L = POS('/',WR)
W3 = SUBSTR(LEFT(WR,L-1),1,25)
WR = SUBSTR(WR,L+1)
L = POS('/',WR)
W4 = SUBSTR(LEFT(WR,L-1),1,9)
WR = SUBSTR(WR,L+1)
L = POS('/',WR)
W5 = SUBSTR(LEFT(WR,L-1),1,9)
WR = SUBSTR(WR,L+1)
L = POS('/',WR)
W6 = SUBSTR(LEFT(WR,L-1),1,40)
WR = SUBSTR(WR,L+1,32)
RECVAL = W3||WR||W6||W4||W5||W2||W1
RETURN
/*-------------------------------------------------------------------*/
SQLERROR: PROCEDURE EXPOSE SQLCODE SQLERRP SQLERRMC SQLERRD. SQLWARN. ,
SQLSTATE
/*-------------------------------------------------------------------*/
SAY "SQLCODE = " SQLCODE
SAY "SQLSTATE = " SQLSTATE
SAY "SQLERRP = " SQLERRP
SAY "SQLERRMC = " SQLERRMC
SAY "SQLERRD = " SQLERRD.1",",
SQLERRD.2",",
SQLERRD.3",",
SQLERRD.4",",
SQLERRD.5",",
SQLERRD.6
SAY "SQLWARN = " SQLWARN.0",",
SQLWARN.1",",
SQLWARN.2",",
SQLWARN.3",",
SQLWARN.4",",
SQLWARN.5",",
SQLWARN.6",",
SQLWARN.7",",
SQLWARN.8",",
SQLWARN.9",",
SQLWARN.10

EXIT 12
RETURN
/*-------------------------------------------------------------------*/

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

How to recover accidentally dropped Table by Getting DBID, PSID, OBID from table backup with help of DSN1PRNT

2008-12-19T11:27:00.000+05:30

You can act as recovery expert: Read on...

Case: Suppose you have dropped a table accidentally. As soon as the table dropped all information regarding this table will be dropped from DB2 catalog. If you have DDL you can create the table but you can't load the previously taken image copies to new table as DBID, PSID and OBID will definitely mismatch.

Here is the procedure to follow in case of such scenario.

For the data set that contains the dropped table, run DSN1PRNT with the FORMAT and NODATA options.
Record the HPGOBID field in the header page and the PGSOBD field from the data records in the data pages.
For the auxiliary table of a LOB table space, record the HPGROID field in the header page instead of the PGSOBD field in the data pages.

Field HPGOBID is 4 bytes long and contains the DBID in the first 2 bytes and the PSID in the last 2 bytes.

Field HPGROID (for LOB table spaces) contains the OBID of the table.
A LOB table space can contain only one table.

Field PGSOBD (for non-LOB table spaces) is 2 bytes long and contains the OBID of the table.
If your table space contains more than one table, check for all OBIDs. By searching for all different PGSOBD fields. You need to specify all OBIDs from the data set as input for the
DSN1COPY utility.

Convert the hex values in the identifier fields to decimal so that they can be used as input for the DSN1COPY utility.

Run the DSN1PRNT utility with the PARM=(FORMAT, NODATA) option on all data sets that might contain user table spaces.

The NODATA option suppresses all row data, which reduces the output volume that you receive.

Print(0) is the header page which contains the PSID and DBID.
Most probably you need to print at least 3 pages i.e. PRINT(0,2) to get OBID.

//TSG10PNT JOB ,'DSN1COPY ',NOTIFY=TSG10,CLASS=S,MSGCLASS=O,
// MSGLEVEL=(1,1),COND=(7,LT)
//*
//*=========================================================
//* DISPLAY FOR TABLESPACE SIHCY
//*=========================================================
//DISPAGE EXEC PGM=DSN1PRNT,
// PARM='PRINT(0,2),FORMAT,NODATA'
//SYSUT1 DD DSN=CF1M.DHGDG.DCMORGU1.SIHCY.F001.G7449V00,
// DISP=OLD
//SYSUT2 DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*

From SYSPRINT we can get
HPGOBID='018F0099'X
First 2 bytes gives us x'018F' which is equal to decimal 399 which is DBID.
Last 2 bytes gives us x'0099' which is equal to decimal 153 which is PSID.
You can only find the OBID which refers to PGSOBD in subsequent pages
if more than one tables are there.
Here we can see PGSOBD='009A'X which is decimal equivalent as 154.

I am pasting the first 3 pages of DSN1PRNT SYSPRINT output
at last section of the blog.

Note: This information can be also obtained by DSN1COPY utility with
print option. But this is not recommended.

After getting all the information you can restore the same data
to the target tablespace by mapping to the respective IDs..
Please see my other blog "DSN1COPY" to more how to map these ids.

You can code your DSN1COPY as below:
//*=========================================================
//* IBM DSN1COPY FOR TABLESPACE SIHCY
//*=========================================================
//SIHCY EXEC PGM=DSN1COPY,
// PARM='FULLCOPY,OBIDXLAT,RESET'
//SYSUT1 DD DSN=CF1M.DHGDG.DCMORGU1.SIHCY.F001.G7451V00,
// DISP=OLD
//SYSUT2 DD DSN=DSN2.DSNDBD.DSPS02.SIHCY.I0001.A001,
// DISP=OLD
//SYSPRINT DD SYSOUT=*
//SYSXLAT DD *
399,475 -- DBID info
153,734 -- PSID info
154,4493 -- OBID info
/*

Note: As a prevention measure, every table in production should be defined with "RESTRICT ON DROP" clause.

*******************************************
SYSPRINT output from DSN1PRNT
*******************************************
1
***********************************************************************************************************************************
1DSN1999I START OF DSN1PRNT FOR JOB TSG10PN1 DISPAGE
DSN1998I INPUT DSNAME = CF1M.DHGDG.DCMORGU1.SIHCY.F001.G7449V00 , SEQ
DSN1989I DSN1PRNT IS PROCESSED WITH THE FOLLOWING OPTIONS:
4K/NO IMAGECOPY/NUMPARTS = 0/ FORMAT/NO EXPAND/NO SWONLY/ PRINT/NO VALUE/ / /PIECESIZ= /NODATA
DSSIZE=

PAGE: # 00000000 ----------------------------------------------------------------------------------------------------------------
HEADER PAGE: PGCOMB='10'X PGLOGRBA='0B0BF46E3539'X PGNUM='00000000'X PGFLAGS='38'X
HPGOBID='018F0099'X HPGHPREF='0000092D'X HPGCATRL='00'X HPGREL='D2'X HPGZLD='00'X
HPGTORBA='000000000000'X HPGTSTMP='00010101000000000000'X HPGSSNM='DSN1'
HPGFOID='0098'X HPGPGSZ='1000'X HPGSGSZ='0040'X HPGPARTN='0000'X
HPGZ3PNO='000000'X HPGZNUMP='00'X HPGTBLC='0001'X HPGROID='009A'X
HPGZ4PNO='00000000'X HPGMAXL='018D'X HPGNUMCO='001C'X HPGFLAGS='0000'X
HPGCONTM='20081206185850843175'X HPGSGNAM='GDSN1 ' HPGVCATN='DSN1 '
HPGRBRBA='0B0BCA98C7D0'X HPGLEVEL='0B0BCA98C7D0'X HPGPLEVL='0B087E0E732E'X
HPGCLRSN='0B0BF46E3539'X HPGSCCSI='0000'X HPGDCCSI='0000'X HPGMCCSI='0000'X
HPGFLAG2='00'X HPGEPOCH='0031'X HPGCATV='00'X FOEND='N'

PAGE: # 00000001 ----------------------------------------------------------------------------------------------------------------
SEGMENTED SPACEMAP PAGE: PGCOMB='00'X PGLOGRBA='0B0BCA98B972'X PGNUM='00000001'X PGFLAGS='30'X
SEGNUM='0056'X SEGFREE='0031'X SEGENT='0026'X SEGSIZE='0040'X
SEGLENT='0000092B'X FOEND='E'
FIRST PART OF SEGMENTED SPACE MAP:
SEG 0001 00000002009AC0 3333333333333333333333333333333333333333333333333333333333333333
SEG 0002 00000003009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0003 00000004009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0004 00000005009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0005 00000006009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0006 00000007009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0007 00000008009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0008 00000009009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0009 0000000A009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 000A 0000000B009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 000B 0000000C009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 000C 0000000D009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 000D 0000000E009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 000E 0000000F009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 000F 00000010009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0010 00000011009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0011 00000012009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0012 00000013009A80 33333333333333333333333333333333333333333333F3333333333333333333
SEG 0013 00000014009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0014 00000015009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0015 00000016009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0016 00000017009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0017 00000018009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0018 00000019009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0019 0000001A009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 001A 0000001B009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 001B 0000001C009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 001C 0000001D009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 001D 0000001E009A80 3333333333333333333333333333333333333333333333333333333333333333
1SEG 001E 0000001F009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 001F 00000020009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0020 00000021009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0021 00000022009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0022 00000023009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0023 00000024009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0024 00000025009A80 3333333333333333333333333333333333333333333333333333333333333333
SEG 0025 00000000009A80 3333333333333333333333333333333333333333333000000000000000000000

SECOND PART OF SEGMENTED SPACE MAP:
RELPG 00 20 40 60 80 A0 C0 E0
0000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

RELPG 00 20 40 60 80 A0 C0 E0
0001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

RELPG 00 20 40 60 80 A0 C0 E0
0002 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

RELPG 00 20 40 60 80 A0 C0 E0
0003 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

RELPG 00 20 40 60 80 A0 C0 E0
0004 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

RELPG 00 20 40 60 80 A0 C0 E0
0005 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

RELPG 00 20 40 60 80 A0 C0 E0
0006 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

RELPG 00 20 40 60 80 A0 C0 E0
0007 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

RELPG 00 20 40 60 80 A0 C0 E0
0008 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

RELPG 00 20 40 60 80 A0 C0 E0
0009 00000000 00000000

PAGE: # 00000002 ----------------------------------------------------------------------------------------------------------------
DATA PAGE: PGCOMB='00'X PGLOGRBA='000000000000'X PGNUM='00000002'X PGFLAGS='00'X PGFREE=429
PGFREE='01AD'X PGFREEP=3647 PGFREEP='0E3F'X PGHOLE1='0000'X PGMAXID='09'X PGNANCH=0
PGTAIL: PGIDFREE='00'X PGEND='E'
ID-MAP FOLLOWS:
01 0014 01A7 033A 04CD 0660 07F3 0986 0B19
09 0CAC

RECORD: XOFFSET='0014'X PGSFLAGS='00'X PGSLTH=403 PGSLTH='0193'X PGSOBD='009A'X PGSBID='01'X
RECORD: XOFFSET='01A7'X PGSFLAGS='00'X PGSLTH=403 PGSLTH='0193'X PGSOBD='009A'X PGSBID='02'X
RECORD: XOFFSET='033A'X PGSFLAGS='00'X PGSLTH=403 PGSLTH='0193'X PGSOBD='009A'X PGSBID='03'X
RECORD: XOFFSET='04CD'X PGSFLAGS='00'X PGSLTH=403 PGSLTH='0193'X PGSOBD='009A'X PGSBID='04'X
RECORD: XOFFSET='0660'X PGSFLAGS='00'X PGSLTH=403 PGSLTH='0193'X PGSOBD='009A'X PGSBID='05'X
RECORD: XOFFSET='07F3'X PGSFLAGS='00'X PGSLTH=403 PGSLTH='0193'X PGSOBD='009A'X PGSBID='06'X
1RECORD: XOFFSET='0986'X PGSFLAGS='00'X PGSLTH=403 PGSLTH='0193'X PGSOBD='009A'X PGSBID='07'X
RECORD: XOFFSET='0B19'X PGSFLAGS='00'X PGSLTH=403 PGSLTH='0193'X PGSOBD='009A'X PGSBID='08'X
RECORD: XOFFSET='0CAC'X PGSFLAGS='00'X PGSLTH=403 PGSLTH='0193'X PGSOBD='009A'X PGSBID='09'X
0DSN1994I DSN1PRNT COMPLETED SUCCESSFULLY, 00000003 PAGES PROCESSED

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

DSN1COPY info and object id(OBIDs) translation

2008-12-14T11:54:00.000+05:30

DSN1COPY is useful for retsoring from same/other DB2 subsystem.

DSN1COPY is a very useful utility which helps you copy:
1) DSN1copy sequential datasets to DB2 VSAM datasets
2) DB2 VSAM datasets to sequential datasets
3) DB2 image copy datasets to DB2 VSAM datasets
4) DSN1copy sequential datasets to other sequential datasets
5) DB2 VSAM datasets to other DB2 VSAM datasets

This also provides other facilities:
1) Print hexadecimal dumps of DB2 data sets and databases
2) Check the validity of data or index pages, including dictionary pages for compressed data
3) Transalte database object identifiers (OBIDs) to enable moving datasts between different systems.

This utility is also compatible with LOB tablespaces. You can specify LOB keyword and omit the SEGMENT and INLCOPY keywords.

Here are the complete job which will help you to invoke this utility. The actual DSN1COPY step perfroms the copy from the image copy dataset to the target VSAM DB2 dataset.

//TSG10Y JOB ,'RF-DSALAGGK',NOTIFY=TSG10,CLASS=L,MSGCLASS=O,
// MSGLEVEL=(1,1),COND=(7,LT)
//*==============================================================
//LOADDUMM EXEC PGM=DSNUTILB,REGION=2M,
// PARM='DSN2,TSG10LD,'
//STEPLIB DD DSN=SYS1.DSN2.SDSNLOAD,DISP=SHR
//*==============================================================
//* PARM='DSN2,TSG10LD,RESTART' USE FOR RESTART
//*==============================================================
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTWK01 DD DSN=TSG10.SORTWK01,
// DISP=(,DELETE),
// SPACE=(CYL,(5,5),,,ROUND),
// UNIT=SYSDA
//SORTWK02 DD DSN=TSG10.SORTWK02,
// DISP=(,DELETE),
// SPACE=(CYL,(5,5),,,ROUND),
// UNIT=SYSDA
//SORTWK03 DD DSN=TSG10.SORTWK03,
// DISP=(,DELETE),
// SPACE=(CYL,(5,5),,,ROUND),
// UNIT=SYSDA
//SORTWK04 DD DSN=TSG10.SORTWK04,
// DISP=(,DELETE),
// SPACE=(CYL,(5,5),,,ROUND),
// UNIT=SYSDA
//SPSSW DD DUMMY
//SYSUT1 DD DSN=TSG10.SYSUT1,
// DISP=(,DELETE),
// SPACE=(CYL,(5,5),,,ROUND),
// UNIT=SYSDA
//SYSMAP DD DSN=TSG10.SYSMAP,
// DISP=(,DELETE),
// SPACE=(CYL,(5,5),,,ROUND),
// UNIT=SYSDA
//SYSERR DD DSN=TSG10.SYSERR,
// DISP=(,DELETE),
// SPACE=(CYL,(5,5),,,ROUND),
// UNIT=SYSDA
//SORTOUT DD DSN=TSG10.SORTOUT,
// DISP=(,DELETE),
// SPACE=(CYL,(5,5),,,ROUND),
// UNIT=SYSDA
//SYSIN DD *
LOAD DATA INDDN SPSSW REPLACE LOG NO
INTO TABLE SPU11.T1PSSW
REPAIR OBJECT
SET TABLESPACE DSALAGGK.SPSSW NOCOPYPEND
//*==========================================
//* STOP TARGET DB2 TABLESPACES
//*==========================================
//STOP EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=SYS1.DSN2.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN2) RETRY(4)
-STOP DATABASE(DSALAGGK) SPACENAM(SPSSW)
//* IBM DSN1COPY FOR TABLESPACE SPSSW
//*==========================================
//SPSSW EXEC PGM=DSN1COPY,
// PARM='FULLCOPY,NUMPARTS(14),OBIDXLAT,RESET'
//SYSUT1 DD DSN=DR1W.DHGDG.DSALAGG1.SPSSW.F001.G2754V00,
// LABEL=(1,SL),VOL=(,RETAIN),
// DISP=OLD
//SYSUT2 DD DSN=DSN2.DSNDBD.DSALAGGK.SPSSW.I0001.A001,
// DISP=OLD
//SYSPRINT DD SYSOUT=*
//SYSXLAT DD *
423,1069
37,12
38,22
//*========================================================
//* START TARGET DB2 TABLESPACES
//*========================================================
//START EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=SYS1.DSN2.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN2) RETRY(4)
-START DATABASE(DSALAGGK) SPACENAM(SPSSW) ACCESS(FORCE)
//*========================================================
//* REBUILD THE INDEXES
//*==========================================================
//RECINDX EXEC PROC=PL0002,REGN=20M,RESTART=RESTART(BYPASS),
// SUBSYS=DSN2,COND=(4,LT)
//PL000201.ST02MSG DD SYSOUT=U
//PL000201.SL02MSG DD SYSOUT=U
//PL000201.SYSIN DD *
REBUILD INDEX (ALL) TABLESPACE DSALAGGK.SPSSW
ALLMSGS
ESTIMATED-KEYS 99999999
MAXTASKS 2
SORTNUM 4
SORTDEVT SYSDA
SPACE-DEFN YES
STARTUP-ACCESS FORCE
//*==========================================================
//* RUNSTATS TARGET ENVIRONMENT
//*==========================================================
//RUNSTATS EXEC PGM=DSNUTILB,REGION=2M,COND=(4,LT),
// PARM='DSN2,DSALAGGK,'
//STEPLIB DD DSN=SYS1.DSN2.SDSNLOAD,DISP=SHR
//*==========================================================
//* PARM='DSN2,DSALAGGK,RESTART' USE FOR RESTART
//*==============================================================
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
RUNSTATS TABLESPACE DSALAGGK.SPSSW INDEX(ALL) SHRLEVEL REFERENCE
/*

//SYSXLAT DD *
423,1069 DBID PROD,TEST OF TABLESPACE
37,12 PSID PROD,TEST OF TABLESPACE
38,22 OBID PROD, TEST OF TABLE
SELECT DBID FROM SYSIBM.SYSTABLESPACE
WHERE NAME = 'XXXXX' AND
DBNAME = 'XXXXXXXX';
SELECT PSID FROM SYSIBM.SYSTABLESPACE
WHERE NAME = 'XXXXX' AND
DBNAME = 'XXXXXXXX';
SELECT OBID FROM SYSIBM.SYSTABLES
WHERE NAME = 'XXXXX' AND
DBNAME = 'XXXXXXXX';

Note: This utility will not work well if you have more than one table in a single tablespace.

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

TABLESPACE design and SEGSIZE consideration

2008-12-11T12:03:00.000+05:30

While designing the tablespace, Segment size is an important parameter to consider. Here are IBM recommendations for such calculation.

NO OF PAGES SEGSIZE
-----------..... -------

< = 28 ............4 TO 28
28 TO 128 .........32
> = 128 ...........64

How to determine that when will you cosider which type of tablespace.

NO OF PAGES ....TABLESPACE DESIGN
----------- ----------------------

> 100,000 .....CONSIDER PARTITIONING

> 10,000 ......ONE TABLE SEGMENTED TABLESPACE

Greater than 128 TO Less than 10,000 MULTIPLE-TABLE SEGMENTED TABLESPACES(But I think one table in one tablspace is best design for maintenance purpose)

Less than 128 ............MULTIPLE-TABLE SEGMENTED TABLESPACES(But I think one table in one tablspace is best design for maintenance purpose)

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA

How to bring down DB2 forcefully

2008-12-09T11:55:00.000+05:30

At times there are some held threads which prevents DB2 subsystem down for maintenance purpose. We can follow these if we require:

These are some console commands which can be tried:

/MODIFY DSN1IRLM,ABEND,NODUMP

OR
/F DSN1IRLM,ABEND

THEN IF NECESSARY

/C DBM1

/FORCE DBM1 (LAST RESORT)

OR

/C MSTR

/FORCE MSTR (LAST RESORT)

WON'T START? IPL !!!!!!!!!!!

Cheers..
Prakash C. Singh
IBM Certified DB2 DBA

Know More about WLM Stored Procedure

2008-12-08T13:39:00.000+05:30

1) HOW TO KNOW THAT WLM IS AVAILABLE OR NOT?
IN S OF SPOOL COMMAND:

/D WLM,APPLENV=DSN1PRCH

OR

/%WLMDISP DSN1PRCH - IF YOU EXECUTE THIS COMMAND, THEN LOOK AT ULOG.

2) HOW TO RECOVER IF IT IS IN DIFF STATE OTHER THAN AVAILABLE STATE
WE CAN RECOVER THE DSN1PRCH STARTED TASK BY EXECUTING:
/%WLMRECOV DSN1PRCH

OR

IF WE NEED TO REFRESH THE WLM ENV: THIS COMMAND NEED TO BE EXECUTED
CONNECT TO DSN1 USER TSGDBP1 THEN SOR TERMINAL.

CALL SYSPROC.WLM_REFRESH('DSN1PRCH','DSN1',?,?)

TO DISPLAY THE STATUS OF THE PROCEDURE YOU CAN ISSUE A DB2:-

-DIS PROCEDURE(DB2CONN.ABG529P)

Simillarly You can stop and start the Stored Procedure by

-STA PROCEDURE(DB2CONN.ABG529P)

-STOP PROCEDURE(DB2CONN.ABG529P.

Cheers..
Prakash C. Singh
IBM Certified DB2 DBA

Catalog mainframe server/subsystem/database

2008-11-29T16:31:00.000+05:30

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

CCSID Mismatch after applying PTF for DB2 V7 to V8 migration

2008-11-27T11:21:00.000+05:30

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)

Knowing Tapes are still on Rack SIL0 command

2008-11-22T11:48:00.000+05:30

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.

To know the z/OS version and last IPL info

2008-11-21T12:03:00.000+05:30

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

DB2 Version and other info

2008-11-20T11:53:00.000+05:30

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

Conditional Restart DB2 DSNJU003

2008-11-16T12:39:00.000+05:30

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

Resource Limit Specification Table DSNRLST01

2008-11-15T20:35:00.000+05:30

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

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

2008-11-14T11:15:00.000+05:30

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

Job seems not executing for longer period of time

2008-11-13T12:33:00.000+05:30

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

How to Revoke SYSADM previlege without cascading effect

2008-11-11T09:25:00.000+05:30

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

Access the commands of DB2 from command line prompt

2008-11-10T11:26:00.000+05:30

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

DB2 DBA Z/OS

2008-11-09T13:26:00.000+05:30

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