Relational Database Connectivity Overview

[aguibert]

Relational databases are a very common part of many applications. This article describes how to configure your server to interact with a relational database.

JDBC driver library configuration

Connectivity with a relational database requires a JDBC driver, which is typically provided by the database vendor. JDBC drivers allow you to define data sources, from which you obtain connections to the database. In order to configure a JDBC data source in Liberty server configuration, you must enable one of the JDBC features and configure a <library> containing your JDBC driver jar:

<featureManager>
    <feature>jdbc-4.2</feature>
</featureManager>

<library id="jdbcLib">
    <fileset dir="${server.config.dir}/jdbc" includes="*.jar"/>
</library>

To be usable in Liberty, your JDBC driver must provide at least one of these types of data sources or must provide a java.sql.Driver with the ServiceLoader facility:

• javax.sql.XADataSource
• javax.sql.ConnectionPoolDataSource
• javax.sql.DataSource
• java.sql.Driver

For the commonly used JDBC drivers, Liberty is already aware of the implementation class names for the various data source types. In most cases, you only need to tell Liberty where to find the JDBC driver.

If you use Maven to build your application, you can download and deploy the JDBC driver to the proper location using pom.xml configuration similar to the following:

      <plugin>
    <groupId>io.openliberty.tools</groupId>
    <artifactId>liberty-maven-plugin</artifactId>
    <version>3.3-M2</version>
        <configuration>
          <!-- Copies the 'com.ibm.db2:jcc:11.5.4.0' dependency to ${server.config.dir}/jdbc at build time -->
          <copyDependencies>
            <dependency>
              <filter>com.ibm.db2:jcc:11.5.4.0</filter>
              <location>jdbc</location>
            </dependency>
          </copyDependencies>
        </configuration>
      </plugin>

Data source configuration

Any JDBC compliant driver may be configured with Liberty. Liberty also has built-in configuration of commonly used database types. The general pattern for configuring a DataSource in Liberty follows the following pattern:

<library id="jdbcLib">
    <fileset dir="${server.config.dir}/jdbc" includes="*.jar"/>
</library>

<dataSource id="myDB" jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties serverName="localhost" portNumber="5432"
                databaseName="myDB"
                user="exampleUser" password="examplePassword"/>
</dataSource>

To use a <dataSource> configured in server.xml, you can inject or lookup the DataSource using one of the following approaches:

Injection (in a web component or enterprise bean component)

@Resource(lookup = "jdbc/myDB")
DataSource myDB;

JNDI lookup (requires the jndi-1.0 feature to be enabled in server.xml)

DataSource myDB = InitialContext.doLookup("jdbc/myDB");

PostgreSQL configuration

JDBC driver available at: https://mvnrepository.com/artifact/org.postgresql/postgresql

Sample data source configuration:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.postgresql serverName="localhost" portNumber="5432"
                databaseName="myDB"
                user="exampleUser"
                password="examplePassword"/>
</dataSource>

To run a Postgres Docker container locally, run the command:

docker run -it --rm=true --memory-swappiness=0 --ulimit memlock=-1:-1 \
           --name postgres-liberty \
           -e POSTGRES_USER=exampleUser \
           -e POSTGRES_PASSWORD=examplePassword \
           -e POSTGRES_DB=myDB \
           -p 5432:5432 \
           postgres:10.5

IBM DB2

JDBC driver available at: https://mvnrepository.com/artifact/com.ibm.db2/jcc

Sample data source configuration:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.db2.jcc serverName="localhost" portNumber="50000"
                databaseName="test"
                user="db2inst1"
                password="foobar1234"/>
</dataSource>

To run a DB2 Docker container locally, run the command:

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 \
           --name db2-liberty \
           -e AUTOCONFIG=false -e ARCHIVE_LOGS=false -e LICENSE=accept \
           -e DBNAME=test \
           -e DB2INSTANCE=db2inst1 \
           -e DB2INST1_PASSWORD=foobar1234 \
           -p 50000:50000 \
           --privileged \
           ibmcom/db2:11.5.0.0a

Microsoft SQLServer

JDBC driver available at: https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc

Sample data source configuration:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.microsoft.sqlserver serverName="localhost" portNumber="1433"
                databaseName="tempdb"
                user="sa"
                password="examplePassw0rd"/>

</dataSource>

To run a SQL Server Docker container locally, run the command:

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 \
           --name mssql-liberty \
           -e ACCEPT_EULA=Y \
           -e SA_PASSWORD=examplePassw0rd \
           -p 1433:1433 \
           mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

Oracle

JDBC driver available at: https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8

