IBM Software Group

1
IBM Software Group
IBM Technical Exchange Colorado DB2 User Group
Kevin Harrison Consulting IT Architect DB2 for
z/OS Technical Support Data Management West
Region kharrison_at_us.ibm.com
2
Software Announcements

• IBM Information on Demand
• http//www-306.ibm.com/software/data/ondemandbusin
  ess/index_products.html
• get the right information to the right people at
  the right time
• address the need to horizontally integrate
  information
• integrate, analyze, and optimize heterogeneous
  types and sources of business information
  throughout its lifecycle to manage risk and
  create new business insight.
• SOA
• Information as a service
• z/OS new software offerings
• Architectural Premise
• http//www-306.ibm.com/software/solutions/soa/

3
IBM Information on Demand New Software Portfolio

• Business Process Innovation - Built on IBM's deep
  industry expertise and industry specific
  Information Framework Process and Data models
  these solutions focus on the integration of on
  demand information services such as search,
  analytics, data placement and synchronization to
  assure the right time delivery of information
  in-line and in-context for analysis.
• Business Analysis and Discovery - IBM's Business
  Analysis and Discovery solutions go beyond static
  reporting and information access to provide the
  industry's most advanced engine for analysis and
  discovery by enabling information to flow across
  the enterprise.
• Master Data Management - IBM's Master Data
  Management solution provides a complete set of
  services, technologies and solutions to create
  and maintain a "single version of truth" for
  enterprise data domains such as customer,
  product, supplier and constituent for all
  stakeholders across and beyond the enterprise.
• Risk Compliance - IBM's Risk and Compliance
  Management Solutions enable clients to make the
  collection of compliance data a key business
  process, to increase the speed and cost
  effectiveness of meeting compliance requirements.
  
• Work Force Productivity - These solutions are
  focused on attacking information overload in
  today's work environments to help workers spend
  time using enhanced information to make better
  business decisions, not be overwhelmed by it. Our
  productivity solutions define opportunities to
  create employee work environments that are
  focused, relevant and actionable for all
  stakeholders to enable information to be used in
  smart and intelligent ways
• Business Performance Process Management - These
  IBM solutions are built upon sense and respond
  frameworks to realize the adaptive, rather than
  reactive, enterprise. They provide line of
  business executives with critical information
  drawn and analyzed from across the enterprise to
  respond quickly to problems and exploit
  opportunities - offering insight never previously
  availed to power innovation for competitive
  advantage.

4
Java Usage

• zAAP
• Whitepaper references
• http//www-03.ibm.com/servers/eserver/zseries/zaap
  /faq/faq19
• SQLJ vs JDBC
• More and More questions on this topic
• It is a somewhat controversial argument
• SQLJ has definite advantages and is growing in
  popularity
• JDBC has easier prep process

5
WLM Application Environments
6
Maintenance

• Maintenance level
• If You are on V8 you should be at 0511
• Most major performance PTFs
• Storage and CPU reductions
• UK09097 gtgt PK14393
• LC32 for sequential Prefetch
• UK11702 gtgt PK14254
• The infamous SQL -189
• Toleration of CCSID 0
• Only if DB2 coprocessor is utilized

7
Maintenance
8
Migration Notes

• Reduction in IRLM
• You will note a reduction in locks and overall
  IRLM CPU
• Bind Performance
• BIND performance can be up to 10x faster
• Given the fact that in order to pick up fast
  column processing you must BIND
• Have to BIND to pick up CM functionality
• CPU usage
• If you dont REBIND regression could be higher
• Fetch intensive workloads can be higher
• Maintenance relieves stress
• Puffing of DBRMs
• Without rebind the skeletons in the EDM Pool will
  be puffed up to look like V8 structures when they
  are loaded.
• This applies to all modes of V8.
• The puffing effect in terms of extra CPU is very
  minor

9
DB2 Universal Database for z/OSDB2 for z/OS and
OS/390 - Product Lifecycle 

• Last Updated January 23, 2006
• The DB2 UDB for z/OS Product Lifecycle matrix
  displays the marketing and support services
  available to you over the life of your product.
  Hyperlinks are enabled to take you directly to
  the product announcement letter by simply
  selecting any of the dates listed. If your
  product or version is not listed on the Product
  Lifecycle chart below, it has probably reached
  End of Service. While every effort has been made
  to provide accurate information, the
  authoritative source for product information is
  the IBM announcement letters.
• All statements regarding the future direction and
  intent of IBM are subject to change or withdrawal
  without notice and represent goals and objectives
  only.

