Using the New Compatibility Mode

In May when I posted a summary of new DB2 11 features, I mentioned that the latest release allows applications with SQL or XML to be run in compatibility mode. Since this is an important feature that will help customers much more quickly migrate to DB2 11, I'll provide some detail.

In DB2 11, you can set the SQL function and features available to an application package based on an application compatibility value. The values are either V10R1 or V11R1. Leaving the application value at V10R1 gives you time to evaluate any changes in behavior when switching over to V11R1. Should you experience a negative behavior, you can just switch back to V10R1. The application compatibility value can be set through a bind option, a subsystem parameter or the special register.

A new subsystem parameter, APPLCOMPAT, is used to set the application programming value. During a migration this will default to V10R1; during a new install it will default to V11R1. These default behaviors can be changed using install panel DSNTIP41. It's recommended that the default value be the same for all members of a data sharing group. 

With a value of V10R1 during a migration, all existing applications will continue to execute as if they're on a DB2 10 version subsystem -- even when running in DB2 11 new function mode.

Over time, as you ensure everything is stable in DB2 11 (with compatibility mode set to V10R1), you can start using the BIND/REBIND parameter, APPLCOMPAT, with a value of V11R1. This, obviously, enables the application to exploit the new features available in DB2 11 new function mode. Note: The BIND/REBIND command parameter APPLCOMPAT only sets the compatibility mode for static SQL statements.

For dynamic SQL, use the special register, CURRENT APPLICATION COMPATIBILITY, to specify the compatibility level. The initial value of CURRENT APPLICATION COMPATIBILITY is based on the APPLCOMPAT value used in the BIND PACKAGE:

            Example to find the current application compatibility value:
                SET :host-variable = CURRENT APPLICATION COMPATIBILITY;

            Examples to set the special register:
                 SET CURRENT APPLICATION COMPATIBILITY = :host-variable;
                 SET CURRENT APPLICATION COMPATIBILITY = ‘V10R1’;

By providing the capability to manage and reduce negative behaviors that can occur during the migration process, the new compatibility mode should make it easier and quicker for customers to move to new DB2 releases.

If you've already used the new compatibility mode during a migration to DB2 11, please share your thoughts about this feature in comments.

Additional Processes Authorized for zIIP in DB2 11

As I read this Willie Favero post about zIIP capacity, I got to wondering about additional zIIP processing capabilities in DB2 11.

My search began with this IBM summary of what's new in DB2 11. I found only one reference to zIIP:

            Cleanup of pseudo deleted index entries as part of DB2 system task cleanup is   now eligible for zIIP processing.

Then I remembered this IDUG editorial committee-produced white paper(registration required), which includes a section on zIIP processing in DB2 11.

This section features a list of the processes that are enabled to run on zIIP:

• Asynchronous processing that's executed under enclave service request blocks (SRBs) and "charged" for CPU consumption purposes to an IBM DB2 address space (rather than to user applications), with the exception of P-lock negotiation processing. Such zIIP eligible processing includes:
• Cleanup of pseudo deleted index entries as part of DB2 system task cleanup1
• Cleanup of XML multi-version documents (available in DB2 10 for z/OS via APAR PM 72526)
• Log write and log read
• DB2 base LOAD, REORG and REBUILD INDEX utility processing of inline statistics collection (up to 30 percent offload).
• DB2 base processing of RUNSTATS utility Column Group Distribution statistics collection (up to 80 percent offload).
• DB2 base LOAD utility index management processing when running LOAD REPLACE.

Then I checked out the IBM Knowledge Center. By searching on the term, “cleanup of XML multi-version documents," I found this list of "Authorized uses for System z Integrated Information Processors (zIIP)":

• SQL request workloads that use DRDA to access DB2 for z/OS over TCP/IP connections
• Parallel query child processes
• Utility processes
• XML processing
• Asynchronous SRB processing

Since substantial cost savings can be achieved when customers can use zIIP in place of the general processor, expect to see more processes authorized for zIIP in future DB2 releases. And hopefully, IBM will make it easier for us to learn which processes are being authorized for zIIP.

DB2 10-11 Migration: Application and SQL release incompatibilities

I previously noted that DB2 11 allows applications with incompatible SQL or XML to run in compatibility mode.

Here I'll examine a related question: What will cause an application to be incompatible?

IBM provides this information in the DB2 for z/OS Installation and Migration Guide, in the section titled, "Application and SQL release incompatibilities."

A list of potential incompatibilities, along with brief descriptions, follows. See the installation and migration guide to understand how these incompatibilities can impact your applications and how to deal with them.

* Change to determination of ASUTIME for dynamic statements -- In Version 11, new-function mode with application compatibility is set to V11R1, and the dynamic SQL ASUTIME limit for each routine is used by the resource limit facility.

* Automatic rebind of plans and packages created before DB2 Version 9 -- Plans and packages that were last bound before Version 9 are not supported in Version 11 conversion mode and later.

* Change in default for ODBC-limited block fetch initialization keyword -- The default for the LIMITEDBLOCKFETCH initialization keyword is changed in Version 11.

* DB2 objects created with period specifications in Version 10 -- In Version 11, views, materialized query tables and SQL table functions that were created with period specifications in Version 10 are not supported.

* Dropping columns -- In Version 11 new-function mode, columns named CLONE, ORGANIZATION or VERSIONING should be specified as a delimited identifier to be dropped from a table.

* XPath processing -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, XPath is allowed to continue processing even when errors occur on filtered results when using predicate expressions with explicit cast or operations with an invalid value.

* XML document node implicitly added on insert and update -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, if an XML document does not have a document node, then one is added during insert and update operations.

* Client information special registers length -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, special registers for client information fields might return different length values. The values in the CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNAME, CURRENT CLIENT_APPLNAME, and CURRENT CLIENT_ACCTNG special registers are determined by the application compatibility level.

* Client information results from ADMIN_COMMAND_DB2 -- In DB2 Version 11 conversion mode, the ADMIN_COMMAND_DB2 result set row returns changes in the created global temporary table SYSIBM.DB2_THREAD_STATUS when processing-type = "THD". The column data type and maximum lengths for WORKSTATION, USERID, APPLICATION and ACCOUNTING change.

* Altering limit keys blocks immediate definition changes -- In DB2 Version 11 new-function mode, if a limit key is altered for certain table space types, immediate definition changes cannot be made until the limit key changes are materialized.

* Removing the SYSPUBLIC schema from the SQL PATH routine option -- Starting in DB2 Version 11 conversion mode, SYSPUBLIC is the schema that is used for public aliases. As such, the SQL PATH routine option must not specify the SYSPUBLIC schema.

* SYSIBMADM schema added to the SQL path -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, SYSIBMADM is added to the SQL path as an implicit schema.

* Change in result for CAST(string AS TIMESTAMP) -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the result of CAST(string AS TIMESTAMP) is changed in some cases.

* New maximum lengths for values that are returned for some built-in functions -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the maximum lengths for values that are returned for some built-in functions is decreased.

* Timestamp string representations -- DB2 Version 11 new-function mode with application compatibility set to V11R1 strictly enforces valid string representations of timestamp values.

* SQL reserved words -- Version 11 has several new SQL reserved words. These are listed in reserved words.

 

* Fully qualified user-defined function names -- When using a user-defined function with the same name as a built-in function that has been added to Version 11, the function name must be fully qualified. If the function name is unqualified and SYSIBM precedes the schema used for this function in the SQL path, DB2 invokes one of the built-in functions.

* SQLCODE changes -- Some SQLCODE numbers and message text may change in DB2 Version 11. Also, the conditions under which some SQLCODEs are issued may change.

With earlier DB2 releases, addressing incompatibilities would require substantial changes and testing before the migration could take place. That's the major benefit of DB2 10 compatibility mode. It enables customers to migrate quickly to DB2 11 while maintaining current applications until they can be modified and tested on the new release.

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.

Learning XQUERY

Over the years I’ve written often about the significance of XML. As XML becomes more commonly used in mainframe environments, IBM continues to expand the capability of DB2 for z/OS to manage XML documents: first with XPATH and then with XQUERY.

To understand when to use XPATH and when to use XQUERY, I recommend the DB2 for z/OS PureXML Guide. But basically, XPATH should be used when you need to identify qualifying documents in an XML column using XMLEXISTS function. This is because XPATH is more efficient than XQUERY; it can take advantage of XML indexes with the XMLEXISTS predicate.

XQUERY, on the other hand, should be utilized when you need to use constructors or conditional logic. You cannot use an XML index with XQUERY expressions.

XQUERY was introduced in DB2 10 for z/OS in November 2011 through APARs PM47617 and PM47618. (It was unveiled on DB2 for LUW about five years earlier.) The IBM Redbook, "XQuery in IBM DB2 10 for z/OS," explains the basics of XQuery through simple examples.

In addition, this publication reviews the basic constructs of the XQUERY language and explains the three types of XQuery expressions. Coding are examples provided for:

• FLWOR
• direct XML constructors
• conditional expressions

 For more in-depth information, you should also read the Redbook, "Extremely pureXML in DB2 10 for z/OS." Other reference materials, including technical articles, webcasts, podcasts/presentations, demos and sample downloads are available from the IBM developerWorks DB2 for z/OS pureXML home page.

I'm seeing more mainframe organizations develop in XML and store and manage XML documents in DB2 for z/OS. If your company uses XML, please share some of your experiences in Comments.

More on Migration Planning

Last week I mentioned IBM's free DB2 10 migration planning workshops. IDUG has its own offering for those who will be attending the group's upcoming conference.

The title of IDUG's workshop, "DB2 10 for z/OS Migration Planning," shouldn't mislead you. It's not just for systems programmers; it's designed for everyone who works with DB2 for z/OS, including application developers, data architects, database adminstrators, IT managers, new users.

Technical areas this presentation applies to:

• New Release (DB2 for z/OS)
• DB2 10 for z/OS Overview
• DB2 9 Recap (For those migrating from V8)
  o Application Development focused topics
  o SQL & Application Access
  o XML
  o Temporal (Versioned Data)
  o LOBs
  o Advance
• Transitional Topics
  o Optimization Evolution
  o Index Improvements
  o Security Enhancements
  o Utilities
  o Data Sharing
  o Distributed Communications
• Systems oriented topics
  o Virtual Storage
  o Serviceability / Instrumentation
• DB2 10 Migration
  o Review of V8 to V10 & V9 to V10
  o Preparations
  o Planning
  o Process

Register for this free one-day workshop. IDUG's DB2 Tech Conference is May 14-18 in Denver.

XML's Growing Importance on the Mainframe

At the recent IDUG conference in Melbourne, Australia, I attended Phil Grainger's day of education. The topic of XML came up and someone asked if it was being used in DB2 for z/OS. While I'm amazed at the slow adoption of XML on DB2 for z/OS, to be honest, the first implementation of XML using CLOBs wouldn't scale and performed very poorly. However, since the introduction of XML data types with DB2 9, we can now index into documents and the processing scales very well.

I think another reason customers have been reluctant to use XML on z/OS is that, prior to DB2 10, developers could only update an entire document, rather than just parts of it. Obviously that poses its own performance and logging implications. But again, DB2 10 removes this barrier. Now you can update parts of XML documents by using the standard UPDATE statement with the XMLMODIFY function. XMLMODIFY uses basic XQuery updating expression to insert, delete or replace nodes. You can also replace values of nodes in XML documents stored in XML columns.

With its latest version, DB2 fully supports storing, updating and processing XML documents. Now is the time for DB2 DBAs on z/OS to embrace XML and use it in their applications. Why? Because XML has an important place on the mainframe. XML ever-increasily serves as the data transport layer between businesses. Those who don't use XML in DB2 for z/OS must store their data off of the mainframe (in some other database such as SQL Server, Oracle or DB2 LUW). So where is your company processing XML documents?

Heading to SHARE

I'll be on hand for the SHARE conference in Anaheim, Calif., from Feb. 27 to March 4.

Despite SHARE's relevance to the mainframe world, I actually hadn't attended a conference in many years until I went to the one in Boston last summer.

I enjoyed that conference because I got a chance to see old friends from the DB2 community. I also made plenty of new friends between networking with folks in different areas of z/OS management and talking to young professionals involved in the zNextGen Project.

At the Anaheim conference, SHARE is specifically organizing sessions on these topics:

• Using zEnterprise to deliver value while reducing the cost of managing your data center.

• Virtualization to make better use of existing resources.

• Virtualization as a cost-effective approach to delivering new services.

• Security and privacy practices that keep you compliant and that avoid compromise costs.

• Effective application technologies and architectures that deliver timely business solutions.

Since my primary interest is DB2 (surprise), I searched and found that this conference will have 74 DB2 sessions.

If you're going to the conference, here are a few DB2 sessions you won't want to miss:

• 8356 -- DB2 10 performance topics (Roger Miller, IBM)