Sample data source configuration:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.oracle URL="jdbc:oracle:thin:@//localhost:1521/myDB" 
                user="exampleUser"
                password="examplePassword"/>
</dataSource>

MySQL

JDBC driver available at: https://mvnrepository.com/artifact/mysql/mysql-connector-java

Sample data source configuration:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties serverName="localhost" portNumber="3306"
                databaseName="myDb"
                user="exampleUser"
                password="examplePassword"/>
</dataSource>

To run a MySQL Docker container locally, run the command:

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 \
           --name mysql-liberty \
           -e MYSQL_DATABASE=myDB \
           -e MYSQL_USER=exampleUser \
           -e MYSQL_PASSWORD=examplePassword \
           -p 3306:3306 \
           mysql:8

Derby Embedded (in-memory DB)

JDBC driver available at: https://mvnrepository.com/artifact/org.apache.derby/derby/10.14.2.0

Sample data source configuration:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.derby.embedded databaseName="memory:myDB" createDatabase="create"/>
</dataSource>

Generic database unknown to Liberty

Sample data source configuration:

<dataSource id="myDB" jndiName="jdbc/myDB" type="javax.sql.XADataSource">
    <jdbcDriver libraryRef="jdbcLib"
                        javax.sql.XADataSource="com.example.jdbc.SampleXADataSource"/>
    <properties serverName="localhost" portNumber="1234"
                databaseName="myDB"
                user="exampleUser"
                password="examplePassword"/>
</dataSource>

Specify the type of the DataSource using the <dataSource type="..."> attribute, where type can be one of the interface class names described in the "JDBC driver library configuration" section. Then, specify the mapping of interface class name to the driver's implementation of that class on the <jdbcDriver> element as shown above.

Configuring driver-specific data source attributes

Every JDBC driver provides a different collection of attributes that may be configured on its data source implementation classes. As long as the JDBC driver's data source has setter methods with a String or primitive parameter it is possible to configure these attributes in Liberty configuration by following the pattern:

    <properties someProperty="someValue" anotherProperty="5" />

For example, consider the currentLockTimeout attribute on the DB2 JDBC driver's data source classes:

• public void setCurrentLockTimeout(int lockTimeout);
• public int getCurrentLockTimeout();

It is possible to configure this setting with the following server.xml configuration:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties serverName="localhost" portNumber="1234" databaseName="myDB"
                user="exampleUser" password="examplePassword"
                currentLockTimeout="30s"/>
</dataSource>

Types of data sources and resolution strategy

To access a database from your application, application code must use the javax.sql.DataSource interface. The application server provides a managed implementation of this javax.sql.DataSource interface, which is backed by one of the various data source or driver implementations that JDBC drivers provide. These data source or driver implementations come in the following varieties:

• javax.sql.DataSource This type of data source is the basic form. It does not provide interoperability that enhances connection pooling, and cannot participate as a two-phase capable resource in transactions that involve multiple resources.

• javax.sql.ConnectionPoolDataSource This type of data source is enabled for connection pooling. It cannot participate as a two-phase capable resource in transactions that involve multiple resources.

• javax.sql.XADataSource This type of data source is both enabled for connection pooling and is able to participate as a two-phase capable resource in transactions that involve multiple resources.

• java.sql.Driver This is a basic way to connect that requires a URL and is typically used in Java SE. Like javax.sql.DataSource, it does not provide interoperability that enhances connection pooling, and cannot participate as a two-phase capable resource in transactions that involve multiple resources.

When the <dataSource type="..."/> attribute is omitted, Liberty chooses the data source type in the following order, depending on which is available.

If you are using the jdbc-4.3 feature or higher or if it is the DefaultDataSource, then Liberty chooses the data source type in the following order, depending on which is available:

• javax.sql.XADataSource
• javax.sql.ConnectionPoolDataSource
• javax.sql.DataSource

If you are using the jdbc-4.2, 4.1, or 4.0 feature and it is not the DefaultDataSource, then Liberty chooses the data source type in the following order, depending on which is available:

• javax.sql.ConnectionPoolDataSource
• javax.sql.DataSource
• javax.sql.XADataSource

It should be noted that the capability that is provided by XADataSource is generally a superset of the capability that is provided by the other data source types, although some JDBC vendors might have subtle differences in behavior or introduce different limitations between the various data source types that are not spelled out in the JDBC specification.

Configuring a default data source

If you enable any Java EE or Jakarta EE features it is possible to configure a default data source. To configure a default data source, set the ID of the <dataSource> element to DefaultDataSource. For example:

<dataSource id="DefaultDataSource">
    <!-- properties and jdbcDriver -->
</dataSource>

