Not in Transaction Error Message -255
Ruchi Patel
uses unbuffered transaction logging. The 4gl program gives -255 error
message when I am trying to open a cursor that is defined for UPDATE.
Here is a test program.
PREPARE updt_stmt FROM
SELECT * FROM T WHERE ROWID = ? FOR UPDATE
DECLARE upd_curs CURSOR FOR updt_stmt
OPEN upd_curs USING ROWID
I need to convert applications written for SE to work with ONLINE using
COMPILER. The SE uses NO Transaction logging. The ONLINE uses
UNBUFFERED TRANSACTION LOGGING. What choices do I have?
If I use BEGIN WORK before OPEN statement then it works fine. According
to Informix I don't need to re-write all my application.
Please advise.
Ruchi
My e-mail address is Patel...@avid.com
Dave Snyder
same style, same cursor names, etc. Anyway, you won't have to re-write
all of your applications, but you will have to tweek all of your applications.
The BEGIN WORK before the OPEN (for update) is needed but you'll also need
COMMIT WORK and/or ROLLBACK WORK in strategic places. One thing I told a
guy I was training to do was run db4glgen against a small table twice. The
only difference between the two runs was the addition of the "-l" option
to generate transaction logging code. He then printed both listings and
he also printed the output from a "diff" of the two programs. This gave
him all the places that needed altered code. BTW, you'll need a few
SET ISOLATION LEVEL statements in some strategic places too.
DAS
--
Dave Snyder @ Bell Atlantic - Philadelphia, PA
WORK: ddx...@boots.bell-atl.com HOME: da...@das13.snide.com
Jonathan Leffler
>Date: 9 Mar 1995 20:49:39 GMT
>X-Informix-List-Id: <news.12087>
>
>I am working with online 6.0 version on HP 9000 platform. The database
>uses unbuffered transaction logging. The 4gl program gives -255 error
>message when I am trying to open a cursor that is defined for UPDATE.
>Here is a test program.
>
>PREPARE updt_stmt FROM
>SELECT * FROM T WHERE ROWID = ? FOR UPDATE
>DECLARE upd_curs CURSOR FOR updt_stmt
>OPEN upd_curs USING ROWID
>
>I need to convert applications written for SE to work with ONLINE using
>COMPILER. The SE uses NO Transaction logging. The ONLINE uses
>UNBUFFERED TRANSACTION LOGGING. What choices do I have?
>
>If I use BEGIN WORK before OPEN statement then it works fine. According
>to Informix I don't need to re-write all my application.
I attach a document I wrote in 1988 on the subject of handling transactions
in I4GL code for databases which may, or may not, have transactions. I
removed a few names but the content remains basically valid.
Note that since you are (I trust) using at least version 4.00, you can
automatically detect whether you are working with OnLine or SE, with
transactions or not, and with a MODE ANSI database or not, by looking at
the SQLCA.SQLAWARN flags (in I4GL; a different name in ESQL/C) immediately
after the database is opened.
Note that you also need to worry about table locking. If you have a no
transactions, you use LOCK TABLE and UNLOCK TABLE; if you have them, you
use LOCK TABLE inside a transaction and COMMIT WORK. If you need to code
for both, use a pair of functions lock_table() and unlock_table() to handle
these deviances.
I also attach a document on whether to use a transaction log or not from
the same era. It doesn't take into account OnLine as Turbo was then not
readily available, let alone OnLine. The bug in ROLLFORWARD DATABASE it
mentions has been fixed too.
Pretending you can switch between logged and unlogged databases without
code changes and without careful planning is ludicrous. That is a personal
opinion, but it is based on a non-neglible amount of experience.
Yours,
Jonathan Leffler (jo...@informix.com) #include <disclaimer.h>
===========================================================================
Handling Transactions in Informix-4GL code
1. The need for a method of handling transactions
There are two problems with handling transactions in an Informix-4GL
program: first, it is necessary to decide where to create the transaction
boundaries; and second, it is necessary to insert code to handle the
transaction. Also, transaction logs can be made to come and go - it isn't
always easy to make the transaction log go, but it can be done - so another
problem occurs if there is any possibility that the transaction log may not
exist; executing a BEGIN WORK statement when there is no log causes an
error.
There is no simple cure for the problem of deciding where transaction
boundaries should be created, but the other problems can be eased.
2. A simple method of handling transactions
To make the transaction handling code robust (not dependent on the presence
or absence of the transaction log) the code should be written on the
assumption that there is a log, but instead of writing the BEGIN WORK,
COMMIT WORK or ROLLBACK WORK statements, write a call to a routine which
only executes the transaction statement if the log is present. The
routines are:
CALL begin_work() { Conditionally BEGIN WORK }
CALL commit_work() { Conditionally COMMIT WORK }
CALL rollback_work() { Conditionally ROLLBACK WORK }
By calling these routines where the corresponding statement would be
written, the code is made robust. An additional help is the routine called
end_work; it takes an integer argument which indicates whether the
transaction was successful or not, and executes either COMMIT WORK or
ROLLBACK WORK depending on the value in the flag. If the value is 0
(zero), the transaction is successful and is committed; otherwise, it fails
and is rolled back. CALL end_work(STATUS) These routines make use of
another routine called translog which tests whether there is a transaction
log on the database; it returns TRUE if there is a transaction log, and
FALSE if there is not. This routine only tests the database once, so it is
not an expensive function to use, but in general, the other 4 routines are
the only ones that need to use it.
3. The actual routines
{
@(#)translog.4gl 1.1
@(#)JLSS Informix Tools: General Library
@(#)Handle transactions whether database has log or not
@(#)Author: JL
}
{ Global to this file -- not accessible outside }
DEFINE logstatus INTEGER
{ 0 => State of log unknown, 1 => Log absent, 2 => Log present }
{ Determine whether there is a transaction log }
FUNCTION translog()
DEFINE
junk INTEGER
IF logstatus = 0 THEN
SELECT Tabid
INTO junk
FROM Systables
WHERE Systables.Tabtype = 'L'
IF STATUS = NOTFOUND THEN
LET logstatus = 1 { Log absent }
ELSE
LET logstatus = 2 { Log present }
END IF
END IF
RETURN (logstatus = 2)
END FUNCTION {translog}
{ Begin a transaction if there is a log }
FUNCTION begin_work()
IF translog() THEN
BEGIN WORK
END IF
END FUNCTION {begin_work}
{ Commit a transaction if there is a log }
FUNCTION commit_work()
IF translog() THEN
COMMIT WORK
END IF
END FUNCTION {commit_work}
{ Rollback transaction if there is a log }
FUNCTION rollback_work()
IF translog() THEN
ROLLBACK WORK
END IF
END FUNCTION {rollback_work}
{ Terminate a transaction }
FUNCTION end_work(state)
DEFINE
state INTEGER
IF state != 0 THEN
CALL rollback_work()
ELSE
CALL commit_work()
END IF
END FUNCTION {end_work}
Jonathan Leffler
Sphinx Ltd.
22nd February 1988
===========================================================================
To log or not to log, that is the question
(With a limited apology to the Immortal Bard and the Prince of Denmark.)
1. Using a transaction log
Should the operational version of the database have a transaction log, and
does it have to be decided immediately? The answer to both questions is
"Yes"; the two sections which follow justify these answers, and the
remaining sections discuss consequences of these answers.
2. Why use a transaction log?
There are at least three points in favor of using a transaction log.
1. A transaction log makes backups easier. After a full backup, only the
log needs to be backed up until the next full backup because the log
records all the changes made to the database.
2. There are a number of parts of the application where changes must be
made to several tables, and unless all the changes are successful, the
database must be restored to its state prior to the first change.
This is precisely what a transaction is for.
3. The database will be more resilient. Even if the machine should
crash, the transaction log will allow the database to be brought up to
date, as far as the last complete transaction prior to the crash.
The only points against using a transaction log are:
1. It slows down changes to the database because they have to be logged.
The slowing down should be barely appreciable, except perhaps in
batch-mode changes when the total time taken may increase measurably.
2. The log needs maintenance as it will grow indefinitely. However, the
database will need regular monitoring in any case, so this should not
be a major problem.
On balance, a transaction log is more useful than it is a nuisance, so a
transaction log should be used in the operational database.
3. Why decide immediately?
It is necessary to decide whether to use a transaction log or not before
coding starts because different code is required if logs are used compared
with when they are not used. A cursor for a SELECT FOR UPDATE statement
must be used within a transaction if there is a transaction log on the
database. If there is no transaction log, transactions may not be used.
It is not practical to code the system so that if there is a transaction
log, transactions are used, but if there is no log, transactions are not
used. (It is probably technically possible to do this if the programs are
designed carefully and compiled against a database with transaction logging
enabled.)
4. Are there any alternatives?
Audit trails could be used to monitor changes to selected tables, and these
selected tables could be recovered from the audit trails in the event of a
crash. However, the ability to ensure that a sequence of updates all
succeed and to restore the database to its original state if any of the
updates failed; that ability would be lost if audit trails were used.
5. How should the database be created?
The database will be created without (repeat, without) a transaction log.
The tables and indexes will be created without the log; there is no need to
record how the database is created. Any basic data will be loaded into the
database without a log; the data will be stored in ASCII unload files and
can be reloaded as often as necessary, so there is no need to log the base
data that is inserted. Access permissions for individual tables should be
set up. At this stage, only the creator of the database can access it.
The database should now be completely backed up, and so should the
materials used to create it. After the backup is complete and verified,
the transaction log should be started, and finally, the relevant users
should be granted access to the database - these grants will be the first
records on the transaction log. (Exactly when the log should be started
will have to be determined.)
6. Why create it like this?
There are two reasons for using this procedure, one noble, honest and
sensible the other rather ignoble and devious. The honest reason is
because there is no need to record how the database is created provided all
the tools and data to re-create the database are available. The devious
reason has to do with a bug that used to exist in the database recovery
mechanism which, under some circumstances, would not create a table. If no
permanent tables are created while the transaction log is in use, this bug
(if it is still a bug) is evaded.
7. Database backup procedures
With a transaction log in use, there are two types of backup to be done on
the database, the full backup and the partial backup. For both types of
backup, the crucial first step is to ensure that the database is quiescent;
nobody should be using it, and no background programs should be accessing
it.
For a partial backup, the transaction log is backed up, the backup is
verified and then the database is released. There is a variant procedure
to this, which empties the transaction log after each partial backup. In
favor of this variant is the fact that the size of the log is reduced;
against it, the recovery process now involves recovering the database from
each of the transaction logs backed up since the last full backup, whereas
the other method (not zeroing the log) only needs one recovery because the
log still contains all the changes since the last backup. Which technique
is used will probably depend on the rate at which the log grows and the
amount of disc space available for the log.
For a full backup, after locking users out of the database, all the tables
in the database (except the transaction log) are backed up, and the backup
is verified. The transaction log is then emptied, and the database is then
released.
8. Backup records
If there is not already a backup recording system, then such a system shall
be introduced. It is crucial that accurate records are kept of what is
backed up on what media. A record must be kept of:
i. What was backed up
ii. When it wad done: date and time
iii. Who did it
iv. Which tape was used
v. What command is needed to restore the data onto the
disc (including any change directory commands)
vi. Where the tape is stored.
Ideally, this information should be stored in a database, but a paper
record (printout of the database) must also be kept, because there could be
nothing more infuriating than to suffer a serious crash, to know that there
is a complete set of backups, but to discover that there is no record of
what is backed up on each tape and so to be unable to recover the
information.
9. Disaster simulation
On at least three occasions during the development stage of this project, a
disaster shall be simulated to test the recovery mechanisms. At least one
such crash shall be very early in the development phase. At least one such
crash shall be near the end of the development phase. At least one such
crash shall be part of the proving trials.
For each such crash, a full backup of the database shall be made and
verified without employing the transaction log. The database shall then be
destroyed and restored twice. The first recovery shall assume that the log
was destroyed and recover completely off the tapes. This will bring the
database up-to-date at the point where the backup was made. The second
recovery shall use the transaction log off the disc to bring the database
fully up-to-date. The backup made just before the simulated disaster will
only be used if the recovery mechanisms fail.
Jonathan Leffler
Sphinx Ltd.
29th January 1988