REST API: SQL Procedures

Obtain a list of procedures associated with the data source. The SQL Procedures service implements the CA IDMS Server JDBC API method getProcedures within the class IdmsDatabaseMetaData.
HTTP Method and URI Path
The URI path has the following format:
GET
<base URL>
/sql/procedures/
<datasource>
?
<query parameters>
Where:
  • <base URL>
    Specifies the base URL address for the CA IDMS REST API service
  • <datasource>
    (required) Identifies the CA IDMS system where the API request is to be sent and is defined in the datasources.yml file
  • <query parameters>
    (optional) The following parameters can be used to filter results. See "About Parameters and Parameter Values" for usage information.
    • procedurePattern -- Filter pattern for the procedure name
    • schemaPattern -- Filter pattern for the schema name
About Parameters and Parameter Values:
  • The parameter "catalog" is not used within CA IDMS.
  • The filtering parameters allow the wildcards
    %
    and
    _
    . Within a pattern string,
    %
    means match any substring of 0 or more characters, and
    _
    means match any one character. Only metadata entries matching the search pattern are returned. In the URL, the
    %
    must be encoded as
    %25
    . To see examples of ways to use wildcards in your request, see Examples for Specifying Patterns.
  • Parameter values are case-sensitive.
For three reserved fields in responses, IDMS returns the following information:
  • reserved1
    Number of input parameters
  • reserved2
    Number of output parameters
  • reserved3
    Maximum number of result sets that can be returned
The examples that follow illustrate this behavior.
Example:
Returns all procedures (no filter option used)
https://zoshost.domain.com:10010/api/v1/sql/procedures/SYSDEMO
Response:
{ "procedures": [ { "procedureCatalog": null, "procedureSchema": "DEMOEMPL", "procedureName": "IDMSCONP", "reserved1": 16, "reserved2": 16, "reserved3": 0, "remarks": null, "procedureType": 1, "specificName": "IDMSCONP" }, { "procedureCatalog": null, "procedureSchema": "REL", "procedureName": "GET_MGR", "reserved1": 3, "reserved2": 3, "reserved3": 0, "remarks": null, "procedureType": 1, "specificName": "GET_MGR" }, { "procedureCatalog": null, "procedureSchema": "R162SSQL", "procedureName": "GETSTATE", "reserved1": 2, "reserved2": 2, "reserved3": 0, "remarks": null, "procedureType": 1, "specificName": "GETSTATE" }, { "procedureCatalog": null, "procedureSchema": "SYSCA", "procedureName": "GET_DIAGNOSTICS", "reserved1": 21, "reserved2": 21, "reserved3": 0, "remarks": null, "procedureType": 1, "specificName": "GET_DIAGNOSTICS" }, { "procedureCatalog": null, "procedureSchema": "SYSCA", "procedureName": "GET_STATISTICS", "reserved1": 42, "reserved2": 42, "reserved3": 0, "remarks": null, "procedureType": 1, "specificName": "GET_STATISTICS" } ], "errors": [] }
Example:
Returns procedures, using a filter pattern
https://zoshost.domain.com:10010/api/v1/sql/procedures/SYSDEMO?schemaPattern=SYSCA
Response:
{ "procedures": [ { "procedureCatalog": null, "procedureSchema": "SYSCA", "procedureName": "GET_DIAGNOSTICS", "reserved1": 21, "reserved2": 21, "reserved3": 0, "remarks": null, "procedureType": 1, "specificName": "GET_DIAGNOSTICS" }, { "procedureCatalog": null, "procedureSchema": "SYSCA", "procedureName": "GET_STATISTICS", "reserved1": 42, "reserved2": 42, "reserved3": 0, "remarks": null, "procedureType": 1, "specificName": "GET_STATISTICS" } ], "errors": [] }