© 2003 International Business Machines Corporation. All rights reserved.

Important: Read the disclaimer before reading this article.

Introduction

With the advent of data warehousing and its requirement for information about data, there has been a great deal of focus on metadata. Much of it deals with external repositories, and many have forgotten or do not realize that substantial metadata already exists within any Informix© SQL database in the system catalogs. Furthermore, knowledge of the system catalogs is a highly useful tool for the day-to-day maintenance of the database.

Thinking about this recently, I realized that I have never read an article on these catalogs telling me what I could do with them. Therefore I resolved to write one. Although there is little I can say to improve upon the excellent documentation available within the SQL reference manual (Chapter 2), perhaps I can provide some insight into how to pull the information out of these catalogs in a useful manner. To read this article properly, pull out the SQL Reference manual and keep one hand on the keyboard to try the examples.

This is not an exhaustive treatment of the subject. There are some catalog tables of minimal interest and a number of others which describe code which was placed into the system in one form or another - procedures, triggers, views; dbschema is a better tool for extracting this information.

You should never update or insert data into the system catalogs manually. Updating catalogs should always be performed with the proper SQL/DDL statements.

Back to top

Tables

Let's start with a simple look at the systables table. As you can see from the documentation, this contains some rather basic information: the table name (tabname), owner of the table (owner), number of rows (nrows), number of columns (ncols), number of indexes (nindexes) and so forth. A reasonable "hello world" type of query might be:

 
SELECT tabname FROM systables;			 

This will give us a list of all of the table names within the database. Included in that list we will find systables itself! We really don't care about the catalogs themselves, so let's exclude them from our query. It just so happens that the tabid used to number tables is reset after the database is created so that the first new table will have a tabid of 100:

 
SELECT tabname FROM systables WHERE tabid > 99;			 

We now see just a list of the tables we have created ... well sort of: we also see tables like sysmenus and sysmenuitems (if we have defined the isql user menu) and syscolatt (if we have used upscol to define form level attributes for any columns within the database). Also we'll see syscdr* tables, if we are in a replication environment, and bld* and sysbld* tables, if we have run Blade Manager to list or register DataBlade modules. These tables are not in the 'under 100' list of tables, because they are created after the database. Perhaps we should have done:

 
SELECT tabname  
  FROM systables  
 WHERE tabname NOT MATCHES 'sys*'; 

This will avoid most of the system catalogs, but will include a few special non-tables ' GL_COLLATE', ' GL_CTYPE' , and ' VERSION'; which describe our NLS settings and the engine version. If we have created our own tables using the 'sys*' prefix these too will be avoided, although I would recommend using a separate database for any metadata table we create for our own purposes. For brevity's sake I am going to leave out altogether the table-limiting portion of the WHERE clause in all following examples.

Our example isn't incredibly useful so far; all we have are table names. Perhaps we can add a little more meat to it:

 
SELECT tabname, nrows * rowsize, npused  
  FROM systables; 
			

This will show us how much space our table is using, both in logical terms (number of rows * the row size; approximate for variable-sized rows) and in physical terms (number of pages used). Be sure to UPDATE STATISTICS before running this query, as this latter command will update these values within this table. Allocated space should be pulled from the sysmaster database, which has the space allocated for all tables. Example:

 
SELECT dbsname, tabname, sum(size) total_size 
  FROM sysextents 
 GROUP by dbsname, tabname 
 ORDER BY total_size desc; 
			

In the stores database the items table will show one page used; the sysmaster query may show 8 pages allocated. The number of allocated pages for this table will depend on Informix page size and the parameters EXTENT SIZE and NEXT SIZE given in the CREATE TABLE statement.

If we add tabtype to our list of selected tables we might find some values in there that reflect synonyms (tabtype='S'), Views ('V') or even Private synonyms ('P'). We can find out more about these tables by querying the syssyntable and sysviews tables (if we have a 4.0 or earlier engine, we will want to query syssynonyms - now obsolete, but still present). For example, to list all of the synonyms within our database we might try:

 
SELECT a.tabname "local_tab", b.server, b.dbname, b.owner, b.tabname "remote_tab" 
  FROM systables a, syssyntable b 
 WHERE a.tabid = b.tabid; 
			

