BTEQ losing header comments of view definitions

Tools & Utilities
Enthusiast

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




1 REPLY
Junior Contributor

Re: BTEQ losing header comments of view definitions

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;