Mapepire Server

Server side component for the Mapepire project, which provides a new, convenient way to access Db2 on IBM i.

This provides server-side support for Code for IBM i, more specifically database support. Intended for programmatic interaction.

Client SDKs for Java, JavaScript, and Python are in the works!

Usage

For installation instructions, please see the Mapepire project documentation

This server-side job processes requests asynchronously. Requests are sent and received through simple use of stdin/stdout streams. The intent is that the client program is able to launch this process through SSH and interact with it through pipes.

The data stream is relatively simple. Requests and responses are newline-delimited and are formatted in JSON.

All requests require these two fields to be specified:

• id (string): Since the server can process requests asynchronously, responses are not guaranteed back in the same order as requests were sent. The id field passed into the request will be included in the response so that the client can match it up to the request. This can be any string, but should be unique for obvious reasons
• type (string): this specifies the type of request

All responses will include these fields:

• id (string): corresponding to the request ID
• success (boolean): whether or not the request was successful

If an error occurs, all responses will include these fields:

• error: a description of the error

Under certain error conditions, responses may contain one or more of these fields:

• sql_rc: the SQL error code
• sql_state: the SQL state

The following request types are currently supported

Type	Description	Additional input fields	Additional output fields
connect	Connect to the database (implicitly disconnects any existing connection)	props: a semicolon-delimited list of connection properties application: the application name (for use in Client Special Registers) technique: database connection technique (cli or tcp)	job: the server job
cl	Run CL command	cmd: the CL command	data: the resulting job log entries
sql	Run SQL	sql: the SQL statement rows: the maximum number of rows to return on the first request terse: return data in terse format	metadata: metadata about the result set data: the data is_done: whether all rows were fetched
prepare_sql	Prepare SQL statement	sql: the SQL statement terse: return data in terse format
execute	Execute prepared SQL statement	cont_id: the request ID of the previously-run sql or prepare_sql parameters: array parameter values corresponding to any parameter markers used. If parameters is an array of arrays, then the sql operations are executed as a batch.	data: the data
prepare_sql_execute	Prepare and execute SQL statement	parameters: array parameter values terse: return data in terse format	data: the data
sqlmore	fetch more rows from a previous sql/prepare_sql/prepare_sql_execute request	cont_id: the request ID of the previously-run sql/prepare_sql/prepare_sql_execute request rows: the maximum number of rows to return	data: the data is_done: whether all rows were fetched
sqlclose	close cursor from a previous sql/prepare_sql/prepare_sql_execute request	cont_id: the request ID of the previously-run sql/prepare_sql/prepare_sql_execute request
getdbjob	Get server job for database tasks		job: the server job
getversion	Get version info		build_date: build date version: version
ping	Liveness check		alive: this program is still responsive db_alive: there is an active connection to the database
setconfig	Set configuration options	tracelevel: see valid trace levels, below tracedest: one of (file, in_mem) jtopentracelevel: see valid trace levels, below jtopentracedest: one of (file, in_mem)	tracedest, tracelevel,jtopentracedest, jtopentracelevel,
gettracedata	Get trace data		tracedata: the trace data (as a singular HTML string) jtopentracedata: the JtOpen trace data (plain text)
exit	Exit

Valid trace levels:

• OFF: off
• ON: all except datastream
• ERRORS: errors only
• DATASTREAM: all including data stream
• INPUT_AND_ERRORS: errors and data stream inputs

Examples

Example request to exit gracefully:

{"id": "bye", "type": "exit"}

Example to connect to the database with an initial library list

{"id": "conn14", "type": "connect", "props":"naming=system;libraries=jesseg,qiws"}

Example SQL query:

{"id": "1l", "type": "sql", "rows":4, "sql":"select * from qiws.qcustcddt"}

Example to fetch more data (4 more rows) from previous query

{"id": "2l", "type": "sqlmore", "cont_id":"1l", "rows":4}

Options for customizing behavior

Operation sql supports the following Java system properties:

• codeserver.jdbc.autoconnect: Enable SQL to be run without first issuing a connect request (uses default values)
• codeserver.verbose: verbose mode

So, for instance:

/QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/bin/java -Dcodeserver.jdbc.autoconnect=true -jar codeforibmiserver.jar

Developing and testing the server

1. Clone repository
2. Maven install
3. Run and Debug inside of Visual Studio Code:
   • Debug CLI: debugging CLI mode of the server. JSON stream passed via standard I/O.
   • Daemon Mode: debugging Daemon Server via websockets. Use one of the available Node.js, Python, etc, libraries in combination of this.