Transcript Audit your SOX off - Baltimore/Washington DB2 Users Group

Audit Your SOX off,
and other uses of a DB2 Log Tool
Rick Weaver
BMC Software
Overview
Mostly generic look at Log Tools
-
Basics
Examples
 Auditing
 Recovery
 Data Migration
BMC Log Master for DB2 used for examples
High-Speed Apply Engine benefits
© Copyright 7/7/2015 BMC Software, Inc
2
Log Tool Basics
Read the log
Provide context
-
Associate Unit of Recovery info to DM activity
Combine multiple records into single entities
Manage committed versus rolled back activity
Allow for robust selection criteria
Allow for “ONGOING” processes
Produce outputs
-
SQL, DDL, LOAD, REPORTS, reusable inputs
© Copyright 7/7/2015 BMC Software, Inc
3
Log Tool Basics
Externalization of data
-
-
Completion of partially logged updates
 Potentially costly process
 Avoid with DATA CAPTURE CHANGES
Decompression
Invoke Edit and Field Proc Decode functions
Decode DB2 Internal Formats
 Numerics, Date, Time, and Timestamps
Normalize Log Records to the current Version
Serialize XML
© Copyright 7/7/2015 BMC Software, Inc
4
Log Tool Basics
Resources Used
-
BSDS – Boot Strap Datasets
ARCHIVE and ACTIVE Log Datasets
DB2 Catalog
EDM Pool
TABLESPACE VSAM Datasets
IMAGE COPY Datasets
Reusable inputs for reprocessing, SQL application, etc.
© Copyright 7/7/2015 BMC Software, Inc
5
Mining the DB2 Log Data – BMC Log Master
Member
BSDS
Archive
Logs
Member
BSDS
Active
Logs
Archive
Logs
Member
BSDS
Active
Logs
Archive
Logs
Batch
Log Scan
DB2
Logical
Log
Repository
Online
Interface
© Copyright 7/7/2015 BMC Software, Inc
DDL Generator
Load Generator
Load
Utility
Reports
Report Writer
SQL Generator
Active
Logs
DML
DDL
SQL Processor
High Speed Apply
Load
File
6
Audit Your SOX Off
Approach(es)
Ongoing process storing data for later reprocessing
-
Logical Log – Merged into Dailies, Weeklies…
Report Generation
-
Audit, Detail, Summary, Catalog Activity
User Control over content
Verbose Detailed output
-
SQL or DDL
LOAD or LLOG file output to post process
© Copyright 7/7/2015 BMC Software, Inc
7
Audit Your SOX Off
Examples
Power user (SYSADM, DBADM) activity
Track Authorization changes
Audit all schema changing activities
Look for changes to sensitive columns and tables
Detect Dynamic SQL activity
Report on Utility Execution
Ad Hoc research for cause of data corruption
© Copyright 7/7/2015 BMC Software, Inc
8
Audit Example
Power user activity
Need to reconcile power user activity to change control
Detail Report produced on a daily basis for listed AUTHIDs and sensitive
DATABASEs
Report ORDER-ed BY
-
CORRELATION ID (Jobname, TSO ID, etc.)
UNIT OF RECOVERY
Each activity has to be reconciled to a Change Control Ticket
© Copyright 7/7/2015 BMC Software, Inc
9
ONGOING versus FOR LIMIT
ONGOING – TO point resolution
-
TO CURRENT
TO DATE(*) TIME(-00.30.00)
OR LIMIT
-
5 LOG FILES
7 DAYS
24.00.00
© Copyright 7/7/2015 BMC Software, Inc
11
Audit Example
Track Authorization Changes
Need to track all Authorization activity
Catalog Activity Report produced daily
Filter was for all GRANT and REVOKE activity
The Catalog Activity Report is summary level
Another option – VERBOSE DDL
© Copyright 7/7/2015 BMC Software, Inc
12
Audit Example
Schema changing activities
Report on ALL DDL activity in the system
Method
-
Generate Logical Log on an ONGOING basis
Merge into Weeklies, Monthlies
Generate Reports from Monthly Logical Log
© Copyright 7/7/2015 BMC Software, Inc
14
Audit Example
Changes to sensitive columns or tables
Need was to track the life cycle of a check (cheque)
Customer wrote ISPF front end for Auditors
-
-
Auditor would enter
 Account
 Check number
 Range for activity
