Archives of the TeradataForum
Message Posted: Mon, 20 Sep 2010 @ 09:20:20 GMT
We currently submit our DDL script (both in Dev and Prod) through Sql Assistant. Whilst having two minutes to sit and ponder the universe I wondered whether there would, instead, be benefits in changing that process to submit DDL through Bteq.
What we currently have is, I would boast, organized and consistent (and it works) but just wondering whether there are benefits from Bteq (or other) that we're not exploiting with our current approach.
Any thoughts or Bteq snippets that might spring to mind ?
In order to give as well as take within the forum, others might like to consider something simple that we do :
At then start of each script we execute a simple Procedure which logs the fact that a particular script number has started, along with the date of execute and that sort of metadata information. We then continue with this process for every Table structural change within the script, with a simple comment line of basically what the DDL statement is doing. At the end of the script, we then close off the logging with a final call to the Stored Procedure to indicate that the script is complete.
This, over time, gives us a very good repository of change (which from time to time is very useful).
This is an example of what we end up with (names changed to protect the guilty !):
DEVDB1_T 4658 TABLEXYZ 2010-08-10 15:31:50 Table Creation DEVDB1_T 4658 TABLEUVW 2010-08-10 15:32:04 Table Creation DEVDB1_T 4658 TABLERST 2010-08-10 15:32:48 Table Creation . . DEVDB1_T 4658 TABLEOPQ 2010-08-10 16:04:00 Table Creation DEVDB1_T 4658 TABLELMN 2010-08-10 16:04:02 Table Creation DEVDB1_T 4658 TABLECDE 2010-08-11 12:54:32 Added 2 columns and removed 1 col DEVDB1_T 4600 TABLEABC 2010-07-28 14:43:44 2 Removed and 3 added columns DEVDB1_T 4579 TABLEKLM 2010-07-14 16:34:28 Table Creation DEVDB1_T 4566 TABLEIKJ 2010-07-05 11:29:54 Drop Column EDW_LOAD_TD DEVDB1_T 4566 TABLEFGH 2010-07-05 11:30:06 Drop Column EDW_LOAD_TD DEVDB1_T 4540 TABLECDE 2010-07-05 15:21:24 New columns and rename columns DEVDB1_T 4540 TABLEABC 2010-07-05 15:22:25 Increase length TIME_PERIOD_CD DEVDB1_T 4540 TABLECDE 2010-07-05 15:25:48 Table Creation DEVDB1_T 4539 TABLEABC 2010-07-20 11:50:58 New column CAM_ID
|Copyright 2012 - All Rights Reserved|
|Last Modified: 21 Mar 2013|