11.4.23 Operation taking background import and chunks into consideration (Example 1: Adding and deleting data on a regular basis)

(a) Defining the purchasing history table (PURCHASE)

Define the purchasing history table (PURCHASE) as follows:

• Define the purchasing history table as a multi-chunk table because the table will be subject to background import.

• Define the purchasing history table as a column store table because the table will be used as a fact table of a star schema.

• Store the purchasing history table in DB area TBLDAT_001.

• Set the maximum number of chunks in the purchasing history table to 44. The following shows how this value was estimated.

■ Estimating the maximum number of chunks in the purchasing history table

The purchasing history table will store the data of the past six months. This is the basis for estimating the maximum number of chunks to be specified in the chunk specification of the CREATE TABLE statement.

Assuming 31 days in a month, background import is performed 31 times a month, and therefore the number of chunks needed is 31. For the remaining five months, the number of chunks is kept low by grouping data into a single chunk every month in order to maintain a high level of retrieval performance.

Assuming a safety factor of 1.2, the maximum number of chunks that can be created is as follows:

maximum-number-of-chunks-to-be-created = ↑(31 + 5) × 1.2 (safety factor)↑ = 44

Note

If the number of chunks increases, this negatively impacts retrieval performance. Therefore, we recommend that you design and operate your database system to minimize the number of chunks that will be used.

Based on this estimate, specify 44 as the maximum number of chunks. Specify this value for CHUNK of the chunk specification of the CREATE TABLE statement. The following shows an example of specifying the CREATE TABLE statement to define the purchasing history table.

■ Example of specifying the CREATE TABLE statement

CREATE TABLE "PURCHASE"
            ("SHOP_ID"     INTEGER,
             "TRADE_TIME"  TIMESTAMP,
             "ITEM_ID"     INTEGER,
             "ITEM_NAME"   VARCHAR(100),
             "CUSTOMER_ID" INTEGER,
             "PURCHASE_ID" CHAR(8))
    IN "TBLDAT_001"
    CHUNK=44                                ...1
    STORAGE FORMAT COLUMN                   ...2

Explanation:

1. This specification sets the maximum number of chunks. This specification defines the purchasing history table as a multi-chunk table.

2. This specification defines the purchasing history table as a column store table.

For details about the CREATE TABLE statement, see CREATE TABLE (define a table) in Definition SQL in the manual HADB SQL Reference.

(b) Defining range indexes for the purchasing history table (PURCHASE)

Define range indexes for the purchasing history table (PURCHASE). Define the range indexes so that they are stored in DB area RIXDAT_001.

Define range indexes for the trade time (TRADE_TIME column). The following shows an example of specifying the CREATE INDEX statement to define range indexes.

■ Example of specifying the CREATE INDEX statement

CREATE INDEX "TRADE_TIME_RIX"
   ON "PURCHASE"("TRADE_TIME") IN "RIXDAT_001" 
      EMPTY INDEXTYPE RANGE

For details about the CREATE INDEX statement, see CREATE INDEX (define an index) in Definition SQL in the manual HADB SQL Reference.

(c) Defining tables other than the purchasing history table (PURCHASE)

Define also the following tables, which are required to manage the master data other than the purchasing history data:

• Shop table (SHOPLIST)

• Product table (ITEMLIST)

• Customer table (CUSTOMER)

• Trade time table (TRADETIME)

Define the preceding tables as follows:

• Define the preceding tables as single-chunk tables because they will not be subject to background import.

• Define the preceding tables as row store tables because they will be used as dimension tables of a star schema.

• Define the preceding tables so that they are stored in DB areas TBLDAT_002 to TBLDAT_005, respectively.

• B-tree indexes must be defined for some columns of the preceding tables. The following lists the relevant columns. Define the B-tree indexes so that they are stored in DB areas IDXDAT_002 to IDXDAT_005, respectively.

  • Branch store code (SHOP_ID column) of the shop table (SHOPLIST)

  • Product ID (ITEM_ID column) of the product table (ITEMLIST)

  • Customer number (CUSTOMER_ID column) of the customer table (CUSTOMER)

  • Trade time (TRADE_TIME column) of the trade time table (TRADETIME)

(d) Table used in this operation example

The following figure shows the purchasing history table (PURCHASE) used in this operation example.

(a) Identifying the chunks to be merged

To determine the chunk IDs of the chunks to be merged, retrieve the system table STATUS_CHUNKS as explained below.

The following shows an SQL statement example.

■ SQL statement example

SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" TC
   WHERE "TABLE_SCHEMA"='ADBUSER01' AND "TABLE_NAME"='PURCHASE' 
      AND "CHUNK_COMMENT"='January 2014_daily'

■ Retrieval result

 CHUNK_ID
 --------------------
                    1
           (omitted)
                   31

Explanation:

In this example, the chunk IDs (1 to 31) of the chunks to be merged are identified by using the chunk comment ('January 2014_daily'), added during background import, as the key during retrieval of the STATUS_CHUNKS table.