ISPF Application would generate and submit a logscan job
Report could be viewed after job execution
© Copyright 7/7/2015 BMC Software, Inc
17
Audit Example
Dynamic SQL activity
WHERE
PLAN NAME IN (
'DSNTEP2',
'DSNTIAUL',
'DSNTIAD',
'DISTSERV',
'DSNESPCS')
OR
(PLAN NAME = 'ADMPROD'
AND
CORRELATION ID <> 'ALLOWJOB')
OR
PLAN NAME LIKE 'QMF%'
OR
PLAN NAME LIKE 'CDB%'
OR
PLAN NAME LIKE 'ACT%'
© Copyright 7/7/2015 BMC Software, Inc
18
Audit Example
Report on Utility Execution
Basically, an extract of SYSIBM.SYSCOPY
Can exclude non-utility ICTYPEs
-
‘A’ – Alter
‘C’ – Create
Approach
-
Generate a LOAD CSV file to post process
Transformed ICTYPE to meaningful value
© Copyright 7/7/2015 BMC Software, Inc
19
Recovery Capabilities
Transaction Level Recovery
-
Avoid RECOVER outages and FIX IT programs
UNDO the bad
REDO the good after a PIT
Back out Integrity Checking
Discover and generate QUIET POINTs
DROP RECOVERY
Test System Regression
Customized Post Processing
© Copyright 7/7/2015 BMC Software, Inc
21
Recovery Capabilities
High-speed Apply Engine
SQL or LLOG input
-
LLOG processing almost always faster
Avoids SQL generation and parsing
Multi-threaded
Control over object distribution
Robust conflict resolution
Restartable
Used as the method for Migration as well as Recovery
© Copyright 7/7/2015 BMC Software, Inc
22
UNDO - Take Away Only the Bad Data
BMC Log Master for DB2 can apply UNDO SQL to get rid of bad transactions.
Business Value – ZERO downtime for transaction level recovery!
Good Transaction 1
Bad Transaction
Generate
UNDO SQL
© Copyright 7/7/2015 BMC Software, Inc
Good Transaction 2
UNDO Bad Transactions
Apply
UNDO SQL
23
Recovery
Back out Integrity Report
You choose to do an UNDO…
But, has anything happened to the row between the UNDO range and current
BACKOUT INTEGRITY
© Copyright 7/7/2015 BMC Software, Inc
29
Recovery Example
QUIET POINT Analysis
Avoid QUIESCEs
Customer historically doing a QUIESCE during a ‘low’ processing time every
night
Still received 100-200 application time outs
Now use Log Processing to find and manifest QUIESCEs
Finding points with no open Units of Recovery when RECOVER is necessary
versus QUIESCE
© Copyright 7/7/2015 BMC Software, Inc
32
Automated Drop Recovery – Reduce Risk
Generates JCL and outputs
to automate Drop Recovery
• UNDO DDL to recreate the dropped object
Scans DB2
Log Records
• Syntax for recovery and object ID translation
Process is initiated
from the online interface
• Drop Recovery Report
• DB2 commands to rebind application plans that were invalidated when
the object was dropped
Recreates dropped objects
Log Master Technology
DB2
Subsystem
Drive Recovery Technology using
copy and log from Dropped Object.
RECOVER PLUS Technology
OBID Translation
Applies log to point of DROP
Post recovery SQL and Rebind
© Copyright 7/7/2015 BMC Software, Inc
35
Recovery Example
System Regression
Test System Regression
-
Back out the test cycle versus recover to PIT
Set LOGMARK at beginning of test
Set LOGMARK at end of test
Generate LLOG from Begin to End LOGMARKS
Execute High-speed Apply Engine to UNDO LLOG
Production too…
© Copyright 7/7/2015 BMC Software, Inc
37
Recovery Example
Post Processing
Client has need to reverse changes up to 90 days
Many updates have occurred since
Solution
-
Produce LLOG or LOAD output for target data
Post Process to reverse changes while preserving current data
© Copyright 7/7/2015 BMC Software, Inc
38
Migration
Data Warehouse
Test System Synchronization
To other Platforms
-
High-Speed Apply Engine
LOAD CSV or SDF (all character) Formats
© Copyright 7/7/2015 BMC Software, Inc
39
DB2 Data Migration
Don’t replicate entire files, just migrate the changes!!!
DB2 LOG
RBA 1000
RBA 2000
RBA 3000
RBA 4000
(inflight URID 1988)
Log Master
BATCH PGM
Migrated
1000 - 2000
less inflight
URID 1988
Log Master
BATCH PGM
Logical
Log(+1)
Logical
Log(+1)
Input to LOAD utility
or Apply SQL process
© Copyright 7/7/2015 BMC Software, Inc
Migrated
2000 - 3000
plus inflight
URID 1988
Log Master
BATCH PGM
Migrated
3000 - 4000
Logical
Log(+1)
REPOSITORY
Migrated RBA range
In-flight URIDs
40
Migration Example
To a Teradata Decision Support System
Used SQL to port
-
Post processed the SQL to change the comma delimiter
Wanted to limit size of any given extract
-
Used the OR LIMIT # LOG FILES
Selection criteria
-
List of 52 Tables to include and 18 batch jobs to exclude
> 800 Partitions – all compressed
Volume ~24 Billion transactions a year
© Copyright 7/7/2015 BMC Software, Inc
41
Migration Example
Synchronizing a Test System
Needed to keep Regression Test system in sync with production to prove
changes before implementation (462 Tables)
Use LLOG as the capture format
Use High-speed Apply Engine to process the LLOG
Has a process in place to “Refresh” the test system after major production DDL
changes or utility windows
RESETs the ONGOING capture after refresh
© Copyright 7/7/2015 BMC Software, Inc
43
UDCL
Unload – Drop – Create – Load
Reduce Object Restructuring outage window
-
Standard Practice
 Put object into Read only
 Unload object being restructured
 STOP all access to object
 Drop object
 Create object with appropriate changes
