Using EXPLAININPUTSCHEMA

At the beginning of this year, IBM introduced a new bind query parameter, EXPLAININPUTSCHEMA.

Available for DB2 for z/OS 10.0, this parameter is designed to simplify the task of populating EXPLAIN tables that are required for command input. The parameter allows DBAs to create these tables under a separate schema.

Prior to this enhancement, customers had to delete most existing rows in the plan_table while maintaining only those rows needed to be used as input into the BIND QUERY command. Plan_tables normally contain the access path for many different programs, so having to delete this history was a real problem for many who rely on this information.

To take advantage of this enhancement, you need to create a new plan_table using a new schema name. For example, to create a plan_table under schema BINDQ and then copy rows from your existing plan_table to be used as input into BIND QUERY, you'd issue this statement:

BIND QUERY EXPLAININPUTSCHEMA(’BINDQ’)

With this change, the BIND QUERY LOOKUP(YES) must change to recognize the new table. The format to use BINDQ schema is:

BIND QUERY LOOKUP(YES) EXPLAININPUTSCHEMA(‘BINDQ’)

Read more about this parameter in the DB2 10 for z/OS Command Reference.

Red Alert Could Impact DB2 10 Users, z/OS 1.12 and 1.13 Releases

This IBM Red Alert notification, “Potential loss of data for DB2 v10 users on z/OS 1.12 and 1.13 releases,” is dated May 29:

                Users Affected:

                DB2 V10 and PDSE users potentially affected by error in z/OS Media Manager IO recovery. Users of zDMF running zHPF may be particularly exposed because all I/O requests are intercepted by zDMF and returned to Media Manager in error for IO redrive using FICON, however any I/O errors, including interface control check and unit checks, that are retried by the media manager are also subject to the problem.

                Description:

                There are certain situations where the Media Manager will redrive the failed channel program with a different type of channel program. In some highly timing dependant cases, such as with I/Os that cross extents, the redrive may not occur correctly, and no error is presented to the caller, potentially leading to data being down level on disk. The problem may occur for DB2 V10 or PDSE data sets. Please see APAR OA42277 for more details.

                Recommended Actions:

                Apply ++APAR or PTFs for OA42277. Activation requires re-IPL of the system.

                Also please do not use zDMF until the fix for OA42277 is applied.

A May 31 update to this alert added z/OS 2.1 to the list of platforms and IMS FastPath DEDB to the list of databases impacted.

 

Deciphering Encryption Options

Numerous options are available for implementing encryption in DB2 for z/OS environments. However, each one has its restrictions and limitations. The encryption solution you choose (column, row, built in functions, edit procs,) comes down to what it is you're trying to accomplish.

My preferred encryption option has always been the built-in SQL function, ENCRYPT. In my initial testing I chose a character column to encrypt, and then used this set of SQL statements to test it out:

 

  SET ENCRYPTION PASSWORD = 'ABC’;

  INSERT INTO EMP(EMPNO,FIRSTNME, MIDINIT, LASTNAME)

  VALUES (ENCRYPT('12346'),'TROY,'L', 'COLEMAN') #

 

This generated the following error:

 

DSNT408I SQLCODE = -20144, ERROR:  THE ENCRYPTION IS INVALID BECAUSE THE    

         LENGTH OF THE PASSWORD WASS LESS THAN 6 BYTES OR GREATER THAN 128 

         BYTES                                                             

 

I quickly realized I needed to increase the password from three characters (ABC) to six (ABCDEF). But then I encountered this error:

SQLCODE = -433, ERROR:  VALUE '3C2BD50401F40010D5ABD5AE04B804B8B96180- 

506FFE4F232B640B11451BA29F7C506A24249436CF'X IS TOO LONG   

 

With additional research, I found that the column needs to be varchar. The 6-character column actually needed to be a varchar 64 byte column. This is made up of 6 bytes for maximum length of unencrypted data plus 2 additional bytes to round up to 8-byte boundary. Then there's 24 bytes for the password phrase plus 32 bytes for the password hint.

This experience made me realize that I've been underestimating the critical role database design considerations play when it comes to implementing encryption. For instance, many third-party applications and reporting tools limit the use of DB2 SQL encryption language. Even a basic statement like SET ENCRYPTION PASSWORD might not be allowed. So unless you can get your vendor to add in support for DB2 SQL encryption statements, you should look at other options.

With this in mind, here are some excellent resources on encryption.

* First is this presentation given by Roger Miller at IDUG EMEA 2009. It provides an overview of various encryption solutions, including ICSF, DB2 fieldprocs, DB2 editprocs, user-defined functions, SQL functions. Encryption is also discussed as it relates to DRDA, tape and disk systems.

* The IBM Redbook, Security Functions of IBM DB2 10 for z/OS, also covers an array of encryption options.

* Another IBM Redbook, Securing and Auditing Data on DB2 for z/OS, provides step by step details on implementing an editproc. It also covers key restrictions (e.g., a table that uses an editproc cannot be altered).

* This 2008 article by Ulf Mattsson explains how to encrypt columns, rows, indexes and disk in DB2 for z/OS.

If you use encryption on DB2 for z/OS, please share your experiences in comments.

Best Practices for Processing LOB and XML Data

DB2 has long supported LOB and XML data processing. LOB support goes all the way back to the GA of DB2 6 in 1998. XML support, I believe, debuted in DB2 7 as a bolt-on using XML extenders.

However, neither introduction went smoothly. When first unveiled, LOB support had many performance implications, which caused most customers to avoid storing data in LOBs. And due to the complexity and lack of integration with the DB2 optimizer, customers were very slow to adopt XML. To its credit, IBM has addressed these issues through the years, and thanks to significant performance improvements, customers are increasingly using LOBs.

On the other hand, even though native XML data type support was delivered with DB2 Version 9.1 in 2007, use of XML on DB2 for z/OS continues to be minimal. Perhaps this stems from the fact that XML processing has changed so dramatically through time. It started as local, host-based processing, then went to client/server applications development in C and C+. Now processing is done through web applications using Java.

I’ve written extensively on the use of XML over the years. It's an improved solution thank to enhancements in the DB2 optimizer as well as greater support for developers using XML on z/OS. If you've avoided storing XML DB2 on z/OS because of performance or the lack of XQUERY and XPATH functionality, I believe you should reconsider.

There are two important resources that bring you the latest techniques and best practices for processing LOB and XML data. The first is the DB2 10 for z/OS Application Programming and SQL Guide, which explains how to develop DB2 10 for z/OS applications using languages such as Assembler, C, COBOL, Fortran, PL/I and Rexx. If you're interested in Java development, you should check out the DB2 10 for z/OS Application Programming Guide and Reference for Java.

If you've done LOB and XML development and taken advantage of some of the new features in DB2 10, I'd like to hear from you. Same goes for those who are new to working with LOB and XML data in DB2 for z/OS. Please share your experiences in comments.

Using File Reference Variables

DB2 10 for z/OS introduced support for reading and writing LOB and/or XML data to files using file reference variables. File reference variables allow large LOB or XML values to be inserted from or selected into files rather than host variables. This saves memory because applications aren't holding large XML or LOB values.

A related benefit of using file reference variables is that you avoid storing large XML/LOB values in host application working storage. The data bypasses the application storage and travels directly to the client file system and database server.  And by keeping the data out of the application storage, you avoid any limitations the application host language may place on working storage sizes. These languages supports file reference variables: C, C++, COBOL, PL/I, Assembler, REXX and Java. 

Do you have an application that takes advantage of DB2 10 for z/OS file reference variables? Please take a moment and post a comment about your experience.