search

IBM / nodejs-idb-connector

A JavaScript (Node.js) library for communicating with Db2 for IBM i, with support for queries, procedures, and much more. Uses traditional callback-style syntax
MIT License
38 stars 23 forks source link

temporary memory of job increases massive when using CLOB/JSON field in SQL-request #156

Closed nedi-dev closed 1 year ago

nedi-dev commented 1 year ago

Describe the bug While using "DB2 for i" nodes in Node-RED for a REST-API-application we discovered a massive usage of temporary memory of QP0ZSPWT-job. It increases the value by 1 MB for 10 requests. Analysis showed that the behaviour occurs in the idb-connector as well as in the odbc package. Finally the usage of a clob-field in a sql view with DB2-JSON-aggregate functions is the root of the problem. All other connection-properties can be neglected.

To Reproduce Steps to reproduce the behavior:

  1. Create a sql-view with JSON-functions (s. attachement "20220830_DEV_TESTJSON.sql")
  2. Create node.js-test app with idb-connector or odbc (s. attachement "app.js")
  3. Start node.js app and observe the consumption of temporary memory in Job QP0ZSPWT (wrkactjob)
  4. Workaround: Using a sql-CAST from CLOB to VARCHAR prevents the memory increment (s. attachement "20220830_DEV_TESTJSON_Workaround.sql") As the JSON-data can be more than 32kb this workaround is not intended for the final solution.

Expected behavior The implementation of CLOB-fields in the driver should be checked for variables whose memory is not released.

Screenshots WRKACTJOB:

Screenshot 2022-08-30 200126_blurred

Fielddefinition JSON > CLOB Screenshot 2022-08-30 200235_blurred

SQL and JS-files as attachement: CLOB_examples.zip

kadler commented 1 year ago

Since this same issue occurs for you in both idb-connector and node-odbc, I suspect it has nothing to do with the database connector used (and you'd get the same result in pure ODBC or JDBC or whatever).

My guess is this is due to the job keeping the lob locator open or the cursors are only pseudo-closed, keeping resources around. You can try setting OPEN_CURSOR_CLOSE_COUNT or LOB_LOCATOR_THRESHOLD in QAQQINI to have these resources free up sooner: https://www.ibm.com/docs/en/i/7.3?topic=qaqqini-query-options

github-actions[bot] commented 1 year ago

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

nedi-dev commented 1 year ago

From IBM Service we received a PTF for IBM i 7.4 that solved this issue: https://www.ibm.com/support/pages/ptf/SI79954 SI79954 - OSP-DB-OTHER SQL JSON BUILT-IN FUNCTIONS DO NOT RELEASE USED