Previous
Topic:
The #INCLUDE Directive |
Next
Topic: Verifying and Filtering Data Using the PIC Clause |
The following simple examples address each of the steps required to load data from an ASCII file into a database using ndlm. This example assumes that the target database called testdb has been created and configured.
Creating the Destination TableA table named friend is created in the people schema of the testdb database as follows:
CREATE TABLE people.friend
(
age INTEGER,
first_name CHAR(10),
last_name CHAR(20)
);The ASCII file names.txt has the following appearance:
Ignore these 22 bytes
George , "Smith", M:57
Martha , "Jones", F:39
Mary , "Lincoln", F:78
John , "Marshall", M:15In this example, each record is terminated by a line feed character. Last names are separated from gender by a comma and a tab. The first 22 characters in the first row constitute the file header (including the line feed character) which will be ignored by ndlm during the import.
Note:
The above example applies to a UNIX-format input file. If names.txt was created under Windows, the end-of-record signifier would be the ASCII carriage return and line feed characters together (\r\n) instead of just the line feed character (\n). Because of this extra character, the first line would be "Ignore these 23 bytes" in the equivalent Windows example.
Creating the Import Specification FileThe import specification file has the following appearance; it is followed by an explanation of its contents:
IMPORT @names.txt
{IGNORE 22
RECORD
{
field_first 10,\s
field_last "*",\t
field_gender 1:
field_age 2\n
}
people.friend
{
first_name field_first
last_name field_last
age field_age
}}
This import specification file instructs ndlm as follows:
- The first line instructs ndlm to import from the file names.txt.
- The third line instructs ndlm to ignore the first 22 bytes in names.txt (21 characters plus the line feed character).
- The record specification indicates that each record in the input file consists of four fields defined, respectively, as follows:
- The first is a fixed-length field of 10 characters followed (that is, delimited) by a comma and a space
- The second is a variable-length field delimited by double quotation marks and followed by a comma and a tab
- The third is a fixed-length field of one character followed by a colon
- The fourth is a fixed-length field of two characters followed by a line feed.
- The map specification indicates that data is to be imported into the friend table (in the people schema) by assigning the contents of the field field_first to the first_name column, field_last to the last_name column, and field_age to the age column. Since the field_gender field is not included in the map specification, its contents are not loaded into the database by ndlm.
For the purposes of this example, assume that the specification file is called names.imp. To load all rows from names.txt into the people.friend table in the database called testdb, started using the SAND CDBMS connection server1_testdb, invoke ndlm from the operating system command prompt with the user name DBA as follows:
ndlm server1_testdb testdb DBA names.imp Note that if a user name other than DBA is used, that user must have at least SELECT and INSERT privileges on the target table.
ndlm displays a preview screen that shows the columns and their corresponding values for the first three records, and prompts for confirmation that the data is formatted correctly. If the prompt is answered affirmatively, the import proceeds and all the records are loaded into the friend table in the people schema. If answered negatively, the import terminates before any records are loaded into the friend table.
Additional Invocation Examples
Suppose you have already tested the import procedure and know that the data is formatted properly. You can suppress the verification prompts by specifying the -b flag as follows:
ndlm server1 -b server1_testdb testdb DBA names.imp
If you want to import only the first 20,000 rows of the input file, you could use the -p flag while suppressing the prompts with -b as follows:ndlm server1 -b -p 20000 server1_testdb testdb DBA names.imp
If you want to import rows 20,001 through 30,000 you could do so by using the -p and -s flags together as follows:ndlm server1 -b -s 20000 -p 10000 server1_testdb testdb DBA names.imp
Previous
Topic:
The #INCLUDE Directive |
Next
Topic: Verifying and Filtering Data Using the PIC Clause |