Progress
Database Administration
Guide and Reference


SQLLOAD Utility

A command-line utility that loads user data from a formatted file into an SQL-92 database. Typically, the source file for the load is created by executing the SQLDUMP utility. The SQLLOAD utility can process a source file created by another application or utility, if the format of the file conforms to SQLLOAD requirements. The file extension made available to SQLLOAD for processing must be .dsql. See the entry on SQLDUMP for a description of the required file format.

Before you can execute SQLLOAD against a database server, the server must be configured to accept SQL connections and must be running. See "Creating and Deleting Databases," for instructions on creating a database and "Starting Up and Shutting Down," for information about starting a Progress database or database server.

SYNTAX

Operating System
Syntax
UNIX
Windows
sqlload -u user_name [ -a password ]  
    -t [ owner_name.]table_name1 
    [ [,owner_name.]table_name2, ... ]  
    [ -l log_file_name ] 
    [ -b badfile_name ] 
    [ -e max_errors ] 
    [ -s skipcount ] 
    [ -m maxrows ] 
    [ -F comma | quote ]  
    database_name   

-u user_name

Specifies the user SQLLOAD uses to connect to the database. If you omit the user_name and password, SQLLOAD prompts you for these parameter values. If you omit the user_name and supply a password, SQLLOAD uses the value defined in the USER environment variable.

-a password

Specifies the password used by the database for authentication.

-C code-page-name

A case-insensitive character string that specifies the name of the dump file’s code page. If the -C parameter specifies a code page name that is not valid, Progress reports a run-time error. If the -C parameter does not appear at all, the code page name defaults to the client’s internal code page, which is:

-t owner_name.table_name

Specifies a list of one or more tables to load into a database. This parameter is required. Pattern matching is supported, using a percent sign (%) for multiple characters and an underscore (_) for a single character. The pattern matching follows the standard for the LIKE predicate in SQL-92. You can load a single table, a set of tables, or all tables. If you omit the optional owner_name table qualifier, SQLLOAD uses the name specified by the -u parameter. The files from which SQLLOAD loads data are not specified in the SQLLOAD syntax. The utility requires that the filename follow the naming convention owner_name.table_name.dsql.

-l log_file_name

Specifies the file to which SQLLOAD writes errors and statistics. The default is standard output.

-b badfile_name

Specifies the file where SQLLOAD writes rows that were not loaded.

-e max_errors

Specifies the maximum number of errors that SQLLOAD allows before term processing. The default is 50.

-m maxrows

Directs SQLLOAD to stop loading rows at the specified number.

-n

Directs SQLLOAD to check for syntax errors without loading any rows.

-F comma | quote

Directs SQLLOAD to load data in comma-delimited format or quote-delimited format. The default is quote.

database_name

Identifies the database where you are loading tables. You can load tables into a single database each time you invoke SQLLOAD. There is no option flag preceding the database_name. This parameter is required, and must be the last parameter specified. The database name is specified like a JDBC-style URL: db_type:T:host:portnum:dbname.

The SQLLOAD utility reads application data from variable-length text-formatted files and writes the data into the specified database. The column order is identical to the table column order. SQLLOAD reads format and content header records from the dump file. You can load multiple tables in a single execution by specifying multiple table names, separated by commas. Data for one table is from a single dump file. Every source file corresponds to one database table. For example, if you specify 200 tables in the SQLLOAD command, you will load 200 database tables.

The format of the records in the input files is similar to the Progress 4GL .d file dump format. See the "SQLDUMP Utility" section in this chapter for a description of the record format. The maximum record length SQLLOAD can process is 32K.

Each database record read is share-locked, for consistency. You must ensure that the SQL Server has a lock table large enough to contain one lock for every record in the table. The default lock table size is 10,000 locks.

SQLLOAD writes any errors to standard output and halts the loading process for any error so that data integrity is not compromised.

EXAMPLES

This example directs the SQLLOAD utility to load the data from two dump files into the salesdb database. The input files to SQLLOAD must be tucker.customers.dsql and tucker.products.dsql:

sqlload -u tucker -a sulky -t tucker.customers,tucker.products 
progress:T:thunder:4077:salesdb 

This example directs SQLLOAD to load the data from all appropriately named dump files into the specified tables in the salesdb database:

sqlload -u tucker -a sulky -t %.cust%,%.invent%,%.sales% 
progress:T:thunder:4077:salesdb 

NOTES


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095