Purge the work tables cretaed a month before and create report with the list of work tables created last month

Database
Enthusiast

Purge the work tables cretaed a month before and create report with the list of work tables created last month

Hi All,

I need to purge work tables which were created a month before. I am using the below query to extract the list of all such tables:

SELECT DISTINCT 'DROP TABLE DB1.' || B.TABLENAME || ' ;'

FROM dbc.tables WHERE tablekind = 'T' AND databasename = 'DB1'

AND TRIM(tablename) LIKE any

('WK_%0','WK_%1','WK_%2','WK_%3','WK_%4','WK_%5','WK_% 6','WK_%7','WK_%8','WK_%9')

AND CAST(CreateTimeStamp AS DATE) <  add_months(date - extract(day from date) + 1, -1)

;

But, I need to run .HANG 10; command after every 50 drops of work tables.

One way is to try to insert the .HANG 10; command after every 50 drop statements in the above extracted file using DFSORT. Is there any other way we can perform this?

Thanks in advance,

Terankit

7 REPLIES
WAQ
Enthusiast

Re: Purge the work tables cretaed a month before and create report with the list of work tables created last month

Try using recursion.

Supporter

Re: Purge the work tables cretaed a month before and create report with the list of work tables created last month

try 

SELECT DISTINCT 'DROP TABLE DB1.' || TABLENAME || ' ;
' !! case when row_number() over (order by tablename) mod 10 = 0 then '.hang 50;' else '' end

FROM dbc.tables WHERE tablekind = 'T' AND databasename = 'DB1'

AND TRIM(tablename) LIKE any

('WK_%0','WK_%1','WK_%2','WK_%3','WK_%4','WK_%5','WK_% 6','WK_%7','WK_%8','WK_%9')

AND CAST(CreateTimeStamp AS DATE) < add_months(date - extract(day from date) + 1, -1)

;

Enthusiast

Re: Purge the work tables cretaed a month before and create report with the list of work tables created last month

Hi Waq,

Can you please explain a bit? I am not aware of this.Thanks.

Supporter

Re: Purge the work tables cretaed a month before and create report with the list of work tables created last month

hm I mixed the 10 and 50...

check also dieters posts in 

http://forums.teradata.com/forum/tools/bteq-cannot-write-hexa-character-in-output-file

if you have trouble with the newline in the export...

WAQ
Enthusiast

Re: Purge the work tables cretaed a month before and create report with the list of work tables created last month

Hi terankit,

Use the query provided by Ulrich. It will give you the desired result.

Enthusiast

Re: Purge the work tables cretaed a month before and create report with the list of work tables created last month

Thanks a lot Ulrich and Waq. That query gave me the desired output perfectly. Thanks again.

Terankit

Enthusiast

Re: Purge the work tables cretaed a month before and create report with the list of work tables created last month

Hi Ulrich,

I have used this query to add ".HANG10" after every 25 statements. But, when I executed this query using BTEQ it is not processing ".HANG10" command with the DROP statement. I think we can not use DROP statement and .HANG in single line..because when I manually moved the .HANG10 command in the new line it went fine.

Can you please let me know how to do this because the above query will add .HANG 10 immediately after the DROP Command.

Thanks,

Terankit