## OMBPlus and child types in a mapping or process flow

7 10 2010

Some time ago I needed a script to get a hierarchical list of all mappings and process flows that where tied together. I had some problems finding out the type of activities of a process flow. Because, if I encountered a Sub Process flow I wanted to recursively go into that subprocess and get all activities there. I could not find any property that contained the needed information but I found out that you can get a list of all subprocess activities or mappings in a processflow. Like this:

OMBRETRIEVE PROCESS_FLOW '$p_procesFlow' GET SUBPROCESS ACTIVITIES OMBRETRIEVE PROCESS_FLOW '$p_procesFlow'  GET MAPPING ACTIVITIES


My immediate problem was solved. However there are many more types that can be used and it sure was not the most elegant way to do this. But I had no idea how to do it better until I found an undocumented property here. Below the procedure I created to get the type of an activity or operator.

proc get_typ {p_parentType p_parent p_childType p_child o_typ } {
upvar $o_typ l_typ switch$p_childType {
"OPERATOR" { set pos 6 }
"ACTIVITY" { set pos 5 }
}
set l_typ [lindex \
[split [OMBRETRIEVE $p_parentType '$p_parent' \
$p_childType '$p_child' \
GET PROPERTIES (STRONG_TYPE_NAME) ] '.' ] $pos ] switch [string toupper$l_typ] {
"AGGREGATION"             { set l_typ AGGREGATOR }
"ANYDATACAST"             { set l_typ ANYDATA_CAST }
"VARIABLES"               { set l_typ CONSTANT }
"USERTYPES"               { set l_typ CONSTRUCT_OBJECT }
"PSEUDOCOLUMN"            { set l_typ DATA_GENERATOR }
"DISTINCT"                { set l_typ DEDUPLICATOR }
"EXTERNALTABLE"           { set l_typ EXTERNAL_TABLE }
"FLATFILE"                { set l_typ FLAT_FILE }
"MAPPINGINPUTPARAMETERS"  { set l_typ INPUT_PARAMETER }
"JOIN"                    { set l_typ JOINER }
"KEYLOOKUP"               { set l_typ KEY_LOOKUP }
"MATERIALIZEDVIEW"        { set l_typ  MATERIALIZED_VIEW }
"MAPPINGOUTPUTPARAMETERS" { set l_typ OUTPUT_PARAMETER }
"SUBMAP"                  { set l_typ PLUGGABLE_MAPPING }
"POSTMAPTRIGGER"          { set l_typ POSTMAPPING_PROCESS }
"PREMAPTRIGGER"           { set l_typ PREMAPPING_PROCESS }
"SETOPERATION"            { set l_typ SET_OPERATION }
"ORDERBY"                 { set l_typ SORTER }
"TABLEFUNCTION"           { set l_typ TABLE_FUNCTION }
"TRANSFORMFUNCTION"       { set l_typ TRANSFORMATION }
default                   { set l_typ [string toupper $l_typ ] } } }  I created a temporary process flow called TMP and a likewise named mapping. In both I included all possible activitiy or operator types. Not resulting in a valid object, but that was not the point of the excercise. As shown in the procedure above the names of the types retrieved with the STRONG_TYPE_NAME property does not exactly match the name used in the OMB language. So I had to add a translation switch in the procedure. Maybe a bit too straightforward, but it works. Now I can find the type and use that to generate or alter all kinds of operators or activities. Below a simple example showing the possible types. OMBCC '/<project>/<oracle_module>' set map TMP set ops [OMBRETRIEVE MAPPING '$map' GET OPERATORS]
foreach op $ops { get_typ MAPPING$map OPERATOR $op typ puts "Operator$op is of type $typ" puts "OMBRETRIEVE MAPPING '$map' GET $typ OPERATORS" OMBRETRIEVE MAPPING '$map' GET $typ OPERATORS } OMBCC '/<project>/<processflow_module>/<processflow_package>/' set pf TMP set acts [OMBRETRIEVE PROCESS_FLOW '$pf' GET ACTIVITIES]
foreach act $acts { get_typ PROCESS_FLOW$pf ACTIVITY $act typ puts "Activity$act is of type $typ" puts "OMBRETRIEVE PROCESS_FLOW '$pf' GET $typ ACTIVITIES" if {$typ == "START" } { puts "START activity is special"
} else {
OMBRETRIEVE PROCESS_FLOW '$pf' GET$typ ACTIVITIES
}
}


