IBM BPM: Listing iFixes installed

From the following article
http://www-01.ibm.com/support/docview.wss?uid=swg21294769

• On Linux
  at
  /var/ibm/InstallationManager/installed.xml
  

• On Microsoft Windows XP, Windows 2000 and Windows 2003
  at
C:\Documents and Settings\All Users\Application Data\IBM\InstallationManager\installed.xml


• On Microsoft Windows Vista
  at
  C:\ProgramData\IBM\Installation Manager\installed.xml
  

• This is also available for viewing by selecting
  Windows Start Button > IBM Installation Manager > View Installed Packages

For specific WAS_HOME the following method can be used:

/opt/IBM/BPM80/AppServer/bin/versionInfo.sh -ifixes

Converting GMT dates to local time zone

In TW 6.x the dates in LSW_TASK table are stored in GMT time zone. When querying the table directly (should be avoided in general) the dates can be converted to a given time zone similar to the following:

SELECT
    task_id,
    to_char(rcvd_datetime, 'yyyy/MM/dd HH24:mi:ss') AS rcvd_datetime,
    to_char(from_tz(CAST(rcvd_datetime AS TIMESTAMP),'GMT') AT TIME ZONE '+03:00', 'yyyy/MM/dd HH24:mi:ss') AS local_rcvd_datetime
    FROM
        lsw_task

In order to automate conversion and take into account Daylight Saving changes the query can use dbtimezone Oracle function (assuming the Oracle instance is configured correctly):

SELECT
    task_id,
    to_char(rcvd_datetime, 'yyyy/MM/dd HH24:mi:ss') AS rcvd_datetime,
    to_char(from_tz(CAST(rcvd_datetime AS TIMESTAMP),'GMT') AT TIME ZONE dbtimezone, 'yyyy/MM/dd HH24:mi:ss') AS local_rcvd_datetime
    FROM
        lsw_task

If the date needs to be displayed in TW's end user time zone, than the offset can be retrieved via tw.local.user_timeZoneOffset variable:

SELECT
    task_id,
    to_char(rcvd_datetime, 'yyyy/MM/dd HH24:mi:ss') AS rcvd_datetime,
    to_char(from_tz(CAST(rcvd_datetime AS TIMESTAMP),'GMT') AT TIME ZONE '<#= tw.local.user_timeZoneOffset #>', 'yyyy/MM/dd HH24:mi:ss') AS local_rcvd_datetime
    FROM
        lsw_task

IBM BPM errors after applying Fix Pack 5 for DB2

After applied Fix Pack 5 to my DB2 Express-C install, my IBM BPM 7.5.0.1 Advanced started to throw errors related to Messaging  Engine (ME) not being available when I connected to the server with Process Designer. The status of the Messaging  Engine for the Process Server was "Starting".
First think I did I removed all SIB tables from all schemas (that's what I usually do if I see any ME related errors :) ), but that obviously did not help.

In SystemOut.log of the Messaging cluster I found the following error

DB2 SQL Error: SQLCODE=-443, SQLSTATE=38553, SQLERRMC=SYSIBM.SQLTABLES;TABLES;SYSIBM:CLI:-805, DRIVER=3.61.65

Which appeard to be caused by the following problem

https://www-304.ibm.com/support/docview.wss?uid=swg21449630


After I did the rebinding for all DB2 databases everything started to work.

Upgrading Oracle XE 10g to XE 11g

I've been using Oracle XE with several Lombardi Teamworks and WLE installs on my laptop. In order to run  latest IBM BPM 7.5+ software I need Oracle 11g, so XE 11g instead of XE 10g was a natural choice. Besides, I am a bit tired of DB2 ;)


The upgrade is very straightforward. I pretty much just followed the documentation from Oracle below
http://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#autoId25

Here are my notes:

- the script gen_inst.sql creates the sql files in OracleXE bin folder, next to sqlplus command
- there were "object exists" errors during the first import which did not seem to cause any issues later