OWB 11.1 on an 10.2 database with ‘Split Repositories’

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%  > %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%  > %RTHOME%\owb\log\run_service.log
:exit
popd

Change OWB location details

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

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

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 LAAD_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.

Missing window panes in OWB editors

I have been having this several times. And each time I have to look for the exact location and name of the files you have to handle to fix things. So now I intend to note the reference to the blog entry and at least one forum discussion here. So I will be able to find it.

The problem at hand is that now and again the ‘Palette’ or ‘Object properties’ window pane disappear from the Mapping Editor or the Process Flow Editor. Or any other editor in OWB. No matter how often you activate or deactivate the window pane in the ‘window’ menu, it just won’t appear. I found the solution in the OWB blog, here. But, for my own sake, I will copy it in this blog entry.

Lost your panels in the UI – here is how to fix it…

By jean-pierre.dijcks on May 10, 2007 3:45 PM

I have seen people ask this question a couple of times, and there is actually an easy fix to the problem. The layout for an editor (editors are for example the mapping editor, the data object editor, the profile explorer etc.) is saved per computer to the file system. These files are written everytime the layout is changed and when running the editor OWB reads the layout. That is why, if a panel is “gone” it will stay that way…

When the product is newly installed these editor layout files are not there, so removing them is the solution to “missing panels”. Removal means the panels are appearing as they are in “factory defaults”.

You can find the layout files in “<Oracle Home>/owb/bin/admin” and they are typically either named <editorname>layout.xml (most of them) or <componentname>.layout. For example for mappings, the layout file is MappingEditorLayout.xml, for the console (the designer) the files is called console.layout.

I typically remove the file from the file system, but you may want to try renaming it if you are unsure that this is what you want to try…

Update 11gR2:
Things have changed, for better or worse we’ll have to see. Any way, here’s a link to the 11gR2 way of doing the above.

Workflow replacement

I just saw an interesting post if you have trouble redeploying a process flow. The error you get is something like:

RPE-02062: Itemtype cannot be dropped as it has a running process.

And, yes, that is the link to the post where all the (gory) details can be found. It is documented for 11gR1 but I imagine it will work for 10 and 9 as well.

Links in rather nicely with one of my previous posts. One more thing to look into ….

I have to find out if this works

Resume a failed process flow:

How do I resume a failed process flow?

Looks promising

Useful OMBPlus commands

More to follow, no time to make a real post, but I need to put this in a place I can easily reach.

A method to find out which properties are available instead of the more intuitive

OMBRETRIEVE MAPPING '<name>' TABLE_OPERATOR '<tbl>' GET ALL PROPERTIES

This is the ‘real’ syntax:

OMBDESRIBE CLASS_DEFINITION

eg.

OMBDESCRIBE CLASS_DEFINITION 'TABLE_OPERATOR' GET PROPERTY_DEFINITIONS

I found it here

Another one I need now and again:

The type of activities that can be present in a Process FLow

Finding out activity types that are supported I made a list (OWB 10.2.0.3):

OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET AND ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET ASSIGN ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET DATA_AUDITOR ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET EMAIL ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET END_ERROR ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET END_LOOP ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET END_SUCCESS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET END_WARNING ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET FILE_EXISTS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET FOR_LOOP ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET FORK ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET FTP ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET MANUAL ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET MAPPING ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET NOTIFICATION ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET OR ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET ROUTE ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET SET_STATUS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET SQLPLUS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET SUBPROCESS ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET TRANSFORMATION ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET USER_DEFINED ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET WAIT ACTIVITIES
OMBRETRIEVE PROCESS_FLOW 'EERSTE' GET WHILE_LOOP ACTIVITIES


The above being an extract from this

And if you are really interested in what properties can be used

set model OWB
set classes [OMBDESCRIBE MODEL '$model' GET CLASS_DEFINITIONS]
foreach class $classes {
puts "############### Class: $class ###############"
puts "--- Properties:"
set props [OMBDESCRIBE CLASS_DEFINITION '$class' GET PROPERTY_DEFINITIONS]
foreach prop $props {
puts "------ $prop"
}
}

