|
|
|
This section gives different types of examples for dbload, 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 example is the commands file to load records into the DEPT table. The input data file name is deptrecs_in which is an ASCII file in the variable length record format.
Example 1:
This example is showing an ASCII input file called name_in.txt that is using the '^' as it's field delimeter. The table "name" in this example has 3 columns:
name_number CHAR 8
name CHAR 47
gender CHAR 1
The commands.txt file is:
DEFINE RECORD name_rec AS
( name_number, name, gender ) FIELD DELIMITER '^' ;
FOR EACH RECORD name_rec FROM "name_in.txt"
INSERT INTO name (name_number, name, gender)
VALUES (name_number, name, gender) ;
NEXT RECORD
The name_in.txt file is:
1^James Smith^m
2^James Johnson^m
3^James Williams^m
4^James Brown^m
5^James Jones^m
6^James Miller^m
7^James Davis^m
8^James Garcia^m
9^James Rodriguez^m
10^James Wilson^m
The syntax for the dbload.exe command is:
dbload.exe -f commands.txt -u admin -a ADMIN 6597@localhost:ctreeSQL
Example 2:
DEFINE RECORD dept_rec AS
( dept_no, dept_name, location ) FIELD DELIMITER ' ' ;
FOR EACH RECORD dept_rec FROM deptrecs_in
INSERT INTO ADMIN.dept (no, name, loc)
VALUES (dept_no, dept_name, location) ;
NEXT RECORD
Example 3:
The following is the commands file to load records into the CUSTOMER table. The input data file is cust_in which is a binary file in the fixed length record format.
DEFINE RECORD cust_rec OF FIXED LENGTH 36
AS (
cust_no POSITION (1:4) LONG,
cust_name POSITION (5:15) CHAR,
cust_street POSITION (16:28) CHAR,
cust_city POSITION (29:34) CHAR,
cust_state POSITION (35:36) CHAR
) ;
FOR EACH RECORD cust_rec FROM cust_in
INSERT INTO ADMIN.customer (no, name, city, street, state)
VALUES (cust_no, cust_name, cust_city, cust_street, 'CA') ;
NEXT RECORD
Example 4:
The following is the commands file to load records into the ORDERS table. The input data file is orders_in which is a binary file in the fixed length record format.
DEFINE RECORD orders_rec OF FIXED LENGTH 30
AS (
order_no POSITION (1:4) LONG,
order_date POSITION (6:16) CHAR,
product POSITION (18:25) CHAR,
qty POSITION (27:30) LONG
) ;
FOR EACH RECORD orders_rec FROM orders_in
INSERT INTO ADMIN.orders (no, date, prod, units)
VALUES (order_no, order_date, product, qty) ;
NEXT RECORD
