Fall Into a Smorgasbord of Db2 Goodies

Last month's entry coincided with the start of Fall on the calendar. Another event that occurs each Fall is IBM announcing IBM i Technology Refresh content. This year's announcement happened on October 10^th and as you've come to expect there are several Db2 for i enhancements. No major additions to Db2 this time, but several small Db2 enhancements that could make your life easier - thus, usage of smorgasbord in this title is a good fit.

I'll illuminate my favorite Db2 enhancements in the latest IBM i 7.4 and 7.5 Technology Refreshes (TRs), but you'll want to review the complete list of enhancements. Feel free to add comments to this entry if you find other Db2 for i enhancements that you believe are worthy of highlighting.

Tightening security for the valuable business data stored on your IBM i systems is a top priority, so the latest TRs have a couple of interesting security enhancements. If you're using SQL to perform encryption, you can now perform 256-bit encryption with the new ENCRYPT_AES256 function. The original SQL function for AES encryption, ENCRYPT_AES, only supports 128-bit encryption. If you have applications using Dynamic SQL with interfaces that may be susceptible to SQL injection attacks, then you may want to consider using the enhanced PARSE_STATEMENT service to verify only the intended Db2 objects are being referenced by your Dynamic SQL statement strings.

The new HTTP functions in QSYS2 introduced 2 years ago have been a big boon to developers using SQL to integrate restful services into their IBM i applications. The latest TRs continue to enhance this HTTP services toolkit with the addition of new HTTP header response controls for the existing functions and two new functions. The new functions are HTML_ENTITY_ENCODE and HTML_ENTITY_DECODE. The encode can be used to convert a character string into a UTF-8 string that is valid HTML. For example, if your input string contains the less than or greater than characters like <b>, the encode function will produce &lt;b&gt; - the decode function provides similar functionality, just the other direction.

The usability of the SQL Error Logging Facility (SELF) is also improved with the ability to have Db2 monitor and log for groupings of SQL codes - specifically the warning codes (SQLCODE>0) and the error codes (SQLCODE<0). As shown below, the special values of *WARN & *ERROR can be specified instead of assigning multiple individual code values to the IBM-provided global variable.

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256) DEFAULT '*WARN';

SET SYSIBMADM.SELFCODES = '*ERROR';

When the logged SQL error data in the SELF_ERROR_LOG view in QSYS2 is analyzed, one may also want to leverage the new SQLCODE_INFO function in SYSTOOLS to return the message text associated with the logged SQLCODE value.

Developers that are using Code for IBM i as their IDE will also want to give the new Db2 for i Extension a try to enrich their usage of SQL in this graphical development environment.

The final Db2 for I enhancement to highlight is the GENERATE_SPREADSHEET function in SYSTOOLS. This function provides a way for you to programmatically drive the Data Transfer feature in IBM i Access Client Solutions (ACS) to generate a spreadsheet. The function can be used to populate the contents of a spreadsheet file in IFS with the rows in table or the rows produced by a query.

Here's an example of using the new function to generate a spreadsheet file containing the results of the specified query.

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
   PATH_NAME => '/mydir/EmpQry_Spreadsheet', 
   SPREADSHEET_QUERY => 'SELECT fname, lname, workdept FROM emp WHERE role=''ANALYST'' ', 
   LIBRARY_NAME => NULL,  

   FILE_NAME => NULL, 
   SPREADSHEET_TYPE => 'csv', 
   COLUMN_HEADINGS => 'COLUMN' )

I hope you find a few tasty enhancements from the buffet of Db2 items delivered by this latest IBM i TRs that can make your IBM i life easier!