95
The information provided applies to the Informix Server Version 11.70.xC3
and later.
This blog entry presents a demo script for the "SmartMart" feature that was introduced with the recent release of Informix Ultimate Warehouse Edition 11.70.FC3. The script analyses a given query workload and produces the definition for a data mart that will be able to accelerate this workload. This definition is saved in an XML text file. The "iwadb" example database (see this earlier blog entry for more info) will be used to illustrate the workflow.
When using the script with the "iwadb" example database, nothing needs to be changed in the script. During the probing phase the script will connect to the database "iwadb". Then it will run the available workload file q1.sql, that was part of the iwadb package, to collect the probing data. Next, the script will show the probing data before analysing that collected data. For the analysis phase an extra database, named "smartmart_demodb" is used. If it does not yet exist it will be created, otherwise potentially existing old data will be cleared. The analysis result will be put into this database. Finally, in the extraction phase a data mart definition XML file will be extracted from the "smartmart_demodb" database. The name of the data mart the XML file will be "smart_iwadb1" and the XML file name will be "smart_iwadb1.xml". This XML file can be used readily to deploy a new data mart, either utilizing the Example Java CLI or by importing the XML file into the ISAO Studio GUI. If a data mart with the name "smart_iwadb1" already exists, then this data mart first needs to be dropped before creating the new data mart.
The script can also be used for running other workload against a
different warehouse database. In that case the four parameters
DBNAME, WORKLOAD_SQLFILE,
MARTDATABASE and MARTNAME at the top of the
script should be changed to adequate values.
#!/bin/sh
# Script to demonstrate the use of the "Smart Mart" feature, that was
# introduced with the release of Informix Server 11.70.xC3.
#
# The following variables must be set properly. Default values are given,
# but they probably need to be adjusted to the specific environment.
# Name of the warehouse database, for which a data mart is to be created.
DBNAME=iwadb
# Name of SQL file containing the query workload.
WORKLOAD_SQLFILE=q1.sql
# Name of the database that will contain the mart definition tables.
# This must be a database with logging.
MARTDATABASE=smartmart_demodb
# Name of the data mart to be created.
MARTNAME=smart_iwadb1
# Begin execution of the script.
# Clear probing data in case probing was already done earlier.
echo "clearing probing data ..."
rm -f sqexplain.out
echo "set environment use_dwa 'probe cleanup';" \
| dbaccess ${DBNAME} - 2>&1 | egrep -v '^$'
echo "done"
echo " "
# 'empty' output of "onstat -g probe" means that no probing data exists.
echo "check probing data is cleared ..."
onstat -g probe
echo "done"
echo " "
# Update statistics low must have been done on the warehouse database for
# the query probing to work.
# We assume that it was done before. Otherwise activate the following
# 4 lines of the script by removing the '#' comment sign.
echo "update statistics ..."
echo "update statistics low;" | dbaccess ${DBNAME} - 2>&1 | egrep -v '^$'
echo "done"
echo " "
# SQL tracing can be turned on to collect the SQL query statements for
# the analysis that will be done later.
# echo "turning on SQL statement tracing ..."
# echo "execute function task('set sql tracing on', 1000, 20, \
# 'high', 'global');" | dbaccess sysadmin - 2>&1 | egrep -v '^$'
# echo "done"
# echo " "
# run the actual query workload, i.e. the SQL query statements contained
# in the WORKLOAD_SQLFILE as specified above. With the setting of
# AVOID_EXECUTE, the statements will also be listed in the sqexplain.out
# file, but they will not really be executed, therefore no results will be
# produced by this run. However, the SQL query statements will be collected
# for the analysis of the workload.
echo "run and probe queries ..."
(echo "set explain on avoid_execute; set environment use_dwa 'probe start';"; \
cat ${WORKLOAD_SQLFILE}) | dbaccess -e ${DBNAME} - 2>&1 | egrep -v '^$'
echo "done"
echo " "
# Demonstrate visualization of the probing data collected above.
echo "check probing data ..."
onstat -g probe
echo "done"
echo " "
# Once traced SQL statements are no longer needed turn the SQL statement
# tracing off.
# echo "turning off SQL statement tracing ..."
# echo "execute function task('set sql tracing off');" \
# | dbaccess sysadmin - 2>&1 | egrep -v '^$'
# echo "done"
# echo " "
# A separate database is used to keep all the information needed for
# data mart definitions. This database is named $MARTDATABASE and must
# be a logging database.
# If this database exists already, then use it. Otherwise create it.
echo "check if database ${MARTDATABASE} exists ..."
outstr=`echo "select name from sysdatabases where name = '${MARTDATABASE}';" \
| dbaccess sysmaster - 2>&1`
ret=$?
if [ ${ret} -eq 0 ]; then
martdb=`echo "${outstr}" | awk '/name/ {print $2}'`
else
echo "${outstr}" | egrep -v '^$'
martdb=''
fi
echo "done"
echo " "
if [ "x${martdb}" = "x${MARTDATABASE}" ]; then
echo "clearing mart definition data possibly left from previous runs ..."
echo "delete from iwa_marts where martname='${MARTNAME}';" \
| dbaccess ${MARTDATABASE} - 2>&1 | egrep -v '^$'
echo "done"
else
echo "creating new database for mart definitions: ${MARTDATABASE} ..."
echo "create database ${MARTDATABASE} with log;" \
| dbaccess - - 2>&1 | egrep -v '^$'
echo "done"
fi
echo " "
# The previously collected probing data will be analysed to form the
# data mart definition for the data mart $MARTNAME in the separate
# logging database $MARTDATABASE.
# If in this database there already exists a definition for the data mart
# $MARTNAME, then the new information from the probing above will be
# merged into the existing data mart definition. Practically, this means
# that tables and columns not defined in the mart before may now be added,
# so that the data mart will also serve the query workload probed above.
# If no definition exists for $MARTNAME, then a new definition will be
# created.
echo "inserting probing data to mart schema tables ..."
echo "execute procedure probe2mart('$DBNAME', '$MARTNAME');" \
| dbaccess $MARTDATABASE - 2>&1 | egrep -v '^$'
echo "done"
echo " "
# The data mart definition is extracted from the mart definition tables
# in $MARTDATABASE, and written in the proper XML syntax, so that the
# resulting file can be used to create the data mart $MARTNAME.
echo "extracting mart definition to file ${MARTNAME}.xml ..."
rm -f $MARTNAME.xml
echo "select lotofile(genmartdef(martname),'$MARTNAME.xml"'!'"','client') \
from iwa_marts where martname='$MARTNAME';" \
| dbaccess $MARTDATABASE - 2>&1 | egrep -v '^$'
echo "done"
echo " "
# End execution of the script.
exit
In the next blog entry we will look at the details of each of the steps performed in the script, to provide further understanding of the commands. This will be valuable for modifying the script to a specific environment, so that it can be enhanced, also for use in production.
