Transcript Agenda - neodbug

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 Bielik
Company Name: PROGRESSIVE INSURANCE
May 20, 2008 • 01:30 p.m. – 02:30 p.m.
Platform: DB2 for z/OS
AGENDA
•About us
•Our Process
•Wrap-up
•QA
2
DB2 at Progressive
Two Data Centers
Cleveland
Colorado Springs
Claims
Print
Quoting
Payroll
Billing
3
Production Environment
25 Databases
10 Way Data Sharing Env.
1400 Tables
Several Tables over 1 BILLION Rows
DASD over 4 Tera Bytes
4
What did we need to change ?
• Support Tables
• State mandates to ease Billing
Methods
•Natural Disasters like
Hurricanes, Floods,
Wildfires etc.
5
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
6
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
7
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!
8
LAW of QADs
QAD
Quick & Dirty Programs
LAW
Long and Wrong
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
9
New Production Data Change Process
SQL
Statements
Program to check
SQL stmts
SYSIBM
Tables
O/P File of
reformatted SQL
Stmts as
‘Select * stmts’
Execute to
Unload data for
Each
SELECT * Stmt
O/P File
of SQL
Stmts
All SQL Stmts
With Headers
& Trailers for
Month
Make copy of
I/P File
using
Naming stds
TSO Batch
process of SQL Stmts
10
Production Data Change Process
Technical Overview & Examples
11
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.
12
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.
13
Production Data Change Process Flow
SQL
OUTFILE
SELFILE
SYSIBM
TABLES
Validate SQL
SYSPUNCH SYSREC00
Target Table
Manual
Verification
SYSOUT
FIXFILE
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
14
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.
15
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).
16
Sample Table Layout
CREATE TABLE EMPLOYEE_TBL
(EMPLOYEE_NBR
,EMPLOYEE_NAME
,DEPT_NBR
,HIRE_DATE
,SALARY
,INSERT_TS
INTEGER
CHAR(30)
INTEGER
DATE
DECIMAL(8,2)
TIMESTAMP
NOT
NOT
NOT
NOT
NOT
NOT
NULL
NULL
NULL
NULL
NULL
NULL
WITH
WITH
WITH
WITH
WITH
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
PRIMARY KEY
(EMPLOYEE_NBR
))
17
Example: Valid Update
Scenario:
Change DEPT_NO to 11 for EMPLOYEE_NBR 1 On the EMPLOYEE_TBL table
Target Data – Before Image
Column
EMPLOYEE_NBR
EMPLOYEE_NAME
DEPT_NBR
HIRE_DATE
SALARY
INSERT_TS
Type(len)
INT
CH(30)
INT
DATE
DEC(8,2)
TIMESTAMP
Data
1
JOHN SMITH
4
12/05/2004
46290.00
2008-01-16-10.44.44.491594
Input SQL Statement
UPDATE EMPLOYEE_TBL
SET DEPT_NBR = 11
WHERE EMPLOYEE_NBR = 1
18
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;
19
Example: Valid Update
 Validation Job Output Files
Output File OUTFILE
PROD FIX DATE/TIME = 2008-01-13-15.48.21.279471
UPDATE EMPLOYEE_TBL
SET DEPT_NBR = 11
WHERE EMPLOYEE_NBR = 1
;
NUMBER OF ROWS AFFECTED:
000000001
GMB011308A
Output File SELFILE
SELECT * FROM EMPLOYEE_TBL
WHERE EMPLOYEE_NBR = 1;
20
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
15.47.01.812152.
....12/05/2004.ã...2008-01-13-
21
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)
22
Example: Valid Update
Target Data – After Image
Column
EMPLOYEE_NBR
EMPLOYEE_NAME
DEPT_NBR
HIRE_DATE
SALARY
INSERT_TS
Type(len)
INT
CH(30)
INT
DATE
DEC(8,2)
TIMESTAMP
Data
1
JOHN SMITH
11
12/05/2004
46290.00
2008-01-16-10.44.44.491594
23
Example: Invalid Update (table name misspelled)
Input SQL Statement
UPDATE EMPLOYEE_TABLE
SET EMPLOYEE_NBR = 5
WHERE EMPLOYEE_NBR = 1
;
24
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
DSNT418I
DSNT415I
DSNT416I
DSNT416I
SQLCODE = -204, ERROR: EMPLOYEE_TABLE IS AN UNDEFINED NAME
SQLSTATE
= 42704 SQLSTATE RETURN CODE
SQLERRP
= DSNXOTL SQL PROCEDURE DETECTING ERROR
SQLERRD
= 500 0 0 1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD
= X'FFFFFE0C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
*********END
OF DISPLAY************
*************************************
25
Example: Invalid Update (attempting to update primary key)
Input SQL Statement
UPDATE EMPLOYEE_TBL
SET EMPLOYEE_NBR = 5
WHERE EMPLOYEE_NBR = 1
26
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**************
27
Example: Invalid Update (missing WHERE clause)
Input SQL Statement
DELETE FROM EMPLOYEE_TBL
;
28
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 ***
**************************************
29
Process
Quick & Easy
Run by Production Control
Data Backup & Recovery
1 Year
30
Process
Handles Update, Delete & Insert
Audit Trail for SOX Compliance
31
Questions
32
Session I07
24/7 Environment, need to update Production data?
AUDIT, Data Security, & SOX Compliance FEARS!!!
DON'T WORRY, BE HAPPY!!!
Ashish Malhotra
Lori Zaremba
Glenn Bielik
PROGRESSIVE INSURANCE
[email protected]
[email protected]
[email protected]
33