VAST 9.0, DB2, changed behavior AbtResultTable or Abt*Cursor?

Hi group,

Hermann,

I cannot really help with your specific problem. But we see HY010 in 8.6.3 and cannot explain as well. We're on 8.6.3 because 9.0 is not available on Linux yet. We use DB2 10.5 on 64 bits Ubuntu.
I can't remember seeing HY010 that often on 8.6.

We use Glorp and no hand written SQL, we also do not create query specs ourselves. The Function Sequence Error seems to show up in all kinds of places, seems random.

Joachim



Am Montag, 30. Oktober 2017 17:40:01 UTC+1 schrieb Hermann Ottens:

Hi group,

during our migration to VASmalltalk 9.0 we discovered a problem.

Under certain circumstances a query produces an error while the results of another query are still being fetched.

The error is

[SQLSTATE=HY010 - [IBM][CLI Driver] CLI0125E  (... translation of "function sequence error" ...). SQLSTATE=HY010 [Native Error=-99999]]

I managed to distill the error from our code so that i can provide a script that produces this error.

I cross-checked the script to make sure that it's running fine on 8.5.2 and 8.6.2 (and i also tested it with DB2 client versions 9.7, 10.1 and 10.5, all with the same result).

I'm using a clean image with the feature "ST: Database, DB2 CLI" loaded.

This is the script:

| conSpec connection sqlTab collTab sqlView collView |

conSpec := AbtDatabaseConnectionSpec

forDbmClass: #AbtIbmCliDatabaseManager

databaseName: '....'.                         "<- fill in database name"

sqlTab := 'SELECT tabname FROM SYSCAT.TABLES'.                "select could be from any non-empty table"

sqlView := 'SELECT viewname FROM SYSCAT.VIEWS WHERE 1=2'.     "select produces no result, only for speed, where-condition can be deleted"

collTab := OrderedCollection new.

collView := OrderedCollection new.

["================================"

| querySpecTab rtTab querySpecView rtView |

connection := conSpec connect.

connection autoCommit: true.

(querySpecTab := AbtQuerySpec new) statement: sqlTab.

(querySpecView := AbtQuerySpec new) statement: sqlView.

rtTab := connection

resultTableWithHoldFromQuerySpec: querySpecTab                  "get outer result table"

withValues: nil

ifError: connection errorBlock.

rtTab do:                                                                       "iterate through result table (fetches row from underlying db cursor)"

[:eachRowUmf | | rowModif |                                             "first iteration runs fine"

collTab add: eachRowUmf asString.                                       "second iteration produces error when the second fetch is executed"

rtView := connection

resultTableWithHoldFromQuerySpec: querySpecView         "get inner result table"

withValues: nil

ifError: connection errorBlock.

rowModif := rtView next.                                                "fetch one row (if present)"

rtView close.                                                           "close inner result table"

collView add: rowModif asString].

"================================"]

ensure: [connection disconnect].

Array

with: (collTab isEmpty ifTrue: [nil] ifFalse: [collTab first])

with: (collView isEmpty ifTrue: [nil] ifFalse: [collView first]).

Has anyone encountered the same error and knows what to do?

If i delete the "autoCommit: true", then the script runs successfully. (Alas this is not possible in the production code.)

(BTW: this problem doesn't occur when connecting to an Oracle database.)

Cheers,

Hermann

Hi Hermann,

Hello Bob,

Hello Joachim,