PI98649: NEW FUNCTION UPDATE OF DB2 NATIVE RESTFUL SERVICES SUPPORT
A fix is available
Closed as new function.
New Function update of Db2 native RESTful services support. DB2REST Additional keywords and symptoms: ******************************************************** Also addresses SQLDDNAME close/free issue indicated by message DSNT274I (DDNAME COULD NOT BE OPENED) and JES message IEC301I
**************************************************************** * USERS AFFECTED: * * All users of DB2 for z/OS planning to * * use DB2 native RESTful services support. * **************************************************************** * PROBLEM DESCRIPTION: * * Update to the DB2 native RESTful * * services support, including both * * defect fixes and new functionality. * **************************************************************** * RECOMMENDATION: * **************************************************************** This APAR provides a service update to the DB2 native RESTful services support, including both defect fixes and new functionality. In particular, this service update APAR addresses the following issues: 1. Many customers have stated the need to be able to operationally control their defined Db2 REST services. 2. Many customers have requested a formal and integrated solution for REST service versioning support which would allow for the development and deployment of new "versions" of existing user REST services, while still allowing the existing version(s) of the same named REST service to be used. 3. REST Discover Service incorrectly displays DATE/TIME data type inputs as character string data type in Request Schema for statements executed on accelerator server. 4. The camel case JSON key names, ServiceCollectionID, ServiceProvider, ServiceURL, ServiceDescription, and ServiceName, which are returned in a DISCOVER ALL services request are inconsistent with the corresponding serviceCollectionID, serviceProvider, serviceURL, serviceDescription, and serviceName, JSON keys returned by the DISCOVER DETAILS API. The returned JSON keys for DISCOVER ALL and DISCOVER DETAILS should be the same so that common code could be used to parse the replies. 5. The result columns of a set operator(UNION/INTERSECT/EXCEPT) can appear unnamed. 6. There is insufficient information in the response schema following the creation of a native REST service to determine what bind options were used, or if the supplied bind option was unrecognized. 7. REST service execution can return incorrect result set for DECFLOAT type column. Problem can happen when the string representation of subsequent values expands with respect to the first value. 8. A Db2 RESTful service is created for an SQL statement asking for an output Db2 DECIMAL column. When the scale in the DECIMAL column definition is zero and the actual SQL data in the table is zero, Db2 native RESTful services generate an incorrectly formatted JSON numeric value 00. 9. A Db2 RESTful service is created for an SQL statement that has an output Db2 DECIMAL column. When the precision and scale in the DECIMAL column definition are equal, invalid JSON numeric values are generated for minimum and maximum of the column's response schema during discover details. For example, the minimum of a DECIMAL(2,2) is -0.99, but -.99 was generated. The maximum of a DECIMAL(2,2) is 0.99, but .99 was generated. For any other DECIMAL definitions, incorrect JSON numeric values are generated for minimum and maximum of the column's response schema. For example, the minimum of a DECIMAL(8,3) is -99999.999, but -9999.999 was generated. The maximum of a DECIMAL(8,3) is 99999.999, but 9999.999 was generated. 10.The Db2 statistical counters which reflect the number of messages sent and received from client locations are not incremented during Db2 RESTful service request processing. 11.When attempting to process two or more BIND SERVICE subcommands, which reference the same SQLSTMTDD definition, within the same TSO batch job step, the second and subsequent BINDs receive an error that the specified SQLSTMTDD has been freed and is no longer allocated to the job step. 12.Db2 RESTful services support generates client product_id (PRDID) and accounting values which are not documented. 13.Create REST service fails with SQLCODE-104 when the input SQL statement text includes a terminating semi-colon character. Although a semi-colon is used to terminate SQL statements in some environments (eg. SPUFI), the terminating semi-colon itself is NOT part of valid SQL statement text, which results in the SQLCODE-104 failure when it is supplied for a REST service SQL statement. Although the current behavior is expected, several customers have requested a usability enhancement for the create REST service processing which will allow the input SQL statement text to include a terminating semi-colon. 14.There is inconsistency in what bind options are allowed between CREATE SERVICE and BIND SERVICE. Additionally, bind options that are not applicable to native REST services are accepted with no indication or warning. 15.Due to an internal Db2 logic error, the cursor ISOLATION bind option specified when a REST service is created is not set correctly, and all REST services are being created with the default "CS" (cursor stability) isolation level. 16.During BIND SERVICE and CREATE SERVICE, warnings due to bind options not being applicable can result in CREATE or BIND failure, and the service not being created. 17.During CREATE SERVICE, the OPTHINT bind option value is folded to uppercase even though a lower cased hint value was specified. 18.BIND SERVICE incorrectly allows delimited collection ids or service names containing invalid characters. 19.Remote BIND PACKAGE at a DB2 for z/OS server can fail when bind option keyword values contain mixed cased characters.
The following changes have been made: 1. This APAR adds new "-START RESTSVC", "-STOP RESTSVC", and "-DISPLAY RESTSVC" DB2 commands that can be used to operationally control Db2 user defined REST services. The START/STOP commands will only affect REST services that exist at the time when the command is issued. The started or stopped state of REST services is preserved across Db2 restarts and the commands apply to the entire group in a data sharing environment. If a REST service is stopped, then any new invocation attempt or discover of the service details will fail with an HTTP 503 (resource unavailable) return status. In addition to the new commands to START and STOP services, the DISCOVER ALL services output now includes a new serviceStatus key that shows the current status, "started" or "stopped", for each reported service. The general syntax for the new Db2 -START RESTSVC command is: -START RESTSVC(service_specification) SCOPE(GROUP) The general syntax for the new Db2 -STOP RESTSVC command is: -STOP RESTSVC(service_specification) ACTION(REJECT) SCOPE(GROUP) The general syntax for the new Db2 -DISPLAY RESTSVC command is: -DISPLAY RESTSVC(service_specification) STATUS(*|STARTED|STOPREJ) LIMIT(integer|*) SCOPE(GROUP) The new commands require the introduction of new Db2 messages and a Db2 abend reason code. An overview of those messages and the reason code can be found later in this APAR along with an explanation of the parameters for the new commands. Refer to the Db2 for z/OS documentation in the IBM Knowledge Center for complete information about the updated Db2 native REST services support. 2. This APAR provides support for a new Db2 REST service versioning solution. Although a high level description of the new REST service versioning functionality is provided in this APAR closing text, please refer to the Db2 Knowledge Center for the complete documentation for the new Db2 REST service versioning support. The Db2 REST service versioning support is based on the existing Db2 package versioning support, along with REST service specific additions. After REST service versioning is enabled, users will have the ability to create new REST services using a user specified "version ID" string, up to 64 characters in length. As part of the new Db2 REST service versioning support, a new additional Db2 REST URI format has been added to enable the use, discovery, and invocation of versioned REST services. The currently supported REST service URI format is: /services /<collection id> /<service name> If the <collection id> portion is omitted, then the default "SYSIBMSERVICE" collection ID is used. Example: /services/MyServices/simpleSelect1 NOTE: In order to maintain compatibility with existing REST user applications, the existing Db2 REST URI format will continue to be fully supported, even after REST service versioning has been enabled. The new, additional Db2 REST service URI format, which supports an optional REST version identifier, is: /services/<collection id>/<service name> /<version> If the optional /<version> is specified, then the URI refers to the specified version of the service. If the optional version is not included in the URI, then the URI will resolve to the "default service version" of the service. With this change, the valid Db2 REST service URI variations and mappings would be as follows: /services/<service_name> Example: /services/simpleSelect1 Uses SYSIBMSERVICE as the default collection ID. Resolves to the default service version of REST service SYSIBMSERVICE/<service name>. /services/<collection id>/<service_name> Example: /services/MyServices/simpleSelect1 Resolves to the default service version of REST service <collection id>/<service name> /services/<collection id>/<service name> /<version> Example: /services/MyServices/simpleSelect1/v2 Resolves to version <version> of REST service <collection id>/<service name> NOTE: "default service version" refers to the first/initial Db2 service that is created for a given <collection id>.<service name>, when no other REST service with the same <collection id>.<service name> already exists. In addition to the new URI format, Db2 will now also report new service versioning related metadata in the Discover All Services and Discover Details Services output. When REST service versioning is enabled (see below), the Discover All and Discover Details output can include new "version", "isDefault", and "alternateServiceURL" versioning metadata JSON keys. Db2 REST Service Versioning Enablement ====================================== REST service versioning is enabled by running sample install JOB, DSNTIJR2, which is shipped with this APAR. WARNING WARNING WARNING This APAR MUST be installed to the target Db2 subsystem(s) *BEFORE* JOB DSNTIJR2 is run to enable REST service versioning. In a Db2 Data Sharing environment, this APAR MUST be installed on ALL MEMBERS in a data sharing group *BEFORE* JOB DSNTIJR2 is run to enable REST service versioning. Please refer to sample install JOB DSNTIJR2 for additional information, requirements, and details. 3. Db2 REST Discover Service has been changed to correctly display the data type of DATE/TIME data type inputs in the Request Schema for statements executed on an accelerator server. 4. The following JSON keys that are returned in a DISCOVER ALL services API call have been renamed using lower camel case, as shown, in order to be consistent with DISCOVER DETAILS output: ServiceCollectionID -> serviceCollectionID ServiceProvider -> serviceProvider ServiceURL -> serviceURL ServiceDescription -> serviceDescription ServiceName -> serviceName 5. Db2 has been changed to show the expected names of the result columns of a set operator (UNION/INTERSECT/EXCEPT). 6. Db2 has been changed to report all bind options, either explicitly specified or defaulted by Db2, in the response schema under StatusOptions/Applied following the successful creation of a REST service. Explicitly specified, but unrecognized bind options, are now reported in the response schema under StatusOptions/Unrecognized. 7. Db2 native RESTful services have been changed to correctly return result sets containing DECFLOAT columns with expanding values. 8. Db2 native RESTful services have been changed to correctly return a JSON numeric value 0 for any output Db2 decimal type with a scale of zero and the Db2 SQL data value is zero. 9. Db2 native RESTful services have been changed to correctly add a leading 0 before the decimal point if the data value has no digits before the decimal point, and return correct values for minimum and maximum in the column's response schema for any Db2 DECIMAL column definition during discover service details. 10.Db2 native RESTful service support has been changed to properly increment the Db2 statistical counters which reflect the number of sent and received messages from a client location. 11.The Db2 BIND SERVICE subcommand has been changed to no longer free the allocated SQLSTMTDD at the end of its processing. The same SQLSTMTDD value can then be referenced by multiple BIND SERVICE subcommands within the same job step and the SQLSTMTDD will then only be freed at job step end. 12.Db2 has been changed to document the contents of the distributed client PRDID and accounting strings used for Db2 native RESTful request processing. The PRDID is set as follows: - HTP01010: HTTP/REST requesting client - HTS01010: HTTPS/REST requesting client The client accounting string is set as follows: - REST HTTP function code (Char(5)): 'GET ' - service discover 'POST ' - service invoke - Fully qualified RESTful service name (Char242): may be truncated These changes, which are shipped with this APAR, have been made to the DSNDQMDA member of the Db2 externalized source library, SDSNMACS. 13.Db2 native RESTful services have been changed to provide a usability enhancement in REST create service processing which allows a single terminating semi-colon to be present in the input SQL statement text for BIND SERVICE and the create service API. 14.Db2 native RESTful services have been changed to allow the following bind options and values for BIND SERVICE and the CREATE SERVICE API: ACTION(ADD), CURRENTDATA(NO), DESCSTAT(YES), ENCODING(UNICODE), and SQLERROR(NOPACKAGE). The bind options APRETAINDUP, APREUSESOURCE, and PATHDEFAULT are not applicable and are no longer accepted for BIND SERVICE or the CREATE SERVICE API. 15.Db2 native RESTful services have been changed to correctly set the specified cursor ISOLATION level bind option during REST service creation. This change will only take effect during the creation of new REST services after this APAR has been installed. Any existing REST services whose cursor ISOLATION level did not get correctly set must be recreated after this APAR has been installed. 16.Db2 native RESTful services have been changed to correctly allow a REST service to be created following bind option warnings during BIND SERVICE and the CREATE SERVICE API. 17.Db2 native RESTful services CREATE SERVICE API has been changed to not fold the OPTHINT bind option value to uppercase. 18.Db2 native RESTful services BIND SERVICE has been changed to verify that delimited collection ids or service names contain only valid Db2 identifier characters. 19.Db2 for z/OS server BIND PACKAGE processing has been corrected to recognize bind option keyword values containing mixed case characters. DB2 Information APAR II14827 MUST be reviewed for any IMPORTANT additional documentation, and any known limitations or restrictions related to the DB2 native REST services support. PI98649 introduces the following changes to externals: ====================================================== New Db2 abend reason codes: 00D36801 Explanation A Db2 internal error was detected while processing a DISPLAY, START, or STOP RESTSVC command.
Reported component name
DB2 OS/390 & Z/
Reported component ID
YesSpecatt / New Function / Xsystem
Last modified date
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
DSN9SCNE DSNWZDG2 DSN9SCNS DSNIXMSI DSNTBRB2 DSNTIJR2 DSNLJDSC DSNLJSPS DSNTBCM2 DSNLSSRB DSNLJCMD DSNDQMDA DSNTBCM5 DSNLJDSN DSNLJEMG DSNWDFDM DSNLJDSS DSNLJGUS DSNFLDIR DSNLJMUS DSNLSSRW DSNWDFMG DSNTBRSD DSNECP48 DSNTIJRS DSNECP49 DSNTBAP DSNLDTML DSNTBAS HDBBB10J DSNLJHPP DSNXODML DSNFCDIR DSNLXRWA DSNECP54 DSNTBAS2 DSNLJACC DSNLJSSG DSNXOMD DSN9SCN2 DSNDQ9ST DSNLJECK DSNLJSSP DSNLIRTR DSNTBMSG DSNLXRCS DSNTBCM DSNTBFR2 DSNLJXUS DSNLJTIN DSN9SCNA
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
Applicable component levels
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.