ALTER TABLESPACE statement failed with SQLCODE = -650 REASON 40 in ssid0001 Job

Products

• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS

Issue/Introduction

The ssid0001 Post-Install Tasks Job failed with a SQLCODE = -650 REASON 40 error message in the ALTER TABLESPACE ...  MOVE TABLE staements. 
 
For example:

    ALTER TABLESPACE PTDB.PTITS1                                     
        MOVE TABLE PTI.TABLE_1                                        
            TO TABLESPACE PTDB.PTITS2                                
    ;
                                                                  
 DSNT408I SQLCODE = -650, ERROR:  THE ALTER STATEMENT CANNOT BE        
          EXECUTED, REASON 40                                          
 DSNT418I SQLSTATE   = 56090 SQLSTATE RETURN CODE                      
 DSNT415I SQLERRP    = DSNXIATS SQL PROCEDURE DETECTING ERROR          
 DSNT416I SQLERRD    = 415  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION  
 DSNT416I SQLERRD    = X'0000019F'  X'00000000'  X'00000000'           
          X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC         
          INFORMATION                                                  

Cause

When you run ALTER TABLESPACE MOVE TABLE, Db2 does not move the data instantly. Instead, it creates a pending definition change. The table is logically marked to move, and the source tablespace is placed in AREOR (REORG-pending) status.

Reason Code 40 triggers because:

• The table you are trying to move is already "owned" by a previous, uncommitted (non-materialized) MOVE TABLE operation.

• Db2 prohibits "stacking" multiple move operations on the same table or within the same tablespace until the first one is materialized by a REORG.

Even if you are trying to move the table to a different target or simply re-running the command because you thought it did not "take," Db2 sees the catalog is already modified for a pending move. You cannot alter the destination or repeat the move until the physical data shift occurs.

 

Resolution

To clear this error and complete the migration, you have two paths:

Complete the Move

Run the REORG TABLESPACE utility on the source (segmented) tablespace. This materializes the move, transfers the data to the PBG.

Cancel the Move

Run the ALTER TABLESPACE... DROP PENDING CHANGES statement. This will clear the AREOR status and allow you to start over.