Database Connectivity

Database Connectivity
lac40
You can connect to your database when you create your API or you can add a data source connection to your existing API. 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 your API using API Creator, see Create your API.
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 an 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 using API Creator.
API Creator update processing relies on database services for locking and transaction management. API Creator retrieval processing optimizes queries by leveraging the database engine's services, such as the index selection. The defined SQL joins determine how much API Creator can optimize the query.
In this article:
 
 
Add a Connection to a Database
Each API includes a list of one or more registered databases and one or more SQL database locations. This list might include only the database connections you established when you created your API. You can add database entries, such as test databases, integration databases, and a production database.
You can add, update, and delete data source connections in your API.
  1. 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
    .
  3. 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). Resources, rules, and JavaScript reference multiple data sources using the database prefixes. 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 resources. 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:
     Choose your database prefix name carefully. Consider using a memorable word, for example "demo" or "customers".
    Changing the data source prefix causes rules and resources 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, Teradata
    Data source 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 the URL you enter here.
     
    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-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 you specified in the 
    User name
     field.
     
    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.
     
    Active
     
    Select this checkbox to activate this data source connection, to use this data source as the current database for your API, and to test the connection to the database. If 
    CA Live API Creator
     is unable to successfully connect to the database, 
    CA Live API Creator
     clears this checkbox and marks the database as 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
  4. Click 
    Test Connection
    .
    API Creator verifies the connection to this database.
     
    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.
  1. In the Create section, click 
    Data Sources
    .
    The Connection tab displays by default.
  2. Click 
    Error log
    .
View your Schema
When you connect to a database, API Creator reads your database schema. API Creator references the database objects and determines the names (table names, view names, column names, foreign key names, and procedures) from the database or catalog. API Creator creates endpoints, or resources, and the JavaScript object model using the database schema.
The schema page shows your tables, views, and stored procedures.
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.
     
    CA Live API Creator
     escapes the periods (.) found in table, stored procedure, and function names by appending a tilde (~).
    Best practice:
     Call endpoints by using the 
    href
     attribute that is in the 
    @metadata
     section of a JSON response.
Traverse Rules
You can traverse to a rule from the Schema page.
  1. With your API open, in the Create section, click 
    Schema
    .
    The Tables tab displays by default.
  2. Double-click the rule in the Rules list.
    The rule opens.
Database Administration
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.
With your API open, in the Create section, click 
Data Sources
. The Connection page displays by default.
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), 
CA Live API Creator
 reads the database metadata (your data source connections, resource definitions, logic, and security settings) and caches it in API Server's admin database. This metadata is shared between nodes. As the schema evolves and you change it, you must reload it. When you reload the schema, 
CA Live API Creator
 throws away the API Server cache, rereads the database metadata, and caches it in API Server's admin database.
The time it takes 
CA Live 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. With your API open, in the Create section, click 
    Schema
    .
    The Tables tab 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.
From the Tables tab, 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. From the Tables tab, click 
    Export Schema
    .
    The Export Schema window opens.
  2. Select the data source to export and then click 
    Export
    .
Your schema is exported.
Import Schemas
The schema is a JSON file in a specific format that describes the tables, columns, keys, and relationships. When you import a schema, 
CA Live API Creator
 creates a complete persistent store using the schema model. It uses managed data REST endpoints (
@schema
@table
@column
@databases
, and 
@relationships
) to retrieve this information.
Prerequisite:
 The schema must be marked as editable (the 
Schema is editable
 checkbox is selected).
  1. From the Tables tab, click 
    Import Schema
    .
    The Import Schema window opens.
  2. Select the data source that you want to import the schema to from the 
    Data source
     drop-down, and then click 
    Select File to upload
    .
  3. Go to the JSON file containing the schema that you want to import, and then click 
    Open
    .
  4. (If you are importing a schema from a different SQL database) Complete the following:
    1. Click 
      Advanced Features
      .
    2. Select one or more of the following options:
      Skip Table Creation Phase
       
      Select this checkbox to skip the table-creation phase. When this checkbox is selected, 
      CA Live API Creator
       imports the data without relationship-enforcement constraints.
      Default:
       Cleared
      Skip Relationship Creation Phase
       
      Select this checkbox to skip the relationship-creation phase.
      You can select this checkbox 
      after
       you have imported the schema to have 
      CA Live API Creator
       enforce relationships.
      Default:
       Cleared
      Ignore DB Column Type
       
      Select this checkbox if you are moving from one database vendor to another. 
      CA Live API Creator
       uses the 
      generic_type
       and subtype for each column instead of the 
      db_column_type
      , for example 
      "db_column_type": "DECIMAL(19,4)"
      CA Live API Creator
       attempts to create a datatype similar to the 
      db_column_type
      .  
       Schema import does support cross database datatypes. 
      CA Live API Creator
       guesses the appropriate type if possible.
      Default:
       Cleared
      Ignore Primary Key Name
       
      (Optional) Select this checkbox if you are moving from one database vendor to another and you want 
      CA Live API Creator
       to ignore the exported primary key name and create a new unique name, for example 
      "name": "SQL160918190322411"
      .
      Default:
       Cleared
      Ignore Constraint Name
       
      Select this checkbox to have 
      CA Live API Creator
       ignore the exported database constraint name and create a new name.
  5. 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. From the Tables tab, click 
    Reload Schema
    .
  2. In the Create section, click 
    API Properties
    .
    The Details page appears by default.
  3. 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.
  4. In the Analyze section, click 
    Issues
    , and then review the issues.
    For more information about how to generate a list of API issues and how to review the issues, see Manage API Issues.
Your API is verified.
Associate Entities that Use Sequences to the Sequence
Oracle typically handles auto-generated keys using sequence objects. SQL Server 2012 supports sequences in addition to IDENTITY. There is no way to know which sequence is used by which table or view. You can associate entities (tables or views) that use a sequence with said sequence.
Databases that support sequences display the column and sequence name.
  1. In the Create section, click 
    Data Sources
    .
    The Connection tab appears by default.
  2. Click 
    Sequences
    .
    The Sequences page displays.
  3. For each table or view that uses a sequence, select the column that is set by the sequence, and the corresponding sequence.
    The following image image shows sample sequences on this page:
    Sequences tab.png  
  4. To use sequences for TRIGGER updated columns and Oracle 12c auto-sequences, select the column and leave the sequence value as 
    None
    .
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 associations between a child entity (table or view) and its parent entity. Define foreign keys by way of validations, including a validation name. You can define virtual relationships across schemas by defining relationships between databases.
For more information: