SAND CDBMS Tools Reference Guide
Data Loader (ndlm)

Exporting Data with ndlm

Data Export Overview

When exporting data, ndlm retrieves data from database tables and writes the data to a specified output file according to the formatting instructions in a specification file.

The following steps summarize the procedure for exporting data from a database to an output file using ndlm:

1. Formulate a SELECT statement that retrieves the data that you want to export. You can export data from more than one table, order the rows, restrict the rows with a WHERE clause, and so on. The SELECT statement must be included in the export specification file described in Step 3.
2. Decide on the desired arrangement of data written to the output file by ndlm. This layout must be described in the export specification file described in Step 3.
3. Create an export specification file. This file instructs ndlm to perform an EXPORT operation, identifies the output file and defines the output file record layout, and describes the data retrieval in terms of a SELECT statement.
4. Invoke ndlm with the proper instance-name and connection-name arguments, the appropriate user authorization (one that has SELECT privileges on each source table), and the export specification file name. Ndlm invocation flags take precedence over any conflicting options included in the specification file.

Creating an Export Specification File

An export specification file instructs ndlm to perform an export operation, specifies the format of the output file, and specifies a SELECT to be performed on tables in the database. The export specification file is an ASCII text file that can be created using most standard text editors or word processors. It has the following general structure:

Each element of the export specification file is described below.

Note:
Comment lines (beginning with //, or surrounded by /*... */ ) can be included anywhere in the export specification script.

EXPORT

The EXPORT keyword instructs ndlm to perform an export operation. Make sure that a space separates the keyword from the @ symbol.

@output-file-name

The output-file-name argument identifies the ASCII output file. It must be prefixed by the "at" symbol (@), and may include a full path specification.

{ }

Braces are used to group components of the specification file. They must appear in the relative positions indicated in the export specification structure outline. In the outline, braces are placed on separate lines to make the file more readable. Tabs, spaces, and line feed characters are treated by ndlm as "white space" and may be used to make the file easier to read.

RECORD

The RECORD keyword is required in all export specification files; it indicates that the record specification follows.

Record specification

The record specification describes the record layout of the data written to the output file. The record specification is described in greater detail in the following section, Export Record Specification.

SELECT statement

The SELECT statement argument must be a single, legal SAND CDBMS SQL SELECT statement for the specified database. All table names referenced in the SELECT statement must reside in the database named in the ndlm invocation. As shown in the outline above, the statement must be terminated by a semicolon ( ; ). Refer to the SAND CDBMS SQL Reference Guide for a complete description of SELECT statement syntax and usage.

Note that if more than one SELECT statement is included (each terminating with a semicolon), only the first will be processed, and no error or warning message will be issued.

 

Export Record Specification

In an ndlm export specification file, a record specification describes the mapping between database columns and export file fields:

column-name . . . column-name

field-format . . . field-format

There must be exactly one column-name/field-format pair for each desired field in the output ASCII file. The column-name and field-format entries must be separated by "white space" (that is, blank spaces or tabs). The columns are exported in the order in which they appear in the record specification.

column-name

The column-name entries identify individual columns returned by the SELECT statement included in the export specification file. The name may be qualified with a table name (that is, in the format table-name.column-name).

field-format

Each field-format entry specifies the length of a field (in characters) and how it is to be delimited. Refer to the Import Record Specification section for information about the rules relating to field-format specifications. When a field width of zero (0) is specified, an export is performed with no output for that particular field. Variable-length fields can be declared for export.

Spaces appearing at the beginning or end of an output field will be trimmed off by ndlm.

Export Example

The following example illustrates each of the steps required to export data to an ASCII file from a database using ndlm. This example assumes that the source database already exists and contains data. The database name is testdb and was started using the SAND CDBMS connection server1_testdb.

The Export Source Table

The table called friend (in the people schema) from which data will be retrieved has the following structure:

age INTEGER
first_name CHAR(10)
last_name CHAR(20)

Creating the Export Specification File

The export specification file has the following appearance:

EXPORT @names.txt
{

RECORD
{
first_name 10:\t
last_name (*)\n
}
SELECT first_name, last_name FROM people.friend;

}

This export specification file instructs ndlm to perform the following operations:

1. Execute the SELECT statement.
2. Write the records returned to the output file as specified in the export record specification.

Each row of data written to names.txt will have two fields: the first is a fixed-length field of 10 characters followed by a colon and a tab character; the second is variable-length field enclosed in parentheses and ending with a line feed character.

The ASCII Output File

After ndlm executes the export operation, the ASCII output file (names.txt), will have the following appearance:

George    : (Smith)
Martha    : (Jones)
Mary      : (Lincoln)
John      : (Marshall)

Each row ends with a line feed character, and the first name is separated from the last name by a colon and a tab character.

ndlm Invocation Example

Suppose that the export specification file is called names.exp as in the example described above. This example shows how to connect to the database testdb, started using the SAND CDBMS connection server1_testdb, and export all rows from the people.friend table under the user authorization DBA.

Invoke ndlm from the operating system command prompt as follows:

ndlm server1_testdb testdb DBA names.exp