Exporting Data to an SCT File
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:
- 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.
- 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.
- 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.
- 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:
<SELECT statement> ;
Each element of the export specification file is described below.
Comment lines (beginning with //, or surrounded by /*... */ ) can be included anywhere in the export specification script.
The EXPORT keyword instructs ndlm to perform an export operation. Make sure that a space separates the keyword from the @ symbol.
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.
The RECORD keyword is required in all export specification files; it indicates that the record specification follows.
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.
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:
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.
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).
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.
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:
Creating the Export Specification File
The export specification file has the following appearance:
SELECT first_name, last_name FROM people.friend;
This export specification file instructs ndlm to perform the following operations:
- Execute the SELECT statement.
- 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
Exporting Data to an SCT File