Managing Data Growth with Archiving

1
Managing Data Growth with Archiving

• Steve Rosenberger
• Manager Database Support
• SBPASC
• Steve.Rosenberger_at_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
(No Transcript)
20

• ISSUES and SUPPORT

21
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

22
ISSUES ENCOUNTERED (Continued)

• Nobodys 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
• Weve incurred no other issues in four years of
  usage

23

• WRAP-UP

24
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

25

• QUESTIONS?