db2 tools update 2008 - ibm optim ibm datamirror db2 performance toolkit the sap edition tivoli...

© 2008 IBM Corporation

®

Dirk CoomansSenior IT/Specialist, IBM CertifiedIBM Software Group BeNeLux

DB2 Tools update

IBM Software Group | Lotus software

IBM Software Group | Information Management software

2

Agenda

� 09:00 - 10:25 : DB2 9 for zOS technical briefing (Kurt Struyf)

� 10:25 - 10:35 : Announcement

� 10:35 - 10:50 : : Break

� 10:50 - 11:50 : DWH on System Z (Louis Holla)

� 11:50 - 12:30 : DB2 Tools Update (Admin, Recovery, Utilities) (Dirk Coomans)

� 12:30 - 13:30 :: Lunch

� 13:30 - 14:30 : DB2 Performance Mgmt update (Norbert Jenninger)

� 14:30 - 14:45 :: Break

� 14:45 - 15:15 : DB2 Performance Mgmt update… (Norbert Jenninger)

� 15:15 - 16:00 : Data Governance, Enterprise Information Integration (Dirk Coomans)

� 16:00 - 17:00 : IBM Information Server for System Z (Toon Latinne)

� 17:00 - : Cocktail



3

Topics

� Introduction

� Recovery Management

� Utilities Management

� Database Management

� Enterprise Data Management

� Enterprise Information Integration

� Regulatory compliance



4

Business Analysis� IBM DataQuant

� IBM Cognos

� IBM QMF

� DB2 Web Query Tool

Utilities Management� DB2 Utilities Suite

� DB2 Utilities Enhancement Tool

� DB2 Automation Tool

� DB2 Automation Toolkit SAP Ed.

� DB2 Cloning Tool

� DB2 Storage Management Utility

� DB2 High Performance Unload

Application Management� DB2 Path Checker

� DB2 Bind Manager

� IBM Optim

� DB2 Table Editor

Database Administration� DB2 Administration Tool

� DB2 Object Comparison Tool

� Data Encryption for DB2 and IMS

� DB2 Administration Toolkit SAP Edition

� DB2 Audit Management Expert

� DB2 Change Management Expert

Information Integration� WebSphere Classic Data Event Publisher

� WebSphere Classic Federation Server

� WebSphere Classic Replication Server

� WebSphere Data Event Publisher

� WebSphere DataStage

� WebSphere QualityStage

� WebSphere Replication Server

� IBM Datamirror

Recovery Management� Application Recovery Tool for IMS

and DB2 Databases

� DB2 Archive Log Accelerator

� DB2 Change Accumulation Tool

� DB2 Log Analysis Tool

� DB2 Object Restore Tool

� DB2 Recovery Expert

Performance Management� OMEGAMON XE DB2 Performance Expert

�OMEGAMON XE DB2 Performance Monitor

�DB2 Buffer Pool Analyzer

� DB2 Optimization Expert

� DB2 Query Monitor

� DB2 SQL Performance Analyzer

� DB2 Performance Toolkit SAP Edition

� DB2 Performance Expert

- Available on zSeries - Available on zSeries and Multiplatforms (LUW) - Only available on Multiplatforms (LUW)

IBM DB2 Tools for z/OS and Multiplatforms - Categories



5

2007

DB2 for z/OS Tools – Delivering Customer Value

DB2 V8 Exploitation

New Tools

� DB2 Test Data Base Generator

� DB2 II Classic Federation

New Releases/New Function

� DB2 Administration Tool

� DB2 Archive Log Accelerator






� DB2 Object Restore


� Major new function delivered via PTFs for DB2 Admin and DB2 Object Compare

� DB2 Data Propagator for z/OS

� DB2 Replidata for z/OS

New Tools

� DB2 Thread Expert

� DB2 Administration Toolkit the SAP Edition

� DB2 Automation Toolkit the SAP Edition

� DB2 Performance Toolkit the SAP Edition

� Tivoli OMEGAMON XE for DB2

Performance Expert on z/OS


Performance Monitor on z/OS

� WebSphere DataStage for z/OS

� WebSphere QualityStage for z/OS





� DB2 Path Checker

� DB2 Bind Manager

� DB2 Data Archive Expert


� DB2 Test Database Generator



� DB2 Buffer Pool Analyzer

New Tools


� DB2 Audit Management Expert


� DB2 Space Management Utility

� Regulatory Compliance Tools Suite


� DB2 Administration Toolkit the SAP Edition

� DB2 Automation Toolkit the SAP Edition


� Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS

� Tivoli OMEGAMON XE for DB2 Performance Monitor on z/OS





� DB2 Data Archive Expert




� DB2 Test Database Generator



2004

2005

2006

2007

DB2 V9 Exploitation

New Tools

� DB2 Optimization Expert

� DB2 Accessories Toolkit

� IBM DataQuant

� DB2 Utility Enhancement Tool

� IBM Optim

� IBM Cognos

� IBM Datamirror




Performance Expert on z/OS


Performance Monitor on z/OS











Continued focus on Lowering our customers TCO of DB2 and Autonomic Computing



6

DB2 TOOLS latest stuff

9.5WebSphere Data Event Publisher for z/OS

9.5WebSphere Replication Server for z/OS

3.2DB2 SQL Performance Analyzer

3.1DB2 Log Analysis Tool for z/OS

2.1DB2 Audit Management Expert for z/OS