This can be useful to indicate what tables in our database are linked to other databases and instances. The first tabname ("local_tab") is the name of the local table, the second tabname ("remote_tab" from syssyntable) is the name of the table to which this synonym refers. Server and dbname will be blank if the base table is on the same instance or database.

Back to top

Columns

Column names are still not terribly useful when it comes time for the data dictionary. We really want to know the datatypes of the columns within the table. (Note, we could just use the dbschema command which will print the DDL, but at times we may wish to format this information differently or just want to access the information directly from the catalogs). Fortunately there is another table, which identifies each column that belongs to the table - this is syscolumns; it is joined to systables by a foreign key known as tabid (the primary key for systables).

 
SELECT tabname, colname  
  FROM systables a, syscolumns b 
 WHERE a.tabid = b.tabid; 

Now we can see a breakout of each table and the columns that belong to that table. Odds are that they were read from the system catalogs in the order of their appearance within the table, but that is not guaranteed -- so let's make sure we get them in the order that they actually occur within the table.

 
SELECT tabname, colno, colname  
  FROM systables a, syscolumns b 
 WHERE a.tabid = b.tabid 
 ORDER BY colno; 

It would be better, though, if we could tell what the datatype of each column was and its length.

 
SELECT tabname, colno, colname, coltype, collength 
  FROM systables a, syscolumns b 
 WHERE a.tabid = b.tabid 
 ORDER BY colno; 

For the items table from the stores_demo database this would yield:

Ugh. Coltype is a number, and at times collength is impossibly large. A quick check into the reference manual gives us a list of the possible values here and their meaning. A much longer look also yields us some formulas to compute the real length and datatype precision based on the contents of the collength value. Rather than walk through all of that here I recommend a utility called dbdiff2 at www.iiug.org: it has three 4GL functions (col_cnvrt, fix_nm, and fix_dt) which take these two values as input and return the proper DDL defining the column. These functions, along with their awk equivalents, are included in the appendix of this article.

Note that the column types supported by these utilities are current for the 7.x and 8.x engines. For the data type extensions of 9.x, look to sqltypes.h for the most current list of datatypes.

Back to top

Indexes

We might want to look at indexes as well. The sysindexes table lists all of these; however, figuring out how to tie an index back to columns can be a royal pain. The part1 column in this table is the column number of the first column in the index. Alas, it does not stop there: an index can span 16 columns (8 with SE), so we wind up with columns part1 - part16. To decipher this properly we need a procedural language with the ability to connect to the database. This, I would vote as more trouble than it's worth for casual use. For an example of pulling out the index information take a look at dbdiff2 or analyse_idx (also available at www.iiug.org).

For the purposes of this discussion, we will use only the head (first column) of the index. Here is a query to return the basic information about an index

 
SELECT a.tabname, b.colname, c.idxname 
  FROM systables a, syscolumns b, sysindexes c 
 WHERE a.tabid = b.tabid 
   AND a.tabid = c.tabid 
   AND b.colno = c.part1; 

Sysindexes also has some valuable information on the index itself - its depth, number of leaves, unique values, and whether it's clustered or not. The first three of these are collected via statistics, so be sure to 'UPDATE STATISTICS;' before running the following query.

 
SELECT a.tabname, b.colname, c.idxname, c.idxtype, 
       c.clustered, c.levels, c.leaves, c.nunique 
  FROM systables a, syscolumns b, sysindexes c 
 WHERE a.tabid = b.tabid 
   AND a.tabid = c.tabid 
   AND b.colno = c.part1 

• Idxtype indicates whether the index is a 'U'nique or 'D'uplicate index. It can reflect other values under XPS to match the wider choice of indexes available in that engine.
• Clustered indicates whether it is a clustered index or not ('C' for clustered)
• Levels indicate how deep the btree is. A 2 level index indicates a single root/branch page and 1 or more leaf pages - which will be reflected in the 'leaves' column.
• Nunique is the number of unique values in the first column. Obviously for a unique single column index this should be the same as the rowcount for the table - in fact, let's add that rowcount into our query so that we can see exactly how unique our single column indexes are:

 
UPDATE STATISTICS; 
SELECT a.tabname, b.colname, c.idxname, c.idxtype, 
       c.clustered, c.levels, c.leaves, c.nunique,  
       nrows, nunique/nrows Percent_Unique 
  FROM systables a, syscolumns b, sysindexes c 
 WHERE a.tabid = b.tabid 
   AND a.tabid = c.tabid 
   AND b.colno = c.part1 
   AND part2 = 0; 
			

