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
Jim YoumansSr Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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 OlsenDBACommented:
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
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

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 OlsenDBACommented:
Yep.  A backup of the tablespace should solve this.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 OlsenDBACommented:
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 OlsenDBACommented:
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 OlsenDBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows OS

From novice to tech pro — start learning today.