Database Connectivity

Database Connectivity
lac31
Each API has a list of one or more registered databases and one or more SQL database locations. This list may include only the database connections that were created when you created your API. You can add database entries, such as test databases, integration databases, and a production database and define cross-database relationships. API Creator update-processing relies on database services for locking and transaction management. Retrieval-processing leverages the database engine's services for query optimization (index selection, join strategy).
You can use multiple databases in the same API or combine resources, build rules between databases, and access them by defining multiple databases.
In this article:
 
 
Connect to your Database
You can connect to multiple databases, so these options are not restrictive. For example, you can click 
New empty Database
 and then connect your API to multiple databases. Your database can be in the cloud or on-premise. API Server requires access to your database, whether it be stored in the cloud or within your firewall. Use this procedure to connect to your on-premise databases.
Use the following scenarios to connect to your database when creating new APIs:
  • Example Northwind database 
    (Provided in some 
    CA Live API Creator
     configurations). This option illustrates how to create an API using a pre-defined database. The Northwind database operates in conjunction with the Tour, which provides a brief introduction to 
    CA Live API Creator
    's functions.
For more information about the Northwind database, see Northwind API Project Sample.
  • Target database
    . Use this option if you want to create an API for an existing database.
  • JNDI data source.
     Select this option if your connection information is not stored in API Server's admin database or if you want greater flexibility in leveraging database management system (DBMS)-specific options, such as SSL access and mirroring.
    Firewalls require accommodations.
  • New empty database. 
    This option creates an API for a new managed database using API Creator and extended in Data Explorer.
    Prerequisite: 
    Your managed data server must be defined and active.For more information about creating a new database using API Creator, see Managed Data Background.
  • No database
    . This option creates an API without an initial database connection. API without databases leverage base API Server for message listening and response generation. For an example, you can download the JSON sample file.
    For more information about this JSON sample file, see Create your API Project.
  • A new database using your current tools.
     In this scenario, you create your database using your current tools then connect to that database when you create your API.
