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
Netezza Connector- Temp Tables - Not Dropped Automatically
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 17
- Joined: Thu Jan 15, 2015 5:21 am
- Location: India
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
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
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
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