simplify and improve database administration by leveraging your storage system
Skip this Video
Download Presentation
Simplify and Improve Database Administration by Leveraging Your Storage System

Loading in 2 Seconds...

play fullscreen
1 / 43

Simplify and Improve Database Administration by Leveraging Your Storage System - PowerPoint PPT Presentation

  • Uploaded on

Simplify and Improve Database Administration by Leveraging Your Storage System. Reggie Culpepper. March 31, 2011. Introduction. Let’s look at storage from the DBA perspective. It is… That for which we are required to estimate the future usage

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Simplify and Improve Database Administration by Leveraging Your Storage System' - hanae-french

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
simplify and improve database administration by leveraging your storage system

Simplify and Improve Database Administration by Leveraging Your Storage System

Reggie Culpepper.

March 31, 2011


Let’s look at storage from the DBA perspective. It is…

  • That for which we are required to estimate the future usage
  • What we ask for when a table is about to run out of space
  • What we ask for after a table does run out of space
  • What we ask for when new applications and objects go into testing, QA and production
  • What the storage administrators guard as if it were their child
introduction continued
Introduction (Continued)

There is another aspect of storage

  • Storage systems can shorten the time it takes to copy, backup and recover DB2 objects
  • There are utilities that copy DB2 objects nearly instantaneously
  • There are tools that allow a DBA to take advantage of the storage system’s utilities and capabilities
  • These tools are both database knowledgeable and storage aware. They assist the database administrator and reduce the concerns of the storage administrator
  • Advantages
  • Terminology
  • Trends and directions
  • Definitions
  • System Level Clones
  • Table Space and Index Refreshes
  • System Level Backup Overview
    • Performing instant backups
  • Intelligent Recovery Manager
    • System and application recovery
    • Performing instant restores
advantages using storage based fast replication
Advantages using Storage-Based Fast Replication

What is storage-based fast replication?

The act of copying volumes or data sets using microcode facilities in the modern storage processors

Requires a sophisticated infrastructure and meta-data to manage the DBMS and storage processor coordination


Copies data instantaneously

DB2 system cloning on average – < 30 minutes

Table space refreshes – minutes

DB2 Backups are taken in seconds

DB2 Fast restore and parallel recovery drastically reduces recovery time

Provides high availability

Provides a consistent copy of production without sacrificing availability

Allows clones to be available quicker

Simplify disaster recovery – you can now do a DB2 restart

Provides huge cost savings

Doesn’t use host CPU or I/O resources

Copy process is done in the storage processor

Save CPU and I/O costs

Save personnel time

Fast-replication or Snap

The capability of a storage system which uses its processor and memory to give the appearance of an instantaneous copy of a volume or a data set

Database, table space, system

Slow copy

Any copy utility that uses the z/OS system’s host CPU to copy a volume or a data set

Database, table space, system


A copy procedure that establishes a relationship between a source and target volume such that all changes on the source are “mirrored” on the target.




  • What is a clone?
    • A clone is an exact replica
  • What is DB2 system or DB2 table space cloning?
    • The act of replicating the data, making the replica accessible,and then using the replica in lieuof the original data
db2 cloning terminology
DB2 Cloning Terminology

DB2 system clone

Clones a complete DB2 system including all its databases

Leverages volume fast replication

DB2 table and index space cloning

Refreshes specific table and index spaces

Used primarily for refreshing an application

Leverages data set fast replication when using cloning tools, (lowest level is by data set)

DB2 clone table

Clone table attributes into another table

Primarily used to load tables non-disruptively

Toggle capability between original table and the clone table

No fast replication

DB2 subsetting

Refreshes a target table with a subset of data from the source

Example: copy data for a single USA State when multiple States are in the same table

No fast replication

database and storage administration trends and directions
Database and Storage Administration - Trends and Directions
  • Large DB2 and IMS systems require high availability
    • Fast and non-intrusive backup and cloning facilities are required
    • Fast recovery capabilities are required to minimize downtime and promote high availability
    • Most backup, recovery and cloning solutions do not leverage storage-based fast-replication facilities
  • Storage-based fast-replication facilities are under-utilized
    • Tend to be used by storage organizations
    • Tend not to be used by database administrators (DBAs)
  • Storage aware database products
    • Allow DBAs to use fast-replication in a safe and transparent manner
    • Provide fast and non-intrusive backup and cloning operations
    • Simplify recovery operations and reduces recovery time
    • Simplify disaster recovery procedures
database and storage integration
Database and Storage Integration


Database Systems

Application and

Database Management


  • Organizational Integration
  • New Backup Methods
  • New Recovery Strategies
  • Business Recovery Monitoring
  • Cloning Automation
  • Disaster Restart Solutions

Storage Aware

Database Tools



Storage Administration


Business Continuity





clone and refresh overview
Clone and Refresh Overview

Clone DB2 systems (includes all databases)

Uses volume-based fast replication, including:

FlashCopy (IBM,EMC,HDS), TimeFinder/Snap(EMC), SnapShot (IBM,STK), Onsite Mirrors, Software Point-in-Time

Performs the necessary operations so that the data can be used by the cloned DB2 system

Refresh DB2 Table and Index Spaces

Uses data set based fast replication, including:

FlashCopy (IBM,EMC,HDS), TimeFinder/Snap(EMC), SnapShot (IBM,STK)

Performs the necessary operations to enable the cloned table and index spaces to be used on the same or another DB2 system

why clone db2 systems and or table and index spaces
Why Clone DB2 Systems and/or Table and Index Spaces?

To virtually widen batch windows

Execute processes that won’t fit within shrinking batch windows

Offload business processes from production

Improve production performance

To copy SAP, PeopleSoft interrelated data

To create or refresh a test, development, quality assurance, or production environment

To apply maintenance and verify integrity before applying to production

To stage data-warehouse loads

To aid in problem determination

You may be cloning your DB2 systems and table spaces today!

storage based fast replication to copy data
Storage-Based Fast Replication to Copy Data
  • By Volume
    • Snaps
      • Volume FlashCopy (IBM,EMC,HDS)
      • Volume SnapShot (IBM,STK)
      • Volume TimeFinder/ Snap(EMC)
    • Onsite mirrors
      • TimeFinder/Clone(EMC)
      • Metro Mirror (IBM)
      • SRDF (EMC)
      • ShadowImage (HDS)
  • By Data set
    • Snaps
      • Data Set FlashCopy (IBM,EMC,HDS)
      • Data set SnapShot (IBM,STK)
      • Data set Snap (EMC)
other solutions to copy data
Other Solutions to Copy Data
  • By Volume
    • Software Point-in-Time copies
      • TDMF (IBM)
      • FDRPAS (Innovation Data Processing)
  • By Data set
    • Any traditional data set copy
      • Much slower
challenges to data access on the same or shared lpar
DB2 system cloning by volume

The volumes have been cloned but how do you access the data that was just cloned?


VOLSERs can have the same volume names as the source

Data has the same data set names as the source

If you don’t want to access the data

from a different, non-sharing LPAR or

SYSPLEX, how do you access the data?

Challenges to Data Access on the Same or Shared LPAR
challenges to data access on the same or shared lpar1
Challenges to Data Access on the Same or Shared LPAR

Target TDBA01



ICF User Catalog













  • Result:
  • Data sets on the volume are copied, but keep their original name
  • Only the source data sets are cataloged; even if the catalog is on the cloned volumes, it isn’t connected to the system’s master catalog
storage aware database tools solve data access challenges
DB2 system cloning by volume

Changes the target volume identifiers if they are the same as the source

Renames the VTOC, VTOCIX, and VVDS to match the target volume

Renames and catalogs all data sets to a new HLQ

Updates the DB2 metadata

Makes data accessible on the same or shared LPAR

Storage Aware Database Tools Solve Data Access Challenges











db2 metadata updated
“DB2 Metadata” Updated

What Is Changed in DB2?

DB2 directory updates


Optionally, the DB2 storage group names

DB2 Catalog updates by SQL


Optionally updates the WLM environment name(s)

BSDSs updates

The DB2 catalog name

The ‘active’ log data set names

Optionally, the ARCHIVE data set names and volume serial numbers

Optionally updates the target DB2 BSDS’s DDF parameters

db2 system cloning steps
DB2 System Cloning Steps

Target DB2

Production DB2‘Source’







Update DB2 directory and BSDSs

Start DB2 in maintenance mode for metadata management


DB2 volume



Correct DB2 catalog and directory page spaces




Or consistency




Update DB2 catalog


Correct application page spaces


Volume copy


Stop target in maintenance mode



Start DB2clone in normal mode


table and index space refresh overview
Copies source DB2 data sets to target DB2 data sets

Researches the DB2 catalog(s) to gather data necessary to copy the source DB2 data sets and make them accessible after they are copied

Uses data set fast replication

Any fast or slow copy mechanism can be used, including:

FlashCopy (IBM,EMC,HDS), TimeFinder/Snap(EMC)

SnapShot (IBM,STK), DFSMSdss or FDR

Verifies source and target compatibility

Refreshes table and index spaces within the same DB2 system or to another DB2 system

Lowest level that can be copied is a data set

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

Table and Index Space Refresh Overview
db2 is updated
DB2 is updated

What Is Changed in DB2?

The target DB2 catalog is updated so that the starting sequence numbers for any Identity Columns are greater than the MAXASSIGNEDVAL entries on the source

Optionally, data in specified columns on specified tables is masked

db2 table and index space refresh steps
DB2 Table and Index Space Refresh Steps

Target DB2

Production DB2‘Source’

Create targetobjects if they don’t exist





  • Source Job


LISTDEF selection

Verify object compatibility


Target Job

Stop or fuzzy copy


Object ID translation





Update identity columns


Start, if stopped


Start target table and index spaces

why use db2 system level backup db2 image copies work why change
Why use DB2 System Level Backup? DB2 IMAGE COPIES work! Why change?

You are responsible for recovering your companies most critical assets

IMAGE COPIES are tried and true: They work

They can be non-disruptive

Some third party venders use them as input

Recovering objects from image copies is simple

However, IMAGE COPIES have 3 points of failure

They must be created by the DBA

They must be scheduled and scheduled at the right point in the job stream

They must execute successfully (any number of hardware and software events could cause them to fail and not be restarted)

Storage aware backup products

Can backup an entire subsystem instantaneously

Allows recovery of individual objects to a point-in-time or to current

Can create IMAGE COPIES from the backup

db2 and ims system level backup overview performing instant backups
DB2 and IMS System Level Backup Overview- Performing Instant Backups

A System Level Backup is a backup of the entire DB2 or IMS environment at a point in time

Recorded in metadata repositories

Leverages storage-based fast replication to drive the volume backup

Backup instantly - performed in seconds

Offloading data copy process to the storage processor saves CPU and I/O resources

Faster than data set copies

Backup DB2 and IMS without affecting applications

Backup windows reduced by replacing image copies

Extends processing windows

Data consistency ensures data is dependent-write consistent

DB2 suspend, IMS suspend

Storage-based consistency functions

Equivalent to a power failure

DB2 or IMS

Storage aware DB2 and IMS backup

Storage Processor APIs


DB2 or IMSVolumes


DB2 or IMS System Backup

db2 and ims system level backup overview performing instant backups1
DB2 and IMS System Level Backup Overview- Performing Instant Backups


DB2 or IMS


Backup and



  • Backup validation each time ensures successful recoveries
    • Insurance that a backup is available
  • Automated backup offload (archive/recall)
    • Copies system backup from fast replication disk to tape for use at either local or disaster site (or both)
  • Can be used in combination with other backups (image copies)
  • More advanced than DB2 BACKUP SYSTEM
    • Proven concept
    • Available for years
    • Provides enhanced reporting
    • Validates objects in good state for backup
    • Supports multiple storage vendors

Storage Processor APIs




System Backup




System and Application Recovery- Instant Data Restoration

  • Recover DB2 and IMS systems or application objects from disk or tape automatically
  • Intelligent Recovery Manager invoked to optimize recovery plans
  • Faster recovery
    • Instantaneous system or application restore process
    • Parallel recovery minimizes downtime
  • DB2 or IMS system backup can be used for database, table space, or application recovery
    • Data set fast-replication used to restore data
    • Parallel log apply reduces recovery time
  • One system backup used for system, application, and disaster recovery

DB2 or IMS


Backup and


Storage Processor APIs

Source Database Volumes



SystemLevel Backup




DB2 or IMS

