1 / 25

Managing Data Growth with Archiving

Managing Data Growth with Archiving. Steve Rosenberger Manager – Database Support SBPASC Steve.Rosenberger@fepoc.com. OBJECTIVES. Who are we? How is our environment configured? Why Archive? Our process Metrics Issues and Support Wrap-up Questions. Who is SBPASC?. ABOUT SBPASC….

Rita
Download Presentation

Managing Data Growth with Archiving

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing Data Growth with Archiving Steve Rosenberger Manager – Database Support SBPASC Steve.Rosenberger@fepoc.com

  2. OBJECTIVES • Who are we? • How is our environment configured? • Why Archive? • Our process • Metrics • Issues and Support • Wrap-up • Questions

  3. Who is SBPASC?

  4. ABOUT SBPASC… • Service Benefit Plan Administrative Services Corporation (SBPASC) • Federal Employee Program Operations Center® – FEPOC • Subsidiary of: • CareFirst Blue Cross Blue Shield, Inc. • Blue Cross Blue Shield Association • Organization of 300 associates • Process all Enrollment and Claims for Federal employees and dependents enrolled in BCBS health insurance coverage • Process approximately 180 million claims annually • Process approximately 9.8 million enrollment transactions annually: • 2.4 million Contracts • 4.7 million Members

  5. ENVIRONMENT CONFIGURATION

  6. BCBSA NETWORK

  7. ENTITY RELATIONSHIP DIAGRAM

  8. SYSTEM ARCHITECTURE • Database • DB2 V9.1 on z/OS • DB2 Application data approximately 4 TB • Current DB2 Backup Strategy • DB2 Image Copy weekly • DB2 Archive Logs multiple times daily • Some VSAM • Storage • IBM DS8100 • Mainframe • Z9 – 506 • 1C9 prior to 2007 • z/OS 1.9

  9. APPLICATION ARCHITECTURE • Application System availability 20 x 6 • Applications • Mix of COBOL and JAVA • Mix of Web based and Green Screen CICS (phasing out) • Near Real-Time Processing • Claims • Batch claims process 5 times daily • Interactive claims process within minutes • Process approximately 300,000 medical claims per day • Process approximately 280,000 pharmacy claims per day • Near Real-Time Processing • Enrollment • Process on-line every 5 minutes • Also one batch run per day • Process approximately 20,000 changes per day • Process 50,000 – 60,000 changes per day during Open Season

  10. WHY ARCHIVE?

  11. SYSTEM CONSIDERATIONS • Storage Considerations • Constraints within the storage environment • Constraints within the database environment • Performance Considerations • I/O Response Times • CPU Utilization • Contention • Database Maintenance Considerations • Reorgs • Image Copies • RUNSTATS • Restores

  12. OUR PROCESS

  13. ARCHIVE PROCESS • Active database holds 2 years of claims, building the 3rd • Archive the oldest year annually • Based on age (Process Date) • Contract ID range within age • 2006 was first year using archive for DB2 • Claims database was originally VSAM • Originally archived to tape • Now archive to DASD • Converted tape archives to DASD (119 tapes) • 400 GB of DASD per year of archive

  14. ARCHIVE PROCESS (Continued) • Weekly Archive Process • Two jobs per day Monday – Friday • Two steps per job • Each step archives approximately 1 million claims • Approximately 20 million claims archived per week • Deletes performed on Saturday • Four jobs with staggered start times • Five steps per job • Each step processes one archive file • Process takes 12 hours • Must be monitored due to deadlocks • Deletes all rows for all claims from one table, then the next table and so on…

  15. ARCHIVE PROCESS (Continued) • Referential Integrity (RI) between 29 DB2 tables • Delete Cascade • Nulls allowed in Foreign Keys • RI turned off during Delete processing • Restore Process • Plans may request archived claims to be added back to the active database for review or adjudication • Process runs weekly (Saturday) • 16 jobs run concurrently • Processes 600 archive input files • Six hours wall clock • 25 hours CPU

  16. METRICS

  17. YEARLY ARCHIVE METRICS • 2003 Claims Archive/Delete • 137 million claims/2.0 billion rows • 186 CPU archive hours/93 CPU delete hours • 372 wall clock archive hours/248 wall clock delete hours • 2004 Claims Archive/Delete • 135 million claims/2.5 billion rows • 195 CPU archive hours/97.5 CPU delete hours • 390 wall clock archive hours/260 wall clock delete hours

  18. YEARLY ARCHIVE METRICS (Continued) • 2005 Claims Archive/Delete • 153 million claims/2.9 billion rows • 223 CPU archive hours/111 CPU delete hours • 596 wall clock archive hours/298 wall clock delete hours • 2006 Claims Archive/Delete • 154 million claims/3.2 billion rows • 112 CPU archive hours/112 CPU delete hours • 300 wall clock archive hours/300 wall clock delete hours

  19. ISSUES and SUPPORT

  20. ISSUES ENCOUNTERED • DB2 Long Names • Ran into issue in test environment – March 2008 • Tool did not support DB2 V8 function • Work around by changing name • Tape files converted to DASD • DBA changed LRECL to be more efficient • Tool maintains this information internally • Changed LRECL back to 32K for these files

  21. ISSUES ENCOUNTERED (Continued) • Nobody’s Perfect • Test run of production archive failed in March 2008 • Restore processing working fine • Installed and tested several patches without success • Installed upgrade to tool with patches • Still failed • Installed additional patch • Everything has worked fine since • Support during this process was very responsive • We’ve incurred no other issues in four years of usage

  22. WRAP-UP

  23. CONCLUSION • Each environment is unique • Understand your requirements for archiving • Delete with archive vs. deferred delete • Compare row content before delete function • Performance impact to operational systems • Availability of System resources

  24. QUESTIONS?

More Related