Extending the MQ security certificate

You can extend the life of the IBM® MQ security certificate that is used for replication from the default three years to 100 years to ensure that the certificate does not expire and prevent replication from continuing.

Before you begin

  • It is advisable to perform this task during a maintenance window or during a time of lower database activity.
  • Make sure that all replication sets are active and the last consistency point is current.

About this task

Self-signed MQ SSL certificates are used internally to transport data from sources to targets. By default, the expiration date for the MQ certificate is three years to coincide with Db2® Warehouse SSL certificate. However, if the MQ certificate expires while replication is active, data in MQ queues is not recoverable and you would be required to perform a full refresh of all replication target tables.

The following procedure extends the life of the MQ certificate to prevent this situation from occurring.

Procedure

  1. Disable the Wolverine high-availability monitoring process on both the source and target: 
    wvcli system disable -m renew_mq_cert
  2. Stop all send queues on the source system by following these steps:
    1. Set the state of the send queues in the replication metadata to inactive (I) by using the following SQL statement: 
      update qasn.ibmqrep_sendqueues set state='I'
    2. Find the name of the capture program thread: 
      ps -ef|grep asnqcap
      In the following example, the thread (capture_server) name is FRKJKKDZ:
      dsadm    1576952 1576022  1 10:22 pts/3    00:01:08 /opt/ibm/db2/V11.5.0.0/bin/asnqcap capture_server=FRKJKKDZ capture_schema=QASN capture_path=/mnt/blumeta0/bludr/logs/replication xf_del_file=y qfull_retry_delay=60000 qfull_num_retries=1000 max_capstarts_intload=5 term=n use_stream_trans=y startallq=n
    3. Reinitialize the capture program to enable the send queue state change: 
      asnqccmd capture_server=FRKJKKDZ capture_schema=QASN reinit
  3. On the target system, verify that all receive queues are empty by following these steps:
    1. Find the name of the queue manager by running the MQ command dspmq.

      In this output, the queue manager name is AOMTDBXP_AQM:

      QMNAME(AOMTDBXP_AQM)      STATUS(Running)
    2. Run the following commands to check that the current depth (number of messages) in all receive queues is 0: 
      runmqc AOMTDBXP_AQM
DISPLAY QLOCAL(*) CURDEPTH

      The output shows CURDEPTH values of 0:

      20 : DISPLAY QLOCAL(*) CURDEPTH
AMQ8409I: Display Queue details.
   QUEUE(SET10001_RECVQ)                   TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_RECVQ_FT)                TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_RECVQ_FT.2)              TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_RECVQ_FT.3)              TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_RECVQ_FT.4)              TYPE(QLOCAL)
   CURDEPTH(0)
  4. On the source system, verify that the ADMINQ, ACKFT, and XMITQ queues are empty by following these steps:
    1. Find the name of the queue manager by running the IBM MQ command dspmq.

      In this output, the queue manager name is FRKJKKDZ_CQM:

      QMNAME(FRKJKKDZ_CQM)      STATUS(Running)
    2. Run the following commands to check that the current depth (number of messages) in all local queues is 0: 
      runmqsc FRKJKKDZ_CQM
DISPLAY QLOCAL(*) CURDEPTH

      The output shows CURDEPTH values of 0 for the ADMINQ, ACKFT, and XMITQ queues, as well as other local queues:

      1 : DISPLAY QLOCAL(*) CURDEPTH
AMQ8409I: Display Queue details.
   QUEUE(FRKJKKDZ_ADMINQ)                  TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_ACKFT)                   TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_TXQ)                     TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_TXQ_FT)                  TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_TXQ_FT.2)                TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_TXQ_FT.3)                TYPE(QLOCAL)
   CURDEPTH(0)
AMQ8409I: Display Queue details.
   QUEUE(SET10001_TXQ_FT.4)                TYPE(QLOCAL)
   CURDEPTH(0)
  5. Stop the capture program at the source database
    1. Stop the capture process: 
      asnqccmd capture_server=FRKJKKDZ capture_schema=QASN stop
    2. Verify that the asnqcap thread no longer exists: 
      ps -ef|grep asnqcap
  6. Stop the apply program at the target database by following these steps.
    1. Find the name of the apply server: 
      ps -ef|grep asnqapp
      In the following example, the server name is AOMTDBXP:
      dsadm    1614543 1614088  0 10:28 pts/3    00:00:18 /opt/ibm/db2/V11.5.0.0/bin/asnqapp apply_server=AOMTDBXP apply_schema=QASN apply_path=/mnt/blumeta0/bludr/logs/replication CDE_FILE_MISSING_TIMEOUT=600 term=n et_load_named_pipe_group_id=3000 startallq=y
    2. Stop the apply process: 
      asnqacmd apply_server=AOMTDBXP apply_schema=QASN stop
    3. Verify that the asnqapp thread no longer exists: 
      ps -ef|grep asnqapp
  7. Create a shell script file with the following content and save it as bludr-mq-renew-cert.sh. 
    #/bin/bash

DECRYPT_PW=$(/opt/ibm/dsserver/scripts/decrypt.sh $(cat ~dsadm/.mqs/kdb.pass))
DIST_NAME=`grep "MQ_DIST_NAME" /opt/ibm/apiserver/wlp/usr/servers/bludr/server.env`
DIST_NAME=${DIST_NAME#*=}
CN_NAME=`grep "MQ_CN_NAME" /opt/ibm/apiserver/wlp/usr/servers/bludr/server.env`
CN_NAME=${CN_NAME#*=}
GSKIT_CMD="$(ls -1d /opt/ibm/db2/V*)/gskit/bin/gsk8capicmd_64"

openssl x509 -in ${BLUDR_SHARED_DIR}/certificates/mq_public_cert.arm -text -noout | head -n 10 > ~dsadm/openssl.backup.txt
cat ~dsadm/openssl.backup.txt
cp -r ~dsadm/.mqs ~dsadm/.mqsbackup
rm -f ~dsadm/.mqs/dsadm*

${GSKIT_CMD} -keydb -create -db ~/.mqs/dsadmkey.kdb -pw ${DECRYPT_PW} -stash
${GSKIT_CMD} -cert -create -db ~dsadm/.mqs/dsadmkey.kdb -pw ${DECRYPT_PW} -expire 36500 -label ${CN_NAME} -dn ${DIST_NAME} -default_cert yes
mv ${BLUDR_SHARED_DIR}/certificates/mq_public_cert.arm ${BLUDR_SHARED_DIR}/certificates/mq_public_cert.arm.backup

${GSKIT_CMD} -cert -extract -db ~/.mqs/dsadmkey.kdb -pw ${DECRYPT_PW} -label ${CN_NAME} -target ${BLUDR_SHARED_DIR}/certificates/mq_public_cert.arm

openssl x509 -in  ${BLUDR_SHARED_DIR}/certificates/mq_public_cert.arm -text -noout | head -n 10
  8. Run the bludr-mq-renew-cert.sh script on both the source and target. In the output, check the second Validity section and verify that the the Not After value is about 100 years (2121 or later). 
    $ ./bludr-mq-renew-cert.sh
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 3686575169745515400 (0x33295895a674f388)
    Signature Algorithm: sha256WithRSAEncryption
        Issuer: C=US, O=IBM, CN=4818ff96-c9ec-4316-be2a-78ac79d2838d
        Validity
            Not Before: Dec  2 08:20:43 2021 GMT
            Not After : Dec  2 08:20:43 2024 GMT
        Subject: C=US, O=IBM, CN=4818ff96-c9ec-4316-be2a-78ac79d2838d
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 3382945298641481939 (0x2ef2a2d3541a70d3)
    Signature Algorithm: sha256WithRSAEncryption
        Issuer: C=US, O=IBM, CN=4818ff96-c9ec-4316-be2a-78ac79d2838d
        Validity
            Not Before: Dec  4 11:50:32 2021 GMT
            Not After : Nov 11 11:50:32 2121 GMT
        Subject: C=US, O=IBM, CN=4818ff96-c9ec-4316-be2a-78ac79d2838d
  9. On the source system, run the bludr-trust-targets.sh script: 
    /opt/ibm/bludr/scripts/bin/bludr-trust-targets.sh add 9.30.43.143 bluadmin bluadmin 9.30.161.62 9444
  10. Run the bludr-restart.sh script on both source and target to restart the capture and apply programs. 
    /opt/ibm/bludr/scripts/bin/bludr-restart.sh
  11. Run the bludr-status.sh script on both source and target to verify that the replication REST server is active. 
    /opt/ibm/bludr/scripts/bin/bludr-status.sh
  12. Start all send queues on the source system by following these steps:
    1. Set the state of the send queues in the replication metadata to active (A) by using the following SQL statement: 
      update qasn.ibmqrep_sendqueues set state='A'
    2. Reinitialize the capture program to enable the send queue state change: 
      asnqccmd capture_server=FRKJKKDZ capture_schema=QASN reinit
  13. Enable Wolverine on both the source and target: 
    wvcli system enable -m renew_mq_cert_complete