Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / April 2008

Tip: Looking for answers? Try searching our database.

import into db2 fails

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Christian Welzel - 30 Mar 2008 09:16 GMT
Hi there,

currently i'm trying to migrate a mysql-db to db2 express-c.
therefor i use the data migration toolkit and all seems just
fine until the point where mtk tries to import the data.
the tables are created just fine, at least i can see them in
the db2cc. but while importing data i get these exceptions:

Fri Mar 28 23:39:38 GMT+01:00 2008      ERROR   VERIFICATIONITEM        \
       SQL error -668 on SELECT COUNT(*) FROM "DB2INST1"."ALLIANCE_APPLICATIONS" \
       : com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, \
       SQLERRMC=3;DB2INST1.ALLIANCE_APPLICATIONS, DRIVER=3.50.152
com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, SQLERRMC=3; \
       DB2INST1.ALLIANCE_APPLICATIONS, DRIVER=3.50.152
       at com.ibm.db2.jcc.b.wc.a(wc.java:55)
       at com.ibm.db2.jcc.b.wc.a(wc.java:126)
       at com.ibm.db2.jcc.b.jk.b(jk.java:3578)
       at com.ibm.db2.jcc.b.jk.a(jk.java:3560)
       at com.ibm.db2.jcc.t4.db.a(db.java:730)
       at com.ibm.db2.jcc.t4.db.m(db.java:719)
       at com.ibm.db2.jcc.t4.db.i(db.java:244)
       at com.ibm.db2.jcc.t4.db.c(db.java:52)
       at com.ibm.db2.jcc.t4.t.c(t.java:44)
       at com.ibm.db2.jcc.t4.sb.i(sb.java:153)
       at com.ibm.db2.jcc.b.tk.P(tk.java:1438)
       at com.ibm.db2.jcc.b.tk.a(tk.java:2217)
       at com.ibm.db2.jcc.b.tk.a(tk.java:557)
       at com.ibm.db2.jcc.b.tk.executeQuery(tk.java:541)
       at com.ibm.mtk.model.verification.MTKVerificationItemExtension.verify(MTKVerificationItemExtension.java:2113)
       at com.ibm.mtk.model.verification.MTKVerification.verify(MTKVerification.java:1224)
       at com.ibm.mtk.model.deployment.MTKDeploymentModelElement.deploy(MTKDeploymentModelElement.java:879)
       at com.ibm.mtk.ui.deployment.MTKDeploymentView$11.run(MTKDeploymentView.java:1015)

Why does this happen?
The database is installed on a remote server (linux), mtk is running
on my desktop pc (linux).

In Deploy_ETS.log there are some messages like this:

SQL2036N  Der Pfad für die Datei oder Einheit
"/home/gawain/mtk/projects/B/DataOutScripts/ETS_admin_agb_delict.out" ist
ungültig.

What does this mean?
I can access this file just fine with cat or more.

Signature

MfG, Christian Welzel aka Gawain@Regenbogen

 GPG-Key:     http://www.camlann.de/key.asc
 Fingerprint: 4F50 19BF 3346 36A6 CFA9 DBDC C268 6D24 70A1 AD15

Serge Rielau - 30 Mar 2008 12:13 GMT
> Hi there,
>
[quoted text clipped - 41 lines]
> What does this mean?
> I can access this file just fine with cat or more.

Hmmm.. Not entirely clear what's cause and what's effect. See below for
teh explanation fro SQLCODE -668.
Anyway, is the directory/file accessible by teh DB2 "fenced user id"?
Assuming you clicked through the defaults I'd think that id is the same
as the instance id fro which you punched in the password on install.

Anyway since all this happens on your desktop, if all else fails just
make the directory path and file accessible to everyone...

If after fixing that DB2 still complains about the -668. Follow the
instructions below to get you back on track.

SQL0668N  Operation not allowed for reason code "<reason-code>" on table
      "<table-name>".

Explanation:

Access to table "<table-name>" is restricted. The cause is based on the
following reason codes "<reason-code>":