And the output is below. I explicitly named the operators/activities after the type they were to enable an easy translation.

Operator AGGREGATOR is of type AGGREGATOR
Operator ANYDATA_CAST is of type ANYDATA_CAST
Operator CONSTANT is of type CONSTANT
Operator CONSTRUCT_OBJECT is of type CONSTRUCT_OBJECT
Operator DATA_GENERATOR is of type DATA_GENERATOR
Operator DEDUPLICATOR is of type DEDUPLICATOR
Operator EXPAND_OBJECT is of type CONSTRUCT_OBJECT
Operator EXPRESSION is of type EXPRESSION
Operator EXTERNAL_TABLE is of type EXTERNAL_TABLE
Operator FILTER is of type FILTER
Operator FLAT_FILE is of type FLAT_FILE
Operator INPUT_PARAMETER is of type INPUT_PARAMETER
Operator JOINER is of type JOINER
Operator KEY_LOOKUP is of type KEY_LOOKUP
Operator LCRSPLITTER is of type LCRSPLITTER
Operator LCR_CAST is of type LCRCAST
Operator MATCHMERGE is of type MATCHMERGE
Operator MATERIALIZED_VIEW_1 is of type MATERIALIZED_VIEW
Operator OUTPUT_PARAMETER is of type OUTPUT_PARAMETER
Operator PIVOT is of type PIVOT
Operator PLUGGABLE_MAPPING is of type PLUGGABLE_MAPPING
Operator POST_MAPPING_PROCESS is of type POSTMAPPING_PROCESS
Operator PRE_MAPPING_PROCESS is of type PREMAPPING_PROCESS
Operator SEQEUNCE is of type SEQUENCE
Operator SET_OPERATION is of type SET_OPERATION
Operator SORTER is of type SORTER
Operator SPLITTER is of type SPLITTER
Operator TABLE_ is of type TABLE
Operator TABLE_FUNCTION is of type TABLE_FUNCTION
Operator TRANSFORMATION is of type TRANSFORMATION
Operator UNPIVOT is of type PIVOT
Operator VARRAY_ITERATOR is of type CONSTRUCT_OBJECT
Operator VIEW_ is of type VIEW
Activity WHILE_LOOP is of type WHILE_LOOP
Activity EMAIL is of type EMAIL
Activity END_SUCCESS is of type END_SUCCESS
Activity MAPPING is of type MAPPING
Activity ROUTE is of type ROUTE
Activity OR1 is of type OR
Activity TRANSFORMATION is of type TRANSFORMATION
Activity AND1 is of type AND
Activity END_WARNING is of type END_WARNING
Activity END_LOOP is of type END_LOOP
Activity WAIT is of type WAIT
Activity MANUAL is of type MANUAL
Activity USER_DEFINED is of type USER_DEFINED
Activity START1 is of type START
Activity ASSIGN is of type ASSIGN
Activity END_LOOP_1 is of type END_LOOP
Activity FORK is of type FORK
Activity SET_STATUS is of type SET_STATUS
Activity FILE_EXISTS is of type FILE_EXISTS
Activity END_LOOP_2 is of type END_LOOP
Activity FTP is of type FTP
Activity SQLPLUS is of type SQLPLUS
Activity END_ERROR is of type END_ERROR
Activity FOR_LOOP is of type FOR_LOOP
Activity DATA_AUDITOR_MONITOR is of type DATA_AUDITOR
Activity SUBPROCESS is of type SUBPROCESS


## MDL import/export

