This chapter describes the c-treeSQL database dump utility, dbdump.
dbdump writes the data in a database to a file. The format of the exported data is specified by the record description given in an input command file to dbdump.
Both dbload and dbdump commands files use DEFINE RECORD statements with similar syntax to specify the format of loaded or exported data records. The commands file specifies the data file, the format of data records, and the destination (or source) database columns and tables for the data.
Prerequisites for dbdump
Before running dbdump, you need:
- A valid, readable commands file
- SELECT privileges on the tables named in the commands file
dbdump Command-Line Syntax
The dbdump command accepts the commands file name, the database name and a command option.
Syntax
dbdump -f commands_file [-u user_name] [-a password] [-n] [‑z] [-p] [‑l] database_name
Options
- -f commands_file - Specifies the file containing dbdump commands.
- -u user_name - The user name c-treeSQL uses to connect to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_USER environment variable specifies the default user name. If DH_USER is not set, the value of the USER environment variable specifies the default user name.)
- -a password - The password c-treeSQL uses to connect to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_PASSWD environment variable specifies the default password.)
- -n - Parse the commands file and display errors, if any, without exporting data. If the parsing is successful, the following message displays on stdout:
No errors in the commands file.
- ‑z (V10.3 and later) - The number of records to fetch per call is controlled by the ‑z switch. To be consistent with the switch of dbload, it defaults to 1.
- ‑l (V10.3 and later) - The ‑l switch controls the progress output frequency in terms of record reads (the output is generated whenever the number of record reads since the last output becomes greater than or equal to the specified value).
- -p (V10.3 and later) - The dbdump -p command-line switch activates the new query passthru mechanism, which does not perform any major change in the query. When this switch is in use, dbdump interprets the query in the command file, converts it to lowercase, and wraps everything that is considered an identifier with double-quotes. This can cause a syntax error if the command file contains functions. For example, consider the following statement:
SELECT RTRIM(cm_custnumb),...
The default behavior of dbdump results in the following statement, which would cause a syntax error:
select "rtrim" ( "cm_custnumb" ) ,...
Using the dbdump -p switch results in this statement, which does not cause an error:
select rtrim ( cm_custnumb ) ,...
- database_name - Name of the database.
- -B - Use BOM (Unicode Byte Order Mark) in output file.
- -S BASIC | <cert_filename> - Basic TLS encryption or cross checked authentication using <cert_filename>
Automatic Record Definition
Customers have a need to export all data in a CSV format. dbdump is great for this purpose. (In general, dbdump primary usage is with SELECT * FROM ...) However, with large tables, there is a lot of setup work to create the DEFINE RECORD information. For example, for a table with more than 100 columns, they have to manually describe the complete output structure for all fields.
In V11.5 and later we have introduced a new syntax in the DBDUMP script to automatically generate the record definition. The syntax definition for the commands file is as shown:
dbdump_commands:
define_record_statement
for_record_statement
where define_record_statement is either the following (existing syntax):
DEFINE RECORD record_name
[ OF FIXED LENGTH record_length
AS (
field_name position_specification
type_specification,
...
)
]
[ FIELD DELIMITER delimiter_char ]
[ RECORD DELIMITER delimiter_string ]
or the following (new added syntax):
AUTODEFINE RECORD record_name
[ FIELD DELIMITER delimiter_char ]
[ RECORD DELIMITER delimiter_string ]
[FIELDS PER LINE number ];
Unicode
The dbdump Data Unload and dbload Data Load utilities properly dump and load data out of a Unicode-enabled FairCom DB SQL Server. The commands file must be in ASCII format. The output file generated by dbdump is in Unicode format with an optional Byte Order Mark (BOM) indicating the Unicode encoding form (using the ‑B command-line switch).
The input file for dbload needs to be in Unicode (native "wchar" encoding form) with an optional BOM, in which case the utilities check for the proper format.
Data File Formats
The output data file can be defined to be having one of the following record formats:
- Variable length records
- Fixed length records
For both these types of records an optional field delimiter and an optional record delimiter can be specified. The field delimiter, when specified, should be a single character. By default, comma is the field delimiter. The record delimiter can be specified in the commands file and it can be more than one character. By default, the newline character, \n, is the record delimiter.
Variable Length Records
For variable-length records, the fields in the data file can be of varying length. Unless the keyword FIXED is used in the commands file, it is assumed that the dbload record processing will be for variable-length records.
Fixed Length Records
For fixed-length records, the fields in the data file must be of fixed length. The length of the record must be the same for all records and is specified in the commands file. In case of fixed-length records, the field and record delimiters are ignored. That is, the POSITION specification must be such that the delimiters are ignored. For more information on the commands file refer to The Commands File.
The data files that contain fixed-length records can either be ASCII or binary files.
The Commands File
The commands file specifies:
- Record format for the output file
- Query which is to be used for exporting data
There is no file naming convention for the commands file. For example, the commands file name to load the ORDERS table could be orders.cmd.
The commands file must contain the following parts:
- The DEFINE RECORD statement
- The FOR RECORD statement
The syntax definition for the commands file is as shown:
dbdump_commands:
define_record_statement
for_record_statement
The following is sample commands file showing dump instructions.
DEFINE RECORD ord_rec AS
( ord_no, item_name, date, item_qty ) FIELD DELIMITER ' ' ;
FOR RECORD ord_rec dump into ord_dat
USING SELECT order_no, product, order_date, qty
FROM items;
The DEFINE RECORD Statement
The DEFINE RECORD statement is used to define the record of the output file. The following are the definitions that are made known by the DEFINE RECORD statement:
- Names the record of the output file
- Names the fields of the record
- Specifies whether the records in the data file are variable length records or fixed length records
- If fixed length records, specifies the position and data type of the field
The following is the syntax definition of the DEFINE RECORD statement:
DEFINE RECORD record_name
[ OF FIXED LENGTH record_length
AS (
field_name position_specification type_specification,
...
)
]
[ FIELD DELIMITER delimiter_char ]
[ RECORD DELIMITER delimiter_string ] ;
position_specification::
POSITION ( start_position : end_position )
type_specification::
| CHAR
| SHORT
| LONG
| FLOAT
| DOUBLE
The following are the variable descriptions of the DEFINE RECORD syntax:
- record_name is the name used to refer to the records found in the data file.
- record_length is the length of the fixed length record. This length should include the length of field or record delimiters, if any.
- field_name is the name used to refer to a field in the data file.
- delimiter_char is the field delimiter and is a single character. delimiter_char must be specified as a literal.
- delimiter_string is the record delimiter and can be a single character or a string. It must be specified as a literal.
- start_position is the position where the field starts. It must be an unsigned integer.
- end_position is the position where the field ends. It must be an unsigned integer.
The first position of each record is 1 and not 0.
If DATE, TIME, and TIMESTAMP types are to be dumped they can be specified as characters in the commands file. If it is a fixed length record then the type specification can be CHAR.
The following is an example of the DEFINE RECORD statement for fixed length records:
DEFINE RECORD rec_one OF FIXED LENGTH 20
AS (
fld1 POSITION (1:4) SHORT,
fld2 POSITION (5:15) CHAR,
fld3 POSITION (16:20) CHAR
) ;
See also:
- Automatic Record Definition in the topic titled dbdump Command-Line Syntax.
The FOR RECORD Statement
The FOR RECORD statement writes each valid record into the data file after selecting the record from the database. The syntax for the FOR RECORD statement is shown below:
FOR RECORD record_name DUMP INTO data_file_name
USING select_statement ;
The following are the variable descriptions of the FOR RECORD statement:
- record_name specifies the same name used in the associated DEFINE RECORD statement.
- data_file_name is the name of the output data file name.
- select_statement is any valid SELECT statement.
Examples
This section gives different types of examples for dbdump, both for variable length records as well as fixed length records. The data files can either be ASCII or binary files. If they are binary files they must be in the fixed length record format.
The following shows the commands file to write records from the DEPT table. The output data file name is deptrecs_out which is an ASCII file in the variable length record format.
DEFINE RECORD dept_rec AS
( no, name, loc ) FIELD DELIMITER ' ' ;
FOR RECORD dept_rec DUMP INTO deptrecs_out
USING SELECT dept_no , dept_name , location
FROM ADMIN.dept ;
The following shows the commands file to write records from the CUSTOMER table. The output data file is cust_out which is a binary file in the fixed length record format.
DEFINE RECORD cust_rec OF FIXED LENGTH 37
AS (
no POSITION (1:4) LONG,
name POSITION (5:15) CHAR,
street POSITION (16:28) CHAR,
city POSITION (29:34) CHAR,
state POSITION (35:36) CHAR
) ;
FOR RECORD cust_rec DUMP INTO cust_out
USING SELECT cust_no, cust_name, cust_city, cust_street, cust_state
FROM ADMIN.customer ;
The following shows the commands file to dump records from the ORDERS table. The output data file is orders_out which is a binary file in the fixed length record format.
DEFINE RECORD orders_rec OF FIXED LENGTH 31
AS (
no POSITION (1:4) LONG,
date POSITION (6:16) CHAR,
prod POSITION (18:25) CHAR,
units POSITION (27:30) LONG
) ;
FOR RECORD orders_rec DUMP INTO orders_out
USING SELECT order_no, order_date, product, quantity
FROM ADMIN.orders ;