Example of using a full transactional table (ACID) in Db2 Big SQL

Data in a transactional table is stored differently from a table that is not using ACID semantics.

When data is modified, the resulting changes are stored in one or more delta directories. The base directory contains rows that have been compacted. A compaction operation will merge data in the base and delta directories into one new base directory.

Example

This example shows you how to create a full transactional table, modify its contents, run a compaction operation on the table, and query the table from Db2® Big SQL. The example demonstrates the effects of these operations in the file system.

  1. Create a full transactional table (ACID) in Db2 Big SQL:
    [bigsql]  1> create hadoop table acid1 (id int, value int) stored as orc
  tblproperties ('transactional'='true');
  2. Insert some data and then query the table in Db2 Big SQL:
    [bigsql]  1>insert into acid1 values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
[bigsql]  1> select * from acid1 order by id;
+----+-------+
| ID | VALUE |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
|  4 |     4 |
|  5 |     5 |
|  6 |     6 |
+----+-------+
6 rows in results(first row: 0.487s; total: 0.491s)
    The following files have been created on the file system:
    $ hadoop fs -ls -R /warehouse/tablespace/managed/hive/bigsql.db/acid1
drwxrwx---+  - bigsql hive          0 2020-03-09 10:43 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delta_0000001_0000001_0000
-rw-rw----+  3 bigsql hive          1 2020-03-09 10:43 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delta_0000001_0000001_0000/_orc_acid_version
-rw-rw----+  3 bigsql hive        698 2020-03-09 10:43 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delta_0000001_0000001_0000/bucket_00001
  3. Run insert, update, and delete operations, and then query the table:
    [bigsql]  1> insert into acid1 values (7, 7), (8, 8);
2 rows affected (total: 1.018s)
[bigsql]  1> update acid1 set value=value * 10 where id=2;
1 row affected (total: 1.233s)
[bigsql]  1> delete from acid1 where id=3 or id=8;
2 rows affected (total: 0.886s)
[bigsql]  1> select * from acid1 order by id;
+----+-------+
| ID | VALUE |
+----+-------+
|  1 |     1 |
|  2 |    20 |
|  4 |     4 |
|  5 |     5 |
|  6 |     6 |
|  7 |     7 |
+----+-------+
6 rows in results(first row: 0.729s; total: 0.750s)
  4. The file system now has the following directories:
    hadoop fs -ls /warehouse/tablespace/managed/hive/bigsql.db/acid1
Found 7 items
drwxrwxrwx+  - bigsql hive          0 2020-03-09 10:51 /warehouse/tablespace/managed/hive/bigsql.db/acid1/._biginsights_stats
drwxrwx---+  - bigsql hive          0 2020-03-09 10:50 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delete_delta_0000003_0000003_0001
drwxrwx---+  - bigsql hive          0 2020-03-09 10:51 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delete_delta_0000004_0000004_0001
drwxrwx---+  - bigsql hive          0 2020-03-09 10:51 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delete_delta_0000004_0000004_0002
drwxrwx---+  - bigsql hive          0 2020-03-09 10:43 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delta_0000001_0000001_0000
drwxrwx---+  - bigsql hive          0 2020-03-09 10:49 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delta_0000002_0000002_0000
drwxrwx---+  - bigsql hive          0 2020-03-09 10:50 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delta_0000003_0000003_0000
  5. Run a major compaction operation and wait for it to complete:
    [bigsql]  1> alter table acid1 compact 'MAJOR' and wait;
0 rows affected (total: 5m1.161s)
[bigsql]  1> select * from acid1 order by id;
+----+-------+
| ID | VALUE |
+----+-------+
|  1 |     1 |
|  2 |    20 |
|  4 |     4 |
|  5 |     5 |
|  6 |     6 |
|  7 |     7 |
+----+-------+
6 rows in results(first row: 0.671s; total: 0.673s)
    The base directory has been created. The compaction cleaner will later remove the old delta and delete_delta directories that are now obsolete.
    $ hadoop fs -ls /warehouse/tablespace/managed/hive/bigsql.db/acid1
Found 8 items
drwxrwxrwx+  - bigsql hive          0 2020-03-09 11:01 /warehouse/tablespace/managed/hive/bigsql.db/acid1/._biginsights_stats
drwxrwx---+  - bigsql hive          0 2020-03-09 10:54 /warehouse/tablespace/managed/hive/bigsql.db/acid1/base_0000004_v0008620
drwxrwx---+  - bigsql hive          0 2020-03-09 10:50 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delete_delta_0000003_0000003_0001
drwxrwx---+  - bigsql hive          0 2020-03-09 10:51 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delete_delta_0000004_0000004_0001
drwxrwx---+  - bigsql hive          0 2020-03-09 10:51 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delete_delta_0000004_0000004_0002
drwxrwx---+  - bigsql hive          0 2020-03-09 10:43 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delta_0000001_0000001_0000
drwxrwx---+  - bigsql hive          0 2020-03-09 10:49 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delta_0000002_0000002_0000
drwxrwx---+  - bigsql hive          0 2020-03-09 10:50 /warehouse/tablespace/managed/hive/bigsql.db/acid1/delta_0000003_0000003_0000