Check the chunks to be merged and their chunk IDs based on the explanation in 11.4.8 Checking the chunk status and the number of chunks created.

(b) Checking whether chunk reorganization is necessary

For the merge-target chunks identified in (a) Identifying the chunks to be merged, you can check whether reorganization of the chunks is necessary. To do this, use the adbdbstatus command to obtain information about the need for reorganization.

The following shows an example of executing the command.

■ Command execution example

adbdbstatus -d reorginfo -n ADBUSER01.PURCHASE -c 1-31

Explanation:

In this example, the command obtains the information about the need for reorganization in terms of the chunk IDs of the merge-target chunks (1 to 31).

The information about the need for reorganization (Reorganization_necessity) is output for each chunk. If Reorganization_necessity is Recommended, perform reorganization of the chunk. For details about the procedure for reorganizing chunks, see (c) Reorganizing chunks.

After reorganizing chunks, merge them into a single chunk as described in (d) Merging chunks.

(c) Reorganizing chunks

As a result of the check in (b) Checking whether chunk reorganization is necessary, if you find that there are chunks for which the need for reorganization (Reorganization_necessity) is Recommended, reorganize them. This subsection describes the chunk reorganization procedure in an example case where the chunk with the chunk ID 2 needs to be reorganized.

■ Procedure for reorganizing chunks

1. Stop the commands, jobs, and application programs that update the purchase history table (PURCHASE).

2. Search the system table STATUS_CHUNK, and check the status and comment of the chunk with the chunk ID 2.

   ■ Example of executing an SQL statement

   SELECT CHUNK_ID, CHUNK_COMMENT, CHUNK_STATUS
     FROM "MASTER"."STATUS_CHUNKS"
       WHERE "TABLE_SCHEMA"='ADBUSER01' AND "TABLE_NAME"='PURCHASE'
         AND "CHUNK_ID"=2

   ■ Execution result of the SQL statement

   CHUNK_ID    CHUNK_COMMENT CHUNK_STATUS
   ----------- ------------- ------------------------
             2 daily         Normal

3. Use the adbexport command to output the data of the chunk with the chunk ID 2.

   Execute the command twice: once by specifying the export option file (/home/adbmanager/exp_file/exp_opt_file01.txt), and once by specifying the output data path file (/home/adbmanager/exp_file/exp_filepath.txt).

   ■ Command execution example

   adbexport -u ADBUSER01 -p '#HelloHADB_01'
             -z /home/adbmanager/exp_file/exp_opt_file01.txt
             -n PURCHASE
             -c 2
             /home/adbmanager/exp_file/exp_filepath.txt

   ■ Content of the output data path file (/home/adbmanager/exp_file/exp_filepath.txt)

   /home/adbmanager/exp_file/purchase_data0.csv
   /home/adbmanager/exp_file/purchase_data1.csv
   /home/adbmanager/exp_file/purchase_data2.csv
   /home/adbmanager/exp_file/purchase_data3.csv
   /home/adbmanager/exp_file/purchase_data4.csv
   /home/adbmanager/exp_file/purchase_data5.csv
   /home/adbmanager/exp_file/purchase_data6.csv
   /home/adbmanager/exp_file/purchase_data7.csv
   /home/adbmanager/exp_file/purchase_data8.csv
   /home/adbmanager/exp_file/purchase_data9.csv

4. Use the adbimport command to import the output data.

   Execute the command twice: once by specifying the import option file (/home/adbmanager/imp_file/imp_opt_file01.txt), and once by specifying the input data path file (/home/adbmanager/exp_file/exp_filepath.txt).

   For the input data path file, use the output data path file, as is, that you used when executing the adbexport command. For the -m option, specify the string 'daily' of the chunk comment that you checked in step 2.

   ■ Command execution example

   adbimport -u ADBUSER01 -p '#HelloHADB_01' -k "'" -s , -g 10
             -w /home/adbmanager/tmp
             -z /home/adbmanager/imp_file/imp_opt_file01.txt
             -b --status wait
             -m 'daily'
             PURCHASE
             /home/adbmanager/exp_file/exp_filepath.txt

5. Use the adbchgchunkstatus command to change the status of the chunk.

   Change the status of the reorganization-target chunk (chunk with the chunk ID 2) and the chunk that you created in step 4 (chunk with the chunk ID 32).

   ■ Command execution example

   adbchgchunkstatus -u ADBUSER01 -p '#HelloHADB_01'
                     -w 2 -n 32 PURCHASE

   When the preceding command is executed, the chunk with the chunk ID 2 is placed in wait status, and the chunk with the chunk ID 32 is placed in normal status.

6. Use the PURGE CHUNK statement to delete the reorganization-target chunk (chunk with the chunk ID 2).

   ■ Example of executing an SQL statement

   PURGE CHUNK PURCHASE WHERE CHUNKID=2

7. Start the commands, jobs, and application programs that update the purchase history table (PURCHASE).

(d) Merging chunks

To merge chunks, use the adbmergechunk command. The following shows an execution example of the adbmergechunk command.

■ Command execution example