DB2 and IMS System Level Backup- Disaster Recovery

  • Simplifies disaster recovery operations
    • System level backup for restart
    • System level backup and roll forward
  • System backup is “restartable”
    • Restore volumes containing the last SLB
    • Uncommitted changes backed-out during DB2 or IMS emergency restart
    • Disaster recovery is as simple as restarting from a power failure
  • Intelligent Disaster Recovery Manager
    • Prepares recovery assets and manages remote restore and recovery operations
  • Reduced recovery time at a DR site
  • Transform disaster recovery procedures into a tape-based disaster restart process
    • Similar benefits as storage-based remote replication solutions
  • Possible tertiary DR option for sites using remote mirroring

Database System

and Storage


Storage Processor APIs

Source Database Volumes

SystemLevel Backup

“Restartable DBMS Copy”


Database Tools

DB2 and IMS System Level Backup - Storage

  • Reduce storage and processing costs by utilizing one backup for multiple purposes
    • Local DB2 or IMS system recovery
    • Local Application recovery
    • Disaster restart/recovery
  • Leverages storage-processor and fast-replication software investments
    • Saves CPU, I/O, and processing resources
  • Expose fast copy capabilities to the DBAs safely and transparently using “storage-aware” database utilities
  • Provides a sophisticated infrastructure and metadata to manage DB2 / IMS and storage processor coordination
  • Multiple storage vendor support
    • IBM - FlashCopy
    • EMC - TimeFinder/Mirror/Clone/Snap, FlashCopy
    • Hitachi – ShadowImage, FlashCopy
    • IBM RAMAC Virtual Array, STK – SnapShot
  • Perform DB2 or IMS system cloning operations from a system level backup



DB2 or IMS



Source Database Volumes

SystemLevel Backup

intelligent recovery manager
Intelligent Recovery Manager
  • Performs efficient local recoveries using available recovery resources and tools
  • Backup and recovery utilities look like a single product from the end-users perspective
    • Centralizes backups
    • Only one product is needed for either all DB2 or all IMS recovery processes (local recovery, disaster recovery, rebuilding damaged index, database, etc.)
    • Sophisticated ISPF interface
  • Simplifies and automates recovery processes:
    • Related databases and table spaces (application) can be grouped and saved (in advance)
    • Recovery JCL can be built (in advance)
    • Run-time analysis to determine recovery resources available
      • Combination of SLB and other DB2 or IMS recovery assets
      • Can be directed to use DB2 or IMS recovery assets only
    • Run-time analysis of what recovery utility to invoke and in what order
    • Recovery process for IMS spawns jobs to perform recovery tasks
    • Takes the technical knowledge out of having to create complex recovery JCL
intelligent recovery manager overview system level backup recovery
Intelligent Recovery Manager Overview- System Level Backup Recovery

Analyzes system backup and DBMS to generate JCL that will restore/recover the system in quickest way possible

Automates volume restore from fast replication disk or from tape copy

Full DBMS Restore - Restore Entire DBMS

Includes active and archive logs, DB2 BSDS, IMS RECON, ICF catalogs and z/OS control datasets

Can be used for disaster restart or local restart of an entire DBMS

Data Only Recover

Restore volumes that contain DB2 tablespaces and Indexspaces, IMS databases and indexes

Perform roll forward recovery with one pass of the log

Recovery of all objects is performed to a specified point in time after the SLB

Detects objects that had a LOG NO event occur in recovered log range

Automatically generates recovery using Image Copies and rebuild indexes for those objects

Can be used at DR site to replace traditional image copy recovery methods

SLB volumes are restored at DR site from a system backup on tape

Recovery is performed with one pass of the log


intelligent recovery manager overview steps to application recovery from an slb
Intelligent Recovery Manager Overview- Steps to Application Recovery from an SLB
  • Application profile created in advance
    • Single or group of databases or table spaces
    • Saves recovery time - related applications are defined ahead of time and used when application needs recovery
  • Select Application Profile
    • Explode command (option to exclude some databases / table spaces if required)
    • Determine the timestamp to which you want to recover
      • Recover to current (DB2 and IMS)
      • Recover to a timestamp (DB2 and IMS) (DB2 timestamp utility converts to RBA)
      • Recover to DB2 RBA/LRSN
    • ‘Update the application profile with this timestamp
  • Analyzes all databases / table spaces in the profile and generates the most appropriate recovery method for each object
    • Logically related databases can be automatically included
    • Generates JCL to restore objects from either IC or SLB
    • Indexes that cannot be restored are rebuilt
    • Access to databases / objects is automatically stopped and restarted at end of recovery
  • Storage-based fast-replication is used to perform instant restore
    • Performs an instantaneous data set restore process
    • Fast replication from SLB is available even if data set has moved or was deleted or an Online Reorg occurred after SLB
