JCLS TO ALTER LOCK PARAMETERS OF ANY TABLE (DB2 V 9)
LOCKPART
JCL to alter tablespace lockpart parameter (only for partitioned) to NO
in DB2
//STEPLIB DD DSN=SYS1.DSN900.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=X
//SYSTSIN DD *
DSN SYSTEM(DB2PROD)
RUN PROGRAM(DSNTEP) PLAN(DB2PLAN) -
LIBRARY('DSN.DB2PROD.RUNLIB.LOAD')
END
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
ALTER TABLESPACE PRDX1.TSSMG
LOCKPART NO;
COMMIT;
This will give problem as tablespace is not stopped. it will give following error:
PAGE 1
***INPUT
STATEMENT:
ALTER TABLESPACE PRDX1.TSSMG
LOCKPART NO;
SQLERROR ON ALTER
COMMAND, EXECUTE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -626, ERROR: THE ALTER STATEMENT IS NOT EXECUTABLE BECAUSE
THE PAGE SET IS NOT STOPPED
DSNT418I SQLSTATE = 55015 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXIATS SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 155
0 0 -1
0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'0000009B' X'00000000'
X'00000000' X'FFFFFFFF' X'000
INFORMATION
PAGE 1
***INPUT
STATEMENT:
COMMIT;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 00000 SQLSTATE RETURN CODE
DSNT416I SQLERRD = 0
0 0 -1
0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000'
X'00000000' X'FFFFFFFF' X'000
An ALTER statement specifies an ADD PART, BUFFERPOOL, USING, PRIQTY, SECQTY, ERASE, or GBPCACHE clause, but the page set is not stopped.
PAGE SET
PAGE SET is a physical grouping of pages. Page sets are of two types:
Linear : DB2 uses linear page sets for simple table spaces, segmented table spaces, and indexes.
Partitioned : DB2 uses partitioned page sets when it implements partitioned table spaces.
Each of the Page set is composed of several types of pages: header page, space map pages, dictionary pages, and data pages.
Here the page set is partitioned which is on table space.
Always check for tablespace status:
tso dsn s(Db2prod)
-stop db(prdx1) sp(tssmg)
confirm using below command
-dis db(prdx1) sp(tssmg)
DSNT361I -P * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I -P
***********************************
DSNT362I -P DATABASE = PRDX1 STATUS = RW
DBD LENGTH
= 1434054
DSNT397I -P
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ----- ----------------- -------- -------- --------
-----
TSSMG TS 0001 STOP
-THRU 0039
******* DISPLAY OF DATABASE PRDX1 ENDED
**********************
DSN9022I -P DSNTDDIS
'DISPLAY DATABASE' NORMAL COMPLETION
DSN
After this resubmit above job and it will be executed successfully.
PAGE 1
***INPUT
STATEMENT:
ALTER TABLESPACE PRDX1.TSSMG
LOCKPART NO;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 00000 SQLSTATE RETURN CODE
DSNT416I SQLERRD = 0
0 0 -1
0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000'
X'00000000' X'FFFFFFFF' X'000
INFORMATION
ALTER SUCCESSFUL
PAGE 1
***INPUT
STATEMENT:
COMMIT;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 00000 SQLSTATE RETURN CODE
DSNT416I SQLERRD = 0
0 0 -1
0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000'
X'00000000' X'FFFFFFFF' X'000
LOCKSIZE
Alter tablespace to specify locksize
tablespace using below job and it runs successfully.
//PRODSPU EXEC PGM=IKJEFT01,DYNAMNBR=30
//STEPLIB DD DSN=SYS1.DSN900.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=X
//SYSTSIN DD *
DSN SYSTEM(DB2PROD)
RUN PROGRAM(DSNTEP) PLAN(DB2PLAN) -
LIBRARY('DSN900.DB2PROD.RUNLIB.LOAD')
END
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
ALTER TABLESPACE PRDX1.TSSMG
LOCKSIZE TABLESPACE;
COMMIT;
-start db(prdx1)
sp(tssmg)
DSN9022I -P DSNTDDIS 'START DATABASE' NORMAL COMPLETION
DSN
***
-dis db(prdx1)
sp(tssmg)
DSNT360I -P
***********************************
DSNT361I -P * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I -P
***********************************
DSNT362I -P DATABASE = PRDX1 STATUS = RW
DBD LENGTH
= 1434054
DSNT397I -P
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ----- ----------------- -------- -------- --------
-----
TSSMG TS 0001 RW
-THRU 0039
******* DISPLAY OF DATABASE PRDX1 ENDED
**********************
DSN9022I -P DSNTDDIS
'DISPLAY DATABASE' NORMAL COMPLETION
DSN
This means to change locksize parameter, no need to stop tablespace but to change lock part parameter, stop tablespace.
Confirm lock size change from DB2 Catalog, and also
status (A=> available)
SELECT
NAME
,DBNAME
,BPOOL
,PARTITIONS
,LOCKRULE
,LOCKMAX
FROM SYSIBM.SYSTABLESPACE
WHERE
NAME = 'TSSMG'
AND
DBNAME = 'PRDX1'
WITH
UR
Note: when you change locksize to tablespace(or table), lockmax
automatically changes to 0
NAME DBNAME
BPOOL PARTITIONS LOCKRULE
LOCKMAX STATUS
------ --------- -------- ---------- --------
-------- ------
TSSMG PRDX1 BP32K 39 S 0 A
Now, if when we try to alter tablespace lockmax
parameter with value other than 0
, it shows error (because current locksize is
tablespace/ table)
However, if the current locksize was ROW/ANY/PAGE,
there would have been no DB2 error.
//PRODSPU EXEC PGM=IKJEFT01,DYNAMNBR=30
//STEPLIB DD DSN=SYS1.DSN900.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=X
//SYSTSIN DD *
DSN SYSTEM(DB2PROD)
RUN PROGRAM(DSNTEP) PLAN(DB2PLAN) -
LIBRARY('DSN900.DB2P.RUNLIB.LOAD')
END
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
ALTER TABLESPACE PRDX1.TSSMG
LOCKMAX 1000;
COMMIT
PAGE 1
***INPUT
STATEMENT:
ALTER TABLESPACE PRDX1.TSSMG
LOCKMAX 1000;
SQLERROR ON ALTER
COMMAND, EXECUTE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -611, ERROR: ONLY LOCKMAX 0 CAN BE SPECIFIED WHEN THE LOCK
WHEN THE LOCK SIZE OF THE TABLESPACE IS
TABLESPACE OR TABLE
DSNT418I SQLSTATE = 53088 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXIATS SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 140
0 0 -1
0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'0000008C' X'00000000'
X'00000000' X'FFFFFFFF' X'000
INFORMATION
PAGE 1
This error is because lock escalation is not supported from tablespace or table levels.
No comments:
Post a Comment