Fix (APAR):  JR23979

Status:  Fix

Release:  6.0.1.2,6.0.1.1

Operating System:  AIX,HP-UX,Linux,Solaris,Windows,z/OS

Supersedes Fixes:  

CMVC Defect:  JR23979

Byte size of APAR:  264065

Date: 2007-03-02

Abstract:  DB2 jdbc driver throws an exception when creating an index on one of the Relationship tables what causes the application to not get installed (SQL0614N).  The DB2 limitation that c

Description/symptom of problem:  
JR23979 resolves the following problem:

ERROR DESCRIPTION:                                              
DB2 jdbc driver throws an exception when creating an index on   
one of the                                                      
Relationship tables what causes the application to not get      
installed and that the following error is thrown                
                                                                
com.ibm.wbiserver.relationshipservice.exceptions.RelationshipSer
viceExce                                                        
ption:                                                          
Error in role "rolename"                                        
runtime                                                         
schema                                                          
creation: com.ibm.db2.jcc.a.SqlException: The index or index    
extension                                                       
"xxx" cannot be created or altered because the                  
combined                                                        
length of the specified columns is too long.".                  
                                                                
The DB2 limitation that causes the exception to be thrown is    
that the index                                                  
cannot be created because the sum of the key column internal    
lengths                                                         
exceeds 1024 bytes.                                             

LOCAL FIX:                                                      
none/Using cloudscape database or swapping out the relationship 
tables only to cloudscape                                       

PROBLEM SUMMARY

USERS AFFECTED:
WebSphere Process Server 6.0.1 users.

PROBLEM DESCRIPTION:
DB2 jdbc driver throws an exception
when creating an index on one of the
Relationship tables what causes the
application to not get installed
(SQL0614N).

The DB2 limitation that causes the
exception to be thrown is that the
index cannot be created because the
sum of the key column internal
lengths exceeds 1024.

RECOMMENDATION:
None

There are 4 Role KeyAttributes defined in the problematic
role (TestMemRel_InboundASBO role), each of them correspond
to one attribute of the BO (InboundASBO).
These BO attributes have a maximum length of 10 characters
defined in its BO schema definition but when they are
referenced by the Relationship Role via the Role
KeyAttributes, this length value doesn't get stored and
when the Relationship Service creates the role table, it
will create a column table for each of those KeyAttributes
of type VARCHAR(255). Since we will have 4 of this columns in
the role table (plus some others internal to the Relationship
Service) and the Relationship Service tries to create an
index with those 4 columns plus another two columns of type
INT and TIMESTAMP, the limit of 1024 bytes is reached and the
corresponding sql exception is thrown (SQL0614N).

Relationship Service stores the column information in a
particular class (com.ibm.wbiserver.relationshipservice.
dblayer.SchemaInfoTeller$ColumnInfo) when executing
DB2Creator.createRoleBaseSchema() but it doesn't introspect
the ASBO definition for the restrictions on that particular
element so it could get the max. length for that BO element and

therefore create a column table of that right size.

PROBLEM CONCLUSION:                                             
The fix for this problem is contained in WPS 6.0.1.3.  There    
are iFix's available for WPS 6.0.1.1 and WPS 6.0.1.2.           

Directions to apply fix:  Before applying the iFix ensure that WebSphere Process Server 6.0.1 is installed on the system.
Also download and follow Update Installer installation instructions.  The Update Installer can be downloaded from the following link:
http://www.ibm.com/support/docview.wss?rs=180&uid=swg21205991

1) Copy the pak file to the directory maintenance in the UpdateInstaller directory

2) Shutdown WebSphere Process Server. It is important that you perform a controlled and complete shutdown of the server to ensure that all transactions have completed, before installing the fix.

3) Run the UpdateInstaller and follow the prompts.

4) Restart WebSphere

Directions to remove fix:  NOTE:  FIXES MUST BE REMOVED IN THE REVERSE ORDER IN WHICH THEY WERE APPLIED.  DO NOT REMOVE A FIX UNLESS  ALL FIXES APPLIED AFTER IT HAVE FIRST BEEN REMOVED.  YOU MAY REAPPLY ANY REMOVED FIX.

Example:  If your system has fix1, fix2, and fix3 applied in that order and fix2 is to be removed, fix3 must be removed first, then fix2 may be removed

1) Shutdown WebSphere. It is important that you perform a controlled and complete shutdown of the server to ensure that all transactions have completed, before installing the fix.

2) Follow the Fix instructions that are packaged with the Fix Installer on how to uninstall and reinstall the Fix.

3) Restart WebSphere

Directions to re-apply fix:  1) Shutdown WebSphere. It is important that you perform a controlled and complete shutdown of the server to ensure that all transactions have completed, before installing the fix.

2) Run the UpdateInstaller and follow the instructions. The Update Installer can be downloaded from the following link:
http://www.ibm.com/support/docview.wss?rs=180&uid=swg21205991

3) Restart WebSphere Process Server

Additional Information: