Migrating an SQL database from DMSQL to MCPSQL is not directly allowed. The changes in the physical database structure and the functionality of the software between two products does not permit the software to automatically migrate an SQL database. The only way to migrate an SQL database is to recreate it using the ADD DATABASE command in Schema Administrator, and then copy and reload any data from the old database. Attempting to use the Schema Administrator to UPDATE an SQL database from DMSQL to MCPSQL will result in the following error:
Cannot UPDATE a native SQL database from DMSQL to MCPSQL.
If you have a native SQL database in DMSQL with data that you wish to migrate to MCPSQL, do the following:
-
Run the new MCPSQL version of the Schema Administrator with the ADD command using the DDL file used to create the original database. For example:
Run *SYSTEM/MCPSQL/ADMIN("ADD DATABASE <database name>"); FILE CARD = <DDL file name> -
Copy the data from the original DMSQL database to the new MCPSQL database. There are many methods and utilities available to export and input data from one database to another. These include:
-
Use the QDC utility of DMSQL to select all data in a table of the existing DMSQL database, save the data to a comma delimited file, then use the LOADER utility of MCPSQL to load the data into the new MCPSQL database. Repeat for each table in the database that needs to be migrated. The QDC utility of DMSQL is documented in Using Query Design Center. The LOADER utility of MCPSQL is documented in Using the Loader Utility.
-
Use connection methods such as ODBC and OLE DB to retrieve data from the existing DMSQL database into another type of relational repository, then insert that data into the new MCPSQL database. OLE DB and ODBC documentation is available from several guides found under the Database, Query, and Reporting category of the ClearPath MCP documentation.
-
Write a COBOL or ALGOL application to retrieve data from the existing DMSQL database, store it to an intermediary data source, and then write it to the new MCPSQL database. Documentation is available from several programming guides found under the Database, Query, and Reporting category of the ClearPath MCP documentation.
-
