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.
Saturday, April 25, 2009
Sunday, March 15, 2009
How to read DSN_STATEMNT_TABLE
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:
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
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
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
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
Sunday, March 8, 2009
EXPLAIN Tables (PLAN_TABLE ,DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE)
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
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
Tuesday, December 23, 2008
Printing zPARMs of DB2 using IBM-supplied stored procedure DSNWZP
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:
===================================
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
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
Friday, December 19, 2008
How to recover accidentally dropped Table by Getting DBID, PSID, OBID from table backup with help of DSN1PRNT
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
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
Sunday, December 14, 2008
DSN1COPY info and object id(OBIDs) translation
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
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
Subscribe to:
Posts (Atom)