Extracting DDL Statements from a PeopleSoft Data Mover exported DAT file
By Giri Mandalika on Feb 02, 2010
Case in hand: Given a PeopleSoft Data Mover exported data file (db or dat file), how to extract the DDL statements [from that data file] which gets executed as part of the Data Mover's data import process?
Here is a quick way to do it:
SET EXTRACTstatements in the Data Mover script (DMS) before the
% cat /tmp/retrieveddl.dms .. SET EXTRACT OUTPUT /tmp/ddl_stmts.log; SET EXTRACT DDL; .. IMPORT \*;
It is mandatory that the
SET EXTRACT OUPUTstatement must appear before any
Run the Data Mover utility with the modified DMS script as an argument.
eg., OS: Solaris
% psdmtx -CT ORACLE -CD NAP11 -CO NAP11 -CP NAP11 -CI people -CW peop1e -FP /tmp/retrieveddl.dms
On successful completion, you will find the DDL statements in /tmp/retrieveddl.dms file.
Check chapter #2 "Using PeopleSoft Data Mover" in Enterprise PeopleTools x.xx PeopleBook: Data Management document for more ideas.
It appears PeopleSoft introduced a bug in Data Mover functionality on \*NIX platforms somewhere in PeopleTools 8.49 releases. If Data Mover repeatedly fails with "
Error: Unable to open OUTPUT: " when extracting statements or actions using "
SET EXTRACT", run the same DMS script on any Windows system as a workaround. For more information, check Oracle Support Document "E-DM: 'Error: Unable to open OUTPUT:' when attempting to extract DDL with Data Mover (Doc ID 887792.1)". From the same document: the fix to this bug on \*NIX platforms is targeted to fix after PeopleTools 8.51 release.