Archives of the TeradataForum

Message Posted: Mon, 20 Sep 2010 @ 09:20:20 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Guys,

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

Regards

David Clough
Database Developer
Database Design Group

	<Prev	Next>		<<First	<Prev	Next>	Last>>