Specific for DB2:
MVPRDEXP/MVPRDIMP.DMS as an example for this discussion.
When you use Data Mover to move data from one database to another, you must specify the database-specific overrides which DataMover requires in order populate the objects (Tables) in the database, and also to create any new objects that are not present in the database.
If the overrides are not present then Data Mover will obtain the relevant details from the .DB or .DAT import file contains the details which relate to the database from which the data was exported — not a good idea when you are trying to import into a different database with different DBNAMES and Owner information.
At this point you need to provide certain override information for Datamover which is specific to your Database.
A situation analogous to this is when you setup your Demo Database from a .DB file which PeopleSoft provides. This DB file may have and probably was exported from a MS SQL Server Database with its own characteristics. That is why we provided the DBSETUP.EXE for you to run and enter all the relevant details for your site. (Refer Chapter 5 of the Install and Admin Guide – Setting up Database). You are instructed to enter the details and then click the “Create Scipt” button, which creates the HCDMODBO.DMS script in your file server PS_HOME\Scripts directory.
If you look at this script you will see several SET DDL override statements which are specific to your environment. These same overrides must be cut and pasted (and customised to suit the database into which you are importing) into your MVPRDIMP.DMS script. See below for an example — this should get you through any problems with invalid database names for objects which require to be created.
Example : HRMS Demo Database Setup script
REM – HCDMODBO.DMS
REM – Created by DBSETUP
REM – HRMS Demo Database HCDMO US English Database import
set log c:\temp\hcengs.log;
set input d:\hdmod1a\data\hcengs.db;
set execute_sql set current sqlid = ‘HCDMOD1A’;
set ddl table space * input stogroup as PSRTD1SG;
set ddl index * input stogroup as PSRTX1SG;
set ddl unique index * input stogroup as PSRTX1SG;
set ddl record * input dbname as HCDMOD1A;
set ddl record * input owner as HCDMOD1A.;
set ddl index * input owner as HCDMOD1A.;
set ddl unique index * input owner as HCDMOD1A.;
set ddl index * input owner2 as HCDMOD1A.;
set ddl unique index * input owner2 as HCDMOD1A.;
set no view;
set no space;
set no trace;
set no record;
*** The DMS Script overrides are ignored if the PSRECDDLPARM or PSIDXDDLPARM tables are populated for those records. If these tables are populated, delete the rows for your platformid BEFORE you Export the tables ***
If you currently have an exported DB or DAT file that you exported from a database which may have had data in the above tables you may want to check the DDL that Data Mover will create when you import into your new database. Follow these steps:
(For this example we will use MVPRDIMP.DMS but you may use your import DMS script)
1) Copy MVPRDIMP.DMS to MVPRDNEW.DMS
2) Apply the Overrides (SET DDL statements as required)
3) Add the following lines
SET EXTRACT OUTPUT C:\TEMP\TEST.DDL
SET EXTRACT DDL
(Note: the IMPORT * should already be present. If the tables already exist use REPLACE_ALL command insted of IMPORT command)
This will create all the necessary DDL that will be required during the Import process and output it to a the TEST.DDL file without actually importing the data into your database.
4) Check the DDL output to ensure that the overrides did in fact work.
5) If the overrides were not used in the DDL output then the most likely problem is that the database from which the data was extracted had information in the PSRECDDLPARM or PSIDXDDLPARM tables.
There are basically two workarounds at this time….
1) Backup PSRECDDLPARM and PSIDXDDLPARM from Source….using the Data Mover EXPORT command (against the Source database)
2) Delete entries from PSRECDDLPARM and PSIDXDDLPARM for PLATFORMID=1 (against Source)
3) Run the Export DMS scripts (against Source)
4) Apply the OVERRIDES to the Import DMS
5) Run the Import DMS (against the Target database)
6) Go back to the Source Database
7) Go to Data Mover and use REPLACE_DATA for PSRECDDLPARM and PSIDXDDLPARM from the backed up .DAT file (against the Source database)
1) Delete Entries from PSRECDDLPARM and PSIDXDDLPARM
2) Do the Export from the Source Database
3) Do the Import to the target with overrides
4) Run the SETDBNAM.SQR and SETINDX.SQR against source to get it back to the original state
Here’s an update:
Db2 Os390 customer upgrading, in the Test the Move to Production step. As in the above steps, did deletes from psrecddlparm and psidxddlparm where platformid = 1 (db2). This allowed them to export from one environment and import into a different environment. However this failed due to space problems when importing one large table – the space parm failed as it was using a very low value.
Before the export, instead of deleting from psrecddlparm where platformid = 1 and deleting from psidxddlparm where platform = 1
which wipes out all db2 parm settings, and caused the problem to begin with
do this delete:
delete from psrecddlparm where parm name = dbname
…so the import does not attempt to load the exported file back into the originating database
delete from psidxddlparm where parm name = stogroup