May 20, 2008 • 01:30 p.m. – 02:30 p.m. Platform: DB2 for z/OS

1. Session: I07 Session Title: 24/7 Environment, need to update Production data? AUDIT, Data Security, & SOX Compliance FEARS!!! Speaker Name:Ashish Malhotra, Lori Zaremba & Glenn BielikCompany Name: PROGRESSIVE INSURANCE May 20, 2008 • 01:30 p.m. – 02:30 p.m. Platform: DB2 for z/OS

2. AGENDA About us Our Process Wrap-up QA

3. DB2 at Progressive Two Data Centers Colorado Springs Cleveland Claims Payroll Print Billing Quoting

4. Production Environment 25 Databases 10 Way Data Sharing Env. 1400 Tables Several Tables over 1 BILLION Rows DASD over 4 Tera Bytes

5. What did we need to change ? • Support Tables • State mandates to ease Billing Methods • Natural Disasters like Hurricanes, Floods, Wildfires etc.

6. Process • Quick & Easy • Data Backup • Data Recovery • Handle Inserts, Updates & Deletes • Run by Production Control • Audit Trails for SOX Compliance • Data Retention for 1 Year

7. Options considered when deciding a new process • Quick and easy – Fun to use – It works on TV • But wasn’t realistic • We used a lot of batteries

8. Super User Id’s • User-ids that have update/delete/insert authority on production databases: Fill out problem log with when id used/for what/by whom • Not Ideal: • Developers could easily forget to fill out problem log when id used. • No ability to audit. • Developers can forget and log in with these id’s and make changes to production unintentionally. • Developers can share passwords. • Developers can possibly lock up tables. • Our DBA’s didn’t like – we want to make them happy!

9. LAW of QADs QAD LAW Long and Wrong Quick & Dirty Programs Quickly written programs to update/delete/insert data • Longer to write than expected • Programs cloned that are incorrect to begin with • Missing commits • No easy way to test because specific data only exists in production

10. New Production Data Change Process O/P File of SQL Stmts All SQL Stmts With Headers & Trailers for Month Program to check SQL stmts SQL Statements O/P File of reformatted SQL Stmts as ‘Select * stmts’ Make copy of I/P File using Naming stds SYSIBM Tables Execute to Unload data for Each SELECT * Stmt TSO Batch process of SQL Stmts

11. Production Data Change Process Technical Overview & Examples

12. Job 1 - SQL Validation and data backup • Validate the SQL by doing a dynamic SQL PREPARE. This will validate SQL syntax, as well as validate table names, column names, etc. • Do further checking against our internal company standards. Some of the items that are verified include: • Limit of number of deletes or updates to 1000 rows • All deletes or updates must contain a WHERE clause • Primary key columns cannot be updated • Convert all UPDATE and DELETE statements into SELECT COUNT(*) statements and execute them dynamically to count the number of rows that will be affected by the SQL. This step is not run for INSERT statements. • Create a FIXFILE. It is a copy of the INSERT/UPDATE/DELETE SQL statements used by the JOB 2 to perform the database update. • Run an DB2 unload utility to backup all rows that will be affected by the update/delete. This step is not run for INSERT statements.

13. Job 2 - SQL Execution This job is a batch SPUFI job that executes the SQL statements contained in the FIXFILE created in the previous job.

14. Production Data Change Process Flow SQL OUTFILE SELFILE SYSIBM TABLES Validate SQL SYSPUNCH SYSREC00 Target Table SYSOUT FIXFILE Manual Verification 1 – Programmer codes SQL statements to update or insert into the target table 2 – Programmer requests run of the batch job to validate the update/insert SQL Execute SQL SYSPRINT 3 – Programmer manually verifies the output of the validation job 4- If the output from the validation job looks good, the programmer requests that the Execute SQL job be run