1        The table is in the Set Integrity Pending No Access state. The
         integrity of the table is not enforced and the content of the
         table may be invalid. An operation on a parent table or an
         underlying table that is not in the Set Integrity Pending No
         Access state may also receive this error if a dependent table
         is in the Set Integrity Pending No Access state.

2        The table is in the No Data Movement state. When in this state,
         operations that cause data movement are disallowed. Data
         movement operations include REDISTRIBUTE, update of database
         partitioning key, update of multi-dimensional clustering key,
         update of range clustering key, update of data partitioning key
         and REORG TABLE.

3        The table is in the Load Pending state. A previous LOAD attempt
         on this table resulted in failure. No access to the table is
         allowed until the LOAD operation is restarted or terminated.

4        The table is the Read Access state. This state can occur during
         on-line LOAD processing (LOAD INSERT with the READ ACCESS
         option), or after an on-line LOAD operation, but before all
         constraints have been validated in the newly appended portion
         of the table using the SET INTEGRITY statement. No update
         activity is allowed on this table.

5        The table is in the Load In Progress state. The LOAD utility is
         currently operating on this table, no access is allowed until
         the LOAD is finished.

6        Materialized query tables that reference a nickname cannot be
         refreshed in ESE.

7        The table is in the reorg pending state. This can occur after
         an ALTER TABLE statement containing a REORG-recommended
         operation.

8        The table is in the alter pending state. This can occur when
         using the table in the same unit of work as an ALTER TABLE
         statement containing a REORG-recommended operation.

9        The table is in Redistribute Pending state. The REDISTRIBUTE
         utility is not completed on this table, no access is allowed
         until the REDISTRIBUTE is finished.

User response:

1        Execute the SET INTEGRITY statement with the IMMEDIATE CHECKED
         option on table "<table-name>" to bring the table out of the
         Set Integrity Pending No Access state. For a user maintained
         materialized query table, execute the statement with the
         IMMEDIATE UNCHECKED option instead of the IMMEDIATE CHECKED
         option.

2        Execute REFRESH TABLE statement on the dependent immediate
         materialized query tables and staging tables of table
         "<table-name>". The contents of these dependent immediate
         materialized query tables and staging tables can be
         incrementally maintained from the appended data of
         "<table-name>" through previous LOAD INSERT operations and from
         the attached data of "<table-name>" through previous ALTER
         TABLE statements with the ATTACH clause.

3        Restart or terminate the previously failed LOAD operation on
         this table by issuing LOAD with the RESTART or TERMINATE option
         respectively.

4        Issue the LOAD QUERY command to check whether the table is in
         the process of being loaded. If yes, wait until the LOAD
         utility has completed, or if necessary, restart or terminate
         previously failed LOAD operation. If LOAD is currently not in
         progress, issue the SET INTEGRITY statement with the IMMEDIATE
         CHECKED option, to validate constraints in the newly loaded
         portion of the table.

5        Wait until the current LOAD operation has finished. You can use
         the LOAD QUERY command to monitor the progress of load.

6        Define a materialized query table using the MAINTAIN BY USER
         option. Then, use an INSERT statement with a subquery to
         populate the materialized query table.

7        Reorganize the table using the REORG TABLE command (note that
         INPLACE REORG TABLE is not allowed for a table that is in the
         reorg pending state).

8        Complete the unit of work, and re-issue the command.

9        If the REDISTRIBUTE utility is working, wait until it finishes
         working on the current table. You can use the LIST UTILITIES
         command to monitor the progress of the REDISTRIBUTE utility. If
         a previous REDISTRIBUTE operation failed and left the table in
         this state, issue the REDISTRIBUTE utility again with the
         CONTINUE or ABORT option and let it finish on this table.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Christian Welzel - 30 Mar 2008 13:49 GMT
Serge Rielau schrieb:

> Hmmm.. Not entirely clear what's cause and what's effect. See below for
> teh explanation fro SQLCODE -668.

