-- unload_samples.txt -- 2018 11 14 Date first published for use with Foxhound 4. -- 2018 11 16 Examples and MESSAGE DEBUG ONLY statements expanded. -- -- ********************************************************************************** -- © Copyright 2018 RisingRoad. All rights reserved. All unpublished rights reserved. -- breck.carter@gmail.com -- www.risingroad.com -- ********************************************************************************** -- This script is provided AS IS, without warranty or liability of any kind. -- You may use, reproduce, modify and distribute this script without -- limitation, on the condition that you retain the foregoing copyright -- notice and disclaimer as to the original code. -- ********************************************************************************** -- -- unload_samples - Unloads some or all of the rows from the Foxhound 4 sample-related views. -- -- Syntax -- -- CALL unload_samples ( from_timestamp, -- to_timestamp, -- sampling_id, -- folder_path ); -- -- from_timestamp The TIMESTAMP value for the earliest rows to be selected from the views -- that contain timestamp columns; e.g., alert but not alerts_criteria. -- A NULL or omitted value means that no earliest limit is placed on these rows. -- -- to_timestamp The TIMESTAMP value for the latest rows to be selected from the views -- that contain timestamp columns; e.g., alert but not alerts_criteria. -- A NULL or omitted value means that no latest limit is placed on these rows. -- -- sampling_id The UNSIGNED INT value for the one sampling_id value to be selected -- from views that are specific to individual target databases; e.g., -- alerts_criteria but not exception_diagnostic. -- A NULL or omitted value means all target databases are included. -- -- folder_path The string specifying the path where the output text files are written. -- A NULL or omitted value is interpreted as C:\temp. -- -- Examples -- -- 1. Unload all the samples recorded since May 1 2018 and write the files to C:\temp. -- READ unload_samples.txt; -- CALL unload_samples ( '2018-05-01' ); -- -- 2. Unload the last 7 day's samples to C:\temp. -- READ unload_samples.txt; -- CALL unload_samples ( CURRENT TIMESTAMP - 7 ); -- -- 3. Turn on MESSAGE TO CONSOLE debugging, then unload all October 2018 samples to C:\temp. -- SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON'; -- READ unload_samples.txt; -- CALL unload_samples ( '2018-10-01', '2018-11-01' ); -- -- 4. Unload all samples for target database 2 to C:\temp. -- CALL unload_samples ( @sampling_id = 2 ); -- READ unload_samples.txt; -- -- 5. Unload samples for the first week of May 2018 for target database 5 to C:\data\db5. -- READ unload_samples.txt; -- CALL unload_samples ( '2018-05-01', '2018-05-08', 5, 'C:\\data\\db5' ); -- -- 6. Same as Example 5, using named arguments in a different order. -- READ unload_samples.txt; -- CALL unload_samples ( @input_folder_path = 'C:\\data\\db5', -- @sampling_id = 5, -- @from_timestamp = '2018-05-01', -- @to_timestamp = '2018-05-08' ); -- -- Remarks -- -- Here are the views that are unloaded: -- -- 1. alert One row per alert. -- 2. alert_cancelled One row per alert cancellation. -- 3. alerts_criteria One row for the Monitor Options page settings for each sampling session. -- 4. all_clear One row per alert all-clear. -- 5. autodropped_connection One row per connection that was dropped by the AutoDrop facility. -- 6. exception_diagnostic One row for each time Foxhound detected an error or other important event. -- 7. peaks One row for each target database, holding various peak values. -- 8. ping_log One row for each time the custom ping process was run. -- 9. purge_run One row for each run of the database purge process. -- 10. sample_connection One row for each connection for each sample holding connection-level properties. -- 11. sample_detail One row for each sample. -- 12. sample_header One row for each sample holding server and database-level properties. -- 13. sampling_options One row for each target database. -- 14. schedule One row for each week-long schedule defined on the Monitor Options page. -- 15. schedule_day_entry One row for each day in one schedule. -- 16. schedule_period_entry One row for each 15-minute period in one day in one schedule. BEGIN DROP PROCEDURE unload_samples; EXCEPTION WHEN OTHERS THEN -- ignore END; CREATE TEMPORARY PROCEDURE unload_samples ( IN @from_timestamp TIMESTAMP DEFAULT NULL, IN @to_timestamp TIMESTAMP DEFAULT NULL, IN @sampling_id UNSIGNED INT DEFAULT NULL, IN @input_folder_path LONG VARCHAR DEFAULT 'C:\\temp\\' ) BEGIN DECLARE @range_condition VARCHAR ( 100 ) DEFAULT '[empty]'; DECLARE @folder_path LONG VARCHAR DEFAULT '[empty]'; DECLARE @sql LONG VARCHAR DEFAULT '[empty]'; DECLARE @sqlcode INTEGER; DECLARE @sqlstate VARCHAR ( 5 ); DECLARE @errormsg VARCHAR ( 32767 ); CASE WHEN @from_timestamp IS NULL AND @to_timestamp IS NULL THEN SET @range_condition = ''; WHEN @from_timestamp IS NULL AND @to_timestamp IS NOT NULL THEN SET @range_condition = STRING ( ' <= ''', @to_timestamp, '''' ); WHEN @from_timestamp IS NOT NULL AND @to_timestamp IS NULL THEN SET @range_condition = STRING ( ' >= ''', @from_timestamp, '''' ); ELSE SET @range_condition = STRING ( ' BETWEEN ''', @from_timestamp, ''' AND ''', @to_timestamp, '''' ); END CASE; IF @input_folder_path IS NULL THEN SET @folder_path = 'C:\\temp\\'; ELSE SET @folder_path = TRIM ( @input_folder_path ); IF RIGHT ( @folder_path, 1 ) <> '\\' THEN SET @folder_path = STRING ( @folder_path, '\\' ); END IF; END IF; -------------------------------------------------- -- alert SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.alert WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, IF @range_condition = '' THEN '' ELSE STRING ( ' AND alert_in_effect_at', @range_condition ) END IF, ' ORDER BY alert_occurrence', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'alert.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 1. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 1. Unload alert OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- alert_cancelled SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.alert_cancelled WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, IF @range_condition = '' THEN '' ELSE STRING ( ' AND alert_all_clear_at', @range_condition ) END IF, ' ORDER BY alert_cancelled_occurrence', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'alert_cancelled.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 2. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 2. Unload alert_cancelled OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- alerts_criteria SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.alerts_criteria WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, ' AND criteria_set_type = ''Target Specific''', -- 'Factory Settings' and 'Saved Defaults' are not included ' ORDER BY sampling_id, criteria_set_type', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'alerts_criteria.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 3. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 3. Unload alerts_criteria OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- all_clear SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.all_clear WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, IF @range_condition = '' THEN '' ELSE STRING ( ' AND alert_all_clear_at', @range_condition ) END IF, ' ORDER BY all_clear_occurrence', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'all_clear.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 4. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 4. Unload all_clear OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- autodropped_connection SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.autodropped_connection WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, IF @range_condition = '' THEN '' ELSE STRING ( ' AND autodrop_issued_at', @range_condition ) END IF, ' ORDER BY autodrop_occurrence', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'autodropped_connection.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 5. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 5. Unload autodropped_connection OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- exception_diagnostic SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.exception_diagnostic', ' ORDER BY exception_id', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'exception_diagnostic.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 6. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 6. Unload exception_diagnostic OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- peaks SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.peaks WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, ' ORDER BY sampling_id', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'peaks.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 7. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 7. Unload peaks OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- ping_log SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.ping_log WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, IF @range_condition = '' THEN '' ELSE STRING ( ' AND ping_log_inserted_at', @range_condition ) END IF, ' ORDER BY ping_id', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'ping_log.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 8. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 8. Unload ping_log OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- purge_run SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.purge_run WHERE 1 = 1', IF @range_condition = '' THEN '' ELSE STRING ( ' AND started_at', @range_condition ) END IF, ' ORDER BY run_number', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'purge_run.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 9. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 9. Unload purge_run OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- sample_connection SET @sql = STRING ( 'UNLOAD SELECT sample_connection.*', ' FROM DBA.sample_connection INNER JOIN DBA.sample_detail', ' ON sample_connection.sample_set_number = sample_detail.sample_set_number', ' WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sample_detail.sampling_id = ', @sampling_id ) END IF, IF @range_condition = '' THEN '' ELSE STRING ( ' AND sample_detail.sample_recorded_at', @range_condition ) END IF, ' ORDER BY sample_detail.sample_set_number, sample_connection.connection_number', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'sample_connection.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 10. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 10. Unload sample_connection OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- sample_detail SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.sample_detail WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, IF @range_condition = '' THEN '' ELSE STRING ( ' AND sample_recorded_at', @range_condition ) END IF, ' ORDER BY sample_set_number', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'sample_detail.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 11. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 11. Unload sample_detail OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- sample_header SET @sql = STRING ( 'UNLOAD SELECT sample_header.*', ' FROM DBA.sample_header INNER JOIN DBA.sample_detail', ' ON sample_header.sample_set_number = sample_detail.sample_set_number', ' WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sample_header.sampling_id = ', @sampling_id ) END IF, IF @range_condition = '' THEN '' ELSE STRING ( ' AND sample_detail.sample_recorded_at', @range_condition ) END IF, ' ORDER BY sample_header.sample_set_number', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'sample_header.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 12. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 12. Unload sample_header OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- sampling_options SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.sampling_options WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, ' ORDER BY sampling_id', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'sampling_options.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 13. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 13. Unload sampling_options OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- schedule SET @sql = STRING ( 'UNLOAD SELECT * FROM DBA.schedule WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND sampling_id = ', @sampling_id ) END IF, ' ORDER BY schedule_id', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'schedule.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 14. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 14. Unload schedule OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- schedule_day_entry SET @sql = STRING ( 'UNLOAD SELECT schedule_day_entry.*', ' FROM DBA.schedule_day_entry INNER JOIN DBA.schedule', ' ON schedule_day_entry.schedule_id = schedule.schedule_id', ' WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND schedule.sampling_id = ', @sampling_id ) END IF, ' ORDER BY schedule_day_entry.schedule_id, schedule_day_entry.day_number', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'schedule_day_entry.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 15. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 15. Unload schedule_day_entry OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- -- schedule_period_entry SET @sql = STRING ( 'UNLOAD SELECT schedule_period_entry.*', ' FROM DBA.schedule_period_entry INNER JOIN DBA.schedule', ' ON schedule_period_entry.schedule_id = schedule.schedule_id', ' WHERE 1 = 1', IF @sampling_id IS NULL THEN '' ELSE STRING ( ' AND schedule.sampling_id = ', @sampling_id ) END IF, ' ORDER BY schedule_period_entry.schedule_id, schedule_period_entry.period_number', ' TO ''', REPLACE ( @folder_path, '\\', '\\\\' ), 'schedule_period_entry.txt''' ); MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 16. @sql = ', @sql ) TO CONSOLE DEBUG ONLY; EXECUTE IMMEDIATE @sql; MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' 16. Unload schedule_period_entry OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' All done OK' ) TO CONSOLE DEBUG ONLY; -------------------------------------------------- EXCEPTION WHEN OTHERS THEN SELECT SQLCODE, SQLSTATE, ERRORMSG() INTO @sqlcode, @sqlstate, @errormsg; MESSAGE STRING ( 'EXCEPTION in CALL unload_samples() at ', CURRENT TIMESTAMP, ': SQLCODE = ', @sqlcode, ', SQLSTATE = ', @sqlstate, ', ERRORMSG() = ', @errormsg, ', @sql = ', @sql ) TO CLIENT; MESSAGE STRING ( 'EXCEPTION in CALL unload_samples() at ', CURRENT TIMESTAMP, ': SQLCODE = ', @sqlcode, ', SQLSTATE = ', @sqlstate, ', ERRORMSG() = ', @errormsg, ', @sql = ', @sql ) TO CONSOLE; RESIGNAL; END;