(The part2=0 was added to restrict the query to indexes built on single columns)

Of course this will fail when we have tables with no rows in them as we aren't allowed to divide by zero for some strange reason, but for the remainder it will indicate just how unique our index is. Perhaps we should modify the query and add:

 
AND nrows > 0			 

Some of the things to watch for with indexes are those which are highly duplicative and those which are very small. If the entire table is small and fits on a single page, it is cheaper to read the page and scan through it for our value than it is to read an index page and then read the data page for the value. Also a highly duplicative index means that we have to perform a number of subsequent reads to get data pages. (e.g. a Percent_Unique rate of .125 means that there are on average 8 values for every index entry, this means potentially 9 reads every time we use that index!).

Back to top

Defaults

The sysdefaults catalog holds default values for any given column in the database should they exist:

 
SELECT a.tabname, b.colname, c.type, c.default 
  FROM systables a, syscolumns b, sysdefaults c 
 WHERE a.tabid = b.tabid 
   AND a.tabid = c.tabid 
   AND b.colno = c.colno; 

This will give us the default value for each defaulted column within each table. There are some types which are special: 'U'ser, 'C'urrent, 'N'ull, 'T'oday, 'S'itename.

Back to top

Views

While it's easy enough to find views within the database (select tabname from systables where tabtype = 'V'), we might also care to get the view definition. Again this should be done with dbschema. The information is however available with:

 
SELECT viewtext, seqno  
  FROM sysviews  
 WHERE tabid = ?  
 ORDER BY seqno 

We might also care to see only what tables a view is based upon. These can be determined by using the sysdepend table:

 
SELECT a.tabname, b.tabname 
  FROM sysdepend c, systables a, systables b 
 WHERE btabid=a.tabid 
   AND dtabid=b.tabid 
   AND b.tabname = ?; 
			

This gives us the key information from the sysviews table without the need to parse it.

Back to top

Permissions

The permissions tables are a useful set of tables to pore through - it can show us who has permission to do what. A frequent issue in this regard is revocation of permissions - if two people have granted permission to a third user and one of them revokes that permission, the permission granted by the second user is still in effect.

Sysusers stores the basic user ids of those who have permissions other than 'public' within the system ('public' is also listed and controlled herein). Select * from sysusers will show us those users with 'D'ba authority 'R'esource authority and 'C'onnect authority. We grant or revoke these permissions as grant/revoke [to|from] user;

Sysroleauth Sysroleauth is a child of this table, since any user can also be a role. Sysroleauth describes the 'role' and who has been granted access to it (and then whether they can pass on that permission). So you can set up a basic permission scheme for a series of users through a role and then grant users access to the role, instead of setting up each user with permission for each table. Unfortunately this is not ANSI standard and some front end tools are confused about assuming a role.

Systabauth and its companion table syscolauth describe what permission has been granted to each user for each table (and potentially column) and whether that permission can be passed on.

 
SELECT tabname, grantor, grantee, tabauth 
  FROM systables a, systabauth b 
 WHERE a.tabid = b.tabid 
 ORDER BY tabname 

The tabauth string is a composite of 8 values:

• Select
• Update
• Column level permission exists (in which case we look at syscolauth)
• Insert
• Delete
• Index
• Alter
• References

If the value is in upper case, then the user in question (Joe) has permission to issue a grant statement to a third user (this is the result of a GRANT ... permission ... WITH GRANT OPTION;);). Joe can not grant select permission to John, since John granted Joe that permission. In this case, if the user Informix revokes SELECT permission from Joe, Joe will still be allowed to select from the table (foo), because John also gave him SELECT permission. The only way to fully revoke the select permission from Joe, in this case, is to either log in as John and revoke the permission or revoke the permission from John with CASCADE. With a more current release of 9.4 you can:

 
 REVOKE <privilege> ON <object> FROM <user> AS <grantor>				 

To view column permissions we would:

 
SELECT a.tabname, b.colname, c.grantor, c.grantee, c.colauth 
  FROM systables a, syscolumns b, outer syscolauth c 
 WHERE a.tabid = c.tabid 
   AND a.tabid = b.tabid 
   AND c.colno = b.colno 
 ORDER BY tabname 

Running this against the sysdistrib table in the stores_demo database we see:

