syncsort and db2 – how to get more from your db2 systems
TRANSCRIPT
Syncsort and DB2 – How to Get More from Your DB2 Systems
Bill Bostridge, Director North American Mainframe Sales
Ed Hallock, Director of Product Marketing & Management
2
Today’s Presenters
Syncsort Confidential and Proprietary - do not copy or distribute
Ed Hallock is a highly experienced Information Technology Professional with a broad
experience base in software product development, support, product management, marketing, and business development. In his diverse career Ed has benefited from working for some of the largest independent software vendors, in a variety of roles, providing enterprise solutions to Global 1000 corporations. Ed has extensive experience in performance and availability management for systems and applications. He holds a bachelor’s degree in Computer Science from Montclair State University in Upper Montclair, New Jersey and has presented at numerous industry events as well as corporate related conferences and seminars.
Bill Bostridge Bill Bostridge leads the North American sales team for Syncsort and has over
30 years of technology experience in both the distributed and IBM mainframe environment.
Bill came to Syncsort 4 years ago following the acquisition of his company Circle Computer Group, specialists in a unique migration strategy for legacy IMS and VSAM application databases to DB2. Since that initial acquisition, Syncsort has grown the mainframe software portfolio with additional strategic acquisitions and organic development activity to create a world class suite of solutions. Bill and his team work closely with our customers to introduce these new products and services, all with the aim of helping to improve efficiencies and reduce mainframe computing costs.
3
Agenda
• EZ-DB2
• CPU workload-centric optimization for DB2
•DL/2
• The best approach to migrate IMS data to DB2
• Standardize application change & enhancement in SQL
•Q&A
Syncsort Confidential and Proprietary - do not copy or distribute
CPU Workload-centric Optimization for DB2
5
DBA Challenges
Growing volumes of dynamic SQLs impacting the environment
– distributed Java and .Net applications
– ad hoc query reporting tools
– packaged applications
Ensuring SQL performance meets SLAs and other service-quality goals
Avoiding excessive CPU consumption
Avoiding or postponing costly upgrades
Operating with reduced staff levels and less than adequate optimization tools
Syncsort Confidential and Proprietary - do not copy or distribute
6
What is EZ-DB2?
Syncsort Confidential and Proprietary - do not copy or distribute
• Leading solution for DB2 SQL workload performance optimization on z/OS
• Saves DB2 CPU resources
• Improves performance
• Reduces the impact of application and environmental changes
7
What EZ-DB2 Does…
Captures and consolidates static and dynamic SQL including SQL originating from distributed environments
Reveals the “hidden” SQL statements that consume the most system resources.
Automatically and intelligently derives the optimal index design based your actual SQL workload and production data characteristics.
Anticipates how environmental changes such as migration of application changes from Dev/Test to production may affect performance and resource consumption.
Syncsort Confidential and Proprietary - do not copy or distribute
8
EZ-DB2 Workload Analysis In Action
3.) Consolidation for Effective Workload Analysis
EZ-Tracer
EZ-Cache
EZ-ImpactAnalyzer
Weighted Access PathImpact Cost
EZ-XOPIndex
Optimizer
WeightedIndex
Design
Consolidated‘weighted’SQL Workload
EZ-SQLWarehouse
WeightedTop ‘n’
ResourceConsumers
Weighted Predicate Set
Analysis
EZ-IndexAnalyzer
RawSQL WorkloadDynamic/Static
Dynamic(DB2 Cache)
Dynamic & Static( DB2
Performance Trace)
Static SQL from DB2
Catalog
Free Form SQL / Other ISV Tools
Syncsort Confidential and Proprietary - do not copy or distribute
1.) Static & Dynamic SQL collected by EZ-Cache & EZ-Tracer
2.) Merge with SQL from DB2 CTLG & other sources
4.) Consolidated workload used for:– Analyzing and optimizing
indexes
– Determining access path impact from changes
9
What Customers Say About EZ-DB2
Syncsort Confidential and Proprietary - do not copy or distribute
“We were able to reclaim the cost of EZ-DB2 in production CPU savings within 5 months of installing the product and have gone
on to see significant extra savings in the following months.”
“In just over a month using EZ-DB2, the Company identified in excess of 35% DB2 CPU savings. These savings were eventually
extended to 50%. CPU reduction was accomplished with no adverse impact on application performance.”
“Just last month EZ DB2 identified an SQL that decided to go south after running for several months ‘with no problems’ and was pegging out both of our machines in data sharing. Quick 5 minute trace to identify bad SQL, add an index and minor SQL change and problem gone. EZ DB2 to the rescue!”
10
Why EZ-DB2?
✓Improve performance and reduce DB2 resource utilization
✓Reduce CPU costs and improve throughput
✓Improve system stability and delay CPU upgrades
✓Avoid potentially detrimental changes to applications and program SQL
✓Maintain DB2 system integrity when making environment changes or migrating applications between subsystems
✓Ensure correct index design for new and existing applications
✓Automated optimization of DB2 index designs
Syncsort Confidential and Proprietary - do not copy or distribute
Migrating IMS to DB2Without Rewriting Applications
12
IMS – Its been a great workhorse but…
IMS is an aging hierarchical platform that provides minimal scope
for modernization
Rewriting these applications is expensive
Rewrite projects in many cases overrun both in time and cost
High risk involved in a “big bang” rewrite approach
Replace with 3rd party COTS software
Does not fully meet the requirements of the business
Requires significant work to retro fit to mirror existing
functionality
Significant cost in both software and services to implement
Syncsort Confidential and Proprietary - do not copy or distribute
13
Who is going to support your IMS applications?
IMS Skills
Staff that wrote original applications and support these
aging systems are heading off to retirement
In many cases, reliant on 3rd party vendors to support IMS
Skills drain to support legacy applications is a significant risk
to the enterprise
On-going IMS costs
IMS MLC costs, IMS support tools, IMS Upgrade costs
IMS DB DBA Support and Applications Support
These costs will continue month on month, year on year and
IBM and your IMS tools vendor knows this!!
Syncsort Confidential and Proprietary - do not copy or distribute
14
The low risk alternative – Syncsort DL/2
Migration to DB2 without touching application code
This provides a low risk alternative to eliminate IMS DB and 3rd
party support tools and significantly reduce on-going costs
Significantly reduces the reliance on IMS skills
Opens up data in a DB2 relational model to support new business
initiatives
Removes requirements for any current data replication to DB2
Enables application change to shift to SQL/DB2 skills
Over time provides an orderly move toward native SQL/DB2
applications, if required
Syncsort Confidential and Proprietary - do not copy or distribute
15
DL/2: Transparent Data Migration to DB2®
✓Migrate IMS Segments to DB2 tables without making anyapplication changes
✓ Eliminate IMS DB and 3rd party IMS support tools
✓Reduce overall application maintenance tools
✓ Syncsort DL2 is not a replication tool, it is a replacement for IMS DB
Syncsort Confidential and Proprietary - do not copy or distribute
16
What Do We Mean by Transparent Data Migration?
• No modifications, compiles, recompiles, link-edits of existing
application programs
• Migrate at your own pace – one file at a time, multiple files
• Environmental changes for batch JCL and CICS can be made in non-
disruptively in advance
• Simple switch between IMS/VSAM and DB2 for application testing
• Fallback capability
• Easiest and fastest route to DB2 and value delivery
LOWEST RISK MIGRATION APPROACH – PROVEN TIME AND TIME AGAIN
Syncsort Confidential and Proprietary - do not copy or distribute
17
DL/2: Transparent Migration Process Overview
Before During After
IMS
NO
DB2
YES
DL2 Stub
Application Program
Static SQL
Datain
DB2?
IMS Stub
Application Program
DL2 Stub
Application Program
IMSDB2
Syncsort Confidential and Proprietary - do not copy or distribute
18
Accessing IMS Databases
DBCTL
DLISAS DBRC
IMS/DC
CICS/TS
BMP
DLI/DBB
BATCH
Syncsort Confidential and Proprietary - do not copy or distribute
Supported by DL/2:
• xxxTDLI Assembler IMS database calls
• EXEC DLI COBOL IMS system calls
• AIBTDLI PL/I and LE/370 IMS command codes
19
DL/2 at a Major Swiss Bank
• Global Foreign Exchange platform – 3rd largest in the world
• Rewrite project minimum 5-7 years
• Costs of IMS DB in excess of $2m per annum
• High risk involved in “big bang” rewrite approach
• DL/2 initially rejected in favour of a re-write
• After 18 months rewrite project stalled
• Mainframe infrastructure insist on a DL/2 pilot
• Pilot successful and full project initiated and completed in 8 months
with elimination of IMS DB and IMS tools licensing
Syncsort Confidential and Proprietary - do not copy or distribute
20
DL/2 at State of Florida DOC
• Florida DOC – Inmate management system for the entire State
• Cost reduction and on-going application modernization
• Requirement to clean up outdated information and provide modernization
platform in DB2
• Legislature unable to approve funding for multi million $ rewrite
• DL/2 again provides modernization platform with minimal risk
• IMS databases migrated to a normalized DB2 design with significant data
typing and re-engineering
• Application change requests now delivered via SQL/DB2 skills
• Significant cost reduction with elimination of IMS DB and IMS tools
licensing
Syncsort Confidential and Proprietary - do not copy or distribute
21
DB2 Design Summary
Syncsort Confidential and Proprietary - do not copy or distribute
• Each IMS segment maps to one DB2 table
• IMS concatenated key becomes DB2 primary key
• DB2 referential integrity ensures hierarchic integrity
• User defined DB2 table names, column names
• Optionally, DB2 design based on copybook/record definition
• Data transformation using built in conversion facilities• Date fields
• Numeric fields
• May require data cleansing
• Different design models can be deployed for different databases, even down to the individual segment level
22
Typical Migration Project
Syncsort Confidential and Proprietary - do not copy or distribute
• Databases grouped by application/size/complexity for optimal testing
• Primarily 3 main project phases:
Database Group 1
Database Group 2
Database Group 3
TIME
Database Group n
Eliminate IMS
Database mapping usually performed by Syncsort
Map/migrate Application testing Production cutover
23
Syncsort Migration Services
Syncsort Confidential and Proprietary - do not copy or distribute
• Free of charge DBD and PSB review service
• Proof of concept
• Mapping and migration service
• Onsite support for production cutover
24
Why the Transparency Approach?
Syncsort Confidential and Proprietary - do not copy or distribute
• NO application program changes are required
• LOWEST RISK migration strategy – bite-sized chunks
• REDUCED COSTS through elimination of IMS DB and BMC tools licenses
• SIMPLIFICATION of DBA support through DBMS convergence
• CHOICE of migration methods to meet differing requirements
• Simple, rapid method to reduce costs very quickly
• More measured method to meet DB2 design objectives
• A combination of both
• Simple method first, measured method later
• MINOR JCL changes (which can be made in advance)
25
DL/2 Transparent Data Migration
Syncsort Confidential and Proprietary - do not copy or distribute
• Eliminate IMS DB and IMS tools License costs with NO changes to
application code
• Support Legacy Modernization initiatives
• Reduce Risk as IMS DBA and Programming skills head for retirement
Industry Leader in Mainframe Software Products
27
Thank You!
Questions?
More Information:
http://www.syncsort.com/en/Solutions/Mainframe/Transparent-Data-Migration
http://www.syncsort.com/en/Products/Mainframe/EZ-DB2
Syncsort Confidential and Proprietary - do not copy or distribute