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
//PRODDB2 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(‘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.
The Mainframe hub 