Public has been granted the right only to select any column. No permission has been specified for the encdat column. Colauth might also have contained a 'u' in the second position for update privilege and/or an 'r' in the third position for references privilege.

Back to top

Stored procedures

Stored procedures are better extracted using dbschema. However, there is still some information of interest within the structures used to define the stored procedures.

The procedure itself is described in the sysprocedures table. The body (in text and compiled form) of the procedure is stored in the sysprocbody table.

The text of the stored procedure is thus available as:

 
SELECT data, seqno  
  FROM sysprocbody a, sysprocedures b 
 WHERE a.procid = b.procid 
   AND b.procname = ? 
   AND datakey = 'T' 
 ORDER BY seqno 

The optimization plan for the procedure is stored in sysprocplan. This plan is created when the procedure is created and updated whenever an update statistics for procedure <procedure> is executed. Since the content of tables a stored procedure uses may change over time, it is wise to periodically update this optimization plan. Within the sysprocplan table is a column called created, which indicates when the plan was last updated. An update statistics routine for procedures might start with selecting the names of procedures whose statistics should be updated:

 
SELECT procname  
  FROM sysprocedures a, sysprocplan b 
 WHERE a.procid = b.procid 
   AND created < today - 7; 

Sysprocauth maintains permissions for a procedure. This can be useful to determine who has access to a procedure. Remember that a procedure is run with the permissions of the user who created the procedure.

Back to top

Referential integrity and other linkages

One of the issues with referential integrity is that we may need to drop or disable a foreign key constraint before reorganizing one of the tables in a relationship. The depend.sh script in the appendix will help us with this.

The sysconstraints table has a record for every constraint in the database. Each of these has different types (constrtype):

C Check P Primary key R Reference (Foreign Key) U Unique N Not Null

These constraints in turn can tie the source and referring tables together (for referential keys) through the sysreferences table.

 
SELECT a.tabname, constrname, d.tabname 
  FROM systables a, sysconstraints b, sysreferences c, 
       systables d 
 WHERE b.constrtype = 'R' 
   AND a.tabid = b.tabid 
   AND b.constrid = c.constrid 
   AND c.ptabid = d.tabid 
   AND a.tabname = ?; 
			

This will show the first table, along with the referring constraint and the table to which reference is made.

We might also want to attach the object state to the above query so that we can determine if a constraint is disabled. The sysobjstate table will help us out here. It tracks constraints, triggers and indexes by name. To extract that information we could change our query to:

 
SELECT a.tabname, constrname, d.tabname, e.state 
  FROM systables a, sysconstraints b, sysreferences c, 
       systables d, sysobjstate e 
 WHERE b.constrtype = 'R' 
   AND a.tabid = b.tabid 
   AND b.constrid = c.constrid 
   AND b.constrname = e.name 
   AND c.ptabid = d.tabid 
   AND a.tabname = ?; 

Also of interest is the syschecks table, which identifies check constraints. It has a matching table, syscoldepend which identifies the dependent table and column. A useful query here would be:

 
SELECT a.constrname, a.constrtype, b.tabname, c.colname, 
       d.checktext 
  FROM sysconstraints a, systables b, syscolumns c,  
       syschecks d, syscoldepend e 
 WHERE a.constrid = d.constrid 
   AND a.constrid = e.constrid 
   AND e.tabid = b.tabid 
   AND e.colno = c.colno 
   AND b.tabid = c.tabid 
   AND d.type = 'C' 

From our friendly stores database this yields:

This indicates that there is check constraint (quantity >=1) on the quantity column in the items table. The constraint name can be a handy thing to have around.

Back to top

Conclusion

The System catalogs are a useful adjunct not only to the metadata needs of a data warehouse or a reverse-engineering requirement, but also in day to day maintenance of the database. There are several other system catalogs as well as the sysutil and sysmaster databases which are properly the subject of another article . The number and content of these catalogs and databases have grown with the product, although the original catalogs are still there. As we determine needs for metadata within the database, we should check the catalogs - what we need may already be there.

Back to top

Appendix

The code discussed in this article can also be found electronically at:

It is reproduced in this appendix to provide further examples.

Listing 1. 4gl functions

 
############################################################# 
# 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] # basic datatype 
 
   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,0) 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,0) 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,1) 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,tp) 
 