1.2DB2 Accessories Suite for z/OS

Announced Dec 4, 2007

2.1DB2 Utilities Enhancement Tool for z/OS

2.1DB2 Recovery Expert for z/OS

2.1DB2 Cloning Tool for z/OS

1.2DataQuant for z/OS

Announced Oct 15, 2007

1.1DB2 Grouper

2.2DB2 TDBG for MP

1.1DB2 Change Management Expert for MP

2.2DB2 Performance Expert (PE) for MP

9.1DB2 Utilities Suite

1.1IBM DataQuant for z/OS (NEW)

1.1IBM DataQuant for Multiplatforms (NEW)

2.1DB2 Administration Toolkit for z/OS the SAP Edition

9.1IBM QMF

1.1DB2 Accessories Suite for z/OS (NEW)

1.1DB2 Optimization Expert for z/OS (NEW)

1H 2007

withdrawnIBM DB2 Test Database Generator

withdrawnIBM DB2 Data Archive Expert for z/OS

2.3DB2 Query Monitor for z/OS

1.2DB2 Optimization Expert for z/OS

Announced Mar 4, 2008



7

DB2 zOS and DB2 Tools support

� Support V7 still supported

� V8 and 9 :

– Big versions

– Day 1 support

– Learn more about all of these tools on the IM Tools website:

http://www.ibm.com/software/data/db2imstools/

– Check DB2 Tools and DB2 for z/OS V8 and V9.1 compatibility on:http://www1.ibm.com/support/docview.wss?rs=434&context=SSZJXP&uid=swg21256800



8

Database Administration

�Compare object structure in the same

subsystem or across subsystems

�Generate APPLY jobs – make the

target look like the source

DB2 OBJECT COMPARISON TOOL

�Catalog Navigation

�Change ManagementDB2 ADMINISTRATION TOOL

New Release

New Release



9

Database Change Management in DB2 Admin/DB2 Object Compare 7.2

� A collaborative change management process

� Changes are stored in a set of DB2 tables

– Audit trail provided for changes

– Meta data now stored in DB2 (versions, masks, ignores)

� Supports a collaborative Change Management process

– Tool determines if another change for this object, or it’s dependents, is in

process

– User can join the changes, supersede it, ignore it, etc.

– Establishes a change ID for tracking purposes

� Tool then automates the steps

– Provides an easy interface to modify the existing object

– Automatically propagates the change to dependent objects

– Builds job streams to enact the change / User submits the jobs

– Provides an impact analysis report

– Changes can be promoted to other systems

– Can create a new version file

� Virtual changes can be built on top of changes not yet deployed



10

Database Change Management

Change Management Database

� Set of processes to manage changes to objects in a structured manner

� Store changed information in the Change Management Data Base (CMDB)

� Allow ‘immediate’ and ‘normal’ changes

� Allow collaborative change (virtual changes)

� Provides a ‘change recovery’ capability

� Facilitates the moving of changes to other systems (promote / import)

� Allows for the reporting of changes (auditing)



11

Database Change Management

Change Management process

� REGISTER a change

� ANALYZE the change

– Determines how to do the change

� RUN or implement the change

� RECOVER a change

� PROMOTE a set of changes

� IMPORT a set of changes



12

Utility Management

�IBM utilities DB2 UTILITY SUITE

�Clone a DB2 subsystemDB2 CLONING TOOL for z/OS

� Manage DB2 spaceDB2 STORAGE MANAGEMENT

UTILITY (SMU)

�Thread Expert

�Extra Utility functions DB2 Utility Enhancement Tool

� Goes directly against VSAM data sets

� Multiple output formats

� User exit to manipulate data

DB2 HIGH PERFORMANCE UNLOAD –

z/OS or MP

� Generate IBM utility JCL on an as

needed basis - ExceptionDB2 AUTOMATION TOOL

New Version

New Version

New Version



13

DB2 Utilities Suite V9.1Announcement: March 16 , 2007

� Support for all new functions in DB2 Version 9 for z/OS product

– Universal Table Spaces (UTS)

• Partition By Growth (PBG)

• Partition By Range (PBR)

– XML table spaces (PBG or PBR)

– Not logged tables/table spaces

– Clone tables

– Index on expression

– New data types (BIGINT, VARBINARY, DECFLOAT, XML)

New Version



14


� Features and functions:

� BACKUP SYSTEM – includes new options to assist in the offloading of the system level backups to tape

� CLONE table function is available in the following utilities

• CHECK DATA, CHECK INDEX, CHECK LOB

• COPY, COPYTOCOPY

• DIAGNOSE

• LISTDEF

• MERGECOPY

• MODIFY RECOVERY

• QUIESCE

• RECOVER

• REORG INDEX

• REPAIR

• REPORT

• UNLOAD



15



� Higher data availability

• CHECK DATA, CHECK LOB

• REBUILD INDEX

• REORG LOB (allows read access)

• LOAD REPLACE (clone table function)

• REPAIR

• REORG TABLESPACE PARTITION (BUILD2 phase has been eliminated)

� COPY

• Includes SCOPE PENDING support – improve usability

• Template switching (e.g., copy to tape if large; to disk if small)

� LOAD / UNLOAD – support new data types and a new rounding mode

TEMPLATE LRG DSN &DB..&TS..D&DA..T&TI. UNIT=TAPE

