## fragment-list.sh - list of partitions, table names and, when appro- ## priate, index names. ## ## This script was originally partition-list.sh, written as a ## preliminary function to be used by partitions.sh and who-locks.sh to ## simplify their logic. It has been enhanced with a copuple of ## distinguishing features, enough to justify a new name, described ## below. ## ## Enahnced while in the employ of oninit.com ## ## -- Jacob Salomon jakesalomon@yahoo.com ## ## ********************* IMPORTANT NOTE ****************************** ## This script accesses the system catalogs of each database in the ## server. If the user running the script does have connect privelege on ## any database, that database will be skipped in the output. An ## attempt at a work-around was to: ## o chown informix:informix ptlist.sh # Set the script ownership to ## # informix:informix ## o chmod ug+s ptlist.sh # Set the SET-UID/GID flags ## ## This does not help. For now our options are to: ## - deal with it ## - run the script only when logged in as (or SU'd to) user informix ## - specify a list of databases as a parameter list. ## ## Modifications for fragment-liet.sh over partition-list.sh: ## 1. Displays completely qualified table name: database:owner.tabname ## 2. 4 new columns drawn from sysfragments catalog: ## - Partition Strategy (Null, E, R, I, D) ## - Fragment number (0 thru n-1) - Ceom sysfragments.eval_pos ## - Partition name (which may not be the same as the dbspace) ## - The fragmentation expression. THis may be unwildy but the output ## of this script is meant to be used by other scripts, not read by ## human eyes. ## 3. Fixed a bug whereby the partition information on the partition ## partition tables themselves would display, although a database was ## specified. ## ******************************************************************* ## ## Note: Whether or not to have dbaccess echo back the sql commands as ## it executes them: By using the variable DASH_E, I can decide whether ## or not to echo them. Is you can see from the setting of DASH_E, I ## have opted to echo. ## #DASH_E="" # If SQL-echo is not desired #DASH_E="-e" # dbaccess -e option to echo SQL #TRUE=0 # Standard shell truth values #FALSE=1 ## ## ## Function: dblist() - Output a list of all target database in the ## current Informix server instance. If a database (or a list ## of databases) was specified, then reflect back only those ## databases. ## #dblist() #{ # if [ "$db_list" = "" ] # If database list is empty, scan them all # then # std_err=/dev/null # dbaccess sysmaster - 2>${std_err} <<%% | sed -e '/^$/d' # output to pipe "cat" without headings # select name from sysdatabases order by 1; #%% # else # echo $db_list | tr "," " " # fi #} # ## Function: is_logging() - Tell me if a database is set for logging ## Parameter: ## - The name of a database ## Returns: ## - 0 (true) if named database has logging, ## - 1 (false) if database has no logging ## - 2 (ENOEXIST) if database does not exist ## #is_logging() #{ # dbparam_name=${1:-"no_param"} # (In case parameter was omitted) # # # Note: The sed commands in the following pipeline are to eliminate # # blanks and empty lines. # # # lstatus=$( # dbaccess sysmaster - 2>/dev/null <<%% | sed -e s/' '*// -e /^$/d # output to pipe "cat" without headings # select is_logging # from sysdatabases # where name = "$dbparam_name"; #%% # ) ## echo "lstatus($dbparam_name)" = "<$lstatus>" # case "$lstatus" in # 1) rval=0 ;; # Status 1 (yes) => shell-truth value of 0 (True) # 0) rval=1 ;; # Status 0 (no) => shell-truth value of 1 (False) # *) rval=2 ;; # Only error assumed: database does not exist # esac # # return $rval # Not echo; return value as an exit status #} ## ## Main execution starts here. ## Scheme: Build a set of queries, one query for each database, that ## lists all partitions in the database. Then run a few more to get ## info about tables or, more accurately, tblspaces, that may not reside ## in databases. These are: ## - Temp tables, which may me associated with a database or may show up ## as belonging to "databases" SORTTEMP or HASHTEMP. ## - The database TBLspace, which is a real partitions but has only a ## view representation in sysmaster. ## - The partition table of each dbspace, known as table TBLspace. ## For these partitions, I have to fake some of the data, especially the ## tabid and table type. ## ## Note that for the queries that seek out real tables within each ## database, a union is also required because the partition number of ## fragmented table appears in sysfragments, not in systables. ## ## Enough explanation for you? ;-) ## ## These flag masks indicate something is a temp or hash table ## #SYSTEMP=\'0x20\' #USRTEMP=\'0x40\' #HASHTEMP=\'0x80\' # ## The environment variable BOGUS_TABID might have been defined & ## exported before this script was called. Try to use it but be ## prepared in case it has not been exported. ## #temp_tabid=${BOGUS_TABID:=2147483646} # Bogus tabid for temp tables # ## Set up some file names to use for output at various stages. ## #Q_Out=/tmp/QO.$$.out #Q_Template=/tmp/QT.$$.sql # Name of template file to build queries #U_Total=/tmp/UT.$$.unl # Combine unl files here #>${U_Total} # Create that total-unload file # #ISOLATION="set isolation to dirty read;" # (If I may use this clause) # ## Now, for which databases/tables do I hunt up information? ## #DBLISTED=$FALSE # Expect most calls to be for all databases #if [ $# -eq 0 ] # If caller supplied no parameters #then # DBLIST=$(dblist) # Get partitions info on all databases #else # If caller told me which databases # DBLIST="$*" # then hunt up only those # DBLISTED=$TRUE # Indicate a database list was supplied #fi ## ## Create a template SQL script file to be used for each database ## that I will search. ## #cat >$Q_Template <<%% #%ISO #--unload to %UNL #select "%DB" database, -- Name of database as a constant # owner, -- Name of the owner of the table # tabname, # tabid, -- Include this to identify catalogs # partnum, -- Partition number - no hex now # "S" ft, -- Fragment type is *S*ingle table # "(NA)" index, -- No index name in table partition # "-" strategy, -- Not round-robyn, expression, or index # -1 part_pos, -- No partition position in regular table # "(NA)" part_name -- Regular table has no partition name # from %DB:informix.systables # where partnum != 0 -- Avoid fragments, views, synonyms etc. #union #select "%DB" database, -- For fragmented tables # owner, -- Name of the owner of the table # tabname, # t.tabid, -- Include this to identify catalogs # partn partnum, -- Fragmented table: Partition num is here # fragtype ft, -- Fragment type # indexname index, -- Here the index name may be relevant # f.strategy, -- R, E, or I # f.evalpos part_pos, -- Fragment number within table # f.partition part_name -- Partition name # from %DB:informix.systables t, %DB:informix.sysfragments f # where t.tabid = f.tabid # into temp %DB_fragment_partns with no log -- Will update exprtext cols #; # #-- To get at the fragmentation expressions, which are text blobs, I #-- need first unload them into a flat text file, then load them back #-- into another temp table with a lvarchar column. There is no easy #-- conversion from text blob to lvarchar. #-- #unload to /tmp/%DB_justFragExpressions.$$.unl #select sf.partn, sf.exprtext # from informix.sysfragments sf # where sf.strategy in ("E", "T") #; #create temp table %DB_justFragExpressions #( partn integer, # expr lvarchar(4000) #) with no log #; #load from /tmp/%DB_justFragExpressions.$$.unl #insert into %DB_justFragExpressions #; #-- #!rm /tmp/%DB_justFragExpressions.$$.unl #-- Now update the expression placeholders with the expression from the #-- "just fragment expressions" row that matches the same partition #-- number. #-- #--X update %DB_fragment_partns #--X set exprtext = %DB_justFragExpressions.expr #--X where %DB_fragment_partns.partnum = %DB_justFragExpressions.partn #--X; #-- Note: The above does not work. Not necessary here, since I need to #-- see the expression, which could come from two tables. # #-- If all has worked, the new temp table has all the fragmentation #-- expressions along with the other data. #-- #--unload to %UNL select * from %DB_fragment_partns ; #unload to %UNL #select fp.database, # fp.owner, # fp.tabname, # fp.tabid, # fp.partnum, # fp.ft, # fp.index, # fp.strategy, # fp.part_pos, # fp.part_name, # jfe.expr # from %DB_fragment_partns fp, outer(%DB_justFragExpressions jfe) # where jfe.partn = fp.partnum # --order by database, owner, tabname, ft desc, strategy desc, part_pos #; #%% ## ## Note: In the dbaccess command below (in the FOR loop) I need to go ## into each database itself. I cannot connect to sysmaster and access ## the catalogs of all other databases for a cute reason: Those databases ## may not have the same logging mode as sysmaster. It is [currently] ## an Informix restricttion that if I am connected to a logged database, ## I cannot query an unlogged database. (Error -568: Cannot reference an ## external database without logging.) ## #for dbname in $DBLIST #do # if is_logging $dbname # If the database has logging # then # ISOLATION_CLAUSE="$ISOLATION" # else # No logging on this database # ISOLATION_CLAUSE="--${ISOLATION}" # Avoid "no transactions" error # fi # U_Plop=/tmp/UNL-${dbname}.$$.unl # Unload-file for database query # >${U_Plop} # Make sure UNL file exists for rm command # printf "Database: <%s>\n" "${dbname}" >>$Q_Out # sed -e s@%DB@${dbname}@g \ # -e s@%UNL@${U_Plop}@g \ # -e s@%ISO@"${ISOLATION_CLAUSE}"@ ${Q_Template} | # dbaccess $DASH_E $dbname - >>$Q_Out 2>&1 # (Debugging version has -e) # cat $U_Plop >>$U_Total # Combine UNL files # rm $U_Plop # Remove single-table UNL file #done # ## The above queries have handled all tables listed in someone's system ## catalog. The job is not complete because temp tables and master do not ## appear there; their info can be found only in the SMI tables. The ## following queries fill that gap. ## #U_Plop=/tmp/UNL-temps.$$.unl # Unload file for temps query #>$U_Plop # Make sure it exists for remove #printf "Database: (temps)\n" >>$Q_Out ## ## I have included tabid only to locate catalogs by a tabid < 100. ## Use a bogus tabid for all temp tables to avoid mistaking a temp ## table for a catalog ## #dbaccess $DASH_E sysmaster <<%% >>$Q_Out 2>&1 #set isolation to dirty read; #unload to $U_Plop #select tn.dbsname, # tn.owner, # tn.tabname, # $temp_tabid tabid, -- Bogus tabid for temp tables # tn.partnum, # "t" ft, -- Temp table fragment type # "(temp)" index, -- No index fragments for temp tables # "?" strategy, -- How do I find strategy of temp table? # 0 part_pos, -- How do I determine position of temp frag? # "(temp)" part_name, -- Regular table has no partition name # "(bogus1)" # from sysmaster:systabnames tn, # sysmaster:systabinfo ti # where tn.partnum = ti.ti_partnum # and ( (sysmaster:bitval(ti_flags,$SYSTEMP) = 1) # or (sysmaster:bitval(ti_flags,$USRTEMP) = 1) # or (sysmaster:bitval(ti_flags,$HASHTEMP) = 1) # ) #; #%% #cat $U_Plop >>$U_Total # Combine with other UNL output #rm $U_Plop # Get rid of this as part of unl build ## ## And now for the tblspace partition and sysdatabases itself: ## bit only if this was called for all partitions ie. with no databases ## on the command line. If a database was specified, skp this extra. ## #if [[ "${DBLISTED}" -eq $FALSE ]] #then # User did not specify database; show Partitions # dbaccess $DASH_E sysmaster <<%% >>$Q_Out 2>&1 # set isolation to dirty read; # unload to $U_Plop # select "Partition" database, # owner, -- (Informix owns all system tables) # tabname, # $temp_tabid tabid, # partnum, # "P" ft, # "(part)" index, # "-" strategy, -- Partition table is not fragmented, so.. # 0 part_pos, -- No partition position in partn table # "(part)" part_name, -- Partition table has no partition name # "(PartitionTable)" # from sysmaster:systabnames tn # where tabname = "TBLSpace" # union # select dbsname database, # owner, -- (Informix owns all system tables) # tabname, # $temp_tabid tabid, # partnum, # "D" ft, # "(NA)" index, # "-" strategy, -- sysdatabases is not fragmented so # -1 part_pos, -- No partition position # "(NA)" part_name, -- and no partition name # "(SystemTable)" # from sysmaster:systabnames tn # where tabname = "sysdatabases" # ; #%% #fi #cat $U_Plop >>$U_Total # Combine with other UNL output #rm $U_Plop # Get rid of this as part of unl build # ## OK, the file whose name is hiding in $U_Total has info on all ## partitions in the the entire system. That is the end-all of this ## entire exercise. Output it to the STDOUT and clear outa here! ## #cat $U_Total # The main output of this program ## ## Now clean up after ourselves ## #rm $Q_Template #rm $U_Total #grep [0-9]: $Q_Out >/dev/null # Just find IF there was an error msg #all_ok=$? # Grab copy of the exit code #if [ $all_ok -eq 0 ] # If an error message appears in the file #then # display the entire error file # echo 1>&2 Warning: Database access errors detected # cat $Q_Out 1>&2 #fi #rm $Q_Out