10
DB2 Universal Database for z/OSDB2 for z/OS and
OS/390 - Product Lifecycle  (cont.)
Indicates projected date. Actual end of
marketing or end of service date has not been
announced yet.
11
New features in DB2 for z/OS V9 DB2 Tools
continue to help solve your information-intensive
business problems
12
Managing and Protecting Your VSAM Data 
13
DB2 for z/OS and DB2 Tools A Common Cents
Solution to Reduce Cost and Complexity 
14
Software Announcements Links

• SW Webcasts
• http//www-306.ibm.com/software/os/zseries/events/
  
• HW Announcements z9
• http//www-03.ibm.com/systems/z/feature112905/
• http//www-03.ibm.com/systems/systemz9/z9109/
• DB2 V8 Library refresh 2/2006
• http//www-306.ibm.com/software/data/db2/zos/v8boo
  ks.html

15
Preventing COPY SHRLEVEL(CHANGE) from keeping
objects serialized for the entire duration of the
COPY
Problem The COPY utility with the
SHRLEVEL(CHANGE) option keeps all of the objects
in my list serialized (in UTRW status) for the
entire duration of the utility. These objects
cannot be used by other utility operations, such
as LOAD or REORG, which are not compatible with
COPY. How can I prevent COPY from keeping objects
serialized for the entire duration of the COPY
utility? Solution Specify the statement OPTIONS
EVENT(ITEMERROR,SKIP) before the COPY statement
with the SHRLEVEL(CHANGE) option. If you specify
OPTIONS EVENT(ITEMERROR,SKIP), each object in the
list is placed in UTRW status and the read claim
class is held only while the object is being
copied. If you do not specify OPTIONS
EVENT(ITEMERROR,SKIP), all of the objects in the
list are placed in UTRW status and the read claim
class is held on all objects for the entire
duration of the COPY utility. For more details,
see APAR PQ74111
16
FAQ and more !!!

• Technical support page has a lot of usage
  information
• It is DB2 z/OS Central
• http//www-306.ibm.com/software/data/db2/zos/suppo
  rt.html
• Highlights
• How the EDMPOOL actually works in DB2
  presentation
• Discussion of CURRENTDATA
• CCSID Casting
• Thread States
• And all sorts of stuff gt Presentations, FAQs,
  User examples

17
IBM System z9, z/OS DB2 for z/OS

• New backup and restore
• Multilevel Security
• Unicode conversion
• Compression
• zSeries Application Assist Processor
• z/Architecture new instructions
• WLM enhanced

• IBM System z9 Integrated Information Processor
  (zIIP)
• Enhanced Cryptography
• Enhanced channels
• Faster Processors, up to 54
• More memory, better value, 64-bit virtual storage

18
Specialty engines

• IBM System z9 Integrated Information Processor
  (IBM zIIP) (planned for 2006)
• Designed to improve resource optimization
• Can help lower cost of computing for eligible
  workloads
• Requires z/OS 1.6
• First IBM exploiter will be DB2 for z/OS V8
• System z9 Application Assist Processor (zAAP)
• zAAPs support Java code execution
• z/OS Java Virtual Machines (JVMs) assist with the
  execution of code from standard processors to
  zAAPs
• JVM executes the Java code on the zAAP
• Designed to provide a Single Tier integrated
  application and database serving environment
• Requires z/OS 1.6
• Integrated Facility for Linux (IFL)
• Provides additional processing capacity
  exclusively for Linux workloads
• Runs Linux native or as a guest of z/VM V4 and
  V5
• Internal Coupling Facility (ICF)
• Provides additional processing capacity used for
  coupling to other processors

19
(No Transcript)
20
(No Transcript)
21
Specialty engines
z/OS LPAR WAS Application
zAAP
TCP/IP HiperSockets
z/OS LPAR
The IBM System z9 specialty engines can operate
on the same machine together (shown at left,
instances where zIIP can be employed)
DRDA
DB2
Application
WAS Application
Application not on System z
D R D A
TCP/IP
CP
zAAP
Star Schema
zIIP
DRDA
TCP/IP HiperSockets
Linux on System z LPAR Application
IFL
22
New IBM System z9 Integrated Information
Processor (IBM zIIP)

• New specialty engine for the System z9 mainframe
  (planned for 2006) designed to help
• Customers integrate data across the enterprise
• Improve resource optimization and lower the cost
  of ownership for eligible data serving workloads
• z/OS manages and directs work between the general
  purpose processor and the zIIP
• Number of zIIPs per z9-109 not to exceed number
  of standard processors
• No changes anticipated to DB2 for z/OS V8
  applications
• DB2 for z/OS V8 will be first IBM exploiter of
  the zIIP with
• System z9 109
• z/OS 1.6 or later
• DB2 for z/OS V8

23
Types of DB2 for z/OS workloads that may benefit
from zIIP

