PM28886: Indexes IX300B1 and IX300B2 are missing for DB2 for z/OS

APAR status

• Closed as program error.

Error description

• The indexes IX300B1 and IX300B2 have been introduced in fixpack
  3 of WebSphere Portal 6.1 (6.1.0.3). Database transfer
  procedures started after application of fixpack 3 or newer will
  create them correctly. However, installations of WebSphere
  Portal that already used databases on DB2 for z/OS before
  fipack
  3 or newer has been applied, will not have these two indexes,
  but IX300B instead. In that case the configuration task of
  cumulative fix 06 or newer will fail. Also a migration to
  WebSphere Portal 7.0 will not be successful.
  
  Error message when configuring APAR PM16278 as part of
  cumulative fix 06 or later:
  
  [sqlproc] DbDomain: release
  [sqlproc] Database autocommit parameter true
  [sqlproc] No delimiter has been specified, using [;] to
  separate the SQL statements.
  [sqlproc] Reading file /opt/IBM/WebSphere/wp_profile/ConfigEng
  ine/config/work/db/db2_zos/release/PM16278_install.sql
  [sqlproc] Connected to jdbc:db2://dbserver.mycorp
  .com:50000/DB2LOC
  [sqlproc] Setting database autocommit to:true
  [sqlproc] ALTER INDEX RELEASE.IX300B1 NOT PADDED
  [sqlproc] Error during statement execution!
  [sqlproc] com.ibm.db2.jcc.b.co: DB2 SQL Error: SQLCODE=-204,
  SQLSTATE=42704, SQLERRMC=RELEASE.IX300B1, DRIVER=3.53.70
  [sqlproc]     at com.ibm.db2.jcc.b.bd.a(bd.java:676)
  [sqlproc]     at com.ibm.db2.jcc.b.bd.a(bd.java:60)
  [sqlproc]     at com.ibm.db2.jcc.b.bd.a(bd.java:127)
  [sqlproc]     at com.ibm.db2.jcc.b.fm.c(fm.java:2474)
  [sqlproc]     at com.ibm.db2.jcc.b.fm.d(fm.java:2451)
  [sqlproc]     at com.ibm.db2.jcc.b.fm.b(fm.java:1892)
  [sqlproc]     at com.ibm.db2.jcc.t4.db.h(db.java:219)
  [sqlproc]     at com.ibm.db2.jcc.t4.db.b(db.java:45)
  [sqlproc]     at com.ibm.db2.jcc.t4.t.b(t.java:38)
  [sqlproc]     at com.ibm.db2.jcc.t4.sb.g(sb.java:120)
  [sqlproc]     at com.ibm.db2.jcc.b.fm.ab(fm.java:1887)
  [sqlproc]     at com.ibm.db2.jcc.b.fm.a(fm.java:2812)
  [sqlproc]     at com.ibm.db2.jcc.b.fm.e(fm.java:961)
  ...
  

Local fix

• Drop index IX300B and create index IX300B1 and IX300B2 in
  database domains "release", "community" and "customization"
  manually according to the definitions in SQL script
  "wps_inittables.sql". This script can be found in the
  installation directory of WebSphere Portal, e.g.
  /opt/IBM/WebSphere/PortalServer/base/wp.db
  .impl/config/templates/db/db2_zos/<domain>/wps_inittables.sql,
  where <domain> is a placeholder for the name of the actual
  database domain. Valid values are "release", "community" and
  "customization".
  

Problem summary

