Feedback

Feedback

Did this article resolve your question/issue?

   

Article

THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT while fetching using SequeLink for DB2 on z/OS

Printable View
« Go Back

Information

 
TitleTHE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT while fetching using SequeLink for DB2 on z/OS
URL Name6058
Article Number000124005
Environment
Question/Problem Description

The above error is returned while retrieving a larger resultset with SequeLink (5.x, 6.x) for z/OS with some (Powerbuilder) applications.

Steps to Reproduce
Clarifying Information
Steps to Reproduce:
  • Create the following table

    create table manyrecords (col1 char(100))
    insert into manyrecords values ('abcdefghijklmnopqrstuvwxy
    abcdefghijklmnopqrstuvwxy
    abcdefghijklmnopqrstuvwxy
    abcdefghijklmnopqrstuvwx*')

    insert into manyrecords select * from manyrecords (execute a couple of times so that there are 512 records)
  • In Microsoft ODBC Test, establish a connection and execute the following sequence of ODBC API calls:

    SQLExecDirect:
    In: Statementhandle = 0x00881BE0, StatementText = "select * from manyrecords", Statementlength = 25
    Return: SQL_SUCCESS=0
    SQLFetch:
    In: StatementHandle = 0x00881BE0
    Return: SQL_SUCCESS=0
  • Without closing the connection open up a second statement handle, execute, fetch and drop the statement handle

SQLAllocStmt:
In: hdbc = 0x00881968, phstmt = VALID
Return: SQL_SUCCESS=0
SQLExecDirect:
In: Statementhandle = 0x008832E8, StatementText = "select * from manyrecords", Statementlength = 25
Return: SQL_SUCCESS=0
Bind Col All:
icol, fCType, cbValueMax, pcbValue, rgbValue
1, SQL_C_CHAR=1, 101, 0, ""
Fetch All:
Fetching all from 1 columns.
512 rows fetched for Fetch All.
SQLFreeStmt:
In: StatementHandle = 0x008832E8, Option = SQL_DROP=1
Return: SQL_SUCCESS=0

  • Continue to fetch from the first resultset. Fetching the first records will work:

SQLFetch:
In:  StatementHandle = 0x00881BE0
Return: SQL_SUCCESS=0

but when fetching more records (after about 310 rows) the following error is returned:

Fetch All:
Fetching all from 1 columns.
SQLFetch returned: SQL_ERROR=-1
szSqlState = "S1000", *pfNativeError = -518, *pcbErrorMsg = 144
szErrorMsg = "[DataDirect][ODBC SequeLink driver][DB2/MVS] DSNT408I SQLCODE = -518, ERROR:  THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT"
316 rows fetched for Fetch All.

Error MessageODBC TRACE

pdshell15 ed8-a9c EXIT SQLFetch with return code -1 (SQL_ERROR)
HSTMT 08FE2AD8

DIAG [S1000] [DataDirect][ODBC SequeLink driver][DB2/MVS] DSNT408I SQLCODE = -518, ERROR: THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT (-518)

pdshell15 ed8-a9c ENTER SQLErrorW
HENV 00000000
HDBC 08FE18F0
HSTMT 08FE2AD8
WCHAR * 0x00EEEE64 (NYI)
SDWORD * 0x00EEEE24
WCHAR * 0x00EEEE70
SWORD 1023
SWORD * 0x00EEEE28


SequeLink ServerlogDSNTIARFun(sqlcode=-518, len=2400, lrecl=240) -> 0
ERRTXT0=' DSNT408I SQLCODE = -518, ERROR: THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT'
ERRTXT1=' DSNT418I SQLSTATE = 07003 SQLSTATE RETURN CODE'
STATETOKEN at record 1
ERRTXT2=' DSNT415I SQLERRP = DSNXERT2 SQL PROCEDURE DETECTING ERROR'
ERRTXT3=' DSNT416I SQLERRD = -280 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION'
ERRTXT4=' DSNT416I SQLERRD = X'FFFFFEE8' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION'
ENDOFMSG at record 5
Diagnostics (Error) added: message:[HY000][DB2/MVS] DSNT408I SQLCODE = -518, ERROR: THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT;code:-518;component:6;function:8;row:0;column:0
SWDB2_Error_Handling: Error : DSNT408I SQLCODE = -518, ERROR: THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT
Defect Number
Enhancement Number
Cause

Default commit/rollback behavior for SequeLink for DB2 on z/OS when DataSourceCursorHold=0 (false) is SQL_CB_DELETE which means that when a commit/rollback occurs that all open resultsets on that connection will be closed.

Default commit/rollback behavior for SequeLink for DB2 on z/OS when DataSourceCursorHold=1 (true) is SQL_CB_PRESERVE which means that when a commit/rollback occurs the open resultsets will be preserved and you can continue to fetch on them.

Note that the error is not thrown when small resultsets are involved.

This is because the SequeLink client asks the SequeLink Server to fetch a bunch of rows (performance optimization) rather than row per row also when the application only requested one row. As long as the application fetches a row which is already present in the cache on the SequeLink client and the SequeLink Server does not need to go back to the DB2 server no error is returned. Only when the end of this cache has been reached and the SequeLink Server asks the DB2 database to return the next set of rows on the (already closed) resultset, the above error will be returned by DB2 which the SequeLink Server will pass on to the SequeLink client and the calling application.

Resolution
  • Set the SequeLink Server datasource attribute DataSourceCursorHold to True
  • or recode the ODBC application so that it only opens one resultset at a time when in autocommit mode
  • or recode the ODBC application so that the resultsets are opened within the same transaction (i.e. switch autocommit off and start manual transactions) when multiple resultsets are required at the same time
Workaround
Notes
Last Modified Date9/14/2015 4:24 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.


Copyright © 2017 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.

Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.

Terms of Use Privacy Policy Trademarks License Agreements Careers Offices