Transaction Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 24 Apr 2007 15:28 GMT

Hello All,

We have the following transaction situation. Is it possible within a
transaction, "begin work" ......"commit work" to insert records to a
log table and have those records committed regardless of whether the
transaction gets rolled back or committed. In fact it would be
especially valuable to have those records in the log table when the
transaction get rolled back.

Any ideas??

--Dave

Reply to this Message

Keith Simmons - 24 Apr 2007 16:39 GMT

> Hello All,
>
[quoted text clipped - 13 lines]
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

Dave

It is not possible to have two levels of transaction, what you could
do is write a successful log record within the transaction and then
write a failure log record in a separate transaction as part of your
failure/rollback routine. If you rollback a transaction then
everything within that transaction (even two-stage commit on a remote
database) is rolled back.

Keith

Reply to this Message

Art S. Kagel - 24 Apr 2007 17:39 GMT

> Hello All,
>
[quoted text clipped - 4 lines]
> especially valuable to have those records in the log table when the
> transaction get rolled back.

Write the log records in a separate connection. If you open the connections
with the 'WITH CONCURRENT TRANSACTIONS' clause you can switch between them
during the transaction in one and write to the log table in the other.

Art S. Kagel

Reply to this Message

Claus Samuelsen - 24 Apr 2007 18:00 GMT

> Hello All,
>
[quoted text clipped - 8 lines]
>
> --Dave

If your using IDS 9.2 or (preferable much) later create you log table as
a RAW table. Everything that goes into raw tables are outside the
transaction and cannot be rolled back.
Claus

Reply to this Message

Zachi - 25 Apr 2007 22:46 GMT

> Hello All,
>
[quoted text clipped - 8 lines]
>
> --Dave

Informix does not support nesting transactions. You can write these
records to a raw table, or use a different connection for these
records

Zachi.

Reply to this Message

Alexey Sonkin - 27 Apr 2007 03:05 GMT

Just use 'RAW' table for that.
Starting 10.0xC6, RAW tables can be indexed...
This approach works very well in our applications

-Alexey

-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of Dave
Sent: Tuesday, April 24, 2007 10:29 AM
To: informix-list@iiug.org
Subject: Transaction Question

Hello All,

We have the following transaction situation. Is it possible within a
transaction, "begin work" ......"commit work" to insert records to a
log table and have those records committed regardless of whether the
transaction gets rolled back or committed. In fact it would be
especially valuable to have those records in the log table when the
transaction get rolled back.

Any ideas??

--Dave

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

Reply to this Message

Data Cruncher - 28 Apr 2007 18:41 GMT

Can raw tables have constrains also, like CHECK constraints.

This can help in those applications where we can afford loosing
data in some tables.

Just use 'RAW' table for that.
Starting 10.0xC6, RAW tables can be indexed...
This approach works very well in our applications

-Alexey

Reply to this Message

Jack Parker - 28 Apr 2007 23:30 GMT

I seem to remember that check constraints are ok, (as well as not null), but
not FK.

j.

-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of Data Cruncher
Sent: Saturday, April 28, 2007 1:42 PM
To: informix-list@iiug.org
Subject: Raw Tables

Can raw tables have constrains also, like CHECK constraints.

This can help in those applications where we can afford loosing
data in some tables.

Just use 'RAW' table for that.
Starting 10.0xC6, RAW tables can be indexed...
This approach works very well in our applications

-Alexey

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

Reply to this Message

Prasanna A Mathada - 30 Apr 2007 06:10 GMT

Hi

The following paragraph might help you, over the query you are
dealing with.

RAW Tables

RAW tables are nonlogging permanent tables and are similar to tables in a
nonlogging database. RAW tables use light appends, which add rows quickly
to the end of each table fragments. Updates, inserts, and deletes in a RAW
table are supported but not logged. RAW tables do not support indexes,
referential constraints, or rollback. You can restore a RAW table from the
last physical backup if it has not been updated since that backup. Fast
recovery rolls back incomplete transactions on STANDARD tables but not on
RAW tables. A RAW table has the same attributes whether stored in a
logging or nonlogging database.

RAW tables are intended for the initial loading and validation of data. To
load RAW tables, you can use any loading utility, including dbexport or
the High-Performance Loader (HPL) in express mode. If an error or failure
occurs while loading a RAW table, the resulting data is whatever was on
the disk at the time of the failure.

Recommendation: Do not use RAW tables within a transaction. After you have
loaded the data, use the ALTER TABLE statement to change the table to type
STANDARD and perform a level-0 backup before you use the table in a
transaction.

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

Thanks,
Prasanna Alur Mathada

"Data Cruncher" <datacruncher4@yahoo.com>
Sent by: informix-list-bounces@iiug.org
28/04/2007 23:11

To
informix-list@iiug.org
cc

Subject
Raw Tables

Can raw tables have constrains also, like CHECK constraints.

This can help in those applications where we can afford loosing
data in some tables.

Just use 'RAW' table for that.
Starting 10.0xC6, RAW tables can be indexed...
This approach works very well in our applications

-Alexey

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

Reply to this Message