Bug #45983 ibmdb2i_create_index_option=1 not working for primary key
Submitted: 6 Jul 2009 21:01 Modified: 14 Jul 2009 15:03
Reporter: Tim Clark Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DB2SE for IBM i Severity:S3 (Non-critical)
Version:5.1.35 OS:Any
Assigned to: Tim Clark CPU Architecture:Any
Tags: ibmdb2i, ibmdb2i_create_index_option, primary key
Triage: Triaged: D3 (Medium)

[6 Jul 2009 21:01] Tim Clark
Description:
With ibmdb2i_create_index_option set to 1, creating an IBMDB2I table with a primary key should produce an additional index that uses EBCDIC hexadecimal sorting. However, this does not work. Adding indexes that are not primary keys does work.

How to repeat:
> use test;
> set ibmdb2i_create_index_option=1;
> create table t1 (i int primary key) engine=ibmdb2i;

If I then go the IBM i command line and DSPLNK OBJ('/qsys.lib/"""test""".lib/*'), there is only the physical file associated with the table. There is no additional index.
Object link            Type     Attribute
"t1".FILE              FILE     PF       
QSQJRN.JRN             JRN        
QSQJRN0001.JRNRCV      JRNRCV     
SYSCHKCST.FILE         FILE     LF
SYSCOLUMNS.FILE        FILE     LF
SYSCST.FILE            FILE     LF
SYSCSTCOL.FILE         FILE     LF
SYSCSTDEP.FILE         FILE     LF
SYSINDEXES.FILE        FILE     LF
SYSKEYCST.FILE         FILE     LF
SYSKEYS.FILE           FILE     LF

Suggested fix:
Honor the ibmdb2i_create_index_option when creating a table with a primary key.
[8 Jul 2009 9:10] Bugs System
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/78191

3014 V Narayanan	2009-07-08
      Bug#45983 ibmdb2i_create_index_option=1 not working for primary key
      
      With ibmdb2i_create_index_option set to 1, creating an IBMDB2I table
      with a primary key should produce an additional index that uses EBCDIC
      hexadecimal sorting. However, this does not work. Adding indexes that
      are not primary keys does work. The ibmdb2i_create_index_option should
      be honoured when creating a table with a primary key.
      
      This patch adds code to the create() function to check for the value
      of the ibmdb2i_create_index_option variable and, when appropriate, to 
      generate a *HEX-based shadow index in DB2 for the primary key. Previously 
      this behavior was limited to secondary indexes.
      
      Additionally, this patch restricts the creation of shadow indexes to
      cases in which a non-*HEX sort sequence is used, as the documentation
      for ibmdb2i_create_index_option describes. Previously, the shadow index
      would in some cases be created even when the MySQL-specific index used
      *HEX sorting, leading to redundant indexes.
      
      Finally, the code used to generate the list of fields for indexes 
      and the code used to generate the SQL statement for the shadow
      indexes has been refactored into individual functions.
     @ mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45983.result
        Bug#45983 ibmdb2i_create_index_option=1 not working for primary key
        
        Result file for the test case.
     @ mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45983.test
        Bug#45983 ibmdb2i_create_index_option=1 not working for primary key
        
        Add tests to verify that the ibmdb2i_create_index_option is being honoured
        when creating a table with a primary key.
     @ storage/ibmdb2i/ha_ibmdb2i.cc
        Bug#45983 ibmdb2i_create_index_option=1 not working for primary key
        
        - Add code to the create() function to check for the value of the
          ibmdb2i_create_index_option variable and, when appropriate, to 
          generate a *HEX-based shadow index in DB2 for the primary key.
        
        - Restrict the creation of shadow indexes to cases in which a
          non-*HEX sort sequence is used.
        
        - Refractor code used to generate the list of fields for indexes
          and the code used to generate the SQL statement for the shadow
          indexes into individual functions.
     @ storage/ibmdb2i/ha_ibmdb2i.h
        Bug#45983 ibmdb2i_create_index_option=1 not working for primary key
        
        Add function prototypes for the functions that.
        
        - Generate the list of fields for indexes
        - Generate the SQL statement for the shadow
          indexes
[10 Jul 2009 11:20] Bugs System
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:v.narayanan@sun.com-20090709125144-y5h71bbcqkw2vmei) (merge vers: 5.4.4-alpha) (pib:11)
[13 Jul 2009 17:48] Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090713174543-cd2x7q1gi1hzoand) (version source revid:staale.smedseng@sun.com-20090710151930-6e6kq5tp7ux1rtbh) (merge vers: 5.1.37) (pib:11)
[14 Jul 2009 15:03] MC Brown
An entry has been added to the 5.1.37 and 5.4.4 changelogs: 

When creating tables using the IBMDB2I storage engine with the ibmdb2i_create_index_option option set to 1, creating an IBMDB2I table with a primary key should produce an additional index that uses EBCDIC hexadecimal sorting, but this index was not created.
[12 Aug 2009 22:11] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 23:03] Paul Dubois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:48] Bugs System
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)
[27 Aug 2009 16:32] Bugs System
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)
[7 Oct 2009 16:25] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.