asked on

MySQL tables on iSeries / IBMDB2I

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.

Gary Patterson, CISSP

Can you post the layout for the problem DB2 table?

Gary Patterson, CISSP

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.

HelpDeskGeiger

ASKER

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 '' ) ;

ASKER CERTIFIED SOLUTION

Gary Patterson, CISSP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial