Informix Guide to SQL: Tutorial Modifying Data

Even if all the software is error-free and all the hardware is utterly reliable, the world outside the computer can interfere. Lightning might strike the building, interrupting the electrical supply and stopping the computer in the middle of your UPDATE statement. A more likely scenario occurs when a disk fills up or a user supplies incorrect data, causing your multirow insert to stop early with an error. In any case, as you are modifying data, you must assume that some unforeseen event can interrupt the modification.

When an external cause interrupts a modification, you cannot be sure how much of the operation was completed. Even in a single-row operation, you cannot know whether the data reached the disk or the indexes were properly updated.

If multirow modifications are a problem, multistatement modifications are worse. They are usually embedded in programs so you do not see the individual SQL statements being executed. For example, to enter a new order in the demonstration database, perform the following steps:

1. Insert a row in the orders table. (This insert generates an order number.)
2. For each item ordered, insert a row in the items table.

Two ways to program an order-entry application exist. One way is to make it completely interactive so that the program inserts the first row immediately and then inserts each item as the user enters data. But this approach exposes the operation to the possibility of many more unforeseen events: the customer's telephone disconnecting, the user pressing the wrong key, the user's terminal or computer losing power, and so on.

The following list describes the correct way to build an order-entry application:

• Accept all the data interactively.
• Validate the data, and expand it (look up codes in stock and manufact, for example).
• Display the information on the screen for inspection.
• Wait for the operator to make a final commitment.
• Perform the insertions quickly.

Even with these steps, an unforeseen circumstance can halt the program after it inserts the order but before it finishes inserting the items. If that happens, the database is in an unpredictable condition: its data integrity is compromised.

The solution to all these potential problems is called the transaction. A transaction is a sequence of modifications that must be accomplished either completely or not at all. The database server guarantees that operations performed within the bounds of a transaction are either completely and perfectly committed to disk, or the database is restored to the same state as before the transaction started.

The transaction is not merely protection against unforeseen failures; it also offers a program a way to escape when the program detects a logical error.

The database server can keep a record of each change that it makes to the database during a transaction. If something happens to cancel the transaction, the database server automatically uses the records to reverse the changes. Many things can make a transaction fail. For example, the program that issues the SQL statements can fail or be terminated. As soon as the database server discovers that the transaction failed, which might be only after the computer and the database server are restarted, it uses the records from the transaction to return the database to the same state as before.

The process of keeping records of transactions is called transaction logging or simply logging. The records of the transactions, called log records, are stored in a portion of disk space separate from the database. This space is called the logical log because the log records represent logical units of the transactions.

Only databases on Enterprise Decision Server generate transaction records automatically.

Most Informix databases do not generate transaction records automatically. The DBA decides whether to make a database use transaction logging. Without transaction logging, you cannot roll back transactions.

In addition to logical-log files, Enterprise Decision Server allows you to create logslices and alter them to add logical logs at any time. A logslice is a set of log files that occupy a dbslice. These log files are owned by multiple coservers, one log file per dbspace. Logslices simplify the process of adding and deleting log files because a logslice treats a set of log files as a single entity. You create, alter, and delete dbslices using the onutil utility. For more information about logslices, see the Administrator's Guide.

Databases on Enterprise Decision Server must be logged databases and logging cannot be turned off. However, you can specify that individual tables are logging or nonlogging tables. To meet the need for both logging and nonlogging tables, Enterprise Decision Server supports the following types of permanent tables and temporary tables:

• Raw permanent tables (nonlogging)
• Static permanent tables (nonlogging)
• Operational permanent tables (logging)
• Standard permanent tables (logging)
• Scratch temporary tables (nonlogging)
• Temp temporary tables (logging)

For more information about the table types that Enterprise Decision Server supports, see the Informix Guide to Database Design and Implementation.

Logging must be turned on in your database for cascading deletes to work because, when you specify a cascading delete, the delete is first performed on the primary key of the parent table. If the system fails after the rows of the primary key of the parent table are performed but before the rows of the foreign key of the child table are deleted, referential integrity is violated. If logging is turned off, even temporarily, deletes do not cascade. After logging is turned back on, however, deletes can cascade again.

Databases that you create with Enterprise Decision Server are always logging databases.

Dynamic Server allows you to turn on logging with the WITH LOG clause in the CREATE DATABASE statement.

You can use two methods to specify the boundaries of transactions with SQL statements. In the most common method, you specify the start of a multistatement transaction by executing the BEGIN WORK statement. In databases that are created with the MODE ANSI option, no need exists to mark the beginning of a transaction. One is always in effect; you indicate only the end of each transaction.

In both methods, to specify the end of a successful transaction, execute the COMMIT WORK statement. This statement tells the database server that you reached the end of a series of statements that must succeed together. The database server does whatever is necessary to make sure that all modifications are properly completed and committed to disk.

A program can also cancel a transaction deliberately by executing the ROLLBACK WORK statement. This statement asks the database server to cancel the current transaction and undo any changes.

An order-entry application can use a transaction in the following ways when it creates a new order:

• Accept all data interactively
• Validate and expand it
• Wait for the operator to make a final commitment
• Execute BEGIN WORK
• Insert rows in the orders and items tables, checking the error code that the database server returns
• If no errors occurred, execute COMMIT WORK; otherwise execute ROLLBACK WORK

If any external failure prevents the transaction from being completed, the partial transaction rolls back when the system restarts. In all cases, the database is in a predictable state. Either the new order is completely entered, or it is not entered at all.