Migrating from 15.0.3 64-bits to 15.7 64-bits

[Former Member]

We are upgrading our systems from Sybase 15.0.3 64.bits to 15.7 64-bits (Adaptive Server Enterprise/15.7/EBF 21984 SMP SP110 /P/RS6000/AIX 6.1/ase157sp11x/3546/64-bit/FBO.)　 We are having trouble making 2 of 65 batch jobs work.　 We use ctlib (Sybase CTISQL Utility/15.7/P-EBF20092 ESD #4/DRV.15.7.0.4/RS6000/AIX 6.1/BUILD1570-015/OPT/Thu)

We have assumed that 15.7 is upward compatible and have not changed our applications other than recompiling and linking with new libraries.

　

The error we are experiencing is that we somehow seem to loose our cursor.　 There are no errors reported in the database logs and hence we assume that we receive an error from ctlib. 　The error is happening often but we have also been able to run the two jobs without errors.　 The jobs are retrieving and updating data in two databases on the same database server.

We have looked for solved errors and bug id 735036 seem to be somewhat similar but it says that this has been corrected in SP100 (we are running SP 110).

　

The structure of the code is like this:

Select rows from a table (in db1) with cursor

While still rows to be treated

　　　　　　　　　　　　　　　 Fetch a row and get the data (10 rows fetched from the cursor at a time)

　　　　　　　　　　　　　　　 Start transaction

　　　　　　　　　　　　　　　　　　　 do some more selects and updates (in db2) based on the information from the fetched row

　　　　　　　　　　　　　　　 end transaction

　　　　　　　　　　　　　　　 delete the row (in db1)

end while

The second time the fetch tries to read from the cursor, it has been closed.

Just to see what happened we replaced the DELETE with an UPDATE

It still failed using the UPDATE command but had fetched quite a few more rows than the DELETE command allowed us to do. (Using UPDATE it failed after 197 rows rather than 10).

This is the select statement setting up the cursor that fails:

SELECT TST_F4..TRANS_LINJE.TRANS_ID,TST_F4..TRANS_LINJE.TRANS_LINJE_NR,TST_F4..TRANS_LINJE.TRANS_STATUS,TST_F4..TRANS_LINJE.DATA_LINJE,TST_F4..TRANS_LINJE.LOCK_FLAG FROM TST_F4..TRANS_LINJE,TST_F4..TRANS T_O1 WHERE ((TST_F4..TRANS_LINJE.TRANS_STATUS=?)) AND ((T_O1.HOVED_AKT=? AND T_O1.TRANS_TYPE=? AND T_O1.TRANS_STATUS=? AND TST_F4..TRANS_LINJE.TRANS_ID=T_O1.TRANS_ID)) ORDER BY TST_F4..TRANS_LINJE.TRANS_ID,TST_F4..TRANS_LINJE.TRANS_LINJE_NR

Select with CURSOR

　

Anyone else experiencing similar problems?

[former_member188958]

How is the cursor declared?  Is it "for update"?  Sensitive or sermi-sensitive?

[Former Member]

The code that is running has "survived" several upgrades and has not been changed for years.  The paramaeters sensitive / semi-sensitive are new in 15.7?  We have assumed that the code will be upward compatible.

This is from the tracefile:

ct_cmd_alloc--cmdptr:203cddcc

Context: 0 Connection: 201b2598 Command: 0

ct_dynamic--type:717  id:s11  idelen:-9  buf:SELECT TST_F4..TRANS_LINJE.TRANS_ID,TST_F4..TRANS_LINJE.TRANS_LINJE_NR,TST_F4..TRANS_LINJE.TRANS_STATUS,TST_F4..TRANS_LINJE.DATA_LINJE,TST_F4..TRANS_LINJE.LOCK_FLAG FROM TST_F4..TRANS_LINJE,TST_F4..TRANS T_O1 WHERE ((TST_F4..TRANS_LINJE.TRANS_STATUS=?)) AND ((T_O1.HOVED_AKT=? AND T_O1.TRANS_TYPE=? AND T_O1.TRANS_STATUS=? AND TST_F4..TRANS_LINJE.TRANS_ID=T_O1.TRANS_ID)) ORDER BY TST_F4..TRANS_LINJE.TRANS_ID,TST_F4..TRANS_LINJE.TRANS_LINJE_NR  buflen:-9

Context: 0 Connection: 0 Command: 203d3478

ct_send--

Context: 0 Connection: 0 Command: 203d3478

And this is the debug information when it fails:

ct_cmd_drop

Context: 0 Connection: 0 Command: 20407868

CT_DBG_ERROR: All handles passed into function are NULL

(CS_FAIL) file ctcursor.c, line 1103