SAND CDBMS Tools Reference Guide
Data Loader (ndlm)

Import Map Specification

The map specification, contained in the map specification (table-name) section of the load specification file, maps or assigns fields listed in the import record specification to columns in the database table; it can also populate columns with the values of NDL++ expressions. The column-field pairs must be separated by at least one blank space or a tab character, and may be listed in any order.

The general form of the map specification is as follows:

column-name . . . column-name

expression . . . expression

column-name

Each column-name entry must precisely match a column name in the table being loaded. A column name may not appear more than once in the map specification. Not every column in the table must appear in the map specification.

expression

The expression can be either a field label on its own, or any NDL++ statement as described below. It can also be a numeric or string constant, in which case that constant will be inserted in the target column for each record of the table.

field label
If the expression consists of a field label on its own, it must precisely match a field label in the record specification. A field label may be assigned to more than one column, or it may be omitted from the map specification altogether.

NDL++ expressions
The map specification may contain NDL++ statements, which use variables, functions, and conditional expressions to perform a variety of data manipulation and conversion operations. These statements are described briefly in the following section. For a complete description of NDL++ statements, see the NDL++ Data-Load Scripting Functions section.

@@
The predefined '@@' constant references the name of the data file from which the current record is being loaded. This could be useful if multiple data files are used in the load operation, and you wish to associate each record of data with the name of the data file where the record originated.

#
The predefined '#' constant is the number (minus one) of the record currently being loaded. The first record loaded from a flat file is 0, the second is 1, and so on, to the end of the flat file (n -1). If multiple flat files are being processed in the same operation, the numbering sequence continues to the next flat file. So, for example, if the first flat file loaded has 100 records (record numbers 0–99), the record numbers from the next flat file will start at 100. Note that the numbering sequence will not carry over to subsequent load operations; the next separate load operation to the same table that uses the '#' constant will start over at record number 0.

The '#' constant should not be used with the ndlm -j option (set the number of reading threads in Parallel Loader mode). Because of the way that the reading threads option works, there is no guarantee of unique record numbers for the loaded data. As well, the starting record number could be very large (rather than 0), and there would be no predictable relationship between a record's row number in the source flat file and the loaded record number.

Inserting a Constant

To insert a constant numeric or string value into a table during the data load process, specify the appropriate column name along with the literal value in the table map section of the script. String constants must be enclosed in single quotes, as in the following example:

import @test.txt
{
record
{
col1 *,
col2 *,
}
test_table
{
country_code 'UK'
service_code 6
client_number col1
service col2
}
}

In this example, the string UK will be inserted into the country_code column (character data type) and the number 6 will be inserted into the service_code column (numeric data type) for each row loaded into test_table.

Note:
To insert a null value for each row in a column, simply omit the column name from the table map section. The column must not have a NOT NULL constraint defined on it, or else the load will fail with a data exception error.