– Repartitioning
– COLUMN changes (metrics or location)
Load Table
REBIND plans
START access to object
Outage lasts the entire length of the UNLOAD/LOAD window



-
© Copyright 7/7/2015 BMC Software, Inc
47
Traditional ‘UDCL’ Method
Read Only
Pre Image
Copy
Unload
Data
Stop all Objects - OUTAGE
Drop
Tablespace
Create
Tablespace
Limited RO Activity
Load
Data
Post Image
Copy
Install New
Programs
Runstats
Bind/Rebind
Application
Available
© Copyright 7/7/2015 BMC Software, Inc
48
UDCL
Unload – Drop – Create – Load
Reduce Object Restructuring outage window
-
-
Log Master / Apply Plus Alternative




Create new object with appropriate changes
Unload old object
Load new object
Run job to Capture changes between Unload and Current (Apply too)



Put old object into read only
Do final Capture / Apply (reconcile)
STOP objects and RENAME


REBIND plans
START and carry on
– (Reiterravally, until ready to switch over)
–
–
Old object to temporary name
New object to old name
Outage only lasts from the point of final capture through rename and follow on
actions
© Copyright 7/7/2015 BMC Software, Inc
49
SHRLEVEL CHANGE TRANSFORM
Prep Activities - Current tables, Data, Online and Batch Unaffected
Create new
Structures
(TS_New,
TB_New, IX_New)
Create OCC
From
TS_Orig,
Set
Log Mark(0)
Application Unavailable
Rename
TB/IX_New to
TB/IX_Orig
Transform
TS_New,
Rebuild
IX_New,
Inline Copy,
Runstats
© Copyright 7/7/2015 BMC Software, Inc
DB2 Log
Set New Log
Mark(+1)
Application RO
STOP all
Final
Rename
Log Update
TB/IX_Orig to
TB_New
TB/IX_Backup
Start TS_New,
TB_Orig, IX_Orig
Execute Log Scan from
TB_Orig
From Log Mark(0)
to Current Time
Creating Apply SQL
Start
TS_Orig
Read Only
Application Available
Update all
TS/IX jobs
Rebind
& Utils,
DROP TS_Orig
Update
TB_New
Apply
SQL
Files
50
Wrap up
Auditing
Recovery
Migration
Alternative uses of migration
Additional ideas to share?
Questions?
© Copyright 7/7/2015 BMC Software, Inc
51
Audit Your SOX Off,
and other uses of a DB2 Log Tool
Ken McDonald
BMC Software
[email protected]
© Copyright 7/7/2015 BMC Software, Inc
52