Tuesday Feb 02, 2010

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.

---

Updated 07/16/2010:

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.

About

Benchmark announcements, HOW-TOs, Tips and Troubleshooting

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today