IBM Support

JR26724: SQLGETDATA DOES NOT RETURN ALL DATA WHEN XML COLUMN HAS LARGE AMOUNTS OF DATA

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Error Description:  When retrieving data from an XML column
    using SQLGetData, all data is not returned.
    
    Snippet of cli trace:
    
    [1178305634.341447 - 05/04/2007 15:07:14.341447] SQLGetDiagRec(
    pszSqlState="01004", pfNativeError=-99999,
    pszErrorMsg="[IBM][CLI Driver] CLI0002W  Data truncated.
    SQLSTATE=01004", pcbErrorMsg=58 )
    [1178305634.362199 - 05/04/2007 15:07:14.362199]     <---
    SQL_SUCCESS   Time elapsed - +7.856100E-002 seconds
    
    [1178305634.366525 - 05/04/2007 15:07:14.366525] SQLGetData(
    hStmt=1:1, iCol=2, fCType=SQL_C_BINARY, rgbValue=&00123680,
    cbValueMax=32767, pcbValue=&001235cc )
    [1178305634.398756 - 05/04/2007 15:07:14.398756]     ---> Time
    elapsed - +4.326000E-003 seconds
    
    [1178305634.403284 - 05/04/2007 15:07:14.403284] SQLGetData(
    rgbValue=x'xxxx...xxxx', pcbValue=4523402 )
    [1178305634.430817 - 05/04/2007 15:07:14.430817]     <---
    SQL_SUCCESS_WITH_INFO   Time elapsed - +6.429200E-002 seconds
    
    [1178305634.434941 - 05/04/2007 15:07:14.434941] SQLGetDiagRec(
    fHandleType=SQL_HANDLE_STMT, hHandle=1:1, iRecNumber=1,
    pszSqlState=&0012fe7c, pfNativeError=&00123074,
    pszErrorMsg=&00123080, cbErrorMsgMax=1025, pcbErrorMsg=&00123068
    )
    [1178305634.472262 - 05/04/2007 15:07:14.472262]     ---> Time
    elapsed - +4.124000E-003 seconds
    [1178305634.476446 - 05/04/2007 15:07:14.476446] (
    iRowNumber=-2, iColumnNumber=2 )
    [1178305634.489142 - 05/04/2007 15:07:14.489142]
    
    [1178305634.521672 - 05/04/2007 15:07:14.521672] SQLGetData(
    hStmt=1:1, iCol=2, fCType=SQL_C_BINARY, rgbValue=&00123680,
    cbValueMax=32767, pcbValue=&001235cc )
    [1178305634.552230 - 05/04/2007 15:07:14.552230]     ---> Time
    elapsed - +4.419000E-003 seconds
    
    [1178305634.556430 - 05/04/2007 15:07:14.556430] SQLGetData(
    rgbValue=x'xxxxx...xxxxx', pcbValue=4490635 )
    [1178305634.586189 - 05/04/2007 15:07:14.586189]     <---
    SQL_SUCCESS_WITH_INFO   Time elapsed - +6.451700E-002 seconds
    
    [1178305634.590354 - 05/04/2007 15:07:14.590354] SQLGetDiagRec(
    fHandleType=SQL_HANDLE_STMT, hHandle=1:1, iRecNumber=1,
    pszSqlState=&0012fe7c, pfNativeError=&00123074,
    pszErrorMsg=&00123080, cbErrorMsgMax=1025, pcbErrorMsg=&00123068
    )
    [1178305634.631576 - 05/04/2007 15:07:14.631576]     ---> Time
    elapsed - +4.165000E-003 seconds
    [1178305634.635793 - 05/04/2007 15:07:14.635793] (
    iRowNumber=-2, iColumnNumber=2 )
    [1178305634.648397 - 05/04/2007 15:07:14.648397]
    [1178305634.692300 - 05/04/2007 15:07:14.692300] SQLGetData(
    hStmt=1:1, iCol=2, fCType=SQL_C_BINARY, rgbValue=&00123680,
    cbValueMax=32767, pcbValue=&001235cc )
    [1178305634.721774 - 05/04/2007 15:07:14.721774]     ---> Time
    elapsed - +4.639000E-003 seconds
    
    [1178305634.725970 - 05/04/2007 15:07:14.725970] SQLGetData(
    rgbValue=x'xxxxxxxx..xxxx', pcbValue=32767 )
    [1178305634.751625 - 05/04/2007 15:07:14.751625]     <---
    SQL_SUCCESS   Time elapsed - +5.932500E-002 seconds
    
    [1178305634.755898 - 05/04/2007 15:07:14.755898] SQLGetData(
    hStmt=1:1, iCol=2, fCType=SQL_C_BINARY, rgbValue=&00123680,
    cbValueMax=32767, pcbValue=&001235cc )
    [1178305634.787349 - 05/04/2007 15:07:14.787349]     ---> Time
    elapsed - +4.273000E-003 seconds
    
    [1178305634.791494 - 05/04/2007 15:07:14.791494] SQLGetData( )
    [1178305634.795939 - 05/04/2007 15:07:14.795939]     <---
    SQL_NO_DATA_FOUND   Time elapsed - +4.004100E-002 seconds
    
    From the above trace you see the data is being fetched at chunks
    of 32767, but after retrieving several chunks we see 4490635 is
    left to be returned but the next SQLGetData goes from 4490635 to
    32767.  So data is missing in the last 4457868 bytes.
    

Local fix

  • Use a chunksize that is:
     (1023*32)-2
    

Problem summary

  • Users Affected:
    Users accessing XML columns with large data.
    
    Problem Description:
    Cli application fetching XML data from an XML column which is
    large (Customer scenario is 5.5 MB).  SQLGetDATA returns some
    data but then issues a sql0100 before all 5.5 MB of data
    retrieved.
    
    
    Problem Summary:
    CLI application will return sql0100 prior to retrieving all data
    from a large XML column.
    

Problem conclusion

  • Problem was first fixed in Version 9 Fixpak 4 (s092407)
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR26724

  • Reported component name

    DB2 UDB ESE WIN

  • Reported component ID

    5765F4101

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-05-31

  • Closed date

    2007-09-25

  • Last modified date

    2010-12-11

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

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

    JR28272

Fix information

  • Fixed component name

    DB2 UDB ESE WIN

  • Fixed component ID

    5765F4101

Applicable component levels

  • R910 PSY

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 January 2022