When a default data source is configured, a reference can be obtained in either of the following ways:

Injection

@Resource
DataSource myDB;

JNDI lookup (requires the jndi-1.0 feature to be enabled in server.xml)

DataSource myDB = InitialContext.doLookup("java:comp/DefaultDataSource");

Configuring the connection pool

Every <dataSource> element may have an optional <connectionManager> element nested inside of it. By default, the connection manager for each <dataSource> element has sensible default values, but can be customized if needed. For example:

<dataSource jndiName="jdbc/myDB">
  <jdbcDriver libraryRef="jdbcLib"/>
  <connectionManager maxPoolSize="10" minPoolSize="2"/>
  <properties ... />
</dataSource>

For complete details on <connectionManager> configuration attributes, refer to this page: https://openliberty.io/docs/20.0.0.10/reference/config/connectionManager.html

Validating JDBC Connections

Once you have configured your data source, you may want to quickly test the configuration to see if your Liberty server can access your database. To do this, refer to the "Testing database connections" topic here: https://draft-openlibertyio.mybluemix.net/docs/20.0.0.11/testing-database-connections.html

[dmuelle]

Thanks @aguibert - this looks good. I can work on an asciidoc draft for your review on the OL draft site. I can see a few tweaks to make per ID guidelines and a couple blanks I will probably need help filling in due to my limited familiarity with the subject.

One question I have is about the example configs for different datasources- Are the examples you give here just a template that I should fill out with the values in the KC topic? I see some database-specific example values there that are not in your PostgreSQL example.

I also notice that topic doesn't mention the docker command- is that for development environments where there you'd want to run the db in a local container for testing purposes?

[aguibert]

@dmuelle don't port this to asciidoc quite yet please. I'm still soliciting feedback from other SMEs in this area and have just made the first round of updates a few minutes ago, but there will likely be a few more edits.

One question I have is about the example configs for different datasources- Are the examples you give here just a template that I should fill out with the values in the KC topic? I see some database-specific example values there that are not in your PostgreSQL example.

I have example configs for each DB, I'll fill them in now since the other SMEs seem to be happy with the overall structure of the article.

I also notice that topic doesn't mention the docker command- is that for development environments where there yopu'd want to run the db in a local container for testing purposes?

Yes, the docker commands are very handy for local development. I've copied this information from my cheat sheet here: https://aguibert.github.io/openliberty-cheat-sheet/#_postgresql

[dmuelle]

@aguibert initial draft is now available for review at https://draft-openlibertyio.mybluemix.net/docs/20.0.0.12/relational-database-connections-JDBC.html

[aguibert]

Thanks for putting this together @dmuelle !

Review comments:

• Wording change: can connect and interact with --> can connect to and interact with
  • Instead of saying JDBC provider say JDBC driver
  • Conciseness: so that an application that is running on --> so an application running on
  • the link for Java Database Connectivity feature. is broken, but that might just be because of the draft site. But it looks like it's pointing to jdbc-4.3. Can we have it point to jdbc-4.2 instead? This is because jdbc-4.3 only works for JDK 11+ (many users are still on Java 8)
  • Conciseness: contains your JDBC driver file or files --> contains your JDBC driver
  • In the first code snippet, instead of using <fileset dir="server1/jdbc" includes="*.jar"/> lets use <fileset dir="${server.config.dir}/jdbc" includes="*.jar"/> (same comment applies to all other instances of this fileset too)
  • Wording change: In most cases, you specify only the location of the JDBC driver. --> In most cases, you only need to specify the location of the JDBC driver.
  • The code block on this sentence is formatted incorrectly:

    In this example, the com.ibm.db2:jcc:11.5.4.0' dependency is copied to the `${server.config.dir}/jdbc directory at build time to deploy the IBM Db2 JDBC driver:

  • In the maven code block the indentation is off, and there is a missing closing </plugin> block on the end
  • In the section Generic database that is unknown to Open Liberty the indentation of the javax.sql.XADataSource="com.example.jdbc.SampleXADataSource"/> line is a bit off
  • Instead of as shown in the previous example. should it be as shown in the example above? As a reader seeing "previous example" makes me wonder if it's referring to the "current" example directly above, or the example in the previous section.
  • It looks like the Configuring a default data source section was replaced with a link to a different page that describes the same content. I think this new page is the proper place for the Configuring a default data source section, so if we only want to have that info in one place, I propose we have it here and then link to it from the feature doc.
  • Wording change: The java.sql.Driver driver implementation provides a basic way to connect to a data source --> The java.sql.Driver driver implementation provides a basic way to connect to a database
  • In the last 3 bullet points under the If you are using the jdbc-4.2, 4.1, or 4.0 feature section, the class names should be in a code block like we have done in the previous section

