Informix

 View Only
  • 1.  in-place alter - get fields

    Posted Sun February 21, 2021 04:07 AM

    Hello,

    Depending on what I saw here

    https://youtu.be/xsylAz5U1sY?t=1239

    I ran this script for finding the unresolved in-place alter.

    SELECT dbsname, trim(owner) ||"."|| tabname AS fullname

                    FROM sysmaster:systabnames t, sysmaster:sysptnhdr h

                    WHERE h.partnum = t.partnum

                    AND dbsname NOT IN ("sysmaster")

                    AND pta_totpgs != 0

    and I see that we have lot of tables with schema changes.

    To fix them, as I saw here https://youtu.be/xsylAz5U1sY?t=1223 , we need to do a dummy update, but for this we need to know which fields got a change.

    So, is it possible to get the fields' names in addition of tables' names?

     

    Thanks



    ------------------------------
    Sh To
    ------------------------------

    #Informix


  • 2.  RE: in-place alter - get fields

    IBM Champion
    Posted Sun February 21, 2021 08:53 AM
    Sh To:

    You no longer have to do a dummy update to resolve incomplete in-place alters, there is an SQL API function to do that with less overhead and impact:

    database sysadmin;
    execute function task( 'update_ipa', 'tablename', 'database', 'owner' );

    Optionally you can include the keyword 'parallel' in the command: 'update_ipa parallel' to process the update using multiple threads which is most useful for partitioned tables.

    If you do decide to go the dummy update route, that's fine, but 1) be careful not to cause a long transaction rollback, and 2) you don't have to update the new or modified columns specifically, any single column will do. So, I usually just set the one of the key columns to itself: "SET some_key = some_key".

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: in-place alter - get fields

    Posted Mon February 22, 2021 01:06 AM
    Thanks a lot

    ------------------------------
    Sh To
    ------------------------------