For more information about connecting to a database when creating new APIs, see Create your API Project.
Add a Connection to a Database
  1. In the Integrate section, click 
    Data Sources
    .
    The Connection tab displays by default.
  2. Complete the following fields, and then save your changes:
    Database name
     
    The name for your database connection.
     
    Database prefix
     
    The prefix for your database connection. If you have more than one active database in your API, the prefix specifies which database to use, for instance when defining rules and resource definitions.
    Choose your database prefix name carefully, since renaming the prefix breaks rules and resource definitions.
    For more information specifying database prefixes and multiple databases, including how to connect your API to multiple databases, see Integrate Multiple Databases.
    Best Practice:
     Specify a database prefix using a memorable word like "demo" or "customers".
     
    Maximum length:
     20 characters
     
    Case Sensitive:
     Yes
    Database type
     
    Specifies what type of database you want to use. For more information about which database to use, consult your database administrator or system administrator.
    Values:
     MySQL, Oracle, SQLServer, AzureSQL, NuoDB (beta), PostgreSQL, Pervasive PSQL (beta), Salesforce, Derby, DB2 for z/OS, DB2 for LUW, Apache HBase, SAP Netweaver
    Database URL
     
    The URL that points to your database. This URL is heavily database-dependent. For more information, see the relevant documentation.
     
    Examples:
     
    • MySQL database URL example:
      jdbc:mysql://<server-name>[:port-number (default 3306)]/[database-name]
    • Derby database URL example:
      jdbc:derby:C:\espresso\Jetty\projects\Market\MarketDB
     
    Schema/Owner name
     
    The database-dependent name that points to your schema on the database server. 
    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 (").
    • 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
     
    The user name under which you access your database. Your database administrator assigns you a user 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 (").
    • 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 user specified in the 
    User name
     field.
    Active
     
    Select this checkbox if you want to use this database as the current database for your API and you want to establish an active data source connection. API Creator reads the database metadata for active data source connections and caches it in the API Server admin database. This metadata is shared between nodes.
    When you mark the database connection as active and save your connection settings, API Creator tests the database connection. If API Creator does not reach the database successfully, this checkbox is cleared and the database is marked inactive.
     
    Log errors
     
    Select this checkbox if you want to record database-level errors for this data source in the Error log.
    Selecting this checkbox can impact performance.
    Schema is Editable
     
    Select this checkbox to mark this schema as editable.
    Role name strategy
     
    The role name strategy for this data source.
    Values:
     
    • Standard:
       Generates consistent role names that are stable as foreign keys are added and removed from the database.
    • Deprecated:
       Provided for backward compatibility with existing APIs. This option is not recommended for new APIs.
     
    Default: 
    Standard
    Do not change the role name strategy for existing APIs. Changing the role name strategy impacts existing rules and JavaScript calls that use these role names.
The database connection is added.
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, see Troubleshooting.
Debug Database Issues
Use the error log for debugging database issues. You can view the error log on the Error log tab.
  1. In the Integrate section, click 
    Data Sources
    .
  2. Click the 
    Error log
     tab.
Schema as Data Model
When you create your API and connect to your database, API Creator reads your database schema. API Creator references the database objects and determines the names (table names, column names, and foreign key names) from the database catalog. API Creator uses the database schema to create resources and the JavaScript object model.
Table references are prefixed with the data source name.
For more information about the JavaScript object model, see Customize your API.
View your Schema
In the Integrate section, click 
Schema
. The Tables tab displays by default. The following image shows this tab:
  CA Technologies.png  
Database Administration
API Creator includes the Demo, Sample, and Your Database test databases. This test database is a blank placeholder.
Configure your Data Source to Access your Database Administration Web Tool
You manage your schema using your existing tools and procedures. As the schema changes, you must reload it. You can configure your data source to access your database administration web tool using the fields on the Details tab.
For more information about using managed databases, see Database Creation.
Reload Schemas
When you first create an active data source connection, API Creator reads the database metadata and caches it in the API Server admin database. This metadata is shared between nodes. As the schema evolves and you change the schema, you must reload it. When you reload the schema, API Creator throws away the API Server cache, rereads the database metadata, and caches it in the API Server admin database.
  1.  In the Integrate section, click Schema.
  2. Click the 
    Tables
     tab.
  3. Click 
    Reload Schema
    .
The time it takes API Creator to rescan the database metadata and update the cache depends on the size of the database and the latency to the database server.
Reload the Browser from the Cache
You can refresh the local copy of schema from 
CA Live API Creator
. API Creator reloads the browser from the cache (possibly update by other users) by polling the 
@tables
 system REST endpoint for your API.
  1. In the Integrate section, click 
    Schema
    .
  2. Click the 
    Tables
     tab.
  3. Click the Synch icon.
 For more information about the 
@tables
 system REST endpoint, see System REST Endpoints.
Export Schemas
You can export any schema from the current API data source listed.
  1. In the Integrate section, click 
    Schema
    .
  2. Click the 
    Tables
     tab.
  3. Click 
    Export Schema
    .
  4. Select the data source to export, and then click 
    Export
    .
Your schema is exported.
Import Schemas
Prerequisite:
 The schema must be marked as editable.
  1. In the Integrate section, click 
    Schema
    .
  2. Click the 
    Tables
     tab.
  3. Click 
    Import Schema
    .
  4. Select the data source to import, and then click 
    Import
    .
Your schema is imported.
Verify your API after Schema Changes
The JavaScript object model always matches your current schema (subject to schema caching). Changes to your schema can make rule-based objects (for example, resources) invalid, due to dangling references.
  1. In the Integrate section, click 
    Schema
    .
  2. Click the 
    Tables
     tab.
  3. Reload the schema by clicking 
    Reload Schema
    .
  4. In the Create section, click 
    API Properties
    .
    The Details tab displays by default.
  5. Verify your API by clicking 
    Verify
    .
    The resources that refer to objects that no longer exist (for example, because they were deleted or renamed) are identified and logged as issues.
  6. In the Analyze section, click 
    Issues
    .
  7. Review the issues.
    For more information about generating a list of API issues and reviewing the issues, see Generate a List of API Issues.
Display Sequences
Databases that support sequences display the column and sequence name. The following image image shows the Sequences tab:
  CA Technologies  
Extend your Data Model to Include Non-persistent Attributes
You can extend your data model to include attributes not in the schema. You can use these attributes in resources and rules. Just as with conventional systems, you must make design decisions about whether to persist derived data. If you decide to persist attributes, synchronize data with logic.
 For more information:
Synchronize Data with Logic
Logic processing interacts with values currently stored in the database.
Data Source Best Practices
We recommend explicit foreign key definitions. Define foreign keys by way of validations, including a validation name.
For more information about how to define foreign keys by way of validations, see Validation Rule Type.
You can define virtual foreign keys across multiple databases by defining relationships between databases.
For more information about how to define relationships between databases, see Integrate Multiple Databases