Setup for IBM Db2 on Cloud
Using Db2 for persistence
This document guides a developer or administrator through the steps necessary to setup and configure IBM Db2 on IBM Cloud as a persistence layer for the IBM FHIR Server.
Create a Db2 instance
Log in to your IBM Cloud account Link.
Click
Create resource
.Choose Db2.
Select the Pricing Plan -
Standard
orEnterprise
- Note, the IBM FHIR Server does not support using the Lite plan.
Create
Create
Your instance is now creating or created.
Scale the instance
Depending on the plan you choose, you may want to scale the instance after it has been created (e.g. 4 cores, 16GB). The instance can be scaled more than once, so it doesn’t matter if you don’t get the sizing right first time.
Note:
- The scaling of the instance requires a service restart.
- The instance CPU/Memory are scalable up and down. The storage in only scaled up.
Create the administrator credential
Navigate to the Db2 instance you created.
Click on the
Service credentials
panel.- If the list of service credentials is empty, create a new one.
- Click
New credential (+)
- Enter the Name. Any name works for example,
ibm-fhir-server-db2
. - Click Add
- Click
- If the list of service credentials is empty, create a new one.
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"
- Save these details for later, as these properties are needed to deploy and manage the IBM FHIR Server schema.
For improved security, the admin user should only be used to deploy the schema objects (tables, indexes, stored procedures etc) and administer the database, NOT for connecting from the IBM FHIR Server application.
Adding the FHIRSERVER user
Following the least-privilege principle, the IBM FHIR server itself recommends running as db user, not db administrator.
Using IAM apiKey
The IBM FHIR Server uses an API Key associated with an IAM Service Id. The IAM Service Id is mapped to a Db2 user which is granted explicit privileges to the tables and stored procedures.
The IBM FHIR Server uses the access flow:
Read API Key and the tenant key from the fhir-server-config.json
Connect to Db2 to access authenticated data using IAM
Confirm tenant-key to access authorized data
The steps to create the API key are:
On the IBM Cloud console, upper right corner, select
Manage
>Access (IAM)
.Select the Service IDs panel.
Click
Create (+)
- Enter a meaningful name, such as
fhir-service-id
. - Enter a description, such as
for instance db2-ho
. - 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.
- Enter a meaningful name, such as
Select the
API keys
tab.- Note: This tab is not the same
IBM Cloud API keys
on the left.
- Note: This tab is not the same
To create an API Key, select
Create (+)
.- Enter a name for the api key, for example,
fhir-server-api
. - Click
Create
. - 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.
- Enter a name for the api key, for example,
This API key is used in the database configuration section of the fhir-server-config.json
file.
Before the API key can be used, you need to create a Db2 user and associate it with the new ServiceId.
Navigate to the Resources page
Find and click on your IBM Db2 on Cloud instance.
Click on Manage.
Click on Open Console.
Click Administration > User Management
- To confirm the IAM instance:
- Click Run Sql (click create new if not brought into SQL edit session)
- Enter the SQLSELECT CASE WHEN VALUE = 'IBMIAMauth' THEN 1 ELSE 0 END AS IAM_ENABLED FROM SYSIBMADM.DBMCFG WHERE NAME = 'srvcon_gssplugin_list'
- You see
0
if not IAM enabled.
- To confirm the IAM instance:
Click Add. A panel opens on the right-hand side.
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
becomesserviceid
. 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.
Click
Create
.
You are now able to connect to the database as the FHIRSERVER user using only the API key created above.
Using Db2 Auth
On the Db2 Resource, click
Manage
Click
Open Console
Click Administration > User Management
Click Add User, and enter the user details with a sufficiently complex password. Set the
User Privilege
asUser
.Save these details for the datasource.xml.
Testing the connection
The Db2 driver (click here to download) is able to execute a connectivity test to check the configuration of the combo of API-key/Service-Id/Db2-User-Id.
Test IAM Access
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;"- 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.
- Note: Don’t forget the trailing
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 sectionNote: 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.
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.
Test Db2 Auth Access
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;"- 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.
- Note: Don’t forget the trailing
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
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.
Deploy the IBM FHIR Server schema
Now that you’ve created the database and credentials, use the fhir-persistence-schema
utility to deploy the IBM FHIR Server schema:
download the
fhir-persistence-schema
cli jar from the corresponding project release: https://github.com/IBM/FHIR/releasescreate 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=bludbuser=<USERID>password=<PASSWORD>sslConnection=trueexecute the following commands:
java -jar schema/fhir-persistence-schema-*-cli.jar \--prop-file db2.properties --schema-name FHIRDATA --create-schemasjava -jar schema/fhir-persistence-schema-*-cli.jar \--prop-file db2.properties --schema-name FHIRDATA --update-schemajava -jar schema/fhir-persistence-schema-*-cli.jar \--prop-file db2.properties --schema-name FHIRDATA --grant-to FHIRSERVERjava -jar schema/fhir-persistence-schema-*-cli.jar \--prop-file db2.properties --schema-name FHIRDATA --allocate-tenant defaultsave the tenantKey from the
allocate-tenant
step above; this is needed to configure the IBM FHIR Server datasource in the next step
For more information on using the fhir-persistence-schema cli jar, see https://github.com/IBM/FHIR/tree/main/fhir-persistence-schema/docs/SchemaToolUsageGuide.md.
Configuring an IBM FHIR Server datasource
The IBM FHIR Server uses the native Open Liberty datasources. To configure a FHIR tenant datasource for a tenantKey, use the following template in the fhir-server-config.json:
"persistence": {"datasources": {"default": {"tenantKey": "myTenantKey","currentSchema": "${DB_SCHEMA}","hints" : {"search.reopt": "ONCE"},"type": "db2",
Since release 4.3.2 you can use the search.reopt
query optimizer hint (shown above) to improve the performance of certain search queries involving multiple search parameters. This optimization is currently only available for Db2. Valid values are “ALWAYS” and “ONCE”. See Db2 documentation for REOPT
for more details.
To configure the datasource.xml for db2 create a datasource.xml for the configDropins folder. Note: CurrentSchema is case sensative to what you used in the fhir-persistance-schema tool used above.
For IAM User
Create a file as the following
<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.jccapiKey="${DB_APIKEY}"serverName="${DB_HOSTNAME}"
For Db2 Auth user
Create a file as the following:
<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.jccserverName="${DB_HOSTNAME}"currentSchema="${DB_SCHEMA}"
Each tenant datastore must have a corresponding dataSource definition and these dataSources must either follow the default jndiName pattern (jndi/fhir_[tenantid]_[dsid]
) or the name must be explicitly configured in the corresponding section of fhir-server-config.json
.
Mapping from IBM Db2 on Cloud endpoint credentials
This section explains how to populate the FHIR datasource from IBM Db2 on Cloud configuration details from an example configuration:
Use the following table to populate your datasource.
IBM FHIR Server Configuration | Description |
---|---|
serverName | from the credential object select hostname |
portNumber | from the credential object select port |
databaseName | from the credential object select db , generally always BLUDB |
apiKey | from the created user in the assigned to the fhiruser group. Reference Section FHIRSERVER User and API Key |
securityMechanism | If using IAM, set to 15 to trigger the use of IAM-based apiKey authentication |
pluginName | If using IAM, set to IBMIAMauth |
currentSchema | the schema name of your deployed tenant Schema, for instance FHIRDATA |
user | the userid |
password | the password for the user |
driverType | 4 , always JDBC Type-4 |
sslConnection | true , if you are using IBM Cloud |
sslTrustStoreLocation | Local server path to the truststore, resources/security/dbTruststore.jks |
sslTrustStorePassword | The password to the truststore |
- Reference the section Create the Administrator Credential to see an example.
Note, no username properties are given, because the authentication module only requires the API-KEY.
Configuring a BulkData’s Liberty Datasource with API key
The IBM FHIR Server Bulk Data modules utilize Java Batch (JSR-352) from the Liberty Profile feature - batch-1.0
. The batch feature is configured to use Db2 as follows:
Create a Db2 user (e.g. FHIRBATCH)
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.
Create the datasource
- Note: The Java Batch is configured in
bulkdata.xml
and included from the IBM FHIR Server’sserver.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
The Db2 certificate should be added to the Liberty Profile truststore. Be sure to use the same Java runtime that Liberty Profile uses when manipulating any keystores.
Encrypt Secrets
All passwords including apiKey values should be encrypted using the Liberty Profile securityUtility.
Appendix: Db2 Lite Plan
The Lite plan is not supported for development and evaluation.
- 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.