Rem
Rem $Header: bsln_pkgdef.sql 12-may-2005.13:33:48 jberesni Exp $
Rem
Rem bsln_pkgdef.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem bsln_pkgdef.sql - Baseline packages (creation).
Rem
Rem DESCRIPTION
Rem This script defines the packaged procedures and functions required
Rem for metric baseline support.
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem jberesni 05/12/05 - fix 4353079
Rem jberesni 04/16/05 - merge from DB control
Rem jberesni 03/05/05 - sync with DB control
Rem mbhoopat 11/22/04 - fix decl problem
Rem jberesni 11/18/04 - refactor
Rem jberesni 10/18/04 - update thresholds
Rem jberesni 09/28/04 - migrate from DB control
Rem jberesni 09/23/04 - fix 3910279
Rem jberesni 08/10/04 - daynight
Rem jberesni 08/01/04 - compute_all and set_all
Rem jberesni 07/28/04 - restructure
Rem jberesni 07/23/04 - misc fixes
Rem jberesni 07/15/04 - candidate1
Rem jsoule 05/19/04 - add exceptions
Rem jsoule 05/18/04 - external constants
Rem jsoule 05/17/04 - update
Rem jsoule 05/10/04 - Created
Rem
create or replace
package mgmt_bsln
-----------------------------
-- Grid Control deployment 0
-----------------------------
as
-----------------------------------------------------------------------------
--
-- externally visible constants and subtypes
--
-----------------------------------------------------------------------------
K_BSLN_XX constant mgmt_bsln_baselines.subinterval_key%type := 'XX';
K_BSLN_HX constant mgmt_bsln_baselines.subinterval_key%type := 'HX';
K_BSLN_XD constant mgmt_bsln_baselines.subinterval_key%type := 'XD';
K_BSLN_HD constant mgmt_bsln_baselines.subinterval_key%type := 'HD';
K_BSLN_XW constant mgmt_bsln_baselines.subinterval_key%type := 'XW';
K_BSLN_HW constant mgmt_bsln_baselines.subinterval_key%type := 'HW';
K_BSLN_NW constant mgmt_bsln_baselines.subinterval_key%type := 'NW';
K_BSLN_ND constant mgmt_bsln_baselines.subinterval_key%type := 'ND';
K_BSLN_NX constant mgmt_bsln_baselines.subinterval_key%type := 'NX';
K_DEFAULT_KEY_VALUE constant varchar2(10) := ' ';
K_DEFAULT_NUM_OCCURS constant number := 1;
K_FAIL_ACTION_UNSET constant varchar2(16) := 'UNSET';
K_FAIL_ACTION_PRESERVE constant varchar2(16) := 'PRESERVE';
K_METHOD_SIGLVL constant mgmt_bsln_threshold_parms.threshold_method%type := 'SIGLVL';
K_METHOD_PCTMAX constant mgmt_bsln_threshold_parms.threshold_method%type := 'PCTMAX';
K_SIGLVL_95 constant number := 0.95;
K_SIGLVL_99 constant number := 0.99;
K_SIGLVL_999 constant number := 0.999;
K_SIGLVL_9999 constant number := 0.9999;
K_SOURCE_EM constant mgmt_bsln_datasources.source_type%type := 'EM';
K_SOURCE_DB constant mgmt_bsln_datasources.source_type%type := 'DB';
K_TRUE constant integer := 1;
K_FALSE constant integer := 0;
K_BSLN_STATIC constant mgmt_bsln_baselines.type%type := 'S';
K_BSLN_ROLLING constant mgmt_bsln_baselines.type%type := 'R';
K_STATUS_ACTIVE constant mgmt_bsln_baselines.status%type := 'ACTIVE';
K_STATUS_INACTIVE constant mgmt_bsln_baselines.status%type := 'INACTIVE';
---------------------------------------------------------------------------
--
-- package exception declarations
--
---------------------------------------------------------------------------
X_INVALID_BASELINE constant number := -20101;
X_INVALID_INTERVAL constant number := -20102;
X_DATASOURCE_NOT_FOUND constant number := -20103;
X_INVALID_THRESHOLD_METHOD constant number := -20104;
X_INVALID_METRIC constant number := -20105;
X_BASELINE_NOT_FOUND constant number := -20106;
X_SOURCE_CONFLICT constant number := -20107;
X_NOT_SUPPORTED constant number := -20108;
X_BSLNTHR_ERROR constant number := -20109;
INVALID_BASELINE exception;
INVALID_INTERVAL exception;
DATASOURCE_NOT_FOUND exception;
INVALID_THRESHOLD_METHOD exception;
INVALID_METRIC exception;
BASELINE_NOT_FOUND exception;
SOURCE_CONFLICT exception;
NOT_SUPPORTED exception;
BSLNTHR_ERROR exception;
PRAGMA EXCEPTION_INIT(INVALID_BASELINE, -20101);
PRAGMA EXCEPTION_INIT(INVALID_INTERVAL, -20102);
PRAGMA EXCEPTION_INIT(DATASOURCE_NOT_FOUND, -20103);
PRAGMA EXCEPTION_INIT(INVALID_THRESHOLD_METHOD, -20104);
PRAGMA EXCEPTION_INIT(INVALID_METRIC, -20105);
PRAGMA EXCEPTION_INIT(BASELINE_NOT_FOUND, -20106);
PRAGMA EXCEPTION_INIT(SOURCE_CONFLICT, -20107);
PRAGMA EXCEPTION_INIT(NOT_SUPPORTED, -20108);
PRAGMA EXCEPTION_INIT(BSLNTHR_ERROR, -20109);
----------------------------------------------------------------------------
--
-- package subtypes
--
----------------------------------------------------------------------------
subtype guid_t is mgmt_bsln_baselines.bsln_guid%type;
subtype subinterval_code_t is mgmt_bsln_statistics.subinterval_code%type;
subtype subinterval_key_t is mgmt_bsln_baselines.subinterval_key%type;
subtype key_value_t is mgmt_bsln_datasources.key_value%type;
subtype fail_action_t is mgmt_bsln_threshold_parms.fail_action%type;
subtype threshold_method_t is mgmt_bsln_threshold_parms.THRESHOLD_METHOD%TYPE;
subtype param_value_t is mgmt_bsln_threshold_parms.critical_param%type;
-- deployment-specific subtype declaration
subtype alert_threshold_t is varchar2(256);
-----------------------------------------------------------------------------
--
-- utility modules
--
-----------------------------------------------------------------------------
function valid_key (subinterval_key_in subinterval_key_t)
return boolean;
function target_uid
(target_guid_in in guid_t)
return guid_t
DETERMINISTIC;
function target_uid
(dbid_in in mgmt_bsln_datasources.dbid%type
,instance_num_in in mgmt_bsln_datasources.instance_num%type)
return guid_t
DETERMINISTIC;
function this_target_uid
return guid_t;
function metric_uid
(metric_guid_in in guid_t)
return guid_t
DETERMINISTIC;
function metric_uid
(metric_id_in in mgmt_bsln_datasources.metric_id%type)
return guid_t
DETERMINISTIC;
function datasource_guid
(target_uid_in in guid_t
,metric_uid_in in guid_t
,key_value_in in key_value_t := K_DEFAULT_KEY_VALUE)
return guid_t
DETERMINISTIC;
function baseline_guid
(target_uid_in in guid_t
,name_in in mgmt_bsln_baselines.name%type)
return guid_t
DETERMINISTIC;
function stdhh24 (date_in in date)
return binary_integer;
function subinterval_code
(subinterval_key_in in subinterval_key_t
,time_in in date)
return subinterval_code_t;
function cached_subinterval_code
(subinterval_key_in in subinterval_key_t
,time_in in date)
return subinterval_code_t;
function target_source_type (target_uid_in in mgmt_bsln.guid_t)
return varchar2;
function baseline_is_active (bsln_guid_in in guid_t)
return boolean;
function datasource_rec(ds_guid_in in guid_t) RETURN mgmt_bsln_datasources%ROWTYPE;
function baseline_rec(bsln_guid_in in guid_t) RETURN mgmt_bsln_baselines%ROWTYPE;
-----------------------------------------------------------------------------
--
-- administration modules
--
-----------------------------------------------------------------------------
procedure update_moving_window
(interval_days_in in number
,subinterval_key_in in subinterval_key_t
,target_uid_in in guid_t := null
);
procedure create_baseline_static
(name_in in mgmt_bsln_baselines.name%type
,interval_begin_in in date
,interval_end_in in date
,subinterval_key_in in subinterval_key_t
,target_uid_in in guid_t := null
);
procedure create_baseline_rolling
(name_in in mgmt_bsln_baselines.name%type
,subinterval_key_in in subinterval_key_t
,interval_days_in in number
,target_uid_in in guid_t := null
);
procedure drop_baseline
(name_in in mgmt_bsln_baselines.name%type
,target_uid_in in guid_t := null
);
procedure register_datasource
(target_guid_in in guid_t
,metric_guid_in in guid_t
,key_value_in in key_value_t := K_DEFAULT_KEY_VALUE
);
procedure register_datasource
(dbid_in in mgmt_bsln_datasources.dbid%type
,instance_num_in in mgmt_bsln_datasources.instance_num%type
,metric_id_in in mgmt_bsln_datasources.metric_id%type
);
function registered_ds_guid
(target_guid_in in guid_t
,metric_guid_in in guid_t
,key_value_in in key_value_t := K_DEFAULT_KEY_VALUE)
return guid_t;
function registered_ds_guid
(dbid_in in mgmt_bsln_datasources.dbid%type
,instance_num_in in mgmt_bsln_datasources.instance_num%type
,metric_id_in in mgmt_bsln_datasources.metric_id%type)
return guid_t;
procedure deregister_datasource
(target_guid_in in guid_t
,metric_guid_in in guid_t
,key_value_in in key_value_t := K_DEFAULT_KEY_VALUE);
procedure deregister_datasource
(dbid_in in mgmt_bsln_datasources.dbid%type
,instance_num_in in mgmt_bsln_datasources.instance_num%type
,metric_id_in in mgmt_bsln_datasources.metric_id%type);
procedure activate_baseline
(name_in in mgmt_bsln_baselines.name%type
,target_uid_in in guid_t := null
);
procedure deactivate_baseline
(name_in in mgmt_bsln_baselines.name%type
,target_uid_in in guid_t := null
);
procedure unset_threshold_parameters
(bsln_guid_in in guid_t
,ds_guid_in in guid_t
);
procedure set_threshold_parameters
(bsln_guid_in in guid_t
,ds_guid_in in guid_t
,threshold_method_in in mgmt_bsln_threshold_parms.threshold_method%type
,warning_param_in in mgmt_bsln_threshold_parms.warning_param%type
,critical_param_in in mgmt_bsln_threshold_parms.critical_param%type
,num_occurs_in in integer := K_DEFAULT_NUM_OCCURS
,fail_action_in in fail_action_t := K_FAIL_ACTION_UNSET
);
-----------------------------------------------------------------------------
--
-- operational routines
--
-----------------------------------------------------------------------------
procedure set_all_thresholds;
procedure compute_all_statistics;
-----------------------------------------------------------------------------
--
-- submit and drop jobs to compute and set thresholds
--
-----------------------------------------------------------------------------
procedure submit_bsln_jobs;
procedure delete_bsln_jobs;
-----------------------------------------------------------------------------
--
-- new enable/disable API
--
-----------------------------------------------------------------------------
function is_enabled return integer;
-----------------------------------------------------------------------------
--
-- extraction cursor record and ref cursor types
--
-----------------------------------------------------------------------------
type extract_rectype is record
(datasource_guid mgmt_bsln.guid_t
,bsln_guid mgmt_bsln.guid_t
,subinterval_key mgmt_bsln_baselines.subinterval_key%TYPE
,obs_time date
,obs_value number
);
type extract_cvtype is ref cursor return extract_rectype;
-----------------------------------------------------------------------------
--
-- extract and compute statistics modules
--
-----------------------------------------------------------------------------
function extract_compute_stats
(extract_cv in extract_cvtype
,compute_date_in in date := SYSDATE)
return bsln_statistics_set
PIPELINED
CLUSTER extract_cv by (datasource_guid)
PARALLEL_ENABLE
(PARTITION extract_cv BY HASH(datasource_guid));
function exptail_stats (observation_set_in bsln_observation_set)
return bsln_statistics_set;
function compute_statistics
(bsln_name_in in mgmt_bsln_baselines.name%type
,interval_begin_in in date
,interval_end_in in date
,subinterval_key_in in subinterval_key_t
,target_uid_in in guid_t := null
)
return bsln_statistics_set;
procedure load_statistics
(statistics_set_in in bsln_statistics_set
,replace_flag_in in boolean := TRUE);
function data_and_model_OK
(threshold_method_in in varchar2
,threshold_param_in in number
,sample_count_in in number
,fit_quality_in in number
)
return integer;
----------------------------------------------------------------------
-- record type to pass to new_threshold_value function
----------------------------------------------------------------------
TYPE THR_rectype is RECORD
(threshold_method mgmt_bsln_threshold_parms.threshold_method%TYPE
,num_occurrences mgmt_bsln_threshold_parms.num_occurrences%TYPE
,warning_param mgmt_bsln_threshold_parms.warning_param%TYPE
,critical_param mgmt_bsln_threshold_parms.critical_param%TYPE
,fail_action mgmt_bsln_threshold_parms.fail_action%TYPE
,sample_count number
,minval number
,maxval number
,pctile_95 number
,pctile_99 number
,pctile_999 number
,pctile_9999 number
,est_fit_quality number
,est_sample_count number
);
procedure new_threshold_value
(THR_rec_in THR_rectype
,param_in mgmt_bsln_threshold_parms.warning_param%TYPE
,value_inout in out alert_threshold_t);
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
--
-- SLPA declarations for Design by Contract support
--
-----------------------------------------------------------------------------
ASSERTFAIL EXCEPTION;
ASSERTFAIL_C CONSTANT INTEGER := -20999;
PRAGMA EXCEPTION_INIT(ASSERTFAIL, -20999);
PKGNAME_C CONSTANT VARCHAR2(20) := 'MGMT_BSLN';
-----------------------------------------------------------------------------
end mgmt_bsln;
/
show errors