50 DB2 Nuggets #47 : Expert Advice: Troubleshooting error: CLI0145E Fetch type out of range. SQLSTATE=S1106

Body

Hello,

      Recently I worked on an interesting case where a CLI application received "CLI0145E  Fetch type out of range. SQLSTATE=S1106" error and I would like to share with you the steps I did, to troubleshoot the issue.

This is a new application which worked in one server and failed in this specific server.

Data Collected:

I asked customer for CLI trace reproducing the issue and db2Support.zip from the DB2 Client.

Collecting CLI trace:

/support/pages/node/357309

Analysis:

In the CLI trace, I noticed:

[08/20/2014 09:09:01.753686] SQLSetStmtAttrW( hStmt=2:1, fOption=SQL_ATTR_CURSOR_TYPE, pvParam=&00000003, uiStrLen=0 )
[08/20/2014 09:09:01.770671]     ---> Time elapsed - +4.101000E-003 seconds

[08/20/2014 09:09:01.773707] SQLSetStmtAttrW( )
[08/20/2014 09:09:01.776726]     <--- SQL_SUCCESS_WITH_INFO   Time elapsed - +2.304000E-002 seconds

[08/20/2014 09:09:01.780486] SQLGetDiagRecW( fHandleType=SQL_HANDLE_STMT, hHandle=2:1, iRecNumber=1, pszSqlState=&0021f214, pfNativeError=&0021f2e0, pszErrorMsg=&0021ee14, cbErrorMsgMax=511, pcbErrorMsg=&0021f520 )
[08/20/2014 09:09:01.808392]     ---> Time elapsed - +3.760000E-003 seconds
[08/20/2014 09:09:01.811583] ( iRowNumber=-2, iColumnNumber=-2 )
[08/20/2014 09:09:01.822424]

[08/20/2014 09:09:01.826012] SQLGetDiagRecW( pszSqlState="01S02", pfNativeError=-99999, pszErrorMsg="[IBM][CLI Driver] CLI0005W  Option value changed. SQLSTATE=01S02", pcbErrorMsg=64 )
[08/20/2014 09:09:01.842715]     <--- SQL_SUCCESS   Time elapsed - +6.222900E-002 seconds
 

The attribute being set SQL_ATTR_CURSOR_TYPE was being set to a value of 3 which maps to SQL_CURSOR_STATIC (this can be seen in the header file sqlext.h located in sqllib/include).   SQL_CURSOR_STATIC is a scrollable cursor.   The CLI0005W - Option value changed message gets thrown when the attribute value is changed to a value other than the one being set.   For SQL_ATTR_CURSOR_TYPE that would mean that we are downgrading the cursor from a scrollable cursor to a non-scrollable cursor.

Below that, the error is returned for SQLExtendedFetch with the FetchType of SQL_FETCH_FIRST followed by SQL_FETCH_NEXT, which can be done by scrollable cursors only.

[08/20/2014 09:09:03.203824] SQLExtendedFetch( hStmt=2:1, fFetchType=SQL_FETCH_NEXT, iRow=1, pcRow=&0134afb8, rgfRowStatus=&0134b578 )
[08/20/2014 09:09:03.222687]     ---> Time elapsed - +2.969000E-003 seconds

[08/20/2014 09:09:03.503722] SQLExtendedFetch( pcRow=1 )
[08/20/2014 09:09:03.511168]     <--- SQL_SUCCESS   Time elapsed - +3.073440E-001 seconds
[08/20/2014 09:09:03.515353] ( rgfRowStatus[1]=SQL_ROW_SUCCESS )
[08/20/2014 09:09:03.521280]

[08/20/2014 09:09:03.525486] SQLExtendedFetch( hStmt=2:1, fFetchType=SQL_FETCH_FIRST, iRow=1, pcRow=&0134afb8, rgfRowStatus=&0134b578 )
[08/20/2014 09:09:03.547639]     ---> Time elapsed - +1.431800E-002 seconds

[08/20/2014 09:09:03.551855] SQLExtendedFetch( )
[08/20/2014 09:09:03.554991]     <--- SQL_ERROR   Time elapsed - +2.950500E-002 seconds

[08/20/2014 09:09:03.559221] SQLGetDiagRecW( fHandleType=SQL_HANDLE_STMT, hHandle=2:1, iRecNumber=1, pszSqlState=&0021f22c, pfNativeError=&0021f2f8, pszErrorMsg=&0021ee2c, cbErrorMsgMax=511, pcbErrorMsg=&0021f538 )
[08/20/2014 09:09:03.589395]     ---> Time elapsed - +4.230000E-003 seconds
[08/20/2014 09:09:03.592702] ( iRowNumber=-2, iColumnNumber=-2 )
[08/20/2014 09:09:03.603116]

[08/20/2014 09:09:03.606779] SQLGetDiagRecW( pszSqlState="S1106", pfNativeError=-99999, pszErrorMsg="[IBM][CLI Driver] CLI0145E  Fetch type out of range. SQLSTATE=S1106", pcbErrorMsg=67 )
[08/20/2014 09:09:03.622024]     <--- SQL_SUCCESS   Time elapsed - +6.280300E-002 seconds

So the error is expected when you do a SQL_FETCH_FIRST, on a non-scrollable cursor.

To investigate why the cursor was downgraded, I checked their CLI CFG and found that they had the following parameter set:

Patch2=6

Also, When a connection is established the trace shows all the CLI keywords that were set at the time and I noticed the same in the CLI trace too:

[08/20/2014 09:08:54.542780] SQLDriverConnectW( szConnStrOut="DSN=XXXX;UID=XXXXX;PWD=******;DBALIAS=XXXX;PATCH2=6;", pcbConnStrOut=165 )
[08/20/2014 09:08:54.554134]     <--- SQL_SUCCESS   Time elapsed - +5.398810E-001 seconds
[08/20/2014 09:08:54.557880] ( DSN="XXXX" )
[08/20/2014 09:08:54.565209]
[08/20/2014 09:08:54.568955] ( UID="XXXXX" )
[08/20/2014 09:08:54.576362]
[08/20/2014 09:08:54.580100] ( PWD="" )
[08/20/2014 09:08:54.587432]
[08/20/2014 09:08:54.591195] ( DBALIAS="XXXX" )
[08/20/2014 09:08:54.598541]
[08/20/2014 09:08:54.615139] ( PATCH2="6" )

Here is the link that has information on all CLI PATCH parameters:

/support/pages/node/78749

6     Forces CLI to return a message indicating that scrollable cursors are not supported. This setting is required by some applications (such as Visual Basic) that make use of LOBs or that do not require or want scrollable cursors to be used, even though they have been explicitly requested by the application.

So the parameter Patch2=6 was downgrading the scrollable cursor to a non-scrollable cursor, causing the SQLExtendedFetch with the FetchType of SQL_FETCH_FIRST to fail with CLI0145E Fetch type out of range error.

They confirmed that the parameter is no longer needed for any other application and removed the parameter, then the application started to work fine.

Hope these steps help you to troubleshoot if you encounter similar error!

Please do post your feedback/comments or questions.

Thanks!

Subbulakshmi Prabhu