Add a new column to an existing table while retaining table data

Sort:
You are not authorized to post a reply.
Author
Messages
Woozy
Veteran Member
Posts: 709
Veteran Member
    I have a custom table that has been around for some time.  The business has requested a modification which requires the addition of an additional column to the table.  Can I make this change without losing the existing data in the table?  If not, what is the best way to export/import the data that will work with a new table layout?

    By the way, I will be making the table change in DEV which will then be promoted to PROD.  I'm most concerned that the folks who do the promotion are told what to do to make this work without data loss.

    Thanks for your help.
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    The.Sam.Groves
    Veteran Member
    Posts: 89
    Veteran Member
      The SQL statement to add a column to a table is:

      ALTER  ADD    

      by default the add should not modify existing data in the table, however any existing rows will receive the default value you've defined (or not defined) for the column.

      However you may run into unintended consequences that a DBA may be required to assist you on when doing this. These can include things such as the table needing to be reorganized by the server to reallocate the space it was assigned and 'defragment' the table into a new space.

      Or am I misunderstanding the question?
      Scott Perrier
      Veteran Member
      Posts: 39
      Veteran Member
        if the custom table is created with the lawson tools, then yes. Using DBDEF will allow you to add a column to the table. the blddbdict will create the table change file. Then the dbreorg will dump table in the old format and them load the table with the new column.
        Woozy
        Veteran Member
        Posts: 709
        Veteran Member
          Thanks The.Sam.Groves and Scott Perrier,

          Since we are under managed services, so we don't have access to do a direct SQL alter...I'd be much happier if I could do it that way.  I just wanted to be sure that the Lawson utilities wouldn't truncate the data.

          Scott - do I need to use a specific flag on the dbreorg to retain the data?

          Thanks!
          Kelly Meade
          J. R. Simplot Company
          Boise, ID
          Scott Perrier
          Veteran Member
          Posts: 39
          Veteran Member
            it depends on the parms selected but the default is safe mode which is what you want to do.
            dbreorg -S {productline}
            Usage: To reorganize a database.
            Syntax: dbreorg [-SCFYdin] [-W WFFile] {{-p DA} | PL}
            dbreorg [-lcs] {{-p DA} | PL}
            DA is DataArea and PL is ProductLine
            S - Safe mode (default). Dump data before altering db tables
            C - Conservative mode. Do not alter db tables
            F - Fast mode. Do not dump data before altering db tables
            Y - Yes. Do not prompt to continue in Fast mode (requires -F)
            d - No physical database exists
            i - Rebuild DataId dictionaries
            n - Do not run srgen
            p - Reorg just specified DA
            G - Do Garbage collection. If GCFile is not specified, reorg
            all files
            l - List files to be reorganized; no reorg
            c - Show file changes (requires -l)
            s - List number of records in the files (requires -l)
            Scott Perrier
            Veteran Member
            Posts: 39
            Veteran Member
              a friendly reminder that once you alter the table in your Dev system, you can no longer cleanly refresh that table/data from production. It can still be done but takes some rework just like when the CTP install has a table change. It is best to refresh the table before you convert it, when possible.
              Woozy
              Veteran Member
              Posts: 709
              Veteran Member
                Perfect.  Thanks Much!

                Yep, we've run into the data refresh issue many times - mostly with new tables.  Hopefully we can get the change in and promoted before the next data refresh.  Fortunately, this is a very simple change.

                I appreciate your help!
                Kelly Meade
                J. R. Simplot Company
                Boise, ID
                You are not authorized to post a reply.