160k request limit on dbconn

[SvKN0]

Node.js version: v10.9.0 idb-connector version: 1.2.4 IBM i version: V7R3

Hi We just appled PTFs on our system SF99703 - DB2 FOR IBM I SF99225 - IBM OPEN SOURCE SOLUTIONS FOR I and facing a dbconn problem. After ~160k request through dbconn it fails. We try to recreate dbconn but it doesn't solve a problem. Only restart NodeJS server back it to live. In the job log we found next message Message . . . . : Error occurred in SQL Call Level Interface Cause . . . . . : A procedure call encountered an error. The error cod is 14 - Maximum number of handles allocated

To Reproduce db = require('idb-connector'); dbconn = new db.dbconn(); dbconn.conn("*LOCAL"); let sql = 'select 1 x from sysibm.sysdummy1'; for (var i = 1; i <= 160005; i++) { sqlStm = new db.dbstmt(dbconn); sqlStm.execSync(sql, r => { if (i>159990) { dbconn.debug(true); console.log(i, r); } }) sqlStm.close(); }

Console ...
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)
ExecSync().
SQLExecDirect(0): select 1 x from sysibm.sysdummy1 SQLExecDirect() call for (select 1 x from sysibm.sysdummy1) Has Results
SQLDescribeCol(0) index[0] sqlType[4] colScale[0] colPrecise[4] 159996 [ { X: '1' } ]
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)
ExecSync().
SQLExecDirect(-1): select 1 x from sysibm.sysdummy1
159997 null
...

[dmabupt]

Hello @SvKN0 , I can not reproduce the problem on my system (Node.js v10.19.0 & idb-connetor v1.2.4/v1.2.8). Even when I increased the number to 320005, it still worked well.

ExecSync().
SQLExecDirect(0): select 1 x from sysibm.sysdummy1
SQLExecDirect() call for (select 1 x from sysibm.sysdummy1) Has Results
SQLDescribeCol(0)       index[0]        sqlType[4]      colScale[0]     colPrecise[4]
320005 [ { X: '1' } ]
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)
$ node -v
v10.19.0
$ npm -g ls | grep idb-connector
+-- idb-connector@1.2.4

So I do not know if this is some issue fixed in newer Node.js releases, or there are some commands to control some system resource limit on IBM i? Maybe you could update your Node.js and idb-connector to check first.

[jsoques]

I also could not reproduce the problem:

OS:  aix ppc64 7.3
Running Node version: v12.16.2
160005 [ { X: '1' } ]
 npm list --depth=0
`-- idb-connector@1.2.8

[SvKN0]

We had no this problem before PTFs installation SF99703 - DB2 FOR IBM I level 18 SF99225 - IBM OPEN SOURCE SOLUTIONS FOR I level 6 I think it is SF99703 I will try to roll it back to confirm the assumption.

• Unfortunately rollback is not possible

[jsoques]

Must be something else, we have these PTFs installed:

[SvKN0]

Must be something else, we have these PTFs installed: We have SF99703 lvl 18 but you only 15

[dmabupt]

I can reproduce it when I applied the group PTF SF99703 Lv18. And I found a document saying "Maximum number of simultaneously allocated CLI handles in a process : 160 000" -- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafzlimtabs.htm

I guess the limit was added in a DB2 PTF to reduce the system resource usage of a process. But we need some DB2 experts to confirm that.

[SvKN0]

We have a case TS003743080 opened. For this case we collected some statistics and get next respond: I reviewed the traces, logs and dumps. The job log shows the SQ99999 error with RC14 but the handle dump for the same job at approximately the same time shows nothing more than a couple of db handles. CLI traces show a very well behaved application closing all of the handles it opens too. Opened a CPS discussion with CLI CPS developer R. M. BPYK8V.

Today we received a test fix. I will keep you updated

[kadler]

Hmm, I thought the maximum number of handles was 32k previously, but that might have been a while ago.

[SvKN0]

Test fix solved the problem. We have already received and installed test PTF SI73448 that will be available for all soon. Info from IBM about CLI limits: In the not-too-distant past our CLI developers did a considerable amount of work to redesign SQL CLI on IBM i to increase the handle limit from around 10,000 to the current limit of 160,000 handles by moving handles out of the 16MB job memory space. This change allowed for applications to make use of many more open handles at one time which is useful for applications with a statement cache.