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

DB2 Version 9.1 Fix Pack 4a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 4 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

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 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