Solved

MySQL tables on iSeries / IBMDB2I

Posted on 2013-12-12
4
836 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
ID: 39714513
Can you post the layout for the problem DB2 table?
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39714565
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
ID: 39716694
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
ID: 39717149
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

864 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

23 Experts available now in Live!

Get 1:1 Help Now