MySQL Bugs: #44610: RCDFMT clause requested when creating DB2 table

Bug #44610	RCDFMT clause requested when creating DB2 table
Submitted:	1 May 2009 16:38	Modified:	14 Jul 2009 15:02
Reporter:	Tim Clark	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DB2SE for IBM i	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Tim Clark	CPU Architecture:	Any
Tags:	RCDFMT

Description:
In order to better support the usage of IBMDB2I tables from within RPG programs, the storage engine should ensure that the RCDFMT name is consistent and predictable for DB2 tables.

The current behavior is to rely upon the system to generate the RCDFMT name when the DB2 table is created. The system generally sets the RCDFMT name to be identical to the initial system name of the object. However, when a table is altered such that the table needs to be re-created, the initial system name of the altered table is of the form "#sql0001" due to the temporary name that MySQL assigns the table. As a result, the RCDFMT name also becomes "#sql0001". This introduces two problems for end users: first, the RCDFMT is now different for what is otherwise the "same" table; second, the RCDFMT name is cryptic. Since there is no way for end users to change the RCDFMT name after a table has been created, the storage engine needs to make sure that the RCDFMT is named well when the table is first created.

How to repeat:
CREATE TABLE test.TABLE1 (i int) ENGINE=IBMDB2I;

Then, from the command line: DSPFD "test"/TABLE1
In the output from the command, the RCDFMT will be listed as TABLE1.

Next:

ALTER TABLE test.TABLE1 ADD COLUMN (f float);

Then, from the command line: DSPFD "test"/TABLE1
In the output from the command, the RCDFMT will be listed as "#sql0001". This should instead be TABLE1.

Suggested fix:
In order to provide predictability, I propose that the storage engine should always explicitly specify the RCDFMT when creating a table. In order to be a valid system identifier, the RCDFMT name should be composed of the first ten alpha-numeric characters in the MySQL table name. Invalid characters could be replaced with underscores.

The following examples show input MySQL statement with the proposed generated DB2 statement:
 create table table1 (i int) engine=ibmdb2i;
=> create table "table1" ("i" int) RCDFMT TABLE1

 create table longtablename (i int) engine=ibmdb2i;
=> create table "longtablename" ("i" int) RCDFMT LONGTABLEN

 create table `table@#$` (i int) engine=ibmdb2i;
=> create table "table@#$" ("i" int) RCDFMT TABLE___

 alter table `innodbtable` engine=ibmdb2i;
=> create table "#sql[...]" ("i" int) RCDFMT INNODBTABL

If a solution exists and the decision is to move (say) a live instance to IBM i and then migrate to DB2 as the storage engine, then the process might be to restore the appropriate parts to the IFS, test execution, when stable, use alter to migrate into the DB2 environment. Since the alter moves data as well, this is a good, secure, reliable method to deploy existing MySQL supported applications to the IBM i environment.

But then any RPG program that is using native I/O operations will need to rename the "#sql0001" record format name in each and every one of the files in order to use them.

And the storage engine, at it's heart, is there to provide access to the MySQL stored data from IBM i program objects.

In SQL, before we had the record format extension on the create table, view and now, index, we did the following:

create table <record_fmt_name> (...
rename table <record_fmt_name> to <file_name>

This left the table with a specific record format name, different from the file name, even though it was created in SQL.

Since MySQL requires V5R4 and up, and the RCDFMT phrase is available in V5R4 and up, there should be no reason that the record format names are mangled in the alter process.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/74310

Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:v.narayanan@sun.com-20090517162523-92p6va4ruqwwqtvb) (merge vers: 5.1.36) (pib:6)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:v.narayanan@sun.com-20090517163051-8p5mbkvf6uis38hf) (merge vers: 6.0.12-alpha) (pib:11)

An entry has been added to the 5.1.36 and 5.4.4 changelogs: 

When creating tables using the IBMDB2I storage engine, the RCDFMT (record format) that would be applied to the corresponding files within the IBM i would be set according to the table name. During a whole table operations, the name could get modified to a value inconsistent with thetable name. In addition, the record format would be inconsistent compared to the file content. The IBMDB2I storage engine now adds an explicit RCDFMT clause to the CREATE TABLE statement passed down to the DB2 storag engine laye

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed to 5.4.2.