28 05 2010

Some time ago, when we moved from OWB 9 to 10, I noticed the size of OWB exports into mdl files dramatically decreased. That was a good thing in that it was easier to distribute the mdl files e.g. to production sites. It it also meant you could not read (or edit) the contents of the mdl files any more. I always assumed the new mdl format was some kind of binary optimized format, but today I read this blog from the OWB guys. And it turns out that the ‘new’ format is just a normal zip file containing 2 files. And that you can specify the export to be done in the ‘old’ text format. Text you can edit!

It could be a means to move/copy one or more mappings from one project to another project. Not easy as you must ‘hack’ the mdl file, but it can be done. Neat.

## Deploying several mappings using OMBPlus

23 02 2010

Every now and then it is a pain to deploy mappings from the Control Center Manager, especially when you have a slow connection to your control center. Retrieving all mappings that can be deployed to a location can take some time (putting it very politely), and you can’t stop the control center from doing that. At least I have not found that possibility. Moreover sometimes you have assembled a list of mappings you want to deploy. In Excel or a plain text file. Mappings you have moved to your acceptance – or production environment and now want to deploy. You can do that using OMBPlus. And, as this is a scripting language, you can create a script to do that for all mappings you want, in one go. Unfortunately deploying is not really very straightforward in OMBPlus. So I created a tcl procedure that sort of simplifies the task. Here it is.

# File   : deploy_map.tcl
# Purpose: deploy MAPPING (parameter)
#          in each target module (parameter)
#          of a project (parameter)
# Author : Kadenza - Eric Buddelmeijer 200908
# Prereq.: a valid repository connection is presumed here

proc deploy_map {p_project p_module p_map p_cc_pwd} {
OMBCC '/${p_project}' # cleanup set deplPlan [OMBLIST DEPLOYMENT_ACTION_PLANS] foreach deplAction$deplPlan {
OMBDROP   DEPLOYMENT_ACTION_PLAN '$deplAction' } OMBCOMMIT OMBCONNECT CONTROL_CENTER USE '${p_cc_pwd}'
set moduleList [OMBLIST ORACLE_MODULES '${p_module}.*' ] foreach moduleName$moduleList {
puts "--------------------- $moduleName -----------------" OMBCC '/${p_project}/$moduleName' # start of objecttype loop ------------------------------------------ # order of objecttypes is relevant!!! set typeList [list MAPPING ] set N 1 set plural "S" foreach objectType$typeList {
set listType [concat $objectType$plural]
set J 1
set objectList [OMBLIST $listType '${p_map}.*']
puts "... Checking ${listType}" if {[llength$objectList] > 0} {
foreach objectName $objectList { set deployAction [OMBRETRIEVE$objectType \
'$objectName' GET PROPERTIES (DEPLOYABLE)] if {$deployAction == "true"} {
set valid [OMBVALIDATE $objectType '$objectName']
if {$valid == "Valid." } { switch$objectType {
"TABLE"    {set deployAction "REPLACE"}
"SEQUENCE" {set deployAction "REPLACE"}
default    {set deployAction "REPLACE" }
}
puts "...... Adding ${J}.$objectType $objectName" if {$J == 1 } {
OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN \
'GEN_DEPLOY_${objectType}' \ ADD ACTION 'DEPLOY_${objectName}'\
SET PROPERTIES (OPERATION) \
VALUES ('$deployAction') \ SET REFERENCE$objectType '$objectName' } else { OMBALTER DEPLOYMENT_ACTION_PLAN \ 'GEN_DEPLOY_${objectType}'\
ADD ACTION 'DEPLOY_A_${objectName}' \ SET PROPERTIES (OPERATION) \ VALUES ('$deployAction') \
SET REFERENCE $objectType '$objectName'
}
incr J
} else { puts "###### SKIPPING $valid$objectType \
$objectName" } } else { puts "...... Skipping explicit NOT deployable \$objectType $objectName" } } #end of objectName loop --------------------------- puts "... Deploying${listType}"
set exedeplPlan [OMBLIST DEPLOYMENT_ACTION_PLANS]
foreach exedeplAction $exedeplPlan { OMBDEPLOY DEPLOYMENT_ACTION_PLAN '$exedeplAction'
OMBDROP   DEPLOYMENT_ACTION_PLAN '\$exedeplAction'
}
incr N
}
# end of if llength objectList > 0
}
# end of object type loop
}
# end of module loop
}



