DSXchange

I tried to load data into a Db2 table using DB2 Bulk Load utility on Datastage. Non-recoverable option was set to true. Load finished successfully but left the table in a 'check pending state'. The table has referential constraints on it. I tried to bulk load a table with no referential constraints, the load finished successfully leaving the table in Normal state. Does anybody know how to bring back the table to Normal state after the load finishes in Datastage?

Thanks for your help!

Your DBA.

How can I Bulk Load tables with referential constraint, using Datatsage without leaving them in a check pending state?

The same way you would outside of DataStage.

I would provide specifics if I could but I've never touched DB2 in my life. However, from hanging out here and reading the documentation (are you using the DB2 Connector?) it seems like DB2 always leaves a bulk loaded table with check constraints or referential integrity constraints in that 'check pending' state. Your only option there is if you want that on any descendant tables as well - i.e. DEFERRED v. IMMEDIATE.

Hence my suggestion to ask your DBA. What DB2 steps are required to get the table out of that state? Perhaps they would be candidates for the 'After SQL' property.

And a very quick google shows that "To remove the check pending state, use the SET INTEGRITY statement. The SET INTEGRITY statement checks a table for constraints violations, and takes the table out of check pending state."

My DBA said they cannot give DBA privileges to the User ID to perform SET Integrity in After-SQL property. He said in order to avoid tables with refential Integrity getting in check-pending state after bulk load, I need to load the whole hierarchy , because when you load just a child table DB2 puts that table in check pending mode. Any thoughts on how to load the whole hierarchy?

Nope but I'd wager others with DB2 and DataStage experience do.

What about whatever passes for a 'stored procedure' in DB2? A typical workaround would be to craft a procedure is owned by the table owner that performs the function in question and then your ETL functional user would be granted the ability to execute it.

chulett wrote:Nope but I'd wager others with DB2 and DataStage experience do.

What about whatever passes for a 'stored procedure' in DB2? A typical workaround would be to craft a procedure is owned by the table owner that performs the function in question and then your ETL functional user would be granted the ability to execute it.

Thanks for your suggestion. We did create a stored procedure and called it in After-SQL property of the DB2 connector. It works!