TEMPLATE SML DSN &DB..&TS..D&DA..T&TI. UNIT=SYSALLDA LIMIT(20 CYL, LRG)

COPY TABLESPACE SMALL.TS COPYDDN(SML)

COPY TABLESPACE LARGE.TS COPYDDN(LRG)



16



� MODIFY RECOVER – alternative ways to remove entries from SYSLGRNX

and SYSCOPY

� RECOVER

• RECOVER to any point in time with consistency

• Select which IC to restore for the recovery

• Ensure COPY uses the most recent IC prior to the recovery point

• Recover at the object level from SYSTEM level backup

� RESTORE – more tape control options – can restore from SYSTEM backup

from tape with parallelism

� RUNSTATS – new statistics – assist with getting a better access path

� UNLOAD – support for skipping rows that are locked for transaction updates



17



�Performance: Utility CPU time reduction

• 10% to 20% in Copy, Recover table space

• 5% to 30% in Load*, Reorg*, Rebuild Index*

• 20% to 60% in Check Index*

• 35% in Load Partition*

• 30% to 40% in Runstats Index*

• 40% to 50% in Reorg Index*

• 70% in Load Replace Partition with dummy input*

* primarily from index processing



18

DB2 Utilities Enhancement Tool for z/OS V2.1 Announcement: October 15 (eGA 10/26) , 2007

� What is it?

� The functionality of DB2 Thread Expert is also included

�DB2 Utilities Suite Extensions


� Thread Expert features :

• ISPF user interface to display and manage DB2 threads

• Batch user interface to block and cancel DB2 threads in application job streams

• Flexible thread filtering capability to target only those DB2 threads that

you really want to cancel

• Ability to block new threads from starting

New Version



19


New Version

� DB2 Utility Suite Extensions

– DSNUTILB intercept

– Automatically size and create mapping tables for on-line REORGs

– Allow sorting and editing of data prior to LOAD

• PRESORT, CONSTANT, VALUEIF

– Automatically block and cancel DB2 threads before utility operations

– Full support for utility restart

– NO JCL CHANGES REQUIRED!



20


New Version

� Thread Blocker

– Optional feature of the batch interface

– Automatic with DSNUTILB intercept

– Blocks new threads from forming on the DB2 objects for which you are canceling existing threads

– Changes the statuses of the DB2 objects to either RO (read only) or UT (utility access only).

– Ensures that a utility has exclusive access to the objects when it runs

– After the utility completes, the original statuses of the objects are restored.



21

DB2 Utilities Enhancement Tool for z/OS V2.1 ISPF interface (Display and cancel threads)



22

DB2 Utilities Enhancement Tool for z/OS V2.1 Batch UI – JCL to Block and Cancel Threads



23

DB2 Utilities Enhancement Tool for z/OS V2.1 DSNUTILB Intercept – JCL for LOAD with ‘CONSTANT’ and ‘PRESORT’



24

DB2 Accessories Suite for z/OS, V1.2Announcement: December 4 (eGA 12/14), 2007 5655-R14

� What is it?

� A no-charge bundle of components designed to enhance the use of DB2 for z/OS data server.

� V1.1 offered:

� Enhanced SQL tuning, IBM Optimization Service Center (OSC),

� Spatial data support

� Unicode and internationalization operations

� New features and functions of V1.2:

– OmniFind Text Search Server search text information stored in a DB2 for z/OS database

– Data Studio - Reduces time required for database application development tasks.

• Physical data modeling • Data distribution viewer • Integrated query editor • SQL builder • SQL routine debugger • Java routine debugger • XML editor • XML schema editor • Data Web services • Object management • Data management • Update statistics

• Visual explain • Security access controls • Project management

IBM Data Studio:http://www.ibm.com/software/data/studio/

IBM Optimization Service Center:

http://www.ibm.com/software/data/db2/zos/downloads/osc.html



25

The Vision – IBM Data Studio

DeployDeploy

AdministerAdminister

GovernGovern

DevelopDevelop

DesignDesign

Eclipse WebShared

DataArchitect

DatabaseDeveloper

IBM

Other

Design

• Data Modeling

• Process Modeling

• Business Modeling

Develop

• Coding

• Debugging

• Teaming

• Testing

• Tuning

Govern

• Security Access

• Security Analysis

• Data Auditing

• Data Archiving

• Data Masking

• Data Encryption

Database Administrator

Administer

• Database Administration

• Data Management

• Change Management

• Recovery Management

• Storage Management

• Performance Management

Business Analyst

Application Developer

Security Administrator

A Complete End to End SolutionA Complete End to End Solution



26

– DB2 cloning (volume, dataset level zOS only)

• Hardware/Software based :

– Snaps (Flashcopy, Snapshot, Snap….)

– Mirrors (PPRC, Mirror….)

• Software based:

– Point in time copy software

– DB2 replication (Multiplatform, Homogeneous and heterogenous replication):

• SQL replication

– Replication of changes

– Based on SQL

• Q based replication

– High volume, low latency data replication based on Websphere MQ

• Event publishing

– Trigger data events

Data movement Tools



27

DB2 Cloning Tool for z/OS V2.1Announcement: October 15 (eGA 10/26), 2007

New Version

� Uses fast copy technology to quickly clone

– By Volume:

• DB2 subsystems

– By Data Set

• DB2 table and index spaces

� Functions :

– Supports any snap, mirror, or PIT copy

– Gives you access to data sets on target volumes:

• Renames and catalogs the data sets

• Fixes the VTOC, VTOCIX, and VVDS conflicts

• Updates all necessary DB2 Directory, Catalog and BSDS information

– Solution to enable the use of the data sets on the target volumes on the same image

• No more complicated cloning

• No more requirement for a separate image

� Value :

– Reduces time needed to create usable DB2 subsystem clones

– Automates DB2 cloning processes using any storage-or software-based copy technology

– Improve performance by offloading read-only access users to a DB2 clone

– Meet 24 x 7 data access demands by using a clones environment equivalent to production quality

– Simulate desired results without spending time to run costly updates

– Vendor independent!



28

Production Copy of Production

Production Databases

Copy for Inquiry

Source Target

� Steps to UseFlashCopy/SnapShot

DB2 Cloning Tool for z/OS V2.1 Volume cloning

1. Run DB2SETLOG– (Suspend source DB2)

2. Run COPY– (Copy source volumes)

3. Run DB2SETLOG– (Resume source)

4. Run RENAME– (Rename target)

5. Run DB2UPDATE– (Update target DB2)

6. Run DB2START– (Start target in maint. mode

7. Run DB2FIX– (Fix catalog and directory page spaces in LPL, GRECP status

8. Run DB2SQL– (Update target DB2 catalog)

9. Run DB2FIX – (Fix application pages spaces in LPL, GRECP status)

10. Run DB2STOP– (Stop target in maint. mode)

11. Run DB2START– (Start target normally)



29

Cloning Timings

� DB2 Cloning Tool – Timings

– Timing 1: 134 vols, 300 GB (FlashCopy V1) 700 data sets

• 3.5 minutes to copy

• 3.5 minutes to rename and catalog!

– Timing 2: 588 mod3 vols (FlashCopy V1) 50,000 data sets

• 3 minutes 41 seconds to COPY

• 10 minutes to RENAME and catalog (SAFE)!

– Timing 3: 7,200 vols (FlashCopy Manager) 59,083 data sets

• 18 seconds to COPY

• 11 minutes 29 seconds to RENAME and catalog!



30

DB2 Cloning Tool for z/OS V2.1 Data Set Clone

� Simplifies and automates the refresh of DB2 table and Index spaces– Data set fast copy not volume fast copy– Task automation– Vendor independent– Refreshes table and index spaces across LPARs– Allows a fuzzy copy without stopping the source

� Automates the translation of the source object IDs– In the target data sets to match those in the target DB2 catalog

� Does the catalog research for you– Determines the source and target data sets– Determines the source and target object IDs– Determines compatibility– Creates the XLATE parameters – Tracks source DB2 extents

� DB2 Cloning Tool supports the following scenarios:– FlashCopy or SnapShot

• All target objects already exist• Some or all target objects do not exist

– Other copy methodologies (Fast and slow)• All target objects already exist• Some or all target objects do not exist

� Uses an interface similar to the IBM LISTDEF facility– Drastically reduces the learning curve



31

1. Run the optional DB2 Cloning Tool TCP/IP server job if target is on another LPAR

2. Create target objects if they don’t exist

3. Run DB2 Cloning Tool source job

– LISTDEF selection

– Stop command if fuzzy copy was not used

– COPY execution– Start command if stopped

4. Run DB2 Cloning Tool target job– Object translation– Starts target table and

index spaces

DB2 Cloning Tool for z/OS V2.1Table and Index Space Steps

� Example contents of DB2 Cloning Tooling

SET TRGJOB(Y) LSSID(DB2T) SQLDD(SQL) SCANO(N)

SYNCDB2 TARGET-SSID (DB2T) -TARGET-DSN (DB2T.DSNDBC.DB2 .TESTD.XFERID##.I0001.A001) -SPACE-TYPE (TS) -PAGE-SIZE (4) -

VCAT (DB2T) -STOGROUP (TEST) -START-SPACE (Y) -RESET-LOGRBA (Y) -XLATE (DBID,00640,00434, -

PSID,00072,00042, -TSOB,00071,00041, -TBOB,00073,00043)

DATAMOVER(PGM(ADRDSSU) FASTREP (PREF) )

SET LOCAL-SSID(DB2S) DEFAULT-SQLID(DBADMIN) /* */ COPY TARGET-DB2(SSID(DB2T) LOC(DB2TLOC) -

IP(1111.2222.3333.4444) -OBJECT-TRANSLATE(CREATOR,SRCCRE,TRGCRE) -

SIIMULATE(Y) -FUZZY-COPY(Y) -DATA-MOVER( PGM(ADRDSSU) -FASTREP(PREF)) -REPLACE-TARGET-DSN(Y) -

COPY-IF-NO-DB2-TARGET-OBJECTS(N) -AUTO-STOP-TARGET-SPACE(Y) -AUTO-START-TARGET-SPACE(Y) -SYNCDB2-DDN(SYNC) -

SQLOUT-DDN(SQL) -RESET-LOGRBA(Y)



32

DB2 Storage Management Utility (SMU)

Optimizing your database DASD performance

� Primary Purpose :

– Verify integrity DB2 table spaces and index spaces

– View information about your DASD space

� Functions :– Displays information by volume, database, or table space, making it easy to understand DASD

constraints , over-allocated data sets, extents, migrated data sets, multivolume

– Verify the integrity of your DB2 table spaces and index spaces

– Performs interactive scans and space map analysis

– Analyzes space-related factors that affect DB2 performance

– Reviews DB2 space map pages to produce detailed reports and graphs that give an overall picture of the condition of a given space

– Finds DB2 widows and orphans, which simplifies the clean-up of nonexistent and forgotten DB2 objects and VSAM data sets

– Allows you to view historical information about the number of data sets, number of extents, and space allocated compared against space used that you can use for DASD trend analysis and capacity planning

– Provides recommendations and explanations to guide you through repairs



33

Recovery Management

� PIT recoveryDB2 RECOVERY EXPERT for z/OS

� Recover dropped objects and their

dependentsDB2 OBJECT RESTORE

� AUDIT

� REDO / UNDODB2 LOG ANALYSIS

� Online Backup

� Create SHRLEVEL REF ICDB2 CHANGE ACCUMULATION TOOL

� Disaster Recovery

� Application RecoveryDB2 AUTOMATION TOOL

� Compress logs

� Able to get more logs on DASDDB2 ARCHIVE LOG ACCELERATOR

� Synchronize DB2 & IMS databasesIBM APPLICATION RECOVERY for IMS

and DB2

UTILITY

MGMT

New Version

New Version



34

DB2 Log Analysis Tool for z/OS V3.1Announcement: December 4 (eGA 12/14), 2007 5655-T56

� Primary Purpose� Recovery Avoidance

– Use LAT to determine exactly what has or hasn’t changed

� Recovery Alternatives

– Use LAT to determine how many updates have occurred, then choose either UNDO/REDO SQL or traditional Image Copy + Log Apply approach

� Auditing and Reporting

– WHO, WHAT, WHEN, WHERE, and HOW

– Without expensive DB2 trace Overhead

– Remember – READ Only SQL (Selects) are not logged – that’s an Audit Expert Function

� “Test Data Generation and Data Replication”

– Might need to consider CAPTURE DATA CHANGES

� Scheduling Utilities

– Quiet time detection to find best time for Online Reorg or to pick possible points of consistency for recovery

� New features and functions:• Use of current table space dictionary to potentially avoid image copy reads and ultimately improve performance

• Triggers identified for more detailed reporting

• General report output can be loaded back into a DB2 table

• Support for large object and XML data types

• One-phase mode processing and log reading in parallel for better efficiency

• …and much more!



35

DB2 Recovery Expert for z/OS V2.1 Announcement: October 15 (eGA 10/26), 2007

Provides� Application recovery� Drop recovery (Reading the Log)� Recovery of single or multiple objects� Dependency analysis - mask or pattern� Quiet point analysis (Assist is built in)� SYSTEM RESTORE

Provides 4 ways to specify a Groups of Objects to Recover� Explicit selection - one by one� By pattern (Objects)� Via RI relationships - Grouper and Catalog RI� Automation Tool - object profiles

Includes functionality from:� Log Analysis Tool (Quiet time analysis)� Object Restore (See Object Restore tab)

3 choices when specifying a recovery point� Recover to current� Recover to a timestamp

– Can be selected fromObject versionRecovery history eventsQuiet points

� Recover to an RBA/LRSN– Can be selected fromRecovery history eventsQuiet points

New Version



36

DB2 Recovery Expert for z/OS V2.1

� Recovery Expert V2 provides

– Automated system backup management

– Object level Recovery

– Reporting

– Tape Offload support

– Validity Checking

– Disaster Recovery

– Several Backup Options

– Integration with DB2 Recovery Expert z/OS V2.1

GUI interface

� A simple, self-managing recovery solution that

enables database recovery operations with minimal

disruption

Significant new hardware and automated backup options



37

What does DB2 RE give me on top of native DB2 Backup/Restore Utility support?

– Backup validation

• DB2 BACKUP SYSTEM can create system level backups which cannot

be used for system restore

– Setup configuration assist

• Helps prepare the DB2 subsystem to meet the requirements of the

DB2 utilities

– Support for object restore in DB2 V7 and V8

– Automatic process, fewer human errors

• ISPF interface



38

Topics

� Introduction

� Recovery Management

� Utilities Management

� Database Management

� Regulatory compliance

� Enterprise Information Integration

© 2008 IBM Corporation

®

Data Governance

Information Integration



40

� Encrypt sensitive data

� Protect sensitive production data

� Save data for future audits and to comply with retention rules

� Auditability - discover who did what, where and when

The Requirements of Regulatory Compliance

� People: Professionals with regulatory experience will be hired to enable firms to meet and anticipate new regulatory requirements

� Process: More robust processes and procedures will enable top management to monitor and enhance regulatory compliance

� Technology: Significant investment will be made. The following are critical for your DB2 systems:

. . . which drive investment in several areas

Regulators have multiple goals. . .

� Improved risk management across the enterprise

� Integrity of financial reporting processes and related business practices

� Customer information security

Anti-Money Laundering

Gramm-Leach Bliley

Department of Defense -

5015.2

SEC Rule 17A-4

Corporate Information Security Accountability Act of 2003

California Bill 1386

Patriot Act HIPAA

Sarbanes Oxley

Basel II



41

IBM Data Encryption Tool for IMS and DB2

�Purpose

� Provides user-customizable, pre-coded exits for encryption of IMS and DB2 data

� Exploits System z Crypto Hardware features, which results in low overhead

encryption/decryption.

� Uses the ANSI Data Encryption Algorithm (DEA), also known as the U.S. National Institute of

Science and Technology (NIST) Data Encryption Standard (DES) algorithm.

� For DB2, encryption and decryption is customizable at the table level.

�AdvantagesƒLowest performance overhead

ƒNo application changes

ƒNo special key management (uses existing ICSF defined keys)

�ISPF EDIT JOB to:

z/OS ICSF

Encryption Code

Database @x v gad t a

DiskApplication

Crypto requests managed by z/OS (and OS/390) Integrated Cryptographic Services Facility (ISCF) which utilize on-board processors

1. linkedits the product supplied DB2 EDITPROC DECENC00 into the user's exit library as a member called DB2DEXIT

2. Next step ZAPs the ICSF administrator defined encryption key token into the IMS DB2 exit link-edited in the previous jobstep

3. DB2 applications will now be able to encrypt and decrypt data when they access EDITPROC DB2DEXIT

.



42

DB2 and IMS Audit Management Expert

� Provides centralized auditing of DB2 and IMS

bringing together information from many different sources into a correlated, coherent

view

� Enables auditors to collect, view, analyze and

report on data and save it into an audit repository

� Allows auditors to automatically generate their own reports and export the data into other

applications such as Excel spreadsheetsMakes life for auditors and

DBAs much easier !!

New For DB2 LUW !!!!



43

Audit Management Expert

� Collects and correlates information from DB2 resources.

– Audit Trace Data, Log Analysis data

� Provides a central resource for auditors to produce a coherent view of DB2 access information.

� Auditors will be able to Access:

– SELECT, INSERT, UPDATE, and DELETE activity by user or by object.

– CREATE, ALTER, and DROP operations against an audited object

– Utility access to an audited object

– DB2 commands entered

– Assignment or modification of an authorization ID

� Provides auditors with flexible options for examining the data in the audit repository.

Features



44

Security in DB2 and IMS Audit Management Expert

� Provide administration of users & passwords that are independent of OS and

DB2 or IMS users.

– Passwords are encrypted and stored in AME repository

– AME Administers access level privileges for its internal users through profiles

• Provide controls for limiting visibility to auditors of auditable objects

� Does not require auditors to be DB2 or IMS defined users within the monitored

system(s)

� Does not require the auditors to log on to the operating system where the

monitored system is running

� Does not require extensive interaction between the auditor and the system

support personnel (DBA/Sys admin)

� Auditor will not be able to directly manipulate any DB2 or IMS resources

� Thus, no security conflict between the gathering of auditing data and users

being audited



45

DB2 Audit Management Expert for z/OS, V2.1 Announcement: December 4 (eGA 12/14), 2007 5655-T57

� New capabilities

– View all SQL statements and their host variables allowing the user to identify the actual row that was accessed

– Includes a Data Capture Facility that provides controllable, time-unlimited data capture without DB2 trace dependency or limitations

• Captures SQL statements (dynamic & static)• Captures the host variable values• Shares collector/overhead with DB2 Query Monitor

� Key Differentiators

– Provides more in-depth, detailed data capture for use in audit reporting

– For network traffic DB2 AME can view / capture exactly what a network sniffer can plus the SQL that is issued locally (ie, user is on a TSO, CICS or IMS terminal that is wired directly into the mainframe)

– Removal of DB2 trace limitations/concerns



46


Data Collection Capabilities

� Collection Profiles can be defined for the recording of audit data

� Data collection can be filtered based on any combination of:

– DB2 objects

– DB2 user IDs

– Applications connecting to DB2

– Time

� Collection Profiles can be shared

– Exported/Imported using XML files to other AME servers,

– or assigned to another DB2 system within the same AME server

� Allows for filters created for common tables (such as SAP) to be

created once and then used at other DB2 servers where the same

application tables are installed



47


Comprehensive Reporting Facilities

� Robust reporting options enable auditors to view and report on data in a variety of ways

� Auditors can view audit data from several perspectives:

– Overview – View all of the subsystems currently being

monitored

– Subsystem - View activity for a selected subsystem

– Detail - View details for a particular type of activity for a selected subsystem

• Viewing filters by User, Object, Plan

– Graphical or Tabular

� Thresholds can be set to flag data that may need further investigation



48

Audit Management ExpertArchitecture

2 GUI interfaces

Administrator

Reporting



49



50



51



52

IBM Optim

� Market leading Enterprise Data Management

(EDM) Solution and Platform:

– Data Growth

– Retention & Discovery

– Data Privacy

– Test Data Management

– Application Upgrades

– Application Retirement

� Solving complex data management issues

since 1989

� 2400 clients worldwide; c. 50% of Fortune

500

� Princeton Softech acquired by IBM,

September 2007



53

Enterprise Data Management

Secure

ProtectPrivacy

Audit

LifecycleMgmt

Data Governance

Lifecycle Mgmt

• Data Retention

• Data Retirement

Secure

• Prevent Access

• Restrict Access

• Monitor Access

Protect Privacy

• Mask Data

• Encrypt Data

Audit

• Audit Access

• Audit Privileges

• Audit Users



54

IBM Data Governance Software

Secure

ProtectPrivacy

Audit

LifecycleMgmt

Data Governance

•Optim Data Growth•Optim Data Growth

•DB2 Audit Management Expert•DB2 Audit Management Expert

Lifecycle Mgmt

• Data Retention

• Data Retirement

Secure

• Prevent Access

• Restrict Access

• Monitor Access

Protect Privacy

• Mask Data

• Encrypt Data

Audit

• Audit Access

• Audit Privileges

• Audit Users

•Optim Data Privacy / Optim Test Data Management

•Data Encryption for IMS and DB2

•Optim Data Privacy / Optim Test Data Management

•Data Encryption for IMS and DB2



55

� Optim Data Growth Solution (Archiving)

– Improve performance

– Control data growth, save storage

– Support retention compliance

– Enable application retirement

– Streamline upgrades

� Optim Test Data Management Solution

– Create targeted, right sized test environments

– Improve application quality

– Speed iterative testing processes

� Optim Data Privacy Solution

– Mask confidential data

– Comply with privacy policies

Optim™ Solutions



56

Current

Production

Historical

Retrieve

Retrieved

Archives

Reporting

Data

Historical

DataReference

Data

Archive

Universal Access to Application Data

Application Application XML ODBC / JDBC

Optim™ Data Growth Solution: Archiving

� Addresses data growth, application retirement, data retention compliance

� Complete Business Object provides historical reference snapshot of business activity

� Storage device independence enables ILM

� Immutable file format enables data retention compliance



57

Optim Test Data Management and Data Privacy Solutions

Create targeted, right-sized test databases

� Refresh, reset and maintain test environments – quickly and easily

� Compare data to pinpoint and resolve application defects faster

� Accelerate release schedule

De-identify

� Substitute confidential information with fictionalized data

� Protect private data in non-production environments

Production or

Production Clone

Extract

Masked

Files

Development

Test

LoadInsert / Update

Compare

Extracted

Files

Mask



58

Optim™ Data Privacy Solution

Production

Contextual,

Application- Aware,

Persistent Data

Masking

Contextual,

Application- Aware,

Persistent Data

Masking

EBS / Oracle

Custom /

Sybase

Siebel / UDB

Test

EBS / Oracle

Custom /

Sybase

Siebel / UDB

� Substitute confidential information with fictionalized data

� Deploy multiple masking algorithms

� Provide consistency across environments and iterations

� Enable off-shore testing

� Protect private data in non-production environments



59

IBM Optim™Innovation to manage the lifecycle of data

Scalable solution provides a central point to deploy policies and to extract, store, port, and protect application data



60

Information Integration

� Replicate IMS

� Replicate VSAM

� Replicate CA-IDMS

� Replicate Adabas

Websphere Classic Replication Server

� Access DB2 / IMS / VSAM

� Access Adabas

� Access CA-Datacom

� Access CA- IDMS

Websphere Classic Federation Server

� SQL Replication

� Q ReplicationWebsphere Replication Server for z/OS

� Publish DB2Websphere Data Event Publisher for z/OS

� Publish IMS

� Publish VSAM

� Publish IDMS

� Publish ADABAS

Websphere Classic Data Event Publisher



61

WebSphere Replication Server

DB2

Sybase

OracleSQL

Server

Informix

DB2

Sybase

OracleSQL

Server

Informix

Teradata

Cleanse Transform

Capture Apply

Log-based

Capture

Source Target

Highly parallel

Apply

Capture WebSphere MQ Apply

DB2

Oracle

Sybase

Microsoft

Informix

DB2

SQL Replication

Q Replication

low latency, high volume replication for peer-to-peer environments

Flexible transformation and scheduling



62

WebSphere Data Event Publishers Facilitating integration

� Capture data events in real time– Websphere Data Event Publisher for z/OS (DB2)– WebSphere Classic Data Event Publisher - features

� Publish these "data events" to– Leverage operational data to drive business processes– Loosely coupled integration maintains application independence– One consistent published data format regardless of source

Log-based capture

User Application

DB2 UDB

WebSphereMQ

Message Broker

Target DBs

ETL or other datapropagation Tool

JMS-aware

ApplicationCICSVSAM

IMS CA-IDMS SAG-Adabas

NativeVSAM



63

WebSphere Classic Data Event Publisher v9.1Implementation

� Real-time &/or background capture and publishing of data changes made to:

– VSAM files – batch & CICS

– IMS databases

– CA-IDMS databases

– Software AG Adabas databases

� Push before & after images

– to a WebSphere message queue for delivery

– in an XML or delimited values layout

– reformatted into a consistent relational format

� Source-data unique change capture agents

– Active – during logging

– "Recovery" – from log files

– Alternatives for VSAM captures

• MVS Forward Recovery Logs• CICS Autojournal• Native VSAM file capture

WebSphere MQ

MVSlogVSAM

CICS Auto-journal

Classic Server

z/OS

MetadataCatalog

ClassicData

Architect

Copybooks DBDs PredictIDD

IDMSJournal

Adabaslog

IMSlog

Change Capture Agents

CHANGES



64

IBM WebSphere Classic Federation Server

Federation

�OLE DB�Excel�Flat files�WebSphere MQ�Custom-built

�WebSphere Adapter �SAP�PeopleSoft�Siebel

XMLWeb Services

PackagedApplications

SQL

�DB2 UDB �Informix�Oracle�Sybase �Teradata �SQL Server �ODBC

�VSAM,�Sequential

�DB2�IMS, �Adabas�CA-Datacom�CA-IDMS

MainframeFiles

MainframeDatabases

SQL

�DB2 CM Family�Domino.doc �Documentum�FileNet�Open Text�Stellent�Interwoven�Hummingbird�SharePoint

�WebSphere�FileNet

�Lotus Notes�Microsoft Index Server

Content& Imaging

Workflow Systems

CollaborationSystems

Content

RelationalDatabases

WebOther

�XML�Web Services

Federation ServerClassic Federation Server for z/OS

Information Integrator Content Edition

� Access diverse & distributed information as if it

were in one system

� Industry leading query optimization with single

sign-on, unified views, and function compensation

� Transactional write capabilities

across heterogeneous sources

� Visual tools for federated data

discovery & data modeling

Information virtualization



65

WebSphere Classic Federation Server for z/OSThe basics

Read-from and write-to mainframe data sources using SQL from Unix, Windows, Linux and JVM platforms

� Standard ODBC/JDBC SQL interfaces

� Metadata-driven means:

– No mainframe programming required

– Fast installation & configuration

– Ease of maintenance

� Works with existing and new:

– Mainframe infrastructure

– Application infrastructure

– Toolsets

� Deliver mainframe data to

– Self-service portals (real-time account details)

– e-commerce solutions (real-time inventory)

– Reporting and analytical tools, e.g. Business Objects

– Data transformation and cleansing (e.g. DataStage, QualityStage)for data warehousing, ODS, MDM, CDI, …

DB2 UDBfor z/OS

Software AGAdabas

VSAM,IAM &

sequential

CA IDMS

CA Datacom

IMS

Classic Federation Server



66

WebSphere Classic Federation Server v9.1Implementation

� Start with a metadata definition

– Create relational description of mainframe data sources by mapping the physical data definitionsto logical tables and views

– Interactive metadata management

• Eclipse GUI for "all" metadata

• Dynamic discovery from sources– IDMS Dictionary, Adabas Predict, COBOL copybooks

• "Accessibility" Client configuration

� Data Access Features

– Leverages native access tools: primary/secondary indexes, SSAs, Calckey…

– Anticipatory processing: reduces client-to-server interactions

– Prepared statements

� JDBC, ODBC and CLI Clients

– Standardized interfaces for tools & applications

AIX, HP-UX,

Solaris, Linux,

Wintel, JVM

z/OS

Tool Application

MetadataCatalog

Classic Server

ClassicData

Architect

Copybooks, DBDs, …

Classic Client

ClassicData Connectors



67

WebSphere Classic Replication Server

� Replication of non-relational System z data

– Classic Replication provides the "capture"

• VSAM, IMS, CA-IDMS, Adabas

– Replication Server provides the "apply":

• DB2 on all platforms, Oracle, SQL Server, Informix and Sybase on distributed• Expanded targeting and transformation via Replication Server apply

– Unidirectional

• Classic to RDBMS only in v9.1… more coming!

– Basic cross-product tooling

• Use Replication Server's command line processor (ASNCLP)• Supported platforms based on "Classic" CLI Client support

Control

Control

Log based

ClassicSource

Admin

WebSphereMQ

TargetQ-RepApply

Utilities

ClassicCapture

WS Classic Replication Server for z/OS WS Replication Server … LUW and/or z/OS

IMSVSAM

CA-IDMSADABAS

WebSphereMQ



68

� Direct Q-Apply to Target

• SQL Expression Transformations

• Stored Procedure Transformations

� Complex Transformations

• Q-Apply to CCD Target Table Type

&

• WebSphere DataStage with transformations to target database(s )

Q-Replication Architecture

VSAM

SourceLog

WebSphereDataStage

DB2

TargetWS Replication

Server(Q-Capture)

WSReplication

Server(Q-Apply)

WebSphere MQ

DB2

SourceLog

Oracle

Target

WSFederation

Server(Q-Apply)

WSClassic

ReplicationIMS

Source

Adabas

Source

IDMS

Source

Sybase

TargetSQL

Server

TargetInformix

Target

z/OS

LUW

CD1CCD

CD1CCD

Teradata

Target

DB2

Staging



69

Replication and ETL

DB2DB2S

DB2I

DB2S

Reporting Tools

DataWarehouse

ODS

Data Marts

Data Marts

Data Marts

Data Marts

Q replication orQ replication orEvent PublishingEvent Publishing

Designer

Log

Merge

DataStage®DB2DB2

Near Real-t

ime

Real-time

Near R

eal-time

Real-time

Rea

l-tim

e

IBM Information ServerUnderstand Cleanse Transform Deliver

Discover, model, and govern information

structure and content

Standardize, merge,and correct information

Combine and restructure information

for new uses

Synchronize, virtualize and move

information for in-line delivery

Platform ServicesParallel

Processing

Services

MetadataServices

Deployment

ServicesAdministration

Services

ConnectivityServices

Linux - AIX

Linux - AIX

z/OS

Linux – AIX - Windows

Cognosor other tool



70

DB2 Tools for z/OS - Continued Growth in 2008

2H08 – Lowering TCO

� OMEGAMON XE for DB2 PE/PM V4.2 – integrated interface

� Real Storage Expert – NEW!

� Automation Tool V3.1

� Change Accumulation Tool V2.1

� High Performance Unload V3.2

1H08 – a Performance Focus

� Query Monitor V2.3 integrates with Audit Management Expert

� Optimization Expert V1.2

� Database Relationship Analyze - NEW! (Grouper++)

� Accessories Suite V1.3



71

db2 tools update 2008 - ibm optim ibm datamirror db2 performance toolkit the sap edition tivoli...

Documents