storage aware database products
Storage aware database products

Vender DBMS Purpose Product

IBM DB2 - Cloning - DB2 Cloning Tool

IBM IMS - Cloning - IMS Cloning Tool

IBM DB2 - Backup and Recovery - DB2 Recovery Expert

IBM                IMS - Backup and Recovery   - IMS Recovery Expert

Mainstar DB2 - Cloning - VCR/FTR

Mainstar IMS - Cloning - ICR/RDR

Mainstar DB2 - Backup and Recovery - DBR for DB2

Mainstar        IMS- Backup and Recovery - DBR for IMS

EMC DB2 - Backup and Recovery - RBR

intelligent disaster recovery manager
Intelligent Disaster Recovery Manager
  • Performs:
    • Local site procedures to prepare for offsite disaster recovery or disaster restart
      • Image copy method
      • System level backup method
    • Remote site restore operations and appropriate recovery or restart procedures
    • Simplifies and automates disaster recovery processes
  • Disaster recovery or disaster restart creation of jobs to:
    • Perform traditional disaster recovery process
    • Restore system level backup and restart DB2 or IMS
    • Restore system level backup, restore conditioned RECONs, run recoveries to point in time, and restart IMS
    • Restore system level backup, update BSDS, restart DB2 apply logs to point in time
    • Restore system level backup, restart DB2, apply image copies that were sent offsite
intelligent disaster recovery manager1
Intelligent Disaster Recovery Manager
  • DB2 Options to:
    • Specify which archive logs are to be used at the disaster site
    • Copy archive logs
      • Option to force a checkpoint before archiving – The tool issues a SET LOG LOGLOAD(0) command                
      • Option to force the active log to archive
    • Build JCL to restore the DB2 catalog and directory from Image Copies
      • The tool builds recovery procedures in the right order to match DB2 release requirements
    • Find appropriate DR image copies and store information about them in the PDS which will be shipped to the DR site
    • Dump the tool’s repository to the PDS and create recovery JCL
    • Copy archive logs to disk at the recovery site to reduce or eliminate contention on the archive log tape during recovery
    • Catalog disaster recovery image copies in ICF catalog at DR site
    • Build the bootstrap data set(s) (BSDS) 
  • IMS Options to:
    • Specify which image copies, change accumulations, and archive logs and what copy for transport to disaster recovery site
    • Dump the tool ‘s repository to the PDS and create recovery JCL
    • Copy archive logs to disk at the recovery site to reduce or eliminate contention on the archive log tape during recovery
    • Tape pick list
    • Copy of RECON is created and conditioned with any logs, change accums and image copies being sent to DR site
      • Removes the requirement to modify the RECON at the DR site
    • If logs and change accums aren’t required, they are marked in error in the conditioned recon so they won't be pulled in
    • Recovery JCL is created, backed up, and sent offsite
  • For both:
    • Recovery JCL created each time intelligent Disaster Recovery Manager is executed at local site
    • Jobs are pre-built and placed in a PDS to be shipped to the disaster recovery site
leveraging your storage system summary of benefits
Leveraging your storage system- Summary of Benefits

Storage-aware database utilities provide storage integration to simplify database administration tasks

System-level backup solutions leverage storage-based fast-replication facilities and investments

Fast and non-intrusive backup operations with less administration

Reduces host CPU, I/O and storage utilization

Backups can be used for system, application, disaster restart

Parallel recovery reduces system and application recovery time

Backup validation each time ensures successful recoveries

Utilizes one backup for multiple purposes

Application recovery can leverage existing products

Intelligent Recovery Managers can be implemented with or without SLBs

Implementation of an SLB methodology can be done over time

Transforms DR procedures into a disaster restart process

Intelligent Disaster Recovery Managers can support image copy or SLB method

Less skills required to implement advanced IMS backup, recovery, and disaster recovery solutions

Managed recovery with or without System Level Backup