A command-line utility that dumps application data from SQL tables into one or more files.

Syntax

sqldump -u user_name[ -a password][ -C code_page_name]
        -t [owner_name.]table_name1 [[,owner_name.]table_name2,...]
        [ -n tenant_name1,...]
        db_name

Parameters

-u user_name

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

-a password
Specifies the password used by the database for authentication.

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.

-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, the utility 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:

  • 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 have a Windows client using the MS1250 code page produce a dump file using the ISO8859-2 code page (to read later on a UNIX machine, perhaps). 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 dump to a file. This parameter is required. Pattern matching is supported in both owner_name and table_name, using a percent sign (%) for one or more characters and an underscore (_) for a single character. The pattern matching follows the standard defined by the LIKE predicate in SQL.

You can dump a single table, a set of tables, or all tables. If you omit the optional owner_name qualifier, SQLDUMP uses the name specified by the -u parameter.

-n tenant_name
Specifies a list of one or more tenants to dump to a file. This parameter is optional.
db_name

Specifies the database where you are dumping tables. You can dump tables from one database each time you invoke SQLDUMP. 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.

SQLDUMP 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 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 the records in a dump file is similar to the ABL .d file format:

  • Converts all values to character representation
  • Delimits CHARACTER values with double quotes
  • Can contain any embedded characters except for NULL values, allowing commas, newlines, and other control characters
  • Uses two sets of double quotes to escape embedded double quotes
  • Delimits NUMERIC and other non-character 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

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

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

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

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

This example directs the SQLDUMP utility to write the data from all tables for all owner names in the salesdb database:

sqldump -u tucker -a sulky -t %.%  progress:T:thunder:4077:salesdb

This example directs the SQLDUMP utility to dump the data from the tenants ten1 and ten2 to two SQL dump files respectively. The user_name and password to connect to the database are supertenUser@superdom and superten. The supertenUser account in the superdom domain must have the authority to access the ten1 and ten2 tenant tables in mtdb database.

To separate the tenant specific data, SQLDUMPutility creates separate directories for each tenant. The ten1/<OWNER>.MTTAB1.DSQL and ten2/<OWNER>.MTTAB1.DSQL are the two directories that are created to dump tenant data:

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

If regTenantUser is mapped to a regular tenant, then this example directs the SQLDUMP utility to dump the data for the regTenantUser tenant's partition:

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

If dbaUser is mapped to a DBA, then this example directs the SQLDUMP utility to dump the tenant-specific data for all the tenants in their respective directory:

sqldump -u dbaUser -a dba -t mttab1 progress:T:localhost:9999:mtdb

If superten is mapped to a super-tenant, then this example directs the SQLDUMP utility to dump all the tenants which start with the word ‘ten' for the table mttab:

sqldump -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 SQLDUMP against a database server, the server must be configured to accept SQL connections and must be running.

  • Each dump file records character set information in the identifier section of each file. For example:

    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 OpenEdge 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 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.
    • 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:

    sqldump -u xxx -a yyy -t "\"Yearly Profits\"" progress:T:<hostname>:<database_port>:<database_name>
  • 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 utility.
  • 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).