Solved

DB2 LOAD operation failed

Posted on 2013-06-20
6
1,542 Views
Last Modified: 2013-06-21
Hi all,

One of the tables got in an inaccessible state after I tried to change a column datatype.
The LOAD failed during that change, and I can't do anything now.

If I try to open the table, I get
com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/NT64] SQL0668N  Operation not allowed for reason code "3" on table "DBO.POSITION".  SQLSTATE=57016

QUIESCE  RESET;
goes with no errors, but doesn't change anything

REORG results in
SQL2216N  SQL error "-668" occurred while reorganizing a database
table or its indexes.


I am not proficient in DB2, especially in administration, so my research lead me nowhere.

Please tell me what I try - specifically,
because "try to cancel the LOAD job" tells me nothing.

Thank toy,

Any help is appreciated.
0
Comment
Question by:darrgyas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39263949
Hi darrgyas,

I doubt that you have a load hung up, but there may be a load status messing you up.

A "LOAD TERMINATE" or "LOAD RESTART" may clear the status.

Repeat the load statement that you issued (it must be the same statement) and include the keyword TERMINATE

If the original statement is:

  LOAD FROM myfile of del INTO table1 ...

then issue:

  LOAD FROM myfile of del TERMINATE INTO table1 ...


What was the original data type of the changed column and what is the new type?


Kent
0
 

Author Comment

by:darrgyas
ID: 39264239
Hi Kent,

I change SMALLint to INTEGER.

The table has appr 250 million records.

Unfortunately, I do not have the original LOAD statement, as it was done automatically by DB2 Control Center.

Is there anything else I can do?
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 39264276
I seldom use Control Center, and never for running backups, so I'm guessing here.  If another expert is more familiar, maybe they'll jump in.

It sounds like there are two issues.  

1)  The ALTER TABLE {table} ALTER COLUMN encountered an error in the data conversion.  If simply copying a small integer to a normal integer caused a conversion error, there's a DB2 error that IBM needs to know about.  (I really don't think that that is what happened.)

What may have happened is that DB2 ran out of space during the conversion and aborted the ALTER statement.  I do not remember if the ALTER logs the change so that it can be rolled back.  If it does, it's probable that the rollback space isn't large enough.  But I would expect that transaction to roll back without a problem.

This ALTER would affect every single row so the rollback space would need to be at least as large as this table.  Depending on row size and blocking it's also possible that growing each row caused the blocks to split, growing the table significantly.


2)  The backup process hung up due to the error above.  The process needs to be cleared.



Can you do anything on the table?  A simple query?

  SELECT COUNT(*) FROM mytable;

I suspect not.  What error message do you get?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:darrgyas
ID: 39264300
Here's what I get:

com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/NT64] SQL0668N  Operation not allowed for reason code "3" on table "DBO.TRACTION".  SQLSTATE=57016
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39264636
Ok.  It looks like we can skip right past item 1 above and deal with the load pending error.



Have you tried doing a LOAD TERMINATE from the Control Center or the Command Line Processor?
0
 

Author Closing Comment

by:darrgyas
ID: 39265873
Thanks for your time, Kent, but I think I dug a hole for myself here. I'll survive.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question