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 
CA 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 
CA 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 that 
    CA Live API Creator
     supports.
    For more information about the versions of IDMS that 
    CA Live API Creator
     supports, see Installation Requirements and Supported Platforms.
Install the JDBC Driver for IDMS
The self-contained, single-user version of 
CA 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 
CA Live API Creator
, no additional steps are required to install the JDBC driver.
If you have installed 
CA 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 
CA 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 
CA 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:
 
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
    .
    The second step in the wizard appears.
  3. Select 
    CA IDMS
     as the data source type.
    The third step in the wizard appears.
  4. Complete the 
    Host [optional Port]
     and 
    Password
     fields, including the following fields, and then click 
    Continue
    :
    Dictionary
     
    Enter the name of the dictionary. 
    Example:
     APPLDICT
    Schema
     
    The database-dependent name that points to your schema on the IDMS server.
    Example:
     MYSCHEMA
    Case 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 (").
    Username
     
    The user name under which you access your database. You database administrator (DBA) assigns you a user name.
    Example:
     DBADMIN
    Case 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, 
CA Live API Creator
 must map the data types in the data source to data types in 
CA 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 
CA 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
 
CA 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 
CA 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:
Set Up to Execute Table Procedures
You can expose tables procedures from IDMS as REST endpoints in 
CA 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 parent
    For 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.