And the procedure can be executed with:

deploy_map <project name> <module name> <map name> <control center password>


Which can be repeated several times of course. The procedure also tries to expand the parameters ‘module name’ and ‘map name’ using a wildcard. This means the procedure tries to expand the parameter in such a way that it is treated as ‘begins with’.

Have fun deploying.

## OWB 11.1 on an 10.2 database with ‘Split Repositories’

13 11 2009

I’m currently working for a customer that is using OWB 10.2.0.1 and a 10.2.0.4 database. All on Windows. Not the best environment in my humble opinion, but hey, it is a customer. They can decide that.

We were having some problems with OWB that have been solved in OWB 10.2.0.4 but also in 11.1.0.6. In other words, we had to do some migration. Because Oracle 11g is the way to go (there were more business-like terms used to persuade the business to accept this decision of course), we decided to skip OWB 10.2.0.4 and start using OWB 11.1.0.6. The DBA’s were not ready to start using database 11g so we also decided to stay on 10.2.0.4 with the database. This means you have to do some extra steps in your database to get OWB 11 running. Perfectly documented in this part of the administration guide for OWB. Worked like a charm.

The difficulties came later on. We have an architecture that looks very much like the Split Repositories Implementation in the same manual. In the said architecture there is only one control center service running, according to the manual. And it is running against the run-time environment. If I tried that I could not deploy from my design repository to my run-time environment and if I stopped the control center service against the run-time environment I could not run any mappings.

So why not start two control center services? One in the design environment and one in the run-time environment. I tried that, but I ran into some obscure java errors that said ‘a file could not be opened because it is in use by another program’. Just that: a file. Not a name, not a directory, just a file. Period. Time to go file hunting.

I imagined it to be a log file of OWB that was opened by the control center service. Both services were using the same OWB_HOME so I went looking there. In <OWB_HOME>/owb/admin/log where 2 files clearly related to the control center service. One was a log file (OWBSYS.LOG) that I traced to a setting in the run time parameters table of the repository owner. The table is owned by owbsys and is called wb_rt_platformproperties. Look for a property_path with value “property.RuntimePlatform.0.messages” and “property.RuntimePlatform.0.errors”. They both have the default property_value “%RTHOME%\owb\log\OWBSYS.log”. I changed these parameters so both control center services would use a different log file. Indeed they did if I started one service, stopped it, started the other service. But if I started both services I still got the ‘file already in use’ message. I left the setting for the parameter because I like to get different log files for different services and went hunting further.

The next log file is created when starting the service with <OWB_HOME>\owb\bin\win32\run_service.bat (or the shortcut to it in the programs menu -> Oracle -> Warehouse Builder -> Administration -> Start Control Center Service). In this bat-file the output of the bat-file itself is sent to a log file using redirection (the >run_service.log at the end of the command). I first started one control center service then changed the bat-file so it would log to a different file and then started the second control center service. And lo and behold, I got me two running control center services.

# Eureka!

I then had to do some fiddling to the bat-file because I did not want to change the log file every time I had to start the services. In Unix or Linux this is easy. You just add date “+%Y%d%m_%H%M%S” to the name of the file and you get an unique file name every time you start. On Windows this is not so easy. I googled around a bit and found some rather complex bat-files that seemed to work if I started them from the command line but worked differently when using the shortcut. It can probably be solved by someone more savvy than me in the wonders of cmd bat files but I decided to take the easy way out. I used the following bat file to find the number of log files that where already present. Add 1 to that count and append the file name with the resulting count. Now my control center services start every time! Even with split repositories.

