The SQLDUMP utility is a command‑line utility that dumps application data from SQL tables into one or more files. You can load the data from the files into another database with the SQLLOAD utility. The SQLDUMP utility does not dump data from ABL tables. The utility is available for only those tables that were created with SQL.
To dump data from multi-tenant tables to one or more files, the utility enables you to use the new command line option (-n) to specify the list of tenants for which the table data must be dumped.
You can also use the SQLDUMP utility to dump data of multi-tenant groups from the existing multi-tenant database.
You can also use the SQLDUMP utility to dump data from partitioned tables at the partition level. For more information, see the .
The following example shows how to connect using the SQLSchema utility with a blank password.
sqlload -t PUB.Customer -u userid -a \'\' progress:T:localhost:port:sports2000
Note: \'\' should be used when connecting using a blank password
The SQLDUMP utility writes user data in row order into ASCII records with variable‑length format. The column order in the files is identical to the column order in the tables. The utility writes both format and content header records to the dump file. You can dump multiple tables in a single execution by specifying multiple table names, separated by commas. Make sure there are no spaces before or after commas in the table list.
Data for one table always goes to a single dump file. Each dump file corresponds to one database table. For example, if you specify 200 tables in the SQLDUMP command, you will create 200 dump files. The SQLDUMP utility assigns the filenames that correspond to the owner_name and table_name in the database, with the file extension .dsql. If a dump file for a specified table already exists, it will be overwritten and replaced. Dump files are created in the current working directory.
The format of each record in a dump file is similar to the ABL .d file format, in that it:
Converts all values to character representation
Delimits CHARACTER values with double quotes
Can contain any embedded characters except NULL values, allowing commas, new lines, and other control characters
Uses two sets of double quotes to escape embedded double quotes
Delimits NUMERIC and other noncharacter data types using a space
Processes TIMESTAMP data as if it were CHARACTER data
Has a size limit of 2K for a single column value
Has a maximum record length of 32K for dump file records
Any error is a fatal error, and SQLDUMP halts the dumping process so that data integrity will not be compromised. SQLDUMP reports errors to standard output.
After successful processing, SQLDUMP writes a summary report to standard output. For each table SQLDUMP processes, the report shows:
Table name
Dump filename
Number of records dumped
Number of bytes dumped
Number of seconds required for processing
Example: SQLDUMP from selected tables
The following example directs the SQLDUMP utility to write the data from two tables to two dump files. The user_name and password for connecting to the database are tucker and sulky. The tucker account must have the authority to access the customers and products tables in database salesdb with owner_name martin.
sqldump -u tucker -a sulky -t martin.customers,martin.products
progress:T:thunder:4077:salesdb
Example: SQLDUMP based on table names
The following example directs the SQLDUMP utility to write the data from all tables in the salesdb database that begin with any of these strings: cust, invent, and sales, and having any owner name that the user tucker has authority to access. The user_name and password for connecting to the database are tucker and sulky.
sqldump -u tucker -a sulky -t%.cust%,%.invent%,%.sales%
progress:T:thunder:4077:salesdb
The following example directs the SQLDUMP utility to write the data from all tables for all owner names in the salesdb database.
Example: SQLDUMP of entire database
sqldump -u tucker -a sulky s-t %.% progress:T:thunder:4077:salesdb
Note: The database_name must be the last parameter given. Each dump file records character set information in the identifier section of each 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 ABL-supported character set name. The name is not case-sensitive.
SQLDUMP does not support the following characters in schema names:
Double quote (")
Forward slash (/)
Backslash (\)
SQLDUMP, however, does support schema names that contain special characters such as a blank space, a hyphen (-), or a 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.
So that the command line does not strip the quotes, 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 dump the table Yearly Profits, use the following UNIX command‑line syntax:
sqldump -t "\"Yearly Profits\"" -u xxx -a yyydatabase_name
In Windows, the command interpreter rules for the use of double quotation marks varies from UNIX.
By default, SQLDUMP displays promsgs messages using the code page corresponding to code-page-name. That is, if you are dumping a Russian database, and code-page-name specifies the name of a Russian code page, the client displays promsgs messages using the Russian code page (unless you specify a different code page by setting the client's SQL_CLIENT_CHARSET_PROMSGS environment variable).
Converts all values to character representation