• 8786 -- IBM Smart Analytics Optimizer for DB2 for z/OS: How Does it Work? (Willie Favero, IBM)

• 8789 -- The DB2 Catalog Has Come a Long Way Since 1983 (Favero)

• 8823 -- Comparing and Contrasting XML Features of DB2 and COBOL: When to Use Which One? (Tom Ross, IBM)

Roger and Willie are simply two of the best speakers you'll ever hear. And as one who's written extensively about XML and COBOL, I'm also looking forward to Tom's presentation on that topic. It's good to see IBM involved in this area.

If you're attending SHARE, please stop by and say hello. I'll be at CA Technologies booth showing off my new product, Chorus.

DB2 10 pureXML

The introduction of pureXML in DB2 9 for z/OS gave administrators a way to store well-formed XML documents in their hierarchical form and retrieve all or portions of these documents. Prior to DB2 9, storing XML was a chore -- the available options were to either shred the documents and archive the XML data in relational tables as relational columns, or store them in a LOB, which posed its own performance and processing problems. DB2 9 users quickly grew to appreciate the labor savings, shortened development cycles and greater flexibility that pureXML offers.

DB2 9 pureXML offered these additional benefits:

•    New storage techniques for efficient management of XML documents.
•    New index technology to speed the process of accessing XML documents.
•    New query language support for XQuery, a new graphical query build for XQuery, and new query optimization techniques.
•    Support for validating XML data based on user-supplied schemas.

DB2 10 will include a second release of pureXML. This updated version is designed to provide numerous performance and usability improvements such as automatic XML schema validation. With this new function, you won't have to specify XML schema for validation -- DB2 will search and find a schema. DB2 also supports multiple schema versions, so existing older documents won't need to be changed.

DB2 10 pureXML is also expected to include:

•    A binary XML exchange format that's designed to improve performance.
•    XML multi-versioning, which is designed to improve concurrency and performance.
•    Support UPDATE on sub-parts of XML documents.
•    Enhanced XML support in stored procedures, UDF and triggers.
•    XML index matching with date and timestamp.
•    CHECK XML and CHECK DATA utilities. The latter will verify the consistency of XML documents stored in a separate XML table space.

If you're just getting started with pureXML, be sure to check out these IBM Redbooks: "DB2 9 pureXML Overview and Fast Start" and "DB2 9 pureXML Guide."

Recovering XML Table Space

I firmly believe that DBAs should always be prepared for the worst-case scenario. With this in mind, an imagecopy backup of data is your best friend. However, if you haven't worked with tables containing XML data, you may not know that both the table space (which contains the base table data) and the auxiliary table space (which contains the XML column) must be backed up. In other words, an auxiliary table space is needed for each XML column in the table.

XML's unique characteristics complicate the task of recovering tables when the table space has been dropped. Once you drop a table space, all recovery information about that table space is no longer available to the recovery utility, even if you re-create the table space. The only way to recover the table space is to rebuild it using the original DDL; then use the DSN1COPY utility to restore the imagecopy into the new table space. For more about DSN1COPY, see the "DB2 Version 9.1 for z/OS Utility Guide and Reference."

Recovering tables with XML data requires preparation. First, you must maintain a list of all the table space, table and index information with internal object identifiers. DSN1COPY needs this information if the newly built or target VSAM files have different object identifiers. Because all auxiliary table spaces must be backed up and restored to the same point in time as the base table spaces, you also need a report listing this information. I wrote a DB2utor blog entry that explains how to get a list of base tables along with associated XML tables and table space names.

REPORTS
Next, you must generate a report showing all table space and index space internal DBID, PSID, OBID and IX_OBID. This query provides the information needed for DSN1COPY:

SELECT SUBSTR(TS.NAME,1,8) AS TSNAME      
     , TS.DBID                            
     , TS.PSID                            
     , TS.PGSIZE                          
     , SUBSTR(T.CREATOR,1,8) AS CREATOR   
     , T.OBID                             
     , SUBSTR(T.NAME,1,18) AS TBNAME      
     , SUBSTR(I.INDEXSPACE,1,8) AS IXSPACE
     , I.DBID AS IX_DBID                  
     , I.ISOBID AS IX_ISOBID              
     , I.OBID AS IX_OBID                  
     , SUBSTR(I.NAME,1,18) AS IXNAME      
     , I.PGSIZE                           
