Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1375
Roger French	1315
mark.cook	1244

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

6 Replies

0 Subscribed to this topic

17 Subscribed to this forum

Sort:

Author

Messages

Woozy

Veteran Member Send Private Message

Posts: 709

10/15/2013 6:48 PM

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.

The.Sam.Groves

Veteran Member Send Private Message

Posts: 89

10/15/2013 7:10 PM

The SQL statement to add a column to a table is:

[code]ALTER ADD [/code]

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 Send Private Message

Posts: 39

10/15/2013 7:48 PM

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 Send Private Message

Posts: 709

10/15/2013 8:00 PM

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!

Scott Perrier

Veteran Member Send Private Message

Posts: 39

10/15/2013 8:50 PM

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 Send Private Message

Posts: 39

10/15/2013 8:55 PM

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 Send Private Message

Posts: 709

10/15/2013 9:15 PM

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!