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

Speaker Name: Ashish Malhotra, Lori Zaremba & Glenn Bielik

Company Name: PROGRESSIVE INSURANCE

Session: I07

Session Title: 24/7 Environment, need to update Production data? AUDIT, Data Security, & SOX Compliance FEARS!!!

2

AGENDA

•About us•Our Process•Wrap-up•QA

3

DB2 at Progressive

Claims

Print

BillingQuoting

Payroll

Two Data CentersCleveland Colorado Springs

4

Production Environment

25 Databases

10 Way Data Sharing Env.

1400 TablesSeveral 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

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

QAD

Quick & Dirty Programs

LAW

Long and Wrong

10

New Production Data Change Process

SQL Statements

SYSIBMTables

Program to check SQL stmts

O/P File of reformatted SQL

Stmts as ‘Select * stmts’

Make copy ofI/P File using

Naming stds

All SQL StmtsWith Headers& Trailers for

Month

O/P File of SQLStmts

Execute to Unload data for

Each SELECT * Stmt

TSO Batch process of SQL Stmts

11

Production Data Change ProcessProduction Data Change Process

Technical Overview & ExamplesTechnical Overview & Examples

12

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.

Job 1 - SQL Validation and data backup

13

This job is a batch SPUFI job that executes the SQL statements contained in the FIXFILE created in the previous job.

Job 2 - SQL Execution

14

Production Data Change Process Flow

SQL

SYSPRINT

SYSREC00

OUTFILE

FIXFILE

SYSPUNCH

SELFILE

SYSOUT

Validate SQL

SYSIBMTABLES

Target Table

Execute SQL

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

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

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.

SQL Validation Job Output Files

16

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

SQL Execution Job Output Files

17

CREATE TABLE EMPLOYEE_TBL CREATE TABLE EMPLOYEE_TBL (EMPLOYEE_NBR INTEGER NOT NULL (EMPLOYEE_NBR INTEGER NOT NULL ,EMPLOYEE_NAME CHAR(30) NOT NULL WITH DEFAULT ,EMPLOYEE_NAME CHAR(30) NOT NULL WITH DEFAULT ,DEPT_NBR INTEGER NOT NULL WITH DEFAULT ,DEPT_NBR INTEGER NOT NULL WITH DEFAULT ,HIRE_DATE DATE NOT NULL WITH DEFAULT ,HIRE_DATE DATE NOT NULL WITH DEFAULT ,SALARY DECIMAL(8,2) NOT NULL WITH DEFAULT ,SALARY DECIMAL(8,2) NOT NULL WITH DEFAULT ,INSERT_TS TIMESTAMP NOT NULL WITH DEFAULT,INSERT_TS TIMESTAMP NOT NULL WITH DEFAULT

PRIMARY KEYPRIMARY KEY (EMPLOYEE_NBR(EMPLOYEE_NBR )) ))

Sample Table Layout

18

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

Example: Valid Update

19

Validation Job Output FilesValidation 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;

Example: Valid Update

20

Validation Job Output FilesValidation 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;

Example: Valid Update

21

Validation Job Output FilesValidation 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.

Example: Valid Update

22

Execution Job Output FileExecution 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)

Example: Valid Update

23

Target Data – After ImageTarget 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

Example: Valid Update

24

Input SQL StatementInput SQL Statement

UPDATE EMPLOYEE_TABLE SET EMPLOYEE_NBR = 5 WHERE EMPLOYEE_NBR = 1 ;

Example: Invalid Update (table name misspelled)

25

Validation Job Output FilesValidation 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************ *************************************

Example: Invalid Update (table name misspelled)

26

Input SQL StatementInput SQL Statement

UPDATE EMPLOYEE_TBL SET EMPLOYEE_NBR = 5 WHERE EMPLOYEE_NBR = 1

Example: Invalid Update (attempting to update primary key)

27

Validation Job Output FilesValidation 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**************

Example: Invalid Update (attempting to update primary key)

28

Input SQL StatementInput SQL Statement

DELETE FROM EMPLOYEE_TBL ;

Example: Invalid Update (missing WHERE clause)

29

Validation Job Output FilesValidation 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 *** **************************************

Example: Invalid Update (missing WHERE clause)

30

Process

Quick & EasyQuick & Easy

Data Backup & RecoveryData Backup & Recovery

1 Year

Run by Production ControlRun by Production Control

31

Process

Handles Update, Delete & InsertHandles Update, Delete & Insert

Audit Trail for SOX ComplianceAudit Trail for SOX Compliance

32

Questions

33

Ashish MalhotraLori ZarembaGlenn Bielik

PROGRESSIVE INSURANCEAshish_Malhotra@Progressive.com

Lori_Zaremba@Progressive.comGlenn_Bielik@Progressive.com

Session I0724/7 Environment, need to update Production data? AUDIT, Data Security, & SOX Compliance FEARS!!! DON'T WORRY, BE HAPPY!!!