Database Connectivity

Database Connectivity
HID_database_connectivity
The update processing in
Layer7 Live API Creator
relies on database services for locking and transaction management. The retrieval processing in
Layer7 Live API Creator
optimizes queries using the database engine's services, such as the index selection. The defined SQL joins determine how much
Layer7 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*
MongoDB**
Provides optimistic locking
Yes
No
Provides pagination
Yes
Provides row/column security
Yes
No
Can use named filters and sorts
Yes
Can define logic (create rules)
Yes
Use HTTP methods in REST requests
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.
The following error message indicates that your version of MySQL requires that you add a connection to a MySQL data source using Secure Sockets Layer (SSL):
javax.net.ssl.SSLException: Unsupported record version Unknown-0.0
For more information about how to avoid this MySQL SSL exception, 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
    :
    • For a 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
        Layer7 Live API Creator
        supports.
        For more information about the JDBC drivers that
        Layer7 Live API Creator
        supports, see Installation Requirements and Supported Platforms.
        If you require a connection to a data source that
        Layer7 Live API Creator
        does not support, create a data source provider.
        For more information, see Manage Data Source Providers.
      • 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:
      MariaDB, MySQL, Oracle, SQL Server, Azure SQL, PostgreSQL, Derby/JavaDB, Db2 for z/OS, Db2 for LUW, Db2 for i, 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.
      Layer7 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
      Layer7 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
      .
    • For a 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
        Layer7 Live API Creator
        supports.
        For more information about the JDBC drivers that
        Layer7 Live API Creator
        supports, see Installation Requirements and Supported Platforms.
        If you require a connection to a data source that
        Layer7 Live API Creator
        does not support, create a data source provider.
        For more information, see Manage Data Source Providers.
      • 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.
      Layer7 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
      Layer7 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
      .
    • For a 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
        Layer7 Live API Creator
        supports.
        For more information about the JDBC drivers that
        Layer7 Live API Creator
        supports, see Installation Requirements and Supported Platforms.
        If you require a connection to a data source that
        Layer7 Live API Creator
        does not support, create a data source provider.
        For more information, see Manage Data Source Providers.
      • 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
        Layer7 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.
      Layer7 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
      Layer7 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:
    • For a 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 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 and unusual characters using double-quotes (").
      • SQL Server:
        Case-insensitive but case-remembering. Enter the value as it is spelled, using the same case. You can enclose the value 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 and enclose unusual characters using back-ticks (`).
      User name
      Your assigned user name in the database. You use this user name in
      Layer7 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 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 and unusual characters using double-quotes (").
      • SQL Server:
        Case-insensitive but case-remembering. Enter the value as it is spelled, using the same case. You can enclose the value 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 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
      Layer7 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.
    • For a 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
      Layer7 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 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 and unusual characters using double-quotes (").
      • SQL Server:
        Case-insensitive but case-remembering. Enter the value as it is spelled, using the same case. You can enclose the value 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 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.
    • For a 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
      Layer7 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 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 and unusual characters using double-quotes (").
      • SQL Server:
        Case-insensitive but case-remembering. Enter the value as it is spelled, using the same case. You can enclose the value 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 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
    Layer7 Live API Creator
    is unable to connect to the database successfully,
    Layer7 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
Layer7 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
Layer7 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,
Layer7 Live API Creator
reads your database schema and exposes the schema resources (tables, views, and stored procedures).
Layer7 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.
Layer7 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: