Resource Unavailable Reason 00C90080 Abend S04E

"Resource Unavailable" is very common for any DBA and generally resource with "resource type" is used by another resource apart from many other reasons.The basic approach is to check for the "Resource Type" ,"Reason" and "Name" in the DSNU message in your sysout.Accordingly check for the status of the object in DB2Interactive or any other third party tool that your site uses.
You can use the comm -DIS DATABASE (DBNAME) SP(SPNAME) CLAIMERS LIMIT(2000)
Depending on your needs choose from several options present for DIS DATABASE command in DB2 Command Reference.
The Most common Resource Types are-
00000100         Database DB
00000200         Table space DB.SP
00000201         Index space DB.SP
00000202         Table space RD.DB.TS
00000210         Partition DB.SP.PT
00000220         Data set DSN

You can also check the LPL state or any restrictive state of the object by using
-DIS DATABASE (DBNAME) SP(SPNAME) LPL
-DIS DATABASE (DBNAME) SP(SPNAME) RES respectively.
The most common reason for Resource Unavailable with 00C90080 reason code  implies that your Resource is in READONLY(RO) mode and you are trying to do some update operations that needs RW access. You can simply Start the DB with Access RW and rerun your job.

Cheers !!!

Changed Catalog Tables DB2 V9 to DB2 V10

Please mark for the differences in the columns of the catalog tables and some new added catalog tables in DB2 v10.
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2z10.doc.wnew/db2z_10_otherutilitychanges.htm


 Cheers!!!

CA Insight Panel Display Commands..

May be you like to use some shortcuts in CA Insight..

1) System Stats Panel ---- d SYSSTATS
2) Buffer Pool List Panel ---- d BUFLISTS
3) EDM Pool Panel ---- d EDMPOOL
4) Log Status & Alloc Panel ---- d LOGALLOC
5) Active THDS Panel ---- d THRDACTV
6) Address space Snapshot ---- d MVS
7) Virtual Storage Panel ---- d MVSSTOR
8) Address Space Enqueues ---- d MVSENQS
9) Address space Programs Panel ---- d MVSPGMS
10) Address space Files Panel ---- d MVSFILES
11) Datasets Open Panel ---- d DATASETS
12) DB2 System Parameters ---- d SYSPARMS
13) Log Activity Panel ---- d LOGSTATS
14) Dataset Drain Panel ---- d SYSDRAIN
15) SQL Counts Panel ---- d SQLTOTAL
16) List Prefetch & Parallelism ---- d SYSLPRF
17) Locks Panel ---- d SYSLOCKS
18) Subsystem Services Panel ---- d SYSSVCS
19) Remote Location List Panel ---- d SYSRMOTE
20) Binds & Authorization Check ---- d SYSBIND
21) Storage Panel ---- d SYSSTRG
22) Dynamic Prepare/Direct Row ---- d SYSDYNP
23) DB2 Routine Counts Panel ---- d SYSRTN
24) Dynamic SQL List in Cache ---- d DYNSQLST
25) Storage Utilization Panel ---- d SYSSTG
26) DB2 Command History ---- d DB2CMDS

Cheers!!!

DCLGEN

Declaration Generator is a command through which we can extract the DDL of the table.It produces an SQL DECLARE TABLE Statement.
It also generates the data type declaration of each column in the assigned language ie. COBOL,PL/I,C.
The DCLGEN can be executed in Online as well as Batch.
In Online it can be done through DB2I(Interactive Menu)
In Batch,you can use DCLGEN command along with the parameters after connecting to the system through DSN.
For executing it in batch you can find the sample job in your SDSNSAMP library.Search for the members DSNTEJ2C and DSNTEJ2P.
You can also execute DCLGEN directly from TSO.Sign on to TSO, issue the TSO command DSN, and then issue the subcommand DCLGEN.

Following is the sample JCL to execute DCLGEN-:

Command ===>                                                                                                 Scroll ===> CSR


****** ***************************** Top of Data ******************************
000001 //DCLGENJ JOB (*******),'DCLGEN (DB2)',
000002 //       TIME=(0,20),NOTIFY=&SYSUID,MSGCLASS=X,REGION=0M,
000003 //       USER=XXXXX,PASSWORD=
000004 //*
000005 //* STEP 1: CREATE COPY FILE TABLE DESCRIPTIONS (DCLGEN)
000006 //PH02CS01 EXEC PGM=IKJEFT01,DYNAMNBR=20
000007 //STEPLIB DD DSN=XXXXXX.SDSNLOAD,DISP=SHR
000008 // DD DISP=SHR,DSN=XXXXXX.SDSNEXIT
000009 //SYSTSPRT DD SYSOUT=*,DCB=(RECFM=F,LRECL=200,BLKSIZE=200)
000010 //SYSUDUMP DD SYSOUT=*
000011 //SYSTSIN DD *
000012 DSN SYSTEM(DSN)
000013 DCLGEN TABLE(XXXXXX.TEST) +
000014 LIBRARY(XXX.XXXXX..XXX.XXXXX(XXXX)') +
000015 ACTION(ADD) APOST +
000016 LANGUAGE(IBMCOB) +
000017 STRUCTURE(POPTVAL)
000018 END
//*
 
 
Cheers!!!!
 
 

Online Reorg DB2

Reorg  utility behavior in SHRLEVEL CHANGE and REFERENCE is very interesting.
To know how Reorg helps in maintaining the consistency in DB2 system is very important.

REORG is like running defragmentor in ur windows OS.To bring the data in a sorted manner and to reorganise the TS and IX in such a way that the fragmented space can be reclaimed and optimized for fast retrieval.
 Data access during reorg 1) NONE 2) REFERENCE 3) CHANGE

