Phantom Read / Garbled Data for WAS 5 to DB2 7 OS390

[Ed Wallen]

I experienced a problem when trying to read and write data from WAS 5 to a DB2 7 mainframe database. The data would get garbled..........so on a read, a record that existed in the database would not get found (the primary key would get garbled into a different number) and for writes, the primary key would be some integer completely out of sequence (and often negative). No exceptions were thrown, so this was hard to track down. Below are my findings.

My environment: WAS 5.0.2.7 on AIX 5.1 going to DB2 v7.1 OS/390 database through DB2Connect 8.1.5 using the JCC Universal type 2 XA driver.

IBM recommended using patch UQ84225.


Error description
DB2DDF DDFL09 DB2DRDA defect pq82641 dpq82641
Integer value is erroreously being byte reversed at Insert when
client coming in with connection pooling.
Problem summary
****************************************************************
* USERS AFFECTED: All Distributed Data Facility (DDF) users. *
* Specifically those with DB2 Connect V6 *
* (or better) client applications that are *
* using connection pooling. *
****************************************************************
* PROBLEM DESCRIPTION: Numeric values sent to a DB2/390 server *
* from a DB2 Connect V6 (or more recent) *
* client application may incorrectly be *
* thought to be in a byte reversed *
* format and DB2/390 will reverse the *
* bytes, creating an incorrect value. *
* *
****************************************************************


-Ed

[Ed Wallen]

Due to this issue, some of the primary key Integers that got inserted into the database fell victim to this IBM bug. I had to provide the application team with a means of converting the identifier to the correct value (reversing the bytes). Here is the simple class that I wrote:

  
 
import java.util.*;
 
 
/**
 * Reverses an Integer's bytes.  An integer is 4 bytes long.  If we say that the bytes are ordered
 * 1 2 3 4 when the input integer is passed in, this class will output the integer value when the bytes 
 * are ordered 4 3 2 1.
 * @author Ed Wallen
 */
public class ByteConverter {
 
    /**
     * Expects 1 argument.  Can be either an integer or the location of a properties file that
     * contains integers as keys.
     */
    public static void main(String[] args) {
 
        if (args.length != 1) {
            System.out.println("\n\nUsage:  " + "\n\tjava ByteConverter <Java Properties File>  " +
                    "\n\tjava ByteConverter <Integer>  ");
            System.exit(-1);
        }
 
        String input = args[0];
        boolean isConverted = false;
 
        try {
            int i = Integer.parseInt(input);
            
            System.out.println("\n\n\n\n--------------------------\nINPUT:  " + input);
            
            int result = reverseBytes(i);
            
            System.out.println("OUTPUT: " + result);
            
            isConverted = true;
        }
        catch (Exception e) {
            // string was not an integer.  Try to locate properties file using input.
            System.out.println("Argument was not an Integer.  Will attempt to locate properties file" +
                    " using: " + input);
        }
 
        if (!isConverted) {
 
            ResourceBundle bundle = ResourceBundle.getBundle(input);
 
            for (java.util.Enumeration enum = bundle.getKeys(); enum.hasMoreElements()  :wink:   {
 
                String key = (String) enum.nextElement();
                try {
                    int i = Integer.parseInt(key);
                    System.out.println("\n\n\n\n--------------------------\nINPUT:  " + i);
                    int result = reverseBytes(i);
                    System.out.println("OUTPUT: " + result);
                }
                catch (Exception e) {
                    // string was not an integer.
                    System.out.println("Key \"" + key + "\" is not an Integer.  System Exit.");
                    System.exit(-1);
                }
 
            }
 
        }
 
        System.exit(0);
    }
 
 
 
    public static int reverseBytes(int i) {
        return ((i >>> 24)           ) |
               ((i >>   8) &   0xFF00) |
               ((i <<   8) & 0xFF0000) |
               ((i << 24));
    }
  
 
}
 

-Ed
[ April 01, 2005: Message edited by: Ed Wallen ]