DB2 native REST services

Native REST in one picture

DDF already speaks DRDA for JDBC/ODBC. Native REST adds an HTTP listener on a secure port (usually AT-TLS). A service is:

• One static SQL statement (or CALL) stored in a package
• A row in SYSIBM.DSNSERVICE
• A URL of the form /services/collection/serviceName

That is different from writing a JAX-RS application. There is no WAR file. The SQL is the API. Parameter markers become JSON keys. Result sets become JSON arrays. CPU for REST execution can be zIIP-eligible like other DDF work, which is why shops like it.

Creating a service: BIND SERVICE and createService

BIND SERVICE

BIND SERVICE(collection) is a DSN subcommand. NAME is the service name (quote mixed-case names). SQLDDNAME points at a sequential or PDS member that holds the SQL. SQLENCODING names the CCSID of that text (1047 is a common EBCDIC choice). Ordinary package bind options (OWNER, QUALIFIER, ISOLATION, EXPLAIN) apply.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//CR8SRVC EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB  DD DISP=SHR,DSN=DSN.DB2T.SDSNEXIT
//         DD DISP=SHR,DSN=DSN.DB2T.SDSNLOAD
//DSNSTMT  DD DISP=SHR,DSN=SYSADM.SERVICE.SQL(SELECT1)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2T)
  BIND SERVICE(SYSIBMSERVICE) -
       NAME("deptByLoc") -
       SQLDDNAME(DSNSTMT) -
       SQLENCODING(1047) -
       DESCRIPTION('Department names for a location') -
       QUALIFIER(HR) OWNER(DB2GRP1)
  END
/*

Example SQL in DSNSTMT (one statement):

1
2
3
SELECT DEPTNO, DEPTNAME
  FROM DEPARTMENT
 WHERE LOCATION = ?

CREATE SERVICE API (DB2ServiceManager)

POST to /services/DB2ServiceManager with JSON. Required keys: requestType (createService), sqlStmt, serviceName. Optional: collectionID, description, version, and bind options (owner, qualifier). JSON keys requestType, sqlStmt, collection, serviceName, description, and version are case-sensitive.

1
2
3
4
5
6
7
8
9
{
  "requestType": "createService",
  "sqlStmt": "SELECT DEPTNO, DEPTNAME FROM DEPARTMENT WHERE LOCATION = ?",
  "collectionID": "SYSIBMSERVICE",
  "serviceName": "deptByLoc",
  "description": "Select department name based on location.",
  "owner": "DB2GRP1",
  "qualifier": "HR"
}

Allowed SQL: a single CALL, DELETE, INSERT, SELECT, TRUNCATE, UPDATE, or WITH statement. No multi-statement scripts.

Collections, versions, discovery

A service collection is the package collection. SYSIBMSERVICE is the IBM-supplied default many shops use; you can use an application collection and GRANT EXECUTE on that collection’s packages.

Service versions need versioning enabled (IBM APAR-level feature). If you omit VERSION, REST uses V1. The version id is VARCHAR(64) and is also the package version. BIND SERVICE VERSION does not use quotes the same way NAME does—follow the Command Reference. Invoke a version by appending it to the URL. Dropping or stopping one version can leave others active.

Service discovery: POST /services/DB2ServiceDiscover. The response lists serviceName, collection, version, and serviceURL so a client does not hard-code paths. Operators still query SYSIBM.DSNSERVICE for catalog truth.

Service invocation: HTTP and JSON

Service invocation is typically HTTP POST (some metadata uses GET). Content-Type is application/json. Parameter markers in the SQL map to named fields in the JSON request. Positional markers become parameters in the request schema Db2 publishes for that service.

1
2
3
4
5
POST https://db2t.example.com:443/services/SYSIBMSERVICE/deptByLoc
Authorization: Basic ...
Content-Type: application/json
 
{"LOCATION": "LONDON"}

The JSON response for SELECT includes a ResultSet array of row objects. For INSERT/UPDATE/DELETE you get row counts and SQLCODE information. Client headers such as Db2-Client-ApplName and Db2-Client-WrkStnName populate client special registers for accounting.

REST authentication and authorization

IBM documents two authentication methods:

• HTTP basic authentication — user ID and password or a RACF PassTicket in the Authorization header (clear or Base64). SAF authenticates
• Client certificate authentication — when AT-TLS presents a certificate Db2 maps to a user

If both are present, Db2 authenticates the certificate, establishes a trusted connection if a matching trusted context exists, then switch-user with the basic credentials. Authorization after connect is normal SQL auth: the ID needs EXECUTE on the service package (and table privileges the package needs if you bound with DYNAMICRULES that check the runner). Treat REST like any other bound program: least-privilege GRANTs, not SYSADM in the HTTP header.

REST service management commands

1
2
3
-DISPLAY RESTSVC(SYSIBMSERVICE.deptByLoc)
-STOP RESTSVC(SYSIBMSERVICE.deptByLoc)
-START RESTSVC(SYSIBMSERVICE.deptByLoc)

FREE SERVICE (DSN) or the manager API dropService removes the service, frees the package, and deletes the DSNSERVICE row. STOP is for maintenance windows; FREE is for decommission. Inactive package copies can be freed separately per IBM’s “freeing inactive packages” REST topic.

Db2 REST + z/OS Connect and OpenAPI

z/OS Connect is an API gateway on z/OS. It can call native REST (or WOLA, CICS, IMS) and present a single developer portal. OpenAPI integration means generating or hand-writing an OpenAPI (Swagger) document for the JSON request/response so distributed teams can stub clients. Native REST itself does not require Connect. Use Connect when you need rate limits, API keys, or a corporate OpenAPI catalog in front of Db2.

Explain It Like I'm Five

Native REST is a mailbox on the Db2 house. You write one SQL homework sheet, stamp it with BIND SERVICE, and nail the sheet to the mailbox. A phone app drops a JSON note (“LOCATION is London”) through the slot. Db2 reads the note, runs the homework, and slides a JSON answer back. START/STOP is putting a “closed” sign on the mailbox without throwing the sheet away. FREE SERVICE is taking the mailbox down. z/OS Connect is a fancy lobby that translates your mailbox into a tourist map (OpenAPI) for people who never learned JCL.

Exercises

1. Write BIND SERVICE JCL for a SELECT with one parameter marker. Name collection, NAME, and SQLDDNAME.
2. Draft the createService JSON for the same SQL. Mark which keys are case-sensitive.
3. Explain when you would -STOP RESTSVC versus FREE SERVICE.
4. List two authentication methods IBM documents and what GRANT you still need.
5. POST to DB2ServiceDiscover in a sandbox and record the serviceURL fields you get back.