DB2 Connector Bulk Load to Log Retain Database
Posted: Fri Jun 29, 2012 2:06 pm
Hello DataStagers!
Your assistance please.
Environment: Database- DB2 9.7 Unix, Database is Log Retain Mode (Not Circular Logging); IIS 8.7 Unix
I am attempting to use the DB2 Connector configured with Write Mode: Bulk Load, Table Action: Append. The table loads correctly; however, since the logging method for the database is set to "Log Retain" the table is now in backup pending state and is unavailable until a database backup is performed. You will recieive a "Table space access is not allowed. SQLSTATE=55039" error if you try to insert records into the table before the backup is complete.
If I were writting SQL to load the table, the syntax is (for deleting all the records, inserting records is similiar) "load from /dev/null of del replace into table COPY YES to /dev/null;" The Replace keyword prevents logging. The key is the "COPY YES to /dev/null" which causes the table not to go into back-up pending state.
My question: How has anyone solved this issue to bulk load to a database set to log retain using the DB2 connector stage without placing the table into back-up pending state? Is there a way to configure the stage or add the COPY Yes... to the SQL that the stage generates?
Please note that this issue does not arise when the logging method of the database is set to circular logging, which option we do not have.
Thanks for your assistance.
God Bless!
Your assistance please.
Environment: Database- DB2 9.7 Unix, Database is Log Retain Mode (Not Circular Logging); IIS 8.7 Unix
I am attempting to use the DB2 Connector configured with Write Mode: Bulk Load, Table Action: Append. The table loads correctly; however, since the logging method for the database is set to "Log Retain" the table is now in backup pending state and is unavailable until a database backup is performed. You will recieive a "Table space access is not allowed. SQLSTATE=55039" error if you try to insert records into the table before the backup is complete.
If I were writting SQL to load the table, the syntax is (for deleting all the records, inserting records is similiar) "load from /dev/null of del replace into table COPY YES to /dev/null;" The Replace keyword prevents logging. The key is the "COPY YES to /dev/null" which causes the table not to go into back-up pending state.
My question: How has anyone solved this issue to bulk load to a database set to log retain using the DB2 connector stage without placing the table into back-up pending state? Is there a way to configure the stage or add the COPY Yes... to the SQL that the stage generates?
Please note that this issue does not arise when the logging method of the database is set to circular logging, which option we do not have.
Thanks for your assistance.
God Bless!