Netezza Connector- Temp Tables - Not Dropped Automatically

nvalia · Post by **nvalia** » Tue Apr 21, 2015 6:36 am

Hi,

We are using Netezza Connector stage to load data to a Netezza Table using nzload. I have noticed when the job fails, since Netezza extensively uses External Tables, tables named like "NZCC_20150417154349152486_7252_1" are created bur NOT dropped even if we select the option "Temporary Work Table Mode" = Automatic" and "Drop Table" = Yes

Any way we can ensure such intermediate temp tables gets dropped, irrespective of whether the Datastage job fails or succeeds as we do not want these meaningless tables sitting in the Database?

Thanks,
NV

maniphilip · Post by **maniphilip** » Tue Apr 21, 2015 11:26 pm

Even we faced the same issue in our project.
In the scenario that you mentioned above if the job will not successfully completes then this NZCC_* tables will remain in the DB.
As a workaround we created temporary work table for each table , and selected the option 'Temporary Work Table mode as existing' and the 'Truncate table option as YES'.It worked.

May be you can try this as a workaround.

Thanks,
Mani

nvalia · Post by **nvalia** » Thu Apr 23, 2015 12:51 pm

Thanks for the reply Mani,
So if we have 100's of tables, you manually created a Temporary Work Table for each of these once and then they would be Truncated and loaded everyday (daily cycle) ?

nvalia · Post by **nvalia** » Fri May 01, 2015 2:40 pm

I created a Stored Procedure that loops for Tables/External Tables starting with NZCC_% found via the _V_Table and Dropp them as a Post Process as part of the ETL cycle.

Since Netezza does not allow Cross Database DDL/DML, create this same procedure in Staging and Target (Star Schema) Database and run it as the last step in the cycle. If there will be any such temp tables it will drop those else do nothing