{**************************************************************************} {* *} {* Licensed Materials - Property of IBM and/or HCL *} {* *} {* IBM Informix Dynamic Server *} {* (c) Copyright IBM Corporation 1996, 2009 All rights reserved. *} {* (c) Copyright HCL Technologies Ltd. 2017. All Rights Reserved. *} {* *} {**************************************************************************} {********************************************************* * * This sql file should be run against sysadmin database * to cleanup and reinstall the AUS feature * ********************************************************} {********************************************************* * * The following section cleans up all the resources * utilized by the AUS feature * ********************************************************} {********************************************************* * Allow us to delete these tasks and disable them so they do * not try and run while we are doing maintenance ********************************************************} UPDATE ph_task SET(tk_attributes,tk_enable)=(0,'f') WHERE tk_name MATCHES "Auto Update Statistics*"; {********************************************************* * Drop all AUS function/SPL ********************************************************} DROP FUNCTION IF EXISTS aus_get_realtime(); DROP FUNCTION IF EXISTS aus_refresh_stats(INTEGER, INTEGER, INTEGER); DROP FUNCTION IF EXISTS aus_refresh_stats(); DROP FUNCTION IF EXISTS aus_refresh_stats(INTEGER, INTEGER); DROP FUNCTION IF EXISTS aus_refresh_stats_orig(); DROP FUNCTION IF EXISTS aus_refresh_stats_orig(INTEGER, INTEGER); DROP FUNCTION IF EXISTS aus_refresh_upgrade(); DROP FUNCTION IF EXISTS aus_refresh_downgrade(); DROP FUNCTION IF EXISTS aus_evaluate_stats(INTEGER, INTEGER); DROP FUNCTION IF EXISTS aus_evaluator_upgrade(); DROP FUNCTION IF EXISTS aus_evaluator_downgrade(); DROP FUNCTION IF EXISTS aus_setup_mon_table_profile(INTEGER, INTEGER); DROP FUNCTION IF EXISTS aus_setup_mon_table_profile(INTEGER, INTEGER, INTEGER); DROP FUNCTION IF EXISTS aus_cleanup_table(INTEGER); DROP FUNCTION IF EXISTS aus_setup_table(); DROP FUNCTION IF EXISTS aus_setup_table(INTEGER); DROP FUNCTION IF EXISTS aus_enable_refresh(); DROP FUNCTION IF EXISTS aus_evaluator(BOOLEAN); DROP FUNCTION IF EXISTS aus_evaluator(INTEGER,INTEGER); DROP FUNCTION IF EXISTS aus_evaluator(INTEGER,INTEGER,INTEGER); DROP FUNCTION IF EXISTS aus_evaluator_dbs(char(128),INTEGER, INTEGER); DROP FUNCTION IF EXISTS aus_evaluator_dbs(char(128),INTEGER, INTEGER, INTEGER); DROP FUNCTION IF EXISTS aus_load_dbs_data(char(128),INTEGER); DROP FUNCTION IF EXISTS aus_load_dbs_data(char(128),INTEGER,INTEGER); DROP FUNCTION IF EXISTS aus_create_cmd_dist(char(128), INTEGER, CHAR(128), INTEGER, INTEGER, INTEGER, BIGINT); DROP FUNCTION IF EXISTS aus_get_exclusive_access(INTEGER, INTEGER); DROP FUNCTION IF EXISTS aus_rel_exclusive_access(); DROP TABLE IF EXISTS aus_work_lock; DROP TABLE IF EXISTS aus_work_icols; DROP TABLE IF EXISTS aus_work_dist; DROP TABLE IF EXISTS aus_work_info; DROP TABLE IF EXISTS aus_work_icols; DROP TABLE IF EXISTS aus_work_dist; DROP TABLE IF EXISTS aus_work_info; DROP TABLE IF EXISTS aus_cmd_info; DROP TABLE IF EXISTS aus_cmd_list; DROP TABLE IF EXISTS aus_cmd_comp; {********************************************************* * Cleanup all configuration params ********************************************************} DELETE FROM ph_threshold WHERE task_name IN ( "Auto Update Statistics Evaluation", "Auto Update Statistics Refresh" ) OR name MATCHES "AUS_*"; {********************************************************* * Remove the task from the database scheduler ********************************************************} UPDATE ph_task SET tk_attributes = BITANDNOT(tk_attributes, 4) WHERE tk_name MATCHES "Auto Update Statistics *"; DELETE from ph_task WHERE tk_name MATCHES "Auto Update Statistics *"; {* The following will reinstall AUS in sysadmin *} {********************************************************* * * The following section load all the parameters * utilized by AUS * ******************************************************** *} INSERT INTO ph_threshold (id,name,task_name,value,value_type,description) VALUES (0,"AUS_AGE","Auto Update Statistics Evaluation","30","NUMERIC(6.2)", "The statistics are rebuilt after this many days."); INSERT INTO ph_threshold (id,name,task_name,value,value_type,description) VALUES (0,"AUS_CHANGE","Auto Update Statistics Evaluation","10","NUMERIC", "The statistics are rebuilt after this percentage of data has changed."); INSERT INTO ph_threshold (id,name,task_name,value,value_type,description) VALUES (0,"AUS_AUTO_RULES","Auto Update Statistics Evaluation","1","NUMERIC", "Ensures a base set of guidelines are followed when building statistics."); INSERT INTO ph_threshold (id,name,task_name,value,value_type,description) VALUES (0,"AUS_SMALL_TABLES","Auto Update Statistics Evaluation","100","NUMERIC", "Tables containing less than this number of rows will always have their statistics rebuilt."); INSERT INTO ph_threshold (id,name,task_name,value,value_type,description) VALUES (0,"AUS_PDQ","Auto Update Statistics Refresh","10","NUMERIC", "Update statistics executes with this PDQ priority."); {********************************************************* * * The following section creates all the procedures * utilized by AUS * ******************************************************** *} CREATE FUNCTION informix.aus_get_realtime() RETURNING DATETIME YEAR TO SECOND DEFINE cur_time DATETIME YEAR TO SECOND; LET cur_time = (SELECT DBINFO( 'utc_to_datetime',sh_curtime)::DATETIME YEAR TO SECOND FROM sysmaster:sysshmvals); RETURN cur_time; END FUNCTION; CREATE FUNCTION informix.aus_setup_mon_table_profile(task_id INTEGER, task_seq INTEGER, inHDRmode INTEGER) RETURNING INTEGER DEFINE task_cnt INTEGER; DEFINE index_cnt INTEGER; --TRACE "FUNCTION aus_setup_mon_table_profile()" ; -- Make sure the task mon_table_profile is there an active SELECT count(*) INTO task_cnt FROM ph_task WHERE tk_name ="mon_table_profile"; -- Check to see if index exists on (id,partnum) SELECT count(*) INTO index_cnt FROM sysindices WHERE tabid = ( SELECT tabid FROM systables WHERE tabname = "mon_table_profile" ) AND ABS(ikeyextractcolno(indexkeys,0)) = ( SELECT colno FROM syscolumns WHERE tabid = ( SELECT tabid FROM systables WHERE tabname = "mon_table_profile" ) AND colname ='id' ) AND ABS(ikeyextractcolno(indexkeys,1)) = ( SELECT colno FROM syscolumns WHERE tabid = ( SELECT tabid FROM systables WHERE tabname = "mon_table_profile" ) AND colname ='partnum' ); IF task_cnt < 1 THEN INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "WARNING", "green", "SERVER","Auto Update Statistics", "Built in server task (mon_table_profile) is missing. " || "Auto Update Statistics proceeding with limited information.", NULL); RETURN -1; END IF; IF index_cnt < 1 THEN INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "green", "SERVER","Auto Update Statistics", "Building index on table mon_table_profile to optimize performance for Auto Update Statistics." , NULL); IF inHDRmode = 1 THEN CREATE INDEX mon_table_profile_sys_ix1 ON mon_table_profile(id,partnum) ONLINE; ELSE CREATE INDEX mon_table_profile_sys_ix1 ON mon_table_profile(id,partnum); END IF; END IF; RETURN 1; END FUNCTION; CREATE FUNCTION informix.aus_get_exclusive_access(task_id INTEGER, task_seq INTEGER) RETURNING INTEGER DEFINE errnum INTEGER; DEFINE cnt INTEGER; DEFINE lk_sid INTEGER; --TRACE "FUNCTION aus_get_exclusive_access()" ; BEGIN ON EXCEPTION IN ( -310 ) SET errnum SELECT count(*) , MAX(aus_lk_sid) INTO cnt, lk_sid FROM aus_work_lock, sysmaster:sysscblst WHERE aus_lk_sid = sid AND aus_lk_task_id = task_id AND aus_lk_task_seq = task_seq; --TRACE "CNT = " || cnt || "lock SID = "||lk_sid; IF cnt > 0 THEN RAISE EXCEPTION -777, -107, "Current AUS tables are in use by Session ID "||lk_sid; END IF -- Fall through if count equal zero DELETE FROM aus_work_lock; END EXCEPTION WITH RESUME CREATE TABLE aus_work_lock ( aus_lk_sid INTEGER, aus_lk_task_id INTEGER, aus_lk_task_seq INTEGER ); INSERT INTO aus_work_lock(aus_lk_sid,aus_lk_task_id,aus_lk_task_seq) VALUES (DBINFO('sessionid'),task_id,task_seq); END RETURN 0; END FUNCTION; CREATE FUNCTION informix.aus_rel_exclusive_access() RETURNING INTEGER --TRACE "FUNCTION aus_rel_exclusive_access()" ; BEGIN ON EXCEPTION IN ( -206, -310 ) END EXCEPTION DROP TABLE aus_work_lock; END RETURN 0; END FUNCTION; CREATE FUNCTION informix.aus_cleanup_table( save_results INTEGER ) RETURNING INTEGER DEFINE errnum INTEGER; DEFINE cnt INTEGER; --TRACE "FUNCTION aus_cleanup_table()" ; IF save_results = 0 THEN SELECT COUNT(*) INTO cnt FROM ph_task WHERE BITAND(tk_attributes, '0x200' ) > 0 AND tk_name like "Auto Update Statistics Refresh%" AND tk_next_execution <= ( SELECT tk_next_execution FROM ph_task WHERE tk_name = "Auto Update Statistics Evaluation"); IF cnt > 0 THEN RAISE EXCEPTION -214, -107, "Current AUS tables are in use"; END IF END IF /* Cleanup all the work tables */ BEGIN ON EXCEPTION IN ( -214, -206, -394 ) SET errnum -- non-exclusive access, & table does not exist IF errnum = -214 THEN RAISE EXCEPTION -214, -107, "Current AUS tables are in use"; END IF -- If error is -206 ignore the error, tables have been dropped END EXCEPTION WITH RESUME DROP TABLE aus_work_icols; DROP TABLE aus_work_dist; DROP TABLE aus_work_info; IF save_results == 0 THEN DROP TABLE aus_work_icols; DROP TABLE aus_work_dist; DROP TABLE aus_work_info; DROP TABLE aus_cmd_info; DROP VIEW aus_cmd_list; DROP VIEW aus_cmd_comp; DROP TABLE aus_command; -- drop views also END IF END RETURN 1; END FUNCTION; CREATE FUNCTION informix.aus_setup_table(inHDRmode INTEGER) RETURNING integer DEFINE errnum INTEGER; --TRACE "FUNCTION aus_setup_table()" ; /* Cleanup all the work tables */ BEGIN ON EXCEPTION IN ( -310 ) SET errnum -- table already exists END EXCEPTION WITH RESUME CREATE TABLE informix.aus_cmd_info ( aus_ci_dbs_partnum INTEGER, aus_ci_stime DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND, aus_ci_etime DATETIME YEAR TO SECOND DEFAULT NULL, aus_ci_database VARCHAR(255) DEFAULT NULL, aus_ci_locale VARCHAR(36) DEFAULT NULL, aus_ci_logmode CHAR(1) DEFAULT NULL, aus_ci_missed_tables INTEGER DEFAULT 0, aus_ci_need_tables INTEGER DEFAULT 0, aus_ci_done_tables INTEGER DEFAULT 0 ); INSERT INTO aus_cmd_info(aus_ci_dbs_partnum,aus_ci_stime) VALUES (0,aus_get_realtime()); IF inHDRmode = 1 THEN CREATE UNIQUE INDEX informix.aus_cmd_info_index1 ON aus_cmd_info(aus_ci_dbs_partnum) ONLINE; CREATE UNIQUE INDEX informix.aus_cmd_info_index2 ON aus_cmd_info(aus_ci_database) ONLINE; ELSE CREATE UNIQUE INDEX informix.aus_cmd_info_index1 ON aus_cmd_info(aus_ci_dbs_partnum); CREATE UNIQUE INDEX informix.aus_cmd_info_index2 ON aus_cmd_info(aus_ci_database); END IF; CREATE TABLE informix.aus_command ( aus_cmd_id SERIAL, aus_cmd_state CHAR(1) DEFAULT 'P' CHECK (aus_cmd_state IN ("P","I","E","C")), -- P => Command is pending -- I => Command is inprogress -- E => Command had an Error -- C => Command is complete w/o Errors aus_cmd_type CHAR(1), aus_cmd_dbs_priority SMALLINT DEFAULT 2, aus_cmd_priority BIGINT, aus_cmd_dbs_partnum INTEGER, aus_cmd_partnum INTEGER, aus_cmd_err_sql INTEGER, aus_cmd_err_isam INTEGER, aus_cmd_time DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND, aus_cmd_runtime INTERVAL HOUR TO SECOND DEFAULT NULL, aus_cmd_exe LVARCHAR(8192) ) LOCK MODE ROW; CREATE VIEW informix.aus_cmd_list AS SELECT aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe FROM aus_command WHERE aus_cmd_state = 'P'; CREATE VIEW informix.aus_cmd_comp AS SELECT aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe, aus_cmd_time FROM aus_command WHERE aus_cmd_state = 'C'; END BEGIN ON EXCEPTION IN ( -310 ) SET errnum -- table already exists END EXCEPTION WITH RESUME /* * This table contains both * index columns as wells as * user defined distribution columns */ CREATE TABLE informix.aus_work_icols ( aus_icols_tabid integer, aus_icols_colno integer, aus_icols_lkey char(1) CHECK( aus_icols_lkey IN ('Y','N')), aus_icols_mode char(1) DEFAULT NULL, aus_icols_colname varchar(128) ) LOCK MODE ROW; CREATE TABLE informix.aus_work_info ( aus_info_id SERIAL, aus_info_db_partnum INTEGER, aus_info_tabname VARCHAR(128), aus_info_tabid INTEGER, aus_info_partnum INTEGER, aus_info_ustlowts DATETIME YEAR TO SECOND, aus_info_npused BIGINT, aus_info_nrows BIGINT, aus_info_nindexes smallint ) LOCK MODE ROW; CREATE TABLE informix.aus_work_dist ( aus_dist_id serial, aus_dist_tabid INTEGER, -- this is the tabid aus_dist_colno INTEGER, aus_dist_mode CHAR(1), aus_dist_resolution FLOAT, aus_dist_confidence FLOAT, aus_dist_smplsize float, aus_dist_rowssmplde BIGINT, aus_dist_constr_time DATETIME YEAR TO SECOND, aus_dist_ustnrows BIGINT ) LOCK MODE ROW; END RETURN 1; END FUNCTION; CREATE FUNCTION informix.aus_refresh_stats_orig( ) RETURNING integer DEFINE rc INTEGER; DEFINE cnt INTEGER; DEFINE i INTEGER; LET rc=1; LET i=1; WHILE ( rc <> 0 ) LOOP LET rc = aus_refresh_stats_orig(-1,i); LET i=i+1; END LOOP; RETURN rc; END FUNCTION; CREATE FUNCTION informix.aus_refresh_stats_orig(task_id INTEGER, task_seq INTEGER) RETURNING integer DEFINE rc INTEGER; DEFINE cnt INTEGER; DEFINE del INTEGER; DEFINE t_partnum INTEGER; DEFINE last_partnum INTEGER; DEFINE t_dbs_partnum INTEGER; DEFINE last_dbs_partnum INTEGER; DEFINE t_id INTEGER; DEFINE t_type CHAR(1); DEFINE t_cmd CHAR(8192); DEFINE p_cmd CHAR(200); DEFINE t_priority BIGINT; DEFINE param_pdq INTEGER; --TRACE "FUNCTION aus_refresh_stats_orig"; LET rc = 0; LET cnt = 0; LET del = 0; LET last_partnum = 0; LET t_partnum = NULL; --SET DEBUG FILE TO "/tmp/aus.refresh."||task_seq; --TRACE ON; -- Get the config thresholds SELECT MAX(value::integer) INTO param_pdq FROM sysadmin:ph_threshold WHERE name = "AUS_PDQ"; IF param_pdq IS NULL THEN LET param_pdq = 0; ELIF param_pdq < 0 THEN LET param_pdq = 0; ELIF param_pdq > 100 THEN LET param_pdq = 100; END IF IF param_pdq > 0 AND is_pdq_allowed() > 0 THEN LET p_cmd = "SET PDQPRIORITY " ||param_pdq; END IF BEGIN ON EXCEPTION IN ( -206 ) -- Exit if aus_cmd_* tables are not found END EXCEPTION FOREACH SELECT --+ FIRST_ROWS aus_cmd_id, RTRIM(aus_cmd_exe),aus_cmd_partnum, aus_cmd_priority, aus_cmd_type, aus_cmd_dbs_partnum INTO t_id, t_cmd, t_partnum, t_priority, t_type , t_dbs_partnum FROM aus_command WHERE aus_cmd_state = 'P' -- Pending ORDER BY aus_cmd_priority DESC, aus_cmd_partnum, aus_cmd_type DESC --TRACE "LAST partnum ="||last_partnum; IF last_partnum == 0 THEN LET last_partnum = t_partnum; LET last_dbs_partnum = t_dbs_partnum; LET cnt = cnt + 1; ELIF last_partnum <> t_partnum THEN LET last_partnum = t_partnum; IF t_priority > 100000 THEN IF last_dbs_partnum <> t_dbs_partnum THEN CONTINUE FOREACH; END IF IF cnt > 100 THEN EXIT FOREACH; END IF LET cnt = cnt + 1; ELSE EXIT FOREACH; END IF END IF --TRACE "UPDATING cmd_id="||t_id ||" TABLE "|| t_partnum||" MODE " || RTRIM(t_type)::lvarchar ||" CNT =" ||cnt; DELETE FROM aus_command WHERE aus_cmd_id = t_id; LET del = DBINFO("sqlca.sqlerrd2"); /* If we did not delete anything then skip this row, parallel */ IF del < 0 THEN CONTINUE FOREACH; END IF BEGIN ON EXCEPTION IN ( -206 ) -- IGNORE if the table is gone END EXCEPTION WITH RESUME EXECUTE IMMEDIATE p_cmd; EXECUTE IMMEDIATE t_cmd; IF is_pdq_allowed() > 0 THEN SET PDQPRIORITY 0; END IF END INSERT INTO aus_command ( aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_state, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe ) VALUES ( t_id, t_type, t_priority, 'C', t_dbs_partnum, t_partnum, TRIM(t_cmd)); LET rc=rc+1; END FOREACH IF cnt > 0 THEN UPDATE aus_cmd_info SET (aus_ci_done_tables) = (aus_ci_done_tables + cnt) WHERE aus_ci_dbs_partnum = last_dbs_partnum; ELIF task_id > 0 THEN /* I have no more work to and I am a real task disable my self */ UPDATE ph_task SET ( tk_enable ) = ('F') WHERE tk_id = task_id; END IF END RETURN rc; END FUNCTION; CREATE FUNCTION informix.aus_enable_refresh() RETURNING INTEGER UPDATE ph_task SET ( tk_enable) = ('T') WHERE tk_name matches "Auto Update Statistics Refresh*"; END FUNCTION; CREATE FUNCTION informix.aus_create_cmd_dist(dbsname char(128), dbs_partnum INTEGER, tabname CHAR(128), partnum INTEGER, tabid INTEGER, param_rules INTEGER, expire_priority BIGINT) RETURNING BIGINT DEFINE rc INTEGER; DEFINE cnt INTEGER; DEFINE collist CHAR(7600); DEFINE maxcollength INTEGER; DEFINE collistlen INTEGER; DEFINE collen INTEGER; DEFINE t_colno INTEGER; DEFINE t_resolution DECIMAL(5,3); DEFINE last_resolution DECIMAL(5,3); DEFINE t_confidence DECIMAL(5,3); DEFINE t_smplsize DECIMAL(16,3); DEFINE t_colname CHAR(128); DEFINE tmp CHAR(128); DEFINE lasttmp CHAR(128); DEFINE end_str CHAR(256); --TRACE "FUNCTION aus_create_cmd_dist( database=" || RTRIM(dbsname) || " tabid = " || tabid || " )" ; LET collist = NULL; LET rc = 0; /* Note the l must be lower case, so we * can order by later can get the order as * H, M, l * when building commands. */ INSERT INTO aus_command ( aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe ) VALUES ( 0, "l", expire_priority, dbs_partnum, partnum, "UPDATE STATISTICS LOW FOR TABLE " || RTRIM(dbsname) || ":" || RTRIM(tabname) ); /* FIND ALL HIGH COLUMNS * The first part finds all column * which were previously in sysdistrib * The second part SQL is executed if * the auto rules are enable, it finds * the minimum set of columns for this * table for the optimizer */ UPDATE aus_work_icols SET ( aus_icols_mode ) = ( 'H' ) WHERE aus_icols_tabid = tabid AND aus_icols_colno in ( SELECT aus_dist_colno FROM aus_work_dist WHERE aus_dist_tabid = tabid AND aus_dist_mode = 'H'); --TRACE "EXISTING HIGH ROWS UPDATED " || DBINFO("sqlca.sqlerrd2") || ")"; IF param_rules > 0 THEN UPDATE aus_work_icols SET ( aus_icols_mode ) = ( decode(aus_icols_lkey,'Y','H','M') ) WHERE aus_icols_tabid = tabid AND aus_icols_mode IS NULL; --TRACE "PARAM_RULES ROWS UPDATED " || DBINFO("sqlca.sqlerrd2") || ")"; ELSE SELECT count(*) INTO cnt FROM aus_work_icols WHERE aus_icols_mode IS NULL AND aus_icols_tabid = tabid; IF cnt > 0 THEN UPDATE aus_cmd_info SET (aus_ci_missed_tables) = (aus_ci_missed_tables + 1) WHERE aus_ci_database = dbsname; END IF END IF /** HIGH COLUMNS **/ LET collist = NULL; LET maxcollength = 7600; LET collistlen = 0; LET collen = 0; LET tmp = NULL; LET lasttmp = NULL; FOREACH SELECT aus_icols_colno, aus_icols_colname, NVL(aus_dist_resolution,0.5) INTO t_colno, t_colname, t_resolution FROM aus_work_icols , OUTER aus_work_dist WHERE aus_icols_tabid = aus_dist_tabid AND aus_icols_colno = aus_dist_colno AND aus_icols_tabid = tabid AND aus_icols_mode = 'H' ORDER BY aus_dist_resolution, aus_dist_colno IF collist IS NULL THEN LET collist = RTRIM(t_colname); LET collistlen = LENGTH(TRIM(collist)); LET last_resolution = t_resolution; LET end_str = " RESOLUTION " || t_resolution || " DISTRIBUTIONS ONLY"; ELIF last_resolution == t_resolution THEN LET collen = LENGTH(RTRIM(t_colname)); -- TRACE "collist len: " || collistlen; IF collen + collistlen + 2 >= maxcollength THEN INSERT INTO aus_command ( aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe ) VALUES ( 0, "H", expire_priority, dbs_partnum, partnum, "UPDATE STATISTICS HIGH FOR TABLE " || RTRIM(dbsname) || ":" || RTRIM(tabname) || " ( " || TRIM(collist) || " ) " || TRIM(end_str) ); LET collist = RTRIM(t_colname); LET collistlen = LENGTH(TRIM(collist)); ELSE LET collist = RTRIM(collist) || ", " || RTRIM(t_colname); LET collistlen = collistlen + collen + 2; END IF ELSE INSERT INTO aus_command ( aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe ) VALUES ( 0, "H", expire_priority, dbs_partnum, partnum, "UPDATE STATISTICS HIGH FOR TABLE " || RTRIM(dbsname) || ":" || RTRIM(tabname) || " ( " || TRIM(collist) || " ) " || TRIM(end_str) ); LET collist = TRIM(t_colname); LET collistlen = LENGTH(TRIM(collist)); LET end_str = " RESOLUTION " || t_resolution || " DISTRIBUTIONS ONLY"; LET last_resolution = t_resolution; END IF --TRACE "COLLIST LENGTH: " || LENGTH(TRIM(collist)); --TRACE "HIGH Colname " || RTRIM(t_colname); END FOREACH IF collist IS NOT NULL THEN INSERT INTO aus_command ( aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe ) VALUES ( 0, "H", expire_priority, dbs_partnum, partnum, "UPDATE STATISTICS HIGH FOR TABLE "|| RTRIM(dbsname) || ":" || RTRIM(tabname) || " ( "|| RTRIM(collist) || " ) " || TRIM(end_str) ); END IF /* -- FIND ALL MED COLUMNS */ UPDATE aus_work_icols SET ( aus_icols_mode ) = ( 'M' ) WHERE aus_icols_tabid = tabid AND aus_icols_colno in ( SELECT aus_dist_colno FROM aus_work_dist WHERE aus_dist_tabid = tabid AND aus_dist_mode = 'M'); LET collist = NULL; LET collistlen = 0; LET collen = 0; LET tmp = NULL; LET lasttmp = NULL; FOREACH SELECT aus_icols_colno, aus_icols_colname, NVL(aus_dist_resolution,2.0), NVL(aus_dist_confidence,0.95), NVL(aus_dist_smplsize,0.0), NVL(aus_dist_smplsize,0)::DECIMAL(16,3) || "_" || NVL(aus_dist_resolution,0)::DECIMAL(5,2) || "_" || NVL(aus_dist_confidence,0)::DECIMAL(4,2) AS C INTO t_colno, t_colname, t_resolution, t_confidence, t_smplsize, tmp FROM aus_work_icols , OUTER aus_work_dist WHERE aus_icols_tabid = aus_dist_tabid AND aus_icols_colno = aus_dist_colno AND aus_icols_tabid = tabid AND aus_icols_mode = 'M' ORDER BY C, aus_dist_colno --TRACE "MED Colname " || RTRIM(t_colname) || " tmp" || RTRIM(tmp); --TRACE "collist " || TRIM(NVL(collist,"NULL")); --TRACE "lasttmp " || TRIM(NVL(lasttmp,"NULL")); --TRACE "tmp " || TRIM(NVL(tmp,"NULL")); IF collist IS NULL THEN LET collist = TRIM(t_colname); LET collistlen = LENGTH(TRIM(collist)); LET lasttmp = tmp; LET end_str = decode(t_smplsize,0.0," "," SAMPLING SIZE " || t_smplsize ) || " RESOLUTION "|| t_resolution || " " || t_confidence || " DISTRIBUTIONS ONLY"; --TRACE "NULL collist" || TRIM(t_colname) || " tmp" || TRIM(tmp); ELIF lasttmp IS NULL OR lasttmp == tmp THEN --TRACE "equal collist" || TRIM(t_colname) || " tmp" || TRIM(tmp); LET collen = LENGTH(RTRIM(t_colname)); --TRACE "collist len: " || collistlen; IF collen + collistlen + 2 >= maxcollength THEN INSERT INTO aus_command ( aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe ) VALUES ( 0, "M", expire_priority, dbs_partnum, partnum, "UPDATE STATISTICS MEDIUM FOR TABLE " || RTRIM(dbsname) || ":" || TRIM(tabname) || " (" || TRIM(collist) || ") " || TRIM(end_str) ); LET collist = RTRIM(t_colname); LET collistlen = LENGTH(TRIM(collist)); ELSE LET collist = RTRIM(collist) || ", " || RTRIM(t_colname); LET collistlen = collistlen + collen + 2; END IF ELSE INSERT INTO aus_command ( aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe ) VALUES ( 0, "M", expire_priority, dbs_partnum, partnum, "UPDATE STATISTICS MEDIUM FOR TABLE " || RTRIM(dbsname) || ":" || TRIM(tabname) || " ("|| TRIM(collist) || ") " || TRIM(end_str) ); LET collist = TRIM(t_colname); LET collistlen = LENGTH(TRIM(collist)); LET lasttmp = tmp; LET end_str = decode(t_smplsize,0.0," "," SAMPLING SIZE " || t_smplsize ) || " RESOLUTION " || t_resolution || " " || t_confidence || " DISTRIBUTIONS ONLY"; END IF --TRACE "MED Colname " || TRIM(t_colname); END FOREACH IF collist IS NOT NULL THEN INSERT INTO aus_command ( aus_cmd_id, aus_cmd_type, aus_cmd_priority, aus_cmd_dbs_partnum, aus_cmd_partnum, aus_cmd_exe ) VALUES ( 0, "M", expire_priority, dbs_partnum, partnum, "UPDATE STATISTICS MEDIUM FOR TABLE " || TRIM(dbsname) || ":" || TRIM(tabname) || " (" || TRIM(collist) || ") " || TRIM(end_str) ); END IF RETURN 0; END FUNCTION; CREATE FUNCTION informix.aus_evaluator_dbs(dbsname CHAR(128), dbs_partnum INTEGER, has_mon_tab_prof INTEGER, inHDRmode INTEGER) RETURNING integer DEFINE aus_if_id INTEGER; DEFINE aus_if_tabname CHAR(128); DEFINE aus_if_tabid INTEGER; DEFINE aus_if_partnum INTEGER; DEFINE aus_if_ustlowts DATETIME YEAR TO SECOND; DEFINE aus_if_npused BIGINT; DEFINE aus_if_nrows BIGINT; DEFINE aus_real_rows BIGINT; DEFINE aus_if_nindexes INTEGER; DEFINE oldest_time DATETIME YEAR TO SECOND; DEFINE expired_value BIGINT; DEFINE max_seq_id INTEGER; DEFINE min_seq_id INTEGER; DEFINE hours_expired BIGINT; DEFINE nrows, isreads, iswrite, isrwrite, isdelete FLOAT; DEFINE delta_time BIGINT; DEFINE small_tab_exp BIGINT; DEFINE tmin BIGINT; DEFINE tmp BIGINT; DEFINE param_age_stats INTEGER; -- Number of hours DEFINE param_change INTEGER; DEFINE param_rules INTEGER; DEFINE param_small_tab BIGINT; DEFINE row_count INTEGER; DEFINE cmd CHAR(1000); --TRACE "FUNCTION aus_evaluator_dbs(database="||TRIM(dbsname)::lvarchar||")" ; -- Get the config thresholds SELECT MAX(value::float * 24)::integer INTO param_age_stats FROM sysadmin:ph_threshold WHERE name = "AUS_AGE"; SELECT MAX(value::integer) INTO param_change FROM sysadmin:ph_threshold WHERE name = "AUS_CHANGE"; SELECT MAX(value::integer) INTO param_rules FROM sysadmin:ph_threshold WHERE name = "AUS_AUTO_RULES"; SELECT MAX(value::bigint) INTO param_small_tab FROM sysadmin:ph_threshold WHERE name = "AUS_SMALL_TABLES"; /* Default values if not found in the config table */ LET param_age_stats = NVL(param_age_stats ,30*24); LET param_change = NVL(param_change ,10); LET param_rules = NVL(param_rules ,1); LET param_small_tab = NVL(param_small_tab ,100); IF param_small_tab > 200000 THEN /* Can not let be higher than 200,000 as * will cause overflow of variable */ LET param_small_tab = 200000; END IF LET row_count = 0; /* Loop through the specified database */ FOREACH SELECT aus_info_id, aus_info_tabname, aus_info_tabid, aus_info_partnum, aus_info_ustlowts, aus_info_npused, aus_info_nrows, aus_info_nindexes INTO aus_if_id, aus_if_tabname, aus_if_tabid, aus_if_partnum, aus_if_ustlowts, aus_if_npused, aus_if_nrows, aus_if_nindexes FROM aus_work_info WHERE aus_info_db_partnum = dbs_partnum ORDER BY aus_info_ustlowts IF aus_if_partnum = 0 THEN LET cmd = "UPDATE aus_work_info SET (aus_info_nrows) = (" || " (SELECT sum(pt.nrows)" || " FROM sysmaster:sysptnhdr pt" || " WHERE pt.partnum IN (SELECT partn FROM " || TRIM(dbsname)::lvarchar||":sysfragments sfg" || " WHERE sfg.tabid = " || aus_if_tabid || " AND sfg.fragtype = 'T')))" || " WHERE aus_info_tabid = " || aus_if_tabid ; EXECUTE IMMEDIATE cmd; LET cmd = "UPDATE aus_work_info" || " SET(aus_info_partnum) = ((SELECT min(lockid)" || " FROM sysmaster:sysptnhdr ptn, " || TRIM(dbsname)::lvarchar||":sysfragments" || " sfg WHERE ptn.partnum = sfg.partn" || " AND sfg.tabid = " || aus_if_tabid || " AND sfg.fragtype = 'T' ))" || " WHERE aus_info_tabid = " || aus_if_tabid ; EXECUTE IMMEDIATE cmd; SELECT aus_info_nrows INTO aus_real_rows FROM aus_work_info WHERE aus_info_tabid = aus_if_tabid; ELSE SELECT sum(pt.nrows) INTO aus_real_rows FROM sysmaster:sysptnhdr pt WHERE pt.partnum = aus_if_partnum; END IF --TRACE "Checking table "||TRIM(dbsname)::lvarchar||"." || TRIM(aus_if_tabname)::lvarchar; --TRACE "Number of rows "||aus_real_rows; LET expired_value = 0; /* If the table is considered a small row table * expire the table and create the commands. */ LET tmp = param_small_tab - aus_real_rows; IF tmp > 0 THEN LET small_tab_exp = tmp + 10000 * param_small_tab; ELSE LET small_tab_exp = 0; END IF /* Calculate the oldesttime between * the statistics and distributions */ SELECT MIN(aus_dist_constr_time) INTO oldest_time FROM aus_work_dist WHERE aus_dist_tabid = aus_if_tabid AND aus_dist_constr_time IS NOT NULL ; IF oldest_time IS NULL AND aus_if_ustlowts IS NULL THEN --TRACE "No Statistics and distributions for "|| TRIM(dbsname)::lvarchar||"." || TRIM(aus_if_tabname)::lvarchar; IF param_rules == 0 THEN /* If we have no stats or dist and we are not * doing auto rules then continue to the next table */ UPDATE aus_cmd_info SET (aus_ci_missed_tables) = (aus_ci_missed_tables + 1) WHERE aus_ci_dbs_partnum = dbs_partnum; CONTINUE FOREACH; ELIF small_tab_exp == 0 THEN /* We have auto rules on, but this tables is not small. */ LET expired_value = 100; ELSE /* We have auto rules on, and a small table */ LET expired_value = small_tab_exp; END IF GOTO do_commands; END IF IF oldest_time IS NULL THEN LET oldest_time = aus_if_ustlowts; ELIF (aus_if_ustlowts IS NOT NULL) AND (aus_if_ustlowts < oldest_time) THEN LET oldest_time = aus_if_ustlowts; END IF --TRACE "Oldest time for table "|| TRIM(aus_if_tabname)::lvarchar || " is "|| oldest_time; /* If we have a small table and * we have any stats or dist OR we are doing auto rules * THEN goto make the commands */ IF (small_tab_exp > 0) AND ( oldest_time IS NOT NULL OR param_rules <> 0 ) THEN LET expired_value = small_tab_exp; GOTO do_commands; END IF /* If we are not applying basic rules * and we have no upd stats low information * for this table before then skip * this table now and look for distributions. */ IF param_rules == 0 AND (aus_if_nrows IS NULL OR aus_if_ustlowts IS NULL) THEN GOTO do_distribution_eval; END IF /* * Check the age of the statistics * If never been done then assign 100 */ --TRACE "Start statistics checking basic row count " || expired_value; --TRACE "aus_if_nrows "|| NVL(aus_if_nrows,"NULL"); --TRACE "aus_real_rows "|| NVL(aus_real_rows,"NULL"); IF aus_if_nrows IS NULL OR aus_real_rows IS NULL THEN LET expired_value = expired_value + 100; ELSE LET tmp = (aus_real_rows - aus_if_nrows) / (aus_if_nrows+1) * 100; --TRACE "Change in basic row count " || tmp ||"%"; IF ABS(tmp) > param_change THEN LET expired_value = expired_value + ABS(tmp); END IF END IF /* * Check the basic row count of the statistics * If never been done then assign 100 */ --TRACE "Start statistics checking for age " || expired_value ; IF oldest_time IS NULL THEN LET expired_value = expired_value + 100; ELSE --TRACE "param_age_stats "||param_age_stats; LET hours_expired = ( ( ( (CURRENT - oldest_time)::INTERVAL HOUR(5) TO HOUR - param_age_stats UNITS HOUR))::char(20))::BIGINT; --TRACE "hours_expired "||hours_expired; IF hours_expired > 0 THEN LET expired_value = expired_value + hours_expired/24; END IF END IF --TRACE "INFO statistics checking basic row count END " || expired_value; <> --TRACE "Statitics checking advanced row count START " || expired_value; IF oldest_time IS NOT NULL AND has_mon_tab_prof == 1 THEN -- Find the MAX/MIN sequence numbers to use for this table statistics -- Currently we do not take into account onstat -z SELECT MAX(run.run_task_seq), MIN(run.run_task_seq), ((((MAX(run.run_time) - MIN(run.run_time)):: INTERVAL MINUTE(9) TO MINUTE)::char(20))::BIGINT) INTO max_seq_id, min_seq_id, delta_time FROM ph_run run, ph_task task WHERE task.tk_id = run.run_task_id AND tk_name = "mon_table_profile" AND run.run_time > oldest_time; --TRACE "Delta Time "|| delta_time ||" max task_seq "||max_seq_id||" min task_seq "||min_seq_id; /* If we had 0 or 1 measurement then delta_time will be 0 * we must skip this check, not enough data */ IF delta_time > 0 THEN LET tmin = (((CURRENT - oldest_time):: INTERVAL MINUTE(9) TO MINUTE)::char(20))::BIGINT; { Get the number of UDIS for statistics } --TRACE "SELECT " || aus_if_partnum ; SELECT tmin * ((SUM(aft.nrows) - SUM(bef.nrows))/delta_time), tmin * ((SUM(aft.pf_isread) - SUM(bef.pf_isread))/delta_time), tmin * ((SUM(aft.pf_iswrite) - SUM(bef.pf_iswrite))/delta_time), tmin * ((SUM(aft.pf_isrwrite) - SUM(bef.pf_isrwrite))/delta_time), tmin * ((SUM(aft.pf_isdelete) - SUM(bef.pf_isdelete))/delta_time) INTO nrows, isreads, iswrite, isrwrite, isdelete FROM mon_table_profile bef, mon_table_profile aft WHERE aft.id = max_seq_id AND bef.id = min_seq_id AND aft.lockid = aus_if_partnum AND bef.lockid = aus_if_partnum AND bef.lockid = aft.lockid; --TRACE "Inserts " || iswrite || "Update " || isrwrite|| "Delete " || isdelete; LET tmp = (iswrite + isrwrite + isdelete) - ( aus_real_rows * param_change/100); --TRACE "Rows Changed " || (iswrite + isrwrite + isdelete) || " Precent Changed " || (iswrite + isrwrite + isdelete) / aus_real_rows ; IF tmp > 0 THEN LET expired_value = expired_value + 100 + tmp; END IF END IF END IF /* * DONE calculating the statistics ranking */ IF expired_value == 0 THEN CONTINUE FOREACH; END IF <> --TRACE "Building stats on table "||TRIM(dbsname)::lvarchar||"." || TRIM(aus_if_tabname)::lvarchar; /* Build the update low stats command if required */ LET tmp = aus_create_cmd_dist(dbsname, dbs_partnum, aus_if_tabname, aus_if_partnum, aus_if_tabid, param_rules , expired_value ); --TRACE "NEW CMD row_count="||row_count; IF tmp < 0 THEN RETURN tmp; END IF LET row_count = row_count + 1; END FOREACH --- End aus_dbs_list FOREACH RETURN row_count; END FUNCTION; CREATE FUNCTION informix.aus_load_dbs_data(aus_dbsname CHAR(128), aus_dbs_partnum INTEGER ,inHDRmode INTEGER) RETURNING bigint DEFINE tmp CHAR(1500); DEFINE nrows BIGINT; DEFINE drows BIGINT; LET nrows = 0; LET drows = 0; /* *********************************************************** * Load the aus_work_info table from systables ************************************************************ */ LET tmp = "INSERT INTO aus_work_info (" || " aus_info_id, aus_info_tabname, aus_info_tabid, aus_info_partnum, " || " aus_info_ustlowts, aus_info_npused, aus_info_nrows, " || " aus_info_nindexes, aus_info_db_partnum " || " ) SELECT " || " 0, tabname, tabid, partnum, " || " ustlowts, npused, nrows, " || " nindexes, " || aus_dbs_partnum || " FROM " || TRIM(aus_dbsname)::lvarchar||":systables t " || " WHERE " || " t.tabtype='T'" ; EXECUTE IMMEDIATE tmp ; --TRACE "Loading "||TRIM(aus_dbsname)::lvarchar||":aus_work_info with ROWS(" || DBINFO("sqlca.sqlerrd2")||")"; LET nrows = nrows + DBINFO("sqlca.sqlerrd2"); IF (aus_dbsname == "sysadmin") THEN DELETE FROM aus_work_info WHERE aus_info_tabname LIKE "aus_work_%" AND aus_info_db_partnum = aus_dbs_partnum; LET nrows = nrows - 4; END IF; IF inHDRmode = 1 THEN CREATE INDEX informix.aus_work_info_idx1 ON aus_work_info(aus_info_tabid) ONLINE; CREATE INDEX informix.aus_work_info_idx2 ON aus_work_info(aus_info_partnum) ONLINE; ELSE CREATE INDEX informix.aus_work_info_idx1 ON aus_work_info(aus_info_tabid); CREATE INDEX informix.aus_work_info_idx2 ON aus_work_info(aus_info_partnum); END IF /* *********************************************************** * Load the aus_work_dist table from sysdistrib table ************************************************************ */ LET tmp = "INSERT INTO aus_work_dist (" || " aus_dist_id, aus_dist_tabid, aus_dist_colno, " || " aus_dist_mode, aus_dist_resolution, aus_dist_confidence, " || " aus_dist_smplsize, aus_dist_rowssmplde, aus_dist_constr_time, " || " aus_dist_ustnrows " || " ) SELECT " || " 0, tabid, colno, " || " mode, resolution, confidence, " || " smplsize,rowssmpld,constr_time, " || " ustnrows" || " FROM " || TRIM(aus_dbsname)::lvarchar||":sysdistrib d " || " WHERE d.seqno=1 " ; EXECUTE IMMEDIATE tmp ; LET drows = drows + DBINFO("sqlca.sqlerrd2"); IF inHDRmode = 1 THEN CREATE INDEX informix.aus_work_dist_idx1 ON aus_work_dist (aus_dist_tabid,aus_dist_colno) ONLINE; ELSE CREATE INDEX informix.aus_work_dist_idx1 ON aus_work_dist (aus_dist_tabid,aus_dist_colno); END IF --TRACE "Loading "||TRIM(aus_dbsname)::lvarchar||":aus_work_dist with ROWS(" || DBINFO("sqlca.sqlerrd2")||")"; /* We need to update the fragment tables partnum with * the lock id, so we can reference them as one item. * Also get the current number of rows. UPDATE aus_work_info SET ( aus_info_partnum, aus_info_nrows) = ( ( SELECT MAX(lockid), SUM(nrows) FROM sysmaster:systabnames t, sysmaster:sysptnhdr pt WHERE t.partnum = pt.partnum AND t.partnum = aus_info_partnum AND t.tabname = aus_info_tabname AND t.dbsname = aus_dbsname) ) WHERE aus_info_db_partnum = aus_dbs_partnum AND aus_info_nrows = -1 ; */ /* *********************************************************** * Load the aus_work_icols table of all index colnames ************************************************************ */ LET tmp = "CREATE VIEW informix.aus_view (tabid,colno, colname,lkey) AS " || " SELECT " || " C.tabid, C.colno, MAX(C.colname), 'N' " || " FROM " || TRIM(aus_dbsname)::lvarchar||":syscolumns AS C, " || TRIM(aus_dbsname)::lvarchar||":sysindices AS I " || " WHERE " || " C.tabid = I.tabid " || " AND C.colno in " || " (ABS(ikeyextractcolno(indexkeys,0)), " || " ABS(ikeyextractcolno(indexkeys,1)) , " || " ABS(ikeyextractcolno(indexkeys,2)) , " || " ABS(ikeyextractcolno(indexkeys,3)) , " || " ABS(ikeyextractcolno(indexkeys,4)) , " || " ABS(ikeyextractcolno(indexkeys,5)) , " || " ABS(ikeyextractcolno(indexkeys,6)) , " || " ABS(ikeyextractcolno(indexkeys,7)) , " || " ABS(ikeyextractcolno(indexkeys,8)) , " || " ABS(ikeyextractcolno(indexkeys,9)) , " || " ABS(ikeyextractcolno(indexkeys,10)) , " || " ABS(ikeyextractcolno(indexkeys,11)) , " || " ABS(ikeyextractcolno(indexkeys,12)) , " || " ABS(ikeyextractcolno(indexkeys,13)) , " || " ABS(ikeyextractcolno(indexkeys,14)) , " || " ABS(ikeyextractcolno(indexkeys,15)) ) " || " GROUP BY 1,2,4 " || "UNION " || "SELECT " || " C.tabid, C.colno, MAX(C.colname), 'N' " || " FROM " || TRIM(aus_dbsname)::lvarchar||":syscolumns AS C, " || "aus_work_dist AS D " || " WHERE C.tabid = D.aus_dist_tabid " || " AND C.colno = D.aus_dist_colno" || " GROUP BY 1,2,4 " ; EXECUTE IMMEDIATE tmp ; LET tmp = "INSERT INTO aus_work_icols" || "(aus_icols_tabid, aus_icols_colno,aus_icols_colname,aus_icols_lkey) " || " SELECT tabid, colno, colname,lkey FROM aus_view"; EXECUTE IMMEDIATE tmp ; --TRACE "Loading "||TRIM(aus_dbsname)::lvarchar||":aus_work_icols with ROWS(" || DBINFO("sqlca.sqlerrd2")||")"; DROP VIEW aus_view; IF inHDRmode = 1 THEN CREATE INDEX informix.aus_work_icols_idx1 ON aus_work_icols (aus_icols_tabid,aus_icols_colno) ONLINE; ELSE CREATE INDEX informix.aus_work_icols_idx1 ON aus_work_icols (aus_icols_tabid,aus_icols_colno); END IF LET tmp = "UPDATE aus_work_icols SET aus_icols_lkey = 'Y' " || "WHERE aus_icols_tabid*65536 + aus_icols_colno " || " IN ( " || " SELECT tabid*65536 + ABS(ikeyextractcolno(indexkeys,0)) " || "FROM " || TRIM(aus_dbsname)::lvarchar||":sysindices)"; EXECUTE IMMEDIATE tmp ; --TRACE "Updating lead keys for aus_work_icols," || DBINFO("sqlca.sqlerrd2")||" lead keys found"; RETURN nrows; END FUNCTION; CREATE FUNCTION informix.aus_evaluator(eval_only BOOLEAN) RETURNING BIGINT IF eval_only THEN RETURN aus_evaluator(-1, 1, 0); END IF RETURN aus_evaluator(-1, 1, 1); END FUNCTION; CREATE FUNCTION informix.aus_evaluator(task_id INTEGER, task_seq INTEGER) RETURNING BIGINT RETURN aus_evaluator( task_id, task_seq, 1); END FUNCTION; CREATE FUNCTION informix.aus_evaluator(task_id INTEGER, task_seq INTEGER,myflags INTEGER) RETURNING BIGINT DEFINE aus_dbsname CHAR(128); DEFINE aus_dbs_partnum INTEGER; DEFINE trows BIGINT; DEFINE nrows BIGINT; DEFINE has_mon_tab_prof INTEGER; DEFINE param_debug CHAR(128); DEFINE dblocale CHAR(36); DEFINE logmode CHAR(3); DEFINE errnum INTEGER; DEFINE inHDRmode INTEGER; LET nrows = 0; LET trows = 0; SELECT FIRST 1 value INTO param_debug FROM sysadmin:ph_threshold WHERE name = "AUS_DEBUG"; IF param_debug IS NOT NULL THEN --SET DEBUG FILE TO TRIM(param_debug)::lvarchar||".prc"; --TRACE PROCEDURE; --TRACE ON; ELSE --SET DEBUG FILE TO "/tmp/aus.debug.off"; --TRACE OFF; --TRACE ON; END IF LET errnum = aus_get_exclusive_access(task_id, task_seq); LET errnum = aus_cleanup_table(0); SELECT FIRST 1 DECODE(type, "Primary", 1, "Standard", 0, 0) INTO inHDRmode FROM sysmaster:sysdri; IF (inHDRmode = 1) THEN SET LOCK MODE TO WAIT 120; ELSE SET LOCK MODE TO WAIT 5; END IF LET errnum = aus_setup_table(inHDRmode); SET ISOLATION TO DIRTY READ; LET has_mon_tab_prof = aus_setup_mon_table_profile(task_id,task_seq,inHDRmode); IF has_mon_tab_prof == 1 THEN LET nrows = exectask( "mon_table_profile" ); END IF /* Loop through all the logging databases * loading up the aus_work_info table */ --TRACE "Begin examining each logging database"; FOREACH SELECT TRIM(name), partnum INTO aus_dbsname, aus_dbs_partnum FROM sysmaster:sysdbspartn WHERE partnum NOT IN (select partnum FROM sysmaster:sysdbspartn WHERE bitand(flags, 3 ) > 0 AND name NOT IN (SELECT value FROM sysadmin:ph_threshold WHERE name = "AUS_DATABASE_DISABLED" )) INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "yellow", "DATABASE", "Auto Update Statistics", "Skipping database ["||trim(aus_dbsname)|| "] for Auto Update Statistics. ", NULL); END FOREACH FOREACH SELECT TRIM(D.name), D.partnum, decode(bitand(D.flags,5),0,'N',4,'A','L') as logmode, T.collate INTO aus_dbsname, aus_dbs_partnum, logmode, dblocale FROM sysmaster:sysdbspartn D, sysmaster:systabnames T WHERE bitand(D.flags, 3 ) > 0 --- Not ansi yet need owner names AND D.name <> "sysmaster" AND D.partnum = T.partnum AND D.name NOT IN (SELECT value FROM sysadmin:ph_threshold WHERE name = "AUS_DATABASE_DISABLED" ) INSERT INTO aus_cmd_info (aus_ci_dbs_partnum, aus_ci_need_tables, aus_ci_database, aus_ci_locale, aus_ci_logmode, aus_ci_stime) VALUES (aus_dbs_partnum, 0, aus_dbsname, dblocale, logmode, aus_get_realtime()); --TRACE "Starting database loading " || RTRIM(aus_dbsname); LET nrows = aus_load_dbs_data(aus_dbsname, aus_dbs_partnum, inHDRmode); --TRACE "Starting database evaluation"; LET trows = aus_evaluator_dbs(aus_dbsname, aus_dbs_partnum, has_mon_tab_prof, inHDRmode); UPDATE aus_cmd_info SET (aus_ci_need_tables,aus_ci_etime) = (aus_ci_need_tables + trows, aus_get_realtime()) WHERE aus_ci_database = aus_dbsname; IF trows < 0 THEN EXIT FOREACH; END IF IF trows > 0 THEN INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "yellow", "DATABASE", "Auto Update Statistics", "Found "||trows||" table(s) in database ["||trim(aus_dbsname)|| "]" || " which need statistics updated. ", NULL); ELSE INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "green", "DATABASE", "Auto Update Statistics", "Found "||trows||" table(s) in database ["||trim(aus_dbsname)|| "]" || " which need statistics updated.", NULL); END IF DROP INDEX aus_work_info_idx1; DROP INDEX aus_work_info_idx2; DROP INDEX aus_work_dist_idx1; DROP INDEX aus_work_icols_idx1; TRUNCATE TABLE aus_work_info; TRUNCATE TABLE aus_work_dist; TRUNCATE TABLE aus_work_icols; END FOREACH --- End Database FOREACH UPDATE aus_cmd_info SET (aus_ci_etime) = ( aus_get_realtime()) WHERE aus_ci_database IS NULL; LET errnum = aus_cleanup_table(1); IF inHDRmode = 1 THEN CREATE INDEX informix.aus_command_ix1 ON aus_command(aus_cmd_id) ONLINE; CREATE INDEX informix.aus_command_ix2 ON aus_command( aus_cmd_state, aus_cmd_priority DESC, aus_cmd_partnum, aus_cmd_type DESC) ONLINE; ELSE CREATE INDEX informix.aus_command_ix1 ON aus_command(aus_cmd_id); CREATE INDEX informix.aus_command_ix2 ON aus_command(aus_cmd_state, aus_cmd_priority DESC, aus_cmd_partnum, aus_cmd_type DESC); END IF LET errnum = aus_rel_exclusive_access(); IF bitand(myflags,1) > 0 THEN LET errnum = aus_enable_refresh(); END IF RETURN nrows; END FUNCTION; {********************************************************* * * The following section create the task in the database * scheduler used by the AUS feature * ******************************************************** *} DELETE from ph_task WHERE tk_name = "Auto Update Statistics Evaluation"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_enable ) VALUES ( "Auto Update Statistics Evaluation", "TASK", "PERFORMANCE", "To Evaluate which columns and tables should have the statistics and distributions refreshed.", "aus_evaluate_stats", DATETIME(01:00:00) HOUR TO SECOND, DATETIME(01:10:00) HOUR TO SECOND, INTERVAL ( 1 ) DAY TO DAY, 't' ); DELETE from ph_task WHERE tk_name = "Auto Update Statistics Refresh"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_monday, tk_Tuesday, tk_Wednesday, tk_Thursday, tk_Friday, tk_sunday, tk_saturday, tk_enable ) VALUES ( "Auto Update Statistics Refresh", "TASK", "PERFORMANCE", "Refreshes the statistics and distributions which were recommended by the evaluator.", "aus_refresh_stats", DATETIME(01:11:00) HOUR TO SECOND, DATETIME(05:00:00) HOUR TO SECOND, INTERVAL ( 1 ) DAY TO DAY, 'f', 'f', 'f', 'f', 'f', 't', 't', 't' ); create function aus_evaluate_stats(task_id integer, seq_id integer) returns integer external name '(aus_evaluator)' language C; CREATE FUNCTION aus_evaluator_upgrade() RETURNING INTEGER UPDATE ph_task SET (tk_execute) = ( "aus_evaluate_stats") WHERE tk_name = 'Auto Update Statistics Evaluation'; RETURN 0; END FUNCTION; CREATE FUNCTION aus_evaluator_downgrade() RETURNING INTEGER UPDATE ph_task SET (tk_execute) = ( "aus_evaluator") WHERE tk_name = 'Auto Update Statistics Evaluation'; RETURN 0; END FUNCTION; create function informix.aus_refresh_stats(task_id integer, seq_id integer, run_time integer default 0) returns integer external name '(aus_refresh)' language C; CREATE FUNCTION informix.aus_refresh_upgrade() RETURNING INTEGER UPDATE ph_task SET ( tk_execute, tk_enable ) = ( "aus_refresh_stats", 't' ) WHERE tk_name = 'Auto Update Statistics Refresh'; UPDATE ph_task SET ( tk_frequency ) = ( INTERVAL ( 1 ) DAY TO DAY ) WHERE tk_name = 'Auto Update Statistics Refresh' AND tk_frequency < INTERVAL ( 1 ) DAY TO DAY; RETURN 0; END FUNCTION; CREATE FUNCTION informix.aus_refresh_downgrade() RETURNING INTEGER UPDATE ph_task SET ( tk_execute, tk_enable, tk_frequency ) = ( "aus_refresh_stats_orig", 'f', INTERVAL ( 1 ) SECOND TO SECOND ) WHERE tk_name = 'Auto Update Statistics Refresh'; RETURN 0; END FUNCTION; {************************************************************************** MARK THE ABOVE TASKS AS SYSTEM TASKS AND FOR PRIVATE THREAD **************************************************************************} UPDATE ph_task SET tk_attributes = BITOR(tk_attributes, 12) WHERE tk_name IN ('Auto Update Statistics Evaluation', 'Auto Update Statistics Refresh'); {************************************************************************** RECORD THE VERSION OF AUS INSTALLED **************************************************************************} DELETE FROM ph_version WHERE object = 'AUS' and type = 'version'; INSERT INTO ph_version(object,type,value) VALUES ( 'AUS','version', 23 );