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.

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 SYSTOOLS.HTTPGETCLOB function. HTTPGETCLOB 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://…“ – SYSTOOLS.HTTPGETCLOB can use both protocols. Only if the server uses a self signed certificate (and not an „officially“ signed one) you 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.

  1. {
  2.   "cod":"200",
  3.   "message":0,
  4.   "cnt":40,
  5.   "list":[
  6.     {
  7.       "dt":1629028800,
  8.       "main"{
  9.         "temp":28,
  10.         "feels_like":27.53,
  11.         "temp_min":27.49,
  12.         "temp_max":28,
  13.         "pressure":1014,
  14.         "sea_level":1014,
  15.         "grnd_level":964,
  16.         "humidity":38,
  17.         "temp_kf":0.51
  18.       },
  19.       "weather":[
  20.         {
  21.           "id":801,
  22.           "main":"Clouds",
  23.           "description":"few clouds",
  24.           "icon":"02d"
  25.         }
  26.       ],
  27.       "clouds":{
  28.         "all":12
  29.       },
  30.       "wind":{
  31.         "speed":3,
  32.         "deg":266,
  33.         "gust":4.89
  34.       },
  35.       "visibility":10000,
  36.       "pop":0,
  37.       "sys":{
  38.         "pod":"d"
  39.       },
  40.       "dt_txt":"2021-08-15 12:00:00"
  41.     },
  42.     {....}
  43.   ],
  44.   "city":{
  45.     "id":0,
  46.     "name":"Pegnitz",
  47.     "coord":{
  48.       "lat":49.7512,
  49.       "lon":11.5395
  50.     },
  51.     "country":"DE",
  52.     "population":0,
  53.     "timezone":7200,
  54.     "sunrise":1629000262,
  55.     "sunset":1629052351
  56.   }
  57. }

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.

  1. SELECT *
  2. FROM json_table(
  3.   systools.httpgetclob('https://api.openweathermap.org/data/2.5/forecast?zip={..ZIP..},{..COUNTRY..}&units=metric&appid={..API-KEY..}', '')
  4.   ,
  5.   'lax $' COLUMNS(
  6.     nested '$.list' COLUMNS (
  7.       description VARCHAR(255) path 'lax $.weather[0].description',
  8.       cloud_percent NUMERIC(3, 0) path 'lax $.clouds.all',
  9.       temp NUMERIC(5, 2) path 'lax $.main.temp',
  10.       temp_feel NUMERIC(5, 2) path 'lax $.main.feels_like',
  11.       temp_min NUMERIC(5, 2) path 'lax $.main.temp_min',
  12.       temp_max NUMERIC(5, 2) path 'lax $.main.temp_max',
  13.       pressure NUMERIC(5, 0) path 'lax $.main.pressure',
  14.       humidity NUMERIC(3, 0) path 'lax $.main.humidity',
  15.       visibility NUMERIC(7, 0) path 'lax $.visibility',
  16.       wind_speed NUMERIC(3, 1) path 'lax $.wind.speed',
  17.       wind_degr NUMERIC(3, 0) path 'lax $.wind.deg',
  18.       rain1h NUMERIC(5, 0) path 'lax $.rain.1h',
  19.       rain3h NUMERIC(5, 0) path 'lax $.rain.3h',
  20.       snow1h NUMERIC(5, 0) path 'lax $.snow.1h',
  21.       snow3h NUMERIC(5, 0) path 'lax $.snow.3h',
  22.       probability NUMERIC(3, 2) path 'lax $.pop',
  23.       reported BIGINT path 'lax $.dt'
  24.     ),
  25.     sunrise BIGINT path 'lax $.city.sunrise',
  26.     sunset BIGINT path 'lax $.city.sunset',
  27.     utc_offset BIGINT path 'lax $.city.timezone',
  28.     lat NUMERIC(6, 2) path 'lax $.city.coord.lat',
  29.     lon NUMERIC(6, 2) path 'lax $.city.coord.lon',
  30.     country CHAR(2) path 'lax $.city.country',
  31.     city VARCHAR(255) path 'lax $.city.name'
  32.   )
  33.   error ON error
  34. );

3 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?

Schreibe einen Kommentar zu QPGMR Antworten abbrechen

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