Database script to abstract LiveCyle random table name when creating custom BAM objects

The other day I had to convert a LiveCycle 8.2 BAM demo that was created by one of our partners to LiveCycle ES2 SP2. Unfortunately I ran into few issues because the underlying LiveCyle database structure has changed slightly which invalidated some of SQL statements for the views in Business Activuty Monitoring (BAM).

I realized the table name which stores the process data for a particular process has changed from something like tb_pt_processname to tb_XXXX (XXXX being a random number).

When the table names were fixed, it was relatively easy to move custom BAM (views, data streams, lookups, etc) objects from one environment to another. But now, since the table names are never the same, the SQL statement is always slightly different which causes issues with the BAM objects .

The way to get around the problem is to create a view that always has the same name which points to the random table. Then in the BAM Workbench interface, build the queries against the view instead of the random table.

I created a SQL script that does the following:

1-      Get the name of the random table name for a particular process name.

2-      Create a view that points to that random table name.

Hopefully that will save you troubles when moving custom dashboard objects that query the LiveCycle database from one environment to another.

This is the detail of the sql script:

DELIMITER $$

DROP PROCEDURE IF EXISTS `adobe`.`sp_createview` $$

CREATE PROCEDURE `adobe`.`sp_createview` (IN processName VARCHAR(255))

BEGIN

DECLARE table_name VARCHAR(50);

SELECT database_table FROM omd_object_type where name = processName and domain_id=’workflow’ into table_name;

DROP VIEW IF EXISTS tb_pt_accountopening_bam;

SET @s = Concat(‘CREATE VIEW tb_pt_accountopening_bam AS SELECT * FROM ‘,table_name);

PREPARE stmt FROM @s;

EXECUTE stmt;

END $$

DELIMITER ;

CALL sp_createview(“pt_accountopeningbamdemo/processes/accountopening_bam”);

Jasmin

VN:F [1.9.22_1171]
Was this helpful? Please rate the content.
Rating: 0.0/10 (0 votes cast)
This entry was posted in Adobe LiveCycle ES2 (9.0.x) and tagged , , , , . Bookmark the permalink.

2 Responses to Database script to abstract LiveCyle random table name when creating custom BAM objects

  1. Dhanesh says:

    Jasmin, I want to create a UDF for ES2 BAM. Where can I possibly find the cognosAPI.jar required to create it?

    Thanks.
    Dhanesh

    • Jasmin says:

      Try to see if obiJAR.jar located in C:\Adobe\Adobe LiveCycle ES2\LiveCycle_ES_SDK\misc\Business_Activity_Monitoring\samples\cqmdb\ear gives you the classes you need.

      Jasmin