Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

How to dump and load SQL-92 schema and data?

« Go Back

Information

 
Article Number000010696
EnvironmentProduct: Progress
Version: 9.1x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
How to dump and load SQL-92 schema and data of non ABL tables?
What are the steps involved in dumping and loading SQL-92 objects data definition and data?
How to use the SQLSCHEMA Utility to dump the schema of SQL-92 tables and/or SQL-92 Views from the source database.
 
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
The steps involved in dumping and loading both the schema and data of the Progress SQL-92 tables:

1.  Use the SQLSCHEMA Utility to dump the schema of SQL-92 tables and/or SQL-92 Views from the source database.  For example:
sqlschema -o myOutputFile -t %.% -u <username> -a <password> progress:T:<hostname>:<port number>:sports2000

2.  Use the SQLDUMP Utility to dump the data from of SQL-92 tables from the source database.  For example:
sqldump -t %.%  -u <username> -a <password> progress:T:<hostname>:<port number>:sports2000

3.  Use the SQL Explorer Tool to load the schema of SQL-92 tables from file(s) created using the SQLSCHEMA Utility into the destination database.  For example:
sqlexp -user <username> -password <password> -db sports2000 -H <hostname> -S <port number> -infile myOutputFile.dfsql

4.  Use the SQLLOAD Utility to load the data of SQL-92 tables from files created using the SQLDUMP Utility into the destination database.  For example:
sqlload -t <SCHEMA NAME>.MYTABLE  -u <username> -a <password> progress:T:<hostname>:<port number>:sports2000
Workaround
Notes
Please take the following into consideration:
  1. The SQLSCHEMA utility cannot write definitions for ABL tables in Progress 9 and OpenEdge 10.0x.This restriction is lifted in OpenEdge 10.1A and later.
  2. The SQLSCHEMA utility cannot generate an equivalent to the Data Administration's incremental (delta) DF. Please use a text comparison tool on the .dfsql files to extract the differences.
  3. SQLDUMP and SQLLOAD utilities do not support tables with LVARBINARY (BLOB) and LVARCHAR (CLOB) column data. Use Binary Dump and Load for tables that contain such data.
  4. The case used for the database name must match the case structure of the physical database file name.
  5. If any SQL tables are created in the pub schema, the _file._creator field will contain the actual userid of the SQL user who created the table, while tables created by the ABL always show "PUB" for the _creator.  To generate a list of these tables to be used in when using the sql dump utilities, run code similar to the following:
OUTPUT TO pubsqltbls.txt.
FOR EACH _file WHERE _tbl-type EQ "T" AND 
                                      _owner EQ "PUB" AND
                                      _creator NE "PUB" NO-LOCK:
  PUT UNFORMATTED _file._file-name ",".
END.
OUTPUT CLOSE.



References to Other Documentation:

Progress Database Administration Guide and Reference: "Database Administration Utilities,SQLSCHEMA Utility"
Progress SQL-92 Guide and Reference: "Starting SQL Explorer in Character Mode"
Progress Database Administration Guide and Reference: "Database Administration Utilities,SQLDUMP Utility"
Progress Database Administration Guide and Reference: "Database Administration Utilities,SQLLOAD Utility"
OpenEdge® Data Management:SQL Development

Progress Article(s):
000021664, How to perform a binary dump and load?
Attachment 
Last Modified Date3/28/2018 3:28 PM