DB2 WIKI REFRESHer

For this installment, Kent Milligan is back with a reminder and some exciting news!

It's all yours Kent...

In my blog entry last month, I highlighted the value of following the DB2 for i Technology Updates wiki to learn about recent DB2 for i enhancements that can boost your productivity and application performance.

One of the times that you will find new info in the wiki is when IBM launches a new Technology Refresh for the IBM i operating system.

Today, Technology Refresh 4 for the IBM i 7.1 operating system was launched with more than 25 database enhancements.

Here is a summary of some of the more interesting DB2 for i enhancements...

The Insert with Remote Subselect support makes it easier to share data between systems.  This new SQL feature enables a table on one system to be populated with data from tables on a different system – eliminating the need to first copy the data into some type of staging area.  The following SQL statement shows easy it is to use this new support.  The only setup work on the requesting system is adding a relational database directory for rmtsystem to the Relational Database Directory with the ADDRDBDIRE system command.

    INSERT INTO locschema.locCustInfo

        SELECT lastname, address, city FROM  rmtsystem.rmtschema.custtable

If you utilize SQL stored procedures and functions to perform critical business calculations that you don’t want to share with others, then you should dig into the new Obfuscation feature for SQL procedures and function. Think of obfuscation as just a really big word for masking. With the obfuscation feature, users will see a masked value of your business logic like the following graphic displays - instead of the original business calculation when accessing the DB2 for i catalog. The obfuscation feature should be most interesting to software vendors that have many algorithms and processes that they would like to protect as intellectual assets.

The new SQL XMLTable function puts the finishing touch on the DB2 and XML integration capabilities that were delivered at GA of the IBM i 7.1 release.  The XMLTable function is a swiss-army knife type of tool enabling developers to use a wide range of XPath expressions to retrieve data from XML documents in relational format.  Let’s assume the following XML document is stored within an XML column named location_doc within the employee table.

  <dept bldg="025">

        <employee id="146">

               <name><first>Mary</first>

                           <last>Dilbertson</last></name>

               <phone>507-333-1234</phone>

               <office>415</office>

               <officesize measure="SQFT">160</officesize>

        </employee>

  </dept>

This SELECT statement utilizes the XMLTABLE function to return a subset of the business data stored in the XML document in relational format to produce the following output.

  SELECT X.empid, X.lastname, X.officenum 

  FROM employee, 

       XMLTABLE ('$d/dept/employee' PASSING location_doc as "d" 

               COLUMNS 

               empID           INTEGER        PATH '@id',

               lastname       VARCHAR(25)    PATH 'name/last',

               officenum      CHAR(3)         PATH 'office') AS X

Output produced by the XMLTable function:

To increase the adoption of SQL by IBM i developers across all languages and interfaces, a new RUNSQL CL command is available on both the IBM i 7.1 and 6.1 releases.  This command will make it easier for developers to exploit SQL in their CL programs.  

Hopefully, this summary of the “Technology Refresh” DB2 for i enhancements not only whets your appetite to take a deeper look at this new round of DB2 for i updates, but also motivates you to be a frequent visitor to the DB2 for i Technology Updates Wiki.