Deleting all rows from standard (with loggin) table on IDS 9.40

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lukas Barton - 21 Sep 2006 13:57 GMT

Hi,

how can I delete all rows from standard (with loggin) table on IDS 9.40?
- on tuncate table [table_name] server report synstax error (SQL
State: 42000, Error Code: -201)
- on delete from [table_name] causes long transaction error (SQL
State: IX000, Error Code: -458)

I use JDBC.
There are about 300 000 rows in table.
Locking mode is page.
Database is non-ansi.
Table have some foreing keys and indexes.

Thanks helping me,

Lukas

Reply to this Message

Double Echo - 28 Sep 2006 04:07 GMT

I might be wrong but can't you just use ondblog to turn logging off, delete the rows, then turn it on again? It means not having to log all those deletes. But of course if you have to log all of the deletions then this isn't going to work for you. You can do this for loading data too. Very simple.

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ad
min.doc/admin464.htm

Hope this helps...

DE

> Hi,
>
[quoted text clipped - 13 lines]
>
> Lukas

Reply to this Message

Lukas Barton - 28 Sep 2006 11:30 GMT

> I might be wrong but can't you just use ondblog to turn logging off, delete the rows, then turn it on again? It means not having to log all those deletes. But of course if you have to log all of the deletions then this isn't going to work for you. You can do this for loading data too. Very simple.
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ad
min.doc/admin464.htm
>
> Hope this helps...
>

You may miss that I am using JDBC (Hibernate exactly).
So the only valid solution is to delete data in for (while) loop (only
if the Hibernate dialect is InformixDialect).

I have to have similar if (if (...Informix...)) on update/delete
statement with subselect over updated table in where.
Eg.:

Informix:
update Dotaz as dotaz set statusId=:status,
metaTypChyby=:metaTypChyby where dotaz.davka=:davka and exists(select
vstp.id from IdfKlient as vstp where vstp.dotaz=dotaz and
tabulkaVSablone=:tabulka)

Oracle, Postgres etc:
update Dotaz as dotaz set statusId=:status,
metaTypChyby=:metaTypChyby where dotaz.davka=:davka and dotaz.id in
(select vstp.dotaz.id from IdfKlient as vstp where
vstp.dotaz.davka=:davka and tabulkaVSablone=:tabulka)

The first stament does not pass on Interbase/Firebird :-(

That's the price of writing multidatabase application.

Lukas

Reply to this Message

Obnoxio The Clown - 28 Sep 2006 13:06 GMT

>> Hi,
>>
[quoted text clipped - 10 lines]
>> Database is non-ansi.
>> Table have some foreing keys and indexes.

delete from table_name

????

Signature

Bye now,
Obnoxio

"... no bill is required as no value was provided."
-- Christine Normile

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

Reply to this Message

Sebastian, Norma J. - 28 Sep 2006 05:12 GMT

I would first advise that when you come into another DB platform's forum
to ask for assistance, you ask nicely, don't try to slam us in the
process.

When I ask for Oracle help, I don't start out by saying how stupid I
think the product is.
For example, I have a 35 gb EMPTY tablespace ... Empty... 0 objects in
it.... and it takes approximately 200+ hours to drop the tablespace.
Need I add how stupid it seems to have to specify "including contents
and datafiles".... ... why do I have to tell oracle to drop the
contents.... of course I want the contents and datafiles gone.... after
all, didn't I just ask to drop the tablespace? Oh, that's right....
to do it fast in oracle, I just let it drop the definition of the space,
and then I manually go clean up the filesystems myself..... but when I
want oracle to clean up after itself.... well, 200+ hours later the
EMPTY tablespace is finally gone.......
Now see, you can probably explain that all away with your Oracle
experience, but do you want to help me ....why... I have just spent time
slamming your product.... to heck with me.
Why did it take oracle until version 9 to improve extent management....
did it take that long before oracle had to deal with databases of
substantial size....

Informix has never caused me headaches on extent management in 15 years.
I only "patch" my engine when it's time to upgrade it. My last upgrade
was fall of 2004.
Not to mention that when Informix is paid for, we don't have to pay more
for features like table fragmentation (this would be oracle partitioning
for you).
We've had high quality backup utilities for years. RMAN is finally
being trusted in the user community..... informix people find that
really funny too.... we don't know the definition of a cold database
backup because we never do them.... our databases are always online for
backups.

Every time I enter a Metalink TAR or whatever they are called now, it
seems the response is "apply a patch"..... great..... does Oracle
realize this requires downtime which is not what the business community
wants.

Now if you would kindly explain what your little problem is, the pros
out here might be kind enough to rise above your product slamming.....

Or maybe they'll just say RTFM !

Cheers back at ya !

Norma Jean

-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of internetuser
Sent: Wednesday, September 27, 2006 12:20 PM
To: informix-list@iiug.org
Subject: Re: Deleting all rows from standard (with loggin) table on IDS
9.40

I have the same problem here - and I have to say, I find all of these
"solutions" ridiculous. It should not be necessary to do anything other
than "delete from whatever" unless one is just dealing with millions
upon millions of rows.

In my case, I've got one table with less than 900 records that have at
most 300,000 child records in all. The Oracle version of my database
has no problems, but Informix chokes on it - which I find astonishing.
Any commercial database product simply should not even blink at
deleting it all at once.

Yeah, I'm a new Informix user and I don't know whatever Informix tricks
one needs to know, but I seem to be finding issue after issue on things
that just should not be issues. So far I am incredibly underwhelmed and
baffled as to why this database gets used, much less paid for.

Ugh!

Cheers!
-iu

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
============================================================

Reply to this Message