IBM Support

Can we control the COMMITs in different phases of Admin_move_table ?

Technical Blog Post


Abstract

Can we control the COMMITs in different phases of Admin_move_table ?

Body

On of our customers had this question about commits in different phases of the admin_move_table operation. 

Documenting our response here for the benefit of other customers as well. 

 

Question : In Admin_move_table, db2 will do 4 steps/phases ->  INIT, COPY, REPLAY and SWAP.

In COPY step, we can see COMMIT_AFTER_N_ROWS (default 10000 rows) is mentioned in KC document:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0055069.html 

COMMIT_AFTER_N_ROWS - During the COPY phase, a commit is executed after these many rows are copied. 0 means no commits during COPY. Default value is 10000. 

But, there is  nothing mentioned regarding “COMMIT” or its frequency during REPLAY and SWAP phases.  How are transactions/operations committed in these phases ?

 

Answer/Explanation : 

For both REPLAY and SWAP phases db2 will take care of the COMMITs based on source logs while maintaining the database to be  ATOMIC and CONSISTENT. Users do not have control in it. 

 

REPLAY: Copies into the target table any rows that have changed in the source table since the COPY phase began. REPLAY can be used only after the COPY phase has completed.

-> COMMITs while copying the changed rows on target table MUST be SAME to the commits done on the source tables i.e. when there is a COMMIT record in source logs there will be a COMMIT performed in target table. These commit operations should not be controlled by user and hence user is not given any config parameter/option to control it. 

 

SWAP: Executes the REPLAY phase until the number of changes applied during the last scan of the staging table is less than the REPLAY_THRESHOLD value stored in the protocol table. The source table is then taken offline briefly to finish the final REPLAY, and then this command swaps the source table with target table and brings the table back online. SWAP can be used after the COPY phase has completed, but ideally after the REPLAY phase has been called.

-> SWAP is an atomic operation, it must be committed when it is completed (UOW) and never in between. It is an ALL or NONE type of operation. So again, no point in specifying COMMIT or having users control it. 

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13286029