• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

DB2 Can't drop table after load is terminated badly

DB2 10.5 on Windows

Here is another strange one.  I needed to load a large text file so I created a table and started the Load procedure when the server got rebooted (automatic window update).  The database came up ok after the reboot but now I can't drop the table I was loading into.  The table is empty.  I get this error.

ERROR [55039] [IBM][DB2/NT64] SQL0290N  Table space access is not allowed.

So I google this and it is normally linked to one of these.

1. Database crash and recovery
2. Table is in load pending state
3. Tablespace is in not normal state

Well, none of these apply to me.  The database came back fine.  I can run queries and do everything just fine.  The table is not in load pending state.  And the tablespace is in a normal state.  Other tables in this tablespace are acting normally.  It is just this one table.

I can select from the table but that is it.  I can't insert, or reorg or drop.  I just want to drop it.

Any suggestions?

Thanks!

Jim
0
Jim Youmans
Asked:
Jim Youmans
  • 6
  • 5
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Jim,

It's likely that the load is in an odd state since the reboot prevented it from terminating gracefully.

The LOAD and RESTORE commands have a TERMINATE option that should clear the status of a previous operation.

Can you post the command you were running?

Kent
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
Here is the code.
LOAD CLIENT FROM 
'C:\temp\SCHEMA.TABLENAME.del' OF DEL 
INSERT INTO "SCHEMA"."TABLENAME_IMPORT"
STATISTICS NO
ALLOW NO ACCESS;

Open in new window

I thought the same thing so I looked up the terminate command and ran this.
Select TABSCHEMA, TABNAME, LOAD_STATUS from SYSIBMADM.ADMINTABINFO where load_status = 'PENDING' ;

Open in new window

It returns nothing.  

So then I ran this anyway.
load from 'C:\temp\SCHEMA.TABLENAME.del'  of del terminate into SCHEMA.MYTABLE_IMPORT nonrecoverable

Open in new window

And I get this.

SQL27902N  LOAD RESTART/TERMINATE is not allowed on a table that is not in LOAD PENDING state.

So I am not sure what is going on.

Thanks!

Jim
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  The next step is to check db2diag.log.  There should be a detailed entry that corresponds to the time you received the message "SQL0290N  Table space access is not allowed."
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Jim YoumansSr Database AdministratorAuthor Commented:
OK, so looking at DB2DIAG.LOG I see this.

FUNCTION: DB2 UDB, data management, sqldTableDrop, probe:742
MESSAGE : ZRC=0x80020032=-2147352526=SQLB_NOT_ALLOWED_BACKUP_P
          "Access not allowed. Tblspc Backup Pend."
DATA #1 : String, 60 bytes
Unable to drop table.
RC=80020032, Pool=7, Object=4, Class=0


So I am guessing I need to backup the database to resolve this?  Or just backup the tablespace?

Jim
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Yep.  A backup of the tablespace should solve this.
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
OK, that seems to have fixed the tablespace issue.  But here is another question that is related.  Sometimes when I try to do something like creating an FK or alter a table or in this case, drop a table, I get this.

SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".

I know that is DB2 saying it can't lock the table for the operation within the lock lime (60 seconds).  But when I look to see what is blocking the lock I can't find anything.  If I wait 10 or 15 minutes and try again, it will normally work.   How can I see what is causing the block?

Thank you so much for all your help!!!  I have learned a lot from you.

Jim
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Getting a list of active locks is easier in other engines, like Oracle or SQL Server, as querying their systems tables/views is pretty straight forward.  In DB2 the system function MON_GET_LOCKS is the easiest way to go.

  https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0056428.html
0
 
Kent OlsenData Warehouse Architect / DBACommented:
The large text file that you were loading.  Are you loading it into a table dedicated for that file or are you adding data to an existing table?
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
I created the table just for the file.  So it is existing, but dedicated to the load.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  Everything that gets loaded also gets copied to the log file.  Since this is a purpose created table you don't need to do all of the logging.

It will go faster if you'll turn off logging on the table before the load:

ALTER TABLE xxx NOT LOGGED INITIALLY;
LOAD ....
COMMIT;


If you want to reuse an existing table:

ALTER TABLE xxx NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;
ALTER TABLE xxx NOT LOGGED INITIALLY;
LOAD ....
COMMIT;

If the load fails, the table will have become corrupt and non-recoverable.  Deleting it and recreating it will be necessary.  But since the table's only purpose seems to be to load this specific data that shouldn't be an issue.
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
That is awesome!  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now