istoredp.js - needs connect pool option (current design too slow)

[abmusse]

Original report by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

First, to avoid over reaction, many applications using current istoredp.js conn open/close design will work (below).

#!nodejs

var conn = new db.dbconn()
conn.disconn();

However, as workload stress increases (calls per min/sec), istoredp.js full open/close design will likely either stall or even fail. Technically, node db2 driver is often using 'server mode', wherein each open/close will attach/detach a QSQSRVR job to 'run' db2 work. While this happens relatively quickly in human terms, our machine can become very stressed doing all the open/close work. Therefore, we need a new design option to enable connection pools.

Optional read:

I do not want to start a debate about asynchronous (callback) vs. 'taming nodejs' with synchronous thinking like await facility. In fact, maybe we can use one of these 'make nodejs look synchronous' tools as another istoredp.js option. For now, let's just admit that asynchronous nodejs is the correct model, and, correct applications should be using callbacks that handle indeterminate order of task events.

So on then ...

Most every language running on IBM i eventually arrives at the need for db2 connection pools. Each has a different way of implementing the pooling technique.

However, nodejs is asynchronous nature, requires some rather tricky connection pool thinking. In fact, most db2a customer applications i have seen are completely wrong because they share a single connection across asynchronous operations like db.exec, db.prepare, etc. This will FAIL, when the work load gets heavy.

SO, we need a db2 connection pooling class that can be shared with both db2a work and also use the same connection pool with this toolkit.

I have a bears example of db2a connection pooling. This is a crude pooling class, but provides a basic pattern to keep applications from running into the ditch when machine is stressed (fails). I do NOT say this is the best design for a formal connection pool class, but it may help people understand this looming problem.

node4 bears example with connect pool

[abmusse]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Unfortunately current db2a 'async' is not working very well in my tests, really slow and buggy.That is, itoolkit seems to be better with REST 'transport'. I am returning to db2sock project to see if i can get a better 'async' base driver.

[abmusse]

Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

Thanks for conveying the variety of scenarios. I agree with your "best option" even though it will cause scripts to change. I also believe many are only in the beginning stages of implementing iToolkit for Node.js so this should have a relatively low impact.

[abmusse]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Delay this issue until after new xjservice is available.

[abmusse]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Hopefully nobody offended by this open discussion … not intent. Of course, please feel free to correct anything I have wrong with the issues.

Back to istoredp.js connection pooling … we have a few choices:

1) do nothing – Ok. Users will learn what works or not with tookit by experience (bad experience).

Pro: Truthfully, a custom stored procedure for every call is the best option.

Con: Unfortunately, custom stored procedures are difficult to impossible when a call has complex structures (RPG ds structures). The tookit can call 'anything' complex, so, at times, avoiding major customer application RPG re-work, toolkit is the only practical option. Not to mention other toolkit abilities like calling PASE/QSH utilities, CMDs, etc., that can be difficult to program in a stored procedure.

2) istorep.js internal pool – Using the current design. Clever design. We could hide a connection pool in the toolkit istoredp.js class (related singleton connection pool). This would b similar to the bears example in the previous post.

Pro: User application would be unaware of the change.

Con: QTEMP would move around to different jobs depending on pool connection used. Frankly, most RPG folks already have difficult time understanding where QTEMP went (a game of where is Waldo). Also, this would do nothing to change faulty designs in many customer node db2a applications (discussion previous).

3) new connection pool class – Completely new 'class' of sharing pooled connections between db2a work and toolkit work.

Pro: Best option to fix all current faulty designs for asynchronous node programs. Assuming connection pool check-in/out worked correctly, QTEMP would be where expected.

Con: Almost every user script will have to change (*).

(*) In many cases PHP has a much easier to understand synchronous programing model. I mean no harm. Simply a fact many people struggle with asynchronous callback programing.

Any input (useful)???

[abmusse]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

One last related db2a topic ...

The db2a designers were very good users creating two different types of APIs. All asynchronous related APIs have counterpart synchronous versions like db.prepare (asynchronous) and db.prepareSync (synchronous).

This entire connection pooling discussion is about the asynchronous db2a APIs like db.prepare, db.execute, db.exec. These asynchronous APIs are the 'new' functions that really need a connection pool to keep applications from possible falling over under stress (fail).

[abmusse]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Related topic xmlservice is relatively slow ... we know. We are working on a new version json based for better/faster nodejs interaction (other languages as well). Hopefully this summer will have a new version.