Author: Paul

Question: How to use fhir-path-cli?

In IBM FHIR Server 4.7.0, the IBM FHIR Server team introduced fhir-path-cli and related to a blog on $everything.

Now, let’s download some sample data on the server using the Integration Test data.

Download the Sample Data

curl -L https://raw.githubusercontent.com/IBM/FHIR/main/fhir-server-test/src/test/resources/testdata/everything-operation/Antonia30_Acosta403.json -o Antonia30_Acosta403.json

Load the Sample Data bundle to the IBM FHIR Server

curl -k --location --request POST 'https://localhost:9443/fhir-server/api/v4' \
--header 'Content-Type: application/fhir+json' \
--header "Authorization: Basic ${DUMMY_PASSWORD}" \
--data-binary  "@Antonia30_Acosta403.json" -o response.json

Scan the response.json for any status that is not "status": "201". For example, the status is in the family of User Request Error or Server Side Error.
Check the response.json and find the id for Patient (it should be the first one).

        {
            "response": {
                "id": "178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003",
                "status": "201",
                "location": "Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/_history/1",
                "etag": "W/\"1\"",
                "lastModified": "2021-04-21T13:34:50.207684Z"
            }
        },

Check the Patient/[id]/$everything with a _type constraint and _since.

Request *

curl -k --location --request GET 'https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/$everything?_type=CarePlan,CareTeam&_since=2021-01-01T00:00:00Z&_count=1' \
    --header 'Content-Type: application/fhir+json' \
    --header "Authorization: Basic ${DUMMY_PASSWORD}" -o care.json

Response *

{
    "resourceType": "Bundle",
    "id": "689856f9-6ef6-4d74-8f77-a813ff7b1c6d",
    "type": "searchset",
    "total": 11,
    "entry": [
        {
            "fullUrl": "https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003",
            "resource": {
                "resourceType": "Patient",
                "id": "178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003",
                "meta": {
                    "versionId": "1",
                    "lastUpdated": "2021-04-21T13:34:50.207684Z"
                }
                ...
            }
        }
    ]
}

You’ll see further down CareTeam and CarePlan resources…

This is where we can have a bit of fun… download the fhir-path-cli

curl -L https://github.com/IBM/FHIR/releases/download/4.7.0/fhir-path-4.7.0-cli.jar -o fhir-path-4.7.0-cli.jar

Let’s run the fhir-path-4.7.0-cli.jar and test a FHIRPath.

Command Line

java -jar fhir-path-4.7.0-cli.jar --path 'entry.fullUrl' --file care.json

Output

https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CarePlan/178f4a3f86a-1a9a1d73-4d1d-46d5-8494-339dddbfe9d2
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CarePlan/178f4a3f86b-06c07a6c-3be7-4e6a-abb2-040ff67c8c1f
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CarePlan/178f4a3f873-d8069835-938e-45b1-9a70-5aba3794f6f3
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CarePlan/178f4a3f87c-ce204596-8037-416c-b2e0-fc7442d9a276
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CarePlan/178f4a3f87c-46785bc7-807a-40e0-bc08-792977ac8ec8
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CareTeam/178f4a3f86a-b79251c3-5019-426e-8211-748adbdfdf54
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CareTeam/178f4a3f86b-22a7baab-77b3-4fb5-b318-9249ea80ec85
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CareTeam/178f4a3f873-e370fba2-f45e-4cdd-b13b-781e249f84f8
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CareTeam/178f4a3f87c-596d80cc-7b42-4054-bc02-a48acfcc95b7
https://localhost:9443/fhir-server/api/v4/Patient/178f4a3f869-6abfe1e6-3a4a-4c9a-81a8-fb8c6263e003/CareTeam/178f4a3f87c-6cc197cd-b665-46e0-ad35-c016edeaf13b

You’ve seen a brief introduction to fhir-operation-everything and a bonus fhir-path-cli showing the output.

Note, DUMMY_PASSWORD should be replaced with or set as a value when the corresponding curl steps are executed.

Thanks to my colleague that implemented $everything.

2021-04-21

