Rittman Mead - Delivered Intelligence

I was recently in a situation where I had to delete a package (transformation) in OWB and then re-import it from the database, because I had deleted it and re-imported it, every mapping and process flow that referenced the package lost its reference to it. I could have gone through each mapping and process flow and synchronized inbound, manually selected the function, but this would have needed to be done hundreds of times, so I looked at automating the problem using OMB Plus. I would write a script and execute it using the OMB Plus window in the Design Center.

Mappings

The mappings were relatively simple, despite losing the reference, the operators retained the function name. The function was bound to a pre-mapping operator, so using the following code I was able to iterate through each operator and synchronize each of the functions:

foreach opName [OMBRETRIEVE MAPPING '$mapName' GET PREMAPPING_PROCESS OPERATORS ] {
    set fncName [OMBRETRIEVE MAPPING '$mapName' \
    OPERATOR '$opName' \
    GET PROPERTIES(FUNCTION_NAME) ];
    switch -regexp $fncName {
    ^.MAP_START.$ {
      set opRef "/$projectName/$ctlModName/$ctlPkgName/MAP_START";
      puts "Synchronizing $opName to $opRef";
      OMBSYNCHRONIZE FUNCTION '$opRef' \
      TO MAPPING '$mapName' OPERATOR '$opName' \
      USE (RECONCILE_STRATEGY 'REPLACE', MATCHING_STRATEGY 'MATCH_BY_OBJECT_ID');}
    default {puts "Function not changed.";}
 }

Note: there are a number of variable in the code that I had already initialised.

Process Flows

Process flows were much more difficult – there was no way of getting back the reference and even if I was able to derive the transformation from the name of the activity I was still getting errors when trying to synchronize. So the only way to do it was to delete the transformation activity and re-create it. However first I would need to take a copy of each transition in the process flow and then delete them – this is because they would be deleted anyway once the transformation activity was deleted.

foreach transName [ OMBRETRIEVE PROCESS_FLOW '$flowName' GET TRANSITIONS ] {
  set src [ OMBRETRIEVE PROCESS_FLOW '$flowName' TRANSITION '$transName' GET SOURCE_ACTIVITY ];
  set dest [ OMBRETRIEVE PROCESS_FLOW '$flowName' TRANSITION '$transName' GET DESTINATION_ACTIVITY ];
  set cond [ OMBRETRIEVE PROCESS_FLOW '$flowName' TRANSITION '$transName' GET PROPERTIES (TRANSITION_CONDITION) ];
  lappend transPropList [ list $transName $src $dest $cond ];
  OMBALTER PROCESS_FLOW '$flowName' DELETE TRANSITION '$transName';
}

From the name of the activity I was then able to derive the required function.

    switch -regexp $actName {
      START_EXECUTION -
      EXECUTE_MAPPING -
      COMPLETE_EXECUTION -
      MAP_START -
      MAP_END -
      DATA_CONSISTENCY_CHECK {set actRef "/$projectName/$ctlModName/$ctlPkgName/$actName"}
      ^EM_.* {set actRef "/$projectName/$ctlModName/$ctlPkgName/EXECUTE_MAPPING"}
      default {set actRef [OMBRETRIEVE PROCESS_FLOW '$flowName' ACTIVITY '$actName' GET REFERENCE]}
     }    

And then for each activity I would have to get all the parameter details.

    foreach paramName [OMBRETRIEVE PROCESS_FLOW '$flowName' ACTIVITY '$actName' GET PARAMETERS ] {
      set paramValue [OMBRETRIEVE PROCESS_FLOW '$flowName' ACTIVITY '$actName' PARAMETER '$paramName' GET PROPERTIES (VALUE) ];
      set paramBinding [OMBRETRIEVE PROCESS_FLOW '$flowName' ACTIVITY '$actName' PARAMETER '$paramName' GET PROPERTIES (BINDING) ];
      lappend paramsPropList [ list $paramName $paramValue $paramBinding ];
    }

I then had to re-create the activity and reinstate all the parameter properties.

    OMBALTER PROCESS_FLOW '$flowName' DELETE ACTIVITY '$actName';
    OMBALTER PROCESS_FLOW '$flowName' ADD TRANSFORMATION ACTIVITY '$actName' SET REFERENCE TRANSFORMATION '$actRef';
    foreach paramProp $paramsPropList {
      set paramName [ lindex $paramProp 0 ];
      set paramValue [ lindex $paramProp 1 ];
      set paramBinding [ lindex $paramProp 2 ];

      if {$paramName != "" } {
        if {$paramValue != ""} {
          OMBALTER PROCESS_FLOW '$flowName' MODIFY TRANSFORMATION ACTIVITY '$actName' \
          MODIFY PARAMETER '$paramName' SET PROPERTIES (VALUE) VALUES ('[OMBToSettableString $paramValue]');
        }
        if {$paramBinding != ""} {
          OMBALTER PROCESS_FLOW '$flowName' MODIFY TRANSFORMATION ACTIVITY '$actName' \
          MODIFY PARAMETER '$paramName' SET PROPERTIES (BINDING) VALUES ('$paramBinding');
        }
      }
    }
    unset paramsPropList    

And finally add all the transitions back in.

    foreach transProp $transPropList {
      set transName [ lindex $transProp 0 ];
      set transSource [ lindex $transProp 1 ];
      set transDest [ lindex $transProp 2 ];
      set transCond [ lindex $transProp 3 ];
      if {$transCond != "" } {
        OMBALTER PROCESS_FLOW '$flowName' \ADD TRANSITION '$transName' FROM ACTIVITY '$transSource' TO '$transDest' \
        SET PROPERTIES (TRANSITION_CONDITION) VALUES ('$transCond');
      } else {
        OMBALTER PROCESS_FLOW '$flowName' ADD TRANSITION '$transName' FROM ACTIVITY '$transSource' TO '$transDest';
      }
    }

    unset transPropList    

As you can see from the code this took to fix it wasn’t a trivial task, but unfortunately there was not another option, so be warned if you are about to do this.

Posted in Oracle Warehouse Builder |