DEFINE num integer, 
       tp smallint, 
       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 "<<&" 
   IF tp = 0 THEN 
      IF j > i THEN 
         LET strg = strg1 clipped 
      ELSE 
         LET strg = strg1 clipped, ", ", strg2 clipped 
      END IF 
   ELSE		# varchar is just the opposite 
      IF i = 0 THEN 
         LET strg = strg2 clipped 
      ELSE 
         LET strg = strg2 clipped, ", ", strg1 clipped 
      END IF 
   END IF 
 
   RETURN strg 
 
END FUNCTION 
############################################################## 
# Turn collength into meaningful date info - return as string 
############################################################## 
FUNCTION fix_dt(num) 
DEFINE num integer, 
       i, j, k, len   SMALLINT, 
       strg CHAR(30) 
 
   LET i   = (num mod 16) + 1		# offset again 
   LET j   = ((num mod 256) / 16) + 1	# offset again 
   LET k   = num / 256			# length of value 
 
# If this is an interval then life gets interesting, 'k' is  
# the length of the entire string.  So a YEAR TO DAY is  
# YYYYMMDD or 8.  A DAY(3) TO MINUTE is DDDHHMM or 7.   
# We don't know how long the first one is, but  
# we can work it out by computing the 'should  
# be length' of the string and then adding/subtracting  
# the result from the 'should be length' of  
# the major element. 
# 
# Keep in mind --->    YYYYMMDDHHMMSSFFFFF 
#     vs.         j =    1  2 3 4 5 678901 
# 
# I was just working an algorithm to do this, 4  
# notepads, 90 minutes, and 50 lines into it I realized 
# that I was creating something impossible to test or  
# maintain.  Therefore I am opting for something a lot simpler. 
# 
# In the globals I have created an ARRAY of RECORD with  
# start and end points for the major and minor pieces.   
# By subtracting the START point of the  
# major element from the END point of the minor element 
#  I get the 'should be length' 
# 
 
LET len = intvray[i].end_point - intvray[j].start_point 
 
# len should match k.	e.g.: 
#				DAY(5) TO MINUTE  ==> k = 9, len = 6 
#				YEAR(6) TO HOUR   ==> k = 12, len = 14 
 
   LET len = k - len 		   # add len to the major 
 
   IF len = 0 OR j > 11 THEN   # is the default 
			# 12 on have the precision already coded 
 
    LET strg = datetype[j] clipped, " TO ", datetype[i] clipped 
 
   ELSE				# isn't the default 
 
				# uh-oh, how long IS the default major? 
   LET k = intvray[j].end_point - intvray[j].start_point  
 
				# add in the extra 
   LET k = k + len 
 
   LET strg = datetype[j] clipped, "(", k using "<<", ")", " TO ",  
                 datetype[i] clipped 
    
   END IF 
 
   RETURN strg 
 
END FUNCTION 
############################################# 
Required global definitions: 
   DEFINE  datatype ARRAY[40] OF CHAR(20), # for coltype conversions 
           datetype ARRAY[16] OF CHAR(11),  
           intvray  ARRAY[16] OF RECORD 
              start_point SMALLINT, 
              end_point SMALLINT 
           END RECORD 
 
   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] = "NCHAR"		 
   LET datatype[17] = "NVARCHAR" 
   LET datatype[18] = "UNKNOWN"		# little room for growth 
   LET datatype[19] = "UNKNOWN" 
   LET datatype[20] = "UNKNOWN" 
 
   LET datetype[1] = "YEAR" 
   LET intvray[1].start_point = 1 
   LET intvray[1].end_point = 5		# offset by one for easy math 
 
   LET datetype[3] = "MONTH" 
   LET intvray[3].start_point = 5 
   LET intvray[3].end_point = 7		 
 
   LET datetype[5] = "DAY" 
   LET intvray[5].start_point = 7 
   LET intvray[5].end_point = 9		 
 
   LET datetype[7] = "HOUR" 
   LET intvray[7].start_point = 9 
   LET intvray[7].end_point = 11		 
 
   LET datetype[9] = "MINUTE" 
   LET intvray[9].start_point = 11 
   LET intvray[9].end_point = 13		 
 
   LET datetype[11] = "SECOND" 
   LET intvray[11].start_point = 13 
   LET intvray[11].end_point = 15		 
 
   LET datetype[12] = "FRACTION(1)" 
   LET intvray[12].start_point = 15 
   LET intvray[12].end_point = 16 
 
   LET datetype[13] = "FRACTION(2)" 
   LET intvray[13].start_point = 16 
   LET intvray[13].end_point = 17		 
 
   LET datetype[14] = "FRACTION(3)" 
   LET intvray[14].start_point = 17 
   LET intvray[14].end_point = 18		 
 
   LET datetype[15] = "FRACTION(4)" 
   LET intvray[15].start_point = 18 
   LET intvray[15].end_point = 19		 
 
   LET datetype[16] = "FRACTION(5)" 
   LET intvray[16].start_point = 19 
   LET intvray[16].end_point = 20		 