• ERP or CRM application serving
• For applications, running on z/OS, UNIX, Linux,
  Intel, or Linux on System z that access DB2 for
  z/OS V8 on a System z9 109, via DRDA over a
  TCP/IP connection. DB2 gives z/OS the necessary
  information to have portions of these SQL
  requests directed to the zIIP

• Data warehousing applications
• Requests that utilize DB2 for z/OS V8 complex
  star schema parallel queries may have portions of
  these SQL requests directed to the zIIP when DB2
  gives z/OS the necessary information
• Some DB2 for z/OS V8 utilities
• A portion of DB2 utility functions used to
  maintain index maintenance structures (LOAD,
  REORG, and REBUILD INDEX) typically run during
  batch, can be redirected to zIIP.

The zIIP is designed so that a program can work
with z/OS to have all or a portion of its
Service Request Block (SRB) enclave work directed
to the zIIP. The above types of DB2 V8 work are
those executing in SRB enclaves, portions of
which can be sent to the zIIP.
24
How does the zIIP work .....

• The zIIP is designed so that a program can work
  with z/OS to have all or a portion of its enclave
  Service Request Block (SRB) work directed to the
  zIIP. The types of DB2 V8 work listed below are
  those executing in enclave SRBs, portions of
  which can be sent to the zIIP.
• Example 1 Distributed SQL requests (DRDA)
• Queries that access DB2 for z/OS V8 via DRDA over
  a TCP/IP connection are dispatched within z/OS in
  enclave SRBs. z/OS directs a portion of this work
  to the zIIP.
• Example 2 Complex parallel query (BI)
• Complex star schema parallel queries will now use
  enclave SRBs. z/OS directs a portion of this work
  to the zIIP.
• Example 3 DB2 utilities for index maintenance
• DB2 utilities LOAD, REORG, and REBUILD will now
  use enclave SRBs for the portion of the
  processing that is related to index maintenance.
  z/OS directs a portion of this work to the zIIP.

25
Important technical notes

• Utilization of the zIIP is expected to be
  transparent to the application.
• No anticipated changes to applications that use
  DB2 for z/OS V8
• The enclave SRB interface is available upon
  request to non-IBM vendors as well.
• ISVs are interested

26
Some instances where zIIP would not be exploited
z/OS LPAR

• WAS applications may access with a local Type2
  JDBC access. For performance reasons, this is
  the recommended scenario as the WAS code was
  optimized for this configuration. No zIIP would
  be needed here.

WAS Application
DB2
zAAP
Batch
QMF (TSO)
CP
CP
IMS
CICS
CP
CP
27

• Contact your regional DB2 Engine Specialist to
  assist with Data Gathering and Analysis
• Engine Specialist contacts SVL coordinator for
  analysis or performs analysis locally
• Provide info on LPAR config, DB2 related WLM
  service class and reporting class to cross check
  with DB2 data analysis
• May have to modify WLM to add reporting classes
• For estimation of Utilities and Star Schema work
  with local DB2 Engine Specialist for estimation
• Make no assumptions about your eligible workload
  without involving DB2 Engine Specialist who will
  work with SVL

28
SQL Replication
Trigger
Staging Table
Trigger based
Federation Engine
Log
Log based
Nicknames
Nicknames
29
What is Q Replication ?
agent
Log
agent
agent
Log based
WebSphere MQ
Informix
Oracle

• New replication architecture
• High throughput and low latency
• Multi-directional replication
• Event Publishing
• 1 transaction per message
• Highly parallel apply process
• Differentiated conflict detection and resolution

SQL Server
Sybase
Nicknames
30
DB2 LUW VIPER
31
XML in DB2

• "Feels" relational and/or XML
• Both SQL flavor and fully XML flavor
• XML is DB2 internals - XML Extender becomes one
  with the data engine

SERVER
CLIENT
SQL(X)
DB2 Server
XQuery
32
Table Partitioning

• What is Table (Range) Partitioning ?
• Storing a table in more than one physical object,
  across one or more table spaces
• Each table space contains a range of the data
  that can be found very efficiently
• Why?
• Increase table capacity limit
• Increase large table manageability
• Improve SQL performance through partition
  elimination
• Provide fast online data roll-in and roll-out
• Converge towards Informix functionality
• Family compatibility with DB2 on zOS and IDS

33
Large Row Identifiers

• Increase In table size limits and rows per page
• Tablespace level definition
• DMS Tablespace only
• ALTER TABLESPACE ltnamegt CONVERT TO LARGE
• Tablespace is locked, definition is modified and
  catalogues are updated
• Indexes will need to be reorganized
• Every index for every table in the converted
  tablespace needs to be reorganized or rebuilt to
  convert the RID entries from regular to large

