Need ability to control use of commit

colin-grierson commented 3 weeks ago

In our shop there are many old applications with tables that are not journaled. Using db2-for-i to update or insert records fails on these tables because commit is assumed - but is not possible since commit requires journaling. Can we have some way of setting the commit level db2-for-i will use - including *NONE.

My apology if this exists and I have missed it in the documentation. Regards, Colin

bmarolleau commented 3 weeks ago

Hi @colin-grierson , Indeed , the current version requires commitment control, so let's find an elegant way to fix this.
Talk to you later.

bmarolleau commented 3 weeks ago

Hi @colin-grierson ,

I finally pushed an updated version in a new branch . Please could you try to

Clone this branch ( git clone -b attributes-0.2.5-beta.1 https://github.com/IBM/node-red-contrib-db2-for-i/) on your IBM i in a dedicated directory,
Perform and npm install to the local directory using npm <PATH To the directory>

The package version should be 0.2.5-beta.1 instead of 0.2.4.
Once installed, in Node-RED, you'll see an addionnal field "Connection Attributes" where you can specify connection attributes in json array : [{"id": integerID, "value": integerValue}, {...}]. To determine which id (integer) and value (integer) to set, please refer to the IBM i infocenter and the attribute IDs listed in the idb-connector documentation.

For example, [{"id": 10003, "value": 0 },{"id": 0, "value": 1 }, {"id":10027,"value":0} ] means:

SQL_ATTR_AUTOCOMMIT (10003) to SQL_FALSE(0) , // disable auto commit
SQL_ATTR_COMMIT/ SQL_TXN_ISOLATION (0) to SQL_TXN_NO_COMMIT (1) // disable commitment control
SQL_ATTR_2ND_LEVEL_TEXT (10027) to SQL_FALSE (0) // just an additional example

Please adjust values according to your environment (need to check the IDs in the SQLSetConnectAttr API docs) , provided values above are given as-is ^^ . In your case , [ {"id": 0,"value": 1} ] , i.e. SQL_TXN_ISOLATION set to SQL_TXN_NO_COMMIT(1) will disable commitment control and fix anySQLSTATE=55019 SQLCODE=-7008 MYTABLE in LIB not valid for operation` issue.

Finally, once this interim version validated, please keep me informed, and I'll push it to the official npm repository. Hope this helps,

colin-grierson commented 3 weeks ago

Hi Benoit

Super fast fix! Thank you.

I need to get another team to do the install on our IBMi so it will probably take me longer to get this organised and test your change than it took you to make it :-( I will let you know results as soon as I can get things in place.

Your method is very flexible and covers everything, but will need good documentation in the published version I think. ID: 0 (SQL_ATTR_COMMIT / SQL_TXN_ISOLATION) Value: 1 (SQL_TXN_NO_COMMIT)

Thanks again Cheers, Colin

We develop, integrate and manage mission critical systems

From: Benoit Marolleau @.> Sent: Tuesday, 24 September 2024 4:55 a.m. To: IBM/node-red-contrib-db2-for-i @.> Cc: Colin Grierson @.>; Mention @.> Subject: Re: [IBM/node-red-contrib-db2-for-i] Need ability to control use of commit (Issue #16)

Hi @colin-griersonhttps://github.com/colin-grierson ,

I finally pushed an updated version in a new branchhttps://github.com/IBM/node-red-contrib-db2-for-i/tree/attributes-0.2.5-beta.1 . Please could you try to git clone this branch on your IBM i , in a decicated directory, and perform and npm install to the local directory using npm The package version should be 0.2.5-beta.1 instead of 0.2.4. Once installed, in Node-RED, you'll see an addionnal field "Connection Attributes" where you can specify connection attributes in json array : [{"id": integerID, "value": integerValue}, {...}]. To determine which id (integer) and value (integer) to set, please refer to the IBM i infocenterhttps://www.ibm.com/docs/en/i/7.5?topic=functions-sqlsetconnectattr-set-connection-attribute and the attribute IDs listed in the idb-connector documentationhttps://github.com/IBM/nodejs-idb-connector/blob/master/lib/db2a.js. Finally, once this interim version validated, please keep me informed, and I'll push it to the official npm repository. Hope this helps,

— Reply to this email directly, view it on GitHubhttps://github.com/IBM/node-red-contrib-db2-for-i/issues/16#issuecomment-2368842202, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJ3QPHNIHTAEO4JWBNB2QJDZYBBWNAVCNFSM6AAAAABOVEFHHCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGNRYHA2DEMRQGI. You are receiving this because you were mentioned.Message ID: @.***>

colin-grierson commented 3 weeks ago

Hi Benoit

My man is having difficulty determining which directory to have npm install your package into. Can you tell us where it should go – i.e. what the ‘local directory’ should be?

We are using nodejs version 18 We are using a chroot setup: /QOpenSys/USRDEVENV/NODECPG/QOpenSys/… (NODECPG being my development environment)

Thanks again for your help Regards, Colin Grierson

We develop, integrate and manage mission critical systems

From: Benoit Marolleau @.> Sent: Tuesday, 24 September 2024 4:55 a.m. To: IBM/node-red-contrib-db2-for-i @.> Cc: Colin Grierson @.>; Mention @.> Subject: Re: [IBM/node-red-contrib-db2-for-i] Need ability to control use of commit (Issue #16)

Hi @colin-griersonhttps://github.com/colin-grierson ,

I finally pushed an updated version in a new branchhttps://github.com/IBM/node-red-contrib-db2-for-i/tree/attributes-0.2.5-beta.1 . Please could you try to git clone this branch on your IBM i , in a decicated directory, and perform and npm install to the local directory using npm The package version should be 0.2.5-beta.1 instead of 0.2.4. Once installed, in Node-RED, you'll see an addionnal field "Connection Attributes" where you can specify connection attributes in json array : [{"id": integerID, "value": integerValue}, {...}]. To determine which id (integer) and value (integer) to set, please refer to the IBM i infocenterhttps://www.ibm.com/docs/en/i/7.5?topic=functions-sqlsetconnectattr-set-connection-attribute and the attribute IDs listed in the idb-connector documentationhttps://github.com/IBM/nodejs-idb-connector/blob/master/lib/db2a.js. Finally, once this interim version validated, please keep me informed, and I'll push it to the official npm repository. Hope this helps,

— Reply to this email directly, view it on GitHubhttps://github.com/IBM/node-red-contrib-db2-for-i/issues/16#issuecomment-2368842202, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJ3QPHNIHTAEO4JWBNB2QJDZYBBWNAVCNFSM6AAAAABOVEFHHCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGNRYHA2DEMRQGI. You are receiving this because you were mentioned.Message ID: @.***>

bmarolleau commented 3 weeks ago

Hi @colin-grierson ,

Indeed , If you work in a chroot jail (which is a good practice) , you'll need to point to your git clone directory inside the chroot , and use this directory for npm install. But no worries, I've just published the code as "beta" version on npmjs.org so here is the new procedure :

From your PASE environment, change directory to the .node-red directory. In general: cd ~/.node-red
Run the following command to check which version is currently installed: npm list | grep db2-for-i +-- node-red-contrib-db2-for-i@0.2.4
Install the beta version: npm install node-red-contrib-db2-for-i@beta
Check the installed version npm list | grep db2-for-i +-- node-red-contrib-db2-for-i@0.2.5-beta.1
Start node-red in not already done , configure a db2 node with the additional 'Connection attributes' field, with the appropriate attribute and value (integer only for now, please refer to the SQLSetConnectAttr API documentation and links above)

Hope this helps,

IBM / node-red-contrib-db2-for-i

Need ability to control use of commit #16