Question: How to use IBM FHIR Server Notifications with IBM Cloud Functions?

A question I had recently was “How to use IBM FHIR Server” with IBM Cloud Functions….

Here is the recipe:

1 – Create a topic FHIR_NOTIFICATION (for instance in EventStreams)

2 – Setup a IBM Cloud Function per https://github.com/IBM/ibm-cloud-functions-message-hub-trigger

3 – Update your process-message with the following code:

function main(params) {
  return new Promise((resolve, reject) => {
      console.log(params.messages);
    if (!params.messages || !params.messages[0] || !params.messages[0].value) {
      reject("Invalid arguments. Must include 'messages' JSON array with 'value' field");
    }
    const msgs = params.messages;
    const locations = [];
    for (let i = 0; i < msgs.length; i++) {
      const msg = msgs[i];
      console.log(msg.value.location);
      locations.push(msg.value.location);
    }
    resolve({
      locations,
    });
  });
}

exports.main = main;

exports.main = main;

4 – Invoke with parameters

    {
      "messages": [
        {
          "key": null,
          "offset": 0,
          "partition": 2,
          "topic": "FHIR_NOTIFICATION",
          "value": {
            "datasourceId": "default",
            "lastUpdated": "2021-04-16T13:55:13.312415Z",
            "location": "SearchParameter/178daf6d720-d2da7624-1664-4e50-a4f8-e558ab667fac/_history/1",
            "operationType": "create",
            "resource": {
              "base": [
                "Encounter"
              ],
              "code": "diff-start-end-time",
              "description": "Example of sophisiticated extraction",
              "expression": "iif(period.start.exists() and period.end.exists(), between(period.start , period.end, 'days'), {})",
              "id": "178daf6d720-d2da7624-1664-4e50-a4f8-e558ab667fac",
              "meta": {
                "lastUpdated": "2021-04-16T13:55:13.312415Z",
                "versionId": "1"
              },
              "name": "Example of sophisiticated extraction",
              "resourceType": "SearchParameter",
              "status": "draft",
              "type": "quantity",
              "url": "https://fhir.ibm.com/demo/diff-start-end-time"
            },
            "resourceId": "178daf6d720-d2da7624-1664-4e50-a4f8-e558ab667fac",
            "tenantId": "default"
          }
        }
      ]
    }

4 – Click Invoke

5 – Look to the Right to see the logging/activation (Expand the tick on the right-middle of the screen)

6 – You’ll see you now have a Function connected with EventStreams.

Notes

The activation dashboard is important https://cloud.ibm.com/functions/dashboard. You can see great detail on the logs and what is passed to the Function.

