PK84605: DSNT418I SQLSTATE 01004 WARNING

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DSNT418i SQLSTATE 01004 warning
  

Local fix

• No Workaround
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS Users of LOBs.            *
  ****************************************************************
  * PROBLEM DESCRIPTION: An SQLSTATE 01004 (with SQLWARN0 and    *
  *                      SQLWARN1), a data truncation warning,   *
  *                      is inconsistently issued when selecting *
  *                      LOB data.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An SQLSTATE 01004 (with SQLWARN0 and SQLWARN1), a data
  truncation warning, is issued inconsistently by DB2 when
  selecting LOB data. This SQLSTATE is associated with SQLCODE443
  but in this case it is not issued.
  
  The reason for the inconsistency is that DB2 is using two
  different data lengths when making comparisons for generating a
  truncation warning.  This comparison does not consider the
  CCSID.  When CCSIDs are different, data conversion takes place,
  which can change the data length.  As a result, a data
  truncation warning can be issued creating an inconsistency.
  
  
  The following examples illustrate how this inconsistency can
  occur.
  
  Example 1. A data truncation warning is NOT issued when it
             should be.
  
    CREATE DATABASE MYDB;
  
    CREATE TABLESPACE MYTS MAXPARTITIONS 2 IN MYDB CCSID UNICODE;
  
    CREATE TABLE MYTB
      (Cint INTEGER,ROW_ID ROWID NOT NULL GENERATED ALWAYS,
       Csmi SMALLINT,
       CSDBCLOB DBCLOB(1M) WITH DEFAULT NULL,
       CSLOB CLOB(1M) WITH DEFAULT NULL
      ) IN MYDB.MYTS;
  
    INSERT INTO MYTB(CINT,CSLOB,CSDBCLOB) VALUES (1,'BEISPIEL',
    'BEISPIEL');
  
    DCL HDBCLOB1   SQL TYPE IS DBCLOB(5);
  
  EXEC SQL SELECT CSLOB  INTO :HDBCLOB1 FROM MYTB WHERE CINT = 1;
  
  
  Example 2.  A data truncation warning is issued when it should
              NOT be.
  
    CREATE DATABASE MYDB;
  
    CREATE TABLESPACE MYTS MAXPARTITIONS 2 IN MYDB CCSID UNICODE;
  
    CREATE TABLE MYTB
     (Cint INTEGER,ROW_ID ROWID NOT NULL GENERATED ALWAYS,
      Csmi SMALLINT,
      CSLOB CLOB(1M) WITH DEFAULT NULL
     ) IN MYDB.MYTS;
  
  INSERT INTO MYTB(CSLOB) VALUES (' d');
  
  NOTE: This select triggers a data conversion.
  
    SELECT * FROM MYTB;
  

Problem conclusion

• The code in DB2 is modified to issue the data truncation warning
  consistently.
  
  Additional Keywords: SQLSTATE01004 SQLLOB
  

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:

  UK47027

Modules/Macros

• DSNOLMAT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK47027

     UP09/06/16 P F906

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.