Database Connectivity

Database Connectivity
lac52
HID_database_connectivity
The update processing in 
CA Live API Creator
 relies on database services for locking and transaction management. The retrieval processing in 
CA Live API Creator
 optimizes queries using the database engine's services, such as the index selection. The defined SQL joins determine how much 
CA Live API Creator
 can optimize the query.
You can connect to your database when you create your API or you can add a data source connection to your existing API. You can connect to databases that are in the cloud or on-premise. API Server requires access to your database, whether it be stored in the cloud or within your firewall.
You manage your data source connections by adding, updating, and deleting them.
In this article:
Add a Connection to a Database
Your APIs include a list of the registered databases and the SQL database locations. This list might include only the database connections that you established when you created your API.
You can connect your API to a single database or to multiple databases. For example, you can create an empty database and then connect your API to other databases. You can also create a database using third-party tools and then connect to that database when you create your API using API Creator.
Use the following table to guide you to which database to add a connection based on the tasks that the data source supports:
Task
Standard SQL databases*
CSV
SAP NetWeaver
Salesforce
Apache HBase
Apache Cassandra
MongoDB**
Provides optimistic locking
 
Yes
 
 
Yes
 
No
 
Yes
 
 
Yes
 
 
Yes
 
No
Provides pagination
 
Yes
 
 
Yes
 
 
Yes
 
 
Yes
 
 
Yes
 
 
Yes
 
Provides row/column security
 
Yes
 
No
No
 
Yes
 
 
Yes
 
 
Yes
 
No
Can use named filters and sorts
 
Yes
 
 
Yes
 
 
Yes
 
 
Yes
 
 
Yes***
 
Can define logic (create rules)
 
Yes
 
No
No
 
Yes
 
 
Yes
 
 
Yes
 
Use HTTP methods in REST requests
GET, POST, PUT, and DELETE
GET
GET
GET, POST, PUT, and DELETE
GET, POST, PUT, and DELETE
GET, POST, PUT, and DELETE
GET, POST, PUT, and DELETE
*Includes MySQL, Microsoft SQL Server, Oracle, IBM Db2, IBM Db2 for i, IBM Informix, PostgreSQL, Teradata, CA Datacom, and Derby.
**Uses the extensible data source framework. For more information about this framework, see Manage Data Source Providers.
***Cassandra includes known limitations when filtering rows. For more information about these limitations, see Apache Cassandra Data Source.
Recent versions of MySQL might require that you connect to MySQL using Secure Sockets Layer (SSL). The following error message indicates this requirement:
javax.net.ssl.SSLException: Unsupported record version Unknown-0.0
You can avoid this MySQL SSL exception.
For more information about how to avoid this exception while adding a connection to a MySQL data source, see Troubleshooting.
For more information:
Follow these steps:
 
  1. With your API open, in the Create section, click 
    Data Sources
    .
    The Data Sources page appears. A list of existing data source connections displays on the Connection tab.
  2. Above the list of data source connections, click 
    Add
    .
    The Create data source window opens.
  3. Create the data source by completing the following fields, and then clicking 
    Add
    :
    > JDBC data source
    Create data source using
    The category of data source. Select 
    JDBC data source
    .
    Values:
    • JDBC data source.
       A JDBC data source for a JDBC driver that 
      CA Live API Creator
       supports.
      For more information about the JDBC drivers that 
      CA Live API Creator
       supports, see Installation Requirements and Supported Platforms.
      If you require a connection to a data source that 
      CA Live API Creator
       does not support, create a data source provider.
      For more information, see Manage Data Source Providers.
    • Managed data server.
       A data source for a managed database.
    • JNDI named data source.
       A data source through a Java container's JNDI definition.
    • Data source provider. 
      A data source that is based on a data source provider.
    Default:
     JDBC data source
    Data source type
    Specifies the type of data source that you want to use.
    Values:
     MySQL, Oracle, SQL Server, Azure SQL, PostgreSQL, Derby/JavaDB, Db2 for z/OS, Db2 for LUW, Db2 for i, Cassandra, Teradata, Informix, CA IDMS, and CA Datacom
    Default:
     MySQL
    For more information about which data source to specify, consult your database administrator or your system administrator.
    Name
     
    The name for your data source. Enter a name that coordinates with your API definition. You can modify the name.
    CA Live API Creator
     uses this name as the file name for source control management (SCM) when you export the API.
    Unique:
     Yes
    Prefix
     
    The prefix for your data source connection. Data source prefixes help identify the database objects (for example, entities (tables or views) and procedures). Resources, rules, and JavaScript reference multiple data sources using the prefix. If you have more than one active database in your API, the prefix specifies the database that 
    CA Live API Creator
     should use, for instance, when defining rules and resources.
    References to entities–for example, in rules, resource definitions, and role permissions–include the prefix.
    The data source prefix is represented as text so that you can swap test/production databases.
    Maximum length:
     20 characters
    Case Sensitive:
     Yes
    Best Practice:
     Choose your data source prefix name carefully. Consider using a memorable word, for example 
    demo
     or 
    customers
    .
    > Managed data server
    Create data source using
     
    The category of data source. Select 
    Managed data server
    . For more information about how to add a data source for a managed database from an existing API, see Managed Data Server Administration.
    Values:
    • JDBC data source.
       A JDBC data source for a JDBC driver that 
      CA Live API Creator
       supports.
      For more information about the JDBC drivers that 
      CA Live API Creator
       supports, see Installation Requirements and Supported Platforms.
      If you require a connection to a data source that 
      CA Live API Creator
       does not support, create a data source provider.
      For more information, see Manage Data Source Providers.
    • Managed data server.
       A data source for a managed database.
    • JNDI named data source.
       A data source through a Java container's JNDI definition.
    • Data source provider. 
      A data source for a generic data source.
    Default:
     JDBC data source
    Managed data server
    Defines the managed data server that you want to use to create your data source. If you do not have an active managed data server defined, create one.
    Name
    The name for your data source. Enter a name that coordinates with your API definition. You can modify the name.
    CA Live API Creator
     uses this name as the file name for SCM when you export the API.
    Unique:
     Yes
    Prefix
    The prefix for your data source connection. Data source prefixes help identify the database objects (for example, entities and procedures). Resources, rules, and JavaScript reference multiple data sources using the prefix. If you have more than one active database in your API, the prefix specifies the database that 
    CA Live API Creator
     should use, for instance, when defining rules and resources.
    References to entities–for example, in rules, resource definitions, and role permissions–include the prefix.
    The data source prefix is represented as text so that you can swap test/production databases.
    Maximum length:
     20 characters
    Case Sensitive:
     Yes
    Best Practice:
     Choose your data source prefix name carefully. Consider using a memorable word, for example 
    demo
     or 
    customers
    > JNDI named data source
    Create data source using
    The category of data source. Select 
    JNDI named data source
    .
    Values:
    • JDBC data source.
       A JDBC data source for a JDBC driver that 
      CA Live API Creator
       supports.
      For more information about the JDBC drivers that 
      CA Live API Creator
       supports, see Installation Requirements and Supported Platforms.
      If you require a connection to a data source that 
      CA Live API Creator
       does not support, create a data source provider.
      For more information, see Manage Data Source Providers.
    • Managed data server.
       A data source for a managed database.
    • JNDI named data source.
       A data source through a Java container's JNDI definition.
    • Data source provider. 
      A data source for a generic data source.
    Default:
     JDBC data source
    Name
    The name for your data source. Enter a name that coordinates with your API definition. You can modify the name.
    CA Live API Creator
     uses this name as the file name for SCM when you export the API.
    Unique:
     Yes
    JNDI data source name
    Specifies the name of your JNDI data source in your Java container, without the 
    jdbc/
     prefix. For example, 
    MyJNDIDatasource
    .
    Prefix
    The prefix for your data source connection. Data source prefixes help identify the database objects (for example, entities and procedures). Resources, rules, and JavaScript reference multiple data sources using the prefix. If you have more than one active database in your API, the prefix specifies the database that 
    CA Live API Creator
     should use, for instance, when defining rules and resources.
    References to entities–for example, in rules, resource definitions, and role permissions–include the prefix.
    The data source prefix is represented as text so that you can swap test/production databases.
    Maximum length:
     20 characters
    Case Sensitive:
     Yes
    Best Practice:
     Choose your data source prefix name carefully. Consider using a memorable word, for example 
    demo
     or 
    customers
    .
    > Data source provider
    Create data source using
     
    The category of data source. Select 
    Data source provider
    .
    Values:
     
    • JDBC data source.
       A JDBC data source for a JDBC driver that 
      CA Live API Creator
       supports.
      For more information about the JDBC drivers that 
      CA Live API Creator
       supports, see Installation Requirements and Supported Platforms.
      If you require a connection to a data source that 
      CA Live API Creator
       does not support, create a data source provider.
      For more information, see Manage Data Source Providers.
    • Managed data server.
       A data source for a managed database.
    • JNDI named data source.
       A data source through a Java container's JNDI definition.
    • Data source provider. 
      A data source that implements the extensible data source framework. Use when you require a connection to a data source that 
      CA Live API Creator
       does not support.
    Default:
     JDBC data source
    Name
    The name for your data source. Enter a name that coordinates with your API definition. You can modify the name.
    CA Live API Creator
     uses this name as the file name for SCM when you export the API.
    Unique:
     Yes
    Data source provider
     
    Specifies the data source provider for your data source. The list of options that display are the data source providers that the system administrator (sa) user added.
    For more information about data source providers, see Manage Data Source Providers.
    Prefix
     
    The prefix for your data source connection. Data source prefixes help you identify the database objects (for example, entities and procedures). If your API is connected to more than one active database, the prefix specifies the database that 
    CA Live API Creator
     should use, for instance, when defining resources, rules, and JavaScript.
    References to entities–for example, in rules, resources, and role permissions–include the prefix.
    The data source prefix is represented as text so that you can swap test/production databases.
    Maximum length:
     20 characters
    Case Sensitive:
     Yes
    Best Practice:
     Choose the prefix for your data source connection carefully. Consider using a memorable word, for example 
    demo
     or 
    customers
    .
    The data source is added. The 
    Connection
     tab for the data source appears.
  4. Modify the following fields as required, and then save your changes:
    > JDBC data source
    Name
     
    The description of your data source.
    Optional:
     Yes
    Data source type
     
    The type of data source. This is the type that you specified when you created your data source.
    Read-only:
     Yes
    URL
     
    The database-dependent URL that points to your data source. When you create an API and you connect to an empty database, the database name is appended to this URL.
    Examples:
     
    MySQL database URL example:
    jdbc:mysql://<server-name>[:port-number (default 3306)]/[database-name]
    Derby database URL example:
    jdbc:derby:C:\LAC\Jetty\projects\Market\MarketDB
    Db2 for LUW database URL example:
    jdbc:db2://<host>:<port>/<schema_name>
    Schema/Owner name
     
    The name that points to your schema on the database server. The meaning is database-dependent:
    • Derby: The schema for this data source to use.
    • Oracle: The schema for this data source to use, sometimes referred to as the owner.
    • SQL Server: Often 
      dbo
      , the default schema or owner for this data source to use.
    • PostgreSQL: The schema to use.
    • DB2 LUW: The database schema for this data source to use.
    • DB2 z/OS: The database schema for this data source to use.
    • Teradata: The database for this data source to use.
    • MySQL: MySQL does not use this name.
    Case Sensitive:
    • Derby, Db2, Oracle, Db2 for zOS, Db2 for LUW:
       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 (").
      Example: 
      (For Db2 for LUW) LAC_DEMO_EBSDEV
    • PostgreSQL:
       Uppercase is equivalent to lowercase. For example, foo, Foo and FOO are all equivalent and are converted to lowercase (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 (").
    • SQL Server:
       Case-insensitive but case-remembering. Enter the value exactly as it is spelled, using the correct case. You can enclose the value with the correct case and unusual characters using double-quotes (").
    • MySQL:
       The name's behavior depends on the the underlying operating system and configuration settings. You can quote the value when required by MySQL, enclose the value with the correct case, and enclose unusual characters using back-ticks (`).
    User name
     
    Your assigned user name in the database. You use this user name in 
    CA Live API Creator
     to connect to the database.
    Case Sensitive:
     
    • Derby, Db2, Oracle, Db2 for zOS, Db2 for LUW:
       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 (").
      Example:
       DBADMIN
    • PostgreSQL:
       Uppercase is equivalent to lowercase. For example, foo, Foo and FOO are all equivalent and are converted to lowercase (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 (").
    • SQL Server:
       Case-insensitive but case-remembering. Enter the value exactly as it is spelled, using the correct case. You can enclose the value with the correct case and unusual characters using double-quotes (").
    • MySQL:
       The name's behavior depends on the the underlying operating system and configuration settings. You can quote the value when required by MySQL, enclose the value with the correct case, and enclose unusual characters using back-ticks (`).
    Password
    The password for the database. This is the password for the user that accesses this data source.
    Maximum connections
    The maximum number of connections 
    CA Live API Creator
     uses to create a pool of database connections. Each request accessing this data source retrieves a connection from the pool, uses the connection, and then returns the connection to the pool.
    Default:
     20
    If you change this value, then you must apply the changes to connection pooling by restarting API Server.
    > Managed data server
    Name
    The description of your data source.
    Optional:
     Yes
    Data source type
    The type of data source. This is the type that you specified when you created your data source.
    Read-only:
     Yes
    URL
     
    The database-dependent URL that points to your data source. When you create an API and you connect to an empty database, the database name is appended to this URL.
    Examples:
    MySQL database URL example:
    jdbc:mysql://<server-name>[:port-number (default 3306)]/[database-name]
    Derby database URL example:
    jdbc:derby:C:\LAC\Jetty\projects\Market\MarketDB
    Db2 for LUW database URL example:
    jdbc:db2://<host>:<port>/<schema_name>
    Schema/Owner name
    The database name that points to your schema on the database server. The meaning is database-dependent:
    • Derby: The schema for this data source to use.
    • Oracle: The schema for this data source to use, sometimes referred to as the owner.
    • SQL Server: Often 
      dbo
      , the default schema or owner for this data source to use.
    • PostgreSQL: The schema to use.
    • DB2 LUW: The database schema for this data source to use.
    • DB2 z/OS: The database schema for this data source to use.
    • Teradata: The database for this data source to use.
    • MySQL: MySQL does not use this name.
    Case Sensitive:
     
    • Derby, Db2, Oracle, Db2 for zOS, Db2 for LUW:
       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 (").
      Example: 
      (For Db2 for LUW) LAC_DEMO_EBSDEV
    • PostgreSQL:
       Uppercase is equivalent to lowercase. For example, foo, Foo and FOO are all equivalent and are converted to lowercase (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 (").
    • SQL Server:
       Case-insensitive but case-remembering. Enter the value exactly as it is spelled, using the correct case. You can enclose the value with the correct case and unusual characters using double-quotes (").
    • MySQL:
       The name's behavior depends on the the underlying operating system and configuration settings. You can quote the value when required by MySQL, enclose the value with the correct case, and enclose unusual characters using back-ticks (`).
    User name
    Your assigned user name in the database. You use this user name in 
    CA Live API Creator
     to connect to the database.
    Case Sensitive:
    • Derby, Db2, Oracle, Db2 for zOS, Db2 for LUW:
       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 (").
      Example:
       DBADMIN
    • PostgreSQL:
       Uppercase is equivalent to lowercase. For example, foo, Foo and FOO are all equivalent and are converted to lowercase (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 (").
    • SQL Server:
       Case-insensitive but case-remembering. Enter the value exactly as it is spelled, using the correct case. You can enclose the value with the correct case and unusual characters using double-quotes (").
    • MySQL:
       The name's behavior depends on the the underlying operating system and configuration settings. You can quote the value when required by MySQL, enclose the value with the correct case, and enclose unusual characters using back-ticks (`).
    Password
    The password for the database. This is the password for the user that accesses this data source.
    Maximum connections
    The maximum number of connections uses to create a pool of database connections. Each request accessing this data source retrieves a connection from the pool, uses the connection, and then returns the connection to the pool.
    Default:
     20
    If you change this value, then you must apply the changes to connection pooling by restarting API Server.
    > JNDI named data source
    Name
    The description of your data source.
    Optional:
     Yes
    Data source type
    The type of data source. This is the type that you specified when you created your data source.
    Read-only:
     Yes
    URL
    The database-dependent URL that points to your data source. When you create an API and you connect to an empty database, the database name is appended to this URL.
    Examples:
    MySQL database URL example:
    jdbc:mysql://<server-name>[:port-number (default 3306)]/[database-name]
    Derby database URL example:
    jdbc:derby:C:\LAC\Jetty\projects\Market\MarketDB
    Db2 for LUW database URL example:
    jdbc:db2://<host>:<port>/<schema_name>
    User name
     
    Your assigned user name in the database. You use this user name in 
    CA Live API Creator
     to connect to the database.
    Case Sensitive:
    • Derby, Db2, Oracle, Db2 for zOS, Db2 for LUW:
       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 (").
      Example:
       DBADMIN
    • PostgreSQL:
       Uppercase is equivalent to lowercase. For example, foo, Foo and FOO are all equivalent and are converted to lowercase (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 (").
    • SQL Server:
       Case-insensitive but case-remembering. Enter the value exactly as it is spelled, using the correct case. You can enclose the value with the correct case and unusual characters using double-quotes (").
    • MySQL:
       The name's behavior depends on the the underlying operating system and configuration settings. You can quote the value when required by MySQL, enclose the value with the correct case, and enclose unusual characters using back-ticks (`).
    Password
    The password for the database. This is the password for the user that accesses this data source.
    Maximum connections
    The maximum number of connections uses to create a pool of database connections. Each request accessing this data source retrieves a connection from the pool, uses the connection, and then returns the connection to the pool.
    Default:
     20
    If you change this value, then you must apply the changes to connection pooling by restarting API Server.
    > Data source provider
    Name
    Defines the description of your data source.
    Required:
     No
    Data source type
     
    Defines the type of data source. This is the type that you specified when you created your data source.
    Read-only:
     Yes
    Data source provider
     
    Defines the name of your data source provider.
    Read-only:
     Yes
    Hostname
     
    Defines the hostname of your database. For example: 
    localhost
    .
    Required
    : Yes
    Port
     
    Defines the port number of your host. For example, 
    27017
    .
    Database Name
     
    Defines the name for your database.
    Required
    : Yes
    User name
     
    Your assigned user name in the database. You use this user name in 
    CA Live API Creator
     to connect to the database.
    Case Sensitive:
     
    • Derby, Db2, Oracle, Db2 for zOS, Db2 for LUW:
       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 (").
      Example:
       DBADMIN
    • PostgreSQL:
       Uppercase is equivalent to lowercase. For example, foo, Foo and FOO are all equivalent and are converted to lowercase (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 (").
    • SQL Server:
       Case-insensitive but case-remembering. Enter the value exactly as it is spelled, using the correct case. You can enclose the value with the correct case and unusual characters using double-quotes (").
    • MySQL:
       The name's behavior depends on the the underlying operating system and configuration settings. You can quote the value when required by MySQL, enclose the value with the correct case, and enclose unusual characters using back-ticks (`).
    Password
     
    Your password for the database to access this data source.
  5. Activate your data source connection by selecting the 
    Active
     checkbox. Selecting this checkbox also makes this data source the database for your API. If 
    CA Live API Creator
     is unable to connect to the database successfully, 
    CA Live API Creator
     clears this checkbox and marks the database as inactive. By default, this checkbox is cleared.
  6. (JDBC data sources and JNDI named data sources) Select the following options:
    Schema is editable
    Select this checkbox to allow users with the appropriate credentials to edit the schema for this database.
    Default:
     Cleared
    Log errors
     
    Select this checkbox if you want to record database-level errors for this data source in the log.
    Default:
     Cleared
    Selecting this checkbox can impact performance.
  7. Test the data source connection by clicking 
    Test Connection
    .
    If you are adding a connection to a Datacom data source, the JDBC driver for Datacom includes a limitation that prevents you from testing this data source connection.
    API Creator verifies the connection to this database.
The database connection is added.
Next Steps
Complete the following procedures after you have added a connection to a database.
Use the Data Source as the Database for your API
CA Live API Creator
 must successfully connect to the database before you can use the data source as the database for your API.
Define Schema Filters
When you connect to a database, you can have 
CA Live API Creator
 access only a small part of the database instead of reading the schema, including all tables, views, stored procedures, and database functions in the given schema. You can do this by defining schema filters.
For more information, see Define Schema Filters.
Associate Entities to Sequences
If you have added a connection to a database that supports sequences and your entities use sequences, associate the entities to the sequences.
For more information, see Associate Entities to Sequences.
Manage your Data Sources
Use the following topics to manage your data sources.
View your Schema
When you connect to a database, 
CA Live API Creator
 reads your database schema and exposes the schema resources (tables, views, and stored procedures). 
CA Live API Creator
 references the database objects and determines the names (table names, view names, column names, foreign key names, and stored procedures) from the database or catalog. API Creator creates endpoints, or resources, and the JavaScript object model using the database schema.
You can view your tables, views, stored procedures, and database functions on the Schema page in API Creator.
 
CA Live API Creator
 escapes the periods (.) that it finds in tables, stored procedures, and function names by appending a tilde (~).
Best practice:
 Call endpoints using the 
href
 attribute that is in the 
@metadata
 section of a JSON response.
Follow these steps:
 
  1. With your API open, in the Create section, click 
    Schema
    . The 
    Tables
     tab displays by default.
  2. Complete one of the following:
    • To view your tables, click the 
      Tables
       tab.
    • To view your views, click the 
      Views
       tab.
    • To view your stored procedures, database functions, and packages, click the 
      Procedures
       tab.
      For more information about how to view your stored procedures, see Manage Stored Procedures.
Debug Database Issues
You can debug database issues using the error log that displays on the Error log page.
Follow these steps:
  1. In the Create section, click 
    Data Sources
    . The Connection tab displays by default.
  2. Click 
    Error log
    . The error log displays.
Rename Data Source Prefixes
Renaming a data source prefix breaks rules and resource definitions.
For more information about data source prefix names, see Database Connectivity.
Follow these steps:
 
  1. Export your API. For more information about how to export APIs, see Import and Export APIs.
  2. Change the data source prefix in a text editor.
  3. Import your API.
Data Source Best Practices
Define explicit associations between child entities (tables or views) and the parent entity. Define foreign keys by way of validations, including a validation name. You can add virtual relationships across schemas by defining relationships between databases.
 For more information: