Db2

Join us for an insightful discussion on 20 August, 2024 at 11:00 AM ET to get an overview of how you can move any table within your database with ADMIN_MOVE_TABLE, addressing problems like growth, data placement, compression, or even reorganization of tables. Carola Langwald will cover the technical background on ADMIN_MOVE_TABLE and use cases and insights into new features and functions like size estimation. Furthermore, monitoring of ADMIN_MOVE_TABLE and performance considerations are presented.

Click here to register and add the event to your calendar.

Where is the link to join? Check your email the day before the event!

 How to get the most out of this webinar:

• Explore the Db2 Community.

• Ask questions for the presenter by replying to this discussion post before, during and after the webinar. Carola Langwald will be happy to answer your questions and share insights.

• Check out upcoming Db2 events here.

We look forward to seeing you there!

Questions answered during the call:

Q1: If you have a very high volume of online transactions, is it possible that the Z-lock cannot be obtained?  
If so, what will happen and what can be done?
Answer: There is this Option:
FORCE_ALL
    Heavy workload on the source table might prevent ADMIN_MOVE_TABLE from completing the SWAP phase (SQL0911N reason code 2). In other words, if the source table is heavily used by other applications during the SWAP phase, ADMIN_MOVE_TABLE might rollback due to a deadlock (SQL0911N reason code 2).
    Use this option to force all applications having workload on the source table. Forced applications will receive SQL1224N on all subsequent database manager requests.

Q2: Do you know if IBM fixed the following issues:
1. Failure to finalize the move when a Primary Key is defined without first creating a unique index? It fails because it cannot recreate indexes with Autogenerated names. We developed a process, which recreates all such PKs and thus allows table move.
2. Failure to move tables with Check Constraints created prior to Db2 9.7 (this is not a big deal, of course, as this constraint can be dropped and re-created).
The worst thing is that the failure happens during SWAP, while the issue could be detected at the initiation of the move
It fails with the message that it cannot create index with the SQL12345... name. Because of that we alsways require that a PK is created over a unique index, if possible

Answer:
1. We have improved checks in AMT INIT, but need to double check your case is included.
2. see slide later in the presentation

Q3: why ALTER TABLE is not to be used for performing column operations like drop column etc.? why AMT is used instead?
Answer: Dropping a column requires a classic table reorganization

Q4: where to check history of AMT performed on a Table ?
Answer: in the "change history monitor" or query protocol table of AMT

Q5: can we move system catalog tables using AMT from SMS to Automatic tablespaces 
Answer: No, this is not possible

Join us for an insightful discussion on 20 August, 2024 at 11:00 AM ET to get an overview of how you can move any table within your database with ADMIN_MOVE_TABLE, addressing problems like growth, data placement, compression, or even reorganization of tables. Carola Langwald will cover the technical background on ADMIN_MOVE_TABLE and use cases and insights into new features and functions like size estimation. Furthermore, monitoring of ADMIN_MOVE_TABLE and performance considerations are presented.

Click here to register and add the event to your calendar.

Where is the link to join? Check your email the day before the event!

 How to get the most out of this webinar:

• Explore the Db2 Community.

• Ask questions for the presenter by replying to this discussion post before, during and after the webinar. Carola Langwald will be happy to answer your questions and share insights.

• Check out upcoming Db2 events here.

We look forward to seeing you there!

Questions answered during the call:

Q1: If you have a very high volume of online transactions, is it possible that the Z-lock cannot be obtained?  
If so, what will happen and what can be done?
Answer: There is this Option:
FORCE_ALL
    Heavy workload on the source table might prevent ADMIN_MOVE_TABLE from completing the SWAP phase (SQL0911N reason code 2). In other words, if the source table is heavily used by other applications during the SWAP phase, ADMIN_MOVE_TABLE might rollback due to a deadlock (SQL0911N reason code 2).
    Use this option to force all applications having workload on the source table. Forced applications will receive SQL1224N on all subsequent database manager requests.

Q2: Do you know if IBM fixed the following issues:
1. Failure to finalize the move when a Primary Key is defined without first creating a unique index? It fails because it cannot recreate indexes with Autogenerated names. We developed a process, which recreates all such PKs and thus allows table move.
2. Failure to move tables with Check Constraints created prior to Db2 9.7 (this is not a big deal, of course, as this constraint can be dropped and re-created).
The worst thing is that the failure happens during SWAP, while the issue could be detected at the initiation of the move
It fails with the message that it cannot create index with the SQL12345... name. Because of that we alsways require that a PK is created over a unique index, if possible

Answer:
1. We have improved checks in AMT INIT, but need to double check your case is included.
2. see slide later in the presentation

Q3: why ALTER TABLE is not to be used for performing column operations like drop column etc.? why AMT is used instead?
Answer: Dropping a column requires a classic table reorganization

Q4: where to check history of AMT performed on a Table ?
Answer: in the "change history monitor" or query protocol table of AMT

Q5: can we move system catalog tables using AMT from SMS to Automatic tablespaces 
Answer: No, this is not possible

Join us for an insightful discussion on 20 August, 2024 at 11:00 AM ET to get an overview of how you can move any table within your database with ADMIN_MOVE_TABLE, addressing problems like growth, data placement, compression, or even reorganization of tables. Carola Langwald will cover the technical background on ADMIN_MOVE_TABLE and use cases and insights into new features and functions like size estimation. Furthermore, monitoring of ADMIN_MOVE_TABLE and performance considerations are presented.

Click here to register and add the event to your calendar.

Where is the link to join? Check your email the day before the event!

 How to get the most out of this webinar:

• Explore the Db2 Community.

• Ask questions for the presenter by replying to this discussion post before, during and after the webinar. Carola Langwald will be happy to answer your questions and share insights.

• Check out upcoming Db2 events here.

We look forward to seeing you there!