Extracting DDL Statements from a PeopleSoft Data Mover exported DAT file

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:

1. Insert the SET EXTRACT statements in the Data Mover script (DMS) before the IMPORT .. statement.
   
   eg.,
   

   
   % cat /tmp/retrieveddl.dms
   
   ..
   SET EXTRACT OUTPUT /tmp/ddl_stmts.log;
   SET EXTRACT DDL;
   ..
   
   IMPORT *;
   
   

   
   It is mandatory that the SET EXTRACT OUPUT statement must appear before any SET EXTRACT statements.
   
   
2. 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.

______
(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/extracting_ddl_statements_from_a)

Read More