Using Db2 for persistence

Create a Db2 instance

1. Log in to your IBM Cloud account Link.

2. Click Create resource.

3. Choose Db2.

4. Select the Pricing Plan - Standard or Enterprise

   • Note, the IBM FHIR Server does not support using the Lite plan.

5. Create Create

Scale the instance

1. The scaling of the instance requires a service restart.
2. The instance CPU/Memory are scalable up and down. The storage in only scaled up.

Create the administrator credential

1. Navigate to the Db2 instance you created.

2. Click on the Service credentials panel.

   • If the list of service credentials is empty, create a new one.
     1. Click New credential (+)
     2. Enter the Name. Any name works for example, ibm-fhir-server-db2.
     3. Click Add

3. Access the credential, select View Credentials. The entry you just created looks like the following block of JSON(the secrets are blanked out here):

{
  "apikey": "******",
  "connection": {
    ...
    "db2": {
      "authentication": {
        "method": "direct",
        "password": "P1234",
        "username": "username1"
Copy to clipboardCopied!

4. Save these details for later, as these properties are needed to deploy and manage the IBM FHIR Server schema.

Adding the FHIRSERVER user

Using IAM apiKey

1. Read API Key and the tenant key from the fhir-server-config.json

2. Connect to Db2 to access authenticated data using IAM

3. Confirm tenant-key to access authorized data

1. On the IBM Cloud console, upper right corner, select Manage > Access (IAM).

2. Select the Service IDs panel.

3. Click Create (+)

   1. Enter a meaningful name, such as fhir-service-id.
   2. Enter a description, such as for instance db2-ho.
   3. Click the Create.
   • Note (1): If you have already created the entry, click the entry.
   • Note (2): You may have to wait for the table to populate.

4. Select the API keys tab.

   • Note: This tab is not the same IBM Cloud API keys on the left.

5. To create an API Key, select Create (+).

   1. Enter a name for the api key, for example, fhir-server-api.
   2. Click Create.
   3. Copy or download the key.
   • Note: if you don’t retain a copy, don’t panic - you can simply delete the API key and create a new one. API keys are designed to support additions and deletions, so you add as many as you need and delete others for any reason.

1. Navigate to the Resources page

2. Find and click on your IBM Db2 on Cloud instance.

3. Click on Manage.

4. Click on Open Console.

5. Click Administration > User Management

   • To confirm the IAM instance:
     1. Click Run Sql (click create new if not brought into SQL edit session)
     2. Enter the SQL

        SELECT CASE WHEN VALUE = 'IBMIAMauth' THEN 1 ELSE 0 END AS IAM_ENABLED FROM SYSIBMADM.DBMCFG WHERE NAME = 'srvcon_gssplugin_list'
        Copy to clipboardCopied!

     3. You see 0 if not IAM enabled.

6. Click Add. A panel opens on the right-hand side.

7. Select Add IBMid User at the top.

   • User ID: FHIRSERVER

   • IBMid: paste the service id (not service id name) from the Service Id created previously. To get that navigate to the service id you created earlier and click on Details link (top right side next to Actions Menu). A panel opens on the right-hand side which contains ID. This ID needs to be used as IBMid value.

   • Note (1): The page forces the value to lower-case, so ServiceId becomes serviceid. Don’t be alarmed, it still works. Same for the User ID.

   • Note (2): Do NOT select Administrator. One should follow the least-privelege principal for the FHIRSERVER user.

8. Click Create.

Using Db2 Auth

1. On the Db2 Resource, click Manage

2. Click Open Console

3. Click Administration > User Management

4. Click Add User, and enter the user details with a sufficiently complex password. Set the User Privilege as User.

5. Save these details for the datasource.xml.

Testing the connection

Test IAM Access

1. Copy the command to your code editor

   java -cp /path/to/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc  -url "jdbc:db2://<DB2-HOSTNAME>:<DB2-HOST-PORT>/BLUDB:apiKey=<API-KEY>;securityMechanism=15;sslConnection=true;"
   Copy to clipboardCopied!

   • Note: Don’t forget the trailing ; in the URL. Some of the documented examples don’t include it, but it is required in order for the connection to work, although this may be fixed in a future driver release. This only affects this test URL, not the actual FHIR server configuration.

2. Replace the following values with your service details:

   • /path/to/db2jcc4.jar : replace with the path to your driver jar.

   • <DB-HOSTNAME>: the hostname of your Db2 service from the Service Credentials page

   • <DB-HOST-PORT>: the port of your Db2 service from the Service Credentials page

   • <API-KEY>: the API key value created in the previous section

   • Note: When using an API Key, no username needs to be provided. This is because the API Key maps to a ServiceId, and that ServiceId is mapped to the Db2 user.

3. Run in your favorite terminal, and you should see no errors in the output. You should see output like:

   [jcc][10516][13709]Test Connection Successful.
   Copy to clipboardCopied!

Test Db2 Auth Access

1. Copy the command to your code editor

   java -cp /path/to/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc  -url "jdbc:db2://<DB2-HOSTNAME>:<DB2-HOST-PORT>/bludb:user=<userid>;password=<your_password>;sslConnection=true;"
   Copy to clipboardCopied!

   • Note: Don’t forget the trailing ; in the URL. Some of the documented examples don’t include it, but it is required in order for the connection to work, although this may be fixed in a future driver release. This only affects this test URL, not the actual FHIR server configuration.

2. Replace the following values with your service details:

   • /path/to/db2jcc4.jar : replace with the path to your driver jar.
   • <DB-HOSTNAME>: the hostname of your Db2 service from the Service Credentials page
   • <DB-HOST-PORT>: the port of your Db2 service from the Service Credentials page
   • <userid>: The userid to acecss the db
   • <your_password>: The password to access the db with

3. Run in your favorite terminal, and you should see no errors in the output. You should see output like:

   [jcc][10516][13709]Test Connection Successful.
   Copy to clipboardCopied!

Deploy the IBM FHIR Server schema

1. download the fhir-persistence-schema cli jar from the corresponding project release: https://github.com/IBM/FHIR/releases

2. create a properties file named db2.properties with the Db2 Admin connection info from IBM Cloud; for example:

   db.host=<DB-HOSTNAME>
   db.port=<DB-HOST-PORT>
   db.database=bludb
   user=<USERID>
   password=<PASSWORD>
   sslConnection=true
   Copy to clipboardCopied!

3. execute the following commands:

   java -jar schema/fhir-persistence-schema-*-cli.jar \
     --prop-file db2.properties --schema-name FHIRDATA --create-schemas
   java -jar schema/fhir-persistence-schema-*-cli.jar \
     --prop-file db2.properties --schema-name FHIRDATA --update-schema
   java -jar schema/fhir-persistence-schema-*-cli.jar \
     --prop-file db2.properties --schema-name FHIRDATA --grant-to FHIRSERVER
   java -jar schema/fhir-persistence-schema-*-cli.jar \
     --prop-file db2.properties --schema-name FHIRDATA --allocate-tenant default
   Copy to clipboardCopied!

4. save the tenantKey from the allocate-tenant step above; this is needed to configure the IBM FHIR Server datasource in the next step

Configuring an IBM FHIR Server datasource

        "persistence": {
            "datasources": {
                "default": {
                    "tenantKey": "myTenantKey",
                    "currentSchema": "${DB_SCHEMA}",
                    "hints" : {
                        "search.reopt": "ONCE"
                    },
                    "type": "db2",
Copy to clipboardCopied!

For IAM User

<server>
    <!-- ============================================================== -->
    <!-- TENANT: default; DSID: default; TYPE: read-write               -->
    <!-- ============================================================== -->
    <dataSource id="fhirDefaultDefault" jndiName="jdbc/fhir_default_default" type="javax.sql.XADataSource" statementCacheSize="200" syncQueryTimeoutWithTransactionTimeout="true" validationTimeout="30s" isolationLevel="TRANSACTION_READ_COMMITTED">
        <jdbcDriver javax.sql.XADataSource="com.ibm.db2.jcc.DB2XADataSource" libraryRef="sharedLibDb2"/>
        <properties.db2.jcc
            apiKey="${DB_APIKEY}"
            serverName="${DB_HOSTNAME}"
Copy to clipboardCopied!

For Db2 Auth user

<server>
    <!-- ============================================================== -->
    <!-- TENANT: default; DSID: default; TYPE: read-write               -->
    <!-- ============================================================== -->
    <dataSource id="fhirDefaultDefault" jndiName="jdbc/fhir_default_default" type="javax.sql.XADataSource" statementCacheSize="200" syncQueryTimeoutWithTransactionTimeout="true" validationTimeout="30s" isolationLevel="TRANSACTION_READ_COMMITTED">
        <jdbcDriver javax.sql.XADataSource="com.ibm.db2.jcc.DB2XADataSource" libraryRef="sharedLibDb2"/>
        <properties.db2.jcc
            serverName="${DB_HOSTNAME}"
            currentSchema="${DB_SCHEMA}"
Copy to clipboardCopied!

Mapping from IBM Db2 on Cloud endpoint credentials

• Reference the section Create the Administrator Credential to see an example.

Configuring a BulkData’s Liberty Datasource with API key

1. Create a Db2 user (e.g. FHIRBATCH)

2. Associate it with a ServiceId (no need to create an Administration user, a simple user has sufficient privileges) using the same procedure you followed for the fhir-server ServiceId user.

3. Create the datasource

   1. Db2 with IAM https://github.com/IBM/FHIR/blob/main/fhir-server-webapp/src/main/liberty/config/configDropins/disabled/db2-cloud/bulkdata.xml
   2. Db2 with Db2Auth https://github.com/IBM/FHIR/blob/main/fhir-server-webapp/src/main/liberty/config/configDropins/disabled/db2/bulkdata.xml

• Note: The Java Batch is configured in bulkdata.xml and included from the IBM FHIR Server’s server.xml which is installed to {wlp}/usr/server/defaultServer. (fhir-server is installed locally)
• Note: While this feature is not required, it’s best to configure this datasource while configuring the main datasource.

SSL Certificate

Encrypt Secrets

Appendix: Db2 Lite Plan

• The schema size is larger than the available space.
• The instance is not enabled with IAM, and you may use the Service Credentials that are created to connect to configure the datasource.

References

• Db2 on Cloud: Database details and connection credentials