All Forums Tools
muthm 6 posts Joined 10/08
03 Mar 2014
BTEQ losing header comments of view definitions

Hi,
is there any way to preserve header comments when sending a REPLACE VIEW with BTEQ?
In my scenario, I clone database structures from one system to another. I use BTEQ from a shell or perl scripts to run 'SHOW <object>;' for all objects in the databases, dumping the definitions to .sql files on disk. Then I run those SQL files on the target system, again using BTEQ from shell or perl scripts.
This works fine for making exact one-to-one copies for all kind of objects (functions and procedures are a little bit tricky).
But for views there is a problem: I lose header comments when I send the view definition using BTEQ.
Consider this sample view definition, correctly dumped to a .sql file:

/*
    Header comment:
    This is a test view.
*/
REPLACE VIEW SampleDB.TestView AS
LOCKING ROW FOR ACCESS
SELECT *
FROM DBC.TablesV;

 
If I copy the text to SQL Assistant, send it to the database, and then do a SHOW VIEW, I get the expected (and correct) result:

show view SampleDB.TestView;
show view SampleDB.TestView;

/*
    Header comment:
    This is a test view.
*/
REPLACE VIEW SampleDB.TestView AS
LOCKING ROW FOR ACCESS
SELECT *
FROM DBC.TablesV;

But if I run the sql file with BTEQ, it first nicely tells me that it replaced the view:

/*
    Header comment:
    This is a test view.
*/
REPLACE VIEW SampleDB.TestView AS
LOCKING ROW FOR ACCESS
SELECT *
FROM DBC.TablesV;
*** View has been replaced.
*** Total elapsed time was 1 second.
 

But doing a SHOW VIEW in SQL Assistant reveals that the header comments got lost:

show view SampleDB.TestView;
REPLACE VIEW SampleDB.TestView AS
LOCKING ROW FOR ACCESS
SELECT
*
FROM DBC.TablesV;

I suppose that BTEQ doesn't include the leading comment in the Teradata request sent to the database system.
Is there a way to tell BTEQ to include the comments?
Or does anyone have an other idea how I can retain the header comments from a scripting environment (i.e. not using SQL Assistant interactively)?
Thanks a lot!
Matthias

 

dnoeth 4628 posts Joined 11/04
05 Mar 2014

Hi Matthias,
afaik you can't do that as-is in BTEQ.
The only way i know is to move the comment after the REPLACE, e.g.

REPLACE VIEW SampleDB.TestView AS
/*
    Header comment:
    This is a test view.
*/
LOCKING ROW FOR ACCESS
SELECT *
FROM DBC.TablesV;

 

Dieter

You must sign in to leave a comment.