CODEWITHSUNDEEP
db2db2-luw
Rade
Rade

Reputation: 43

File loading issues in DB2 using Load utility

I have a .csv file, comma-delimited (located at C:/). I am using the DB2 LOAD utility to load data present in the CSV file in a DB2 table. 

LOAD CLIENT FROM C:\Users\somepath\FileName.csv of del 
  MODIFIED BY NOCHARDEL COLDEL, insert into SchemaName.TABLE_NAME;

CSV file has 25 rows. After the utility completed I got an error message for NOCHARDEL. My table has all 25 rows properly loaded. Now when I try to execute an insert/update/delete statement on any of the tables present in that schema I am getting following error.

Lookup Error - DB2 Database Error: ERROR [55039] [IBM][DB2/AIX64] SQL0290N Table space access is not allowed.

Could you please help me whether I am making any mistake or missing a parameter that is causing lock on the table.

Earlier while loading the file similar situation occurred, where DBA confirmed that Table space in question is in “load in progress” state

Upvotes: 0

Views: 6167

Answers (3)

Rade
Rade

Reputation: 43

Thanks @mustaccio. I had 60 Million rows to insert. I was using 25 as sample to check the outcome. To add another point, we later came to know that this is a known DB2 bug that keeps the load in progress state (DB2 is unable to acknowledge that the load has completed and the session remains open indefinitely) and place the table space in backup pending state. Recovery is the only option to release the table space once it is in pending state. This issue is fixed in fix pack 10 as per the DB2 team (we are yet to deploy and test). Mean while NONRECOVERABLE key word is working fine for us

Upvotes: 0

pavel_sustr
pavel_sustr

Reputation: 307

The reason why your table is stuck in the LOAD IN PROGRESS state is the NOCHARDEL error happening at the end of the LOAD.

Have you tried restarting the database? This should reinitialize all table spaces and remove any rogue states.

http://www-01.ibm.com/support/docview.wss?uid=swg1IC65395
http://www-01.ibm.com/support/docview.wss?uid=swg21427102

Upvotes: -1

mustaccio
mustaccio

Reputation: 18945

Changes generated by the DB2 LOAD utility are not logged (one of the side-effects of its high performance). If the database crashes immediately after the load it will be impossible to recover the table that was loaded by replaying log records, because there are no such records. For this reason the tablespace containing the loaded table is automatically placed in the BACKUP PENDING mode, forcing you to take a backup of that tablespace or the entire database to ensure it is fully recoverable.

There are options that you can specify for the LOAD command that can help you avoid this situation in the future:

  • NONRECOVERABLE -- this option does not place the tablespace into the BACKUP PENDING mode, but, as its name implies, the table you're loading to becomes non-recoverable in case of a crash, and your only option in that situation will be to drop and re-create the table.

  • COPY YES -- this option creates a copy of the table prior to loading, which can be used to recover the table to its pre-LOAD state in case of a crash.

If you are only loading 25 records, I suggest you use the IMPORT utility instead -- it does not have these restrictions because it is fully logged (at the price of lower performance, which for 25 records won't matter).

Upvotes: 2

Related Questions