Listing 2. awk equivalent

 
#!/bin/ksh 
########################################################### 
# 
# findcol database column 
# 
# returns any table or column within 'database' that has some 
# portion of 'column' within it's name 
# 
# Jack Parker 2002 
# 
########################################################### 
echo " 
select tabname[1,25], colname[1,25], coltype, collength 
  from systables a, syscolumns b 
 where a.tabid = b.tabid 
   and (colname matches \"*$2*\" or tabname matches \"*$2*\"); 
" | dbaccess $1 - 2>/dev/null | tail +4l | grep -v ^$ | awk ' 
BEGIN { 
dtp[0]="char" 
dtp[1]="smallint" 
dtp[2]="integer" 
dtp[3]="float" 
dtp[4]="smallfloat" 
dtp[5]="decimal" 
dtp[6]="serial" 
dtp[7]="date" 
dtp[8]="money" 
dtp[9]="unknown" 
dtp[10]="datetime" 
dtp[11]="byte" 
dtp[12]="text" 
dtp[13]="varchar" 
dtp[14]="interval" 
dtp[15]="nchar" 
dtp[16]="nvarchar" 
dtp[17]="unk" 
dtp[18]="unk" 
dtp[19]="unk" 
dtp[20]="unk" 
 
datp[1] = "year" 
int_start[1]=1 
int_end[1]=5 
datp[3] = "month" 
int_start[3]=5 
int_end[3]=7 
datp[5] = "day" 
int_start[5]=7 
int_end[5]=9 
datp[7] = "hour" 
int_start[7]=9 
int_end[7]=11 
datp[9] = "minute" 
int_start[9]=11 
int_end[9]=13 
datp[11] = "second" 
int_start[11]=13 
int_end[11]=15 
datp[12] = "fraction(1)" 
int_start[12]=15 
int_end[12]=16 
datp[13] = "fraction(2)" 
int_start[13]=16 
int_end[13]=17 
datp[14] = "fraction(3)" 
int_start[14]=17 
int_end[14]=18 
datp[15] = "fraction(4)" 
int_start[15]=18 
int_end[15]=19 
datp[16] = "fraction(5)" 
int_start[16]=19 
int_end[16]=20 
} 
function fixnm(coll,tp) 
{ 
   i = int(coll / 256) 
   j = coll % 256 
   if (tp == 0) { 
	if (j > i ) strg=i 
	else strg = sprintf("%s,%s",i,j) 
	} 
   else { 
	if (i == 0) strg=j 
	else strg = sprintf("%s,%s",j,i) 
	} 
   return strg 
} 
function fixdt(coll) 
{ 
   i = coll % 16 + 1 
   j = int((coll % 256) / 16 ) + 1 
   k = int(coll / 256) 
   ln = int_end[i] - int_start[j] 
   ln = k - ln 
   if (ln == 0 || j > 11) { strg = sprintf("%s to %s", datp[j], datp[i]) } 
   else { 
	k int_end[j] - int_start[j] 
	k = k + ln 
	strg = sprintf("%s (%d) to %s", datp[j], k, datp[i]) 
	} 
   return strg 
} 
{ 
# Tab, colname, type, length 
outstrg="" 
tabname=$1 
colname=$2 
nonull=$3/256 
coltype=$3%256 
collength=$4 
outstrg=dtp[coltype] 
if (coltype == 0) {outstrg=sprintf("%s(%s)",outstrg, collength)} 
if (coltype == 5 || coltype == 8 ) { 
		outstrg=sprintf("%s(%s)",outstrg,fixnm(collength,0)) } 
if (coltype == 13 ) {outstrg=sprintf("%s(%s)",outstrg,fixnm(collength,1)) } 
if (coltype == 10 || coltype == 14 ) { 
		outstrg=sprintf("%s(%s)",outstrg,fixdt(collength)) } 
printf("%-20s %-20s %-25s\n", tabname, colname, outstrg) 
}'  

Listing 3. depend.sh

 
#!/bin/ksh 
############################################################### 
# 
# Quick script thrown together to find dependencies on  
# a table. 
# 
# Usage: 
#  depend.sh database table 
# 
# This will list all tables which refer to this table either  
# by view or by foreign key.  It will also list any tables  
# that this table depends on.  It will recurse through a  
# dependency chain. 
# 
# Jack Parker 2003 
# 
############################################################### 
 
find_par_view() 
{ 
dbaccess $1 - 2>/dev/null <<EOF 
output to pipe "cat" without headings 
select trim(b.tabname) 
from sysdepend c, systables a, systables b 
where btabid=a.tabid 
  and dtabid=b.tabid 
  and b.tabname = "$2"; 
EOF 
} 
 
find_child_view() 
{ 
dbaccess $1 - 2>/dev/null <<EOF 
output to pipe "cat" without headings 
select trim(b.tabname) 
from sysdepend c, systables a, systables b 
where btabid=a.tabid 
  and dtabid=b.tabid 
  and a.tabname = "$2"; 
EOF 
} 
 
find_par_fk() 
{ 
dbaccess $1 - 2>/dev/null <<EOF 
output to pipe "cat" without headings 
select trim(d.tabname) || ' ' || trim(constrname) || ' ' || state 
from systables a, sysconstraints b, sysreferences c, systables d,  
  sysobjstate e 
where constrtype='R' 
  and a.tabid=b.tabid 
  and b.constrid=c.constrid 
  and b.constrname=e.name 
  and c.ptabid=d.tabid 
  and a.tabname = "$2"; 
EOF 
} 
 
find_child_fk() 
{ 
dbaccess $1 - 2>/dev/null <<EOF 
output to pipe "cat" without headings 
select trim(d.tabname) || ' ' || trim(constrname) || ' ' || state 
from systables a, sysconstraints b, sysreferences c, systables d,  
sysobjstate e 
where constrtype='R' 
  and a.tabid=b.tabid 
  and b.constrid=c.constrid 
  and b.constrname=e.name 
  and c.ptabid=d.tabid 
  and d.tabname = "$2"; 
EOF 
} 
 
rec_pfk() 
{ 
idnt="$idnt-" 
find_par_fk $1 $2 | grep -v "^$" | 
while read junk 
do 
        echo $idnt "Parent of FK: $junk" 
        tab=`echo $junk | cut -d" " -f1 ` 
        rec_pfk $1 $tab 
done 
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'` 
} 
 
rec_cfk() 
{ 
idnt="$idnt-" 
find_child_fk $1 $2 | grep -v "^$" | 
while read junk 
do 
        echo $idnt "Child of FK: $junk" 
        tab=`echo $junk | cut -d" " -f1 ` 
        rec_cfk $1 $tab 
done 
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'` 
} 
 
rec_pvw() 
{ 
idnt="$idnt-" 
find_par_view $1 $2 | grep -v "^$" | 
while read tab 
do 
        echo $idnt "Parent View : $tab" 
        rec_cfk $1 $tab 
done 
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'` 
} 
 
rec_cvw() 
{ 
idnt="$idnt-" 
find_child_view $1 $2 | grep -v "^$" | 
while read tab 
do 
        echo $idnt "Child of View : $tab" 
        rec_cfk $1 $tab 
done 
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'` 
} 
 
echo " 
 
Database: $1 
" 
idnt="" 
echo "Searching for tables that ($2) has an FK to:" 
rec_pfk $1 $2 
idnt="" 
echo "Searching for tables that refer to ($2) with an FK:" 
rec_cfk $1 $2 
idnt="" 
echo "Searching for Parent tables for potential view ($2):" 
rec_pvw $1 $2 
idnt="" 
echo "Searching for Child tables for table ($2):" 
rec_cvw $1 $2 

Back to top

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Tables
• Columns
• Indexes
• Defaults
• Views
• Permissions
• Stored procedures
• Referential integrity and other linkages
• Conclusion
• Appendix
• Disclaimer
• About the author
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers