Re: DBEXPORT question
Kerry
Here is a shell script that you may find useful. It can be used to edit the
sql files
generated by dbexport to add extent, lock and dbspace names to the create
table
statements.
It uses a control file (see example at end of mail) to determine what values
to
insert for a given file. If the program runs on your unix system, type
edschema.sh -h
to get help on using the command.
Currently the script may well get tripped up by constraint definitions in the
table.
It does not allow you to choose the location of indexes or the dbspace that
you
want the db to be created in (yet).
It does handle choosing the dbspaces for text and byte fields.
This version does not handle schemas that already contain dbspace, lock and
extent information (I do have one somewhere that does).
Hope you find some use for it, even if it is not really useful for dealing
with your
current problem.
Mark
MarkDenham@aol.com (home)
Denham.M@amstr.com (work)
---------------Clip here for edschema.sh----------------------------------
#!/bin/ksh
# This file has been copied here TEMPORARILY for immediate use for Cheyenne
# upgrade 08/16/96. It will be replaced by editor in INSTALL/bin in future.
#
progname=$0
errorexit() {
echo "USAGE: $progname [-c <controlfile> -s <schemafile>] | -h"
exit 1
}
helpmessage() {
echo "USAGE: $progname [ -c <controlfile> -s <schemafile> ] | -h\\n"
echo "WHERE:\\n"
echo "controlfile - Full pathname of file containing extent size and"
echo " lock level information for tables that are to"
echo " have different values from INFORMIX DEFAULT values"
echo "schemafile - Schema file created by DBEXPORT/DBSCHEMA that needs"
echo " modifying prior to installation."
exit 0
}
readopts() {
while getopts c:s:h option $*
do
case $option in
c)
controlfile=$OPTARG
;;
s)
schemafile=$OPTARG
;;
h)
helpmessage
;;
?)
errorexit
;;
esac
done
# Check program is being used correctly
shift $(($OPTIND - 1))
if [ ! -z "$*" -o -z "$controlfile" -o -z "$schemafile" ]
then
errorexit
fi
if [ ! -r $controlfile ]
then
echo "$progname: Controlfile does not exist or is not readable!"
fi
if [ ! -r $schemafile ]
then
echo "$progname: Schemafile does not exist or is not readable!"
fi
}
readopts $@
# Ignore shell comments in input files
schemaoutput=`basename $schemafile .sql2`.sql
awk '
BEGIN { search=0 }
/^#/ {
continue
}
# Ignore blank lines in control file
$1 == "" && FILENAME != schemafile {
continue
}
# read tablename from control file
/^[a-zA-Z][a-zA-Z0-9_]*:$/ {
ctabname=substr($1, 1, index($1, ":")-1)
ctabname=toupper(ctabname)
continue
}
# Has a dbspace been specified
/[ ]*dbspace/ {
dbspace[ctabname]=$2
continue
}
# Has a first extent size been specified
/[ ]*first extent/ {
firstext[ctabname]=$3
continue
}
# Has a next extent size been specified
/[ ]*next extent/ {
nextext[ctabname]=$3
continue
}
# Has the lock mode been specified
/[ ]*lock mode/ {
lockmode[ctabname]=$3
continue
}
# Has the text blobspace been specified
/[ ]*text space/ {
textspace[ctabname]=$3
continue
}
# Has the byte blobspace been specified
/[ ]*byte space/ {
bytespace[ctabname]=$3
continue
}
# Read schema and find out table name
/create table/ || /CREATE TABLE/ {
stabname=substr($3,index($3,".")+1, length($3))
sub("\\\\(", " ", stabname)
stabname=toupper(stabname)
search=1
}
search == 1 {
# See if a byte field is being defined
bytepos=match($0, "[ ][Bb][Yy][Tt][Ee]([ ,\\)]|$)")
if( bytepos > 0 && bytespace[stabname] != "" ) {
# Save the rest of the string, beyond the BYTE keyword.
endstr=sprintf(" BYTE IN %s%s", bytespace[stabname],
substr($0, bytepos+5))
sub("[ ][Bb][Yy][Tt][Ee]([ ,\\)].*|$)", endstr)
}
# See if a text field is being defined
textpos=match($0, "[ ][Tt][Ee][Xx][Tt]([ ,\\)]|$)")
if( textpos > 0 && textspace[stabname] != "" ) {
# Save the rest of the string, beyond the TEXT keyword.
endstr=sprintf(" TEXT IN %s%s", textspace[stabname],
substr($0, textpos+5))
# Blank out TEXT and everything upto the end of the current line
sub("[ ][Tt][Ee][Xx][Tt]([ ,\\)].*|$)", endstr)
}
}
# Look for the end of the table definition
/\\);/ && search == 1 {
# Write the information needed to define the dbspace, extent and
# lock mode for this table.
sub(";", "") # Remove the end of table definition
print $0
if( dbspace[stabname] != "" ) {
printf " in %s\\n", dbspace[stabname]
}
if( firstext[stabname] != "" ) {
printf " extent size %s\\n", firstext[stabname]
}
if( nextext[stabname] != "" ) {
printf " next size %s\\n", nextext[stabname]
}
if( lockmode[stabname] != "" ) {
printf " lock mode %s\\n", lockmode[stabname]
}
print ";\\n"
# Reset the flags
search=0
continue
}
{ print $0 }
' schemafile=$schemafile $controlfile $schemafile >$schemaoutput
####### Uncomment for editing dbexport sql scripts #########
#if [ $? = 0 ]
#then
# cp $schemafile.t $schemaoutput
#fi
-----------------------Clip here for Control
file------------------------------
#
# This file defines the amount of space that will be occupied by the table
# and the location of the byte/text field data.
#
# Entries in this file must follow the following syntax ([] indicate option):
#
# [<table_name>:
# [ dbspace <spacename>]
# [ (byte|text) <spacename>]
# [ lock mode (page|row)]
# [ first extent <size_in_k>]
# [ next extent <size_in_k>]]
#
# An example entry is:
#
#YOUR_TABLE:
# dbspace sirscsdbs
# lock mode row
# first extent 155648
# next extent 9728
ORDERS:
dbspace main_dbs
byte space blob_dbs
text space text_dbs
first extent 12345
next extent 67890
lock mode row
----------------------------------------End-----------------------------------
---