This is how the bat file now looks (changed parts in bold and italic, you probably have to scroll a bit to see all changes):

@echo off
set STARTUP_TYPE=%1
shift
set NODEID=%1
shift
set RTHOME=%1
shift
set RTUSER=%1
shift
set HOST=%1
shift
set PORT=%1
shift
set SERVICE=%1
shift
pushd %RTHOME%\owb\bin\win32\
set JVM_OPTIONS=-Xmx768M
set JAVAPATH=%RTHOME%\jdk
set OWB_HOME=%RTHOME%
set JDK_HOME=%JAVAPATH%
set ORA_HOME=%RTHOME%
set OEM_HOME=%RTHOME%
set IAS_HOME=%RTHOME%
set ORACLE_HOME=%RTHOME%
set PATH=%RTHOME%\bin;%JAVAPATH%\jre\bin\client;%JAVAPATH%\jre\bin;%RTHOME%\owb\bin\adminrem if exist ..\..\..\owb\lib\int\rtpplatform.jar goto check_common
rem echo cannot find rtpplatform.jar, exiting
rem goto exitrem :check_common
rem if exist ..\..\..\owb\lib\int\rtpcommon.jar goto run_service
rem echo cannot find rtpcommon.jar, exiting
rem goto exit
rem :run_service
:: aanpassing om control center service voor 2 db's op dezelfde machine te laten lopen
set tel=0
for /f "tokens=*" %%a in ('dir %RTHOME%\owb\log\run_service*.log /B/A-D') do set /a tel+=1
set /a tel+=1
SET RUNSRVCLOG=%RTHOME%\owb\log\run_service_%tel%.log
%JAVAPATH%\jre\bin\javaw.exe %JVM_OPTIONS% -DORACLE_HOME="%RTHOME%" -DOCM_HOME="%RTHOME%" -DOCM_ORACLE_HOME="%RTHOME%" -classpath %RTHOME%\owb\bin\admin\;%RTHOME%\owb\bin\admin\launcher.jar -DOWB_HOME=%OWB_HOME% -DJDK_HOME=%JDK_HOME% -DORA_HOME=%ORA_HOME% -DOEM_HOME=%OEM_HOME% -DIAS_HOME=%IAS_HOME% Launcher %RTHOME%\owb\bin\admin\owb.classpath oracle.wh.runtime.platform.service.Service %STARTUP_TYPE% %NODEID% %RTUSER% %HOST% %PORT% %SERVICE%  &gt; %RUNSRVCLOG%
:exit
popd


This is how the bat file used to look:

@echo off
set STARTUP_TYPE=%1
shift
set NODEID=%1
shift
set RTHOME=%1
shift
set RTUSER=%1
shift
set HOST=%1
shift
set PORT=%1
shift
set SERVICE=%1
shift
pushd %RTHOME%\owb\bin\win32\
set JVM_OPTIONS=-Xmx768M
set JAVAPATH=%RTHOME%\jdk
set OWB_HOME=%RTHOME%
set JDK_HOME=%JAVAPATH%
set ORA_HOME=%RTHOME%
set OEM_HOME=%RTHOME%
set IAS_HOME=%RTHOME%
set ORACLE_HOME=%RTHOME%
set PATH=%RTHOME%\bin;%JAVAPATH%\jre\bin\client;%JAVAPATH%\jre\bin;%RTHOME%\owb\bin\adminrem if exist ..\..\..\owb\lib\int\rtpplatform.jar goto check_common
rem echo cannot find rtpplatform.jar, exiting
rem goto exitrem :check_common
rem if exist ..\..\..\owb\lib\int\rtpcommon.jar goto run_service
rem echo cannot find rtpcommon.jar, exiting
rem goto exit
rem :run_service
%JAVAPATH%\jre\bin\javaw.exe %JVM_OPTIONS% -DORACLE_HOME="%RTHOME%" -DOCM_HOME="%RTHOME%" -DOCM_ORACLE_HOME="%RTHOME%" -classpath %RTHOME%\owb\bin\admin\;%RTHOME%\owb\bin\admin\launcher.jar -DOWB_HOME=%OWB_HOME% -DJDK_HOME=%JDK_HOME% -DORA_HOME=%ORA_HOME% -DOEM_HOME=%OEM_HOME% -DIAS_HOME=%IAS_HOME% Launcher %RTHOME%\owb\bin\admin\owb.classpath oracle.wh.runtime.platform.service.Service %STARTUP_TYPE% %NODEID% %RTUSER% %HOST% %PORT% %SERVICE%  &gt; %RTHOME%\owb\log\run_service.log
:exit
popd


