Title: IBM Software Group
1IBM 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
2Software 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/
3IBM 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.
4Java 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
5WLM Application Environments
6Maintenance
- 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
7Maintenance
8Migration 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
9DB2 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.
10DB2 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.
11New features in DB2 for z/OS V9 DB2 Tools
continue to help solve your information-intensive
business problems
12Managing and Protecting Your VSAM DataÂ
13DB2 for z/OS and DB2 Tools A Common Cents
Solution to Reduce Cost and ComplexityÂ
14Software 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
15Preventing 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
16FAQ 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
17IBM 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
18Specialty 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)
21Specialty 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
22New 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
23Types 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.
24How 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.
25Important 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
26Some 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
28SQL Replication
Trigger
Staging Table
Trigger based
Federation Engine
Log
Log based
Nicknames
Nicknames
29What 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
30DB2 LUW VIPER
31XML 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
32Table 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
33Large 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
34Previous 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)
35New 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
36Row 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
37Education
- 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
38Best 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
39DB2 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.
40Reference 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