15. SQL Validation Job Output Files • SYSOUT - Contains the results of the SQL validation. A count of the number of rows that will be affected by the SQL is contained in this file. If any errors were detected, they will also be in this file. • FIXFILE- Copy of the INSERT/UPDATE/DELETE SQL used by the SQL execution job 2 to perform the database update. This file is deleted when the execution job completes. • OUTFILE – This file contains audit type information, such as the date and timestamp of the update attempt. It also contains the SQL statement as well as a count of the number of rows affected (for updates and delete statements only). • SELFILE - Contains a SELECT statement, generated from UPDATE and DELETE statements that was used by the validation job to determine the number of rows affected by the SQL.

16. SQL Execution Job Output Files • SYSPUNCH -Generated LOAD utility control statements that can be used to reload updated or deleted rows back into the source table. • SYSREC00 – Unload file containing all rows that will be updated or deleted by the SQL. • SYSPRINT - Contains the DB2 diagnostic information resulting from the execution of the SQL (batch SPUFI).

17. Sample Table Layout CREATE TABLE EMPLOYEE_TBL (EMPLOYEE_NBR INTEGER NOT NULL ,EMPLOYEE_NAME CHAR(30) NOT NULL WITH DEFAULT ,DEPT_NBR INTEGER NOT NULL WITH DEFAULT ,HIRE_DATE DATE NOT NULL WITH DEFAULT ,SALARY DECIMAL(8,2) NOT NULL WITH DEFAULT ,INSERT_TS TIMESTAMP NOT NULL WITH DEFAULT PRIMARY KEY (EMPLOYEE_NBR ))

18. Example: Valid Update • Scenario:Change DEPT_NO to 11 for EMPLOYEE_NBR 1 On the EMPLOYEE_TBL table • Target Data – Before Image • Column Type(len) Data • EMPLOYEE_NBR INT 1 • EMPLOYEE_NAME CH(30) JOHN SMITH • DEPT_NBR INT 4 • HIRE_DATE DATE 12/05/2004 • SALARY DEC(8,2) 46290.00 • INSERT_TS TIMESTAMP 2008-01-16-10.44.44.491594 • Input SQL Statement • UPDATE EMPLOYEE_TBL • SET DEPT_NBR = 11 • WHERE EMPLOYEE_NBR = 1

19. Example: Valid Update • Validation Job Output Files • Output File SYSOUT • ---------------------------------------- • DISPLAYS BEGIN • ---------------------------------------- • UPDATE EMPLOYEE_TBL • SET DEPT_NBR = 11 • WHERE EMPLOYEE_NBR = 1 • NUMBER OF ROWS AFFECTED: 0000000001 • ************************************* • Output File FIXFILE • UPDATE EMPLOYEE_TBL • SET DEPT_NBR = 11 • WHERE EMPLOYEE_NBR = 1;

20. Example: Valid Update • Validation Job Output Files • Output File OUTFILE • PROD FIX DATE/TIME = 2008-01-13-15.48.21.279471 GMB011308A • UPDATE EMPLOYEE_TBL • SET DEPT_NBR = 11 • WHERE EMPLOYEE_NBR = 1 • ; • NUMBER OF ROWS AFFECTED: 000000001 • Output File SELFILE • SELECT * FROM EMPLOYEE_TBL • WHERE EMPLOYEE_NBR = 1;

21. Example: Valid Update • Validation Job Output Files • Output File SYSPUNCH • LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE • EMPLOYEE_TBL • ( • EMPLOYEE_NBR POSITION( 1 ) INTEGER , • EMPLOYEE_NAME POSITION( 5 ) CHAR(30) , • DEPT_NBR POSITION( 35 ) INTEGER , • HIRE_DATE POSITION( 39 ) DATE EXTERNAL (10) , • SALARY POSITION( 49:53 ) DECIMAL , • INSERT_TS POSITION( 54 ) TIMESTAMP EXTERNAL(26) • ) • ; • Output File SYSREC00 • ....JOHN SMITH ....12/05/2004.ã...2008-01-13-15.47.01.812152.

