PI98649: NEW FUNCTION UPDATE OF DB2 NATIVE RESTFUL SERVICES SUPPORT

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• 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
  

Local fix

Problem summary

• ****************************************************************
  * 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.
  

Problem conclusion

Temporary fix

Comments

• 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.
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI58425 UI58431

Modules/Macros

• 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
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI58431

     UP18/09/18 P F809

• RC10 PSY UI58425

     UP18/09/17 P F809

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.