OMB primer?

I was reading up on my blogs today. A lot of news from the UKOUG conference and it seems I must try to get there some time (next year) as I read nothing but good reviews of the conference and the socialising (socializing in American English? Or is this just Dunglish?). Not to mention the frequent reviews of visits to bars, restaurants and hotels that pop up in the blog posts everywhere. One of the blogs I read by Mark Ritmann mentioned his presentation at UKOUG about best practices for a datawarehouse project, pdf here. I was impressed by the amount of automation using OMB scripting he deemed necessary for the success of a project. Since I am currently working on automation of OWB design/programming using OMB scripting I thought it might come in handy if I do some blogging about my progress. Nothing very fanciful to start with but hopefully useful to anyone who has an interest or a need to start scripting.

Maybe this could even become a series. But, don’t keep your hopes up too much, I tend to get distracted easily. Here goes.

There are several ways to start OMB scripting, each with its own advantages and drawbacks.

Design Center

The easiest one will probably be from the Design Center (in 10g). In standard lay-out that would be the lower right sub-screen:

In this subscreen you have the advantage that you are already connected to the repository and you can directly type in commands like:

OMB+> set OMBPROMPT ON
ON
OMB+> OMBCC '/MY PROJECT'
/MY PROJECT>


What I showed here is the simple command to change the context you are working in and I started with making sure the current context (OMBCC) is shown in the prompt. The context represents the project tree you can browse in the ‘Project Explorer’ on the upper left corner of the screen. Probably very familiar to you already.

You may also have noticed the commands contain mixed case characters. For anyone used to windows that will be an unnerving experience as windows is not case-sensitive and tcl, the language OMBPlus is based on, is very much so. OMBPlus is even kind of picky, not only to case, but also to other language elements like = or ==, () and { }. It will take a bit of getting used to.

As I said, the advantage of this method is that your are already connected. One of the drawbacks in this method is that you can paste parts of scripts in the window and then edit them. But once you have corrected any errors in your commands (that is bound to happen, believe me) you cannot copy the corrected command and paste it back into the editor you copied the commands from. It is one way; paste-into, but not copy-from. That’s enough for now, here comes the second way to start OMBPlus.

OMBPlus.bat (or OMBPlus.sh on unix)

Easily enough started by double clicking the bat-file, or clicking a shortcut to it in the provided menu’s, and then you are presented with the same command prompt from OMB+ only in a separate window:

The first thing you now have to do is to connect to the repository:

OMB+> OMBCONNECT <usrname>/<passwd>@<host>:<port>:<repository database>

Connected.

OMB+> set OMBPROMPT ON
ON
OMB+> OMBCC '/MY PROJECT'
/MY PROJECT>


I have done the same thing as before. Only with the connecting added. And I now have the possibility to copy from the corrected commands I have entered in this shell. This works the same as in any cmd-window in Windows as you have started a .bat file. I won’t go into details about this, I assume them known.

The third method to work with OMBPlus scripting is from inside the Expert Editor. But that is something I’ll hopefully get into in a later post.

Now for some stuff you can use right away.

All fine and neat that there are two (three) ways to connect to OMBPlus, but I have not shown anything you cannot find in the manuals. Be assured, you won’t find much here that is not in the manuals. I am only trying to present it in a way so that you can avoid the pitfalls I have seen. It is after all my blog

Below is a script I have used on 2 different occasions, pleasing two different people, not counting myself.
OMBCC '/<project>/<oracle module>'
set tblList [OMBLIST EXTERNAL_TABLES]
foreach tbl $tblList {
puts $tbl
OMBALTER EXTERNAL_TABLE '$tbl' SET PROPERTIES \
(LOAD_NULLS_WHEN_MISSING_VALUES) VALUES ('true')
OMBRETRIEVE EXTERNAL_TABLE '$tbl' GET PROPERTIES \
(LOAD_NULLS_WHEN_MISSING_VALUES)
}


