CA IDMS Data Source
You can modernize or extend your applications by enabling data and services for IDMS.
lac52
You can modernize or extend your applications by enabling data and services for IDMS. You can then unlock and expose this data using
Layer7 Live API Creator
. This article includes information about how to install the JDBC driver for IDMS and how to connect to an IDMS database. It also includes a list of the IDMS data types that Layer7 Live API Creator
supports. With the JDBC driver installed, the CA IDMS
data source is available as an option in the connection wizard when creating an API.In this article:
3
Verify the Prerequisites
Before you begin installing the JDBC driver for IDMS and connecting to an IDMS database, ensure that you have completed the following prerequisite steps:
- You know your JDBC connection information.
- Your IDMS database has a defined SQL schema and the SQL is set up to use virtual foreign keys. The schema calls and uses the virtual foreign keys that are available in the database.For more information about how to define a SQL schema for access to network data, see the CA IDMS Reference documentation.
- You have installed the version of IDMS thatLayer7 Live API Creatorsupports.For more information about the versions of IDMS thatLayer7 Live API Creatorsupports, see Installation Requirements and Supported Platforms.
Install the JDBC Driver for IDMS
The self-contained, single-user version of
Layer7 Live API Creator
that is based on Jetty includes the JDBC driver for IDMS. The JDBC driver is located in the %JETTY_HOME%/caliveapicreator/lib/ext
directory. If you have installed this version of Layer7 Live API Creator
, no additional steps are required to install the JDBC driver.If you have installed
Layer7 Live API Creator
on another Java container, such as Apache Tomcat, copy the license file and the idmsjdbc.jar
file (the JDBC driver) that is located in the databaseDrivers
directory into the directory that is based on the Java container on which you have installed Layer7 Live API Creator
:Apache Tomcat
Copy the files into the
%{CATALINA_HOME}/lib
directory.Oracle WebLogic
Copy the files into the
<Your WebLogic Domain Directory>/lib
directory.JBoss/WildFly
For more information, see Install on JBoss.
IBM WebSphere
Add the JDBC driver into your Web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation.
For more information about the JDBC drivers that
Layer7 Live API Creator
supports, see Installation Requirements and Supported Platforms.Connect to an IDMS Database
You can connect to your IDMS using the following methods:
- (If you have not created your API yet) When you create your API.For more information about how to create an API, see Creating APIs.
- (If you have an existing API) By adding a connection to an IDMS data source.For more information about how to add a connection to a data source, see Database Connectivity.
The following procedure details how to connect to your IDMS database when creating your API.
Follow these steps:
- In API Creator, on the APIs page, clickCreate New API.The connection wizard opens.
- ClickDatabase First.The second step in the wizard appears.
- SelectCA IDMSas the data source type.The third step in the wizard appears.
- Complete theHost [optional Port]andPasswordfields, including the following fields, and then clickContinue:DictionaryEnter the name of the dictionary.Example:APPLDICTSchemaThe database-dependent name that points to your schema on the IDMS server.Example:MYSCHEMACase Sensitive:Lowercase is equivalent to uppercase. For example, foo, Foo, and FOO are all equivalent and are converted to uppercase (FOO). If the value uses mixed case, enclose the name in double quotes ("), for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").UsernameThe user name under which you access your database. You database administrator (DBA) assigns you a user name.Example:DBADMINCase Sensitive:Lowercase is equivalent to uppercase. For example, foo, Foo, and FOO are all equivalent and are converted to uppercase (FOO). If the value uses mixed case, enclose the name in double quotes ("), for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").
You are connected to the database.
IDMS Supported Data Types
When you are setting up a connection to an external data source,
Layer7 Live API Creator
must map the data types in the data source to data types in Layer7 Live API Creator
. The following table lists the different data type mappings applicable when working with an IDMS database.For more information about the data type definitions, see the CA IDMS Reference documentation.
You can read and write the following IDMS data types in
Layer7 Live API Creator
:Data Type | Modeled as... | Notes |
---|---|---|
BIGINT | long | |
BINARY | binary | |
CHAR | string | |
DATE | SimpleDate | For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset. |
DECIMAL | decimal | signed and unsigned |
DOUBLE | decimal | |
FLOAT | decimal | |
GRAPHIC | string | |
INTEGER | integer | |
LONGINT | bigint | |
NUMERIC | bigdecimal | signed and unsigned |
REAL | float | |
SMALLINT | integer | |
TIME | SimpleTime | For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset. |
TIMESTAMP | SimpleTimestamp | For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset. |
VARCHAR | string | |
VARGRAPHIC | string |
Manage Stored Procedures and Database Functions
Layer7 Live API Creator
exposes your stored procedures or database functions as RESTful resource endpoints.View Stored Procedures and Database Functions
You can view the stored procedures and database functions on the Schema page in API Creator. To view the Schema page, with your API open, in the Create section, click
Schema
. API Creator displays your stored procedures and database functions as a list of names of each database function or stored procedure.For more information about how to view your stored procedures, including how to invoke them, see Manage Stored Procedures.
Identify and Reference Stored Procedures and Database Functions
Stored procedures and database functions with unique names appear as REST endpoints.
The following example shows how you can identify or reference the
myProcedure
stored procedure and the myFunction
database function in Layer7 Live API Creator
:<prefix>:myProcedure<prefix>:myFunction
Test Stored Procedure and Database Functions
Test your stored procedures and database functions in the REST Lab. Issue a POST request while passing the
IN
argument values.For more information:
- About how to test stored procedures and database functions using the REST Lab, see Test your API Using the REST Lab.
- About how to call stored procedures using a POST call, see Manage Stored Procedures.
Set Up to Execute Table Procedures
You can expose tables procedures from IDMS as REST endpoints in
Layer7 Live API Creator
by defining Free SQL resources.Complete the following for each table procedure that you want to expose as a REST endpoint in API Creator.
Compose the SQL
In IDMS, prepare your SQL statement for how you would call your table procedure. If the table procedure requires parameters, include them in the
where
clause.Create the Free SQL Resource in API Creator
Expose the table procedure as a REST endpoint using a Free SQL resource in API Creator. Add the SQL statement to the
Code
field for this resource.For example:
SELECT CAST(0 AS SMALLINT) AS SCOPE, RTRIM(COLUMN_NAME) AS COLUMN_NAME, DATA_TYPE, RTRIM(TYPE_NAME) AS TYPE_NAME, CAST(PRECISION AS INTEGER) AS COLUMN_SIZE, CAST(LENGTH AS INTEGER) AS BUFFER_LENGTH, CAST(SCALE AS SMALLINT) AS DECIMAL_DIGITS, CAST(1 AS SMALLINT) AS PSEUDO_COLUMN FROM @{SCHEMA}.MY_TABLE_PROC WHERE @{WHERE}ORDER BY @{ORDER}
For more information about how to create a Free SQL resource, see Define Free SQL Resource Types.
Execute the Table Procedure
You can execute table procedures by calling the Free SQL resource. If the table procedure requires parameters, you can pass them in using the
where
clause using a named user filter.For more information about how to create a named user filter, see Structured Filters.
IDMS Data Source Limitations
The following are known limitations in IDMS data sources:
- The JDBC driver for IDMS exposes the IDMS network database as tables and columns. For best results, expose primary keys and foreign keys by initializing the network database.For more information about how to initialize network databases, see the CA IDMS Reference documentation.
- When you insert a row into a child table, you must pass the parent ROWIDs into the virtual foreign key values.
- Varying fields might not appear in lists of columns.
- When your API is connected to an IDMS data source, you can use non-persistent attributes (NPAs) only in the following rules:formula, parent copy, validation, commit validation, event, early event, commit event, pre-insert event, and managed parentFor more information about how to use NPAs in rules, see Manage Non-Persistent Attributes.
- You cannot add comments (for example, using the –, /*, or // symbols) to the SQL for Free SQL resources.