FROM SYSIBM.SYSTABLESPACE TS              
   , SYSIBM.SYSTABLES T                   
   , SYSIBM.SYSINDEXES I                  
WHERE TS.DBNAME = 'CSBPDB'                
  AND TS.NAME = T.TSNAME                  
  AND T.DBNAME = TS.DBNAME                
  AND T.DBNAME = 'CSBPDB'                 
  AND T.CREATOR = 'CSBP'                  
  AND T.NAME IN ('PRODUCT','XPRODUCT')    
  AND T.TYPE IN ('T','P')                 
  AND I.DBNAME = T.DBNAME                 
  AND I.TBCREATOR = T.CREATOR             
  AND I.TBNAME = T.NAME                   
ORDER BY T.DBNAME                         
       , T.TSNAME                         
       , T.CREATOR                        
       , T.NAME                           
WITH UR ;   

In this example I qualified the result to database CSBPDB and tables PRODUCT and XPRODUCT. For your production system, generate this report for the entire subsystem and keep it in a safe place.

TSNAME      DBID    PSID      PGSIZE      CREATOR     OBID    TBNAME 
---------------+---------+---------+-------------+----------------+---------+-------------
CSBPTS02       272          61                4       CSBP                    62    PRODUCT
XPRO0000       272         64               16       CSBP                    65    XPRODUCT

                                             IX          IX            IX
TBNAME       IXSPACE       DBID  ISOBID   OBID    IXNAME                        PGSIZE
-----------------+---------------+--------+-----------+-------+----------------------------

--+---------
PRODUCT     PROD1GEQ       272            74         73   PROD_NAME_XMLIDX         4 
PRODUCT     IRDOCIDP         272            70         69   I_DOCIDPRODUCT                 4
PRODUCT     PRODRNAM     272            72         71   PROD_NAME_PIDX                4
PRODUCT     PRODRDET       272            76         75   PROD_DETAIL_XMLIDX      4
XPRODUCT  IRNO11TN         272            68         67   I_NODEIDXPRODUCT           4

BACKUP
I usually just back up the table space, but now that you can backup indexes, I figured I would test the DSN1COPY restore for all table and index spaces related to the PRODUCT table. Before you can use the imagecopy utility on an index, COPY must be enabled on it. Use the ALTER INDEX DDL statement to do this. For example:

ALTER INDEX CSBP.I_DOCIDPRODUCT      
   COPY YES ;

DSN1COPY
Because I want to simulate what happens when a table space is dropped, I went ahead and executed DDL to drop and re-create the table space containing the PRODUCT table and all indexes. Once the table and index space are rebuilt, they must be stopped before you run the DSN1COPY utility. Use the DB2I panels option 7 DB2 commands to do this. For example:

–STOP DB(CSBPDB) SPACE(CSBPTS02)

Now run DSN1COPY to restore the table and index space with the imagecopies taken earlier.

Here's a sample to restore table space CSBPDB.CSBPTS02. Note that:

• OBIDXLAT isn't really needed in this case because the object IDs didn't change.
• FULLCOPY should be used when using a full imagecopy as input.
• PAGESIZE should be taken from the report above. XPRO0000 is 16K.

//DSN1COPY EXEC PGM=DSN1COPY,                                     
//           PARM='OBIDXLAT,RESET,CHECK,FULLCOPY,PAGESIZE(4K)'    
//STEPLIB  DD DISP=SHR,DSN=DSN910.SDSNEXIT                        
//                  DD DISP=SHR,DSN=DSN910.SDSNLOAD                        
//SYSPRINT DD SYSOUT=*                                            
//SYSUT1 DD DISP=OLD,DSN=CSBP.IC.CSBPDB.CSBPTS02.D031109.T160400  
//SYSUT2 DD DISP=OLD,
//                DSN=DB2DB9G.DSNDBC.CSBPDB.CSBPTS02.I0001.A001
//*  SOURCE DBID, TARGET DBID                                     
//*  SOURCE/TARGET PSID OR ISOBID FOR INDEX                       
//*  ALL OTHER SOURCE/TARGET TABLE OBID                           
//SYSXLAT DD *                                                    
272,272                                                           
61,61                                                             
62,62        
/*
//

Once you've completed the restore of all the table and index spaces, restart the objects:

–START DB(CSBPDB) SPACE(*).

Remember: Prepare for the worst-case scenario. Practice now before you really need to use this procedure in an emergency situation.