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 10th 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
<b> - 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!
