2020 Posted by James Cockayne

DB2 v11.5.4 REST API – Part Three

Part Three – Update or Remove a 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 look at how to get started with the REST API from the DBA’s perspective, in part three we look at how to update a service or remove it altogether.


Understanding Versioning

In part two of this series we created a REST service called tripstats with a version of 1.0, and subsequently referred to the version in all the calls to the service URL which looked like this: http://localhost:50049/v1/services/tripstats/1.0.

Over time as requirements change our service may require updating as well.  If any changes we make are ‘behind the scenes’ and the app doesn’t see them there is no need to update the version of the service (although you may want to, for example if the client app records performance data for the service calls it might make sense to bump the version so any differences are clear) – by incrementing the version for any breaking change made to our service however we can ensure updated clients are returned the data they expect in a format they can understand, while maintaining older versions of the service for clients that have not updated.  This is great news for applications that can take advantage of blue green deployment and allows us to gradually roll out new features without impacting users.


Create a New Version of a Service

This task is pretty simple as you can run exactly the same script to create another copy of the service, just update the version number as desired.  A new stored procedure is created in the database with the version number:

new stored procedure database

And the services table is updated:

service table rest api

From the database perspective the versioning appears to effectively result in a new independent service.  So far it appears the version number must be specified when calling a service so there is no functionality to, for example, define a default.


Updating an Existing Service

There are a couple of options to update an existing service.  The one I’d probably go for would be to drop it and recreate – there is no ‘create or replace’ functionality unfortunately. There is a specific option to patch an existing service, however this comes with limitations in that only the SQL or description can be updated.  Moreover, the SQL cannot change between select, insert update and delete, and the parameters cannot change either.  For completeness’ sake, here’s an example script in case the patch functionality proves to be useful:

This script updates the description of the service we created in part two of the blog.  Note the service name and version to patch are specified in the URL – you cannot patch and increment the version using this method.


Removing a Service

There seems to be something of a tradition in the database world of making the most dangerous commands the simplest and easiest to remember, and the REST API service is no different.  To delete a service no JSON body is required, just pass the headers as usual to the URL containing the service and version, and use the HTTP delete method:

There is not much to see when the service is deleted – the entries in the RESTSERVICE table are removed and the stored procedure containing the code is dropped.  Again, there is no relationship between versions of the same service in the database so other versions of the same service are not affected.



Updating and removing a service worked as expected with no surprises, which is a good thing.  I’m still not sure that the ability to patch an existing service adds anything over dropping and recreating it, permissions being retained is useful I guess but the limits on what can change seem too restrictive – perhaps more functionality will come along later.  The ability to implement versioning of the services in the database is a great feature which can provide flexibility for deployments of new functionality as older versions of the services can be retained.  This sort of tolerance to change is key for many newer systems where constant releases to production environments are a reality and it’s good to see this sort of thinking going into new features in DB2 so we can implement it in a standard way rather than coming up with a solution manually.

Overall, throughout this series of blogs the REST API functionality has proved simple to use and reliable, and already looks as if it is going to be a valuable addition to the DB2 feature set.  Familiar to developers and available on so many platforms it should be a consideration for any new development, particularly those based on modern agile techniques and continuous deployment.  Anyone using a microservices architecture or some enterprise service bus implementations would also want to take a look at how they could take advantage.

Hopefully going forward the capabilities of the REST API service are expanded so we can create services with calls to custom stored procedures, or SYSPROC.ADMIN_CMD.  I’m rather liking the idea of just telling Siri (other digital assistants are available…) to kick off jobs in a database without having to fire up the laptop and dial in.  Speaking of Siri, stay tuned for a bonus blog on how to create an iOS shortcut to call a REST API service


Leave a Reply

Your email address will not be published. Required fields are marked *

« | »
Have a Question?

Get in touch with our expert team and see how we can help with your IT project, call us on +44(0) 870 2411 550 or use our contact form…