CA Certificate Update for the European Central Bank (ECB): A Practical Guide

Updating Java and DCM Certificates for ECB Exchange Rate Retrieval on IBM i

In our blog, we have previously discussed how to download exchange rates from the European Central Bank (ECB) using a simple SQL query with SYSTOOLS.HTTPGETCLOB, integrating it seamlessly into our ERP system. If you missed the article, here is the reference: How to Download ECB Exchange Rates with SQL.

Recently, however, the ECB has updated its services, making it necessary to update the CA certificates to continue receiving updated data. Depending on the method used for retrieving exchange rates, you will need to update either Java or the Digital Certificate Manager (DCM).

If You Use SYSTOOLS.HTTPGETBLOB (Java)

If your retrieval method relies on the SQL function SYSTOOLS.HTTPGETBLOB, you need to update the Java certificates to ensure a secure connection to ECB services. Here are the main steps:

Download the Updated CA Certificate

You can obtain the CA certificate directly from the ECB website:

1. Open the browser and visit: ECB Exchange Rates
3. Access the browser’s security/certificate management
5. Navigate to certificate details and download them to your PC

Add the Certificate to the Java Truststore

1. Upload the two certificates to your IFS directory, e.g., /mytempdir
2. Check the Java version in use and locate the Java Keystore directory in IFS (e.g., /QOpenSys/QIBM/ProdData/JavaVM/jdkXX/lib/security/cacerts).
3. Run the following command in an SSH session (PuTTY or QP2TERM/QSH) to check the Java version:java -version

java -version
java version "1.8.0_421"
Java(TM) SE Runtime Environment (build 8.0.8.30 - pap6480sr8fp30-20240801_01(SR8 FP30))
IBM J9 VM (build 2.9, JRE 1.8.0 OS/400 ppc64-64-Bit Compressed References 20240703_73934 (JIT enabled, AOT enabled)
OpenJ9   - 177ad469d4e
OMR      - e74814c
IBM      - 3c87141)
JCL - 20240731_02 based on Oracle jdk8u421-b09

1. If using Java 8 64-bit, the keystore path is:/QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/lib/security
2. Import the certificates using these commands:

keytool -import -file /mytempdir/SSL.comTLSECCRootCA2022.crt -alias CertAuth -keystore /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/lib/security/cacerts -storepass changeit -noprompt

keytool -import -file /mytempdir/EntrustOVTLSIssuingECCCA1.crt -alias CertAuth -keystore /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/lib/security/cacerts -storepass changeit -noprompt


If you get a warning about certificate yet installed, please ingnore it.

To verify:

keytool -list -keystore /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/lib/security/cacerts -storepass changeit

1. Restart your SQL connection and any Java-dependent ERP services.

Test the SQL Query for ECB Rates

SELECT * FROM (
  SELECT * FROM XMLTABLE (
    XMLNAMESPACES (
      DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref',
      'http://www.gesmes.org/xml/2002-08-01' AS "gesmes"
    ),
    'gesmes:Envelope/Cube/Cube/Cube' PASSING XMLPARSE ( DOCUMENT SYSTOOLS.HTTPGETBLOB(
      'https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml', ''
    ))
    COLUMNS 
      currency CHAR(3) PATH '@currency',
      rate REAL PATH '@rate',
      time DATE PATH '../@time'
  ) AS ExchangeRates
) x
WHERE CURRENCY = 'USD'
ORDER BY TIME DESC;

If You Use HTTP_GET (DCM)

For a few years now, HTTP_GET has been a more efficient alternative to HTTPGETCLOB, as it does not use Java and relies on DCM certificates for security.

Access the Digital Certificate Manager

1. Connect to the IBM Digital Certificate Manager (DCM) web interface:http://myibmip:2006/dcm/mainframe/system
2. Import the ECB CA certificates into the *SYSTEM store.
3. Follow the excellent step-by-step guide by Bradley Stone: Importing a CA Certificate

Test Your SQL Query with HTTP_GET

SELECT * FROM (
  SELECT * FROM XMLTABLE (
    XMLNAMESPACES (
      DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref',
      'http://www.gesmes.org/xml/2002-08-01' AS "gesmes"
    ),
    'gesmes:Envelope/Cube/Cube/Cube' PASSING XMLPARSE ( DOCUMENT (
      SELECT HTTP_GET(
        'https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml', ''
      ) FROM SYSIBM.SYSDUMMY1
    ))
    COLUMNS 
      currency CHAR(3) PATH '@currency',
      rate REAL PATH '@rate',
      time DATE PATH '../@time'
  ) AS ExchangeRates
) x
WHERE CURRENCY = 'USD'
ORDER BY TIME DESC;

Conclusion

Updating the certificates is a crucial step to ensure the continued operation of ECB exchange rate retrieval. If you use SYSTOOLS.HTTPGETBLOB, update the Java certificates; if you use HTTP_GET, update the Digital Certificate Manager.

This simple update will allow you to continue integrating ECB exchange rates into your ERP system without interruptions.

If you have any questions or would like to share your experience, leave a comment below!

--- Roberto De Pedrini Faq400.com