{
  "activationId": "960f129da9f04f138f129da9f0ef13f7",
  "annotations": [
    {
      "key": "path",
      "value": "14d753c7-8620-4caa-b6af-3934e33ae451/fhir-event-streams/process-message"
    },
    {
      "key": "waitTime",
      "value": 274
    },
    {
      "key": "kind",
      "value": "nodejs:12"
    },
    {
      "key": "timeout",
      "value": false
    },
    {
      "key": "limits",
      "value": {
        "concurrency": 1,
        "logs": 10,
        "memory": 256,
        "timeout": 60000
      }
    },
    {
      "key": "initTime",
      "value": 25
    }
  ],
  "duration": 32,
  "end": 1618583048738,
  "logs": [
    "2021-04-16T14:24:08.737951Z    stdout: [",
    "2021-04-16T14:24:08.737997Z    stdout: {",
    "2021-04-16T14:24:08.738031Z    stdout: key: null,",
    "2021-04-16T14:24:08.738037Z    stdout: offset: 0,",
    "2021-04-16T14:24:08.738042Z    stdout: partition: 2,",
    "2021-04-16T14:24:08.738047Z    stdout: topic: 'FHIR_NOTIFICATION',",
    "2021-04-16T14:24:08.738052Z    stdout: value: {",
    "2021-04-16T14:24:08.738057Z    stdout: datasourceId: 'default',",
    "2021-04-16T14:24:08.738062Z    stdout: lastUpdated: '2021-04-16T13:55:13.312415Z',",
    "2021-04-16T14:24:08.738066Z    stdout: location: 'SearchParameter/178daf6d720-d2da7624-1664-4e50-a4f8-e558ab667fac/_history/1',",
    "2021-04-16T14:24:08.738071Z    stdout: operationType: 'create',",
    "2021-04-16T14:24:08.738076Z    stdout: resource: [Object],",
    "2021-04-16T14:24:08.738081Z    stdout: resourceId: '178daf6d720-d2da7624-1664-4e50-a4f8-e558ab667fac',",
    "2021-04-16T14:24:08.738085Z    stdout: tenantId: 'default'",
    "2021-04-16T14:24:08.738134Z    stdout: }",
    "2021-04-16T14:24:08.738141Z    stdout: }",
    "2021-04-16T14:24:08.738146Z    stdout: ]",
    "2021-04-16T14:24:08.738152Z    stdout: SearchParameter/178daf6d720-d2da7624-1664-4e50-a4f8-e558ab667fac/_history/1"
  ],
  "name": "process-message",
  "namespace": "14d753c7-8620-4caa-b6af-3934e33ae451",
  "publish": false,
  "response": {
    "result": {
      "msgs": [
        {
          "key": null,
          "offset": 0,
          "partition": 2,
          "topic": "FHIR_NOTIFICATION",
          "value": {
            "datasourceId": "default",
            "lastUpdated": "2021-04-16T13:55:13.312415Z",
            "location": "SearchParameter/178daf6d720-d2da7624-1664-4e50-a4f8-e558ab667fac/_history/1",
            "operationType": "create",
            "resource": {
              "base": [
                "Encounter"
              ],
              "code": "diff-start-end-time",
              "description": "Example of sophisiticated extraction",
              "expression": "iif(period.start.exists() and period.end.exists(), between(period.start , period.end, 'days'), {})",
              "id": "178daf6d720-d2da7624-1664-4e50-a4f8-e558ab667fac",
              "meta": {
                "lastUpdated": "2021-04-16T13:55:13.312415Z",
                "versionId": "1"
              },
              "name": "Example of sophisiticated extraction",
              "resourceType": "SearchParameter",
              "status": "draft",
              "type": "quantity",
              "url": "https://fhir.ibm.com/demo/diff-start-end-time"
            },
            "resourceId": "178daf6d720-d2da7624-1664-4e50-a4f8-e558ab667fac",
            "tenantId": "default"
          }
        }
      ]
    },
    "size": 877,
    "status": "success",
    "success": true
  },
  "start": 1618583048706,
  "subject": "pb",
  "version": "0.0.1"
}

2021-04-16

htop and docker

I was recently introduced to htop – it generates a really nice UI to. see what’s going on.

- Install htop

yum install htop -y
...
Transaction test succeeded
Running transaction
  Installing : htop-2.2.0-3.el7.x86_64                                                                                          1/1
  Verifying  : htop-2.2.0-3.el7.x86_64                                                                                         1/1
Installed:
  htop.x86_64 0:2.2.0-3.el7
Complete!

2 – Type htop, and start diagnosing (iostats also helps)

2021-04-15

Question: How do I decode the Job Id from the Bulk Data Export or Import on the IBM FHIR Server?

Question: How do I decode the Job Id from the Bulk Data Export or Import on the IBM FHIR Server?

You can use the following code to walk through and decode your job id using your passowrd.

2021-04-15

Question: How do I configure fhir-notification Kafka so I don’t have to use a keystore?

The IBM FHIR Server provides an eventing service that notifies about persistence events – CUD (Create-Update-Delete). The notification service can trigger specific actions in a downstream application. You can configure these events to flow to Apache Kafka.

If you want to configure the IBM FHIR Server without a keystore, you can configure SASL_SSL like the below (notice there is no keystore specified).

