IBM Support

IC72964: EXPORT MAY INCORRECTLY REPORT SQL27981W, IF THERE IS A 4-CHARACTER TOKEN IN THE EXPORT'S SELECT STATEMENT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • DB2 export command may incorrectly report a SQL27981W warning
    message.
    
    For example, any of the following will generate the warning
    SQL27981W message:
    db2 "export to abc.del of del values ('ABC ')"
    db2 "export to abc.del of del values ('ABC ' || ' ')"
    db2 "export to abc.del of del select tabname, 'ABC ' from
    syscat.tables"
    db2 "export to test.ixf of ixf select case when name='AAAA' then
     '11' else '22' end from sysibm.systables"
    
    Example output:
    $ db2 "export to abc.del of del select tabname, 'ABC ' from
    syscat.tables"
    
    SQL3104N  The Export utility is beginning to export data to file
    "abc.del".
    
    SQL27981W  The utility could not verify presence of attached or
    detached data
    partitions in the target table or the source table.
    
    SQL3105N  The Export utility has finished exporting "360" rows.
    
    
    Number of rows exported: 360
    
    
    Explanation: DB2 scans and parses the select statement of an
    export command, and incorrectly determines that the first
    4-character token (from token 3 onwards) it finds as the FROM
    keyword, and the next token as the table name.  It then runs
    some internal SQL queries using the table name to determine
    table characteristics.  If it does not determine the correct
    FROM keyword (for example, a 4-character token precedes the real
    FROM keyword), then the internal SQL queries with the wrong
    table name may fail with syntax errors, and DB2 export will then
    return the SQL27891W warning message.
    

Local fix

  • The incorrect SQL27981W warning message can be ignored, or user
    can attempt to modify the export's select statement slightly so
    that it does not contain 4-character tokens preceding a valid
    FROM keyword, and still have the correct semantic meaning.
    
    For example, the following will not generate the warning:
    db2 "export to abc.del of del values ('ABC')"
    db2 "export to abc.del of del values ('ABC' || ' ')"
    db2 "export to abc.del of del values (cast ('ABC ' as char
    (4)))"
    db2 "export to abc.del of del select tabname, cast ('ABC ' as
    char (4)) from syscat.tables"
    db2 "export to test.ixf of ixf select case when DEPTNAME = 'AAA'
    then '11' else '22' end from org"
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Users of export.                                             *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Problem Description above.                               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fix Pack 5.                       *
    ****************************************************************
    

Problem conclusion

  • First Fixed in Version 9.7 Fix Pack 5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC72964

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-12-02

  • Closed date

    2011-12-23

  • Last modified date

    2011-12-23

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

    IC72769

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC72964

Modified date: 23 December 2011