A command-line utility that loads user data from a formatted file into an SQL database.

Syntax

sqlload -u user_name[ -a password][ -C code_page_name]
      -t [owner_name.]table_name1[[,owner_name.]table_name2, ...]
       [ -l log_file_name][ -b badfile_name][ -e max_errors]
       [ -F comma | quote ][ -c commit_frequency]
       [ -n tenant_name1,...]
       db_name

Parameters

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.

The SQLLOAD utility accepts all encoding prefixes supported by the genpassword utility for encoding passwords before transmitting them over the network to the OpenEdge SQL server for authentication. If the password is provided in plain text, it is encoded using ae2h4 before being transmitted over the network.

For more information, see genpassword and Encoding prefix.

-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, a run-time error is reported. If the -C parameter does not appear at all, the code page name defaults to the client's internal code page:

  • If set, the value of the client's SQL_CLIENT_CHARSET environment variable
  • If not set, the name of the code page of the client's locale

For example, you might use the -C parameter to load a dump file whose code page is ISO8859-2, using a Windows client whose code page is MS1250. Although you can accomplish this by setting the client's SQL_CLIENT_CHARSET environment variable, using the -C parameter might be easier.

-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. 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.
-F comma | quote
Directs SQLLOAD to load data in comma-delimited format or quote-delimited format. The default is quote.
-c commit_frequency
Specifies the number of records written to the database before a commit is performed. Committed records cannot be rolled back if an error occurs during the load.
-n tenant_name
Specifies a list of one or more tenants to dump to a file. This parameter is optional.
db_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 db_name. This parameter is required, and must be the last parameter specified. The database name is specified in the following way: progress:T:localhost:demosv:jo.

SQLLOAD loads user data from a formatted file into an SQL 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.

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 ABL .d file dump format. See SQLDUMP utility 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.

Example

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, as shown:

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

This example illustrates the creation of tenant directories when the tenants are dumped to a formatted file using SQLLOADutility: 

sqlload -u supertenUser@superdom -a superten  -t mttab1 -n ten1, ten2 
progress:T:localhost:9999:mtdb

If the regTenant is mapped to a regular tenant, then this example directs the SQLLOAD utility to load the data of the partition of the tenant to mttab1 table: 

sqlload -u regTenantUser@OpenEdgeA -a regTenant -t mttab1 
progress:T:localhost:9999:mtdb

If the dbaUser is a DBA, then this example directs the SQLLOAD utility to load the tenant-specific data for all the tenants in their respective directories: 

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

If the superTenUser is a super-tenant, then this example directs the SQLLOAD utility to load all the data associated with the tenants which start with the word ‘ten' of the table mttab1: 

sqlload -u superTenUser@superdom -a superten -t mttab1 -n ten% 
progress:T:localhost:9999:mtdb

If the dbaUser is a DBA, then this example directs the SQLLOAD utility to load all the data for tenants for the table mttab1: 

sqlload -u superTenUser@superdom -a superten -t mttab1 -n ten% 
progress:T:localhost:9999:mtdb

Notes

  • The db_name must be the last parameter given.
  • Before you can run SQLLOAD against a database server, the server must be configured to accept SQL connections and must be running.
  • The character set used by SQLLOAD must match the character set information recorded in each dump file. If the character sets do not match, the load is rejected. You can use the SQL_CLIENT_CHARSET environment variable to specify a character set.

    Each dump file you create with SQLDUMP contains character set information about that file. The character set recorded in the dump file is the client character set. The default character set for all non-JDBC clients is taken from the local operating system through the operating system APIs. JDBC clients use the Unicode UTF-8 character set.

    To use a character set different than that used by the operating system, set the SQL_CLIENT_CHARSET environment variable to the name of the preferred character set. You can define any OpenEdge-supported character set name. The name is not case sensitive.

  • At run time, SQLLOAD reports an error if it detects a mismatch between the code page of the dump file being loaded and the code page of the client running SQLLOAD.
  • By default, SQLLOAD displays promsgs messages using the code page corresponding to code_page_name. That is, if you are restoring a French database and code_page_name specifies the name of a French code page, the client displays promsgs messages using the French code-page (unless you specify a different code page by setting the client's SQL_CLIENT_CHARSET_PROMSGS environment variable).
  • SQLLOAD does not support the following characters in schema names:
    • Double quote (")
    • Forward slash (/)
    • Backslash (\)
  • SQLLOAD supports schema names that contain special characters, such as a blank space, a hyphen (-), or pound sign (#). These names must be used as delimited identifiers. Therefore, when specifying names with special characters on a UNIX command line, follow these rules:
    • Use double quotes to delimit identifiers.
    • Use a backslash (\) to escape the double quotes used for delimited identifiers.
    • Use double quotes to enclose any names with embedded spaces, commas, or characters special to a command shell (such as the Bourne shell). This use of quotes is in addition to quoting delimited identifiers.

    For example, to load the table Yearly Profits, use the following UNIX command-line:

    sqlload -u xxx -a yyy -t "\"Yearly Profits\"" progress:T:<hostname>:<database_port>:<database_name>
  • In Windows, the command interpreter rules for the use of double quotation marks varies from UNIX.