{
    "fhirServer":{
        "notifications":{
            "kafka": {
                "enabled": true,
                "topicName": "FHIR_NOTIFICATIONS",
                "connectionProperties": {
                    "bootstrap.servers": "broker-1.mybroker.com:9093,broker-2.mybroker.com:9093,broker-0.mybroker.com:9093,broker-5.mybroker.com:9093,broker-4.mybroker.com:9093,broker-3.mybroker.com:9093",
                    "security.protocol": "SASL_SSL",
                    "sasl.mechanism": "PLAIN",
                    "ssl.protocol": "TLSv1.2",
                    "ssl.enabled.protocols": "TLSv1.2",
                    "ssl.endpoint.identification.algorithm": "HTTPS",
                    "security.inter.broker.protocol": "SSL",
                    "sasl.jaas.config": "org.apache.kafka.common.security.plain.PlainLoginModule required username=\"token\" password=\"MYPASSWORD\";",
                    "acks": "all",
                    "retries": "60",
                    "request.timeout.ms": "10000",
                    "max.block.ms": "60000",
                    "max.in.flight.requests.per.connection": "5",
                    "client.dns.lookup": "use_all_dns_ips"
                }
            }
        }
    }
}

If you run a workload against IBM FHIR Server, it’ll generate a set of notifications…

Download Kafka from https://kafka.apache.org
Create a client-ssl.properties

cat << EOF > client-ssl.properties
bootstrap.servers=broker-2.mybroker.com:9093,broker-5.mybroker.com:9093,broker-0.mybroker.com:9093,broker-3.mybroker.com:9093,broker-4.mybroker.com:9093,broker-1.mybroker.com:9093
sasl.jaas.config=org.apache.kafka.common.security.plain.PlainLoginModule required username="token" password="MYPASSWORD";
sasl.mechanism=PLAIN
security.protocol=SASL_SSL
ssl.protocol=TLSv1.2
EOF

Unzip the kafka archive
Check the Kafka Console Consumer

bash bin/kafka-console-consumer.sh --bootstrap-server broker-4.mybroker.com:9093,broker-5.mybroker.com:9093,broker-3.mybroker.com:9093,broker-2.mybroker.com:9093,broker-1.mybroker.com:9093,broker-0.mybroker.com:9093 --topic FHIR_NOTIFICATION --max-messages 25 --property print.timestamp=true --consumer.config client-ssl.properties

2021-04-15

Question: Can I validate a batch of FHIR resources?

Yes, you can use the Batch API.

The Bundle.entry must include a request object pointing at URL <RESOURCE>/$validate with method POST.
The Resource that is validated must be wrapped in a Parameters object and added to the Parameters.parameter.resource

{
    "resourceType": "Parameters",
    "parameter": [
        {
            "name": "resource",
            "resource": <COPY HERE>
        }
    ]
}

Embed it at Bundle.entry.resource, and make a request to the server root.

curl --location --request POST 'https://localhost:9443/fhir-server/api/v4' \
--header 'Authorization: Basic ...' \
--header 'Content-Type: application/json' \
--data-raw '{
    "resourceType": "Bundle",
    "type": "transaction",
    "entry": [
        {
            "fullUrl": "Patient/$validate",
            "resource": {
                "resourceType": "Parameters",
                "parameter": [
                    {
                        "name": "resource",
                        "resource": {
                            "resourceType": "Patient",
                            "id": "example",
                            "name": [
                                {
                                    "use": "official",
                                    "family": "Chalmers",
                                    "given": [
                                        "Peter",
                                        "James"
                                    ]
                                }
                            ]
                        }
                    }
                ],
                "meta": {
                    "tag": [
                        {
                            "system": "http://terminology.hl7.org/CodeSystem/v3-ActReason",
                            "code": "HTEST",
                            "display": "test health data"
                        }
                    ]
                }
            },
            "request": {
                "method": "POST",
                "url": "Patient/$validate"
            }
        }
    ]
}'

You’ll get a response like this, note the HTTP Status Code is 200 coming back… You have to look at Bundle.entry.resource.response.issue to check the severity and details to see why the validation is failing.

{
    "resourceType": "Bundle",
    "type": "transaction-response",
    "entry": [
        {
            "resource": {
                "resourceType": "OperationOutcome",
                "id": "NoError",
                "text": {
                    "status": "additional",
                    "div": "<div xmlns=\"http://www.w3.org/1999/xhtml\"><p>No error</p></div>"
                },
                "issue": [
                    {
                        "severity": "warning",
                        "code": "invariant",
                        "details": {
                            "text": "dom-6: A resource should have narrative for robust management"
                        },
                        "expression": [
                            "Patient"
                        ]
                    }
                ]
            },
            "response": {
                "status": "200",
                "outcome": {
                    "resourceType": "OperationOutcome",
                    "id": "NoError",
                    "text": {
                        "status": "additional",
                        "div": "<div xmlns=\"http://www.w3.org/1999/xhtml\"><p>No error</p></div>"
                    },
                    "issue": [
                        {
                            "severity": "warning",
                            "code": "invariant",
                            "details": {
                                "text": "dom-6: A resource should have narrative for robust management"
                            },
                            "expression": [
                                "Patient"
                            ]
                        }
                    ]
                }
            }
        }
    ]
}

You should add entries for each Resource you want to validate in the Batch.

2021-04-15

Docker Compose and Not Able to set a TTY in a Git Hub actions
In my GitHub Repository IBM FHIR Server, we use GitHub Actions to execute our Continuous Integration (CI) workflows. The CI workflows enable us to execute our code in complicated scenarios – a database (IBM Db2, Postgres), events system (Kafka (two zookeeper, two brokers), NATS) in a mix of various configurations.

I started to enable Docker Compose with Kafka and the IBM FHIR Server’s Audit module.

I ran into this error when running docker-compose exec in my workflow’s run.
```
TEST_CONFIGURATION: check that there is output and the configuration works
the input device is not a TTY
Error: Process completed with exit code 1.
```
It turns out this is well known in GitHub Actions https://github.com/actions/runner/issues/241#issuecomment-745902718 with a great example of a fix at https://github.com/gfx/example-github-actions-with-tty/blob/4c5f457c65dfe61e273e0470414699420be5e134/.github/workflows/test.yml#L18

I ended up changing my workflow, and it passed!

The key being in setting the shell value – shell: 'script -q -e -c "bash {0}"'
```
- name: Server Integration Tests - Audit
env:
WORKSPACE: ${{ github.workspace }}
shell: 'script -q -e -c "bash {0}"'
run: |
bash build/audit/bin/pre-integration-test.sh ${{matrix.audit}}
bash build/audit/bin/integration-test.sh ${{matrix.audit}}
bash build/audit/bin/post-integration-test.sh ${{matrix.audit}}
```
2021-01-04
IBM FHIR Server – Using the Docker Image with Near Feature and FHIR Examples from Jupyter Notebooks
Hi Everyone.

Thanks for sitting down and watching this video. I’m going to show you how to quickly spin up a Docker image of IBM FHIR Server, check the logs, make sure it’s healthy, and how to use the fhir-examples module with the near search.
The following are the directions followed in the video:

Navigate to DockerHub: IBM FHIR Server

Run the Server docker run -p9443:9443 ibmcom/ibm-fhir-server

Note, startup may take 2 minutes as the image is bootstrapping a new Apache Derby database in the image. To use Postgres or IBM Db2, please review the documentation.

Review the docker logs

Check the server is up and operational curl -k -i -u 'fhiruser:change-password' 'https://localhost:9443/fhir-server/api/v4/$healthcheck'

You now have a running IBM FHIR Sever.

Let’s load some data using a Jupyter Notebook.

The IBM FHIR Server team wraps specification and service unit tests into a module called fhir-examples and posts to Bintray: ibm-fhir-server-releases or go directly to the repository.

We’re going to use the python features and Jupyter Notebook to process the fhir-examples.

We’ll download the zip, filter the interesting jsons, and upload to the IBM FHIR Server in a loop.
```
entries = z.namelist()
for entry in entries:
if entry.startswith('json/ibm/bulk-data/location/'):
f = z.open(entry);
content = f.read()
r = requests.post('https://localhost:9443/fhir-server/api/v4/Location',
data=content,
headers=headers,
auth=httpAuth,
verify=False)
print('Done uploading - ' + entry)
```
We’re going to query the data on the IBM FHIR Server using the Search Query Parameter near to search within 10Km of Cambridge Massachusetts.
```
queryParams = {
'near': '42.373611|-71.110558|10|km',
"_count" : 200
}
```
Note, the IBM FHIR Server includes some additional search beyond the UCUM and WS48 units and it’s listed in at the Conformance page.

We’ll normalize this data and put in a Pandas dataframe.

From the dataframe, we can now add markers to the page.
```
cambridge = [ 42.373611, -71.11000]
map_cambridge_locs_from_server = folium.Map(location=cambridge, zoom_start=10)

# Iterate through the Rows
for location_row in location_rows :
# print(location_row)
# Cast the values into the appropriate types as FOLIUM will die weirdly without it.
lat_inc = float(location_row['resource.position.latitude'])
long_inc = float(location_row['resource.position.longitude'])
name_inc = str(location_row['resource.name'])
#print(lat_inc)
#print(long_inc)
#print(name_inc)
label = folium.Popup(name_inc, parse_html=True)
folium.CircleMarker(
[lat_inc, long_inc],
radius=5,
popup=label,
fill=True,
fill_color='red',
fill_opacity=0.7).add_to(map_cambridge_locs_from_server)
map_cambridge_locs_from_server
```
You can see the possibilities with the IBM FHIR Server and the near search.

Reference
- Notebook Link
- Notebook Link for FHIR from Jupyter
2020-11-23

jq fu for FHIR Capability Statements

The following takes the Capability Statements and generates the minimum coverage set of Resources across implementation guides based on the capability statements.

Resources and Profiles output in Markdown

This is handy to check the number of profiles in use (beyond the base spec) and to know the profiles used on the server based on the capability statements.

Call

cat capabilitystatements/*.json | jq -r '.rest[].resource[]| "|\(.type)|\(.supportedProfile)|"' | sort -u

Output

Resource	Profiles
AllergyIntolerance	http://hl7.org/fhir/us/core/StructureDefinition/us-core-allergyintolerance
CarePlan	http://hl7.org/fhir/us/core/StructureDefinition/us-core-careplan
CareTeam	http://hl7.org/fhir/us/core/StructureDefinition/us-core-careteam
Condition	http://hl7.org/fhir/us/core/StructureDefinition/us-core-condition
Device	http://hl7.org/fhir/us/core/StructureDefinition/us-core-implantable-device

Resource and Operations

To check the operations rrequired in the implementation guides, you can use the following to process a set of capability statements into a useful outputs.

Call

cat capabilitystatements/*.json | jq -r '.rest[].resource[]| "|\(.type)|\(.operation)|"' | grep -v null

Output

Resource	Operation	Conformance
ValueSet	$expand	SHOULD

I hope this helps.

2020-09-19

Getting Explain to work with IBM Db2 on Cloud

My team has been running more workloads on IBM Cloud, more specifically with IBM Db2. Our daily tools are slightly different to work with in the cloud, less administrative access and tools we can access on the host – db2batch, db2advis, db2expln and other native tools.

That’s when I ran across some great references that lead me in a direction that works for my team.

Create a User (with Password)
Catalog the Remote Database
Run db2expln

Click Open Console

Expand Settings

Click Manage Users

Click Add

Click Add User

Enter the relevant details for the user

Click Create

I use my db2 docker container

Setup the SSL

mkdir -p /database/config/db2inst1/SSL_CLIENT
chmod -R 755 /database/config/db2inst1/SSL_CLIENT
/database/config/db2inst1/sqllib/gskit/bin/gsk8capicmd_64 -keydb \
   -create -db "/database/config/db2inst1/SSL_CLIENT/ibmca.kdb" \
   -pw "passw0rd" -stash
/database/config/db2inst1/sqllib/gskit/bin/gsk8capicmd_64 -cert \ 
   -add -db "/database/config/db2inst1/SSL_CLIENT/ibmca.kdb" \ 
   -pw "passw0rd" -file sqllib/cfg/DigiCertGlobalRootCA.arm
chmod 775 /database/config/db2inst1/SSL_CLIENT/ibmca.kdb
chmod 775 /database/config/db2inst1/SSL_CLIENT/ibmca.sth

Configure the database

db2 update dbm cfg using SSL_CLNT_KEYDB \
   /database/config/db2inst1/SSL_CLIENT/ibmca.kdb
db2 update dbm cfg using SSL_CLNT_STASH 
   /database/config/db2inst1/SSL_CLIENT/ibmca.sth
db2 update dbm cfg using keystore_location 
   /database/config/db2inst1/SSL_CLIENT/ibmca.kdb

Restart the database

db2stop
db2start

Catalog the database

db2 catalog tcpip node cdtdb1 remote \
   dashdb-txn-flex-yp-xxxx-xxxx.services.dal.bluemix.net server 50001 security ssl
db2 catalog db bludb as fhirblu4 at node cdtdb1
db2 connect to fhirblu4 user testpaul using ^PASSWORD^

If you have a problem connecting, log out of db2inst1 and log back in. It’ll activate the db2profile again.

Run db2expln

db2expln -d fhirblu4 -u testpaul "^PASSWORD^" -graph -f 1.sql \
   -terminator ';' -o 1.out

Optimizer Plan:

Rows
Operator
(ID)
Cost

10
RETURN
( 1)
412211
|
10
TBSCAN
( 2)
412211
|
10
SORT
( 3)
412211
|
77909.3
HSJOIN
( 4)
412164
/------------------------/ \-----------------------\
88591.5 311638
TBSCAN TBSCAN
( 5) ( 8)
410438 1594.97
+---------------------------++--------------------------------+ |
354367 0.00309393 1 311638
Table: IXSCAN IXSCAN Table:
FHIRDATA2 ( 6) ( 7) FHIRDATA2
OBSERVATION_RESOURCES 7.52927 7.57425 OBSERVATION_LOGICAL_RESOURCES
| |
2.18146e+06 1.24649e+06
Index: Index:
FHIRDATA2 FHIRDATA2
IDX_OBSERVATION_TOKEN_VALUES_RPS IDX_OBSERVATION_STR_VALUES_RPS

Relevant References
https://www.ibm.com/cloud/blog/how-to-use-an-api-key-or-access-token-to-connect-to-ibm-db2-on-cloud
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/t0053518.html
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0070395.html
https://developer.ibm.com/recipes/tutorials/ssl-how-to-configure-it-on-db2/
https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/t0012036.html
https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.security.doc/doc/iam.html
https://developer.ibm.com/recipes/tutorials/ssl-how-to-configure-it-on-db2/#r_step8

Addendum

These are the containers settings for SSL:

db2inst1@4dda34a66a99 ~]$ db2 get dbm cfg | grep -i ssl
SSL server keydb file (SSL_SVR_KEYDB) = /database/config/db2inst1/SSL_CLIENT/ibmca.kdb
SSL server stash file (SSL_SVR_STASH) = /database/config/db2inst1/SSL_CLIENT/ibmca.sth
SSL server certificate label (SSL_SVR_LABEL) =
SSL service name (SSL_SVCENAME) =
SSL cipher specs (SSL_CIPHERSPECS) =
SSL versions (SSL_VERSIONS) =
SSL client keydb file (SSL_CLNT_KEYDB) = /database/config/db2inst1/SSL_CLIENT/ibmca.kdb
SSL client stash file (SSL_CLNT_STASH) = /database/config/db2inst1/SSL_CLIENT/ibmca.sth
Keystore location (KEYSTORE_LOCATION) = /database/config/db2inst1/SSL_CLIENT/ibmca.kdb

Db2 Top with remote db

db2top -d fhirpdm -n pdmperf -u bluadmin -p password-removed

Run with setup.sql

db2expln -d fhirdb -setup setup.sql -g -z \; -f uniq.sql -o plan.txt

2020-07-16