PK79220: FETCHING FROM CLOB USING SUBSTRING/CODEUNITS16 CAUSES DATA LOSS.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Fetching from clob using substring/codeunits16 causes data loss.
  DB2INCORR/K
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS v8 and DB2 9 for z/OS users *
  *                 of LOBs.                                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: An incorrect result set can be returned *
  *                      from DB2 when using the SUBSTRING       *
  *                      built-in function with a LOB data type. *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An incorrect result set can be returned when using the SUBSTRING
  built-in function with a LOB data type and a CODEUNITS16 string
  expression value.
  
  The reason for the incorrect output was that DB2 mistakenly
  detected an 'incomplete string' condition for a complete unicode
  string, and as a result, replaced it with padding characters.
  
  The following example could produce an an incorrect result.
  
  Step 1. Create a Table with a LOB column.
  
    CREATE TABLE CLITBLCU
         (INT4         INTEGER,
          CLOB9K       CLOB(9K)       FOR MIXED DATA,
          R1           ROWID          NOT NULL GENERATED ALWAYS)
          CCSID UNICODE;
  
  
  Step 2. Insert into the Table.
  
    strcpy(SQLSTMT.DATA, "INSERT INTO CLITBLCU (INT4,CLOB9K)");
    strcat(SQLSTMT.DATA, " VALUES (?,?)");
    SQLSTMT.LEN=strlen(SQLSTMT.DATA);
    EXEC SQL PREPARE S1 FROM :SQLSTMT;
  
     memset(H1CHR9K, 0, sizeof(H1CHR9K));
     for (jx=1; jx<=1650; jx++)
     {
        wcscat((wchar_t *)H1CHR9K,
               (wchar_t *)"\x20\xAC\x00\xC3\x00\x00");
     }
  
        strcpy(myinsqlda->sqldaid,"SQLDA   ");
        SETSQLDOUBLED(myinsqlda, SQLDOUBLED);
        myinsqlda->sqld = 2;
        myinsqlda->sqln = 2;
  
        myinsqlda->sqlvar 0 .sqltype = 496;
        myinsqlda->sqlvar 0 .sqllen  = 4;
        myinsqlda->sqlvar 0 .sqldata = (char *)&H1INT4;
        myinsqlda->sqlvar 0 .sqlind  = 0;
  
        myinsqlda->sqlvar 1 .sqltype = 408;
        myinsqlda->sqlvar 1 .sqllen  = 0;
        myinsqlda->sqlvar 1 .sqldata = (char *)H1CHR9K;
        myinsqlda->sqlvar 1 .sqlind  = 0;
        SETSQLDALONGLEN(myinsqlda,1,18434);
        SETSQLDALENPTR(myinsqlda,1,&LNH1CHR9K);
  
        myinsqlda->sqldaid 5  = '+';
        myinsqlda->sqlvar 1 .sqlname.length = 8;
  
       ccsid_addr = (struct ccsid_overlay*)
          myinsqlda->sqlvar 1 .sqlname.data;
       ccsid_addr->null1 = 0;
       ccsid_addr->ccsid = 1200;
       ccsid_addr->flag  = 0;
       ccsid_addr->dimension = 0;
  
       printf("\nAPC06 EXECUTE                     NUMBER=  4");
       EXEC SQL EXECUTE S1 USING DESCRIPTOR :*myinsqlda;
  
  
  Step 3. Declare a CURSOR to be used for FETCH.
  
    EXEC SQL DECLARE :H1CHR9K VARIABLE CCSID 1200;
  
    EXEC SQL DECLARE C1 CURSOR FOR S1;
  
       strcpy(SQLSTMT.DATA,
        "SELECT SUBSTRING(CLOB9K, 1, 3300, CODEUNITS16) ");
       strcat(SQLSTMT.DATA,
        "FROM CLITBLCU WHERE INT4=16 FOR FETCH ONLY");
       SQLSTMT.LEN=strlen(SQLSTMT.DATA);
  
       printf("\nAPCXX  PREPARE STMT               number=  3");
       EXEC SQL PREPARE S1 FROM :SQLSTMT;
  
       printf("\nAPCXX  OPEN CURSOR                number=  4");
       EXEC SQL OPEN C1;
  
       memset(H1CHR9K, 0, sizeof(H1CHR9K));
  
       printf("\nAPC03 FETCH NEXT ROW              number=  6");
       EXEC SQL FETCH NEXT C1 INTO :H1CHR9K;
  
  
  Step 4. Check the result. An incorrect result could occur when
          processing the
  
          SELECT SUBSTRING(CLOB9K, 1, 3300, CODEUNITS16)
  
         statement.
  

Problem conclusion

• The code in DB2 is modified to process a unicode string
  correctly when using the SUBSTRING built-in function with
  a CODEUNITS16 parameter.
  
  Additional Keywords: SQLLOB SQLBLOB SQLCLOB
  

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:

  UK44391 UK44392

Modules/Macros

• DSNOGETD
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK44391

     UP09/03/13 P F903

• R910 PSY UK44392

     UP09/03/13 P F903

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.