1 Reply Latest reply: May 15, 2014 10:14 AM by Steve Caiels RSS

    How do you automate Monarch Utility in Data Pump?

    sapnerd _

      I'm looking to automate the preparation of a wide (more than 4000 continuous characters) .txt file which I currently do in 2 passes of Monarch Utility.

       

      gid=MSS Managers

      gdesc=MSS Managers Group

      user=HV22587;TW02933;GM02792;CE53240;AM58066;NC02992;RR50254;FN21938;RF40160;CB22589;PG08933;SP21350;FV83236;KL06820;MW50984;GH26148;SH82962;EM21321;HZ27589;IS21618;AB28877;JI22349;RW21116;SR50589;JL31409;JP02151;CL01663;MH20817;AB01072;SE21559;CS03572;AB02246;SK22730;ES24945;GL72234;

      /CODE

       

      e.g.

      pass 1 replace all instances of "=" with a carriage return and a space (ascii /13/10)

      pass 2 replace all instances of ";" with a carriage return and a space (ascii /13/10)

       

      How do I go about telling Data Pump to do this as part of my process? I'm not a programmer but have heard I might need a batch file or a script of some sort.

       

      Can anybody help me with this?

        • How do you automate Monarch Utility in Data Pump?
          Steve Caiels

          [SIZE=3]Hi SapNerd,[/SIZE][/FONT]

           

          [SIZE=3]One method is to create a batch file as you suggest, then run the batch file by calling it as a pre-process script.  NOTE: It should be a pre-process rather than a pre-export script.[/SIZE][/FONT]

           

          [SIZE=3]Data Pump determines the list of files that it will run against as the process is launched, but after a pre-process script.  By creating a prepped file, that shares a wildcard name with the original input file, you can use monitoring if you wish.  Make sure that the model does not pick up any rouge data from the original unmodified input file.[/SIZE][/FONT]

           

          [SIZE=3]The batch file is as follows (I’ve called it Prepare.bat)[/SIZE][/FONT]

           

          "C:\Program Files (x86)\Monarch\Program\MonarchU.exe" "C:\SapNerd\Input\Input.txt" "C:\SapNerd\Input\Prepped.1" /cr "=" "/13/10"

          "C:\Program Files (x86)\Monarch\Program\MonarchU.exe" "C:\SapNerd\Input\prepped.1" "C:\SapNerd\Input\Input-Prepped.txt" /cr ";" "/13/10"

          /CODE

           

           

          [SIZE=3]And the script to call it is . . . /SIZE[/FONT]

          If Shell("""C:\SapNerd\Prepare.bat""",AppWinStyle.Hide,True,60000) <> 0 Then

            ' TODO: Handle command timeout here.

          End If

          /CODE

           

           

          [SIZE=3]This will wait for up to 60 seconds for the batch file to complete.  If it does not, then it will continue with the process.   You don’t need to add anything in the TODO section, but you could raise an alert to send an email if you wish. /SIZE[/FONT]

           

          [SIZE=3]The main issue I have encountered previously with this approach is that IT restrictions mean that the service account under which Data Pump is running is not able to interact with the system or local drive and therefore cannot run MonarchU.[/SIZE][/FONT]

           

          [SIZE=3]The script below provides a method for running the batch file as a different user.  To avoid exposing the password in the script, enter it in the secure ‘password’ tab of the script editor.  I set one up called Hidden.[/SIZE][/FONT]

           

          '**** Run a Batch file as a different user ***********

          Dim p As New ProcessStartInfo(“C:\SapNerd\Prepare.bat”)

          Dim HiddenPassword as String

           

          p.UseShellExecute = False

          p.Domain = “your.domain.com”

          p.UserName = “YourUserName”

           

          HiddenPassword = Log.ExpandMacros("&[pwd.Hidden]",ProjectID)

          p.Password = New System.Security.SecureString()

          For Each c As Char In HiddenPassword

          p.Password.AppendChar(c)

          Next

           

          Process.Start(p)

          ‘**** End ***********

          /CODE

           

          [SIZE=3]Finally, to avoid a batch file altogether, replace the path to the batch file with the path to MonarchU and add a p.arguments line.  Of course, the process will have to run twice, once for each pass of MonarchU.[/SIZE][/FONT]

           

          '**** Run a Batch file as a different user ***********

          Dim p As New ProcessStartInfo("C:\Program Files (x86)\Monarch\Program\MonarchU.exe")

          Dim HiddenPassword as String

          Dim Timeout as integer

          p.UseShellExecute = False

          p.Domain = “your.domain.com"

          p.UserName = “YourUserName"

          HiddenPassword = Log.ExpandMacros("&[pwd.Hidden]",0)

          p.Password = New System.Security.SecureString()

          For Each c As Char In HiddenPassword

            p.Password.AppendChar(c)

          Next

          TimeOut = 10000     ' Wait up to 10,000 ms for MonarchU to finish.

          p.arguments = """C:\SapNerd\Input\Input.txt"" ""C:\SapNerd\Input\Prepped.1"" /cr ""="" ""/13/10"""

          Dim PWait as Process = Process.Start(p)

          PWait.WaitForExit(Timeout)

          p.arguments = """C:\SapNerd\Input\Prepped.1"" ""C:\SapNerd\Input\Input.prepped"" /cr "";"" ""/13/10"""

          Process.Start(p)

          PWait.WaitForExit(Timeout)

          /CODE

           

          [SIZE=3]I’m not a programmer, (as any programmers out there will have already seen!), so this may not be the most efficient way. /SIZE[/FONT]

          [SIZE=3]Regards,[/SIZE][/FONT]

          [FONT=Times New Roman]Steve.[/FONT][/SIZE]