I left out the connecting. Then I changed the context to an oracle module. Watch for the case again and don’t forget the surrounding quotes. Next I set a variable called tblList to the output of an OMB command “OMBLIST”. In this case I want to list all external tables in the oracle module as I want to change a property of them. For all the other object types like TABLES, FUNCTIONS, SEQUENCES and PROCEDURES you can do the same OMBLIST. Notice that all the object types are in plural. Something to remember.

I now have a variable that holds all external tables in my oracle module. Next I use a tcl command to loop over the contents of this variable. Within the loop I ‘print’ the name of the external table on the screen with the puts command, just to show some progress. And then finally the real work of changing the property. How you find out which properties you can change and how they are changed is something for later (note to self!).

The OMBALTER command has an object type associated with it, but unlike the OMBLIST command it is now in singular (without the ’s’). Next you provide the name of the object. Within quotes! And here we use the ‘running’ variable from the surrounding loop. Then I provide what kind of operation I want to alter, in this case to set a property. After that comes the ‘\’ character to signal that the command continues on the next line. There I provide that name (or names, separated by commas) and values for this property. When more than one property is set, the values are taken in the same order as the properties are provided.

The last command before the end of the loop is a check whether the changing of the property was successful. I retrieve the value of the property and by doing so the value is displayed. Before showing the output of the script there is one thing I must mention again, being the picky-ness of the tcl language. The { character should always be on the same line as the foreach command and there should be a space before the { character. Otherwise it just won’t work and very strange errors may ensue. Below is a sample output of the script.
/<project>/<oracle module>> set tblList [OMBLIST EXTERNAL_TABLES]
EXT_HLP_COMMUNICATION_TYP EXT_HLP_CONTACT_TYPE_EMAIL EXT_HLP_GEWONNEN_SPEELKANS
EXT_HLP_KANALEN EXT_HLP_MEDIUM EXT_HLP_OCT_STATUS EXT_HLP_PRIJSKLASSE EXT_HLP_PR
OVINCIE EXT_HLP_RETOURCODE EXT_HLP_STREETLIFE_OMS EXT_HLP_TERUGBOEKREDEN EXT_HLP
_TOPIC_EMAIL
/<project>/<oracle module>> foreach tbl $tblList {
> puts $tbl
> OMBALTER EXTERNAL_TABLE '$tbl' SET PROPERTIES \
> (LOAD_NULLS_WHEN_MISSING_VALUES) VALUES ('true')
> OMBRETRIEVE EXTERNAL_TABLE '$tbl'GET PROPERTIES \
> (LOAD_NULLS_WHEN_MISSING_VALUES)
> }
EXT_HLP_COMMUNICATION_TYP
EXT_HLP_CONTACT_TYPE_EMAIL
EXT_HLP_GEWONNEN_SPEELKANS
EXT_HLP_KANALEN
EXT_HLP_MEDIUM
EXT_HLP_OCT_STATUS
EXT_HLP_PRIJSKLASSE
EXT_HLP_PROVINCIE
EXT_HLP_RETOURCODE
EXT_HLP_STREETLIFE_OMS
EXT_HLP_TERUGBOEKREDEN
EXT_HLP_TOPIC_EMAIL
/<project>/<oracle module>>


I think that’s about it for today. Happy scripting.

OWB 11g, a tip of the veil lifted?

It seems Oracle is doing a good job of getting OWB developers to attend the ODTUG events the last two years. Last year they introduced OWB 10gR2 there, and that was some real big news then. This year they could not launch the new version yet, but they made sure we are getting curious about what exactly will be in it.

I already had seen the announcement on the warehouse builder weblog and now Mark Rittman is giving some more detail here.  I will not repeat what Mark said, as he has actually seen the presentation on ODTUG and therefore has described it better than I possibly could. But anybody interested in knowing where OWB is going can now go and read it for themselves.