Re: Exporting and importing Informix data (long)
: "Paul E. Begley" <peb@scdipc0.ueci.com> -------------------------------
: I'll quote Paul's response verbatim:
: > You need third party tools (name escapes me) to establish and
: > retain extent information for your database.
I don't keep the old extents, but just recompute them using the output
of the tbcheck -pT command.
Here are the details and scripts that modify the dbexport SQL create
script:
---------------------------------------------------------------------------
Here are some scripts I worked up to create the EXTENT lines in the
database create scripts.
The first one, dbsize, reads the output of 'tbcheck -pT' and generates a
file containing a line with the table name, and another line with the
computed sizes and extents (i.e. + 20%). You will need to remove the
sys* tables at the top of the file, as they do not appear in the
database creation script. You run it as
dbsize tbcheck.out
The script dbadd reads the output of the first script and the database
create script and inserts the extents in the proper places. You run it
as:
dbadd sizefile son_db.sql
You could integrate the two scripts into one, but I think this is easier
to follow.
It saved me alot of time, and possible typing errors. It gives extents
to all files over 8 pages.
Also, I have gotten in the habit of testing the son_db.sql script before
I import by creating the database by hand and running dbaccess and
inserting the intire SQL script into the query workspace and running it
quickly to check for syntax errors. Saves alot of worry if I have made
edits by hand.
---------------------------------------------------------------------------
:
# dbsize
awk ' $1 == "TBLSpace" && $2 == "Report" {printf "%s ",$4}
$3 == "pages" && $4 == "used" {printf "%s\\n", $5} ' $* |\\
cut -d'.' -f2 |\\
awk ' $2 > 8 {printf "^create table.*\\\\\\\\.%s \\n", $1;
sz=$2 * 4 * 1.2;
new = sz * 0.2;
sz = int((sz + 7)/8)*8
new = int((new + 7)/8)*8
if (new <32)
new = 32;
printf "EXTENT SIZE %d NEXT SIZE %d\\n", sz, new}'
---------------------------------------------------------------------------
:
#dbadd
#set -x
trap "rm -f /tmp/$$" 0 1 2 3 15
while :
do
read TABLE || break
read EXTENT || break
awk ' BEGIN {found="N"; done="N"}
{ if (done != "Y" && $0 ~ /'"$TABLE "'/)
found="Y";
if (done != "Y" && found == "Y" && $1 == ");")
{
printf ")\\n%s;\\n","'"$EXTENT"'";
done = "Y";
}
else printf "%s\\n", $0;
}' $2 >/tmp/$$
mv /tmp/$$ $2
done <$1
---------------------------------------------------------------------------
--
Bruce Momjian | 830 Blythe Avenue
root@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)