All Forums Database
Agrias 6 posts Joined 06/11
20 Feb 2013
I need to add starbox comments to table DDL. Works with view DDL, but not table DDL.

Hello everyone,
I'm on a new project and one of our requirements is that all DDL objects must be created using a STARBOX in the DDL for tracking purposes.  Here's what this looks like when creating a view:
/***************************************************** ****************************************************** **********
** DATABASENAME - DATABASENAME **
** OBJECT NAME - AGRIAS123_V **
** PROJECT - AGRIAS PROJECT **
** CREATION DATE - 2012-12-19 **
** INTIAL AUTHOR - AGRIAS, AGRIAS COMPANY **
** DESCRIPTION - AGRIAS TEST TABLE **
** **
***************************************** ---------- Change Log ---------- ****************************************
** Change Date Developer Name Change Desc **
** ------------------- ------------------------ -------------------------------- **
** 2012-12-19 AGRIAS INITIAL CREATION **
****************************************************** ****************************************************** *********/
CREATE TABLE GEADW_SHOP_S.TESTTLC123
(COLUMN1 VARCHAR(1)
, COLUMN2 INT
, COLUMN3 DECIMAL(3,2))
PRIMARY INDEX (COLUMN2);
So when I create this view, and do a SHOW VIEW DATABASENAME.AGRIAS123_V, I see this starbox in the system.
However, if I use a starbox while creating a TABLE, it does not store in the database.  Doing a SHOW TABLE DATABASENAME.AGRIAS123 will only show the database DDL code.
Does anyone know of a way to get around this?  I tried creating these tables in SQL Assistant and BTEQ - neither worked.  I also tried executing in parallel, and adjusting the driver details to not use extended SQL. Does anyone have any tricks for getting the comments to store in table DDL?
Thanks,
Agrias
 

dnoeth 4628 posts Joined 11/04
20 Feb 2013

Hi Agrias,
no, there's no way to keep any comment/formatting within the table DDL.
The output of a SHOW TABLE is not retrieved from any system table, it's created ad-hoc by the parser.
There is the RequestText column in dbc.tvm which holds the source code of the *latest* query modifying the table structure, i.e. when you do a CREATE INDEX/ALTER TABLE, the original CREATE TABLE text (including the comments) is overwritten.
Dieter

Dieter

Agrias 6 posts Joined 06/11
21 Feb 2013

Thanks for the response Dieter, I'll look into what we can do with DBC.TVM.
 

asadali.khan 11 posts Joined 10/10
26 Feb 2013

Hi Agrias,
 
I am not sure about the earlier versions, We are using 13.10, dbc.Tables stores last run of the definitions including the starbox comments in Request_Text Column.
 
Show Request_Text FROM dbc.Tables
WHERE DatabaseName=<Datebase Name>
AND TableName = <TableName>
 
Hope this would solve your problem.
 
Br,
Asad

Asad Ali Khan

You must sign in to leave a comment.