22. Example: Valid Update • Execution Job Output File • Output File SYSPRINT • PAGE 1 • ***INPUT STATEMENT: • UPDATE EMPLOYEE_TBL • SET DEPT_NBR = 11 • WHERE EMPLOYEE_NBR = 1 • ; • RESULT OF SQL STATEMENT: • DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION • DSNT416I SQLERRD = 0 0 1 -1 0 0 SQL DIAGNOSTIC INFORMATION • DSNT416I SQLERRD = X'00000000' X'00000000' X'00000001' X'FFFFFFFF' • INFORMATION • SUCCESSFUL UPDATE OF 1 ROW(S)

23. Example: Valid Update • Target Data – After Image • Column Type(len) Data • EMPLOYEE_NBR INT 1 • EMPLOYEE_NAME CH(30) JOHN SMITH • DEPT_NBR INT 11 • HIRE_DATE DATE 12/05/2004 • SALARY DEC(8,2) 46290.00 • INSERT_TS TIMESTAMP 2008-01-16-10.44.44.491594

24. Example: Invalid Update (table name misspelled) • Input SQL Statement • UPDATE EMPLOYEE_TABLE • SET EMPLOYEE_NBR = 5 • WHERE EMPLOYEE_NBR = 1 • ;

25. Example: Invalid Update (table name misspelled) • Validation Job Output Files • Output File SYSOUT • ---------------------------------------- • DISPLAYS BEGIN • ---------------------------------------- • ************************************* • *********START OF DISPLAY************ • UPDATE EMPLOYEE_TABLE • SET EMPLOYEE_NBR = 5 • WHERE EMPLOYEE_NBR = 1 • DSNT408I SQLCODE = -204, ERROR: EMPLOYEE_TABLE IS AN UNDEFINED NAME • DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE • DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR • DSNT416I SQLERRD = 500 0 0 1 0 0 SQL DIAGNOSTIC INFORMATION • DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000' X'FFFFFFFF' • X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION • *********END OF DISPLAY************ • *************************************

26. Example: Invalid Update (attempting to update primary key) • Input SQL Statement • UPDATE EMPLOYEE_TBL • SET EMPLOYEE_NBR = 5 • WHERE EMPLOYEE_NBR = 1

27. Example: Invalid Update (attempting to update primary key) • Validation Job Output Files • Output File SYSOUT • ---------------------------------------- • DISPLAYS BEGIN • ---------------------------------------- • ******************************************** • **************START OF DISPLAY************** • ---CANNOT PERFORM UPDATE--- • PRIMARY KEY OR CLUSTERING KEY AFFECTED • KEY AFFECTED :EMPLOYEE_NBR • *************** END OF DISPLAY**************

28. Example: Invalid Update (missing WHERE clause) • Input SQL Statement • DELETE FROM EMPLOYEE_TBL • ;

29. Example: Invalid Update (missing WHERE clause) • Validation Job Output Files • Output File SYSOUT • ---------------------------------------- • DISPLAYS BEGIN • ---------------------------------------- • ************************************* • *********START OF DISPLAY************ • NO WHERE CLAUSE FOUND • CHECK INPUT FILE • DELETE FROM EMPLOYEE_TBL • ; • *********END OF DISPLAY************ • ************************************* • ************************************** • *** NO WHERE FOUND IN POSITION 1 *** • **************************************

30. Process • Quick & Easy • Run by Production Control • Data Backup & Recovery 1 Year

31. Process • Handles Update, Delete & Insert Insert • Audit Trail for SOX Compliance

32. Questions

33. Session I07 24/7 Environment, need to update Production data? AUDIT, Data Security, & SOX Compliance FEARS!!! DON'T WORRY, BE HAPPY!!! Ashish MalhotraLori ZarembaGlenn Bielik PROGRESSIVE INSURANCE Ashish_Malhotra@Progressive.com Lori_Zaremba@Progressive.com Glenn_Bielik@Progressive.com