Missing Timestamps and AltEnableTSCopy

I’m working on a deploy for an Oracle (10g) database and I’ve been asked to preserve data. Because most of my tables are compressed I’ve got “use alter when possible” disabled (that and the fact that you rarely get alters anyway but that probably deserves its own post someday). So, PowerDesigner does this:

1. Rename the original table to “tmp_Table”
2. Create the new table
3. Insert into table (col1, …, colN) select col1, …, colN from “tmp_Table”

When I test the script, I discover that my audit columns (“Created Datetime”, “Last Update Datetime”) are missing from my insert statements. No errors reported. No warnings.  No question marks for me to find and fix.  They’re just not there.  This seemed a bit unusual.

Turns out there’s an option in the XDB file that allows you to skip timestamp columns when you reload the table.  Probably handy, but not what I want.  More interesting, the default is set to omit them.  Even more interesting, the option is hidden when you look in the XDB file.  I’m assuming the thinking is that timestamp columns will be populated with defaults (or by trigger) when the data is re-inserted but, in this case, I don’t want my table alterations to change the audit trail information in the table and it seems a bold assumption that all of your timestamp columns are audit trail data anyway.

To change the value, you need to go to Edit Database\Script\Objects\Column then right click on table and select “Add Items”.

Scroll through the list of options that we apparently don’t need to know about and  find AltEnableTSCopy.

Add it to the list of options, and then change the default from 0 (skip timestamp columns) to 1 (copy timestamp columns).  You should now see your timestamp columns included in your insert statements.

I think this behavior is really poorly thought out.

1. The default should be to include all columns in the insert statement rather than omitting them
2. If you’re going to have the default be to omit columns from an insert statement, the parameter to configure this should really be visible without having to go in and manually add it.

I did take a few minutes to peak at the rest of the hidden options in the column and table objects in the XDB file and some of them appear quite useful.  I hope to complete a more thorough review in the future.  If I find anything interesting, I hope to post it here.