Re: anything like MS-SQL

#1 (**permalink**) 04-20-2008, 07:27 AM

--=====================_763011863==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed

What I do to avoid having to hard-code details is

- unload table
- run a myschema (from one of Art Kagel's IIUG package) on the table
- split sql into table create and index/constraint creates
- run create table sql
- alter table to raw mode
- load table
- alter table to standard mode
- run index and constraint creates

Mike

At 06:51 AM 8/17/04, Jean Sagi wrote:

>Personally, I don't think "truncate" as a best practice... but:
>
>1. As trucate is a delete without logging, why don't you consider using
>raw tables (ie no loggin), but you have to have the drawbacks of raw tables.
>
>2. Drop the table, and then recreate it... you will need to have the
>schema first.
>
>4. No other alternative comes to me right now, but why don't you propose
>to IBM-Informix a "truncate" function. Online 5 practically hasn't any
>usefull function... but some good ones where incorporated in IDS.
>
>Chucho!
>
>
>-----Original Message-----
>From: emebohw@netscape.net (sumGirl)
>To: informix-list@iiug.org
>Date: 16 Aug 2004 09:07:30 -0700
>Subject: anything like MS-SQL "truncate" function in Informix?
>
>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.
>
>
>Jean Sagi
>jeansagi@myrealbox.com
>jeansagi@yahoo.com
>
>sending to informix-list

----------------------------------------------
Michael Dunham-Wilkie, M.Sc., M.P.A.
Senior Database Analyst
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: <http://www.barrodale.com>http://www.barrodale.com
Email: mike@barrodale.com
----------------------------------------------
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2

Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
----------------------------------------------
--=====================_763011863==.ALT
Content-Type: text/html; charset="us-ascii"

<html>
<body>
What I do to avoid having to hard-code details is 
- unload table 
- run a myschema (from one of Art Kagel's IIUG package) on the
table 
- split sql into table create and index/constraint creates 
- run create table sql 
- alter table to raw mode 
- load table 
- alter table to standard mode 
- run index and constraint creates 
Mike 
At 06:51 AM 8/17/04, Jean Sagi wrote: 
 
<blockquote type=cite class=cite cite="">Personally, I don't think
"truncate" as a best practice... but: 
1. As trucate is a delete without logging, why don't you consider using
raw tables (ie no loggin), but you have to have the drawbacks of raw
tables. 
2. Drop the table, and then recreate it... you will need to have the
schema first. 
4. No other alternative comes to me right now, but why don't you propose
to IBM-Informix a "truncate" function. Online 5 practically
hasn't any usefull function... but some good ones where incorporated in
IDS. 
Chucho! 
 
-----Original Message----- 
From: emebohw@netscape.net (sumGirl) 
To: informix-list@iiug.org 
Date: 16 Aug 2004 09:07:30 -0700 
Subject: anything like MS-SQL "truncate" function in
Informix? 
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. 
 
Jean Sagi 
jeansagi@myrealbox.com 
jeansagi@yahoo.com 
sending to informix-list</blockquote>
<x-sigsep></x-sigsep>
<tt>---------------------------------------------- 
Michael Dunham-Wilkie, M.Sc., M.P.A. 
Senior Database Analyst 
Barrodale Computing Services Ltd. 
Tel: (250) 472-4372 Fax: (250) 472-4373 
Web:
<a href="http://www.barrodale.com">http://www.barrodale.com 
</a>Email: mike@barrodale.com 
---------------------------------------------- 
Mailing Address: 
P.O. Box 3075 STN CSC 
Victoria BC Canada V8W 3W2 
Shipping Address: 
Hut R, McKenzie Avenue 
University of Victoria 
Victoria BC Canada V8W 3W2 
---------------------------------------------- </body>
</html>

--=====================_763011863==.ALT--

sending to informix-list

#2 (**permalink**) 04-20-2008, 07:28 AM

On Wed, 18 Aug 2004 11:40:30 -0400, Mike Dunham-Wilkie wrote:

Mike, are you aware that myschema will perform the 'split' of the schema into
separate scripts for create table (and a few other commands required by these)
and another for the indexes, constraints, priveleges, etc? Just give myschema
two filenames on the commandline. In addition if you specify -u myschema will
place the UPDATE STATISTICS commands needed to replicate the level of stats
that existed at the time the schema was created (though obviously fresher)
into the file containing CREATE INDEX commands. Or, if you include
-Ufilename3 then myschema will create a third file (filename3) to contain the
stats commands so you can use them periodically in a cron job to refresh stats
(better than dostats if you've carefully constructed a level of stats to
support difficult queries that's different from that produced by the rules
that dostats follows.

Art S. Kagel

> What I do to avoid having to hard-code details is
>
> - unload table
> - run a myschema (from one of Art Kagel's IIUG package) on the table - split
> sql into table create and index/constraint creates - run create table sql -
> alter table to raw mode
> - load table
> - alter table to standard mode
> - run index and constraint creates
>
> Mike
>
> At 06:51 AM 8/17/04, Jean Sagi wrote:
>
>
>>Personally, I don't think "truncate" as a best practice... but:
>>
>>1. As trucate is a delete without logging, why don't you consider using raw
>>tables (ie no loggin), but you have to have the drawbacks of raw tables.
>>
>>2. Drop the table, and then recreate it... you will need to have the schema
>>first.
>>
>>4. No other alternative comes to me right now, but why don't you propose to
>>IBM-Informix a "truncate" function. Online 5 practically hasn't any usefull
>>function... but some good ones where incorporated in IDS.
>>
>>Chucho!
>>
>>
>>-----Original Message-----
>>From: emebohw@netscape.net (sumGirl)
>>To: informix-list@iiug.org
>>Date: 16 Aug 2004 09:07:30 -0700
>>Subject: anything like MS-SQL "truncate" function in Informix?
>>
>>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.
>>
>>
>>Jean Sagi
>>jeansagi@myrealbox.com
>>jeansagi@yahoo.com
>>
>>sending to informix-list
>
> ---------------------------------------------- Michael Dunham-Wilkie, M.Sc.,
> M.P.A.
> Senior Database Analyst
> Barrodale Computing Services Ltd.
> Tel: (250) 472-4372 Fax: (250) 472-4373 Web:
> <http://www.barrodale.com>http://www.barrodale.com Email: mike@barrodale.com
> ---------------------------------------------- Mailing Address: P.O. Box
> 3075 STN CSC
> Victoria BC Canada V8W 3W2

#3 (**permalink**) 04-20-2008, 07:28 AM

On Wed, 18 Aug 2004 11:22:08 -0400, Art S. Kagel wrote:

OH, another note. If you are using any referential integrity constraints,
know that when you drop a table any foreign keys that point to that table will
be dropped as well. IF YOU USE dbschema to get the rebuild schema for the
table you want to truncate, IT WILL NOT PRINT those foreign keys. You will
have to edit them out of a full database level schema.

If instead you use myschema, as Mike suggests, you can include the -F option
to have myschema include foreign key definitions referencing the table.

Art S. Kagel

> On Wed, 18 Aug 2004 11:40:30 -0400, Mike Dunham-Wilkie wrote:
>
> Mike, are you aware that myschema will perform the 'split' of the schema
> into separate scripts for create table (and a few other commands required by
> these) and another for the indexes, constraints, priveleges, etc? Just give
> myschema two filenames on the commandline. In addition if you specify -u
> myschema will place the UPDATE STATISTICS commands needed to replicate the
> level of stats that existed at the time the schema was created (though
> obviously fresher) into the file containing CREATE INDEX commands. Or, if
> you include -Ufilename3 then myschema will create a third file (filename3)
> to contain the stats commands so you can use them periodically in a cron job
> to refresh stats (better than dostats if you've carefully constructed a
> level of stats to support difficult queries that's different from that
> produced by the rules that dostats follows.
>
> Art S. Kagel
>
>
>> What I do to avoid having to hard-code details is
>>
>> - unload table
>> - run a myschema (from one of Art Kagel's IIUG package) on the table -
>> split sql into table create and index/constraint creates - run create table
>> sql - alter table to raw mode
>> - load table
>> - alter table to standard mode
>> - run index and constraint creates
>>
>> Mike
>>
>> At 06:51 AM 8/17/04, Jean Sagi wrote:
>>
>>
>>>Personally, I don't think "truncate" as a best practice... but:
>>>
>>>1. As trucate is a delete without logging, why don't you consider using raw
>>>tables (ie no loggin), but you have to have the drawbacks of raw tables.
>>>
>>>2. Drop the table, and then recreate it... you will need to have the schema
>>>first.
>>>
>>>4. No other alternative comes to me right now, but why don't you propose to
>>>IBM-Informix a "truncate" function. Online 5 practically hasn't any usefull
>>>function... but some good ones where incorporated in IDS.
>>>
>>>Chucho!
>>>
>>>
>>>-----Original Message-----
>>>From: emebohw@netscape.net (sumGirl)
>>>To: informix-list@iiug.org
>>>Date: 16 Aug 2004 09:07:30 -0700
>>>Subject: anything like MS-SQL "truncate" function in Informix?
>>>
>>>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.
>>>
>>>
>>>Jean Sagi
>>>jeansagi@myrealbox.com
>>>jeansagi@yahoo.com
>>>
>>>sending to informix-list
>>
>> ---------------------------------------------- Michael Dunham-Wilkie,
>> M.Sc., M.P.A.
>> Senior Database Analyst
>> Barrodale Computing Services Ltd.
>> Tel: (250) 472-4372 Fax: (250) 472-4373 Web:
>> <http://www.barrodale.com>http://www.barrodale.com Email:
>> mike@barrodale.com ---------------------------------------------- Mailing
>> Address: P.O. Box 3075 STN CSC
>> Victoria BC Canada V8W 3W2

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode