Consume a web service with SQL on IBM i (DB2)

Consuming web services on IBM i was never easier – but in fact, it’s nothing new, the DB2 SQL functions are available at least since Version 7.2.

With Version 7.4 TR5 and Version 7.3 TR11 IBM has released new HTTP functions, which now reside in QSYS2 and which are much faster than the HTTP functions in SYSTOOLS. So I updated this post using the new functions in QSYS2.

Disclaimer: This is the first blog post that I’ve written in English – as I have many international contacts, and the IBM i community is not so large at all, I thought it would be a good idea to start writing in Englisch. But its is not my native language – I’m from Germany – so please forgive me any linguistic faux pas. Now, let’s dive in.

For our example, I will use a service from openweathermap.org, to retrieve a 5 day weather forecast for a given ZIP and country code. To get startet, you have to register and get your own API key – since we won’t gather large amounts of data, the free tier is OK.

Let’s start with the code:

This is a simple select – in line 2 the function JSON_TABLE is invoked – an it receives two „parameters“ – the first is the JSON document or expression, and the second „parameter“ is a JSON path expression and column definition. In fact the whole function call to JSON_TABLE ends in line 34 with the closing parentheses, just before the semicolon.

Now to the first parameter of JSON_TABLE – the JSON document or expression. It’s received from a call to the QSYS2.HTTP_GET function. QSYS2.HTTP_GET has also two parameters – the first is the URL and the second are the HTTP headers. We only need the URL.

The URL of the webservice basically looks like this:

http://api.openweathermap.org/data/2.5/forecast?zip={ZIP},{Country}&appid={API-Key}

The „zip“ parameter could look like „zip=91257,DE“ which is the city of Pegnitz in Germany. You could also add „units=metric“ to receive the data in metric units. And last but not least you need „appid=<…your.API.key…>“ which you get after you registered at the website.

Of course you can use „http://…“ and „https://…“ – can QSYS2.HTTP_GET use both protocols. But I had some problems using https on my test server pub400.com – so I changed the code to http. When using https and the server uses a self signed certificate (and not an „officially“ signed one) you may have to add the servers certificate to the local key store.

But now to the second parameter of JSON_TABLE – the JSON path expression and column definitions. Basically the first JSON path expression matches the „root“ of the document.

Here a shortened example of that JSON document, that we receive from the webservice.

The „$“ sign matches the current or selected structure in the JSON document – in this case the first structure level of curly braces { … }. The term „lax“ means „don’t take everything so seriously“ or more technically, that several types of structural error in the document are automatically resolved (Link).

The following „column“ keyword means „take the JSON key-value pairs of this level, and create column definitions. As with every column definition we need a name and a SQL data type. The special is the „path“ keyword with the following JSON path expression. This expression is always relative to the path expression before the column keyword.

The nested keyword means, that we have a nested structure with keys – often these are arrays with multiple occurrences of structures like the „list“ in our example. It works a bit like a JOIN – the occurrences of the nested structure are „joined“ with the values on the same level – you can see this in our example.

The „description“ column is selected from an array of values – as we only need the first entry, we can select it with an „[0]“ expression. But we always start with „lax $ …“ – that means, that we start in this case on the level of „list“.

The rest of the column definitions is pretty much self explaining. But just before the end we read „error on error“ – these keywords are following the column definitions, and it means, that the function will fail, if there is any error. If we do not specify this, we would receive a <NULL> value if an error occurs.

I hope this little example helps to get you started with the JSON_TABLE function.

Here is the code, ready to be copied – it’s compatible with Version 7.4 TR5 and Version 7.3 TR11 and upwards.

8 Gedanken zu „Consume a web service with SQL on IBM i (DB2)

  1. Hello, my name is Hugo, (I only speak Spanish),
    I found the sql very good, but it gives me this error
    could you help me please. what I understand from the error is that you have to do a conversion ..

    Message ID. . . . : CPF503E Gravity. . . . . . . : 30
    Type of message . . . : Diagnosis
    Send date . . . . . : 08/16/21 Shipping time. . . . . . : 11:41:04

    Message . . . . : Error in user-defined function on member
    QSQPTABL.
    Cause . . . . . : An error occurred while invoking the function defined by
    HTTPGETCLOB user from SYSTOOLS library. The error occurred while
    the associated external service program or program B2RESTUDF was invoked:
    SYSTOOLS.D library, program entry point, or external name
    com.ibm.db2.rest.DB2UDFWrapper.httpGetClob, specific name HTTPG00005. The
    Error occurred in QSQPTABL member library file QSQPTABL
    QSYS2. The error code is 1. The error codes and their meanings are:
    1 – The external service program or program returned SQLSTATE 57017.
    The text message returned from the program is: ███████ ¢.

    • Hi Hugo,

      SQL state 57017 is pointing to an CCSID error – very often in combination with CCSID 65535. Check your CCSID’s used.

      Do you use it directly in „Run SQL Scripts“, in STRSQL or embedded?

  2. Using this page as the example: https://qpgmr.de/ibmi-sql-json_table

    When using the following URL on a browser I do get data returned, however when using it in the code above I only get NULL’s

    https://api.openweathermap.org/data/2.5/weather?lat=51.5073&lon=-0.1277&appid={API key}

    {„coord“:{„lon“:-0.1276,“lat“:51.5073},“weather“:[{„id“:721,“main“:“Haze“,“description“:“haze“,“icon“:“50n“}],“base“:“stations“,“main“:{„temp“:274.6,“feels_like“:272.99,“temp_min“:271.53,“temp_max“:276.03,“pressure“:1037,“humidity“:91},“visibility“:4000,“wind“:{„speed“:1.54,“deg“:70},“clouds“:{„all“:7},“dt“:1675810846,“sys“:{„type“:2,“id“:2075535,“country“:“GB“,“sunrise“:1675754991,“sunset“:1675789167},“timezone“:0,“id“:2643743,“name“:“London“,“cod“:200}

    Is there something I am missing?

  3. I have a simple question. Our IBM i system cannot access internet / web directly. If so, the SQL with web services cannot access web data thru api, right?

    Thanks for your help !

    • Hi Frank,

      yes – of course, if your system can’t connect, you can’t retrieve any information from a web service.

      But „outgoing“ connections through a properly configured firewall shouldn’t be a problem. If necessary you can limit the addresses/domains where the machine can connect, to the absolutely necessary.

      Another solution could be a proxy server – but some web services don’t play nice with proxied connections – especially when some authentication is involved – but simple ones (with API token authorization) shouldn’t work.

      But you have to speak to your network and firewall admin to make some adjustments if you want to use web APIs from your machine.

      HTH
      Daniel

  4. Hi Daniel,

    Thanks for your feedback promptly!

    Our IBM i system OS version is 7.3 now. Do we need to setup IWS server in order to allow web services/API to get data? It’s because I saw some articles that need it for web services/api but it seems it’s used in former OS versions. Is it necessary?

    Except firewall setting, do we need to change any configuration in IBM i to allow internet connection?
    (note: you may share some relevant web links for my study in case)

    Thanks for your help and support!

    • AFAIK nothing special – as long as TCP/IP and DNS is configured correctly, it should work. AFAIK also no need for IWS – but at 7.3 it’s possible, that you need Java installed – this depends on the TR that you have installed.

Schreibe einen Kommentar zu QPGMR Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert