Search

Wednesday, October 10, 2012

JCLs to Alter Lock parameter of DB2 table

   

                         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.



No comments:

Post a Comment