• The indexes IX300B1 and IX300B2 have been introduced with
  WebSphere Portal 6.1.0.3).
  Database transfer procedures started after application of
  fixpack 3 or newer will create them correctly. However,
  installations of WebSphere Portal that already used databases on
  DB2 for z/OS before fipack 3 or newer has been applied, will not
  have these two indexes, but IX300B instead. In that case the
  configuration task of cumulative fix 06 or newer will fail. Also
  a migration to WebSphere Portal 7.0 will not be successful.
  Error message when configuring APAR PM16278 as part of
  cumulative fix 06 or later:
  
        [sqlproc] DbDomain: release [sqlproc] Database autocommit
  parameter true [sqlproc] No delimiter has been specified, using
  [;] to separate the SQL statements.
        [sqlproc] Reading file
  /opt/IBM/WebSphere/wp_profile/ConfigEng
  ine/config/work/db/db2_zos/release/PM16278_install.sql
        [sqlproc] Connected to jdbc:db2://dbserver.mycorp
  .com:50000/DB2LOC [sqlproc] Setting database autocommit to:true
        [sqlproc] ALTER INDEX RELEASE.IX300B1 NOT PADDED [sqlproc]
  Error during statement execution!
        [sqlproc] com.ibm.db2.jcc.b.co: DB2 SQL Error:
  SQLCODE=-204, SQLSTATE=42704, SQLERRMC=RELEASE.IX300B1,
  DRIVER=3.53.70
        [sqlproc]     at com.ibm.db2.jcc.b.bd.a(bd.java:676)
        [sqlproc]     at com.ibm.db2.jcc.b.bd.a(bd.java:60)
        [sqlproc]     at com.ibm.db2.jcc.b.bd.a(bd.java:127)
        [sqlproc]     at com.ibm.db2.jcc.b.fm.c(fm.java:2474)
        [sqlproc]     at com.ibm.db2.jcc.b.fm.d(fm.java:2451)
        [sqlproc]     at com.ibm.db2.jcc.b.fm.b(fm.java:1892)
        [sqlproc]     at com.ibm.db2.jcc.t4.db.h(db.java:219)
        [sqlproc]     at com.ibm.db2.jcc.t4.db.b(db.java:45)
        [sqlproc]     at com.ibm.db2.jcc.t4.t.b(t.java:38)
        [sqlproc]     at com.ibm.db2.jcc.t4.sb.g(sb.java:120)
        [sqlproc]     at com.ibm.db2.jcc.b.fm.ab(fm.java:1887)
        [sqlproc]     at com.ibm.db2.jcc.b.fm.a(fm.java:2812)
        [sqlproc]     at com.ibm.db2.jcc.b.fm.e(fm.java:961) ...
  

Problem conclusion

• The APAR drops index IX300B and creates the missing indexes
  IX300B1 and IX300B2 in database domains "release", "community"
  and "customization".
  
  The corresponding SQL statements are:
  
  DROP INDEX @<domain>.DbSchema@.IX300B;
  
  CREATE INDEX @<domain>.DbSchema@.IX300B1 ON
  @<domain>.DbSchema@.PORT_INST (PARENT_OID, PREF_SCOPE_TYPE,
  PREF_SCOPE_UID)
     USING STOGROUP @<domain>.DbStorageGroup@ PRIQTY 512 SECQTY -1
  BUFFERPOOL @<domain>.DbIndex4KBufferPoolName@;
  
  CREATE INDEX @<domain>.DbSchema@.IX300B2 ON
  @<domain>.DbSchema@.PORT_INST (PARENT_SL, PREF_SCOPE_TYPE,
  PREF_SCOPE_UID)
     USING STOGROUP @<domain>.DbStorageGroup@ PRIQTY 512 SECQTY -1
  BUFFERPOOL @<domain>.DbIndex4KBufferPoolName@;
  
  where "<domain>" is a placeholder for the name of the actual
  database domain and tokens surrounded by '@' characters are
  placeholders for equally named settings defined in
  "wp_profile/ConfigEngine/properties/wkplc_comp.properties".
  
  Version Information:
     Portal Version(s): 6.1.0.3
      Pre-Requisite(s): ---
       Co-Requisite(s): ---
  
     Portal Version(s): 6.1.0.4
      Pre-Requisite(s): ---
       Co-Requisite(s): ---
  
     Portal Version(s): 6.1.0.5
      Pre-Requisite(s): ---
       Co-Requisite(s): ---
  
  Manual Steps:
  Execute configuration task "ConfigEngine[.sh|.bat]
  apply-PM28886"
  
  Platform Specific:
     This fix applies to all platforms.
  
  PM28886 is part of Cumulative Fix 11 - available on Fix Central.
  
  Portal 6.1.0.3 / 6.1.5.0:
  http://www.ibm.com/eserver/support/fixes/fixcentral/swgquickorde
  r?apar=PM29643&productid=WebSphere%20Portal&brandid=5
  
  Portal 6.1.0.4 / 6.1.5.1:
  http://www.ibm.com/eserver/support/fixes/fixcentral/swgquickorde
  r?apar=PM29644&productid=WebSphere%20Portal&brandid=5
  
  Portal 6.1.0.5 / 6.1.5.2:
  http://www.ibm.com/eserver/support/fixes/fixcentral/swgquickorde
  r?apar=PM29645&productid=WebSphere%20Portal&brandid=5
  
  You may need to type or paste the complete address into your Web
  browser.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WEBSPHERE PORTA

• Fixed component ID

  5724E7600

Applicable component levels

• R610 PSY

     UP

• R615 PSY

     UP