Informix OnLine Dynamic Server Handbook
Informix OnLine Dynamic Server Handbook
BTW, the corrected version was shipped on the media accompanying the NT book.
#----------------------------------------------------------------------
# Copyright: Select Software Solutions Pty Ltd
# Description: Program to create an update statistics sql file
# using the recommended update statistics strategy
# for Online Dynamic Server 7.2
# Developed by: Gavin Nour, Select Software Solutions Pty Ltd, 1996
# For assistance: call Select Software Solutions in Sydney, Australia
# Gavin Nour mobile:0419 803 113. Email nourg@selectsoftware.com.au
#
# Additional comments from Gavin:
#
# this application can take 2 input parameters:
# -d database_name
# -o pathed_output_filename
#
# Without either parameter, the application will run against the sysmaster
# database and output its results to /tmp/upd_stat.sql
#
#----------------------------------------------------------------------
DATABASE sysmaster
MAIN
DEFINE last_table,p_tabname LIKE systables.tabname,
p_colname LIKE syscolumns.colname,
p_head_or_comp_idx CHAR(1),
scratch CHAR(2048),
outfile CHAR(80),
dname char(16),
x , totargs SMALLINT
LET dname = "sysmaster"
LET outfile="/tmp/upd_stats.sql" #default output file
LET totargs=NUM_ARGS()
FOR x=1 TO totargs
IF ARG_VAL(x) = "-d" THEN #specify database name with -d
LET dname = ARG_VAL(x+1)
DATABASE dname
END IF
IF ARG_VAL(x) = "-o" THEN #specify output file with -o
LET outfile=ARG_VAL(x+1)
END IF
END FOR
CREATE TEMP TABLE temp_schema (
tabname CHAR(18),
tabid SMALLINT,
colname CHAR(18),
colno SMALLINT,
head_or_comp_idx CHAR(1)
)
CALL load_table()
DECLARE get_tables CURSOR FOR
SELECT tabname, colname, head_or_comp_idx
FROM temp_schema
ORDER BY tabname, head_or_comp_idx
START REPORT create_sql_file TO outfile
LET scratch="-- Update Statistics Report for the ", dname CLIPPED,
" database"
OUTPUT TO REPORT create_sql_file(scratch)
LET scratch="-- Created: ", TODAY USING "mm/dd/yyyy"
OUTPUT TO REPORT create_sql_file(scratch)
LET last_table = "-"
FOREACH get_tables INTO p_tabname, p_colname, p_head_or_comp_idx
IF p_tabname <> last_table THEN
LET scratch=
"UPDATE STATISTICS MEDIUM FOR TABLE ", p_tabname CLIPPED,
" DISTRIBUTIONS ONLY ;"
OUTPUT TO REPORT create_sql_file(scratch)
END IF
IF p_head_or_comp_idx ="H" THEN
LET scratch= "UPDATE STATISTICS HIGH FOR TABLE ", p_tabname CLIPPED,
" (",p_colname CLIPPED,");"
OUTPUT TO REPORT create_sql_file(scratch)
END IF
IF p_head_or_comp_idx ="C" THEN
LET scratch= "UPDATE STATISTICS LOW FOR TABLE ", p_tabname CLIPPED,
" (",p_colname CLIPPED,");"
OUTPUT TO REPORT create_sql_file(scratch)
END IF
LET last_table=p_tabname
END FOREACH
FINISH REPORT create_sql_file
END MAIN
FUNCTION load_table()
DEFINE p_tabname LIKE systables.tabname,
p_tabid LIKE systables.tabid,
x SMALLINT,
scratch CHAR(2048)
LET p_tabname = NULL
FOR x=1 TO NUM_ARGS()
LET scratch=ARG_VAL(x)
IF scratch="-table" THEN
LET p_tabname=ARG_VAL(x+1)
EXIT FOR
END IF
END FOR
IF p_tabname IS NOT NULL THEN
LET scratch="SELECT tabname, tabid FROM systables WHERE tabname='",
p_tabname CLIPPED,"'"
ELSE
LET scratch="SELECT tabname, tabid FROM systables WHERE tabid > 99"
END IF
PREPARE get_tab_prep FROM scratch
DECLARE get_tab CURSOR FOR get_tab_prep
FOREACH get_tab INTO p_tabname, p_tabid
CALL get_col( p_tabname, p_tabid )
CALL get_idx(p_tabid )
END FOREACH
END FUNCTION
FUNCTION get_col( p_tabname ,p_tabid )
DEFINE p_tabname LIKE systables.tabname,
p_tabid LIKE systables.tabid,
p_colname LIKE syscolumns.colname,
p_colno LIKE syscolumns.colno,
scratch CHAR(2048)
LET scratch = "SELECT colname ,colno FROM syscolumns WHERE tabid=",p_tabid
PREPARE get_col_prep FROM scratch
DECLARE get_col CURSOR FOR get_col_prep
FOREACH get_col INTO p_colname, p_colno
INSERT INTO temp_schema
( tabname, tabid, colname, colno , head_or_comp_idx )
VALUES
( p_tabname, p_tabid, p_colname, p_colno, NULL )
END FOREACH
END FUNCTION
FUNCTION get_idx(p_tabid)
DEFINE p_tabid LIKE systables.tabid,
p_colno LIKE syscolumns.colno,
p_sysindexes RECORD LIKE sysindexes.* ,
scratch CHAR(2048),
x SMALLINT
LET scratch="SELECT * FROM sysindexes WHERE tabid=",p_tabid
PREPARE get_idx_prep FROM scratch
DECLARE get_idx CURSOR FOR get_idx_prep
FOREACH get_idx INTO p_sysindexes.*
#-------------------------------------------------------------------
# Mark any column which heads an index
#-------------------------------------------------------------------
IF p_sysindexes.part1 IS NOT NULL THEN
UPDATE temp_schema
SET head_or_comp_idx="H"
WHERE tabid=p_sysindexes.tabid
AND colno=p_sysindexes.part1
END IF
#-------------------------------------------------------------------
# Mark any column which is in a composite index but is not the
# leading column
#-------------------------------------------------------------------
FOR x=2 TO 16 #up to 16 columns in an index
LET p_colno = get_part(x,p_sysindexes.*)
IF p_colno=0 THEN #All columns have been dealt with
EXIT FOR
END IF
LET scratch=
"UPDATE temp_schema ",
" SET head_or_comp_idx='C'",
" WHERE tabid=",p_sysindexes.tabid,
" AND head_or_comp_idx IS NULL", #do not overwrite a "H"
" AND colno=",p_colno USING "<<<<"
PREPARE upd_schema_prep FROM scratch
EXECUTE upd_schema_prep
END FOR
#-------------------------------------------------------------------
END FOREACH
END FUNCTION
FUNCTION get_part(x,p_sysindexes)
DEFINE p_sysindexes RECORD LIKE sysindexes.*,
x SMALLINT
CASE x
WHEN 2
RETURN p_sysindexes.part2
WHEN 3
RETURN p_sysindexes.part3
WHEN 4
RETURN p_sysindexes.part4
WHEN 5
RETURN p_sysindexes.part5
WHEN 6
RETURN p_sysindexes.part6
WHEN 7
RETURN p_sysindexes.part7
WHEN 8
RETURN p_sysindexes.part8
WHEN 9
RETURN p_sysindexes.part9
WHEN 10
RETURN p_sysindexes.part10
WHEN 11
RETURN p_sysindexes.part11
WHEN 12
RETURN p_sysindexes.part12
WHEN 13
RETURN p_sysindexes.part13
WHEN 14
RETURN p_sysindexes.part14
WHEN 15
RETURN p_sysindexes.part15
WHEN 16
RETURN p_sysindexes.part16
END CASE
END FUNCTION
REPORT create_sql_file ( scratch )
DEFINE scratch CHAR(2048)
OUTPUT
LEFT MARGIN 0
RIGHT MARGIN 0
BOTTOM MARGIN 0
TOP MARGIN 0
PAGE LENGTH 1
FORMAT
ON EVERY ROW
PRINT scratch CLIPPED
END REPORT