Rem Rem $Header: create_wb_rt_ht_audit.sql 12-mar-2008.10:11:35 tzrose Exp $ Rem Rem create_wb_rt_ht_audit.sql Rem Rem Copyright (c) 2007, Oracle. All rights reserved. Rem Rem NAME Rem create_wb_rt_ht_audit.sql - create runtime audit table definitions Rem Rem DESCRIPTION Rem This script is used to create runtime audit tables and indexes Rem for a heterogeneous environment using a DB2 database Rem Rem NOTES Rem Logon to the DB2 database to be used for the Audit Data Source Rem db2 -t -vf /owb/rtasst/jrtaudit/db2/create_wb_rt_ht_audit.sql Rem Rem MODIFIED (MM/DD/YY) Rem tzrose 10/10/07 - Created to be compatible for DB2 Rem CREATE TABLE wb_rt_ht_audit_job ( job_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_job PRIMARY KEY ,job_uoid VARCHAR(32) ,job_name VARCHAR(80) NOT NULL ,job_type VARCHAR(30) ,job_status NUMERIC(10,0) ,jrt_exec_key VARCHAR(32) ,start_time TIMESTAMP NOT NULL ,elapse_time NUMERIC(10,0) ,time_zone VARCHAR(30) ,jndi_name VARCHAR(80) ,object_id VARCHAR(32) ,operation VARCHAR(80) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ,last_update_date TIMESTAMP ,last_updated_by VARCHAR(30) ); CREATE TABLE wb_rt_ht_audit_phase ( phase_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_phase PRIMARY KEY ,top_level_phase_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_phase_top REFERENCES wb_rt_ht_audit_phase (phase_audit_id) ,parent_phase_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_phase_par REFERENCES wb_rt_ht_audit_phase (phase_audit_id) ON DELETE CASCADE ,job_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT fk_ht_phase_job REFERENCES wb_rt_ht_audit_job (job_audit_id) ON DELETE CASCADE ,phase_uoid VARCHAR(32) ,phase_name VARCHAR(80) NOT NULL ,phase_type VARCHAR(30) ,phase_status NUMERIC(10,0) ,jrt_exec_key VARCHAR(32) ,phase_errors NUMERIC(10,0) ,start_time TIMESTAMP NOT NULL ,elapse_time NUMERIC(10,0) ,time_zone VARCHAR(30) ,operation VARCHAR(80) ,control_centre_task_name VARCHAR(80) ,control_centre_task_type VARCHAR(30) ,src_method VARCHAR(80) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ,last_update_date TIMESTAMP ,last_updated_by VARCHAR(30) ); CREATE TABLE wb_rt_ht_audit_task ( task_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_task PRIMARY KEY ,top_level_task_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_task_top REFERENCES wb_rt_ht_audit_task (task_audit_id) ,parent_task_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_task_par REFERENCES wb_rt_ht_audit_task (task_audit_id) ON DELETE CASCADE ,phase_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT fk_ht_task_phase REFERENCES wb_rt_ht_audit_phase (phase_audit_id) ON DELETE CASCADE ,task_uoid VARCHAR(32) ,task_name VARCHAR(80) NOT NULL ,task_type VARCHAR(30) ,task_desc VARCHAR(2000) ,task_status NUMERIC(10,0) ,jrt_exec_key VARCHAR(32) ,task_errors NUMERIC(10,0) ,start_time TIMESTAMP NOT NULL ,elapse_time NUMERIC(10,0) ,time_zone VARCHAR(30) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ,last_update_date TIMESTAMP ,last_updated_by VARCHAR(30) ); CREATE TABLE wb_rt_ht_audit_step ( step_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_step PRIMARY KEY ,task_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT fk_ht_step_task REFERENCES wb_rt_ht_audit_task (task_audit_id) ON DELETE CASCADE ,step_uoid VARCHAR(32) ,step_name VARCHAR(80) NOT NULL ,step_type VARCHAR(30) ,step_info VARCHAR(2000) ,step_status NUMERIC(10,0) ,jrt_exec_key VARCHAR(32) ,data_errors NUMERIC(10,0) ,recs_select NUMERIC(10,0) ,recs_insert NUMERIC(10,0) ,recs_update NUMERIC(10,0) ,recs_delete NUMERIC(10,0) ,recs_merge NUMERIC(10,0) ,start_time TIMESTAMP NOT NULL ,elapse_time NUMERIC(10,0) ,time_zone VARCHAR(30) ,exec_statement VARCHAR(2000) ,exec_statement_type VARCHAR(30) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ,last_update_date TIMESTAMP ,last_updated_by VARCHAR(30) ); CREATE TABLE wb_rt_ht_audit_struct ( struct_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_struct PRIMARY KEY ,step_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT fk_ht_struct_step REFERENCES wb_rt_ht_audit_step (step_audit_id) ON DELETE CASCADE ,struct_info VARCHAR(2000) ,parent_operator_uoid VARCHAR(32) ,parent_object_uoid VARCHAR(32) ,parent_object_type VARCHAR(30) ,parent_object_loc_uoid VARCHAR(32) ,parent_object_name VARCHAR(80) ,object_uoid VARCHAR(32) ,object_type VARCHAR(30) ,object_loc_uoid VARCHAR(32) ,object_name VARCHAR(80) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ,last_update_date TIMESTAMP ,last_updated_by VARCHAR(30) ); CREATE TABLE wb_rt_ht_audit_error ( error_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_error PRIMARY KEY ,task_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_error_task REFERENCES wb_rt_ht_audit_task (task_audit_id) ON DELETE CASCADE ,step_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_error_step REFERENCES wb_rt_ht_audit_step (step_audit_id) ON DELETE CASCADE ,rowkey NUMERIC(22,0) ,dest_table_name VARCHAR(80) ,dest_column_name VARCHAR(80) ,statement VARCHAR(2000) ,error_severity NUMERIC(10,0) ,error_number NUMERIC(10,0) ,error_message VARCHAR(2000) ,error_stack_trace VARCHAR(4000) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ,last_update_date TIMESTAMP ,last_updated_by VARCHAR(30) ); CREATE TABLE wb_rt_ht_audit_error_source ( error_source_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_source PRIMARY KEY ,error_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT fk_ht_source_error REFERENCES wb_rt_ht_audit_error (error_audit_id) ON DELETE CASCADE ,error_source_info VARCHAR(2000) ,table_name VARCHAR(80) ,column_name VARCHAR(80) ,column_seq NUMERIC(10,0) ,column_value VARCHAR(2000) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ,last_update_date TIMESTAMP ,last_updated_by VARCHAR(30) ); CREATE TABLE wb_rt_ht_audit_trace ( trace_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_trace PRIMARY KEY ,step_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_trace_step REFERENCES wb_rt_ht_audit_step (step_audit_id) ON DELETE CASCADE ,error_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_trace_error REFERENCES wb_rt_ht_audit_error (error_audit_id) ON DELETE CASCADE ,trace_info VARCHAR(2000) ,table_name VARCHAR(80) ,statement VARCHAR(2000) ,value VARCHAR(2000) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ,last_update_date TIMESTAMP ,last_updated_by VARCHAR(30) ); CREATE TABLE wb_rt_ht_audit_file ( file_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_file PRIMARY KEY ,task_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_file_task REFERENCES wb_rt_ht_audit_task (task_audit_id) ON DELETE CASCADE ,step_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_file_step REFERENCES wb_rt_ht_audit_step (step_audit_id) ON DELETE CASCADE ,file_type VARCHAR(64) ,format_type NUMERIC(10,0) ,file_text CLOB ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ); CREATE TABLE wb_rt_ht_audit_param ( param_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_param PRIMARY KEY ,phase_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT fk_ht_param_phase REFERENCES wb_rt_ht_audit_phase (phase_audit_id) ON DELETE CASCADE ,param_name VARCHAR(80) NOT NULL ,param_uoid VARCHAR(32) ,param_type NUMERIC(10,0) NOT NULL ,param_kind NUMERIC(10,0) NOT NULL ,param_mode NUMERIC(10,0) NOT NULL ,param_scope NUMERIC(10,0) NOT NULL ,is_required NUMERIC(1,0) NOT NULL ,is_fixed NUMERIC(1,0) NOT NULL ,value_kind NUMERIC(10,0) NOT NULL ,value_mode NUMERIC(10,0) NOT NULL ,value VARCHAR(4000) ,type_length NUMERIC(10,0) ,type_scale NUMERIC(10,0) ,type_precision NUMERIC(10,0) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ); CREATE TABLE wb_rt_ht_audit_msg ( msg_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT pk_ht_msg PRIMARY KEY ,job_audit_id NUMERIC(22,0) NOT NULL CONSTRAINT fk_ht_msg_job REFERENCES wb_rt_ht_audit_job (job_audit_id) ON DELETE CASCADE ,phase_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_msg_phase REFERENCES wb_rt_ht_audit_phase (phase_audit_id) ,task_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_msg_task REFERENCES wb_rt_ht_audit_task (task_audit_id) ,step_audit_id NUMERIC(22,0) CONSTRAINT fk_ht_msg_step REFERENCES wb_rt_ht_audit_step (step_audit_id) ,log_type VARCHAR(30) ,log_status NUMERIC(10,0) ,log_level NUMERIC(10,0) ,log_msg VARCHAR(2000) ,msg_id NUMERIC(22,0) ,created_by VARCHAR(30) NOT NULL ,creation_date TIMESTAMP NOT NULL ); CREATE INDEX wb_rt_ht_job_name ON wb_rt_ht_audit_job (job_name); CREATE INDEX wb_rt_ht_job_uoid ON wb_rt_ht_audit_job (job_uoid); CREATE INDEX wb_rt_ht_job_key ON wb_rt_ht_audit_job (jrt_exec_key); CREATE INDEX wb_rt_ht_phase_job ON wb_rt_ht_audit_phase (job_audit_id); CREATE INDEX wb_rt_ht_phase_name ON wb_rt_ht_audit_phase (phase_name); CREATE INDEX wb_rt_ht_phase_key ON wb_rt_ht_audit_phase (jrt_exec_key); CREATE INDEX wb_rt_ht_phase_st ON wb_rt_ht_audit_phase (start_time); CREATE INDEX wb_rt_ht_phase_top ON wb_rt_ht_audit_phase (top_level_phase_audit_id); CREATE INDEX wb_rt_ht_phase_par ON wb_rt_ht_audit_phase (parent_phase_audit_id); CREATE INDEX wb_rt_ht_phase_uoid ON wb_rt_ht_audit_phase (phase_uoid, phase_type, phase_name); CREATE INDEX wb_rt_ht_task_phase ON wb_rt_ht_audit_task (phase_audit_id); CREATE INDEX wb_rt_ht_task_name ON wb_rt_ht_audit_task (task_name); CREATE INDEX wb_rt_ht_task_key ON wb_rt_ht_audit_task (jrt_exec_key); CREATE INDEX wb_rt_ht_task_st ON wb_rt_ht_audit_task (start_time); CREATE INDEX wb_rt_ht_task_top ON wb_rt_ht_audit_task (top_level_task_audit_id); CREATE INDEX wb_rt_ht_task_par ON wb_rt_ht_audit_task (parent_task_audit_id); CREATE INDEX wb_rt_ht_step_task ON wb_rt_ht_audit_step (task_audit_id); CREATE INDEX wb_rt_ht_step_name ON wb_rt_ht_audit_step (step_name); CREATE INDEX wb_rt_ht_step_key ON wb_rt_ht_audit_step (jrt_exec_key); CREATE INDEX wb_rt_ht_struct_par ON wb_rt_ht_audit_struct (parent_object_uoid, parent_object_type, parent_object_loc_uoid); CREATE INDEX wb_rt_ht_struct_obj ON wb_rt_ht_audit_struct (object_uoid, object_type, object_loc_uoid); CREATE INDEX wb_rt_ht_error_task ON wb_rt_ht_audit_error (task_audit_id); CREATE INDEX wb_rt_ht_error_step ON wb_rt_ht_audit_error (step_audit_id); CREATE INDEX wb_rt_ht_source_error ON wb_rt_ht_audit_error_source (error_audit_id); CREATE INDEX wb_rt_ht_trace_step ON wb_rt_ht_audit_trace (step_audit_id); CREATE INDEX wb_rt_ht_trace_error ON wb_rt_ht_audit_trace (error_audit_id); CREATE INDEX wb_rt_ht_file_task ON wb_rt_ht_audit_file (task_audit_id); CREATE INDEX wb_rt_ht_file_step ON wb_rt_ht_audit_file (step_audit_id); CREATE INDEX wb_rt_ht_param_phase ON wb_rt_ht_audit_param (phase_audit_id); CREATE INDEX wb_rt_ht_msg_job ON wb_rt_ht_audit_msg (job_audit_id); CREATE INDEX wb_rt_ht_msg_phase ON wb_rt_ht_audit_msg (phase_audit_id); CREATE INDEX wb_rt_ht_msg_task ON wb_rt_ht_audit_msg (task_audit_id); CREATE INDEX wb_rt_ht_msg_step ON wb_rt_ht_audit_msg (step_audit_id); CREATE TABLE wb_rt_ht_audit_sequence ( SEQ_NAME VARCHAR(30) NOT NULL ,SEQ_COUNT NUMERIC(22,0) NOT NULL ); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditJob',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditPhase',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditTask',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditStep',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditStruct',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditError',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditErrorSource',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditTrace',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditFile',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditParam',0); INSERT INTO wb_rt_ht_audit_sequence (SEQ_NAME, SEQ_COUNT) VALUES ('AuditMsg',0);