Ensuring data consistency

Sybase IQ has special features that ensure data consistency in a data warehouse environment.

Typically, in a data warehouse environment, many users need to read from the database, but only the DBA needs to update it. However, there is often a need to make updates while other users continue to request and receive query results. Sybase IQ keeps track of database changes using table-level snapshot versioning. It keeps a record of what the table looks like when a user begins a write transaction.

Sybase IQ allows multiple readers, but only one writer to a table. In the next procedure, you will connect as two different users and try to write to a table from both connections at the same time. One statement will be rolled back and will receive an error message while the other commits.

1. Connect to the database using a connection name in Interactive SQL:

   CONNECT DATABASE iqdemo 
   AS sales
   

2. Start a read/write transaction.

   INSERT INTO FinancialCodes
   (Code, Type, Description) 
   VALUES ('e6', 'expense', 'Services')
   

3. Connect to the same database with a different connection name and run a query.

   CONNECT DATABASE iqdemo
   AS marketing;
   SELECT CustomerID, OrderDate 
   FROM SalesOrder
   

   sSybase IQ returns the requested information; this is a read-only transaction.

4. Connect to the same database using a different connection name:

   CONNECT DATABASE iqdemo
   AS accounting
   

5. Start a read/write transaction:

   INSERT INTO FinancialCodes
   (Code, Type, Description) 
   VALUES ('r3', 'revenue', 'Sales & Marketing')
   

   This command will fail and be rolled back.

6. As user sales, commit your transaction.

7. Retry your transaction as user accounting. You may now commit or roll back this transaction.

After each transaction commits, Sybase IQ writes updated data pages to disk. This approach is ideal for the data warehouse, where a single application may write millions of rows of data. A checkpoint is a point in a transaction when the database writes information to disk. Most OLTP databases write data to disk at checkpoints. Sybase IQ does not wait for a checkpoint to write physical data. Sybase IQ uses checkpoints to write certain information to disk for internal tracking, which is used if you need to recover your database. While you may set explicit checkpoints, most Sybase IQ checkpoints occur automatically.

To limit the amount of data that Sybase IQ writes to disk, you may set a savepoint. A savepoint defines a point in a transaction after which all changes can be undone by a ROLLBACK TO SAVEPOINT statement. This allows you to commit data before the entire transaction finishes.

Set some savepoints to return to as you run two similar transactions. This time, you can put the second transaction on hold until the first one completes.

1. Connect to the database:

   CONNECT DATABASE iqdemo 
   

2. Start a read/write transaction.

   INSERT INTO FinancialCodes
   (Code, Type, Description) 
   VALUES ('e8', 'expense', 'Services')
   

3. Set a savepoint.

   SAVEPOINT TUES9_45
   

   Naming your savepoint (as shown) is optional. You must follow the rules for object names described in Reference: Statements and Options.

4. Start a read/write transaction:

   INSERT INTO FinancialCodes
   (Code, Type, Description) 
   VALUES ('r3', 'fees', 'Administration')
   COMMIT
   

   If this insert command were to fail, the transaction would roll back to Savepoint TUES9_45. The data inserted in Step #2 would not be lost.

If you are working on several tables within one database, Sybase IQ allows multiple readers and writers in a database, as long as the writers write to different tables.

1. Connect to the demo database using connection name marketing:

   CONNECT DATABASE iqdemo 
   AS marketing
   

2. Start a read/write transaction.

   INSERT INTO FinancialCodes
   (Code, Type, Description) 
   VALUES ('e9', 'expense', 'R & D')
   

3. Connect to the same database using a different connection name:

   CONNECT DATABASE iqdemo
   AS sales
   

4. Start a read/write transaction using a different table:

   INSERT INTO SalesOrders
   (ID, CustomerID, OrderDate, SalesRepresentative) 
   VALUES ('2088', '140', '05-29-98', '195')
   COMMIT
   

   This transaction will commit immediately because, although both are in iqdemo database, the insert operations affect different tables.