What DB2 for z/OS Version 8 Can Do For You
(a DBA’s
perspective)
By
Todd Blandford
DB2 z/OS Version 8 has been Generally Available for over a year now (March 26, 2004). By now, most users of previous versions of DB2 z/OS have heard of the major enhancements to the areas of Scalability, Availability, Recoverability and Performance that this new version brings. DB2 V8 is the largest release of DB2 to date. It incorporates the major changes of 64-bit processing, on-line schema evolution and Unicode for the Catalog.
The following article highlights some of the lesser-known features of DB2 z/OS Version 8. These enhancements are not nearly as publicized as some of the other features of Version 8, but their benefits will serve to make your job easier and more productive once you migrate to V8! These enhancements will also serve to improve your system performance; as always though, your mileage may vary.
* Bufferpool Enhancements: PGFIX(YES):
Version 8 introduces a new option to the ALTER BUFFERPOOL command – PGFIX(YES).
By issuing the
–ALTER BUFFERPOOL(bpname)
VPSIZE(vpsize) PGFIX(YES)
command, you will turn on long term page
fixing for the particular Bufferpool. Page
fixing bufferpools in memory can lead to significant cpu savings, with some customers
seeing up to 8-10% cpu savings. Building on
the savings that can be realized in V6 with the choice of page stealing algorithms (LRU vs
FIFO), PGFIX(YES) puts the BP pages in memory and prevents the fixing and freeing
instructions that need to be executed each time there is an I/O.
One additional nice thing about PGFIX(YES), is that it is available immediately in Version 8 – i.e., in Compatibility Mode. You don’t have to wait for ENFM or NFM to make use of this feature and savings.
A few recommendations:
- Make sure that you have enough real storage. DB2 V8 will allow you to alter a BP to PGFIX(YES) as long as it represents less than 80% of the real storage available. Message DSNB541I will be issued, and the ALTER will fail if you try to allocate more than 80%. That is still a large amount of real storage for an LPAR to be dedicated to one DB2 subsystem. Check with your friendly neighborhood Systems Programmer for a good recommendation on how big a BP you should use PGFIX(YES) with.
- Grow slow into this feature. Start with a small BP and monitor your system paging.
- Use for a BP that has a high I/O rate. To make good use of this feature, you need to be able have a BP that suffers from constant fixing and freeing of pages in memory due to I/O.
- Use with small BPs with a low hit ratio.
Available in Compatibility Mode (CM)
* Enhancements to BIND Parameter:
REOPT(ONCE/NONE/ALWAYS) :
Three new BIND parameters have been added to assist with your Dynamic SQL workloads. Prior to Version 8, your choices were REOPT(VARS) or NOREOPT(VARS), which gave you freedom to re-evaluate host variables at execution time (using REOPT(VARS) ), but if the statement was previously prepared and saved in the Global Prepared Cache, it would be re-prepared each time the statement was executed, even though the statement was just prepared.
REOPT(ONCE) solves that problem by allowing the access path to be prepared ONCE, and then stored in the Dynamic Cache. If the statement has not changed – i.e., the variables are the same – the prepare will only take place once.
REOPT(NONE) is the same is NOREOPT(VARS) in Version 7.
REOPT(ALWAYS) is the same as REOPT(VARS) in Version 7.
Available in New Function Mode (NFM).
* New Zparm: MGEXTSZ:
How many times have you been bit by the DSNP007I – EXTEND FAILED message because either the object was incorrectly sized or the data grew much quicker than you ever expected and the max extents of 255 is reached? MGEXTSZ allows DB2 to adjust the size of the secondary extent size as the extents are taking place. Specifying MGEXTSZ=YES will turn on this function globally, and starting with each new secondary extent, DB2 will increase the size of the SECQTY, if needed.
Available in Compatibility Mode (CM)
* Automatic Recovery of LPL pages:
Prior to Version 8, when any pages were placed in LPL status, they had to be manually recovered. New to V8 is the automatic LPL recovery process, where DB2 will try and recover the pages for you. Message DSNI021I is issued if the recovery is successful, message DSNI005I is it fails. New message, DSNB357I will inform you about pages that have been added to the LPL that might not be automatically recovered.
Available in New Function Mode (NFM)
* Mismatched Operands on Predicates:
In Version 7 and below, when a predicate’s length was longer than the column it was checking, the predicate was determined to be a Stage 2 predicate, and therefore, not indexable. I.e.,:
SELECT col1 FROM EMPLOYEE WHERE DEPARTID=’12345’. Assuming that in column DEPARTID in table EMPLOYEE was defined as CHAR(3), and that it had an index on it, Version 7 of DB2 would force a tablespace scan, because of the length of departid in the WHERE clause. Version 8 corrects this problem by allowing string types with the same CCSID to be stage 1, and indexable.
Available in Compatability Mode(CM)
* Multi-Row Fetch:
New to Version 8 is DB2’s ability to process multiple rows in a single FETCH statement. Prior to V8, a program had to issue multiple FETCH statements in order to read through a CURSOR’s result set. Now, you have the ability to retrieve what is called a rowset – which is a predefined (on the FETCH statement) number of Rows. This function allow the application programming staff a much more robust SQL engine, and can lead to better performance and reduced network activity that comes from repeated trips between the program and the database engine. New syntax has been added to the DECLARE CURSOR syntax: WITH ROWSET POSITIONING- which will allow for multi-row FETCHING. Once the CURSOR is defined this way, issue:
FETCH ROWSET FROM <cursor name> FOR 10 ROWS INTO …. ß this will return 10 rows (if available) at a time. Keep in mind, that you will need to have an array built for the expected result set.
Note: Multi-Row processing is also available for INSERT processing as well.
Available in New Function Mode (NFM)
* Backward Index Scan:
Prior to Version 8, if you wanted to avoid a SORT by creating an index – and you wanted to be able to use both ORDER BY … ASC or ORDER BY … DESC, you would need to create two separate indexes. Version 8 introduces the ability of using the same index to do backward index scans. Backward index scans can not only allow you to drop some un-needed indexes, but it can dramatically improve performance by taking advantage of dynamic prefetching and all that those benefits entail.
Available in Compatability Mode (CM)
DB2 z/OS Version 8 is by far the largest release to date. It is packed full of features that users, DBAs and Systems Programmers have been begging for. The above list is just a short version of some the new functions that will make all of our jobs easier. Please keep in mind that most of the functions above are not available until you get to full-blown Version 8 (NFM). However, the base engine of DB2 will be running as soon as you get to Compatibility Mode. So, any optimizer improvements will be available right then.
If you have any questions about the new functions listed above, or need any more information on Version 8 in general, please send me a quick email by clicking here.