[dmuelle]

@aguibert thanks for reviewing! I made the following edits per your comments:

• [x] Wording change: can connect and interact with --> can connect to and interact with
• [x] Instead of saying JDBC provider say JDBC driver
• [x] Conciseness: so that an application that is running on --> so an application running on got rid of the first "that" but have to keep "that is running" per acrolinx guidance re gerund phrases
• [x] the link for Java Database Connectivity feature. is broken, but that might just be because of the draft site. But it looks like it's pointing to jdbc-4.3. Can we have it point to jdbc-4.2 instead? This is because jdbc-4.3 only works for JDK 11+ (many users are still on Java 8) the link seems to be working. I updated it to go to 4.2 except where 4.3 is explicitly referenced
• [x] Conciseness: contains your JDBC driver file or files --> contains your JDBC driver
• [ ] In the first code snippet, instead of using <fileset dir="server1/jdbc" includes="*.jar"/> lets use <fileset dir="${server.config.dir}/jdbc" includes="*.jar"/> (same comment applies to all other instances of this fileset too) Docs guidance is not to use ${server.config.dir} in config examples and instead to use server1 for the server throughout examples in the docs. Per @lauracowen "Don't use variables (eg for values or for file paths). Just provide plausible example values and then describe what they represent in the explanation of the example config."
• [ ] Wording change: In most cases, you specify only the location of the JDBC driver. --> In most cases, you only need to specify the location of the JDBC driver. per acrolinx, "only" has to directly precede the phrase it modifies.
• [x] In the maven code block the indentation is off, and there is a missing closing </plugin> block on the end
• [x] In the section Generic database that is unknown to Open Liberty the indentation of the javax.sql.XADataSource="com.example.jdbc.SampleXADataSource"/> line is a bit off
• [x] Instead of as shown in the previous example. should it be as shown in the example above? As a reader seeing "previous example" makes me wonder if it's referring to the "current" example directly above, or the example in the previous section. can't use "above" for accessibility reasons- however I agree "previous" is confusing here. I moved the description so that it's before the example and says "in the following example..".
• [x] It looks like the Configuring a default data source section was replaced with a link to a different page that describes the same content. I think this new page is the proper place for the Configuring a default data source section, so if we only want to have that info in one place, I propose we have it here and then link to it from the feature doc. Since it's a feature configuration example, I put it on the JDBC feature page for consistency with the rest of the docs. I've moved it back into this doc
• [x] Wording change: The java.sql.Driver driver implementation provides a basic way to connect to a data source --> The java.sql.Driver driver implementation provides a basic way to connect to a database
• [x] In the last 3 bullet points under the If you are using the jdbc-4.2, 4.1, or 4.0 feature section, the class names should be in a code block like we have done in the previous section

[dmuelle]

from @aguibert via slack:

Lets just use dir="jdbc" then. Using server1/jdbc is misleading because if a relative path is specified, then it is relative to ${server.config.dir}, meaning that server1/jdbc would evaluate to ${server.config.dir}/server1/jdbc and it would be strange if someone had a server1 folder in their server folder.

however, I think we should be allowed to use built-in variables like ${server.config.dir} in config docs @lauracowen, because it makes it more clear where the dir actually is. Not everyone knows that if you specify dir="jdbc" that it's relative to ${server.config.dir}

I've updated the example to just use dir="jdbc"

[dmuelle]

@lauracowen this is ready for your review

https://draft-openlibertyio.mybluemix.net/docs/20.0.0.12/relational-database-connections-JDBC.html

[dmuelle]

See issue #369 re default data source config example on the JDBC feature page- should the example go both on that page and in this doc?

[lauracowen]

In general, looks good. Some specific comments:

• Personally, I'd have taken out the second "that" and kept the first "that"... :) (In the first para of the topic) I agree that multiple "that" are too much/clunky (regardless of what IBM style says) - it just needs to read smoothly/clearly.
• I like the examples in the second para - nice concrete touch there :)
• I don't think "The" in "The JDBC API" should be part of the link. Just "JDBC API".
• The different XML examples should use consistent indentation (number of spaces) throughout the topic - the first uses much deeper indentation than the second so that the second (the Maven pom.xml example) looks a bit odd.
  • We should actually have consistent indentation throughout all the docs - we should probably agree on a standard number of spaces so that all the writers use the same.
