DB2 Dispatch from Vegas: IOD, Day Two

Greetings again from the 2010 IBM Information on Demand conference (IOD) in Las Vegas. Lots of good DB2 10 for z/OS stuff today. Some highlights of sessions I attended:

Getting the most out of native SQL procedures. This presentation was delivered by Pallavi Priyadarshini and Maryela Weihrauch of IBM's Silicon Valley Lab (the home of DB2 for z/OS). I have a major interest in the topic, having blogged about native SQL procedures a number of times while working as an independent DB2 consultant (SQL procedures are stored procedures written entirely in SQL, and native SQL procedures, introduced with DB2 9 for z/OS, have no external-to-DB2 executable -- they execute as packages in the DB2 database services address space, aka DBM1):

• http://catterallconsulting.blogspot.com/2007/12/what-are-your-plans-for-db2-for-zos.html
• http://catterallconsulting.blogspot.com/2008/11/db2-9-for-zos-stored-procedure-game.html
• http://catterallconsulting.blogspot.com/2009/02/another-advantage-of-db2-for-zos-v9.html
• http://catterallconsulting.blogspot.com/2009/06/db2-stored-procedures-cobol-and-result.html
• http://catterallconsulting.blogspot.com/2009/07/migrating-db2-9-for-zos-native-sql.html
• http://catterallconsulting.blogspot.com/2009/08/db2-for-zos-stored-procedures-and-large.html
• http://catterallconsulting.blogspot.com/2010/04/using-db2-stored-procedures-part-1.html
• http://catterallconsulting.blogspot.com/2010/04/using-db2-stored-procedures-part-2.html
• http://catterallconsulting.blogspot.com/2010/04/using-db2-stored-procedures-part-3.html 

Among the many excellent nuggets of information delivered in Pallavi and Maryela's IOD session are the following:

• How does the package of a native SQL procedure differ from that of an external stored procedure? The difference is basically the part that contains the SQL control language statements (these statements -- examples being IF, LOOP, and ITERATE -- provide logic flow control). These go into a part of the stored procedure package called Section 1. Almost all (about 90%) of the Section 1 part of a native SQL procedure package goes above the 2GB bar in the DB2 DBM1 address space, so you shouldn't be concerned about the virtual storage impact of native SQL procedure utilization.
• Note that the value of ASUTIME (the limit on the mainframe service units that a stored procedure can consume before it is canceled) defaults to NO LIMIT. Specifying a value for ASUTIME, even if it is ten or more times greater than what the stored procedure is expected to consume, provides means of keeping a stored procedure from getting into an infinite loop. It might be a good idea to specify an ASUTIME value if the procedure contains a loop operation (maybe via IF, or LOOP, or REPEAT) -- just in case a logic error causes DB2 to fail to exit the loop.
• External SQL procedures do not return unhandled warnings (i.e., warnings for which you don't have a condition handler) to the calling program. Native SQL procedures do.
• Syntax changes might be required when converting an external SQL procedure to a native SQL procedure. To help identify such changes, IBM has provided a HOST(SQLPL) precompiler that can be used to perform a checkout inspection for an SQL procedure definition, validating that the source is properly coded to conform with native SQL PL syntax and behaviors. This function is built into DB2 10, and it will be made available for DB2 9 via the fix for APAR PM13844 (expected to be available sometime in December). Per the APAR text, the HOST(SQLPL) checkout "is a recommended step to perform for all native SQL procedure development. It is a vital step to perform prior to any migration of an external SQL procedure to a native SQL procedure."
• When a stored procedure returns a result set to a caller, keep in mind that virtual storage will be used for that cursor and will not be released until commit. So, ensure that programs -- even read-only programs -- that fetch results from a cursor declared in a stored procedure issue a commit when done fetching rows (best practice is for the caller to also explicitly close the open cursor). Note that COMMIT ON RETURN in a stored procedure's definition is a way to ensure that a commit happens upon completion of the procedure's execution, but if you use that option for a stored procedure that returns a result set to the caller, ensure that the cursor opened in the stored procedure is declared WITH HOLD; otherwise, the automatic commit will close the cursor.
• With DB2 10, the XML data type is valid for stored procedure parameters and variables (prior to DB2 10, you had to use -- depending on the size of the XML document -- a VARCHAR or a LOB data type to work with an XML document in a stored procedure.
• DB2 10 enables the assignment of values to multiple parameters or variables in a stored procedure with a single SET statement. This "chained SET" capability can improve stored procedure performance.
• Testing by DB2 development of native SQL procedures in a DB2 10 for z/OS environment has shown improvements in CPU efficiency of 10-20% versus a DB2 9 system.
• SQL Procedure Language (aka SQLPL -- the SQL statements used to code a SQL stored procedure) can be used to create SQL user-defined functions (UDFs) in a DB2 10 environment. This is a very good thing, because the functionality of SQL UDFs is extremely limited in DB2 9 (and prior release) systems.

New DB2 10 for z/OS security features to help satisfy your auditor. Long title, yes, but a very good session delivered by DB2 developers Jim Pickel and Gayathiti Chandran. Jim started out by telling attendees that a goal in developing the security-related functionality introduced with DB2 10 was to enable organizations to meet compliance requirements with little or no application code changes. Some highlights of the session:

• New authorities provided by DB2 10 facilitate minimization of the use of super-user authorization levels such as SYSADM. Specifically, the new System DBADM authority is likely to be used extensively in place of SYSADM authority. System DBADM authority can be granted with or without data access privileges, and with or without access control privileges (the latter referring to the ability to execute GRANT and REVOKE statements). Interestingly, System DBADM enables execution of most CREATE, ALTER, and DROP statements, but not those that would affect system or security objects. In addition, someone with System DBADM authority would need additional privileges to create objects such as views, functions, and triggers. That said, a System DBADM can execute what are called system-defined routines (such as stored procedures and UDFs), and routines created by someone with install SYSADM authority are considered to be system-defined routines. This, someone with a super-user authority can create administrative routines that could be executed -- but not dropped or modified -- by a person with System DBADM authority.
• Another new authority, EXPLAIN, allows a person to EXPLAIN SQL statements (dynamic statements or statements embedded in programs) without having the authority to execute those statements. The new SQLADM authority adds privileges to those available via the EXPLAIN authority. For example, someone with SQLADM authority could issue an EXPLAIN for the dynamic statement cache, again without having the privileges needed to execute those statements. 
• Additionally, DB2 10 enables the separation of responsibility for managing access to restricted data from the owner of that data. A recommended best practice now is to have roles own objects, as opposed to having objects owned by individual authorization IDs. The CATMAINT utility can be used to switch ownership of an existing object to a role, and this can be done in such a way that programs with SQL statements referencing qualified object names do not have to be changed.
• Auditing capability has been significantly enhanced with DB2 10. Now, all dynamic access to data (versus just the first access with the DB2 9 audit functionality) and any use of privileged authority can be included in the audit trail. More good news: this enhanced auditing capability can be utilized without having to specify AUDIT in the to-be-audited object's DDL. Once an audit policy has been defined, it is "turned on" via a -START TRACE command that names the audit policy (DB2 will turn on, under the covers, the trace record types, or IFCIDs, that are needed to get the information requested in the policy -- that's good for ease of use). Audit information is recorded in SMF records. The DB2 utility DSN1SMFP can be used to print formatted SMF records in a report.
• Auditing of course involves some overhead, so it is recommended that audit policies be defined to track access to specific tables from specific programs. The fact that wildcarding can be used for schema and table names makes it easier to tailor audit policies.
• Besides tracking access to data, an audit policy can be set up to record each use of an authority or authorities.
• The temporal data capability built into DB2 10 (a first in the industry, by the way) makes it much easier to track changes to data records in DB2 tables (this by exploiting the new "system time" characteristic that can be built into a new table or easily added to an existing table).
• You now how passwords have been 8 characters in the mainframe world, like, forever? DB2 10 supports the "password phrase" functionality introduced with z/OS V1R10. A password phrase can be up to 100 characters in length.
• DB2 10 also supports the distributed identity functionality introduced with z/OS V1R11. This can make for a more-robust security set-up in a client-server environment.
• My favorite part of the presentation was the material on the new table-access control features of DB2 10 for z/OS. Why did I so enjoy hearing about this? Because I believe that table access controls could take the place of the view-based security set-up that is often found in a data warehouse system. I've run up against "security views" on more than one occasion, and they can be a big headache, with views on top of views on top of other views, creating a security scheme that is highly complex and which can sometimes get in the way of good query performance (and try digging through these layers of views to see how the associated predicates figure into the performance characteristics of a query that targets one of the top-level views). In a DB2 10 system, table controls can be implemented via DDL with no requirement for overlying views. The controls, which -- after being defined via new SQL statements CREATE PERMISSION (for row-access restrictions) and CREATE MASK (for column-access protection) -- are activated with an ALTER TABLE statement, can determine access to data based on any of several ID types, such as primary authorization ID, SQL ID, and role. The controls affect static and dynamic SQL statements, and they can be used to return "mask" data values to querying users (a mask is defined by way of an SQL case expression). If you have security views implemented in your environment today, read up on CREATE PERMISSION and CREATE MASK, and start thinking about how you might be able to eliminate your security views in favor of DB2 10 table controls.

What's new in DB2 10 for z/OS for DBAs. Jim Teng, IBM Distinguished Engineer, longtime leading member of the DB2 development team, and a longtime friend, gave an excellent presentation on DB2 10 features that DBAs are going to like. These features include the following:

• More online schema changes. The concept of "deferred" changes is introduced with DB2 10: you issue an ALTER TABLESPACE or ALTER INDEX statement to specify a change, and that change is put into effect by a subsequent online REORG of the target object. In this way, you can change the page size, DSSIZE, or SEGSIZE of a universal tablespace (UTS); the page size of an index; and the MAXPARTITIONS value for a partitioned tablespace. You can also convert a single-table simple or segmented tablespace to a UTS. MEMBER CLUSTER can be implemented for a partitioned-by-range (PBR) or partitioned-by-growth (PBG) UTS, or for a "classic" partitioned tablespace.
• Note that a pending object change can be dropped via ALTER before being realized through a REORG -- kind of like an "I changed my mind" capability.
• One DB2 10 ALTER TABLESPACE or ALTER INDEX statement cannot contain both deferred and immediate actions; furthermore, many immediate-effect DDL statements cannot be executed while there is pending DDL waiting to be put into effect via online REORG.
• There are also some new immediate-effect online schema changes made possible by DB2 10. Among these changes are the addition of INCLUDE columns to an existing unique indexed key (though the benefit of increased incidence of index-only access a REBUILD INDEX and a rebind (if we're talking about static SQL). Also immediate is the change to HASH for a UTS (though the table's rows can't be accessed by way of the hash key until the tablespace has been REORGed. Similarly, a change to inline LOBs for a tablespace is immediate, but existing LOB values that can be in-lined won't be until the tablespace is REORGed.
• With DB2 10, when a tablespace or index is altered to reassign it to a different buffer pool (with the same page size), that change takes effect immediately, with no need to stop and start the object (in a pre-DB2 10 data sharing environment, the object had to be stopped BEFORE the ALTER with the BUFFERPOOL specification was issued). Any application processes accessing the target object are quiesced with a DRAIN(ALL), and the new buffer pool is used following commit processing.
• When data is inserted into a table with multiple indexes (two or more if a MEMBER CLUSTER tablespace or an APPEND table; otherwise, more than two), DB2 10 will update the table's indexes in parallel. For a table with quite a few indexes, this can have a big and positive effect on the performance of INSERT processes (some tests have shown an elapsed time improvement of 50% or more).
• DB2 10 will cache (i.e., remember the address in the buffer pool of) the root page of an index when the index is opened. This will eliminate one GETPAGE/release page operation for every subsequent access to the index (good for performance).
• DB2 10 can use list prefetch to efficiently retrieve entries from a disorganized index, thereby reducing the need to frequently reorganize indexes.
• A new DB2 10 trace record, IFCID 359, can be used to monitor the performance (including the elapsed time) of index page split operations.
• Avoidance of full buffer pool scans in a data sharing environment when, for example, a data set is closed, can significantly improve performance for large (i.e., 5 GB or more) buffer pools in a DB2 10 environment (and I'm talking here about the size of an individual pool, not the aggregate size of all pools in a configuration).
• A new buffer pool parameter setting, PGSTEAL(NO), can be used to "pin" a table in memory in a DB2 10 system. With PGSTEAL(NO) in effect, all of a table's pages will be read into memory, using prefetch engines, when the underlying data set is opened. Thereafter, prefetch is turned off for the table (so, make sure that the pool has enough buffers to hold the pages of objects assigned to it; otherwise, you could see a lot of random reads).
• The new "currently committed" locking option available with DB2 10 will enable an application to read previously committed data rows instead of waiting for inserting or deleting processes to release locks on pages or rows (this option can only be used with universal tablespaces). An application for which this option is in effect will still have to wait on locks held by data-updating processes.
• The auto-compress feature of DB2 10 enables a compression dictionary to be built for a tablespace based on INSERT activity, versus having to wait for a LOAD or a REORG. The dictionary is built by an asynchronous DB2 task. The number of row inserts needed to build a dictionary will vary -- it's based on real-time statistics information.
• DB2 10 supports the use of FlashCopy at the data set level.
• The catalog and directory objects in a DB2 10 system will be DB2- and SMS-managed (meaning, among other things, that they are STOGROUP-defined -- a ZPARM lets you specify a default STOGROUP to be used for this purpose).

DB2 10 for z/OS performance improvement. Akiko Hoshikawa joked that her husband tells her that her voice puts him to sleep, then proceeded to get an hold our attention with a lot of good information pertaining to performance improvements delivered by DB2 10. Some notes from the session:

• The virtual storage constraint relief provided by DB2 10 allows, among other things, a larger MAXKEEPD value (relevant for packages bound with KEEPDYNAMIC(YES)). This means more avoidance of "short prepares," and that means better performance. In one SAP test, CPU time was reduced by 26%, just by increasing the value of the MAXKEEPD parameter in ZPARM to 64,000 from 8,000.
• The 1 MB real storage page frame size provided by the z10 and z196 (zEnterpise) servers (backed by 256 contiguous 4K real storage frames) boosts performance by reducing the incidence of "misses" versus "hits" in the translation lookaside buffer (this buffer is used to speed up the translation of virtual storage addresses to real storage addresses). Observed CPU savings tend to be in the 1-4% range. Utilization of 1 MB page frames requires a change to LFAREA in IEASYSxx, followed by an IPL.
• Akiko pointed out that the z196 mainframe supports up to 3 TB of memory.
• In a DB2 10 system, RUNSTATS will collect KEYCARD data by default.
• Hash access to data -- a new DB2 10 feature that causes data rows in a table to be physically placed based on the hashing of a unique key value -- is great for single-row retrieval. The flip side? It can be pretty bad for sequential data retrieval. So, choose carefully when deciding on tables for which you want to use hash access.

Time to call it a day. Back with more tomorrow.