You can do some unix script to it that will do the following:

1) cat myschema | grep -v "primary key ("  > somefile
2) mv somefile myschema
###that will get rid of the primary key statement from your schema file.

Then operate on the primary key statements in the 'somefile' by doing
someting like:
for line in `cat somefile`
do
 tab=`echo $line | awk '{print $5}' | awk -F\_ '{print $2}'`
 mykey=`echo $line | awk -F\( '{print $2}'| sed 's/)//'
 echo "alter table $tab add constraint primary key ( $mykey ) constraint
pk_${tab}" >> myschema
done

----- Original Message -----
From: "Mahdi Sbeih" <mahdi.sbeih@gmail.com>
Sent: Mon, July 14, 2008 4:24
Subject:Split table syntax and take out all constraints...

Hi All,

I have a dbschema output for a large database, and I want to create
all tables as RAW tables first in order to load data and then change
the type to STANDARD.

My problem is that I can't create a RAW table if the table syntax
contains constraints such as
create table mytable
 (
   my_key serial not null ,
   my_name varchar(64) not null ,
   desc1 varchar(32) not null ,
   desc2 varchar(64) not null ,
   primary key (my_key)  constraint pk_mytable
 );

Is there a way to split the above syntax into create and alters, so
the alters will be the constraints such as

create table mytable
 (
   my_key serial not null ,
   my_name varchar(64) not null ,
   desc1 varchar(32) not null ,
   desc2 varchar(64) not null
     );
alter table mytable add constraint primary key (my_key)  constraint
pk_mytable;

Any other ideas?

Thanks,
Mahdi
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

----- End of original message -----

We had to do the same thing recently because we changed platforms,
except that we used HPL which I recommend instead of using raw tables.
We still split things up so we could build our indexes in parallel. We
used myschema then I wrote a quick and dirty perl script. I can't
guarantee it but you are welcome to it. Caveat emptor as they say. One
other thing is that it renames constraints and indexes in the schema
to the way I like them. If you know perl then it would be
straightforward to remove this.

It splits the file into 4 files.

indexes
pks
fks
rest

You run rest first. It creates all of the tables, stored procedures,
triggers (which if you are loading raw you may want to create later),
privs, etc.
Next you run the index file to create the indexes. This may put all of
the indexes for a table on the same line so you can use a simple
script to run them in parallel, if not then I had another script that
did that. Next, run the PK file and then finally the FK file. It is
messy and the script isn't commented because it was a quick and dirty
thing. I just looked at the output again it doesn't compress the
lines. I split that functionality out to another script.

It also may run into an unforeseen issue that didn't occur in our data
set. I'll be leaving for France in a few hours so I won't be much help
for a week.

Here it is:
==== First line below =====
#!/usr/bin/perl -w

use strict ;

$::DEBUG=0;

unless ( (scalar @ARGV) == 1) {
 die "Need to pass in one file name." ;
}

print "$ARGV[0]\n" ;

my $BASE_NAME=$ARGV[0] ;
$BASE_NAME =~ s/\.[^.]*$//g ;

#split out indexes

unless ( open IDXS, "> ${BASE_NAME}_indexes.split.sql" ) {
 die "Couldn't create index file.\n" ;
}

unless ( open FKS, "> ${BASE_NAME}_constraints.split.sql" ) {
 die "Couldn't get Schema\n" ;
}

unless ( open REST, "> ${BASE_NAME}_rest.split.sql" ) {
 die "Couldn't create grant file.\n" ;
}

unless ( open PKS, "> ${BASE_NAME}_pks.split.sql" ) {
 die "Couldn't create grant file.\n" ;
}

my %htExists = () ;

my $index=0;
my $constraint=0;
my $create_procedure=0;
my $end_procedure=0;

my $print_constraint="";
my $print_index="";

my %fks = () ;
my %fk_names = () ;
my %indexes = () ;
my %pks = () ;

my $tab="";
my $line;

while (<>) {
 $line=$_;
 $line =~ s/cluster\s+index/index/sim ;
 #  index creation inside procedures goes with the procedures;
 if ($end_procedure) {
 }
 if ($create_procedure) {
 }
 elsif ( $line =~ /create\s+(unique\s+)?index/i ) {
   # found index
   $index=1;
   $line =~ /on ("informix"\.)?(\w+)\s+\(/i ;
   $tab=$2 ;
 }
 elsif ( $line =~ /alter\s+table\s+(\w+)/i ) {
   $constraint=1;
   $tab=$1;
 }
 elsif ( $line =~ /create procedure/i ) {
   $create_procedure=1;
 }

 if ($create_procedure) {
   if ($end_procedure and $line =~ /;/) {
     $create_procedure=0 ;
     $end_procedure=0 ;
   }
   elsif ( $line =~ /end procedure ;/i ) {
     $create_procedure=0 ;
   }
   elsif ( $line =~ /end procedure/i ) {
     $end_procedure=1 ;
   }
   print REST $line ;
 }
 elsif ($index) {
   $print_index.= $line ;
   if ( /;/ ) {
     print "DEBUG :$print_index:\n" if $::DEBUG ;
     #Get rid of all carriage returns.
     $print_index =~ s/\s+/ /sgm ;

     #take last CR that is now space and remove it
     $print_index =~ s/; /;/sgm ;
     print "DEBUG :$print_index:\n" if $::DEBUG ;

     push( @{$indexes{$tab}}, "$print_index") ;
     $htExists{$tab}=1;

     $index=0 ;
     $print_index = "" ;

   }
 }
 elsif ($constraint) {
   $print_constraint .= $line ;
   if ( /;/ ) {

     print "DEBUG :$print_constraint:\n" if $::DEBUG ;
     #Get rid of all carriage returns.
     $print_constraint =~ s/\s+/ /sgm ;

     #take last CR that is now space and remove it
     $print_constraint =~ s/; /;/sgm ;
     print "DEBUG :$print_constraint:\n" if $::DEBUG ;

     #Convert unique to primary when id .
     my $changed_to_pk=0 ;
     if ($print_constraint =~ /unique\s*\(\s*(${tab}_)?id\s*\)/i) {
       $print_constraint =~ s/unique/PRIMARY KEY/i ;
       $changed_to_pk=1 ;
     }
     if ($print_constraint =~ /primary key/i) {
       my $currpkname ;
       if ( $print_constraint =~ /constraint (\w+);/i ) {
         $currpkname=$1 ;
       }
       else {
         $currpkname="No Name";
       }
       if ( ! ( $currpkname =~ /${tab}_pk/ ) ) {
         my $newpkname=${tab} . "_pk" ;
         $print_constraint =~ s/(\sCONSTRAINT \w+)?;/ CONSTRAINT
$newpkname;/ ;
         $print_constraint .= " { renamed primary key $currpkname to
$newpkname }" ;
       }
       if ( $changed_to_pk ) {
         $print_constraint .= " { changed unique constraint to
primary key }" ;
       }

       push( @{$pks{$tab}}, "$print_constraint") ;
       $htExists{$tab}=1;
     }
     else {
       $print_constraint =~ /ADD CONSTRAINT ((UNIQUE)|(FOREIGN KEY)) \
(/ ;

       my $cons_type = ( $1 eq "FOREIGN KEY" ? "fk" : "un" ) ;

       my $currfkname ;
       if ( $print_constraint =~ /constraint (\w+);/i ) {
         $currfkname=$1 ;
       }
       else {
         $currfkname="No Name";
       }

       if ( ( ! ( $currfkname =~ /${tab}_${cons_type}\d+/ ) ) or
exists($fk_names{$currfkname}) ) {
         my $fknum=1;
         my $newfkname=${tab} . "_" . ${cons_type} . $fknum ;
         while ( exists($fk_names{$newfkname}) ) {
           ++$fknum ;
           $newfkname=${tab} . "_" . ${cons_type} . $fknum ;
         }
         $fk_names{$newfkname}=1 ;
         $print_constraint =~ s/(\sCONSTRAINT \w+)?;/ CONSTRAINT
$newfkname;/ ;
         $print_constraint .= " { renamed constraint $currfkname to
$newfkname }" ;
         $currfkname=$newfkname ;
       }

       $fk_names{$currfkname}=1;

       if ( ${cons_type} eq "fk" ) {
         push( @{$fks{$tab}}, "$print_constraint") ;
         $htExists{$tab}=1;
       }
       elsif ( ${cons_type} eq "un" ) {
         push( @{$pks{$tab}}, "$print_constraint") ;
         $htExists{$tab}=1;
       }
     }

     $constraint=0 ;
     $print_constraint = "" ;
   }

 }
 else {
   print REST $line ;
 }

}

my @order=sort keys %htExists ;

foreach my $currtab (@order) {
 if (exists($indexes{$currtab}) ) {
   print IDXS "{ $currtab } ", (join "\n{ $currtab } ",
@{$indexes{$currtab}}), "\n" ;
   delete($indexes{$currtab}) ;
 }
 else {
   print IDXS "{ $currtab } { Table: $currtab does not have indexes. }
\n" ;
 }
}

foreach my $currtab (@order) {
 if (exists($fks{$currtab}) ) {
   print FKS "{ $currtab } ", (join "\n{ $currtab } ",
@{$fks{$currtab}}), "\n" ;
   delete($fks{$currtab}) ;
 }
 else {
   print FKS "{ $currtab } { Table: $currtab does not have a foreign
key constraint. }\n" ;
 }
}

foreach my $currtab (@order) {
 if (exists($pks{$currtab}) ) {
   print PKS "{ $currtab } ", (join "\n{ $currtab } ",
@{$pks{$currtab}}), "\n" ;
   delete($pks{$currtab}) ;
 }
 else {
   print PKS "{ $currtab } { Table: $currtab does not have a primary
key or unique constraint. }\n" ;
 }
}

close IDXS;
close FKS;
close REST;
close PKS;

Follow the idxname over to sysindexes, part1 will identify the colno
of the table in question.

j.

I got this before but it doesn't tell me what column(s) these
constraints are on, basically it is not enough information to invoke
an alter command.

-Mahdi
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org <mailto:Informix-list@iiug.org>  
<mailto:Informix-list@iiug.org>
http://www.iiug.org/mailman/listinfo/informix-list
<http://www.iiug.org/mailman/listinfo/informix-list>  
<http://www.iiug.org/mailman/listinfo/informix-list>

Just get my dbschema replacement utility, myschema.  If you give it the
names of two output files it places all triggers, indexes and constraints
into the second file and the create table, create view, and UDT statements
into the first file.  No sweat.

Myschema is contained in the package utils2_ak available for download from
the IIUG Software Repository (www.iiug.org/software) or the Oninit web
site.  ( Recently uploaded an update to the file print_procedures.ec, and I
don't see that it has been posted yet, so if you have any trouble compiling
that file, let me know and I'll send you the update directly.)

Art