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 can dump data from both SQL-only tables and ABL tables.

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. For more information, see Dump Multi-tenant tables.

You can also use the SQLDUMP utility to dump data from partitioned tables at the partition level. For more information, see Dump partitioned tables.

Note:
  • The SQLDUMP utility does not support dumping a table containing a CLOB or BLOB column. A request to dump a table with a CLOB or BLOB column will result in an error message, and that table will be skipped.
  • To dump a table containing a CLOB or BLOB column, use the DB binary dump/ PROUTIL utility. For detailed information, see PROUTIL DUMP qualifier topic of the Manage OpenEdge Databases guide.

Syntax

The SQLDUMP utility has the following syntax:

sqldump -u user_name[@domain_name ][ -a password]
 [ -C code-page-name]-t [owner_name]table_name1     
 [[,owner_name]table_name2, ...]  
 [ -n tenant_name1,...]  
 [ -g group_name1,...]   
 [ -p partition_name1,...]database_name
The following example demonstrates how to establish a connection using the SQLDUMP utility with a DBA user account and its associated password:
sqldump -t PUB.Customer -u <dba_user> -a <dba_password> progress:T:<hostname>:<database port>:<database name>
Note: When connecting with a blank password, you can specify the password as either '' (two single quotes) or \'\'. Both represent an empty string and are interpreted as a valid blank password by the SQL utility.

The SQLDUMP 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.

For example, if the password for connecting to the database using the SQLDUMP utility is progress, you can generate the encoded credentials with the ae2h1 prefix as follows: 
genpassword -password progress -prefix ae2h1
As a result, the genpassword utility generates an ae2h1 encoded password:
ae2h1::e41027ea0710535e4bd4e2987d5647c95c1dd9988a7b5a4c18ec208017e7b67a9f4546afa452ff0d332f1fa703101923
You can now connect to the database using the SQLDUMP utility by running the following command:
sqldump -t pub.customer  -u user1 
-a ae2h1::e41027ea0710535e4bd4e2987d5647c95c1dd9988a7b5a4c18ec208017e7b67a9f4546afa452ff0d332f1fa703101923  
progress:T:localhost:9889:test

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 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 -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.

Example: Dump file

The following example depicts a dump file.

A^B^CProgress     sqlschema       v1.0       Quote fmt
A^B^CTimestamp    1999-10-19   19:06:49:0000
A^B^CDatabase     dumpdb.db
A^B^CProgress Character Set: iso8859-1
A^B^CJava Charcter Set: Unicode UTF-8
A^B^CDate Format: MM/DD/YYYY

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 -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.

By default, SQLDUMP displays promsgs messages using the code page corresponding to code-page-name. That is, if you are dumping 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).