• Re "In most cases, you specify only the location of the JDBC driver." - would it work to say "In most cases, you can specify only the location of the JDBC driver." - I think @aguibert 's point was partly that you can specify more but you don't have to, rather than that you must only specify the location (which is what "you specify only..." kind of implies). See what you think and check accuracy of what I said!
• dodgy syntax in "com.ibm.db2:jcc:11.5.4.0' dependency is copied to the${server.config.dir}/jdbc` directory" - the "dependency is copied to the" bit shouldn't be in monospace.
• In the "Data source configuration" section, I don't think you need to say "or an included file". That goes without saying (or at least a link to the config overview topic would handle it if really necessary). Any time you put stuff in a server.xml you can use an included file etc - that's not specific to jdbc config.
• "For example, the following sample shows" -> "The following example shows" or "For example, the following code shows"?
• btw, I agree with you both that "previous example" when you mean "the example above" or "the example I've just shown you" can be confusing to read (again, regardless of what IBM style says). As Andy said "previous" can mean anything prior to now - not necessarily the last example shown. I like how you've dealt with it (I think "the following example", as a phrase, suffers less from this lack of clarity because you can end the sentence in a colon to be clearly introducing the example you mean).
• In discussion with Fred and Nathan about the "testing server configurations" topic (probably going with this new topic title) that @Rwalls1 is currently writing based on (based on Nathan's blog post which will then be redirected to the doc topic), Fred suggested:

  I like the idea that if I'm looking at the jdbc topic Andy just wrote, that I find a section (perhaps condensed) on testing connectivity. Same for when we have JCA and JMS topics, condense section on testing in those topics. And the condensed topic links to a more thorough treatment of the issue @aguibert Is it useful to have the "Validating Connections" section from your cheat sheet in this topic? Basically, assume that the reader knows how to do this testing and doesn't need the background explanation but might need to refer back to the exact details. Then we can link to separate testing topic if they do need the background explanation of how to do the testing. What I want to avoid is making this jdbc topic much longer or duplicating masses from that separate testing topic which is useful in its own right, in other contexts, and therefore should be separate. If you agree, we can take a look and see how it all fits together.

• In the "Common data source configuration examples" section, would it be useful to have a short link list of the databases mentioned in this section? Or is it just easier to scroll and skim down the headings to find the one you want?
• Where it says to get the driver from Maven Central, should that be "Download the xxx driver" rather than "Get"? I ask because "Get" is a bit more vague if you mean "Download the package" rather than "Add an entry into your pom.xml so that the Maven build downloads it for you". If it should be the latter, then maybe "Add the xxx driver to your pom.xml file."?
• With the docker commands, I can see how they're useful in themselves but does the user have to have a particular docker container image for them to work? Will those commands just work for any docker container containing that database? Are there images on DockerHub for each of the databases we mention that would be useful for us to link to?
• "Microsoft SQL Server" (space needed in heading and subsequent mentions)
• "Generic database that is unknown to Open Liberty" - this doesn't feel like a "common configuration". This feels like an alternative to the list of common configurations (otherwise, we'd have an easier way to configure it built into Liberty). This is for exceptions. I think it should be an H2 header eg "Configuring databases that are unknown to Open Liberty" - also a given database, even if it's unknown to Liberty is surely not "generic" - the configuration template we provide is generic, not the database itself?
• "Open liberty provides a managed " - missing capital L
• Can you double-check the connection pooling link? When I click it, it takes me briefly to the anchored section then seems to redirect to the top of the page (and the URL of the top of the page). Not sure why.
• "If the type attribute is not specified in the data source configuration, Open Liberty chooses the data source type in the following order, " reads a bit oddly because it doesn't then lead into a list of the orders. Could it be rephrased as "If the type attribute is not specified in the data source configuration, Open Liberty chooses the data source type according to a defined order, " (that's not the greatest wording but something like that that makes the point before the next two paragraphs give the actual orders)

[dmuelle]

Thanks for reviewing @lauracowen. I've made edits for the following items, will break out the stuff that needs @aguibert input into a separate comment and address there.

