Saturday, December 25, 2010

Termination of REORG TABLESPACE

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.

Sunday, September 19, 2010

DB2 EXIT Routines

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

Sunday, August 8, 2010

Backup Failed with REASON 00C200E1

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

Saturday, July 24, 2010

Know the power of REPAIR utility in DB2

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

Monday, July 5, 2010

How to display the key corresponding to RID Value

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

Sunday, April 18, 2010

Get ready for DB2 X

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

Monday, March 29, 2010

Modelling your production system


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

Sunday, February 21, 2010

Fileaid for DB2 Setup Error - FDBA623 CAF error 00F30034

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

Sunday, February 7, 2010

Display current threads when you get DB2 Not Operational message

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