Donnerstag, 24 of April of 2014

Archives from month » Juli, 2012

Tuning SAP® with DB2® on AIX®

Performance analysis and tuning guide for SAP on DB2 and AIX.

The paper describes how to analyze performance indicators for SAP with DB2 on AIX, and it contains examples of typical problems and processes used to analyze them.

Tuning SAP® with DB2® on AIX®

by Mark Gordon, version 1.3, revised June 30, 2012
© Copyright IBM corporation, 2012

35% discount on new “DB2 Developer’s Guide”

DB2 Developer’s Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS, 6th Edition
By Craig S. Mullins

DB2 Developer’s Guide is the field’s #1 go-to source for on-the-job information on programming and administering DB2 on IBM z/OS mainframes. Now, three-time IBM Information Champion Craig S. Mullins has thoroughly updated this classic for DB2 v9 and v10. Mullins fully covers new DB2 innovations including temporal database support; hashing; universal tablespaces; pureXML; performance, security and governance improvements; new data types, and much more.

Test drive Chapter 10 to see for yourself why this popular book is in its 6th edition.

Then buy at a 35% discount using coupon code IBM6425 plus free ground shipping within the U.S.


Buy the book for $58.50 (with coupon)

Buy the eBook for $46.80 (with coupon)

DB2 10 for z/OS Explain Table Conversion required!

Each new release of DB2 introduces new columns or modifies existing columns in the EXPLAIN tables. DB2 has traditionally honored EXPLAIN tables in previous release formats, but it is best practice to use the current-release format in order to gather maximum benefit from your EXPLAIN data.

In addition, EXPLAIN tables were traditionally created in the EBCDIC encoding scheme because data in the DB2 catalog was EBCDIC encoded. Beginning with DB2 V8, almost all catalog data is Unicode encoded, and it is therefore increasingly beneficial to store EXPLAIN data in Unicode tables.

The APAR PK85068 deprecates use of EBCDIC EXPLAIN tables, and use of EXPLAIN tables in previous release formats. Beginning in DB2 V10 conversion mode, use of EXPLAIN tables in DB2 V7 or a prior-version format will fail with SQLCODE -20008, and use of EBCDIC EXPLAIN tables and EXPLAIN tables in DB2 V8 or V9 format will result in SQLCODE +20520. It is recommended that you begin to identify non-compliant tables and eliminate or convert them. To facilitate this task, this APAR provides the following:

  • Queries for discovering non-compliant tables. In DB2 V8 and V9, these queries are added to job DSNTIJPM. In DB2 V8 only, they are added to job DSNTIJP9.
  • A new sample job, DSNTIJXA, that drives a DB2 REXX exec called DSNTXTA to alter explain tables into current-version format. You can specify to convert all explain tables in DB2 or you can limit the conversion to a particular schema (creator).
  • Another new sample job, DSNTIJXB, that drives a DB2 REXX exec called DSNTXTB to generate statements for migrating data from EBCDIC explain tables in a specified schema. DSNTXTB produces two equivalent types statements:
    • Control statements for processing by the DB2 Utilities cross loader (EXEC SQL)
    • SQL statements for processing by SPUFI or a similar dynamic SQL tool.

These statements should not be processed before they are inspected and validated by the user.

  • A third new sample job, DSNTIJXC, that shows how to process the statements generated by running DSNTIJXB.

In general, use this process (specific details follow):

  • Identify non-compliant explain tables. Reports are provided in job DSNTIJPM (and in DSNTIJP9 in V8 only) that identify all schemas having one or more such tables.
  • Bring all explain tables into current release format. You can use job DSNTIJXA to bring all explain tables in a specified schema into current release format. To convert all non-compliant explain tables regardless of the schema, specify an asterisk as the schema.
  • Migrate all EBCDIC-encoded explain tables to Unicode. This is a two step process:
    1. Use job DSNTIJXB to generate DB2 cross loader control statements (and equivalent SQL statements) that can be used to copy all EBCDIC explain tables in a specified schema ID to Unicode equivalents. All explain tables belonging to the specified schema must first be in the current release format.
    2. After examing the control statements generated by DSNTIJXB, use job DSNTIJXC to process them. DSNTIJXC assumes that you have purchased the DB2 Utilities Suite. If you have not, you can use the equivalent SQL generated by DSNTIJXB. The process works as follows for each table:
      1. RENAME TABLE to append ‘_EBCDIC’ to the EBCDIC explain table. Renaming the EBCDIC table makes its original name available to its Unicode replacement.
      2. CREATE TABLE to create the explain table as a Unicode table
      3. CREATE AUX TABLE (if the table is a LOB table)
      4. CREATE INDEX for each index on the EBCDIC table. To avoid name conflicts, the new indexes are prefixed with DSNU. If the name already starts with DSN then a ‘U’ is inserted at the fourth position.
      5. DECLARE CURSOR on the EBCDIC table to extract the data
      6. LOAD DATA INTO the Unicode table, using the cursor on the _EBCDIC table
    3. After converting an EXPLAIN table from EBCDIC to Unicode, check for joins to this table. When you convert EXPLAIN tables to UNICODE, their applications which join with EXPLAIN tables can have different results due to the CCSID conversion. For more information, see ‘Objects with different CCSIDs in the same SQL statement’ in the DB2 Internationalization Guide.

Repeat these steps until all explain tables are Unicode encoded and in current release format.

For more information the IBM’s description of APAR PK85068: EXPLAIN TABLE MIGRATION.

Documents of the 2012′s BMC DB2 and Mainview User Meeting

Find below the free documents of the annual BMC DB2 and Mainview User Meeting in Munich, June 2012.

The Evolution of the Mainframe: Meeting the Business Requirements of the Future
Nick Pachnos, BMC Software
DB2 Track:
Mainview for DB2 10.1
Peter Plevka, BMC Software
MIPS Aufrüstung vermeiden
Hubertus Beucke, Beucke Unternehmensberatung
DB2 Backup and Recovery – Features you should already be exploiting
Ken McDonald, BMC Software
TRANSFORM your schema changes the fastest way possible
Chris Düllmann, BMC Software
DB2 V 10.1 NFM for z/OS Migration Project 41 Sysplex Groups, 137 Data Sharing Member
Peter Pätsch, Konrath + Pätsch Consultants
Data Surgery and Data Replication by Log Master for DB2
Ken McDonald, BMC Software
Mainview Track:
How to Monitor the z196
Yves Colliard, YCOS
Reduzierung von Spitzenlast MIPS
Peter Plevka, BMC Software
MVSRM – More than Stop-X37
Josef Domes, BMC Software
BMC Mainview Console Management for zEnterprise (aka IOConcepts)
Axel Griepenstroh, BMC Software
Mainview Roadmap
Jürgen Hofmann, BMC Software