DB2_Performance-DB2_Users_Group by yvtong


									DB2 Users Group

    October 13, 2005

           General Announcements

      • October 13 – IBM Teleconference 12:30
        p.m. (today) – DB2 Utilities
      • October 16 - BMC Apptune 5.1 – SYSW
      • October 16 – CA-Insight DB2 Performance
        Monitor Version 11 – SYSA, B, E, S
      • October 20 – SIRDUG, Charlotte, NC
        www.sirdug.org - Terry Purcell

Version 11 is already on SYSW. Please test your usual CA-Insight functions
on the W-lpar and report any issues to the ITS Customer Support Center

SIRDUG – Terry Purcell:
Can My Where Clause Use an Index?
What Statistics are Needed by the Optimizer?
How to Tune SQL Like an Expert in DB2 V8
Query Tuning When You Can't Change the SQL (indexing, clustering,

            General Announcements
       • October 25 11:00 a.m.– Webcast Optimizing
         your performance with DB2-UDB for z/OS
         Version 8
       • November 20 – BMC Apptune 5.1 – Production
       • December 4 – BMC Apptune 5.1 – Production

       • DB2 UDB for z/OS V8 on Book Manager
       • January 22, 2006 - DB2 UDB V8 Compatibility
         mode on SYSW

Hiperlink for zSeries Webcast offerings on last slide will take you to the
primary URL, or

To register select Optimizing your performance with DB2-UDB for z/OS
Version 8 from the list.

There is a non-streaming phone option during the live event for those who
cannot stream the webcast.

The webcast will discuss many improvements in optimization, including
improved ability to use indexes, ability to have the optimizer use previously
computed information, improved statistics for better optimization and
improved techniques. Techniques to fetch and insert multiple rows can save
up to 50% of the cpu time. There are great new opportunities for
improvements with new indexing and partitioning options, improved storage
structure with 64 bit virtual storage and ways to improve IO.

                    Next Meetings

      • November 10th (?)
      • December 8th – last day of Business
        Recovery test
      • January 12, 2006 – Last meeting before
        conversion of SYSW
      • February 9, 2006 – IBM presentation on
        new SQL functionality – Application

1. Do we need to November 10th meeting?
2. February 9, 2006 – How many developers from each agency?

           Migration Considerations

       • Importance of testing on SYSW
       • What do you get with V8 CM
           – Exploitation of 64-bit virtual in DB2
           – New access paths
           – New utility function in V8 CM
           – No new SQL function
           – Optional : Long term PGFIX

“What do you get with V8 CM” list from IBM presented at TRIDUG (DB2
UDB for z/OS V8 Early Experiences by Mark Rader)

Long term PGFIX
 Bufferpool option
 Research to determine best application
 Must be backed by 100% real storage
  Performance benefit is inversely proportional to the buffer hit ratio (Higher
the hit ratio, lower the benefit)
 Best application is small buffer pools with lots of I/Os (Sequential page

           Migration Considerations

      • Programs/subprograms called by a stored
        procedure require local packages (bind)
      • Verify references to SQLDA
          – UNION of labels/column names are handled
            differently when referencing SQLDA
          – May contain truncated data

Programs/subprograms – This will need to be tested on SYSW to determine
the impact if any to your applications.

(Information found in DB2 V8 Release Planning Guide)
SQLDA may contain truncated data: In Version 8, the length of many
names has been extended. However, for compatibility with prior releases,
the length of name fields in the SQLDA is not changing. Truncation of names
that are stored in the SQLDA might occur with distinct name types. To avoid
truncation of distinct type names in the SQLDA, you should not use distinct
name types that are longer than 30 bytes.

           Migration Considerations

       • Procedures and Functions
           – Fully qualify user-defined functions.
           – External name <= 8 bytes if not Java
           – New options
       • Warning message on CHAR function if
         non-blank characters over 255 bytes are
         truncated. Currently issue error message
         in V7.

Procedures and Functions (DB2 V8 Release Planning Guide):
Fully qualify user-defined functions – With each new release there are
new system provided built-in functions. If you do not fully qualify your user-
defined functions, the default path may route your execution to the built-in
If your routine is written in a language other than Java, the external name is
a load module which must be less than or equal to 8 bytes. The external
name must contain characters that are valid for a z/OS load module. A
procedure name cannot consist of a single asterisk.
New options for Procedures and Functions (DB2 V8 SQL Reference)
Specifies whether the routine is to be put in a stopped state after some
number of failures.
STOP AFTER SYSTEM DEFAULT FAILURES Specifies that this routine
should be placed in a stopped state after the number of failures indicated by
the value of field MAX ABEND COUNT on installation panel DSNTIPX. This
is the default.
STOP AFTER nn FAILURES Specifies that this routine should be placed in
a stopped state after nn failures. The value nn can be an integer from 1 to
CONTINUE AFTER FAILURE Specifies that this routine should not be
placed in a stopped state after any failure.
           Migration Considerations

      • New SQL reserved words – Appendix B of
        the SQL Reference manual
      • Cross-Platform reserved words –
        Appendix O (oh) of SQL Reference for
        Cross-Platform Development

New SQL reserved words :
ASENSITIVE                 ENCRYPTION                ENDING
EXCEPTION                  HOLD                      INCLUSIVE
ITERATE                    MAINTAINED                MATERIALIZED
NEXTVAL                    NONE                      PADDED
PARTITION                  PARTITIONED               PARTITIONING
PREVVAL                    QUERY                     REFRESH
RESIGNAL                   ROWSET                    SECQTY
SECURITY                   SEQUENCE                  SIGNAL
SUMMARY                    VALUE                     VARIABLE
VOLATILE                   XMLELEMENT

Cross-Platform reserved words (SQL Reference for Cross-Platform
Development Version 2)

               Unicode Conversions

       • Static SQL may be impacted by Unicode
         conversions at BIND time.
       • Dynamic SQL may be impacted by
         Unicode conversions at Prepare time
         (usually at execution).
          – Tests to include impact on dynamic SQL with
            respect to the statement caching options
          – Only DB53 on SYSW and DB05 in production
            have a type of statement caching turned on.

There are two charts and some explanation provided in the redbook “DB2
UDB for z/OS Version 8: Everything You Ever Wanted to Know, … and
More”, pages 538 and 539 (Adobe pgs, 568 and 569). I contacted IBM to
get a more detailed explanation of the program preparation process.
The chart on page 538 is misleading in that it indicates that the complete
program source is converted to some form of Unicode. From an email
conversation with John J. Campbell (Distinguished Engineer, DB2 UDB for
z/OS Development, IBM Silicon Valley Laboratory), “The modified COBOL
source generated by V8 Precompiler is never converted to UNICODE.”
The chart on page 539 is slightly ambiguous. John provides this more
detailed explanation, “After entry into V8, even in CMP, all SQL is parsed
internally by DB2 in UNICODE and the resultant metadata is in UNICODE
format. Parsing occurs during PREPARE/EXECUTE IMMEDIATE (dynamic
SQL) and BIND (static SQL). The CPU cost of full prepare and BIND will
increase. Prior to NFM when NEWFUN(NO) is the default, the derived
metadata will be converted back to EBCDIC to put in DBRM.
Statement caching is explained in detail in the redbook, “Squeezing the
Most Out of Dynamic SQL”, Chapter 8

  Hyperlinks to References

• DB2 UDB V8 Manuals
• SQL Reference for Cross-Platform
  Development Version 2
• zSeries Webcast offerings
• Everything You Ever Wanted to Know,
  …and More
• Squeezing the Most Out of Dynamic SQL


To top