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 asiqdemo 
   AS sales
   

2. Start a read/write transaction.

   INSERT INTO fin_code
   (code, type, description) 
   VALUES ('e6', 'expense', 'Services')
   

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

   CONNECT DATABASE asiqdemo
   AS marketing;
   SELECT cust_id, order_date 
   FROM sales_order
   

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

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

   CONNECT DATABASE asiqdemo
   AS accounting
   

5. Start a read/write transaction:

   INSERT INTO fin_code
   (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.

Now let's try setting some savepoints to return to as we run two similar transactions. This time, we can put the second transaction on hold until the first one completes.

1. Connect to the database:

   CONNECT DATABASE asiqdemo 
   

2. Start a read/write transaction.

   INSERT INTO fin_code
   (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 Sybase IQ Reference Manual

4. Start a read/write transaction:

   INSERT INTO fin_code
   (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 asiqdemo 
   AS marketing
   

2. Start a read/write transaction.

   INSERT INTO fin_code
   (code, type, description) 
   VALUES ('e9', 'expense', 'R & D')
   

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

   CONNECT DATABASE asiqdemo
   AS sales
   

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

   INSERT INTO sales_order
   (id, cust_id, order_date, sales_rep) 
   VALUES ('2088', '140', '05-29-98', '195')
   COMMIT
   

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

For more details about transaction support, see the Sybase IQ System Administration Guide.