Db2 for z/OS & Db2ZAI

 View Only

  • 1.  ALTER TABLE ADD COLUMN

    Posted Wed April 27, 2022 05:19 PM

    When altering a table to add a column – char(01) not null with default.

    Question – does the table first need stopped at commit and started in UT status – then alter to add new column?

    Or just add new column while table is in RW status?

    Maybe it depends on how much the table is accessed?   This is a very large table accessed by many applications on a 7x24 basis.


    #Db2forz/OS


  • 2.  RE: ALTER TABLE ADD COLUMN

    Posted Thu April 28, 2022 02:34 AM
    Hello Donna,

    You can alter add the column without stopping the table(space). No worries. 

    Kind regards,
    Toine Michielse

    ------------------------------
    Toine Michielse
    ------------------------------

    Original Message


  • 3.  RE: ALTER TABLE ADD COLUMN

    Posted Thu April 28, 2022 09:00 AM
    Hi,

    The alter will need an SKPT LOCK over all the packages associated with he table so you will need to find a quiet period of time in order to run the DDL change without affecting service and having application contention issues.

    Sergi.

    ------------------------------
    Sergi Padró i Blasi
    ------------------------------

    Original Message


  • 4.  RE: ALTER TABLE ADD COLUMN

    Posted Thu April 28, 2022 08:55 AM
    Contention issues related to the table and all associated packages activity can be found, but the change can be done inflight. It should be done in a quiet period of activity; it would be useful to take care of ddltox parm in order to minimize a service disruption. Some sql statements can be affected by the new structure, specially these with unspecified column names (select * from, insert into x values(....))

    ------------------------------
    Sergi Padró i Blasi
    ------------------------------

    Original Message


  • 5.  RE: ALTER TABLE ADD COLUMN

    Posted Thu April 28, 2022 09:01 AM
    Hi Donna,

    The alter is an online schema change that does not require an outage, however it does require exclusive control of the object to make the change.  Long running transactions that have not committed will block the alter.  This includes DELETE, UPDATE, INSERT and SELECT.  The alter DDL will wait for locks and claimers to commit or rollback for the amount of time specified by the IRLMRWT Resource Timeout zParm before it times out.  Transactions that subsequently come in will queue up in a lock-wait state behind the alter. This lock-wait behavior can be observed in a monitoring tool such as Omegamon for Db2.

    The Db2 command DISPLAY BLOCKERS (dbname) DETAIL, where "dbname" is the database name that contains the table, can be used to identify transactions that might block DDL.  Look for transactions that have a lock or claim on the table space, table or index for the table that will be altered, then check the AGE to see how long the transaction has been holding the lock or claim.  If transactions are persistently holding claims or locks for a period of time longer than the IRLMRWT setting, then the alter DDL will likely time out.  SELECT only transactions are often found to be holding claims for an excessively long period of time because people do not realize a commit is required to release the claim as long as the tread is in Db2.  This is a common issue with Db2 programs used by started tasks.  If this type of transaction is observed, updating the program with commit logic after the SELECT SQL will often resolve the issue.

    An alternative to the DISPLAY BLOCKERS Db2 command is the SYSIBMADM.BLOCKING_THREADS built-in table function which can be executed in SPUFI or from a client application. 

    Before executing an alter, check the list of changes that invalidate packages Changes that invalidate packages - IBM Documentation in the IBM Db2 documentation.  The alter in the provided example does not invalidate packages, however there are alters that will.

    The alter will put the table space in the non-restrictive advisory reorg pending status which will be resolved by a REORG or LOAD-replace.  The follow up REORG or LOAD can be done at anytime in the future.  In the meantime, Db2 will keep track of the rows that existed before the alter, the rows where the new column has been updated and the rows that were inserted after the alter.

    Regards,

    ------------------------------
    STEPHEN NEWTON
    ------------------------------

    Original Message


  • 6.  RE: ALTER TABLE ADD COLUMN

    Posted Thu April 28, 2022 09:02 AM
    Hi Donna,

    The alter is an online schema change that does not require an outage, however it does require brief exclusive control of the table.  Long running transactions that have not committed or rolled back will block the alter DDL.  This includes SELECT, UPDATE, DELETE and INSERT.  The alter DDL will queue up in a lock-wait state behind any transactions that hold locks or claims on the table or its related objects.  The DDL will wait the period of time specified in the IRLMRWT Resource Timeout zParm before it times out and fail.  Subsequent transactions will queue up behind the DDL in a lock-wait state.  If the DDL is successful, the subsequent transactions should be fine.  If the DDL times out, most or all of the subsequent transactions should continue without encountering a timeout condition.  The lock-wait behavior can be observed in a monitoring tool such as Omegamon for Db2.

    The DISPLAY BLOCKERS (dbname) DETAIL Db2 command or the SYSIBMADM.BLOCKING_THREADS built-in table function can be executed to identify transactions that could potentially block the DDL.  If the command or function returns a lot of data, I will use the table function from a Db2 client on my PC, then import the output into a spread sheet for further analysis.  Look for entries in the command report or function output related to the table, its table space or its index(es), then check the AGE to identify transactions that hold locks or claims longer then the IRLMRWT timeout period.  If none are found, the alter DDL should be successful.  If any are found, then it will likely be necessary to schedule the change for a time when the transactions are not running.  Adding commit logic to SELECT only transactions will release the claims.

    As an additional note, before altering a table space, table or index, check the current list of changes that will invalidate Db2 packages in the online Db2 documentation (Changes that invalidate packagesChanges that invalidate packages).  After the alter, a REORG or LOAD-replace will remove the non-restrictive advisory reorg-pending status from the table space.  If the utilities are executed at the partition level, the status will be removed once all partitions have been processed.  The REORG or LOAD is not required after the alter, but it is recommended that it be done at some time in the future.

    Regards,

    ------------------------------
    STEPHEN NEWTON
    ------------------------------

    Original Message


  • 7.  RE: ALTER TABLE ADD COLUMN

    Posted Thu April 28, 2022 10:44 AM

    Thanks everyone for the helpful responses !

     




    Original Message


  • 8.  RE: ALTER TABLE ADD COLUMN

    Posted Thu April 28, 2022 09:02 AM
    I usually just add the column.

    ------------------------------
    Bruno Mondragon
    ------------------------------

    Original Message


  • 9.  RE: ALTER TABLE ADD COLUMN

    Posted Fri April 29, 2022 03:51 PM
    You can add columns to the table while the table is online, provided it is not defined with a User Exit.

    It is highly advised to run a Reorg followed by Runstats as soon as possible. Part the reason is for performance as only new rows entered will have column populated with data, older columns use a pointer when the new column is needed. So extra machines instructions are need prior to the reorg being done.


    Original Message