# This is a shell archive. Remove anything before this line, # then unpack it by saving it in a file and typing "sh file". # # Wrapped by jack parker on Thu Dec 21 10:00:04 1995 # # This archive contains: # READ.ME analyse.4gl dialg_lib.4gl idx_form.per # selector.per idxhelp.txt makefile idx_scansrc # LANG=""; export LANG PATH=/bin:/usr/bin:$PATH; export PATH echo x - READ.ME cat >READ.ME <<'@EOF' analyse_idx is an interactive program to give you some information on your indices. It looks at your system catalogues and optionally your source code and reports index usage as it deems them important. As a utility it is a crowbar not a scalpel - by which I mean its purpose is to give you a starting point in looking for poor indices. The following files are enclosed: READ.ME - this file analyse.4gl - the program itself dialg_lib.4gl - Alan Popiels window functions (once again) idx_form.per - the main form used by the program selector.per - a form used in selecting table names makefile - like it sounds idxhelp.txt - an honest to goodness help file. This should be read either in its current form or using the 'About' option off of the main menu. idx_scansrc - a shell/awk script to scan your source files for index references. This script generates a number of temporary files WHICH IT DOES NOT CLEAN UP. This was intentional (as you can tell by the CAPS). These files are: /tmp/idx_files - list of source files checked. /tmp/idx_scan1 - references found. /tmp/idx_scan2 - pass one at cleaning up. /tmp/idx_scan3 - pass two at cleaning up. /tmp/idx_scan.unl - unload file of indexes found. I have found scan1 to be particularly useful for locating specific programs to see how the index was used. The idx_scan.unl file is also useful - once generated you can always reload the data. Notes: Sun users - replace awk with nawk. also replace grep with gnugrep so that the -e switch will work. @EOF chmod 664 READ.ME echo x - analyse.4gl cat >analyse.4gl <<'@EOF' ######################################################################## # # analyse.4gl: A form based utility to check indices for usefulness # # Jparker 6/3/94 # ######################################################################## GLOBALS DEFINE lastline SMALLINT DEFINE datatype ARRAY[40] OF CHAR(20), # for coltype conversions datetype ARRAY[16] OF CHAR(11) # for coltype conversions DEFINE SIZE_THRESH INTEGER, # point at which NOT to try selects PAGESIZE SMALLINT # Machine dependent - set in hskpng DEFINE disp_rows SMALLINT # number of rows on screen (undefined yet) DEFINE DBASE CHAR(64) DEFINE col_data ARRAY[100] OF RECORD colname CHAR(18), desc CHAR(20), occ_cnt SMALLINT, idxtype CHAR(1), clustered CHAR(1), uniq_cnt DECIMAL, joins SMALLINT END RECORD, idx_data ARRAY[100] OF RECORD idxname CHAR(18), idxtype CHAR(1), clustered CHAR(1), desc CHAR(300) END RECORD, max_idx, max_cols SMALLINT DEFINE ONLINE_SW SMALLINT, max_parts SMALLINT END GLOBALS ######################################################################## # Main ######################################################################## MAIN DEFINE tabname CHAR(18), sel_strg CHAR(550) #DEFER INTERRUPT OPTIONS NEXT KEY CONTROL-N, PREVIOUS KEY CONTROL-P, ACCEPT KEY CONTROL-M, HELP FILE "idxhelp.msg" CALL hskpng() # I hate having to define my cursors PHYSICALLY # before I use them, so untidy. LET sel_strg = "SELECT idx_src.colname, occ_cnt, uniq_vals, collength, coltype, idxtype, ", " clustered ", " FROM ""informix"".systables, idx_src, ""informix"".syscolumns, ", " outer ""informix"".sysindexes", " WHERE ""informix"".systables.tabname = idx_src.tabname", " AND ""informix"".systables.tabid = ""informix"".syscolumns.tabid", " AND ""informix"".syscolumns.colname = idx_src.colname", " AND ""informix"".systables.tabid=""informix"".sysindexes.tabid", " AND ""informix"".syscolumns.colno=""informix"".sysindexes.part1", " AND ""informix"".systables.tabname = ?", " ORDER BY occ_cnt DESC, uniq_vals DESC" PREPARE s3 FROM sel_strg DECLARE tab_curs CURSOR FOR s3 IF ONLINE_SW THEN LET sel_strg = "SELECT idxname, idxtype, clustered, part1, part2, part3, part4, ", " part5, part6, part7, part8, part9, part10, part11, part12, ", " part13, part14, part15, part16 " ELSE LET sel_strg = "SELECT idxname, idxtype, clustered, part1, part2, part3, part4, ", " part5, part6, part7, part8 " END IF LET sel_strg = sel_strg CLIPPED, " FROM ""informix"".sysindexes, ""informix"".systables ", " WHERE ""informix"".sysindexes.tabid=""informix"".systables.tabid ", " AND ""informix"".systables.tabname = ?" PREPARE s4 FROM sel_strg DECLARE idx_curs CURSOR FOR s4 OPEN WINDOW w_idx AT 2,2 WITH FORM "idx_form" ATTRIBUTE(BORDER) MESSAGE "Press CTRL-W for help." MENU "Main" COMMAND "About" "About this program (help)" HELP 100 CALL showhelp(100) COMMAND "Source" "Scan source for index usage and load into analysis table." HELP 101 CALL scan_src() COMMAND "Catalogues" "Load system catalogues into analysis table." HELP 102 CALL load_cat() CALL disp_status("") COMMAND "Table" "Check specific table." HELP 103 CALL selector("systables", "tabname") RETURNING tabname CALL chk_tab(tabname, FALSE) COMMAND KEY (U) "colUmns" "Scroll through the column list" HELP 109 CALL set_count(max_cols) DISPLAY ARRAY col_data TO s_coldata.* COMMAND "Index" "Scroll through the index list" HELP 109 CALL set_count(max_idx) DISPLAY ARRAY idx_data TO s_idxdata.* COMMAND "Options" "Set operating parameters and thresholds." HELP 105 CALL opt_menu() COMMAND "Report" "Print analysis of indices to $LPDEST." HELP 106 CALL do_output() COMMAND KEY (L) "cLeanup" "Drop the analysis table, and exit." HELP 107 CALL cleanup() COMMAND "Exit" "Outta here." HELP 108 EXIT MENU END MENU CLOSE WINDOW w_idx END MAIN ######################################################################## # # Scan source code for index references. Load into analysis table. # ######################################################################## FUNCTION scan_src() DEFINE os_cmd CHAR(40), fact1, fact2, fact3, mean_cnt, junk, curr_cnt SMALLINT, curr_tab, curr_col CHAR(18), K_fact DECIMAL, errcd INTEGER, msg CHAR(70) LET os_cmd = "idx_scansrc" RUN os_cmd RETURNING errcd LET errcd = errcd / 256 IF errcd != 0 THEN LET msg = "Error ", errcd, " running idx_scansrc - check $PATH" ERROR msg SLEEP 2 RETURN # out of here END IF CALL disp_status("Loading indices found in source") SELECT COUNT(*) INTO junk FROM "informix".systables WHERE tabname = "idx_src" IF junk=0 THEN CALL disp_status ("Creating analysis table - idx_src") CREATE TABLE idx_src (tabname char(18), colname char(18), occ_cnt smallint, uniq_vals decimal) ELSE CALL alert("Load Alert", "Deleting old data from analysis table") DELETE FROM idx_src END IF LOAD FROM "/tmp/idx_scan.unl" INSERT INTO idx_src LET os_cmd = SQLCA.SQLERRD[3] USING "###### Raw rows loaded" CALL disp_status(os_cmd) CALL disp_status ("Matching src against syscolumns") DELETE FROM idx_src WHERE colname NOT IN (SELECT unique colname FROM "informix".syscolumns WHERE tabid > 99) OR idx_src.colname IS NULL CALL disp_status("Matching src against systables") DELETE FROM idx_src WHERE tabname IS NOT NULL AND tabname NOT IN (SELECT tabname FROM "informix".systables WHERE tabid > 99) CALL disp_status("Final check...") DELETE FROM idx_src WHERE colname NOT IN (SELECT colname FROM "informix".syscolumns) SELECT COUNT(*) INTO curr_cnt FROM idx_src LET os_cmd=curr_cnt USING "#### Index references found in source" CALL disp_status(os_cmd) # All rows left in idx_src are valid indices. Problem is that some don't # have table names. Some are also duplicated where they do AND dont have # table names. DECLARE col_curs CURSOR FOR SELECT colname, occ_cnt FROM idx_src WHERE tabname IS NULL CALL disp_status ("Pro-rating columns where tabname is null") PREPARE t1 FROM "SELECT tabname FROM systables, syscolumns WHERE systables.tabid=syscolumns.tabid and colname = ?" DECLARE t_curs CURSOR FOR t1 FOREACH col_curs INTO curr_col, curr_cnt # all tables that aren't mentioned should get some value use a mean CALL calc_mean(curr_col) RETURNING mean_cnt # the rest get a ratio depending on what they have and what remains SELECT count(*), sum(occ_cnt) INTO fact1, fact2 FROM idx_src WHERE tabname IS NOT NULL AND colname = curr_col SELECT count(*) INTO fact3 FROM systables, syscolumns WHERE systables.tabid=syscolumns.tabid AND colname = curr_col AND NOT EXISTS (SELECT * FROM idx_src WHERE idx_src.colname = curr_col AND idx_src.tabname = systables.tabname); # Put a limit on mean_cnt IF (fact3*mean_cnt) > (curr_cnt/2) THEN LET mean_cnt = curr_cnt / (fact1 + fact3) END IF LET K_fact = (curr_cnt-(fact3*mean_cnt))/fact2 OPEN t_curs USING curr_col FOREACH t_curs INTO curr_tab SELECT count(*) INTO junk FROM idx_src WHERE tabname = curr_tab AND colname = curr_col IF junk > 0 THEN UPDATE idx_src SET occ_cnt = occ_cnt + (occ_cnt * K_fact) WHERE tabname = curr_tab AND colname = curr_col ELSE INSERT INTO idx_src (tabname, colname, occ_cnt) VALUES (curr_tab, curr_col, mean_cnt) END IF END FOREACH CLOSE t_curs END FOREACH FREE t1 DELETE FROM idx_src WHERE tabname IS NULL CALL disp_status ("Source data has been loaded") # now we are left with 'good' data. We have a table, a column, and an # occurence count - which should be somewhat close to reality END FUNCTION ######################################################################## # # Generic field selector from a table. Max 1000 entries displayed. # ######################################################################## FUNCTION selector(tabname, colname) DEFINE selnames ARRAY[1000] OF CHAR(18), sel_idx SMALLINT, tabname, colname CHAR(18), sel_strg CHAR(200) LET sel_strg = "SELECT ", colname clipped, " FROM ""informix"".", tabname clipped, " WHERE tabid > 99 ", " ORDER BY ", colname clipped PREPARE s2 FROM sel_strg DECLARE s1_curs CURSOR FOR s2 FOR sel_idx = 1 TO 1000 INITIALIZE selnames[sel_idx] TO NULL END FOR LET sel_idx=1 FOREACH s1_curs INTO selnames[sel_idx] LET sel_idx = sel_idx + 1 IF sel_idx > 1000 THEN ERROR "Over 1000 rows" END IF END FOREACH FREE s2 CALL disp_status("Press [RETURN] to select") OPEN WINDOW w_sel AT 5,5 WITH FORM "selector" ATTRIBUTE(BORDER) CALL set_count(sel_idx-1) DISPLAY colname TO s_title DISPLAY ARRAY selnames TO s_selector.* ON KEY (INTERRUPT) LET colname = "" CALL disp_status("Aborted") LET int_flag = FALSE ON KEY (CONTROL-M) LET sel_idx = arr_curr() LET colname = selnames[sel_idx] EXIT DISPLAY END DISPLAY CLOSE WINDOW w_sel CALL disp_status ("") RETURN colname END FUNCTION ######################################################################## # # Load table info and display. # ######################################################################## FUNCTION chk_tab(p_tabname, REPORTING) DEFINE p_colname, p_tabname CHAR(18), p_nrows INTEGER, p_rowsize SMALLINT, p_pages INTEGER, i, idx SMALLINT, parts ARRAY[16] OF SMALLINT, p_collength SMALLINT, coltype SMALLINT, sql_strg CHAR(120), msg CHAR(40), junk SMALLINT, rpt_sw SMALLINT, REPORTING SMALLINT SELECT nrows, rowsize INTO p_nrows, p_rowsize FROM "informix".systables WHERE tabname = p_tabname IF p_rowsize > 0 THEN LET junk = (PAGESIZE / p_rowsize) # how many rows fit on a page? LET p_pages = (p_nrows / junk) END IF FOR idx = 1 TO 100 INITIALIZE col_data[idx].* TO NULL INITIALIZE idx_data[idx].* TO NULL END FOR LET idx = 1 OPEN tab_curs USING p_tabname FOREACH tab_curs INTO col_data[idx].colname, col_data[idx].occ_cnt, col_data[idx].uniq_cnt, p_collength, coltype, col_data[idx].idxtype, col_data[idx].clustered LET col_data[idx].desc=col_cnvrt(coltype, p_collength) # how many unique values / nrows in this table? # warning - if table big DON'T DO IT. # hence check threshold IF p_pages < SIZE_THRESH AND col_data[idx].uniq_cnt = 0 AND coltype != 12 AND coltype != 13 THEN LET msg = p_pages USING "Reading unique values for ####### pages" CALL disp_status(msg) # Method 1 let sql_strg= "SELECT UNIQUE ", col_data[idx].colname clipped, " FROM ", p_tabname clipped, " INTO TEMP t1 WITH NO LOG" PREPARE t2 FROM sql_strg EXECUTE t2 IF p_nrows > 0 THEN LET col_data[idx].uniq_cnt = SQLCA.SQLERRD[3]*100/p_nrows END IF DROP TABLE t1 # Method 2 # This is a better method theoretically - doesn't do as much i/o. In fact it # takes the same amount of time or more. I suspect that there is a bunch of # temp table i/o that's not readily visible. # LET sql_strg = "SELECT COUNT (DISTINCT ", # col_data[idx].colname clipped, # ") FROM ", # p_tabname clipped # # PREPARE t2 FROM sql_strg # DECLARE cnt_curs2 CURSOR FOR t2 # # OPEN cnt_curs2 # FETCH cnt_curs2 INTO col_data[idx].uniq_cnt # LET col_data[idx].uniq_cnt = col_data[idx].uniq_cnt*100/p_nrows # CLOSE cnt_curs2 # save it off. UPDATE idx_src SET uniq_vals = col_data[idx].uniq_cnt WHERE tabname = p_tabname and colname = col_data[idx].colname END IF SELECT COUNT(*)-1 INTO col_data[idx].joins FROM "informix".syscolumns WHERE colname = col_data[idx].colname AND collength = p_collength LET idx = idx + 1 IF idx> 100 THEN ERROR "Over 100 rows" EXIT FOREACH END IF END FOREACH LET max_cols = idx-1 LET idx = 1 OPEN idx_curs USING p_tabname FOREACH idx_curs INTO idx_data[idx].idxname, idx_data[idx].idxtype, idx_data[idx].clustered, parts[1], parts[2], parts[3], parts[4], parts[5], parts[6], parts[7], parts[8], parts[9], parts[10], parts[11], parts[12], parts[13], parts[14], parts[15], parts[16] FOR i = 1 TO max_parts IF parts[i] = 0 THEN EXIT FOR END IF SELECT colname INTO p_colname FROM syscolumns, systables WHERE "informix".systables.tabid = "informix".syscolumns.tabid AND "informix".systables.tabname = p_tabname AND colno = parts[i] LET idx_data[idx].desc=idx_data[idx].desc clipped, " ", p_colname END FOR LET idx=idx+1 END FOREACH LET max_idx = idx - 1 CLOSE idx_curs DISPLAY p_nrows, p_rowsize, p_tabname, p_pages TO s_nrows, s_rowsize, s_tabname, s_page IF NOT REPORTING THEN FOR i = 1 TO disp_rows DISPLAY col_data[i].* TO s_coldata[i].* END FOR FOR i = 1 TO disp_rows-1 DISPLAY idx_data[i].* TO s_idxdata[i].* END FOR ELSE FOR i = 1 TO max_cols OUTPUT TO REPORT idx_rpt(p_tabname, p_rowsize, p_nrows, p_pages, col_data[i].*, idx_data[1].*, 1) END FOR LET rpt_sw = 2 FOR i = 1 TO max_idx OUTPUT TO REPORT idx_rpt(p_tabname, p_nrows, p_rowsize, p_pages, col_data[1].*, idx_data[i].*, rpt_sw) LET rpt_sw = 3 END FOR END IF CALL disp_status ("") END FUNCTION ######################################################################## # # Allow user to enter thresholds. # ######################################################################## FUNCTION opt_menu() DEFINE msg CHAR(40), errcode, junk SMALLINT MENU "Options" COMMAND "Size" "Specify table size at which NOT to automatically select do reads" HELP 120 OPTIONS ACCEPT KEY ESCAPE LET msg = SIZE_THRESH USING "Current value #####" PROMPT "Enter value in Pages " FOR SIZE_THRESH OPTIONS ACCEPT KEY CONTROL-M COMMAND "Database" "Specify Database" HELP 121 OPTIONS ACCEPT KEY ESCAPE LET msg = "Current value ", DBASE clipped PROMPT "Enter new database " FOR DBASE OPTIONS ACCEPT KEY CONTROL-M CALL db_check(DBASE) RETURNING errcode IF errcode = 1 THEN LET msg = "Database problem ", status ERROR msg SLEEP 5 EXIT PROGRAM -1 END IF SELECT COUNT(*) INTO junk FROM "informix".systables WHERE tabname = "idx_src" IF junk=0 THEN CALL disp_status ("Creating analysis table - idx_src") CREATE TABLE idx_src (tabname char(18), colname char(18), occ_cnt smallint, uniq_vals decimal) END IF COMMAND "Exit" "Return to previous menu" HELP 111 EXIT MENU END MENU END FUNCTION ######################################################################## # # Load system table and column names into idx_src. Don't duplicate # entries that are there from scan_src() # ######################################################################## FUNCTION load_cat() DEFINE msg CHAR(40), junk SMALLINT SELECT COUNT(*) INTO junk FROM "informix".systables WHERE tabname = "idx_src" IF junk=0 THEN CALL disp_status ("Creating analysis table - idx_src") CREATE TABLE idx_src (tabname char(18), colname char(18), occ_cnt smallint, uniq_vals decimal) END IF CALL disp_status("Loading catalog into analysis tables.") SELECT tabname, colname, 0 occ_cnt, 0 uniq_vals FROM "informix".systables, "informix".syscolumns WHERE "informix".systables.tabid = "informix".syscolumns.tabid AND NOT EXISTS (SELECT idx_src.tabname, idx_src.colname FROM idx_src WHERE idx_src.tabname="informix".systables.tabname AND "informix".systables.tabid = "informix".syscolumns.tabid AND idx_src.colname = "informix".syscolumns.colname) INTO temp t1 WITH NO LOG INSERT INTO idx_src SELECT * FROM t1 LET msg = SQLCA.SQLERRD[3] USING "#### - rows loaded" DROP table t1 CALL disp_status(msg) END FUNCTION ######################################################################## # # generate a report, accept table spec and call chk_tab with a REPORT switch # ######################################################################## FUNCTION do_output() DEFINE whereinfo CHAR(80), sql_strg CHAR(200), p_tabname CHAR(18), done, todo SMALLINT MESSAGE "Enter Table search criteria" CONSTRUCT whereinfo ON tabname FROM formonly.s_tabname LET sql_strg = "SELECT COUNT(*) FROM ""informix"".systables WHERE ", whereinfo CLIPPED, " AND tabid > 99" PREPARE s5 FROM sql_strg DECLARE cnt_curs CURSOR FOR s5 OPEN cnt_curs FETCH cnt_curs INTO todo CLOSE cnt_curs FREE s5 LET sql_strg = "SELECT tabname FROM ""informix"".systables WHERE ", whereinfo CLIPPED, " AND tabid > 99 ORDER BY tabname" PREPARE s6 FROM sql_strg DECLARE rpt_curs CURSOR FOR s6 START REPORT idx_rpt TO "idx.output" LET done=0 FOREACH rpt_curs INTO p_tabname CALL chk_tab(p_tabname, TRUE) LET done=done+1 CALL disp_prog(done, todo) END FOREACH FINISH REPORT idx_rpt FREE s6 END FUNCTION ########################################################################### # misc housekeeping - init stuff. ########################################################################### FUNCTION hskpng() DEFINE errcode SMALLINT, junk SMALLINT LET datatype[1] = "CHAR" LET datatype[2] = "SMALLINT" LET datatype[3] = "INTEGER" LET datatype[4] = "FLOAT" LET datatype[5] = "SMALLFLOAT" LET datatype[6] = "DECIMAL" LET datatype[7] = "SERIAL" LET datatype[8] = "DATE" LET datatype[9] = "MONEY" LET datatype[10] = "UNKNOWN" LET datatype[11] = "DATETIME" LET datatype[12] = "BYTE" LET datatype[13] = "TEXT" LET datatype[14] = "VARCHAR" LET datatype[15] = "INTERVAL" LET datatype[16] = "UNKNOWN" # little room for growth LET datatype[17] = "UNKNOWN" LET datatype[18] = "UNKNOWN" LET datatype[19] = "UNKNOWN" LET datatype[20] = "UNKNOWN" LET datetype[1] = "YEAR" LET datetype[3] = "MONTH" LET datetype[5] = "DAY" LET datetype[7] = "HOUR" LET datetype[9] = "MINUTE" LET datetype[11] = "SECOND" LET datetype[12] = "FRACTION(1)" LET datetype[13] = "FRACTION(2)" LET datetype[14] = "FRACTION(3)" LET datetype[15] = "FRACTION(4)" LET datetype[16] = "FRACTION(5)" LET lastline = 21 LET disp_rows = 5 LET SIZE_THRESH=500 LET PAGESIZE=2048 LET ONLINE_SW = 1 LET max_parts=16 LET DBASE = arg_val(1) WHILE LENGTH(DBASE) = 0 ERROR "Please select a database" SLEEP 1 CALL opt_menu() END WHILE CALL db_check(DBASE) RETURNING errcode IF errcode = 1 THEN EXIT PROGRAM -1 END IF SELECT COUNT(*) INTO junk FROM "informix".systables WHERE tabname = "idx_src" IF junk=0 THEN CALL disp_status ("Creating analysis table - idx_src") CREATE TABLE idx_src (tabname char(18), colname char(18), occ_cnt smallint, uniq_vals decimal) END IF CALL disp_status("") END FUNCTION ######################################################################## # # Undo anything we've done. # ######################################################################## FUNCTION cleanup() CALL disp_status("Dropping analysis table") DROP TABLE idx_src EXIT PROGRAM END FUNCTION ######################################################################## # # Dislay a status message # ######################################################################## FUNCTION disp_status(pgm_status) DEFINE pgm_status CHAR(40) DISPLAY pgm_status AT lastline,37 ATTRIBUTE(REVERSE) END FUNCTION ######################################################################## # # Display a bar of how far done. (Just like Windoze!) # ######################################################################## FUNCTION disp_prog(done, todo) DEFINE done, todo INTEGER, prcnt DECIMAL, st_ln CHAR(78), dsp_pcnt CHAR(5) LET st_ln = 78 SPACES LET prcnt=done/todo*100 LET dsp_pcnt = prcnt USING "##" LET prcnt=prcnt*78/100 LET st_ln = prcnt SPACES, ">" LET st_ln[39,40]=dsp_pcnt LET st_ln[41,41]="%" DISPLAY st_ln clipped AT lastline, 1 ATTRIBUTE(REVERSE) END FUNCTION ########################################################################### # Convert coltype/length into an SQL descriptor string ########################################################################### FUNCTION col_cnvrt(coltype, collength) DEFINE coltype, collength, NONULL SMALLINT, SQL_strg CHAR(40), tmp_strg CHAR(4) LET coltype = coltype + 1 # datatype[] is offset by one LET NONULL = coltype/256 # if > 256 then is NO NULLS LET coltype = coltype MOD 256 # lose the NO NULLS determinator LET SQL_strg = datatype[coltype] CASE coltype WHEN 1 # char LET tmp_strg = collength using "<<<<" LET SQL_strg = SQL_strg clipped, " (", tmp_strg clipped, ")" # SQL syntax supports float(n) - Informix ignores this # WHEN 4 # float # LET SQL_strg = SQL_strg clipped, " (", ")" WHEN 6 # decimal LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength) clipped, ")" # Syntax supports serial(starting_no) - starting_no is unavaliable # WHEN 7 # serial # LET SQL_strg = SQL_strg clipped, " (", ")" WHEN 9 # money LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength) clipped, ")" WHEN 11 # datetime LET SQL_strg = SQL_strg clipped, " (", fix_dt(collength) clipped, ")" WHEN 14 # varchar LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength) clipped, ")" WHEN 15 # interval LET SQL_strg = SQL_strg clipped, " (", fix_dt(collength) clipped, ")" END CASE IF NONULL THEN LET SQL_strg = SQL_strg clipped, " NOT NULL" END IF RETURN SQL_strg END FUNCTION ########################################################################### # Turn collength into two numbers - return as string ########################################################################### FUNCTION fix_nm(num) DEFINE num integer, strg CHAR(8), i, j SMALLINT, strg1, strg2 char(3) LET i = num / 256 LET j = num MOD 256 LET strg1 = i using "<<<" LET strg2 = j using "<<<" LET strg = strg1 clipped, ", ", strg2 clipped RETURN strg END FUNCTION ########################################################################### # Turn collength into meaningful date info - return as string ########################################################################### FUNCTION fix_dt(num) DEFINE num integer, i, j SMALLINT, strg CHAR(20) LET i = ((num mod 16) mod 12) + 1 # offset again LET j = ((num / 16) mod 12) + 1 # offset again LET strg = datetype[j] clipped, " TO ", datetype[i] clipped RETURN strg END FUNCTION ####################################################################### # ensure we can open database ####################################################################### FUNCTION db_check(dbname) DEFINE dbname CHAR(64), msg CHAR(40), err SMALLINT LET err = 0 WHENEVER ERROR CONTINUE DATABASE dbname IF status != 0 THEN LET msg = "Can't open:", dbname clipped, "-", status ERROR msg SLEEP 1 LET err = 1 ELSE SET LOCK MODE TO WAIT 30 IF STATUS THEN LET ONLINE_SW = 0 LET max_parts = 8 END IF END IF RETURN err END FUNCTION ################################################################### # Report section. ################################################################### REPORT idx_rpt(rpt_rec, rpt_rec2, rpt_rec3, rpt_switch) DEFINE rpt_rec RECORD tabname char(18), rowsize smallint, nrows integer, pages SMALLINT END RECORD, rpt_rec2 RECORD colname CHAR(18), desc CHAR(20), occ_cnt SMALLINT, idxtype CHAR(1), clustered CHAR(1), uniq_cnt DECIMAL, joins SMALLINT END RECORD, rpt_rec3 RECORD idxname char(18), idxtype char(1), clustered char(1), strg char(300) END RECORD, rpt_switch smallint OUTPUT TOP MARGIN 0 BOTTOM MARGIN 0 LEFT MARGIN 0 FORMAT BEFORE GROUP OF rpt_rec.tabname SKIP 1 LINES NEED 10 LINES PRINT "Table : ", rpt_rec.tabname clipped PRINT "Rows : ", rpt_rec.nrows, column 40, "Row Size : ", rpt_rec.rowsize IF rpt_rec.nrows > 0 THEN PRINT "Pages : ", rpt_rec.pages using "###,###,###" END IF SKIP 1 LINE PRINT " Src Idx % Uniq Poss. Suit Data" PRINT "Column Hit Typ Cl Vals Joins ablty Type" PRINT "___________________________________________________________________" SKIP 1 LINES ON EVERY ROW CASE rpt_switch WHEN 1 PRINT rpt_rec2.colname clipped, column 20, rpt_rec2.occ_cnt using "###", column 25, rpt_rec2.idxtype, column 30, rpt_rec2.clustered, column 33, rpt_rec2.uniq_cnt using "###.##", column 42, rpt_rec2.joins using "###"; IF rpt_rec2.occ_cnt > 0 THEN PRINT column 46, (rpt_rec2.occ_cnt * rpt_rec2.uniq_cnt * rpt_rec.pages * rpt_rec2.joins/100) MOD 9999.99 USING "####.##"; ELSE PRINT column 46, (rpt_rec2.uniq_cnt * rpt_rec.pages * rpt_rec2.joins/100) MOD 9999.99 USING "####.##"; END IF PRINT column 55, rpt_rec2.desc clipped WHEN 2 SKIP 1 LINE PRINT rpt_rec3.idxname clipped, column 20, "Type: ", rpt_rec3.idxtype, column 30, "Clust: ", rpt_rec3.clustered PRINT rpt_rec3.strg WORDWRAP RIGHT MARGIN 75 WHEN 3 SKIP 1 LINE PRINT rpt_rec3.idxname clipped, column 20, "Type: ", rpt_rec3.idxtype, column 30, "Clust: ", rpt_rec3.clustered PRINT rpt_rec3.strg WORDWRAP RIGHT MARGIN 75 END CASE AFTER GROUP OF rpt_rec.tabname SKIP 1 LINE END REPORT ############################################################################ # calculate a geometric mean. ############################################################################ FUNCTION calc_mean(p_colname) DEFINE p_colname CHAR(18), non_zero_val, i SMALLINT, db_i FLOAT(16) SELECT COUNT(*) INTO non_zero_val FROM idx_src WHERE colname = p_colname AND occ_cnt > 0 DECLARE m_curs CURSOR FOR SELECT occ_cnt FROM idx_src WHERE colname = p_colname AND occ_cnt > 0 LET db_i = 1 FOREACH m_curs INTO i LET db_i = db_i*i END FOREACH # we now have db_i which is a product of all non-zero values, and # non_zero_val which is the number of non-zero_values FOR i = 1 TO 9999 IF i**non_zero_val > db_i THEN EXIT FOR END IF END FOR LET i = i - 1 RETURN i END FUNCTION @EOF chmod 644 analyse.4gl echo x - dialg_lib.4gl cat >dialg_lib.4gl <<'@EOF' ############################################################################### # # Dialogue box library courtesy of Alan Popiel - Denver Co. # ############################################################################### GLOBALS DEFINE atcol SMALLINT, { column position of left edge of box } ident char(80), atrow SMALLINT, { row position of top edge of box } ncols SMALLINT, { computed number of columns in box } nrows SMALLINT, { computed number of rows in box } nlines SMALLINT, { number of lines of msg_text } textline ARRAY[10] OF CHAR(74) { separated lines of msg_text } END GLOBALS { module util_box.4gl - Dialog box utility functions author: R. Alan Popiel, President, Popiel Computing version: 2.10 date: 04 Sep 1992 NOTE: This software is hereby placed in the public domain. Popiel Computing retains no rights or responsibility to this software. *** USE OF THIS SOFTWARE IS ENTIRELY AT YOUR OWN RISK. *** While Popiel Computing has made reasonable efforts to ensure that these functions operate correctly, we make no claims as their merchantability or fitness for any particular purpose. purpose: This module contains utility functions for displaying dialog boxes, etc., on the screen. All functions in this module display a dialog box similar to this on the computer screen: +--------------------------+ upper left corner at 10,nn* or 'rw','cl' | Centered 'title' | title and blank line omitted, if title = "" | | | 'msg_text', line 1 | *nn will be computed to approximately | 'msg_text', line 2, etc. | center the box horizontally in 80 cols. | | | 'ask_for' prompt string | 'alert' does not use 'ask_for' +--------------------------+ functions included: FUNCTION alert - no 'ask_for' or value, 3 second delay, auto close FUNCTION alert_at - same as above, with positioning FUNCTION button - generalized button box handler FUNCTION button_at - same as above, with positioning FUNCTION dialog - generalized dialog box handler, no validation on return value FUNCTION dialog_at - same as above, with positioning FUNCTION notify - 'ask_for' = "Press any key.", no return value FUNCTION notify_at - same as above, with positioning FUNCTION accept_cancel - 'ask_for' = accept/cancel buttons, value in [AC] FUNCTION accept_cancel_at - same as above, with positioning FUNCTION screen_print - 'ask_for' = screen/print/exit buttons, value in [SPX] FUNCTION screen_print_at - same as above, with positioning FUNCTION yes_no - 'ask_for' = yes/no buttons, value in [YN] FUNCTION yes_no_at - same as above, with positioning } ############################################################################### FUNCTION alert ( title, msg_text ) { purpose: Display an alert box with message, then close it. inputs: 1. title: (optional) text of title for alert box. 2. msg_text: text of message, with | between lines. output: displayed alert box. } DEFINE title CHAR(64), msg_text CHAR(512) CALL alert_at ( 10, -1, title, msg_text ) END FUNCTION { alert } FUNCTION alert_at ( rw, cl, title, msg_text ) { purpose: Display an alert box with message, then close it. inputs: 1. rw: row location of top edge of dialog box. 2. cl: column location of left edge of dialog box 3. title: (optional) text of title for dialog box. 4. msg_text: text of message, with | between lines. output: displayed dialog box. } DEFINE rw SMALLINT, cl SMALLINT, title CHAR(64), msg_text CHAR(512) let ident = "@(#)$Header: dialg_lib.4gl,v 1.2 94/01/26 16:06:25 hmgr Exp $" LET atrow = rw LET atcol = cl LET ncols = LENGTH ( title ) + 2 IF ncols = 2 THEN { Special case for alerts without titles: } LET msg_text = "|", msg_text CLIPPED, "||" END IF { Prevent one-line box which fouls up menus. } CALL parse_text ( msg_text ) { Compute box dimensions. } LET ncols = ncols + 2 LET nrows = nlines IF LENGTH ( title ) > 0 THEN LET nrows = nrows + 2 END IF CALL open_dialog_box ( title ) SLEEP 3 CALL close_dialog_box () END FUNCTION { alert_at } ############################################################################### FUNCTION button ( title, msg_text, ask_for, okay ) { RETURNING CHAR(1) } { purpose: Display dialog box with buttons. inputs: 1. title: (optional) text of title for dialog box. 2. msg_text: text of message, with | between lines. 3. ask_for: text of prompt message. 4. okay: list of acceptable input chars. format: "[ABC]". outputs: 1. displayed dialog box. 2. function value: character entered by user, validated against "okay" characters. } DEFINE title CHAR(64), msg_text CHAR(512), ask_for CHAR(64), okay CHAR(8) RETURN button_at ( 10, -1, title, msg_text, ask_for, okay ) END FUNCTION { button } FUNCTION button_at ( rw, cl, title, msg_text, ask_for, okay ) { RETURNING CHAR(1) } { purpose: Display dialog box with buttons. inputs: 1. rw: row location of top edge of dialog box. 2. cl: column location of left edge of dialog box 3. title: (optional) text of title for dialog box. 4. msg_text: text of message, with | between lines. 5. ask_for: text of prompt message. 6. okay: list of acceptable input chars. format: "[ABC]". outputs: 1. displayed dialog box. 2. function value: character entered by user, validated against "okay" characters. } DEFINE rw SMALLINT, cl SMALLINT, title CHAR(64), msg_text CHAR(512), ask_for CHAR(64), okay CHAR(8), answer CHAR(1), { single character, input by user } last_ok SMALLINT { position of last okay character } LET atrow = rw LET atcol = cl LET ncols = LENGTH ( title ) + 2 CALL parse_text ( msg_text ) LET ask_for = buttons ( ask_for ) { Compute box dimensions. } LET ncols = ncols + 2 LET nrows = nlines + 2 IF LENGTH ( title ) > 0 THEN LET nrows = nrows + 2 END IF CALL open_dialog_box ( title ) { Prompt for and get user response. } LET INT_FLAG = FALSE LET answer = " " LET okay = UPSHIFT ( okay ) LET last_ok = LENGTH ( okay ) - 1 WHILE TRUE PROMPT ask_for CLIPPED, " " FOR CHAR answer CASE WHEN INT_FLAG LET INT_FLAG = FALSE LET answer = okay[last_ok] WHEN LENGTH ( answer ) < 1 LET answer = okay[2] OTHERWISE LET answer = UPSHIFT ( answer ) END CASE IF answer MATCHES okay THEN EXIT while ELSE ERROR "" END IF END WHILE CALL close_dialog_box () RETURN answer END FUNCTION { button_at } ############################################################################### FUNCTION dialog ( title, msg_text, ask_for ) { RETURNING CHAR(1) } { purpose: Display generalized dialog box. inputs: 1. title: (optional) text of title for dialog box 2. msg_text: text of message, with | between lines 3. ask_for: (optional) text of prompt message outputs: 1. displayed dialog box 2. function value: character entered by user, unvalidated } DEFINE title CHAR(64), msg_text CHAR(512), ask_for CHAR(64) RETURN dialog_at ( 10, -1, title, msg_text, ask_for ) END FUNCTION { dialog } FUNCTION dialog_at ( rw, cl, title, msg_text, ask_for ) { RETURNING CHAR(1) } { purpose: Display generalized dialog box. inputs: 1. rw: row location of top edge of dialog box. 2. cl: column location of left edge of dialog box 3. title: (optional) text of title for dialog box 4. msg_text: text of message, with | between lines 5. ask_for: (optional) text of prompt message outputs: 1. displayed dialog box 2. function value: character entered by user, unvalidated } DEFINE rw SMALLINT, cl SMALLINT, title CHAR(64), msg_text CHAR(512), ask_for CHAR(64), answer CHAR(1) { single character, input by user } LET atrow = rw LET atcol = cl LET ncols = LENGTH ( title ) + 2 CALL parse_text ( msg_text ) { Set default prompt string, if necessary. } IF LENGTH ( ask_for ) = 0 THEN LET ask_for = " Press any key to continue." ELSE IF ask_for[1] != " " THEN LET ask_for = " ", ask_for CLIPPED END IF END IF { Compute box dimensions. } IF LENGTH ( ask_for ) > ncols THEN LET ncols = LENGTH ( ask_for ) END IF LET ncols = ncols + 2 LET nrows = nlines + 2 IF LENGTH ( title ) > 0 THEN LET nrows = nrows + 2 END IF CALL open_dialog_box ( title ) { Prompt for and get user response. } PROMPT ask_for CLIPPED, " " FOR CHAR answer CALL close_dialog_box () RETURN answer END FUNCTION { dialog_at } ############################################################################### FUNCTION notify ( title, msg_text ) { purpose: Display generalized dialog box. inputs: 1. title: (optional) text of title for dialog box. 2. msg_text: text of message, with | between lines. output: displayed dialog box. } DEFINE title CHAR(64), msg_text CHAR(512) CALL notify_at ( 10, -1, title, msg_text ) END FUNCTION { notify } FUNCTION notify_at ( rw, cl, title, msg_text ) { purpose: Display generalized dialog box. inputs: 1. title: (optional) text of title for dialog box. 2. msg_text: text of message, with | between lines. output: displayed dialog box. } DEFINE rw SMALLINT, cl SMALLINT, title CHAR(64), msg_text CHAR(512), answer CHAR(1) { single character, input by user } LET answer = dialog_at ( rw,cl, title, msg_text, " Press space bar to continue." ) END FUNCTION { notify } ############################################################################### { Generally useful button box routines. } ############################################################################### FUNCTION accept_cancel ( title, msg_text ) { RETURNING CHAR(1) } { purpose: Display dialog box with accept/cancel buttons. inputs: 1. title: (optional) text of title for dialog box 2. msg_text: text of message, with | between lines outputs: 1. displayed dialog box 2. function value: character entered by user, either "A" or "C" } DEFINE title CHAR(64), msg_text CHAR(512) RETURN button_at ( 10, -1, title, msg_text, " ((Accept)) (Cancel)", "[AC]" ) END FUNCTION { accept_cancel } FUNCTION accept_cancel_at ( rw, cl, title, msg_text ) { RETURNING CHAR(1) } { purpose: Display dialog box with accept/cancel buttons. inputs: 1. rw: row location of top edge of dialog box. 2. cl: column location of left edge of dialog box 3. title: (optional) text of title for dialog box 4. msg_text: text of message, with | between lines outputs: 1. displayed dialog box 2. function value: character entered by user, either "A" or "C" } DEFINE rw SMALLINT, cl SMALLINT, title CHAR(64), msg_text CHAR(512) RETURN button_at ( rw, cl, title, msg_text, " ((Accept)) (Cancel)", "[AC]" ) END FUNCTION { accept_cancel_at } ############################################################################### FUNCTION screen_print ( title, msg_text ) { RETURNING CHAR(1) } { purpose: Display dialog box with screen/print/exit buttons. inputs: 1. title: (optional) text of title for dialog box 2. msg_text: text of message, with | between lines outputs: 1. displayed dialog box 2. function value: character entered by user, one of "S","P","X" } DEFINE title CHAR(64), msg_text CHAR(512) RETURN button_at ( 10, -1, title, msg_text, " ((Screen)) (Print) (eXit)", "[SPX]" ) END FUNCTION { screen_print_at } FUNCTION screen_print_at ( rw, cl, title, msg_text ) { RETURNING CHAR(1) } { purpose: Display dialog box with screen/print/exit buttons. inputs: 1. rw: row location of top edge of dialog box. 2. cl: column location of left edge of dialog box 3. title: (optional) text of title for dialog box 4. msg_text: text of message, with | between lines outputs: 1. displayed dialog box 2. function value: character entered by user, one of "S","P","X" } DEFINE rw SMALLINT, cl SMALLINT, title CHAR(64), msg_text CHAR(512) RETURN button_at ( rw, cl, title, msg_text, " ((Screen)) (Print) (eXit)", "[SPX]" ) END FUNCTION { screen_print_at } ############################################################################### FUNCTION yes_no ( title, msg_text ) { RETURNING CHAR(1) } { purpose: Display dialog box with yes/no buttons. inputs: 1. title: (optional) text of title for dialog box 2. msg_text: text of message, with | between lines outputs: 1. displayed dialog box 2. function value: character entered by user, either "Y" or "N" } DEFINE title CHAR(64), msg_text CHAR(512) RETURN button_at ( 10, -1, title, msg_text, " ((Yes)) (No)", "[YN]" ) END FUNCTION { yes_no } FUNCTION yes_no_at ( rw, cl, title, msg_text ) { RETURNING CHAR(1) } { purpose: Display dialog box with yes/no buttons. inputs: 1. rw: row location of top edge of dialog box. 2. cl: column location of left edge of dialog box 3. title: (optional) text of title for dialog box 4. msg_text: text of message, with | between lines outputs: 1. displayed dialog box 2. function value: character entered by user, either "Y" or "N" } DEFINE rw SMALLINT, cl SMALLINT, title CHAR(64), msg_text CHAR(512) RETURN button_at ( rw, cl, title, msg_text, " ((Yes)) (No)", "[YN]" ) END FUNCTION { yes_no_at } ############################################################################### { Internal routines. Not intended for external use. } ############################################################################### FUNCTION buttons ( ask_for ) { RETURNING CHAR(64) } { purpose: Construct a prompt string containing centered "buttons". inputs: 1. ask_for: simulated buttons, as prompt string. 2. ncols: number of columns in box. outputs: 1. functions value: centered prompt string. 2. ncols: possibly incremented number of columns in box. NOTE: This function is intended for use ONLY by routines in this module. } DEFINE ask_for CHAR(64), blanks CHAR(64), n SMALLINT IF LENGTH ( ask_for ) > ncols THEN LET ncols = LENGTH ( ask_for ) END IF LET blanks = " " LET n = ( ncols - LENGTH ( ask_for ) ) / 2 IF n > 0 THEN LET ask_for = blanks[1,n], ask_for CLIPPED END IF RETURN ask_for END FUNCTION { buttons } ############################################################################### FUNCTION close_dialog_box () { purpose: Close window and reset position of PROMPT LINE. input: none. outputs: 1. closed dialog box. 2. OPTIONS reset. NOTE: This function is intended for use ONLY by routines in this module. } CLOSE WINDOW dialog_box OPTIONS PROMPT LINE FIRST END FUNCTION { close_dialog_box } ############################################################################### FUNCTION open_dialog_box ( title ) { purpose: Open window and display title, if any, and message text. input: title: (optional) text of title for dialog box outputs: 1. displayed dialog box. 2. function value: 0 or 2, used as affset for positioning the rest of the line in the dialog box. NOTE: This function is intended for use ONLY by routines in this module. } DEFINE title CHAR(64), j, k, n SMALLINT, { scratch indices used for positioning, etc. } topline CHAR(64) { variable used to construct title line } IF atrow <= 0 OR atrow >= 50 THEN { Test for uninitialized row. } LET atrow = 10 END IF IF atcol <= 0 OR atcol >= 70 THEN { Test for "center box" flag: atcol <= 0 } LET atcol = (80 - ncols) / 2 + 1 END IF OPEN WINDOW dialog_box AT atrow,atcol WITH nrows ROWS, ncols COLUMNS ATTRIBUTE (BORDER, PROMPT LINE LAST) IF LENGTH ( title ) > 0 THEN LET topline = " " LET n = ( ncols - LENGTH ( title ) ) / 2 LET topline = topline[1,n], title CLIPPED DISPLAY topline AT 1,1 LET n = 2 ELSE LET n = 0 END IF { Display message text. } FOR j = 1 TO nlines LET k = j + n DISPLAY textline[j] AT k,2 END FOR END FUNCTION { open_dialog_box } ############################################################################### FUNCTION parse_text ( msg_text ) { purpose: Parse msg_text into lines. inputs: 1. msg_text: text of message, with | between lines 2. ncols: (minimum) number of columns in box outputs: 1. ncols: (possibly) updated number of columns in box, based on widest line in textline array 2. nlines: number of lines in textline array 3. textline: array containing separated lines of msg_text NOTE: This function is intended for use ONLY by routines in this module. } DEFINE msg_text CHAR(512), { text of message, with | between lines } j, k, n SMALLINT, { indexes used to parse msg_text into line } len SMALLINT { length of msg_text string } LET len = LENGTH ( msg_text ) LET n = 0 LET nlines = 0 WHILE n < len AND nlines < 10 { Search for line separator. } LET j = n + 1 LET k = j WHILE msg_text[k] != "|" AND k < len LET k = k + 1 END WHILE IF k - j > 72 THEN LET k = j + 72 END IF { Separator found. Check for special cases. } LET n = k IF msg_text[k] = "|" THEN LET k = k - 1 END IF { Move line into array of lines. } LET nlines = nlines + 1 IF k <= j THEN LET textline[nlines] = "" ELSE LET textline[nlines] = msg_text[j,k] END IF IF LENGTH ( textline[nlines] ) > ncols THEN LET ncols = LENGTH ( textline[nlines] ) END IF END WHILE END FUNCTION { parse_text } ############################################################################### @EOF chmod 664 dialg_lib.4gl echo x - idx_form.per cat >idx_form.per <<'@EOF' database formonly screen { Index Analysis Table Rows Size Pages [f000 ] [f001 ] [f002 ] [f003 ] Colname Description Hit Type Unique Possible Src Clust Vals Joins [f010 |f014 |f011 |a] [b] [f015 |f016 ] [f010 |f014 |f011 |a] [b] [f015 |f016 ] [f010 |f014 |f011 |a] [b] [f015 |f016 ] [f010 |f014 |f011 |a] [b] [f015 |f016 ] [f010 |f014 |f011 |a] [b] [f015 |f016 ] idxname T C Columns [f020 |c|d|f021 ] [f020 |c|d|f021 ] [f020 |c|d|f021 ] [f020 |c|d|f021 ] } attributes f000 = formonly.s_tabname type char, reverse; f001 = formonly.s_nrows type integer, format = "###,###,###", reverse; f002 = formonly.s_rowsize type smallint, format = "###,###", reverse; f003 = formonly.s_page type integer, format = "###,###,###", reverse; f010 = formonly.colname type char, reverse; f014 = formonly.desc type char, reverse; f011 = formonly.occ_cnt type smallint, format = "###,###", reverse; a = formonly.idxtype type char, reverse; b = formonly.clustered type char, reverse; f015 = formonly.uniq_cnt type decimal, format = "###.##", reverse; f016 = formonly.joins type smallint, format = "###,###", reverse; f020 = formonly.idxname type char, reverse; c = formonly.x_idxtype type char, reverse; d = formonly.x_clustered type char, reverse; f021 = formonly.columns type char, reverse; end instructions DELIMITERS " " SCREEN RECORD s_coldata[5](formonly.colname, formonly.desc, formonly.occ_cnt, formonly.idxtype, formonly.clustered, formonly.uniq_cnt, formonly.joins) SCREEN RECORD s_idxdata[4](formonly.idxname, formonly.x_idxtype, formonly.x_clustered, formonly.columns) @EOF chmod 444 idx_form.per echo x - selector.per cat >selector.per <<'@EOF' database formonly screen { [f000 ] [f001 ] [f001 ] [f001 ] [f001 ] [f001 ] [f001 ] [f001 ] [f001 ] [f001 ] [f001 ] CTL-N Nxt, CTL-P=Prv } end attributes f000=formonly.s_title type character; f001=formonly.s_data type character; end instructions DELIMITERS " " SCREEN RECORD s_selector[10](formonly.s_data) @EOF chmod 444 selector.per echo x - idxhelp.txt sed 's/^@//' >idxhelp.txt <<'@EOF' @.100 About analyse_idx is a program designed to give you information about your indices. Keys: Help is CTRL-W Next page is CTRL-N Previous page is CTRL-P Accept key is RETURN or CTRL-M [ Page 1 of 6 ] Displayed Data: The Main form is split into three sections. The first of these is a 'Table' section. You must first select a table using the 'Table' option for data to be displayed. At this point the following information is displayed: Table: The table name. Rows: The number of rows in the table based on the contents of systables. This value is updated whenever an update statistics command is issued. This program DOES NOT DO THAT. If you want current data then do it yourself. Size: This if the rowsize value from systables. It does not include the size of the indices. Pages: This is calculated by rows * (rowsize / pagesize). Page size is machine dependent. You may change it at will in the analyse source code (FUNCTION hskpng()). Why do you care? If the table is small enough there is little point to having ANY indices on the table. If the table is very large then the program will limit the amount of reading it does against that table. This threshold value can be adjusted in the Options sub-menu. Column section The data displayed herein is the contents of the index analysis table (use Source or Catalogues for loading this table). It is sorted with the most frequently used column in joining at the top, followed by the 'uniqueness' value for each field. This is determined from reading your source files. Uniquesness is determined by SELECT UNIQUE COLUMN INTO TEMP and then using SQLCA.SQLERRD[3] as the value / nrows from systables. The following data is displayed. [ Page 3 of 6 ] Colname: The column name. Description: The datatype of this column. Integer keys are best. Hit Src: The number of references to this field in WHERE clauses found in your source. A high number indicates that this field is used a lot in joins and would make a better index than, say, a field which is NOT used. This value is not precisely accurate (See Source Help for it calculation). Type: If this column is indexed then its index type will display here (D=Duplicate, U=Unique). Note that since only the first part of a composite index is counted herein - you may wind up with some things that don't look right. For further information on an index look in the index section. Clust: This will be a 'C' if the index is clustered - or in other words if the data is in the table in the order of this index. [ Page 4 of 6 ] Unique Vals: This is a percentage of the number of unique values in this column in this table over the number of rows (nrows). Since nrows may be out of date - it is possible to have a number higher than 100. The point of this field is that low numbers of unique values make a poor index. It is possible to have a 'U' under index type with less than 100 in this column - this is because the index noted may be a composite. Possible Joins: This is the number of other columns in this database that have the same name and length. A high value here indicates that this may be a common joining field. Look for your most important indices to show up at the top of this list. [ Page 5 of 6 ] Index Section The data displayed here is pretty much for reference only: Idxname: The name of the index. T: Index type (Duplicate or Unique) C: Clustered Columns: The first, however many will fit, columns that make up this index. [ Page 6 of 6 ] @.101 Source Scan This option will scan all source files from a given point in your directory tree (it will ask) searching for index use within your code. It does this by doing a 'find' followed by a grep on each 'found' file for the key words WHERE, AND and OR. It is by no means exhaustive, but the source references found should be indicative of index use. You are given an opportunity to edit the list of files before the grep process is started - this allows you to remove garbage files that are in your source tree but are not used and to weight certain programs. If a program is frequently run you may weight it twice as much as other programs by duplicating its entry in the file list. [ Page 1 of 2 ] This data will be counted and loaded into the analysis table (created as needed). It may take some time depending on the size of your source tree and the speed of your machine - so be careful with its use. It takes about 15 min on our machine. NOTE: Much of the time in source code the name of the table is left out of the WHERE clause. This is handled by pro-rating these references and assigning their occurence to valid table/column pairs. This method is not very accurate - but should be indicative. I would suggest ignoring values under 5. [ Page 2 of 2 ] @.102 Load System Catalogues This option will load the analysis table with a list of columns and tables within your schema. It should be run after the Source Scan to fill in any gaps. NOTE: Source Scan will remove all system catalog data from the analysis table when it is run. Therefore you should generally run this option next. It is not automatic because you may not wish to see any indices but those mentioned in the source code. It is included in case you do not have access to the source code. @.103 Check Specific Table This option will display the current analysis data for all columns within a table. @.105 Options This sub-menu allows you to update operating parameters. @.106 Report This option will report all data collected to date to whatever your default printer is. Under unix this is reflected either in 'lpstat -d' or in your environment variable LPDEST. You are allowed to specify a tablename (standard wildcards accepted). @.107 Clean up This option will remove the analysis file from your database. This is optional because retrieving that data in the first place is time consuming and you may not wish to repeat it. @.108 Exit Program This option will exit the program. @.109 Scroll This option moves your cursor into the Column or Index array so you can scroll through the data. CTRL-N is Next Page, CTRL-P is Previous. @.111 Exit Menu This option will return you to the previous menu. @.120 Specify size One of the values retrieved for each column is a number of unique values in that column expressed as a percentage. For large tables this value make take a while to retrieve. This feature allows you to set a threshold for table sizes above which this program will not attempt to retrieve this value. @.121 Specify database Change the database which is currently selected for analysis. This option will recreate the analysis table in the new database as necessary. @EOF chmod 664 idxhelp.txt echo x - makefile cat >makefile <<'@EOF' i4gl_ver : analyse_idx idx_form.frm selector.frm idxhelp.msg r4gl_ver : analyse_ix.4gi idx_form.frm selector.frm idxhelp.msg analyse_idx : analyse.4gl c4gl analyse.4gl dialg_lib.o -o $@ rm analyse.ec analyse.c analyse_ix.4gi : analyse.4gl dialg_lib.4gl fglpc analyse.4gl fglpc dialg_lib.4gl cat analyse.4go dialg_lib.4go >analyse_ix.4gi dialg_lib.o : dialg_lib.4gl c4gl -c dialg_lib.4gl rm dialg_lib.ec dialg_lib.c idx_form.frm : idx_form.per form4gl idx_form.per selector.frm : selector.per form4gl selector.per idxhelp.msg : idxhelp.txt mkmessage idxhelp.txt idxhelp.msg sharfile : READ.ME analyse.4gl dialg_lib.4gl \ idx_form.per selector.per idxhelp.txt makefile \ idx_scansrc shar READ.ME analyse.4gl dialg_lib.4gl \ idx_form.per selector.per idxhelp.txt makefile \ idx_scansrc > idx_analyser.sh @EOF chmod 664 makefile echo x - idx_scansrc cat >idx_scansrc <<'@EOF' ############################################################################### # # idx_scansrc: Find index references in source # # JParker 6/1/94 # ############################################################################### count_words() { echo "Counting words.....\c" awk ' BEGIN { idx=0 max_idx=0 OFS="|" # output field seperator } { t_tab="" t_col="" # look for "." if found then split off table and column if ((index($0,".")) > 0) { n=split($0,arr,".") t_tab=arr[1] t_col=arr[2] } else t_col=$0 # if tab exists then search array for table and column # else look for column if ( t_tab != "" ) { for (idx=1;idx<=max_idx;idx++) { if ( t_tab == tab[idx] && t_col == col[idx] ) break } } else { for (idx=1;idx<=max_idx;idx++) { if ( t_col == col[idx] ) break } } # broken out of loop idx is either found, or maxed out if (idx<=max_idx) cnt[idx]++ else { max_idx++ tab[idx]=t_tab col[idx]=t_col cnt[idx]=1 } } END { for (idx=1;idx<=max_idx;idx++) { print tab[idx], col[idx], cnt[idx], 0 } }' < /tmp/idx_scan3 > /tmp/idx_scan.unl echo "done" } scan_src() { # get the source tree and scan it for WHERE | AND | OR (indexes) src="" while [ -z "$src" ] do echo " I am going to scan the source tree for index usage. Where might I find its head?\c" read src if [ ! -d "$src" ] then echo "I cannot see $src (not a directory)" src="" else if [ ! -r "$src" ] then echo "I cannot read $src (permissions)" src="" fi fi done fls="" cat /dev/null > /tmp/idx_files echo "Scan for .4gl files? (Y/N)\c" read ans if [ $ans = "Y" -o $ans = "y" ] then echo "Scanning...\c" find $src -name "*.4gl" -print > /tmp/idx_files echo "done\n" fi echo "Scan for .c files? (Y/N)\c" read ans if [ $ans = "Y" -o $ans = "y" ] then echo "Scanning...\c" find $src -name "*.c" -print >> /tmp/idx_files echo "done\n" fi echo "Scan for .ec files? (Y/N)\c" read ans if [ $ans = "Y" -o $ans = "y" ] then echo "Scanning...\c" find $src -name "*.ec" -print >> /tmp/idx_files echo "done\n" fi echo "Scan for .ace files? (Y/N)\c" read ans if [ $ans = "Y" -o $ans = "y" ] then echo "Scanning...\c" find $src -name "*.ace" -print >> /tmp/idx_files echo "done\n" fi no_fls=`wc -l /tmp/idx_files | awk '{print $1}'` echo "I have now prepared a list of $no_fls source files. You can weight the analysis for frequently used programs by duplicating their entry in this list. Would you like to edit the list? (Y/N)\c" read ans if [ $ans = "Y" -o $ans = "y" ] then $editor /tmp/idx_files fi echo "Grepping...\c" cat /tmp/idx_files | while read name do # get file name too. grep -isl -e "WHERE " -e "OR " -e "AND " $name if [ $? -eq 0 ] then echo $name grep -i -e "WHERE " -e "OR " -e "AND " $name echo " " fi done > /tmp/idx_scan1 echo "done\n" # get only "=" lines, lose some basic stuff. grep "=" /tmp/idx_scan1 | grep -iv -e "let " -e "if " -e "for "> /tmp/idx_scan2 grep -i "between" /tmp/idx_scan1 >> /tmp/idx_scan2 no_lns=`wc -l /tmp/idx_scan2 | awk '{print $1}'` echo "I have now prepared a rough list of $no_lns lines on which I think indexes are used." # lose array style vars. # turn into a list of words # lose the three basic words WHERE AND OR grep -v "\[" < /tmp/idx_scan2 | tr -cs "[A-Z][a-z][0-9]\.\_\"" "[\012*]" | grep -v "\"" | grep -vi -e "WHERE" -e "OR" -e "AND" > /tmp/idx_scan3 no_wds=`sort -u /tmp/idx_scan3 | wc -l` # not to worry about excess garbage, it is # all matched against syscolumns anyway # and garbage is thrown out. echo "I now have a list of $no_wds words" } editor=vi # used for editing lists. scan_src count_words exit 0 @EOF chmod 700 idx_scansrc exit 0