I need to write code to delete some tables that are previous 36 months from production as a part of clean up.The tables are stored as tablename_yymm.
I have written it as
bteq << EOF .logon dbname/dwtst01,pwd; .SET ECHOREQ ON .SET RETRY ON .SET TIMEMSG DEFAULT .SET ERRORLEVEL 3807 SEVERITY 0;
.run file logon.txt .set width 200 .set titledashes off .set format off .SET RTITLE' '
.export report file=del_app_work.run
SELECT DISTINCT 'DROP TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';' (Title '') FROM DBC.TABLES WHERE DATABASENAME='dbname'AND TABLEKIND = 'T'AND TABLENAME IN (tablename_1203,tablename_1204) AND CAST(CreateTimeStamp AS DATE) < add_months(date - extract(day from date) + 1, -35);
.Export report file=del_app_work.sql .run file = del_app_work.run
I have identified the tables to be dropped based on createtimestamp but my lead says that it should be cast from table name itself ie extract the month and year from tablename
Unable to do it.
Can anyone here plz help me it is really appreciated.
Thanks in advance!!