Design: Swap to Mapepire over system API for SQL

[worksofliam]

Proposal

Using our component API, we want to bring in Mapepire as well as accompanying logic so we can spin up SQL Jobs via SSH. Replacing our existing SQL runner - QSYS/QZDFMDB2 - with Mapepire, should increase performance significantly.

We would be using Mapepire in single mode (not running it as a server). This is what the Db2 for i extension does today.

Reasoning

QZDFMDB2 is convenient because it is pre-installed on every system. But, it comes with a magnitude of issues:

1. CCSID that is used is determined solely by user profile CCSID and not by job running CCSID.
   • Plus another mix of issues depending on OS version, quoted names, and issues when the SSHD CCSID does not match the users (more here)
2. We use system to invoke call QZDFMDB2 spawns a new job every time we run an SQL statement.
   • We have considered using cl, but that comes with new issues regarding CCSID.

Highlevel

Goals

• All test cases, both UI and vitest cases, should pass as they do today with no impact.
• QZDFMDB2 should not be used anywhere in the code.
• liblist should not be used anywhere in the code.

Non-goals

• There should be no impact to user experience.
• There should be no change to the existing Code for IBM i API.
  • We will do a second iteration which will include changes to the API to get new SQL jobs. This should be used in the vscode-db2i extension.

To be determined

• For all: if the Mapepire component fails to install, should we fallback to QZDFMDB2?
  • @worksofliam says no. Too many differences between QZDFMDB2 and Mapepire and the logic would be ugly. I guess Code for IBM i won't work if it doesn't install?
  • @sebjulliand says no. we would have to maintain two ways for accessing Db2 and I never heard anyone complaining about the installation and usage of mapepire in single mode
  • @NicolasSchindler says no: What could be the reasons for a failed installation? So far, I haven't heard of any problems at our company or at other companies.
• How much do we need sysNameInAmerican and sysNameInLocal after we make this switch?
• vscode-db2i / Mapepire can use a number of Java versions. Should we always use the highest available? We already do a Java check in the connection startup.

Technical impact areas

This adds a new requirement of Java being on the server. We already check for Java on the connection startup.

Replacements

• system will need to be replaced by a new SQL procedure which returns the job log and spool file as system does as the result set.
  • call ileditor.system(OUT exitCode int, IN clCommand varchar(512), IN newLibraryList varchar(512) default '*SAME', IN newAsp varchar(10) default '*SAME')
  • exitCode will likely just be 1 or 0.
  • If we add an ASP parameter here, we can set out selves up for success in the future.
• liblist calls should be replaced with calls to our new ileditor.system procedure.

Overall component changes

• Removed: cqsh
• Removed CopyToImport
• Added: Mapepire (Java)
• Added: ileditor.system (procedure)

[thomprl]

I guess what isn’t clear to me is will everything that is needed going to be installed automatically? Will anything need special authority? Sounds like if DB2 uses it it should be fine but just curious. Just like the debug server I have trouble getting our admins to install anything extra

[worksofliam]

Much like the database extension, only Java would be required on the server. Like other components and the database extension, this would be a per-user install (in their home directory).

[sebjulliand]

@thomprl we're actually just moving the existing logic from the DB2 extension to the core extension. The remote update and execution of mapepire will remain transparent, as it is now.

[jwoehr]

I approve, even considering the Java installation issue. The audience doesn't usually have to be dragged kicking and screaming. If you use the system Java, mapepire+c4i will only depend on a facility that comes from IBM along with the system, albeit requiring a conscious install. Sounds like a plan.

[richardm90]

This sounds like a great idea.

As already mentioned, installation needs to be smooth.

Is Mapepire still a Technology Preview? It still says so in the docs.

I'm just migrating one of my Node.js apps from using idb-pconnector to Mapepire but I'm not familiar with Mapepire in single mode.
Where is that running? It sounds as though it would be auto-installed in the user home directory on the server. What port?

What would need to be pre-installed on the server? It sounds like just Java.

Any OS version restrictions with using Mapepire?

I agree with others regarding the fallback to QZDFMDB2, not necessary, as long as Mapepire installation is easy and smooth.

In terms of the Java version I would default to the highest but allow this to be configured/overridden within Code for i. Would that be doable? Would that be configured per Code for i client or is this a server configuration?

If it goes ahead then its obviously a big change but happy to help where I can, not sure about coding but I can certainly help with beta testing.

[sebjulliand]

It's actually just a mater of moving the existing logic from the Db2 extension into the Code for i extension.
Mapepire is already installed and run in single mode by the Db2 extension, and nobody thinks about it because so far that process seems to be robust (yep, everybody's been using Mapepire without noticing 😁).

[worksofliam]

In terms of the Java version I would default to the highest but allow this to be configured/overridden within Code for i.

This is exactly correct. Use the highest available, or allow the user to set it.