## Change OWB location details

30 10 2009

If you have used your locations to deploy objects to them (who doesn’t?), you cannot change the connection details easily. In the Design Center they are greyed out. The only way you can change them is by starting the control center, unregistering the location, reregister with the new details and deploy all objects again to regain the right deployment status. The last is something you cannot always do because that would mean e.g. dropping tables that already contain data you do not want to lose. Not an ideal situation.
Today I was reading this post about upgrading to 11gR2 which was a good post. Although I’m currently upgrading to OWB 11gR1 as 11gR2 is not yet available on Windows. Stewart Bryson had some trouble because during the upgrade his connection details where automagically updated. He first described how to change the connection details in the ‘old-fashioned’ way I just mentioned. But more importantly he described a second possibility I did not know of. It is possible to change the details using the OWB Repository browser! I do not start the Repository browser very often because it tends to slow down once you have had your share of deploying and executing. A simple view suffices most of the time (I’ll reserve that one for a later post).
But it meant I did not see all functionality, alas. It could have saved me some time.
Anyway, at least in 11gR1 (I’ll look into 10gR2 shortly) you can change host, port, sid and password from a convenient browser screen as presented below. Nice.

BTW, works for File locations too.
BTW2, The link to this screen is perfectly hidden under the link called ‘Unknown’ at the end of the line for each location
BTW3, It is available in OWB 10gR2 as well

## More date arithmetic

22 04 2009

I came across this today. I have been having quite a ball these last weeks with dates, periods and mainly overlapping periods in two tables. Therefore I am currently rather interested in date handling. This is definitely one for the Tips & Tricks book I seem to be gathering here. Nice post.

## OWB Process Flows that keep saying they are running

20 04 2009

Now and then an OWB started Processflow has trouble finishing. Or more likely I have created an incomplete Processflow. Most of the time this leads to an error when deploying the processflow again:

RPE-02062: ItemType LOAD_CMS cannot be dropped as it has running Processes. You must first abort all the running processes using the Oracle Workflow Monitor

To find out if there are any such processflows I use the following query:

SELECT item_type, item_key
FROM wf_item_activity_statuses_v
WHERE activity_type_code= 'PROCESS'
AND activity_status_code != 'COMPLETE';


And you can tell OWF the processflow is not running anymore with:

DECLARE
t_result VARCHAR2(300);
BEGIN
wf_engine.abortprocess (‘itemtype’,’itemkey’,t_result);
COMMIT;
END;


Where Itemtype and ItemKey must be copied from the results of the query.

There are probably more sophisticated methods to handle these situations (probably from OWF-savvy people), but it gives me a handle to know what is going on and what I can do about it.

Edit November 24, 2010:

Sometimes you cannot find any information using the first query above. But still you get the RPE-2062 error. An even more radical approach would be to remove all audit/run-time information about the process flow from OWF. This means you will have to redeploy the process flow, but that is what you had planned anyway. There is a script for this purpose that can be found in <ORACLE_HOME>/wf/admin/sql/wfrmitt.sql. Mind about the slashes or backslashes for your environment. The <ORACLE_HOME>/wf directory is only present on the server where you have installed the workflow software. In most client installations it won’t be there.
Execute the script as the workflow user.