• [x] I don't think "The" in "The JDBC API" should be part of the link. Just "JDBC API".
• [x] The different XML examples should use consistent indentation (number of spaces) throughout the topic - the first uses much deeper indentation than the second so that the second (the Maven pom.xml example) looks a bit odd. I lost a bit of depth on the pom.xml file that I've restored now. It's still not as deep as the others but it also has to cover a lot more ground. The others are consistent with existing examples in the feature config
• [x] Re "In most cases, you specify only the location of the JDBC driver." - would it work to say "In most cases, you can specify only the location of the JDBC driver." - I think @aguibert 's point was partly that you can specify more but you don't have to, rather than that you must only specify the location (which is what "you specify only..." kind of implies). See what you think and check accuracy of what I said!
• [x] dodgy syntax in "com.ibm.db2:jcc:11.5.4.0' dependency is copied to the${server.config.dir}/jdbc` directory" - the "dependency is copied to the" bit shouldn't be in monospace.
• [x] In the "Data source configuration" section, I don't think you need to say "or an included file".
• [x] "For example, the following sample shows" -> "The following example shows" or "For example, the following code shows"?
• [ ] In the "Common data source configuration examples" section, would it be useful to have a short link list of the databases mentioned in this section? It might be useful if we had dependable anchor link functionality :) I'm reluctant to add here before we have that bug resolved. Also concerned it might give the reader the impression that this content makes up the rest of the page. I can go ahead and add it if you disagree
• [x] "Microsoft SQL Server" (space needed in heading and subsequent mentions)
• [x] "Open liberty provides a managed " - missing capital L
• [x] Can you double-check the connection pooling link? When I click it, it takes me briefly to the anchored section then seems to redirect to the top of the page (and the URL of the top of the page). Not sure why. bug fix in progress for this

[dmuelle]

Hi @aguibert - Laura and I had a couple questions re this draft:

• In discussion with Fred and Nathan about the "testing server configurations" topic (probably going with this new topic title) that @Rwalls1 is currently writing based on (based on Nathan's blog post which will then be redirected to the doc topic), Fred suggested:

  I like the idea that if I'm looking at the jdbc topic Andy just wrote, that I find a section (perhaps condensed) on testing connectivity. Same for when we have JCA and JMS topics, condense section on testing in those topics. And the condensed topic links to a more thorough treatment of the issue @aguibert Is it useful to have the "Validating Connections" section from your cheat sheet in this topic? Basically, assume that the reader knows how to do this testing and doesn't need the background explanation but might need to refer back to the exact details. Then we can link to separate testing topic if they do need the background explanation of how to do the testing. What I want to avoid is making this jdbc topic much longer or duplicating masses from that separate testing topic which is useful in its own right, in other contexts, and therefore should be separate. If you agree, we can take a look and see how it all fits together.

• Where it says to get the driver from Maven Central, should that be "Download the xxx driver" rather than "Get"? I ask because "Get" is a bit more vague if you mean "Download the package" rather than "Add an entry into your pom.xml so that the Maven build downloads it for you". If it should be the latter, then maybe "Add the xxx driver to your pom.xml file."?

• With the docker commands, I can see how they're useful in themselves but does the user have to have a particular docker container image for them to work? Will those commands just work for any docker container containing that database? Are there images on DockerHub for each of the databases we mention that would be useful for us to link to?

• "Generic database that is unknown to Open Liberty" - this doesn't feel like a "common configuration". This feels like an alternative to the list of common configurations (otherwise, we'd have an easier way to configure it built into Liberty). This is for exceptions. I think it should be an H2 header eg "Configuring databases that are unknown to Open Liberty" - also a given database, even if it's unknown to Liberty is surely not "generic" - the configuration template we provide is generic, not the database itself?

Is the reference here to a generic example of a database that liberty doesn't recognize, or is it specifically about a generic database? If the latter, I can include a brief clarification of the term

• "If the type attribute is not specified in the data source configuration, Open Liberty chooses the data source type in the following order, " - to clarify- Does OL choose from these options, or look for databases of each type in the given order until it finds one and stops looking? Also- is a dev even likely to need to know this? Or just that the type has to be one of those listed and Liberty will look until it finds one of the specified types. Just wondering if the order per se is useful info.

[aguibert]

Where it says to get the driver from Maven Central, should that be "Download the xxx driver" rather than "Get"?

To me "Download" implies manually downloading. I think "Get" is a fine term to use here because on the front page of openliberty.io we have a "Get Open Liberty" button which links to the downloads page with instructions for getting in image using maven/gradle/docker/zip.

With the docker commands, I can see how they're useful in themselves but does the user have to have a particular docker container image for them to work?

Not entirely sure what you mean here, but by running those commands you get a container for that particular database. Also, the exact params in the docker command pair with the corresponding server.xml config snippets I've shown. So for example to get up and running with a DB2 database and configure their Liberty server to use it, they just run the command:

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 \
           --name db2-liberty \
           -e AUTOCONFIG=false -e ARCHIVE_LOGS=false -e LICENSE=accept \
           -e DBNAME=test \
           -e DB2INSTANCE=db2inst1 \
           -e DB2INST1_PASSWORD=foobar1234 \
           -p 50000:50000 \
           --privileged \
           ibmcom/db2:11.5.0.0a

