Limit Key getting -636 ERROR: RANGES SPECIFIED FOR PARTITION 28 ARE NOT VALid

Chris Bonin

Limit Key getting -636 ERROR: RANGES SPECIFIED FOR PARTITION 28 ARE NOT VALid

I am trying to change limitkeys but I get this error.  I have 28 partitions..

   ALTER TABLE CABXA09D.TFxxxxcb                   
   ALTER PARTITION 27 ENDING AT                    
               (951909786941.) INCLUSIVE ;         
RROR ON   ALTER     COMMAND, EXECUTE   FUNCTION       

SQLERROR ON   ALTER     COMMAND, EXECUTE   FUNCTION                          
 RESULT OF SQL STATEMENT:                                                    
 DSNT408I SQLCODE = -636, ERROR:  RANGES SPECIFIED FOR PARTITION 28 ARE NOT VA
 DSNT418I SQLSTATE   = 56016 SQLSTATE RETURN CODE                            
 DSNT415I SQLERRP    = DSNXISB6 SQL PROCEDURE DETECTING ERROR                
 DSNT416I SQLERRD    = 135  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION        
 DSNT416I SQLERRD    = X'00000087'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'0
          INFORMATION                                                        

 

Here is partition 28 alter:

***INPUT STATEMENT:                                                          
       ALTER TABLE CABXA09D.TFxxxxcb                                             
       ALTER PARTITION 28 ENDING AT                                          
                   (952020381523.) INCLUSIVE ;                               
SQLWARNING ON ALTER     COMMAND, EXECUTE   FUNCTION                          
 RESULT OF SQL STATEMENT:                                                    
 DSNT404I SQLCODE = 610, WARNING:  A CREATE/ALTER ON OBJECT DKRZA02D.TF2REFUND
 DSNT418I SQLSTATE   = 01566 SQLSTATE RETURN CODE                            
 DSNT415I SQLERRP    = DSNXI14 SQL PROCEDURE DETECTING ERROR                 
 DSNT416I SQLERRD    = 250  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION        
 DSNT416I SQLERRD    = X'000000FA'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'0
          INFORMATION                                                        

 

When I reorg all of the values change but Partition 27 value.  I checked to see that PArtition 27 value is less than 28 but I don't see whats wrong.  Any help appreciated.

Jack Campbell

RE: Limit Key getting -636 ERROR: RANGES SPECIFIED FOR PARTITION 28 ARE NOT VALid
(in response to Chris Bonin)

Chris,

The error (SQLCODE -636) suggests you are trying to change the limit key to something that is not allowed or into a range that already exists.OR you have MINVALUE/MAXVALUE specified in one partition somewhere.

Run the SQL below to see all current limit-key values (in logical order), see what it reveals. If that does not help you will need to post the full details of the partitioning key (name/data type) and partitioning key values as it is difficult to review without all the details.

------------------------------------------------------------------------ 

-- LIST LIMIT-KEY COLS/VALUES
------------------------------------------------------------------------
SELECT SUBSTR(T.CREATOR,1,4) AS CREATOR
, CAST(T.NAME AS CHAR(30)) AS TBL_NAME
-- , S.DBNAME
, S.TSNAME
, S.PARTITION
, S.LOGICAL_PART
, T.PARTKEYCOLNUM AS LKEY_COLS
, S.LIMITKEY
FROM SYSIBM.SYSTABLES T
JOIN SYSIBM.SYSTABLEPART S
ON T.DBNAME = S.DBNAME
AND T.TSNAME = S.TSNAME

WHERE T.CREATOR = 'xxxx'
AND T.NAME = 'Table_name'
AND T.TYPE = 'T'

ORDER BY T.NAME, S.LOGICAL_PART
WITH UR

Chris Bonin

Limit Key getting -636 ERROR: RANGES SPECIFIED FOR PARTITION 28 ARE NOT VALid
(in response to Jack Campbell)
Thanks for your help. I ran the update on keylimit 27 again and it worked. It looks like partition 27 new value was more than the old value of partition 28.

Thanks,
Chris Bonin
DB2 z/OS Database Adminstrator
North Carolina Department of Revenue
Office: 919.754.2485
Email: [login to unmask email]<mailto:[login to unmask email]>

[cid:[login to unmask email]



From: Jack Campbell <[login to unmask email]>
Sent: Wednesday, July 22, 2020 11:22 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Limit Key getting -636 ERROR: RANGES SPECIFIED FOR PARTITION 28 ARE NOT VALid

CAUTION: This email originated from outside of the organization. Do not click links or attachments unless you recognize the sender.

-----End Original Message-----
________________________________
E-Mail correspondence to and from this sender may be subject to the North Carolina Public Records Law, and may be disclosed to third parties.
________________________________
________________________________
E-Mail correspondence to and from this sender may be subject to the North Carolina Public Records Law, and may be disclosed to third parties.
________________________________
Attachments

  • image001.png (8.4k)