DB2 v11.5.4 REST API – Bonus Blog

Bonus Blog – Create an iOS Shortcut to call a REST API Service

Enabling clients to interact via HTTP GET/POST requests the REST API functionality provides clients a lightweight, modern interface to data stored in DB2 databases. In this series we looked at how to get started with the REST API from the DBA’s perspective, this blog shows how to call a DB2 REST service using an iOS shortcut – making DB2 voice controlled via Siri.

What is a Shortcut

The Shortcuts app on iOS allows you to chain together a set of actions along with logic into a workflow to complete a task. Shortcuts can interact with services and apps on the device, and be as simple as calculate a tip, or involve various actions to suggest a restaurant halfway between you and a friend. In the IBM demonstration webinars for DB2 11.5.4 there were examples of using the ability to run a shortcut to interact with DB2 via the new REST API and I knew that was something I’d want to do – have the ability to query a database via a Siri voice command on iPad, iPhone or even Apple Watch.

Network & Certificate Considerations

The first thing to note is that the device running the shortcut needs to be able to access the DB2 REST API server – so usually a corporate device or a device that can connect via VPN is going to be required. The second consideration is that by default the DB2 REST API services are accessed via HTTPS, and that means the server needs a certificate that the client can trust. Unfortunately the default certificate used by the REST service was not trusted by iOS, and there is no simple way to tell iOS to ignore the certificate error. In a real-world use case you would want to configure a certificate that the client devices can trust, but for me I wanted a quick way to produce a proof of concept so I bypassed the issue by installing a HTTP to HTTPS proxy on the server hosting the DB2 REST API container using NGINX:

Once running the NGINX proxy will redirect HTTP traffic on port 50049 to HTTPS traffic on port 50050, allowing unsecured connections and hiding the certificate trust error. Note that this may pass your data insecurely between the client and proxy so is only for testing or secure networks.

Getting an Authentication Token

The first thing our shortcut needs to do is get an authentication token. In the earlier blog entries the URL for the rest server was assigned to a variable and we created a dictionary object containing the database connection parameters, and that is how the iOS shortcut starts as well:

In the dictionary object the expiryTime entry is a simple text field but the dbParms dictionary entry is a dictionary itself, containing the parameters as follows. Note that all the fields here are text with the exception of dbPort (number) and isSSLConnection (boolean):

With the connection parameters set we can build the URL using the DB2RestURL variable and appending the ‘/v1/auth’ string – that URL is then passed in to a network action ‘Get contents of URL’ where the method is set to post and the dictionary object is set as the body. The network action is where the call to the DB2 REST API is made, which results in an output containing the JSON response. As we only want the authentication token from the response, we add a scripting action that extracts the token value which will allow us to reference it later:

Calling a Service

Now the shortcut has authenticated and has a valid token we can call a service. For this demonstration we’ll use the tripstats service created in the second blog in this series. First step is to use our DB2RestURL variable to build a URL with the service name and version, then we pass that into another network action which makes the REST call. This time we add the token from the previous steps into the header to authenticate, and set the body to pass the @pdate parameter:

The parameters part of the body is a dictionary again, but this time rather than create a separate dictionary object and pass it in we create a JSON dictionary with a single text field directly in the network action:

This time, when the output from the network action is passed to the scripting action, instead of extracting a single value from the JSON it builds a dictionary object. We will use this object in subsequent steps to reference the individual components and output a human-friendly response – as a reminder this is what the output from the service call looks like as text:

And here are the steps to take the output and make it human friendly so it can be read out by the device. Each value in the output is referenced via the JSON path: resultSet, 1 (for first item in the array – we only have one as the query only returns one row), and then the key such as DAY which is what the columns are named in the query output. The majority of the output can be put into the speak action without modification – thankfully the AVG_TRIP_DIST value is not read out to the full precision – but the DATE field needed some extra processing to remove the unused time portion of the data. Extracting the DATE value separately allows us to choose the formatting when the variable is passed to the speak action. In the screenshot below the shortcut has been run so you can see the results:

And that is our shortcut complete. Updating the name to something meaningful is the last thing to do and then the shortcut can be executed from the home screen, shortcuts widget or even by just asking Siri!

Conclusion

Using the new DB2 REST API with iOS shortcuts shows how easy it can be to set up quick queries for diverse systems – implementing functionality that integrates DB2 into applications even on platforms that traditionally might not have been able to take advantage of native driver-based connectivity. Although this particular example isn’t all that useful outside of a technology demonstration, I can see similar setups being handy for quick queries to, for example, report on batch processing queues or quickly check if the database backups finished successfully – there’s a lot more functionality that could be added such as having the shortcut ask which database you want to query, or giving you a trend based on previous figures. For now though I’m still marvelling at being able to just ask my phone to run a query for me…