SEO

Is there something in Informix thats analagous to the truncate
function in MSSQL Server truncate?

Looking for a way to very quickly get rid of every row in a table. We
have been dropping and recreating on the fly, but that has our
programmers hard coding dbspaces into there apps which is probably not
best practice.

emebohw@netscape.net (sumGirl) wrote in message news:<a5e13cff.0408160807.6473960b@posting.google. com>...
> Is there something in Informix thats analagous to the truncate
> function in MSSQL Server truncate?
>
> Looking for a way to very quickly get rid of every row in a table. We
> have been dropping and recreating on the fly, but that has our
> programmers hard coding dbspaces into there apps which is probably not
> best practice.

A couple of ideas come to mind:

1)
drop all constraints
drop all indexes
alter table xxxx type(raw);
delete from <table> where 1 = 1;
alter table xxxx type(standard);
add back all indexes
add back all constraints

Easy to implement and the deletes are done nonlogging so they are much
faster than in logging mode but you still have to recreate all of the
indexes and constraints on a table.


2) use temp tables for this function. They get deleted automatically
when the connection is dropped. (with no log option puts the temp
table in the tempdbspace)

3) create stored procedures to drop and recreate each of these tables
with the dbspaces and things that you want. Easy to implement and when
you want to move the tables around you only have to change the stored
procedures in informix.

4) create library code using whatever programming language that you
are using to read the system catalogue of a passed in table and drop
and recreate it. The system catalogues have all of the information
that you need to recreate a table. This is very flexable. In the
iiug.org website are several examples of programs that read the system
tables. Even one that is a dbschema replacement that would access all
of the system tables that you need to recreate a table. This is harder
to implement than any of the others.