Simplify and Improve Database Administration by Leveraging Your Storage System

1. Simplify and Improve Database Administration by Leveraging Your Storage System Reggie Culpepper. March 31, 2011

2. Introduction 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

3. 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

4. Agenda • 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

5. 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 Fast 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

6. 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 Mirror 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. Volume Terminology 6

7. Terminology • 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

8. 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

9. 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

10. Database and Storage Integration Mainframe Database Systems Application and Database Management Domain • Organizational Integration • New Backup Methods • New Recovery Strategies • Business Recovery Monitoring • Cloning Automation • Disaster Restart Solutions Storage Aware Database Tools Source Database Storage Administration and Business Continuity Domain Backup, Clone, DR

11. 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

12. DB2 System Cloning

13. 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!

14. 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)

15. 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

16. DB2 system cloning by volume The volumes have been cloned but how do you access the data that was just cloned? Problems: 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

17. Challenges to Data Access on the Same or Shared LPAR Target TDBA01 SourcePDBA01 A1.CAT ICF User Catalog VTOC VVDS VVDS VTOC VTOCIX VTOCIX A.DSN3 A.DSN3 A.DSN2 A.DSN2 A.DSN1 A.DSN1 • 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

18. 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 Source PDBA01 Target TDBA01 VTOC VTOC SYS1.VTOCIX.PDBA01 SYS1.VTOCIX.TDBA01 SYS1.VVDS.VPDBA01 SYS1.VVDS.VTDBA01

19. “DB2 Metadata” Updated What Is Changed in DB2? DB2 directory updates The VCATNAME Optionally, the DB2 storage group names DB2 Catalog updates by SQL The DB2 VCATNAME name 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

20. DB2 System Cloning Steps Target DB2 Production DB2‘Source’ 5 Rename DB2 DB2 Clone 6 Update DB2 directory and BSDSs Start DB2 in maintenance mode for metadata management 7 DB2 volume selection 1 Correct DB2 catalog and directory page spaces 8 SET LOG LOAD(0) SET LOG SUSPEND Or consistency group 2 9 Update DB2 catalog 10 Correct application page spaces 3 Volume copy 11 Stop target in maintenance mode SET LOG RESUME 4 Start DB2clone in normal mode 12

21. DB2 Table Space and Index Space Refreshes

22. 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

23. 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

24. DB2 Table and Index Space Refresh Steps Target DB2 Production DB2‘Source’ Create targetobjects if they don’t exist DB2 DB2 Clone 1 • Source Job 2 LISTDEF selection Verify object compatibility 3 Target Job Stop or fuzzy copy 4 Object ID translation 7 5 COPY 8 Update identity columns 6 Start, if stopped 9 Start target table and index spaces

25. Backup and Recovery

26. 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

27. 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 Source DB2 or IMSVolumes TargetVolumes DB2 or IMS System Backup

28. DB2 and IMS System Level Backup Overview- Performing Instant Backups IMS DB2 or IMS Storage-Aware Backup and Recovery IMS • 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 SourceDatabaseVolumes SLB Offload System Backup Tape Processing

29. Restore 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 Storage-Aware Backup and Recovery Storage Processor APIs Source Database Volumes SLB SLB SystemLevel Backup SLB Tape Processing

30. 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 Coordination Storage Processor APIs Source Database Volumes SystemLevel Backup “Restartable DBMS Copy”

31. Storage-Aware 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 DBA Domain DB2 or IMS Storage Domain Source Database Volumes SystemLevel Backup

32. Intelligent Recovery Manager

33. 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

34. 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 34

35. 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

36. Defining Recovery Options (DB2) 36

37. 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

38. Q & A 38

39. Intelligent Disaster Recovery Manager

40. 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

41. 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

42. 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

43. Q & A