REST API: SQL Services

Use SQL services to access your IDMS data.
The SQL services enable you to access your CA IDMS databases using the SQL API. You can access both network and SQL databases with these services.
For more information, see the Coding Considerations section in the SQL Reference documentation.
Authorization and Authentication
Basic authorization user credentials must be provided in the header (either user ID and password or Base64 encoding), as shown in the following examples:
Authorization: Basic zowe zowe
Base64 encoding of userid:password
These credentials are used to access the API and to sign onto IDMS.
Services
The following services are available:
  • SQL Execution Service -- SQL enables you to execute one or more freeform SQL commands.
  • SQL Metadata Services:
    • SQL Columns -- Obtain a list of columns associated with the data source. The Columns endpoint implements the CA IDMS Server JDBC API method getColumns within the class IdmsDatabaseMetaData.
    • SQL Execution -- The SQL Execution Service allows you to input one or more SQL statements for submission.
    • SQL Exported Keys -- Obtain a list of exported keys that are associated with the data source.  The SQL Exported Keys service implements the CA IDMS Server JDBC API method getExportedKeys within the class IdmsDatabaseMetaData.
    • SQL Imported Keys --  Obtain a list of imported keys associated with the data source. The SQL Imported Keys service implements the CA IDMS Server JDBC API method getImportedKeys within the class IdmsDatabaseMetaData.
    • Primary Keys -- Obtain a list of primary key columns associated with the data source. The SQL Primary Keys service implements the CA IDMS Server JDBC API method getPrimaryKeys within the class IdmsDatabaseMetaData.
    • 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.
    • SQL ProcedureColumns -- Obtain a description of procedure parameters and result columns. The ProcedureColumns endpoint implements the CA IDMS Server JDBC API method with getProcedureColumns in the class IdmsDatabaseMetaData.
    • SQL Schemas -- Obtain a list of schemas associated with the data source. The Schemas endpoint implements a CA IDMS Server JDBC API method getSchemas within the class IdmsDatabaseMetaData.
    • SQL Tables -- Obtain a list of tables associated with the data source. The Tables endpoint implements the CA IDMS Server JDBC API method getTables within the class IdmsDatabaseMetaData.
Filtering Examples
Many of the SQL metadata services allow you to provide filtering parameters to tailor the results of your lookups. Filtering parameters reduce the amount and type of information in the responses that are returned from the service calls. For more flexibility in tailoring your search results, the filtering options accommodate wildcards.
See a range of examples that illustrate how to use patterns and wildcards in your request in Examples for Specifying Patterns.