Solved

MySQL tables on iSeries / IBMDB2I

Posted on 2013-12-12
4
828 Views
Last Modified: 2014-02-04
We are using the zendDBi database and are attempting to setup a MySQL table for an existing DB2 table. Using the method found here:
http://wsip-174-79-32-155.ph.ph.cox.net/wiki/index.php/MySql/DB2StorageEngineDocument
(in the section titled “Existing IBM i DB2 Tables” near the bottom of the page).

This works for most tables but for one table in particular it does not seem to be working.

I’m able to create the MySQL table and copy the FRM files however when I try to access the table via mysql I get the error
“Got error 2102 'The file has changed and might not be compatible with the MySQL table definition' from IBMDB2I”

I read a similar article here:
http://forums.zend.com/viewtopic.php?f=77&t=104778

However no FID file is created. (this did work for another table). Here is what I have tried so far:

•      Checked file permissions on the mysql frm file
•      Checked the permissions on the DB2 table
•      Using “flush tables” command in MySQL
•      Manually creating an FID file with the correct identification number
•      Set IBMDB2I as default engine in my.conf
•      Making sure the table does not contain zoned decimal fields

I’m curious as to what to try next.
0
Comment
Question by:HelpDeskGeiger
  • 3
4 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
Can you post the layout for the problem DB2 table?
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
There will be an QSQSRVR job that is processing requests for the MySQL client.  You can find it by using the WRKOBJLCK command on the user profile (*USRPRF) used by the client.  

Inspect the QSQSRVR job log for additional messages.  

If you want to post them here, please include all of the relevant messages, and the second-level help text for each message, too.
0
 

Author Comment

by:HelpDeskGeiger
Comment Utility
Hi Gary,

I have to get some help from one of the iseries people here to get the DDS however I was able to get this from iseries navigator:

--  Generate SQL
--  Version:                         V7R1M0 100423
--  Generated on:                    12/13/13 08:39:26
--  Relational Database:             S10F3EC1
--  Standards Option:                ISO
CREATE TABLE GB1555AFGD.SROPRG (
--  SQL1509   10   Format name PRG for SROPRG in GB1555AFGD ignored.
      PGOPNO DECIMAL(2, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGOPNO ignored.
      PGDEID CHAR(8) NOT NULL DEFAULT '' ,
      PGSC01 CHAR(1) NOT NULL DEFAULT '' ,
      PGSC02 CHAR(1) NOT NULL DEFAULT '' ,
      PGSC03 CHAR(1) NOT NULL DEFAULT '' ,
      PGSTAT CHAR(1) NOT NULL DEFAULT '' ,
      PGPRDC CHAR(35) NOT NULL DEFAULT '' ,
      PGPSNO CHAR(35) NOT NULL DEFAULT '' ,
      PGDESC CHAR(50) NOT NULL DEFAULT '' ,
      PGPSNA CHAR(30) NOT NULL DEFAULT '' ,
      PGPTPE CHAR(5) NOT NULL DEFAULT '' ,
      PGCSNO CHAR(18) NOT NULL DEFAULT '' ,
      PGPGRP CHAR(5) NOT NULL DEFAULT '' ,
      PGAGRP CHAR(4) NOT NULL DEFAULT '' ,
      PGPCA1 CHAR(5) NOT NULL DEFAULT '' ,
      PGPCA2 CHAR(5) NOT NULL DEFAULT '' ,
      PGPCA3 CHAR(5) NOT NULL DEFAULT '' ,
      PGFICC CHAR(1) NOT NULL DEFAULT '' ,
      PGTIXC CHAR(1) NOT NULL DEFAULT '' ,
      PGBTCC CHAR(1) NOT NULL DEFAULT '' ,
      PGABCC CHAR(1) NOT NULL DEFAULT '' ,
      PGSENC CHAR(1) NOT NULL DEFAULT '' ,
      PGOHIS CHAR(1) NOT NULL DEFAULT '' ,
      PGCDAT DECIMAL(8, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGCDAT ignored.
      PGSTUN CHAR(5) NOT NULL DEFAULT '' ,
      PGDECC DECIMAL(1, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGDECC ignored.
      PGMSUP CHAR(11) NOT NULL DEFAULT '' ,
      PGRESP CHAR(10) NOT NULL DEFAULT '' ,
      PGDSUN CHAR(5) NOT NULL DEFAULT '' ,
      PGCOUN CHAR(4) NOT NULL DEFAULT '' ,
      PGBALC CHAR(1) NOT NULL DEFAULT '' ,
      PGSSTA CHAR(1) NOT NULL DEFAULT '' ,
      PGOVDC CHAR(1) NOT NULL DEFAULT '' ,
      PGDSPC CHAR(1) NOT NULL DEFAULT '' ,
      PGSTRT CHAR(2) NOT NULL DEFAULT '' ,
      PGPSTA CHAR(1) NOT NULL DEFAULT '' ,
      PGROPC CHAR(1) NOT NULL DEFAULT '' ,
      PGROPP DECIMAL(15, 3) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGROPP ignored.
      PGEOQC CHAR(1) NOT NULL DEFAULT '' ,
      PGEOQT DECIMAL(15, 3) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGEOQT ignored.
      PGSECT CHAR(1) NOT NULL DEFAULT '' ,
      PGSECS DECIMAL(15, 3) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGSECS ignored.
      PGFORE DECIMAL(15, 3) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGFORE ignored.
      PGLPCO DECIMAL(17, 4) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGLPCO ignored.
      PGTLPC DECIMAL(17, 4) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGTLPC ignored.
      PGAPCO DECIMAL(17, 4) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGAPCO ignored.
      PGTAPC DECIMAL(17, 4) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGTAPC ignored.
      PGSTCO DECIMAL(17, 4) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGSTCO ignored.
      PGUFN1 DECIMAL(17, 3) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGUFN1 ignored.
      PGUFN2 DECIMAL(17, 3) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGUFN2 ignored.
      PGUFA1 CHAR(15) NOT NULL DEFAULT '' ,
      PGUFA2 CHAR(15) NOT NULL DEFAULT '' ,
      PGASAE CHAR(1) NOT NULL DEFAULT '' ,
      PGASBI CHAR(1) NOT NULL DEFAULT '' ,
      PGADEL CHAR(1) NOT NULL DEFAULT '' ,
      PGLSDY DECIMAL(4, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGLSDY ignored.
      PGSBDC CHAR(1) NOT NULL DEFAULT '' ,
      PGSLSP CHAR(1) NOT NULL DEFAULT '' ,
      PGUDH1 CHAR(20) NOT NULL DEFAULT '' ,
      PGUDH2 CHAR(20) NOT NULL DEFAULT '' ,
      PGUDH3 CHAR(20) NOT NULL DEFAULT '' ,
      PGUDH4 CHAR(20) NOT NULL DEFAULT '' ,
      PGSLSS CHAR(1) NOT NULL DEFAULT '' ,
      PGGTPE CHAR(5) NOT NULL DEFAULT '' ,
      PGSHPG CHAR(5) NOT NULL DEFAULT '' ,
      PGDRPC CHAR(1) NOT NULL DEFAULT '' ,
      PGPCA4 CHAR(5) NOT NULL DEFAULT '' ,
      PGPCA5 CHAR(5) NOT NULL DEFAULT '' ,
      PGPCA6 CHAR(5) NOT NULL DEFAULT '' ,
      PGSNFP CHAR(20) NOT NULL DEFAULT '' ,
      PGSNNM CHAR(20) NOT NULL DEFAULT '' ,
      PGSNNS DECIMAL(3, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGSNNS ignored.
      PGLRDY DECIMAL(4, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGLRDY ignored.
      PGCONG CHAR(5) NOT NULL DEFAULT '' ,
      PGHCOD CHAR(5) NOT NULL DEFAULT '' ,
      PGMSQR DECIMAL(5, 2) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGMSQR ignored.
      PGUASO CHAR(1) NOT NULL DEFAULT '' ,
      PGPDGR CHAR(5) NOT NULL DEFAULT '' ,
      PGCSTC DECIMAL(17, 4) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGCSTC ignored.
      PGEFDT DECIMAL(8, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGEFDT ignored.
      PGMPSP CHAR(11) NOT NULL DEFAULT '' ,
      PGTOOL CHAR(6) NOT NULL DEFAULT '' ,
      PGPPRT DECIMAL(1, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGPPRT ignored.
      PGPPNC CHAR(1) NOT NULL DEFAULT '' ,
      PGBMAP CHAR(20) NOT NULL DEFAULT '' ,
      PGCCHA CHAR(1) NOT NULL DEFAULT '' ,
      PGSQGP CHAR(5) NOT NULL DEFAULT '' ,
      PGPPGR CHAR(5) NOT NULL DEFAULT '' ,
      PGPLAN CHAR(10) NOT NULL DEFAULT '' ,
      PGHSTC CHAR(5) NOT NULL DEFAULT '' ,
      PGPVER DECIMAL(3, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGPVER ignored.
      PGDRNR CHAR(20) NOT NULL DEFAULT '' ,
      PGPRCL CHAR(1) NOT NULL DEFAULT '' ,
      PGPRFA CHAR(5) NOT NULL DEFAULT '' ,
      PGPRSE CHAR(5) NOT NULL DEFAULT '' ,
      PGAVCO CHAR(1) NOT NULL DEFAULT '' ,
      PGPLMD CHAR(1) NOT NULL DEFAULT '' ,
      PGISUN CHAR(5) NOT NULL DEFAULT '' ,
      PGSRVP CHAR(1) NOT NULL DEFAULT '' ,
      PGSPGR CHAR(5) NOT NULL DEFAULT '' ,
      PGISET CHAR(5) NOT NULL DEFAULT '' ,
      PGIS01 CHAR(35) NOT NULL DEFAULT '' ,
      PGIS02 CHAR(35) NOT NULL DEFAULT '' ,
      PGIS03 CHAR(35) NOT NULL DEFAULT '' ,
      PGIS04 CHAR(35) NOT NULL DEFAULT '' ,
      PGIS05 CHAR(35) NOT NULL DEFAULT '' ,
      PGIS06 CHAR(35) NOT NULL DEFAULT '' ,
      PGISPR CHAR(35) NOT NULL DEFAULT '' ,
      PGSCUN CHAR(5) NOT NULL DEFAULT '' ,
      PGSTPU DECIMAL(17, 4) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGSTPU ignored.
      PGPCDE CHAR(1) NOT NULL DEFAULT '' ,
      PGCTNB DECIMAL(19, 4) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGCTNB ignored.
      PGPROP DECIMAL(5, 2) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGPROP ignored.
      PGCTNP DECIMAL(5, 2) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGCTNP ignored.
      PGPCGR CHAR(5) NOT NULL DEFAULT '' ,
      PGCDYN CHAR(1) NOT NULL DEFAULT '' ,
      PGTOYN CHAR(1) NOT NULL DEFAULT '' ,
      PGMSDS CHAR(10) NOT NULL DEFAULT '' ,
      PGSATX CHAR(1) NOT NULL DEFAULT '' ,
      PGSTCA CHAR(15) NOT NULL DEFAULT '' ,
      PGSTCL DECIMAL(3, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGSTCL ignored.
      PGCFCO CHAR(2) NOT NULL DEFAULT '' ,
      PGCRTI DECIMAL(6, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGCRTI ignored.
      PGCTYP DECIMAL(1, 0) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGCTYP ignored.
      PGREBA CHAR(1) NOT NULL DEFAULT '' ,
      PGAVMT CHAR(1) NOT NULL DEFAULT '' ,
      PGAVOP CHAR(1) NOT NULL DEFAULT '' ,
      PGAVSP CHAR(1) NOT NULL DEFAULT '' ,
      PGBATM CHAR(20) NOT NULL DEFAULT '' ,
      PGCSPU DECIMAL(17, 4) NOT NULL DEFAULT 0 ,
--  SQL150D   10   EDTCDE in column PGCSPU ignored.
      PGWHCD CHAR(10) NOT NULL DEFAULT '' ,
      PGASRC CHAR(1) NOT NULL DEFAULT '' ,
      PGIRGP CHAR(5) NOT NULL DEFAULT '' ) ;
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
Comment Utility
DML is fine. No need for DDS.

Looks OK - just CHAR and DECIMAL columns, and everything is within field and record length limitations.  Only thing that catches my eye is the EDTCDE keywords.  Did you have EDTCDE specified on other tables that worked ok?

As you know, 2102  means that MySQL thinks that this table has changed.  

I'm a little confused:  You mentioned that no FID file was created, and that you manually created a FID file.

Usually, the first time a table is accessed via IBMDB2I, a FID file is generated automatically.  This error should only be thrown when, on a subsequent access to the table, the FID on the table doesn't match the value in the FID file.  

If no FID file exists, I don't see how this error could be getting thrown (barring a bug in  IBMDB2I).

Please re-test and check the QSQSRVR job log for useful messages.

IBM provides support for the IBMDB2I storage engine (but not for MySQL itself).  You may want to open a PMR with IBM if it looks like this is really a defect in the IBMDB2I storage engine.

http://www-01.ibm.com/support/docview.wss?uid=nas8N1013084
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now