Introduction to the new concept of DB2: Various file formats supported by IngestUtility

The Ingest application tool is a new concept in the DB2Galileo version. It is a client tool. You can use INGEST to IMPORT data, which has better performance than LOAD and IMPORT. This article introduces various file formats supported by IngestUtility and details IngestUtilitySQLstatement (insert, update, del

The Ingest application tool is a new concept in the DB2 Galileo version. It is a client tool. You can use INGEST to IMPORT data, which has better performance than LOAD and IMPORT. This article introduces various file formats supported by Ingest Utility, and details the Ingest Utility SQL statement (insert, update, del

INGEST is a new feature of DB2 Galileo (DB2 10 Development Code). It is a new concept introduced to have a large number of continuous data streams for real-time and concurrent fast data import. This article describes in detail various data file formats supported by INGEST, as well as supported data import operations such as insert, replace, update, delete, and merge.

Various file formats supported by Ingest Utility, usage, and instance analysis

The Ingest tool supports two data formats: DELIMITED and POSITIONAL. The syntax is shown in Listing 1. When the DELIMITED file format is selected, the Delimiter is followed. If it is not specified, the default Delimiter is ",", which is similar to import and load.

If the data in POSITIONAL file format is followed by the length of the data record, this length refers to the total length of each record captured each time, if the length specified by RECORDLEN is smaller than the length of each column, an error is reported and exited. If it is greater than the sum of the length of each column, the ingest tool ignores the bytes between the end of the last column and the specified length. The RECORDLEN can be specified in the range of 1-32767. If the RECORDLEN length is not specified, the carriage return or carriage return is used as the end of each record by default. After specifying the field name, you can use the POSITION keyword to specify the start POSITION of each field.

List 1: Definitions of data file formats supported by INGEST

 |--+-DELIMITED--+----------+-----------+------------------------>    |            '-BY--char-'           |      '-POSITIONAL--+-------------------+-'                    '-RECORDLEN--length-' field-definition  |--$field-name--+-----------------------------------+----------->                 '-POSITION--(--start--+--------+--)-'                                         '-:--end-'      

Listing 2 is an example of using the POSITIONAL format without specifying the RECORDLEN parameter. In the. asc file, the 12th bits in each row are not empty, and 17th to 19 characters also exist. However, because the start position of position is specified in the ingest statement, the ingest tool only takes the start position specified by position and does not care whether other positions contain characters. The RECORDLEN length is not specified here. Therefore, the Return key is the end position of a line by default.

Listing 2: positional ingest example

Data File. asc content 2 2001 552010aaa 25 2002 452011bbb 143 2003 2c2012ccc 1555 2004 3b2013ddd 1309 2005 7a2014eee result of executing the INGEST statement ingest from file format21_01.asc format POSITIONAL ($ field1 POSITION () int external, $ field2 POSITION () DATE 'yyyy', $ field3 POSITION () TIME 'h', $ field4 POSITION () TIMESTAMP 'yyyy ') restart off insert into t1 (perkey, perioddate, periodtime, periodts) values ($ field1, $ field2, $ field3, $ field4) SQL2979I The ingest utility is starting at "08/08/2012 01:34:10. 517287 ". SQL2914I The ingest utility has started the following ingest job: "DB21001: 20120808.013410.517287: 00002: 00004 ". number of rows read = 5 Number of rows inserted = 5 Number of rows rejected = 0 SQL2980I The ingest utility completed successfully at timestamp "08/08/2012 01:34:16. 322027 "db2 => select * from t1 perkey perioddate periodtime periodts ---------------- ---------- %25 01/01/2002 04:00:00 %1555 %03:00:00 %143 01/01/2003 02:00:00 %2 01/01/2001 05:00:00 %1309 %07:00:00 %5 record (s) selected.