Database Connectivity

Database Connectivity
lac32
Your API includes a list of registered databases and SQL database locations. This list might include only the database connections that you added when you created your API. You can add database connections, such as test databases, integration databases, and a production database. 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).
In this article:
 
 
Connect to your Database
You connect to your database when you create your API. 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 endpoints, or resources, and the JavaScript object model.
You can connect to multiple databases in the same API or combine resources, build rules between databases, and access them by defining multiple databases. For example, you create a new empty database and then connect your API to other databases. You can also connect to a new database using your current tools and then connect to that database when you create your API in API Creator.
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.
For more information:
  • About how to connect to your database when creating a new API in API Creator, see Create your API.
  • About the endpoints and the JavaScript object model that 
    CA Live API Creator
     builds from your schema, see Customize your API.
Add a Connection to a Database
  1. In the Create section, click 
    Data Sources
    .
    The Connection tab displays by default.
  2. Complete the following fields and save your changes:
    Data source name
    The name for your data source connection.
     
    Data source prefix
     
    The prefix for your data source connection. Database prefixes help identify the database objects (for example, tables, views, procedures). If you have more than one active database in your API, the prefix specifies which database 
    CA Live API Creator
     should use, for instance, when defining rules and resource definitions. Resources, rules, and JavaScript reference multiple data sources using the database prefixes. References to entities (for example, in rules, resource definitions, and role permissions) include the database prefix. The database prefix is represented as text, so you can swap test/production databases.
     
    Best Practice:
     Specify a database prefix using a memorable word, for example "demo" or "customers".
     Choose your database prefix name carefully. You can rename the database prefix, but renaming it will cause rules and resource definitions to break. For more information about how to rename your database prefix, see Integrate Multiple Databases.
     
    Maximum length:
     20 characters
     
    Case Sensitive:
     Yes
    Data source type
    Specifies what type of data source you want to use. For more information about which data source 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, CSV Files, Cassandra
     
    Data source URL
     
    The database-dependent URL that points to your data source.
     
    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
    • DB2 for LUW database URL example:
      jdbc:db2://<host>:<port>/<schema_name>
     
    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 (").
      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
     
    The user name under which you access your data source. 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 (").
      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 user specified in the 
    User name
     field.
     
    Active
     
    Select this checkbox to use this data source as the current database for your API and to test the connection to the database. If API Creator is unable to successfully connect to the database, this checkbox is cleared and the database is marked inactive.
    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.
    Schema is editable
    Select this checkbox to allow users with the appropriate credentials to edit the schema for this database.
    Default:
     Cleared 
     
    Role name strategy
     
    Options:
     
    •  
      Standard
      . Generates consistent role names that are stable as you add and remove foreign keys from the database.
    •  
      Deprecated.
       Provided for backward compatibility with existing APIs. This option is not recommended for new APIs.
     
    Default:
     Standard
     If this is an existing API, do not change the role name strategy. Changing the role name strategy impacts existing rules and JavaScript calls that use these role names.
  3. Click 
    Test Connection
    .
    API Creator verifies that API Creator can use this database properly. API Creator runs a trivial query to test this connection. If the query is successful, the test is considered successful.
    CA Live API Creator
     must successfully connect to the database before you can use this database as the current database for your API.
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
You can debug database issues using the error log that displays on the Error log page. In the Create section, click 
Data Sources
Error log
.
View your Schema
You can view your schema on the Tables page. In the Create section, click 
Schema
Tables
.
Database Administration
API Creator includes the Demo and Sample test databases.
Configure your Data Source to Access your Database Administration Web Tool
You manage your schema using your existing tools and procedures. You can configure your data source to access your database administration web tool using the fields on the Connection page. In the Create section, click 
Data Sources
Connection
.
For more information about how to use set up and use managed databases, see Database Creation.
Reload Schemas
When you first create an active data source connection (the 
Active
 checkbox is selected on the Connection page), 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.
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.
  1. In the Create section, click 
    Schema
    .
    The Tables page appears by default.
  2. Click 
    Reload Schema
    .
The schema is reloaded.
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.
For more information about the 
@tables
 system REST endpoint, see System REST Endpoints.
  1. In the Create section, click 
    Schema
    .
    The Tables page appears by default.
  2. Click the 
    Synch
     icon.
The browser is reloaded from the cache.
Export Schemas
You can export any schema from the current API data source listed.
  1. In the Create section, click 
    Schema
    .
    The Tables page appears by default.
  2. Click 
    Export Schema
    .
    The Export Schema window opens.
  3. Select the data source to export and then click 
    Export
    .
Your schema is exported.
Import Schemas
 
Prerequisite:
 The schema must be marked as editable (the 
Schema is editable
 checkbox on the Create, Data Sources, Connection tab is selected).
  1. In the Create section, click 
    Schema
    .
    The Tables page appears by default.
  2. Click 
    Import Schema
    .
    The Import Schema window opens.
  3. 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 Create section, click 
    Schema
    .
    The Tables page appears by default.
  2. Click 
    Reload Schema
    .
  3. In the Create section, click 
    API Properties.
    The Details page appears by default.
  4. 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.
  5. In the Analyze section, click 
    Issues
    , and then 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 sequences are displayed on the Create, Data Sources, Sequences tab. The following image image shows sample sequences on this tab:
  Sequences tab.png  
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. You can define virtual foreign keys across multiple databases by defining relationships between databases.
For more information: