Pyxis Logo
Home / IBM Training / Technical article

“Using ADMIN_GET_TLS_CERT"

A guide to Db2 TLS that explains the key settings and what ADMIN_GET_TLS_CERT reveals, then confines the hands-on work to a reproducible Docker-based lab on a single Linux host.

30 min read
Published 2026-06-26
Pyxis editorial team
Rate this article
Average rating: Not rated
Your rating: Not rated
visualizations: 0
Technical illustration of Db2 TLS certificate-chain inspection and secure database connections

1. Why TLS matters in Db2

For many Db2 environments, TLS is no longer optional. It protects:

  • credentials during client authentication
  • application data while in transit
  • administrative connections on shared networks
  • replication and integration channels that cross trust boundaries

Without TLS, a Db2 TCP connection can expose usernames, passwords, and application traffic to anyone with network visibility. Even in internal networks, that is often unacceptable.

Db2 still uses many parameter names that contain SSL, but operationally the topic is now TLS. In practice, when a Db2 administrator says "enable SSL on Db2", the work is usually about configuring a TLS-capable listener, assigning the correct certificate label, and proving which certificate chain Db2 is actively using.

That last point used to be awkward. You could inspect keystores with GSKit tools and you could test connections from the client side, but there was no simple Db2 SQL interface that told you which certificate chain the server was actively serving.

That is exactly where ADMIN_GET_TLS_CERT helps. In Db2 12.1, the table function returns certificate-chain information for TLS usage contexts such as client/server connections, HADR, and KMIP. This makes certificate review, expiry checks, and operational validation much easier to integrate into normal SQL-based administration.

2. Where TLS appears in Db2

In Db2 environments, TLS often appears in more than one place:

  • CLIENT_SERVER for inbound client connections
  • HADR for communication between primary and standby
  • KMIP for communication with an external key manager

That distinction matters because ADMIN_GET_TLS_CERT reports the usage context explicitly, instead of treating every certificate as if it served the same purpose.

For normal client/server TLS, the instance-level settings that matter most are:

SettingPurpose
DB2COMMEnables the communication protocol. For TLS, SSL must be included.
SSL_SVCENAMEDefines the service name or TCP port used for the TLS listener.
SSL_SVR_KEYDBPoints to the server key database.
SSL_SVR_STASHPoints to the stash file for the key database password.
SSL_SVR_LABELIdentifies the server certificate label inside the key database.

For other TLS contexts, Db2 also uses labels such as:

  • HADR_SSL_LABEL
  • SSL_KMIP_CLIENT_CERTIFICATE_LABEL

3. What ADMIN_GET_TLS_CERT returns

IBM documents the function as:

ADMIN_GET_TLS_CERT(member, full_list)

Two parameters matter:

  • member
    • -1 means the current database member
    • -2 means all active members
  • full_list
    • 0 returns the endpoint or server certificate only
    • 1 returns the full chain, including endpoint, intermediate, and signer/root certificates

The returned columns are operationally useful because they cover not just the label, but also the shape and quality of the certificate:

  • USAGE
  • LABEL
  • CERT_TYPE
  • CERT_SIGNATURE_ALG
  • PUBKEY_TYPE
  • PUBKEY_SIZE
  • FINGERPRINT
  • SERIAL_NUMBER
  • NOT_BEFORE
  • NOT_AFTER
  • ISSUER_DN
  • SUBJECT_DN
  • SUBJECT_ALTERNATE_NAMES
  • KEYSTORE_LOCATION

That means you can use SQL to answer questions such as:

  • Which certificate chain is Db2 using for client/server TLS right now?
  • Does the endpoint certificate expire soon?
  • Is the server still using a weak key size or an outdated signature algorithm?
  • Are the SANs what we expect for the production hostname?
  • Which keystore location is serving the chain on a given member?

4. First inspection queries

Start with the client/server endpoint certificate only:

SELECT
  MEMBER,
  USAGE,
  LABEL,
  CERT_TYPE,
  NOT_BEFORE,
  NOT_AFTER,
  SUBJECT_DN,
  SUBJECT_ALTERNATE_NAMES
FROM TABLE(ADMIN_GET_TLS_CERT(-1, 0)) AS T
WHERE USAGE = 'CLIENT_SERVER';

This is the fastest way to confirm which endpoint certificate Db2 is using for inbound TLS connections.

Then inspect the full chain:

SELECT
  MEMBER,
  USAGE,
  LABEL,
  CERT_TYPE,
  ISSUER_DN,
  SUBJECT_DN,
  NOT_AFTER
FROM TABLE(ADMIN_GET_TLS_CERT(-1, 1)) AS T
WHERE USAGE = 'CLIENT_SERVER'
ORDER BY NOT_AFTER;

This makes expiry review much easier because you can immediately see whether the endpoint or a signer certificate is approaching its end date first.

5. Useful administration queries

Find certificates expiring soon:

SELECT
  USAGE,
  LABEL,
  CERT_TYPE,
  NOT_AFTER,
  DAYS(NOT_AFTER) - DAYS(CURRENT TIMESTAMP) AS DAYS_LEFT
FROM TABLE(ADMIN_GET_TLS_CERT(-1, 1)) AS T
WHERE NOT_AFTER < CURRENT TIMESTAMP + 90 DAYS
ORDER BY NOT_AFTER;

Check key sizes and signature algorithms:

SELECT
  USAGE,
  LABEL,
  CERT_TYPE,
  PUBKEY_TYPE,
  PUBKEY_SIZE,
  CERT_SIGNATURE_ALG
FROM TABLE(ADMIN_GET_TLS_CERT(-1, 1)) AS T
ORDER BY USAGE, CERT_TYPE, LABEL;

Check all active members:

SELECT
  MEMBER,
  USAGE,
  LABEL,
  CERT_TYPE,
  KEYSTORE_LOCATION,
  NOT_AFTER
FROM TABLE(ADMIN_GET_TLS_CERT(-2, 1)) AS T
ORDER BY MEMBER, USAGE, CERT_TYPE, LABEL;

6. HADR and KMIP visibility

Because USAGE identifies the TLS context, the same function can reveal whether Db2 is using different certificates for:

  • CLIENT_SERVER
  • HADR
  • KMIP

Example:

SELECT
  USAGE,
  LABEL,
  CERT_TYPE,
  SUBJECT_DN,
  NOT_AFTER
FROM TABLE(ADMIN_GET_TLS_CERT(-1, 1)) AS T
ORDER BY USAGE, CERT_TYPE, LABEL;

For HADR communication the function returns certificate-chain information only for the currently connected server. If you want to inspect both sides of an HADR pair, connect and run the query on each server rather than assuming the local output represents the remote partner as well.

7. Common mistakes this function helps expose

ADMIN_GET_TLS_CERT does not solve every TLS problem, but it removes a lot of ambiguity. It is particularly helpful when diagnosing these situations:

Wrong label configured

The keystore contains the correct certificate, but SSL_SVR_LABEL points to a different one. The function will show the active label and certificate subject immediately.

Incomplete chain

The endpoint certificate is present, but the required intermediate certificate is missing. With full_list = 1, you can see whether the expected signer chain is actually available.

Expiry surprises

Often the endpoint certificate is still valid while an intermediate signer is about to expire. Sorting by NOT_AFTER exposes that faster than manual keystore inspection.

SAN mismatch

Clients fail hostname validation because the certificate SANs do not match the hostname in the connection string. The SUBJECT_ALTERNATE_NAMES column helps you verify that directly from SQL.

Inconsistent member configuration

On multi-member systems, one member may have been updated while another was not. Querying with member = -2 makes drift visible.

8. A practical verification checklist

When you deploy or rotate a Db2 TLS certificate, the clean verification sequence is:

  1. Confirm the key database contains the expected label.
  2. Confirm Db2 DBM CFG points to the expected key database, stash file, listener, and label.
  3. Restart the instance if required by the change.
  4. Run one real TLS client connection test.
  5. Query ADMIN_GET_TLS_CERT(-1, 0) to confirm the endpoint certificate.
  6. Query ADMIN_GET_TLS_CERT(-1, 1) to confirm the full chain.
  7. Check NOT_AFTER, PUBKEY_SIZE, and SUBJECT_ALTERNATE_NAMES.
  8. In multi-member or HADR environments, repeat the validation on every relevant server.

That combination is much stronger than relying only on a connection test or only on keystore inspection.

The commands to do that work are deliberately left out of the theoretical part of this article. The hands-on material is confined to the Docker lab below.

9. A single-host Docker lab

The second half of this article turns the ideas above into a lab that can be executed with one Linux machine and the Db2 Community Edition container image.

The goal is deliberately narrow:

  • Start one Db2 container
  • Create a simple server certificate for lab use
  • Enable a TLS listener in Db2
  • Verify the listener from the Linux host
  • Inspect the active Db2 certificate with ADMIN_GET_TLS_CERT

This is not a production PKI design. It is a reproducible lab that makes the moving parts visible.

9.1 Prerequisites

You need:

  • A Linux machine
  • Docker Engine and the Docker Compose plugin
  • At least 8 GB RAM available for the lab
  • openssl

For Db2 12.1.5.0, there is one practical wrinkle: the stock container exposes gsk9certutil_64, but in some environments the ICU runtime needed by that tool is not immediately available. To keep the lab self-contained, we will build a small derived image that adds the required runtime libraries before we start Db2.

9.2 Create the lab directory

mkdir db2-tls-docker-lab
cd db2-tls-docker-lab
mkdir -p host-tls
mkdir -p host-tls/db2server
chmod 777 host-tls host-tls/db2server

Keep the lab files in this exact layout:

db2-tls-docker-lab/
|- Dockerfile
|- docker-compose.yml
`- host-tls/
   `- db2server/

That layout matters because the compose file uses a relative bind mount:

- ./host-tls:/host-tls

So docker-compose.yml must stay in the lab root, not inside host-tls/.

9.3 Build a small derived image

Pull the image explicitly first:

docker pull icr.io/db2_community/db2:12.1.5.0
docker images | grep db2_community

Launch vi

vi Dockerfile

and paste the following to create a Dockerfile in the lab directory:

FROM icr.io/db2_community/db2:12.1.5.0

USER root

RUN if command -v microdnf >/dev/null 2>&1; then \
      microdnf install -y libicu && microdnf clean all; \
    elif command -v dnf >/dev/null 2>&1; then \
      dnf install -y libicu && dnf clean all; \
    elif command -v yum >/dev/null 2>&1; then \
      yum install -y libicu && yum clean all; \
    else \
      echo "No supported package manager found in container image" >&2; \
      exit 1; \
    fi

Terminate the vi session, using :wq to write the file and quit.

Build the image:

docker build -t pyxis-db2-tls:12.1.5.0 .

9.4 Create docker-compose.yml

Execute the following code to create the file.

cat > docker-compose.yml <<'EOF'
services:
  db2tls:
    image: pyxis-db2-tls:12.1.5.0
    container_name: db2tls
    privileged: true
    hostname: db2tls
    environment:
      LICENSE: accept
      DB2INST1_PASSWORD: passw0rd
    ports:
      - "50000:50000"
      - "50001:50001"
    volumes:
      - db2tls_data:/database
      - ./host-tls:/host-tls
    healthcheck:
      test: ["CMD", "su", "-", "db2inst1", "-c", "db2pd -inst"]
      interval: 30s
      timeout: 10s
      retries: 15
      start_period: 600s

volumes:
  db2tls_data:
    name: db2tls_data
EOF

9.5 Start the container

docker compose up -d

If a previous db2tls container already exists and was created from an older image definition, force recreation:

docker compose up -d --force-recreate

Check container state:

docker ps

Db2 initialization takes time on first boot. Keep repeating the command until the container shows up as healthy.

Run a simple instance check:

docker exec -it db2tls su - db2inst1 -c "db2level"

Create a small database for the lab:

docker exec -it db2tls su - db2inst1 -c "db2 create database TLSLAB"

9.6 Create a self-signed server certificate

The TLS working directory was already created on the host before the container started:

For Db2 12.1.5.0, use the explicit GSKit 9 binary and runtime paths:

  • binary: /opt/ibm/db2/V12.1/gskit/bin/gsk9certutil_64
  • library path: /opt/ibm/db2/V12.1/lib64/gskit_db2

Export those values before using the GSKit CLI. Check that it is working by requesting the help function:

docker exec -it db2tls bash -lc '
export PATH=/opt/ibm/db2/V12.1/gskit/bin:$PATH
export LD_LIBRARY_PATH=/opt/ibm/db2/V12.1/lib64/gskit_db2:$LD_LIBRARY_PATH
gsk9certutil_64 -help
'

Create the key database and stash file:

docker exec -it db2tls bash -lc '
export PATH=/opt/ibm/db2/V12.1/gskit/bin:$PATH
export LD_LIBRARY_PATH=/opt/ibm/db2/V12.1/lib64/gskit_db2:$LD_LIBRARY_PATH
gsk9certutil_64 -keydb -create \
  -db /host-tls/db2server/server.kdb \
  -pw Db2Tls123! \
  -stash
'

Create a self-signed server certificate in that key database:

docker exec -it db2tls bash -lc '
export PATH=/opt/ibm/db2/V12.1/gskit/bin:$PATH
export LD_LIBRARY_PATH=/opt/ibm/db2/V12.1/lib64/gskit_db2:$LD_LIBRARY_PATH
gsk9certutil_64 -cert -create \
  -db /host-tls/db2server/server.kdb \
  -pw Db2Tls123! \
  -label db2server \
  -dn "CN=db2tls,O=Pyxis,C=PT" \
  -default_cert yes \
  -expire 365 \
  -size 2048 \
  -sig_alg SHA256WithRSA
'

Confirm the label exists:

docker exec -it db2tls bash -lc '
export PATH=/opt/ibm/db2/V12.1/gskit/bin:$PATH
export LD_LIBRARY_PATH=/opt/ibm/db2/V12.1/lib64/gskit_db2:$LD_LIBRARY_PATH
gsk9certutil_64 -cert -list \
  -db /host-tls/db2server/server.kdb \
  -pw Db2Tls123!
'

Because these commands run through docker exec ... bash -lc, the generated files can end up owned by root inside the container. Db2 starts the SSL listener as db2inst1, so fix the ownership before configuring TLS:

docker exec -it db2tls bash -lc '
chown db2inst1:db2iadm1 /host-tls/db2server/server.*
chmod 600 /host-tls/db2server/server.*
ls -l /host-tls/db2server
'

9.7 Configure Db2 for TLS in the container

Enable the SSL/TLS protocol at the instance level:

docker exec -it db2tls su - db2inst1 -c "db2set DB2COMM=SSL"

Configure the TLS listener and certificate settings:

docker exec -it db2tls su - db2inst1 -c "db2 update dbm cfg using SSL_SVCENAME 50001"
docker exec -it db2tls su - db2inst1 -c "db2 update dbm cfg using SSL_SVR_KEYDB /host-tls/db2server/server.kdb"
docker exec -it db2tls su - db2inst1 -c "db2 update dbm cfg using SSL_SVR_STASH /host-tls/db2server/server.sth"
docker exec -it db2tls su - db2inst1 -c "db2 update dbm cfg using SSL_SVR_LABEL db2server"

Restart the instance so the changes become active:

docker exec -it db2tls su - db2inst1 -c "db2stop force"
docker exec -it db2tls su - db2inst1 -c "db2start"

Confirm the active values:

docker exec -it db2tls su - db2inst1 -c '
db2 get dbm cfg | egrep -i "SSL_SVCENAME|SSL_SVR_KEYDB|SSL_SVR_STASH|SSL_SVR_LABEL"
'

docker exec -it db2tls su - db2inst1 -c "db2set -all | grep DB2COMM"

You should now see:

  • DB2COMM=SSL
  • SSL_SVCENAME set to 50001
  • the key database path under /host-tls/db2server
  • SSL_SVR_LABEL set to db2server

9.8 Verify the TLS listener from the Linux host

Use openssl s_client:

openssl s_client -connect localhost:50001 -showcerts </dev/null

What you want to see:

  • A completed TLS handshake
  • The certificate subject shown in the output
  • The certificate validity dates

Because this is a self-signed lab certificate, OpenSSL will normally complain about trust if you ask it to validate a public PKI chain. That is expected in this lab.

If openssl returns no peer certificate available and the handshake reads 0 bytes, the TCP port is reachable but Db2 is not actually presenting a TLS certificate yet. In that case, check these points in order:

  • Verify that the key database really contains the db2server label
  • Verify that the key database and stash files are present inside the container
  • Restart Db2 again after the certificate files exist
  • Confirm that Db2 is listening on the TLS port inside the container
  • Confirm that Docker is still publishing the port on the host

The practical commands are:

docker exec -it db2tls bash -lc '
export PATH=/opt/ibm/db2/V12.1/gskit/bin:$PATH
export LD_LIBRARY_PATH=/opt/ibm/db2/V12.1/lib64/gskit_db2:$LD_LIBRARY_PATH
gsk9certutil_64 -cert -list \
  -db /host-tls/db2server/server.kdb \
  -pw Db2Tls123!
'

docker exec -it db2tls bash -lc 'ls -l /host-tls/db2server'

docker exec -it db2tls su - db2inst1 -c "db2stop force"
docker exec -it db2tls su - db2inst1 -c "db2start"

docker exec -it db2tls bash -lc 'ss -ltnp | grep 50001 || netstat -ltn 2>/dev/null | grep 50001'

docker port db2tls

If the Db2 manager configuration already shows:

  • DB2COMM=SSL
  • SSL_SVCENAME=50001
  • SSL_SVR_KEYDB=/host-tls/db2server/server.kdb
  • SSL_SVR_STASH=/host-tls/db2server/server.sth
  • SSL_SVR_LABEL=db2server

then the remaining fault domain is usually one of these:

  • The key database was created without the expected label
  • The key database or stash file is missing from the mounted directory
  • The key database or stash file is still owned by root and unreadable by db2inst1
  • The instance was restarted before the files existed, and not restarted again afterward
  • Db2 failed to bind the TLS listener even though the configuration values were accepted

9.9 Use ADMIN_GET_TLS_CERT inside the lab

Start with the endpoint certificate only:

docker exec -it db2tls su - db2inst1 -c '
db2 connect to TLSLAB &&
db2 -x "SELECT
          '\''member='\'' || RTRIM(CHAR(MEMBER)) ||
          '\'' | usage='\'' || USAGE ||
          '\'' | label='\'' || LABEL ||
          '\'' | cert_type='\'' || CERT_TYPE ||
          '\'' | not_before='\'' || VARCHAR_FORMAT(NOT_BEFORE, '\''YYYY-MM-DD HH24:MI:SS'\'') ||
          '\'' | not_after='\'' || VARCHAR_FORMAT(NOT_AFTER, '\''YYYY-MM-DD HH24:MI:SS'\'') ||
          '\'' | subject='\'' || SUBJECT_DN ||
          '\'' | keystore='\'' || KEYSTORE_LOCATION
      FROM TABLE(ADMIN_GET_TLS_CERT(-1, 0)) AS T
      WHERE USAGE = '\''CLIENT_SERVER'\''" &&
db2 connect reset
'

For the full chain view, execute:

docker exec -it db2tls su - db2inst1 -c '
db2 connect to TLSLAB &&
db2 -x "SELECT
          '\''member='\'' || RTRIM(CHAR(MEMBER)) ||
          '\'' | usage='\'' || USAGE ||
          '\'' | label='\'' || LABEL ||
          '\'' | cert_type='\'' || CERT_TYPE ||
          '\'' | not_after='\'' || VARCHAR_FORMAT(NOT_AFTER, '\''YYYY-MM-DD HH24:MI:SS'\'') ||
          '\'' | issuer='\'' || ISSUER_DN ||
          '\'' | subject='\'' || SUBJECT_DN
      FROM TABLE(ADMIN_GET_TLS_CERT(-1, 1)) AS T
      WHERE USAGE = '\''CLIENT_SERVER'\''
      ORDER BY NOT_AFTER" &&
db2 connect reset
'

In this simple self-signed lab, the chain is expected to contain only one certificate: the self-signed endpoint certificate itself. That is fine. The point is to confirm that:

  • The function works
  • The usage is reported as CLIENT_SERVER
  • The label is the one configured in SSL_SVR_LABEL
  • The expiry timestamp is visible from SQL

Show certificate fingerprints:

docker exec -it db2tls su - db2inst1 -c '
db2 connect to TLSLAB &&
db2 -x "SELECT
          '\''label='\'' || LABEL ||
          '\'' | cert_type='\'' || CERT_TYPE ||
          '\'' | fingerprint='\'' || FINGERPRINT
      FROM TABLE(ADMIN_GET_TLS_CERT(-1, 1)) AS T
      WHERE USAGE = '\''CLIENT_SERVER'\''" &&
db2 connect reset
'

Show certificate remaining lifetime:

docker exec -it db2tls su - db2inst1 -c '
db2 connect to TLSLAB &&
db2 -x "SELECT
          '\''label='\'' || LABEL ||
          '\'' | cert_type='\'' || CERT_TYPE ||
          '\'' | not_after='\'' || VARCHAR_FORMAT(NOT_AFTER, '\''YYYY-MM-DD HH24:MI:SS'\'') ||
          '\'' | days_left='\'' || RTRIM(CHAR(DAYS(NOT_AFTER) - DAYS(CURRENT TIMESTAMP)))
      FROM TABLE(ADMIN_GET_TLS_CERT(-1, 1)) AS T
      WHERE USAGE = '\''CLIENT_SERVER'\''
      ORDER BY NOT_AFTER" &&
db2 connect reset
'

Show the key size, signature algorithm, and SANs in one query:

docker exec -it db2tls su - db2inst1 -c '
db2 connect to TLSLAB &&
db2 -x "SELECT
          '\''label='\'' || LABEL ||
          '\'' | cert_type='\'' || CERT_TYPE ||
          '\'' | key='\'' || PUBKEY_TYPE || '\''/'\'' || RTRIM(CHAR(PUBKEY_SIZE)) ||
          '\'' | sig_alg='\'' || CERT_SIGNATURE_ALG ||
          '\'' | sans='\'' || COALESCE(SUBJECT_ALTERNATE_NAMES, '\''<none>'\'')
      FROM TABLE(ADMIN_GET_TLS_CERT(-1, 1)) AS T
      WHERE USAGE = '\''CLIENT_SERVER'\''
      ORDER BY CERT_TYPE, LABEL" &&
db2 connect reset
'

Show all TLS usage contexts visible from the instance:

docker exec -it db2tls su - db2inst1 -c '
db2 connect to TLSLAB &&
db2 -x "SELECT
          '\''usage='\'' || USAGE ||
          '\'' | label='\'' || LABEL ||
          '\'' | cert_type='\'' || CERT_TYPE ||
          '\'' | not_after='\'' || VARCHAR_FORMAT(NOT_AFTER, '\''YYYY-MM-DD HH24:MI:SS'\'')
      FROM TABLE(ADMIN_GET_TLS_CERT(-1, 1)) AS T
      ORDER BY USAGE, CERT_TYPE, LABEL" &&
db2 connect reset
'

10. What this combined approach gives you

The theoretical part of this article explains what Db2 expects:

  • The correct listener
  • The correct keystore path
  • The correct stash file
  • The correct certificate label
  • The correct usage context in ADMIN_GET_TLS_CERT

The Docker lab proves those ideas end to end with a reproducible environment.

With only one Linux host and one Db2 container, you can validate the full operational sequence:

  1. Create a key database and server certificate
  2. Enable a TLS listener in Db2
  3. Confirm the listener from the host with OpenSSL
  4. Inspect the active certificate from inside Db2 with ADMIN_GET_TLS_CERT

That is enough to make several important Db2 TLS concepts concrete:

  • The difference between merely having a certificate file and actually binding it into Db2
  • The importance of SSL_SVR_LABEL
  • The usefulness of SQL-based certificate inspection after a change

11. Final remarks

Db2 TLS configuration is usually not hard, but it is easy to get subtly wrong. The common failure modes are operational mismatches:

  • The wrong label
  • The wrong hostname
  • The wrong signer chain
  • The wrong member
  • The wrong file ownership
  • The wrong assumption about what Db2 is actively serving

ADMIN_GET_TLS_CERT closes an important visibility gap because it lets you inspect the active certificate chain with SQL, using the same administrative tooling and workflows that Db2 teams already use for monitoring and validation.

If you already use TLS in Db2, this function is worth adding to your standard post-change checks. If you are about to introduce TLS, it is worth including from day one so that certificate validation becomes part of normal Db2 operations instead of a special troubleshooting exercise.

More in this area

More in this area

Back to training
Article category

Db2 LUW Articles

Browse all technical articles for Db2 LUW in one category page.

Open category Db2 LUW