import into db2 fails
Christian Welzel
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.
--
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
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.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Christian Welzel
> 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
> 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.
--
MfG, Christian Welzel
Serge Rielau
> Serge Rielau wrote:
>
>> 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.
>
I have my doubts that reinstalling DB2 will help.
It must be something obvious - in hindsight.
Cheers
Serge
Christian Welzel
> 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! :)
--
MfG, Christian Welzel aka Gawain@Regenbogen
GPG-Key: http://www.camlann.de/key.asc
Serge Rielau
> Serge Rielau wrote:
>
>> 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! :)
>
http://www.ibm.com/developerworks/forums/forum.jspa?forumID=805
I'd guess that most expertise on MySQL -> DB2 enablement will be
concentrated there.
Christian Welzel
>>> 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