Use BIND service to create and FREE service to drop a Db2 for z/OS REST service

When Db2 first delivered its native REST support back in late 2016, it only allowed the REST services to be created or dropped via REST calls. Application developers who are already familiar with REST calls loves this. For those who don’t want to write an application to create/drop services, they can use a web browser with REST client installed to achieve the same purpose. However, this creates some challenges for those who don’t write application very often or for the folks who want to create the same services on multiple Db2 subsystems.

With PI86867(V11) and PI86868(V12), user can create and drop a native REST service via BIND SERVICE and FREE SERVICE respectively.

Pre-requisite

1. Db2 11 for z/OS APAR PI66828 or Db 12 for z/OS APAR PI70652 or later. Please note these are initial APARs only, there are a lot enhancement since these 2 initial APARs.
2. Db2 APAR PI86867(V11) and PI86868(V12)
3. Prepare your Db2 to support native REST service. Generally speaking, there are two major/minimal steps, including

• Customize and run DSNTIJRS to create Db2 RESTful services database and table.
• Authorize user to access Db2 REST service

See my other article or IBM Knowledge Center for more details.

Use BIND SERVICE (DSN) to create a Db2 Native REST service

Below is the syntax for BIND SERVICE:

BIND SERVICE(<collection-id>) NAME(<service-name>)
  SQLDDNAME(<ddname>)
  SQLENCODING( ASCII | EBCDIC | UNICODE | <ccsid>)
  DESCRIPTION (<description-string>)
  <Additional BIND options>

Suppose we want to create a Db2 native REST service for the following SQL statement:

SELECT SUBSTR(STRING,1,60) as STRING from SYSIBM.SYSXMLSTRINGS WHERE STRINGID= ?

Please note SYSIBM.SYSXMLSTRINGS is a catalog table, so you don’t need to create any user table to make this example work.

1. Put above SQL statement into a HFS file or dataset. We use HFS file as example in this blog, you can also use PDS. See Resources section for more details.

Suppose I have put it in a HFS file (sql.txt) in /tmp directory

$ cat /tmp/sql.txt
SELECT SUBSTR(STRING,1,60) as STRING from SYSIBM.SYSXMLSTRINGS WHERE STRINGID= ?

2. Create a job with following content (you may need to customize the job according to your environment)

//BIND     EXEC TSOBATCH,DB2LEV=DB2A,COND=(4,LT)
//SQLDDNAM DD PATH='/tmp/sql.txt',
// PATHOPTS=ORDONLY,
// RECFM=VB,LRECL=32756,BLKSIZE=32760
//SYSTSIN  DD *
BIND SERVICE(SYSIBMSERVICE) NAME("selectSYSXMLStrings") -        SQLDDNAME(SQLDDNAM) DESCRIPTION('test')

Please note:

1. selectSYSXMLStrings is name of REST service we are planning to create
2. SQLDDNAM point to the file/dataset containing the SQL statement
3. Pay attention to the double quote (“) and single quote (‘) in the BIND statement

Use FREE SERVICE to drop a Db2 Native REST service

Below is the syntax for FREE SERVICE

FREE SERVICE(<collection-id>.<service-name>)

To free the service we created above:

FREE SERVICE(SYSIBMSERVICE."selectSYSXMLStrings")

Resources

SQLDDNAME bind option

Originally published at https://www.ibm.com. Generated 8,670 views as of 12/31/2019.