which will start up a DB2 container instance on the developer's machine that they can connect to with user=db2inst1 and password=foobar1234 and so they can paste the XML config snippet into their server.xml to talk to that container:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.db2.jcc serverName="localhost" portNumber="50000"
                databaseName="test"
                user="db2inst1"
                password="foobar1234"/>
</dataSource>

"Generic database that is unknown to Open Liberty" - this doesn't feel like a "common configuration". ... even if it's unknown to Liberty is surely not "generic" - the configuration template we provide is generic, not the database itself?

I'm fine with changing the heading to Configuring databases that are unknown to Open Liberty as you suggest. And yes, the database itself is not "generic", we are instead showing a more generic way to configure a database. FYI the original source for this section is from the For a JDBC driver that is not known to Liberty section of this page: https://www.ibm.com/support/knowledgecenter/SSD28V_liberty/com.ibm.websphere.wlp.core.doc/ae/twlp_dep_configuring_ds.html

Is the reference here to a generic example of a database that liberty doesn't recognize, or is it specifically about a generic database?

A database that liberty doesn't recognize. Even if Liberty does know about it (e.g. DB2) you can still configure a datasource using the "generic way" (although the config is more verbose)

Does OL choose from these options, or look for databases of each type in the given order until it finds one and stops looking? Also- is a dev even likely to need to know this?

OL looks for these datasource types in the given order until it finds one. A dev probably would not need to know this info, but I think it's important enough that it should be documented

[lauracowen]

@aguibert

To me "Download" implies manually downloading. I think "Get" is a fine term to use here because on the front page of openliberty.io we have a "Get Open Liberty" button which links to the downloads page with instructions for getting in image using maven/gradle/docker/zip. That was what I was asking really. By "Get", do you mean they should get it by adding it into their Maven pom.xml rather than by manual download? It wasn't clear to me in reading that section what you expected the reader to do with the information and, if they should be adding it to their pom.xml, should we explicitly say that?

[dmuelle]

Thanks @lauracowen @aguibert , made the following updates to the draft

• changed the heading to H2, Configuring databases that are unknown to Open Liberty
• added an anchor list for the common database examples since we now have working anchor links :)
• clarified the datasource types order info to say that OL looks for datasource in the specififed ourder and chooses thee first type that's available

[lauracowen]

• Minor thing but should "Default data source configuration" heading be "Configuration of a default data source"? I read it as a default configuration (in which case I wondered why you were even telling me about it) but actually it's about setting a default data source.
• "Embedded Derby" bullet link has a random hyphen after it.
• I think I follow @aguibert 's explanation of "Get" on re-reading it. I think his point is that you get it by running the Docker command to get the container. That's fine. But, in that case, I think that first sentence in each section shouldn't be in a paragraph on its own. I read it as "you need to get the driver from Maven Central, then do some configuration" (which might be partly because there's a link to Maven Central when you don't actually need to go follow it) when actually, it should be read as "you need to get the driver from Maven Central and here's how to do that". Is that correct?
• Should "Data source types" be a lower-level heading beneath the "unknown to OL" section (like it was before)? Or is it applicable beyond just doing a generic configuration?

[aguibert]

Minor thing but should "Default data source configuration" heading be "Configuration of a default data source"? I read it as a default configuration (in which case I wondered why you were even telling me about it) but actually it's about setting a default data source.

I think either Default data source configuration or Configuration of the default data source would be correct (notice a --> the since there can only be one default data source)

Should "Data source types" be a lower-level heading beneath the "unknown to OL" section (like it was before)? Or is it applicable beyond just doing a generic configuration?

It is generally applicable

[dmuelle]

Thanks @lauracowen @aguibert - I made the following changes to the draft:

• Changed Default data source configuration heading to Configuration of the default data source
• Fixed "Embedded Derby" bullet link has a random hyphen after it.
• Put the "Get..." statements in the example datasource configs in the same paragraph w/example intro

[lauracowen]

Thanks - signing off.

[Charlotte-Holt]

@dmuelle Looks great!

Peer review feedback

Data source configuration

• [x] Regarding "For example, the following code shows the currentLockTimeout property on the Db2 JDBC driver’s data source classes:" - Previously in the topic, you referred to the Db2 driver as the "IBM Db2 JDBC driver", I think you could make these consistent.
• [x] In "For more information, see Testing database connections.", the link to "Testing database connections" links to a topic called "Validating server connections"

Common data source configuration examples

