SQL Descriptors Not Deallocated

[DavidRusso]

I've found that preparing a statement allocates descriptors in the SQL server job which never get deallocated. This is a serious problem in my application, as resource usage increases continually as requests are made on the server. On systems where a limit is set for the maximum number of active descriptors in a job, requests eventually start failing with 'SQL0904 - Resource limit exceeded.'

Here is a simple program that demonstrates the problem:

const db2i = require("idb-connector");
const readline = require("readline");

const dbconn = new db2i.dbconn();
dbconn.conn("*LOCAL");

ask();

function ask() {

  const repetitions = 200;
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
  });
  rl.question(`Run ${repetitions} (y): `, function(answer) {

    rl.close();
    if (answer.toLowerCase() == "y" || answer.toLowerCase() == "yes") {

      for (let i = 0; i < repetitions; i++) {
        const dbstmt = new db2i.dbstmt(dbconn);
        dbstmt.prepareSync("select * from qsys2.systables");
        dbstmt.close();
      }
      ask();

    }
    else {

      dbconn.disconn();
      dbconn.close();

    }

  });

}

Run the program from the shell and reply with 'y' to make it prepare/close 200 statements. Then check resource limits on the system with this query:

select
  distinct when_00001, highw00001, job_name
from
  table(
    qsys2.environmental_limits()
  )
where
  limit_id = 18203
  and curre00001 = 'YOUR_USER_PROFILE'
order by
  highw00001 desc

Respond to the program with 'y' a few times and run the query after each run to see what happens. Each iteration (200 prepares) will result in 400 descriptors (2 for each prepare) getting allocated in the job and never deallocated.

I wrote an equivalent test program using SQL CLI, and the problem doesn't occur. So, it certainly seems like a bug in idb-connector.

[dmabupt]

Hello @DavidRusso , would you tell me how to get qsys2.environmental_limits()? It is not a system built-in interface. I also tried to manually run your test code. But can not recreate any failure --

-bash-4.4$ node fdlimit.js
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y
Run 2000 (y): y

[DavidRusso]

qsys2.environmental_limits() is an IBM-provided table function that is part of the system. There is also a stored procedure that can be used to return the same data, which is documented here:

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzajq/rzajqhealthenvlimits.htm

This information can also be viewed in IBM i Navigator :

Running my example code will reproduce the problem of descriptors never being deallocated. The error 'SQL0904 - Resource limit exceeded.' will not occur unless the system has a limit specified for the number of descriptors.

[dmabupt]

@DavidRusso Found the root cause --> Server Mode

The issue can be reproduced by below CLI code ->

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli.h>
#include <unistd.h>

SQLHANDLE henv;
SQLHANDLE hdbc;

int main() {
  char* database = "*LOCAL";
  char* sql = "select * from qsys2.systables";
  SQLINTEGER rc = 0;
  SQLHANDLE hstmt = 0;
  int k = 0;
  int param = SQL_TRUE;

  rc = SQLOverrideCCSID400(1208);

  rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  rc = SQLSetEnvAttr(henv, SQL_ATTR_SERVER_MODE, &param, 0);

  rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
  rc = SQLSetConnectAttr((SQLHDBC)hdbc, SQL_ATTR_AUTOCOMMIT, &param, 0);
  rc = SQLConnect((SQLHDBC)hdbc, (SQLCHAR *)database, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);

  for(int i = 0; i < 10; i++) {
    for(int j = 0; j < 200; j++) {
      rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
      rc = SQLPrepare((SQLHSTMT)hstmt, sql, SQL_NTS);
      rc = SQLFreeStmt((SQLHSTMT)hstmt, SQL_DROP);
      k++;
    }
    printf("run %d times, now sleep 5 seconds...\n", k);
    sleep(5);
  }

  rc = SQLDisconnect((SQLHDBC)hdbc);
  rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  return rc;
}

@kadler Do you know this kind of restrictions of Server Mode in the python-db2 addon?

[kadler]

The problem is that you're never freeing the handle created here:

rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

Any handle allocated using SQLAllocHandle should be freed with SQLFreeHandle or it will leak.

[ThePrez]

@kadler, the SQLFreeStmt I think should free the handle also. We should perhaps get the CLI team engaged with the in-house recreate

[kadler]

Oh, I was thinking that SQL_DROP closed the cursor, but that's SQL_CLOSE. I'm used to ODBC, where SQLFreeStmt is basically deprecated. I'd still recommend using SQLFreeHandle instead.

It's possible that by not calling closing the cursor, the SQLFreeStmt is getting an error that we're ignoring.

[DavidRusso]

According to this, SQLFreeStmt() w/SQL_DROP should close the cursor and free all CLI resources:

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/cli/rzadpfnfstmt.htm

I'm able to reproduce the problem with the CLI program below. The problem occurs regardless of whether SQLFreeStmt() or SQLFreeHandle() are used. Neither call returns an error. And, the problem only occurs when run in server mode, as Xu Meng said.

This is causing a big problem for one of my customers. For some reason, this customer starts getting 'SQL0904 - Resource limit exceeded.' when 140,000 descriptors are allocated, even though the limit is set to 0 (unlimited) on his system. On my test system, the limit (or, lack thereof) seems to be honored, and the error doesn't occur, no matter how many descriptors get allocated. Both the customer's and my test system are IBM i 7.2.

Does this seem like a CLI bug? Should I open a ticket with IBM support about this?

#include <iostream>
#include <sqlcli.h>

using namespace std;

int main(int argc, char * argv[]) {

  SQLHENV env;
  SQLRETURN rc = SQLAllocEnv(&env);
  SQLINTEGER value = SQL_TRUE;
  rc = SQLSetEnvAttr(env, SQL_ATTR_SERVER_MODE, &value, 0);

  SQLHDBC dbc;
  rc = SQLAllocConnect(env, &dbc);
  rc = SQLConnect(
    dbc,
    "*LOCAL",
    SQL_NTS,
    0,
    SQL_NTS,
    0,
    SQL_NTS
  );

  char response[2] = {'y', 0};
  while (*response == 'y') {

    const int repetitions = 200;
    cout << "Run " << repetitions << " (y): ";
    cin.getline(response, 2);
    if (*response == 'y') {

      for (int i = 0; i < repetitions; i++) {

        SQLHSTMT stmt;
        rc = SQLAllocStmt(dbc, &stmt);
        rc = SQLPrepare(stmt, "select * from qsys2.systables", SQL_NTS);
        //rc = SQLFreeStmt(stmt, SQL_DROP); // Same result as below.
        rc = SQLFreeHandle(SQL_HANDLE_STMT, stmt);

      }

    }

  }

  rc = SQLDisconnect(dbc);
  rc = SQLFreeConnect(dbc);
  rc = SQLFreeEnv(env);

  return 0;

}

[kadler]

Hmm. Sounds like a bug in CLI to me.

[DavidRusso]

I opened a PMR with IBM support, provided my example C++ code above, and ref'd this issue. I'll update here with any news.

[github-actions[bot]]

:wave: Hi! This issue has been marked stale due to inactivity. If no further activity occurs, it will automatically be closed.