DB2 Connector Bulk Load to Log Retain Database

[darrreever]

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!

[MT]

Hi darrreever,

the backupo pending in DB2 comes for a reason - to be recoverable!

I strongly recommand that you tak to the DB2 admins before you go on!


So here is the answer to your question:
The DB2 Connector has a option "Copy loaded data" you can use this to create the copy. A copy to /dev/null does not make sense and again would violate the recoverability.
If you really choose to be NOT RECOVERABLE you could set the option "Non-recoverable load" to Yes in the DB2 Connector. In this case do not ask what happend if you have to do a recovery

best regards
Michael

[darrreever]

Hello DataStagers!

Thank you MT for your reply!

If you bulk load a table in a DB2 database that is in Log Retain mode, it will place the tablespace in Back-up pending. If you do not want to be able to recover the load if the database goes down before the next back-up (which was OK in my case), you can configure the DB2 Connector in this manner:

Copy Loaded data: Use device or directory
Loaded data copy location: /dev/null

Once you bulk load using this configuration, the tablespace will still be in normal state, in other words, you can still load into the tables.

The DataStage Help documentation for "Load Control Properties (DB2 Connector)" is more helpful than usual. However, to really become adept in using the stage, review the DB2 help on the "Load Command" (http://pic.dhe.ibm.com/infocenter/db2lu ... 08305.html) . The configuration options of the DB2 Connector matches nearly one to one with the "syntax" options of the DB2 Load command. Since the DBAs are very familiar with those syntax options, being able to use that terminology will greatly facilitate bridging the ETL / DBA divide. Further, the explanation in the DB2 Load Command Help will greatly increase your understanding of what's happening in the database.

Checkout the explanation of Indexing Mode and Check Pending Cascade in the DataStage DB2 Connector help as well, particularly if you are loading into a 3NF table structure with Check Integrity turned-on.

Thanks again.

God Bless!