REORG with SHRLEVEL REFERENCE -: Read Only access to the dataset is given.Any other application can read the data from that TS but cannot make any changes....How it is done?

DB2 creates a shadow copy of the original VSAM dataset (TS) and allows the other applications to read the data from the original dataset. DB2 reloads the reorganised data into the Shadow copy datasets.By the end of reorganization,DB2 switches the future access of the application from the original dataset to shadow copy.
While switching,the application does not hold any access on either dataset.

REORG with SHRLEVEL CHANGE -: DB2 reloads the reorganised data in the shadow copy datasets.In SHRLEVEL CHANGE comes the concept of Mapping Table.

When data is reloaded in the shadow dataset then the RID's of the rows in the original dataset changes.To keep the track of those RID's ie. for source and target(shadow copy) DB2 maintains a mapping table.
The structure is like -:
CREATE TABLE mapping-table-name1
(TYPE CHAR(1) NOT NULL,
SOURCE_RID CHAR(5) NOT NULL,
TARGET_XRID CHAR(9) NOT NULL,
LRSN CHAR(6) NOT NULL);


CREATE UNIQUE INDEX index-name1 ON table-name1
(SOURCE_RID ASC, TYPE, TARGET_XRID, LRSN);

For more understanding -:
1) The orginal tablespace is unloaded and the shadow copy is loaded.The indexes are built as the shadow copy is loaded.
2) The mapping table is maintained to map the position of each row(RID) in original TS and the shadow copy.
3) While the pages are loaded,they are written to the imagecopy datatset.
4) During all this time updating and reading was allowed and the changes were logged.
5) When the shadow copy is loaded,all the logged changes are copied to the shadow copy to bring it up to   date .Information from the mapping table is used to update the correct rows.
6) Pages changed since original image copy was taken are added to copy dataset.
7) The old tablespaces and indexspaces datasets are then renamed and shadow copies are given the original name.
8) Finally the old objects are deleted.

Note -:  If the datasets are not DB2 Managed then u need to preallocate shadow datasets before u execute REORG.
When preallocating the datasets define it as LINEAR and use the SHAREOPTIONS as (3,3).
Cheers!!

ROWID in DB2...

ROWID in DB2…

The Row id is part of every successful database in today’s world.

In DB2 every record stores a RID in the form of hex values. ROWID is basically a combination of page(block),slot (within a page),and file. Fetching the records using ROWID’s is amongst the most optimized method and is used when indexes are used in the predicates. In fact, ROWID is the fastest way of retrieving records.

ROWID is internally generated unique row identifier which is constant and immutable unless a REORG is done.

In DB2 ROWID is of 4 bytes(3 bytes for page and 1 for slot).Since 1 byte is 8 bits and 8 bits can go up to 255,therefore one page in DB2 can contain 255 records and thats why we have the limitation of MAXROWS as 255 per page.

ROWID can be used separately in DB2 by adding a column of ROWID data type.

This column can then be retrieved in a Select and stored in a program's host variable. It can later be used to directly retrieve the row in the table to perform a Select, Update, or Delete. When using a Rowid in this way, a row is directly accessed without performing a Table scan and without needing to use an Index.

Select * from table_name where rowid_column = :hostVarRowid;

In DB2, ROWID is actually a data type of a column.

Cheers!!!

BIND & REBIND in DB2

BIND & REBIND are the two options provided in DB2 for the re-evaluation of access path.

REBIND searches the catalog,takes the information found there and re-evaluates the access path.
BIND being more enhanced operation performs a lots of checks (correct syntax,whether all tables are existing or not ,proper grants are provided or not) and then re-evaluates the access path.

Both BIND & REBIND basically checks for the dependency of the objects referenced in the package.
In a package suppose n number of objects are referenced and any one of them is dropped,then the package will be consequently marked as invalid.
When Someone executes the package then DB2 will perform an automatic rebind and if the object is still not available then that package is marked as inoperative and no further automatic rebinds are performed for the same and the execution will fail.

A BIND permits us to use new BIND parameters but the REBIND uses the already specified parameters.

Further if you want to specify new parameters for the execution of your Package and if the whole SQL has been changed (like referencing to new objects) then a BIND is required .
And if you simply want to redo the evaluation of the access path for the package then being cost efficient and optimized,the REBIND is the best option.


Cheers!!