Re-create a Table with the Same Parameters as the Original

As mentioned above, the Informix dbschema program has some noted limitations. While it will create all of the SQL statements necessary to rebuild the table in its most basic form, some very valuable information is lost. This is the information regarding the extent sizes and the locking mode of the table.

As the DBA continues to tune the database, it becomes very important to keep the tables within the minimum number of extents. It is often necessary to adjust the extent sizes of a table to make sure that it uses its space most effectively and to make sure that its access speed is maximized. When rebuilding the table, much accumulated tuning work would be lost if the latest EXTENT SIZE and NEXT SIZE parameters were lost. The newschema script is designed to capture this otherwise lost information and to create a schema program that is truly useful.

Newschema is composed of two parts. The first is a report in the isql report-writer, ACE. The second part is a script that merges this information with the output of the OnLine dbschema program to provide the information needed.

The ACE report is called storage.ace. If you do not have the isql package containing ACE, the same report could be written in SQL.

database
admin <--(*NOTE* Put your own database name here)
end
define
param [1] which_table char(50)
variable mode char(4)
end
output
top margin 0
bottom margin 0
left margin 0
end
select
tabname,
owner,
locklevel,
fextsize,
nextsize
from systables where tabname = $which_table
end
format
on every row
if locklevel = "P" then
begin
let mode = "PAGE"
end
if locklevel = "R" then
begin
let mode = "ROW"
end
print "EXTENT SIZE ", fextsize using "######",1 space,"NEXT SIZE ",nextsize using "######",1
space,"LOCK MODE ", mode clipped
end

The above ACE program is compiled by using the isql Forms Compile option or by using the appropriate command line options to compile the program. The output program will be called storage.arc.

The following C-Shell script needs to be placed in the DBA's path. I suggest creating a $INFORMIXDIR/local_bin directory and placing all of the included DBA scripts and programs in it. Set the $INFORMIXLOCALBIN = XXXXXXXX environmental variable to point to the location of this local directory. If you place all of your site-specific scripts in a local bin directory, this directory will be preserved with subsequent Informix installs and upgrades. If you have put your scripts in the $INFORMIXDIR/bin directory, you will someday need to decide which are your "local" scripts and which are the "real" Informix distribution programs. You might as well do it right the first time.

The actual script that makes use of the storage.arc report is newschema.

#!/bin/csh
#newschema
set INFORMIXLOCALBIN = $INFORMIXDIR/local_bin
$INFORMIXDIR/bin/dbschema -d $1 -t $2 /tmp/tempschema$$
set storage_option = '$INFORMIXDIR/bin/sacego -s -d $1 $INFORMIXLOCALBIN/storage $2'
cat /tmp/tempschema$$ | sed "s/ );/ ) $storage_option ;/" > /tmp/tempschema2$$
cat /tmp/tempschema2$$ >> $3
rm /tmp/tempschema$$ /tmp/tempschema2$$

The newschema program is similar in concept to the Informix dbschema program with the exception that it works for one table at a time. Invocation is as follows:

newschema   database_name     table_name     sdestination_file_name

This script will append its schema to the destination_file_name, which makes it usable in creating schemas for multiple tables. This is done by using one of my most-often-used UNIX tricks, taking input from a file. Suppose you wanted to do a newschema for all tables in database admin. The first job would be to create a UNIX file of the tables in the subject database. The SQL commands for this would be:

DATABASE admin;
SELECT tabname FROM systables WHERE tabid > 99;

These SQL statements take advantage of the fact that all of the internal tables that OnLine creates for a database will have a tabid in the systables system table of less than 99. Anything greater than 99 must be a user table. Place the output of this script into a file called targets (or whatever else you want to name it). This can be by using the isql query language and using the Query-Language menu option followed by the Output option to place the output in a new file, target. If you are using the SELECT alias under C-Shell, it would be nice if you could simply type from the UNIX prompt:

SELECT tabname FROM systables WHERE tabid > 99 > target

Unfortunately, the shell will not understand this construct in conjunction with an alias. If you are using C-Shell with the history mechanism, you can get around it by using the following series of commands:

SELECT tabname FROM systables WHERE tabid > 99

You then get an output to your screen. Then you type

!! > target

The ! ! (bang bang) command repeats the last command and puts the output in target. The next step is to clean out everything but the tablenames from the file target. This is easily done using a word processor. If you're after a system that can be run without human intervention or if you simply want to make the script more elegant, you could populate the target file by using an ACE report instead of isql. The report writer gives you a lot more flexibility in formatting and layout than isql. For this case, isql works fine, because we're just doing this to show you the techniques.

Okay, you now have a file called target that contains only the names of the tables you want to include in your schema. Now comes time for the UNIX wizardry that makes the job work. You want to tell UNIX to run newschema on every table in target and put the output in the file called mk_admin.sql. Write a C-Shell script called newschema_alldb that looks like this:

#!/bin/csh
foreach table ('cat target')
$INFORMIXLOCALBIN/newschema  admin $table mk_admin.sql
end

When you run this script, it does a newschema on each table in the target file and appends it to mk_admin.sql. You can then re-create the database by running the SQL script from within isql. This method of massaging an output file and then running multiple jobs against it is a most handy tool, especially for all of those "quick and dirty" jobs that you want to do. The same type of approach can be taken with either the Bourne or Korn shells. However, I'm a C-Shell bigot, so I'll leave it to you to translate the scripts if you care to use any of the lesser shells.