DB2 REST service should support ‘Using ARRAY data type as INPUT parm in a Native SQLPL’

We have a SQLPL with ARRAY type as INPUT/OUTPUT parm. REST Service allows to ‘create’ as service calling ‘SQLPL with ARRAY type as INPUT parm’ but throws error while calling the same service.

 

Method :POST

Scheme://HOST[“.” PORT][PATH][“?”QUERY]: http://CSMFTS01DB2.CDC.SCHWAB.COM:3260/services/SPGR0S/getArrayVal1

 

Body

{

"IN_ACCT_ID": "7996932","7997006","8000001","8000006"

}

 

Response: 500 Internal Server Error

 

Body

{

"StatusCode": 500,

"StatusDescription": "Execution failed for DB2 REST Service Manager getArrayVal1 during the invoke service process due to UNSUPPORTED SQL TYPE. Error Location:DSNLJSSP:10"

}

 

REST POST Service should return the result set for given multiple acct_id in array.  

 

getArrayVal1 is defined as below –

 

{

"requestType": "createService",

  "sqlStmt": "call REST_GET_ARRAY (:IN_ACCT_ID, :OUT_ITEM_ISSUE_ID, :OUT_SQLCODE, :OUT_SQLSTATE, :OUT_ERRORMSG)",

"collectionID": "SPGR0S",

"serviceName": "getArrayVal1",

"description": "Call SQL stored procedure to get a resultset ",

"owner": "P1DP057T",

"qualifier": "SAMS",

"validate": "BIND"

}

 

DB2 SQLPL ‘REST_GET_ARRAY’ is defined as below –

 

CREATE PROCEDURE REST_GET_ARRAY1(                   

            IN  IN_ACCT_ID            INTARRAY,     

            OUT OUT_SQLCODE           INTEGER,      

            OUT OUT_SQLSTATE          CHAR(05),     

            OUT OUT_ERRORMSG          VARCHAR(1000) 

          )                                         

          VERSION V1                                 

          NOT DETERMINISTIC                         

          MODIFIES SQL DATA                         

          CALLED ON NULL INPUT                      

          DYNAMIC RESULT SETS 1                     

          ALLOW DEBUG MODE                           

          PARAMETER CCSID EBCDIC