> 3        The table is in the Load Pending state. A previous LOAD attempt
>          on this table resulted in failure. No access to the table is
>          allowed until the LOAD operation is restarted or terminated.

This one i found out myself while googling after "load pending state".
I cannot remember where exactly i found this message but in one of the
dialogs or file i read about it.

> 3        Restart or terminate the previously failed LOAD operation on
>          this table by issuing LOAD with the RESTART or TERMINATE option
>          respectively.

i tried to issue some load terminate commands which db2 accepted and returned
"command successful". but in the next migration attempt, the same exceptions
poped up again.

> Anyway, is the directory/file accessible by teh DB2 "fenced user id"?
> Assuming you clicked through the defaults I'd think that id is the same
> as the instance id fro which you punched in the password on install.

i installed db2 on the server as root and the instance is running as db2inst1.
in the mtk i give db2inst1 as user id for the remote db.

> Anyway since all this happens on your desktop, if all else fails just
> make the directory path and file accessible to everyone...

i'll try that.

> If after fixing that DB2 still complains about the -668. Follow the
> instructions below to get you back on track.

Greetings,
Christian.
Christian Welzel - 30 Mar 2008 22:48 GMT
> Anyway since all this happens on your desktop, if all else fails just
> make the directory path and file accessible to everyone...

I made my whole user dir rwx to all (chown -R a+rwx) and tried
again... same messages. I dropped the db2 database and created
it again... next try... same -668 error message.
I will try to reinstall the whole db2 thingy on the server from scratch,
perhaps something changes after that.

Signature

MfG, Christian Welzel

 GPG-Key:     http://www.camlann.de/key.asc
 Fingerprint: 4F50 19BF 3346 36A6 CFA9 DBDC C268 6D24 70A1 AD15

Serge Rielau - 31 Mar 2008 12:06 GMT
>> Anyway since all this happens on your desktop, if all else fails just
>> make the directory path and file accessible to everyone...
[quoted text clipped - 4 lines]
> I will try to reinstall the whole db2 thingy on the server from scratch,
> perhaps something changes after that.

Have you sent a note to the MTK folks?
I have my doubts that reinstalling DB2 will help.
It must be something obvious - in hindsight.

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Christian Welzel - 31 Mar 2008 18:37 GMT
> Have you sent a note to the MTK folks?

I think so.

> I have my doubts that reinstalling DB2 will help.

You are right. It didnt help.
At least now i get more exceptions... but not because of the
new installation but i extracted the data a second time.
Now MTK is complaining about the missing MySqlCatalog database...
again.
Unfortunately i cannot rememeber how i made this error go away
the last time. damn.

> It must be something obvious - in hindsight.

i didnt hope so. This would mean i wasted a lot of time and it
would me my fault ... NEVER! :)

Signature

MfG, Christian Welzel aka Gawain@Regenbogen

 GPG-Key:     http://www.camlann.de/key.asc
 Fingerprint: 4F50 19BF 3346 36A6 CFA9 DBDC C268 6D24 70A1 AD15

Serge Rielau - 31 Mar 2008 18:44 GMT
>> Have you sent a note to the MTK folks?
>
[quoted text clipped - 14 lines]
> i didnt hope so. This would mean i wasted a lot of time and it
> would me my fault ... NEVER! :)

Hmmm.. Have you tried posting in the DB2 Express C forum?
http://www.ibm.com/developerworks/forums/forum.jspa?forumID=805
I'd guess that most expertise on MySQL -> DB2 enablement will be
concentrated there.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Christian Welzel - 01 Apr 2008 15:04 GMT
Serge Rielau schrieb:

>>> Have you sent a note to the MTK folks?

Got some answer. They tell me, that i'm using JCC T4 driver but MTK requires a
JCC T2 driver. I will try to switch the drivers this evening and try again.
If you could figure out how to do that as the driver i'm using is the default
one of my local db2 client installation...

> Hmmm.. Have you tried posting in the DB2 Express C forum?

Nope. I didnt know about this one.
I will try the other jdbc driver and ask there thereafter.

Bye, Christian
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.