Give Web Service Calls a Rest with Newest Db2 Update

 One might imply from the title that I’m advocating that developers stop using SQL to invoke web services from their IBM i applications. That is not my intent at all! In fact, the Db2 enhancements associated with the newest IBM i Technology Refresh for the IBM i 7.3 and 7.4 releases make it even more compelling to use SQL to invoke web services from IBM i applications.

The reason that SQL invocation of services is more compelling with the latest updates is that Db2 for i now supports the following set of new HTTP functions in QSYS2. These HTTP functions allow SQL to use RESTful services without the overhead of creating a Java Virtual Machine (JVM) like the existing HTTP functions found in SYSTOOLS.

HTTP_GET	HTTP_GET_VERBOSE
HTTP_POST	HTTP_POST_VERBOSE
HTTP_PUT	HTTP_PUT_VERBOSE
HTTP_DELETE	HTTP_DELETE_VERBOSE

The elimination of the JVM requirement means that web service invocations will run faster and more efficiently on your IBM i server. IBM i applications can scale to support more users and more transactions with these new HTTP functions requiring less usage of CPU and heap temporary storage. More details from my IBM colleagues on these HTTP functions can be found here.

The latest Database Group PTFs also include a new Db2 for i service, ACTIVE_QUERY_INFO, that can be helpful in narrowing down your analysis and investigation of query performance issues.

While not delivered with the Database Group PTF, the newest IBM i Access Client Solutions (ACS) version also includes some nice add-ons for Db2 and SQL. These ACS updates include Run SQL Scripts supporting tighter transaction control as well as improved error feedback.

To demonstrate the improved Run SQL Scripts error feedback, let’s use a simple query that divides two integer columns: SELECT Col1/Col2 FROM Tab1. The following figure shows the output from the latest version of ACS Run SQL Scripts. In this example, the second row in table Tab1 has a Null value for Col2 and the fourth row in table Tab1 has a value of 0 for Col2. Those Col2 values explain why the result set returned by this simple query does not contain a numeric division result in the second and fourth rows. 

The “-“ value indicates a Null value was returned for the division result which makes sense since the divisor value in the second row was Null.  The “++++++++++++” value in the last row indicates an error occurred due to the divisor value being 0.  Prior to this latest enhancement, Run SQL Scripts would have returned the same “-“ value for both the second and fourth rows in the result set making it difficult for the user to detect that a divide by zero error had occurred during the execution of their query.  Disclaimer – this improved error feedback was actually part of the prior ACS update, but I forgot to highlight this enhancement in my coverage of the prior Db2 for i update.

These great enhancements are a good reminder about the importance of keeping both your Database Group PTF level and ACS versions current.