• [x] I think "Get the PostgreSQL JDBC Driver from the Maven Central." should be "Get the PostgreSQL JDBC Driver from Maven Central."
• [x] When you use Oracle UCP with Open Liberty you are using the Oracle UCP connection pool instead of the Open Liberty built-in connection pooling functionality. --> Add a comma after "Open Liberty"
• [ ] Oracle UCP might require some properties, such as the user name and password, to be set in the properties.oracle.ucp element. --> I think make "user name" one word
• [x] Since the Liberty connection pool is disabled, some of the Open Liberty data source and connection manager configuration values are ignored. --> I'd use "Because" instead of "Since for clarity

Data source types

• [x] This type of data source is both enabled for connection pooling and is able to participate as a two-phase capable resource in transactions that involve multiple resources. --> Not sure you need "both" in this sentence, it was reading a little funny to me
• [x] Did you use the macros without the title attribute for the JDBC feature names in this section? None of them are plain text.

Application configuration for relational database connections

• [x] To use a data source that is configured in your server.xml file, you can either inject or look it up by referencing the value of the jndiName attribute in your application code. --> Would it be clearer to say, "To use a data source that is configured in your server.xml file, you can either inject the data source or look it up by referencing the value of the jndiName attribute in your application code. "
• [x] The following examples assume a jndiName value of jdbc/myDB is specified in the dataSource element in the server.xml file. --> I'd consider adding "that" after "assume"
• [x] If myDB is configured as the default data source, you can omit the lookup object, as shown in the following example: --> Need a noun after myDB
• [x] When the Java Naming and Directory Interface feature is enabled in your server.xml file, you can obtain a reference to the data source from your application by JNDI lookup, as shown in the following example: --> I don't think JNDI was ever spelled out until this sentence. So maybe either put JNDI in parentheses in the feature name or spell it out before lookup
• [x] If myDB is configured as the default data source, the JNDI lookup can specify a java:comp/DefaultDataSource value instead of the JNDI name for the data source, as shown in the following example: --> Need a noun after myDB

[dmuelle]

Thanks for reviewing @Charlotte-Holt , I have updated the file per your suggestions above, with the exception being for Oracle UCP I realized the property was actually just user instead of username, so I made that change accordingly. Also had to shuffle a few sentences around to avoid acrolinx word length restrictions.

[dmuelle]

Doc is now on vNext branch, will publish with 20.0.0.12 on 11.20

Draft link: https://draft-openlibertyio.mybluemix.net/docs/latest/relational-database-connections-JDBC.html

[lfielke]

Some constructive criticism, for this page if I may: I had some trouble following this documentation page using the Open Liberty Gradle plugin. The page talks about using the Maven plugin to copy dependencies to the server config directory, however it doesn't mention the Gradle plugin.

We're already using Gradle to build our other projects, so I went searching for more information. I did find a blog post at https://openliberty.io/blog/2021/02/18/dev-mode-container-liberty-maven-gradle-plugins.html that describes that:

In this blog post, we introduce the general availability of container support in dev mode and support for copying dependencies. Both of these new capabilities are included in the latest releases of the Liberty Maven and Gradle Plug-ins.

Based on this I thought there should be a way to configure the Gradle plugin to copy dependencies. I couldn't find any examples of this in the docs or elsewhere on the web, so I opened an issue https://github.com/OpenLiberty/ci.gradle/issues/619 to see if I had overlooked this. The suggestion was to use a Gradle copy task to achieve this, which makes sense because it's easy to add ad-hoc Gradle tasks to a build.

So reflecting on this, I humbly suggest a couple of things to help steer new users in the right direction:

• On the docs page, mention why the JDBC driver cannot be managed with the rest of the application's dependencies (eg the aim is to get it loaded by the right classloader because Open Liberty wants to do connection pooling, make it available of JNDI, the same driver cannot be loaded by multiple web applications, etc. Not sure if these are relevant to Open Liberty or there are other specific reasons for this, as I'm a newcomer to Open Liberty).
• Mention the desired end result: getting the driver JAR in the server/config/jdbc dir so that is included when you package the server, eliminating the need to manually install the driver on the server.
• Suggest a Gradle copy task could be used to achieve this, perhaps including the example from issue 619 above.
• Amend the blog post to not suggest that the Gradle plugin directly supports copy dependencies.

For reference, the docs page I'm referring to is: https://openliberty.io/docs/21.0.0.7/relational-database-connections-JDBC.html#_jdbc_driver_library_configuration However the version 21.0.0.7 docs don't seem to be available currently, but the version 21.0.0.6 page seems to be the same.

[dmuelle]

Thanks so much for this feedback @lfielke- I've opened a new issue #4446 to track the work to update the page with this information asap.

[dmuelle]

https://openliberty.io/docs/latest/relational-database-connections-JDBC.html

[dmuelle]

Edited content is on vNext and will publish with 23.0.0.9. Closing as completed.