34
Previous Table Space Design
64GB
4KB
8KB
128GB
16KB
256GB
32KB
512GB
Table space size
Page size
4x109 Rows
255
16M
Row ID (RID) 4 Bytes
For tables in all table spaces (regular,
temporary, DMS, SMS)
35
New Large and Temporary Table Space Design
2TB
4KB
8KB
4TB
16KB
8TB
32KB
16TB
Table space size
Page size
1.1x1012 Rows
512M
2K
Row ID (RID) 6 Bytes
For tables in LARGE table spaces (DMS only) Also
all SYSTEM and USER temporary table spaces
36
Row Compression

• Dictionary based - symbol table for
  compressing/decompressing data records
• Lempel-Ziv (LZ) based algorithm (static
  dictionary)
• Dictionary per table stored within the permanent
  table object (74KB in size)
• Data resides compressed on pages (both on-disk
  and in bufferpool)
• Significant I/O bandwidth savings
• Significant memory savings
• CPU costs Rows must be decompressed before being
  processed for evaluation
• Log data from compressed records in compressed
  format
• Does not compress rows where no storage saving is
  realized for that row
• Repeating patterns within the data (and just
  within each row) is the key to good compression.
  Text data tends to compress well because of
  reoccurring strings as well as data with lots of
  repeating characters, leading or trailing blanks

37
Education

• DB2 Version 8 Migration Workshops
• Let us know if you need one
• DB2 Version 8 Transition class
• CG381
• IBM DB2 Information Management Technical
  Conference
• October 15-20, 2006
• Anaheim, CA
• http//www-304.ibm.com/jct03001c/services/learning
  /
• IDUG
• May 7-11, 2006
• Tampa, FL
• w3.idug.org/na

38
Best Practices Workshop for DB2 z/OS

• zSeries skills aging/retiring/brand new and some
  of the basics are overlooked
• Not a Health Check
• 2-3 days onsite
• Review of your DB2 Usage
• Zparms
• Subsystem
• Tooling
• Hardware/Software feature usage
• z/OS
• DASD features, zSeries exploitation
• Monitoring strategy
• How you can Exploit and Improve
• Part of IBM zIAW
• IBM may propose software/hardware solutions

39
DB2 Redbooks - 2Q 2004 http//www.ibm.com/redboo
ks

• Coupling Facility Performance A Real World
  Perspective (REDP-4414)
• This Redpaper can help you understand the
  Coupling Facility technology and how different
  configuration options can affect your workload.
  It describes how to configure your Coupling
  Facility to enhance performance and high
  availability in a zSeries Parallel Sysplex
  environment.
• DB2 UDB for z/OS Design Guidelines for High
  Performance and Availability (SG24-7134)
• Discussion of the techniques and guidelines for
  database and application design. We expect the
  best practices described in this IBM Redbook will
  help DB2 professionals design high-performance
  and high-availability applications.
• DB2 UDB for z/OS Version 8 Performance Topics
  (SG24-6465)
• February Update
• DB2 UDB for z/OS Stored Procedures Through the
  Call and Beyond (SG24-7083)
• February Update
• DB2 Recovery with DB2 for z/OS (SG24-6370)
• The main concepts, and the primary components for
  possible solutions. We then describe the most
  common solutions, and implement several recovery
  scenarios. All tests were implemented with DB2
  UDB for z/OS Version 8. Includes criteria for
  choosing a solution, and recommendations based on
  recovery best practices.
• DB2 for z/OS and WebSphere The Perfect Couple
  (SG24-6319)
• Provides a broad understanding of the
  installation, configuration, and use of the IBM
  DB2 Universal Driver for SQLJ and JDBC in a DB2
  for z/OS and OS/390 Version 7, and DB2 for z/OS
  Version 8 environment, with IBM WebSphere
  Application Server for z/OS for z/OS Version
  5.02. It describes both type 2 and type 4
  connectivity (including the XA transaction
  support) from a WebSphere Application Server on
  z/OS to a DB2 for z/OS and OS/390 database
  server. Demonstrates the advantages of SQLJ in a
  DB2 environment, the SQLJ support in the IBM
  application development tool WebSphere Studio
  Application Developer, as well as the SQLJ
  support for Enterprise JavaBeans using
  container-managed persistence.

40
Reference URLs
http//www.ibm.com/software/data/db2/zos/index.htm
l ibm.com/software/db2zos
DB2 for z/OS ibm.com/software/db2zos/db2zosv8.ht
ml V8 ibm.com/software/db2zos/support.html
Support ibm.com/software/data/db2imstools/
Tools ibm.com/developerworks/db2
Applications ftp site for papers,
presentations Unicode http//www-06.ibm.com/de
veloperworks/library/whyunicode.html