IIUG World 2019

IIUG 2020 Online World Conference Presentations will begin soon!

Thursday, May 5, 2011

AUS -versus- dostats

Do you like the idea of using the task manager to take care of update statistics tasks but prefer to use dostats than AUS (Auto Update Statistics) in 11.xx?  No problem, for several releases dostats_ng.ec has supported creating an AUS-like refresh function.

For a while now I have noticed that clients that are trying to use AUS to manage their statistics on larger databases are experiencing problems with the system catalogs and some tables having data distributions that are out-of-date or insufficiently detailed.  Many are going back to using dostats with our help.

In this entry I am going to show you how to completely replace AUS with dostats.  The steps are simple:
  1. Get utils2_ak and build and install dostats (by default it is now built from the next generation dostats_ng.ec source).
    1. Get utils2_ak: This one is easy.  Go to the IIUG Software Repository (www.iiug.org/software) and download the package.
    2. Build the package: Usually easy, on Linux and any platform with GNU Make and ESQL/C configured to use GCC, just type 'make'.  The file BUILDING and notes in the makefile (Makefile.nognumake) explain what to do if you have to use UNIX make and the native cc compiler on various platforms.  But this is also easy, it is usually a 5 minute task to modify the makefile for your platform - don't forget to modify the makefile in myschema.d if you want to build myschema as well.  If you are on AIX, you will also have to get my portable at utility "ar2" and compile and use that to extract the source for myschema from it's System V/POSIX format ar archive file.  If you have trouble - email me.
    3. Install:  There is an 'install' target in the makefile, so you just have to edit the target path into which the files will be installed and "make install".
  2. Disable the AUS tasks in the task manager.
    1. Easy Peasey.  You can do this in OAT or just go into dbaccess or OAT's SQL editor and run:
      UPDATE sysadmin:ph_task set tk_enable = 'f' where tk_name matches 'Auto *';
  3. Install new tasks.
    1. There are two tasks, the refresh and the evaluator.  The refresh is VERY easy because dostats takes care of that for you.  Configuring the evaluator is a bit trickier.  Fortunately, I've scripted the whole shebang for you.  The setup_dostats.ksh script below will take care of everything. 
    2. There are three scripts below, setup_stats.ksh, evaluate_stats.ksh, and initial_stats.ksh  Just install all three scripts in your path and make two edits to customize the scripts for your environment:
      1.  In setup_stats.ksh change the start_time to the time you want the refresh task to kick off and estart_time to the time you want the next day's evaluator task to kick off.  Normally you want the evaluator to run after the end of a normal day's processing cycles but before the refresh task.  The refresh task should probably run before large end-of-day processes to optimize their runs.  The evaluator takes about two minutes to process a 2000 table database.
      2. Change the location of the environment setup script you use in all three scripts.
      •  There is a line in each file, below the legend:
      • # Environment "dot" script
      • Change that next line to execute your environment file that gives access to the instance and which must also include the bin directory containing dostats and the scripts themselves in its PATH. 
    3. All three scripts take two arguments, the name of the database you are scheduling, and the path to the directory into which you want log files for the runs to be written.   The script setup_stats.ksh installs a stored procedure in the target database that runs the evaluate_stats.ksh script at the appropriate time, creates a table in sysadmin, dostats_stmts, to hold the set of commands for the refresh to run, creates the SPL procedure that executes the refresh commands and installs it as a task to be run daily at the appropriate times, and creates an initial set of commands for the first run.
    4. As configured the evaluator script will use whatever thresholds for aging and browsing you have already set in OAT for AUS to use.  You can modify those settings in OAT or by editing the values stored in the tables in sysadmin, or you can edit the evaluate_stats.ksh script and replace --aus-thresholds with the -a -A [days] and -b -B [pct_chng] parameters.


  • The third script is just for you to use to unconditionally wipe clean your existing distributions and create a complete new set using dostats.  This will "prime the pump" for the aging threshold so that every table has the same initial starting point for its stats.  You will want to run this script once initially during a quiet time on your system and again say monthly or quarterly just to get everything on an even keel again periodically.
  •  
    ##################################################################
    # setup_stats.ksh - Install a procedure to generate updated data
    #     distributions for the named database.
    #
    # Written by: Art S. Kagel, Advanced DataTools Corp.
    #
    ##################################################################
    dbs=$1
    logdir=$2 

    export start_time='03:00:00'
    export estart_time='00:00:00'
    # Environment "dot" script
    . ~informix/ADTC/aliases.art
    #
    # Install the initial evaluator procedure and populate the dostats_stmts table with
    # an initial set of commands.  Also schedules the refresh job to run at 3h
    #
    {
        dostats -d $dbs -Q 100 -w 10 --aus-thresholds --isolation d -m --procedure dostats_refresh_$dbs --schedule --frequency d --first-time $start_time
     2>&1
    #
    # Install evaluator task to run at midnight daily
    #
    dbaccess -e $dbs - <<EOF

    create procedure evaluate_${dbs}_stats();
    define cmd char(200);
    let cmd = '/home/informix/ADTC/bin/evaluate_stats.ksh $dbs &';
    system cmd;
    end procedure;
    database sysadmin;
    delete from ph_task where tk_name = 'dostats_evaluator_task';
    INSERT INTO ph_task( tk_id,        tk_name, tk_type, tk_group, tk_description, tk_execute,
        tk_start_time, tk_stop_time, tk_frequency, tk_Sunday, tk_Monday,
        tk_Tuesday, tk_Wednesday, tk_Thursday, tk_Friday, tk_Saturday ) VALUES
    ( 0, 'dostats_evaluator_${dbs}_task', 'TASK', 'DOSTATS', 'dostats run for evaluate_${dbs}_stats',
    'EXECUTE PROCEDURE $dbs:evaluate_${dbs}_stats();', $estart_time, '10:00:05', ' 1 00:00:00', 't', 't', 't', 't', 't', 't','t' );
    EOF
    }  | tee $logdir/setup_stats.$dbs.$(date +%Y.%m.%d.%H.%M.%S).out
    echo "Report also written to: $logdir/setup_stats.$dbs.$(date +%Y.%m.%d.%H.%M.%S).out"


    ================================================================================
    #! /user/bin/ksh
    ##################################################################
    # initial_stats.ksh - unconditionally generate a full set of data
    #     distributions for the named database.
    #
    # Written by: Art S. Kagel, Advanced DataTools Corp.
    #  
    ##################################################################
    dbs=$1
    logdir=$2
    # Environment "dot" script
    . ~informix/ADTC/aliases.art
    {
        dostats -d $dbs -E -Q 100 -w 10 -n 100 --aus-thresholds --time-display --drop-distributions --isolation d -m  2>&1
    }  | tee $logdir/initial_stats.$dbs.$(date +%Y.%m.%d.%H.%M.%S).out
    echo "Report also written to: $logdir/initial_stats.$dbs.$(date +%Y.%m.%d.%H.%M.%S).out"
    ============================================================================
    #! /user/bin/ksh
    ##################################################################
    # evaluate_stats.ksh - Install a procedure to generate updated data
    #     distributions for the named database.
    #
    # Written by: Art S. Kagel, Advanced DataTools Corp.
    #
    ##################################################################
    dbs=$1
    logdir=$2
    # Environment "dot" script
    . ~informix/ADTC/aliases.art
    {
        dostats -d $dbs -Q 100 -w 10 --isolation d -m --procedure dostats_refresh_$dbs  2>&1
    }  | tee $logdir/evaluate_stats.$dbs.$(date +%Y.%m.%d.%H.%M.%S).out