adbmergechunk -u ADBUSER01 -p '#HelloHADB_01' -g 2
              -w /home/adbmanager/tmp
              -z /home/adbmanager/merge_file/merge_opt_file01.txt
              -m 'January 2014'
              -c 1-31
              PURCHASE

Explanation:

Using the value specified in the merge chunk option file (/home/adbmanager/merge_file/merge_opt_file01.txt) as the input information, perform chunk merging on the purchasing history table (PURCHASE) as specified in the -c option of the adbmergechunk command.

In this example, the data for January 2014 is grouped together by merging chunks with chunk IDs 1 through 31 into a single chunk.

The following figure shows an example of chunk merging.

Chunks with chunk IDs of 1 through 31 are automatically deleted when the adbmergechunk command terminates normally.

Note

Merging chunks can improve retrieval performance. There is also a limit to the number of chunks that can be managed by a table or DB area. If the number of chunks is insufficient, you can reduce the number by merging multiple chunks into a single chunk.

You can merge chunks even when another user is retrieving the merge-target chunks.

For details about the adbmergechunk command, see adbmergechunk (Merge Chunks) in the manual HADB Command Reference. Also, see 11.4.9 Merging chunks (to reduce the number of chunks).

(e) Checking whether reorganization of merged chunks is necessary

In this subsection, you merge the chunks of January 2014 into one chunk, and then execute the adbdbstatus command to check the information about the need for reorganization (Reorganization_necessity) of the chunk. The following shows the procedure.

Procedure

1. Check the chunk ID of the merge-result chunk.

   ■ Example of executing an SQL statement

   SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" TC
       WHERE "TABLE_SCHEMA"='ADBUSER' AND "TABLE_NAME"='PURCHASE'
         AND "CHUNK_COMMENT"='January 2014'

   ■ Execution result of the SQL statement

    CHUNK_ID
    -----------------
                   32

   Explanation:

   By executing the preceding SQL statement, you can check the chunk ID of the chunk into which the chunks of January 2014 have been merged. In this example, the chunk ID of the merge-result chunk is 32.

2. Check the information about the need for reorganization.

   ■ Command execution example

   adbdbstatus -d reorginfo -n ADBUSER01.PURCHASE -c 32

   This command outputs the information about the need for reorganization (Reorganization_necessity) of the chunk whose chunk ID is 32. If Reorganization_necessity is Recommended, perform reorganization of this chunk.

(a) Identifying the chunks with the earliest creation date and time

Chunks that are older than 6 months (chunks with the earliest creation date and time) are identified in the purchasing history table (PURCHASE). To identify these chunks in the purchasing history table, retrieve the system table STATUS_CHUNKS as described below.

The following shows an SQL statement example.

■ SQL statement example

SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" TC
   WHERE "TABLE_SCHEMA"='ADBUSER01' AND "TABLE_NAME"='PURCHASE'
      AND "CREATE_TIME"=(SELECT MIN("CREATE_TIME")
         FROM "MASTER"."STATUS_CHUNKS"
            WHERE TC."TABLE_SCHEMA"='ADBUSER01'
               AND TC."TABLE_NAME"='PURCHASE')

■ Retrieval result

 CHUNK_ID
 --------------------
                   32
KFAA96404-I 1 rows were selected.

Explanation:

When the STATUS_CHUNKS table is retrieved, the result shows that the chunk with chunk ID 32 is the chunk with the earliest creation date and time (older than 6 months) in this example.

Note

When chunks are merged, the earliest creation date and time among the merge-source chunks is stored in the STATUS_CHUNKS table.

For details about how to retrieve data from the STATUS_CHUNKS table, see C.9 Searching system tables.

(b) Backup acquisition

Now that the chunk ID of the chunk with the earliest creation date and time in the purchasing history table (PURCHASE) has been identified as 32, back up the data in that chunk in a CSV file.

To make a backup, use the adbexport command. The following shows an execution example of the adbexport command:

■ Command execution example

adbexport -u ADBUSER01 -p '#HelloHADB_01'
          -z /home/adbmanager/exp_file/exp_opt_file01.txt
          -n PURCHASE
          -c 32
          /home/adbmanager/exp_file/exp_data_path01.txt

■ Content of the output data path file (/home/adbmanager/exp_file/exp_data_path01.txt)

/home/adbmanager/exp_backup/backup0001.csv
/home/adbmanager/exp_backup/backup0002.csv
/home/adbmanager/exp_backup/backup0003.csv

Explanation:

The data contained in the chunk with chunk ID 32 is backed up in CSV files (/home/adbmanager/exp_backup/backup0001.csv, /home/adbmanager/exp_backup/backup0002.csv, and /home/adbmanager/exp_backup/backup0003.csv).

In this example, since chunks are merged every month, one month's worth of data is backed up.

Note

Although you can back up the files into a single file, distributing the files into multiple files can improve performance by distributing the output-related overhead.

For details about the adbexport command, see adbexport (Export Data) in the manual HADB Command Reference. Also, see 11.4.5 Exporting data in units of chunks.