Hi there AppWorks fans,

Welcome to a new installment of AppWorks tips.

Finally, a dive on database level where we see what needs to happen when we would like to move BPM data to another database. This data can get significantly expand in size! It’s good to see we have the possibility to move this data to another location. In several posts we saw the below screens passing by during the creation of a new BPM service container in the ‘System Resource Manager’ artifact. This post we will create that new database configuration, and we play around with it…It will be a great deep dive again! 😉

These are the three database settings I’m talking about (In perspective of the BPM service container in the ‘System Resource Manager’ artifact):

The ‘Admin Database’

The ‘Scheduler Database’

The ‘Rule Repository Database’

Let get right into it…

Normally on the above screen we just select ‘system’, and we select the current ‘Cordys System’ database configuration like this:

Is it a smart choice? Well…This depends! For a development environment it’s not that bad, but the more you move to a production environment it might not be the best choice to make. You might want to separate database data over separate databases or maybe to another database server for performance reasons…Who knows!? Because I’m just as curious as you are, we just make the database configuration switch in this post and see what the hurdles are to take.

To get track on things we create a ‘dummy’ BPM, we generate a service out of it, and we make sure our new BPM service container can handle the request with a ‘system’ database connection! After this we will create a new database configuration and see if things will still work!?

So, have a project available with just a simple BPM:

This is my BPM:

Publish it, and you should be able to run the thing by right-clicking it.

In that same right-click context menu we are also able to generate a service out of the BPM (from the ‘Execution’ submenu!):

Save it nicely in the ‘services’ folder of the project, mark the operation and click ‘Finish’. Now right-click the generated service operation and do a first test:

You’ll end up with an error: Service Group Lookup failure.

Time for a BPM service container!

BPM service container creation

In quick steps:

• Open the ‘System Resource Manager’ artifact
• In the top left corner, hit the ‘new service group’ button
• Select the ‘Business Process Management’ connector and click ‘Next >’
• Give a name for the group (like bpm_service_group), select our ‘dummy_bpm’ service, and click ‘Next >’
• Give a name for the service itself (like bpm_service_container), automatically select startup, assign an OS Process, and click ‘Next >’
• Now you reach the point where the databases need to be selected; For now we just select the ‘system’ space where we have the option to select ‘Cordys System’. Let’s do this for the ‘Admin DB’, the ‘Scheduler DB’, and the ‘Rules DB’ (is available when you mark the ‘Enable Rule Engine’ option in the “Process Engine” tab!). When done, click ‘Next >’
• ‘Finish’ the wizard.

Time for another test of the webservice; With this time a valid response:

You can also double-check the PIM artifact on this one! 😏

Database configuration update

Alright…Time for the next step…Back to the properties of our service container. To be more specific, the ‘Admin Database’ tab!

Switch back to the organization specific space and create a new database configuration in our own organization:

You get a new modal popup to fill in with data (don’t forget to mark the ‘Create New Database’ option!):

This is my connection string: jdbc:postgresql://appworks21-3.mydomain.com:5432/bpm_admin

Hit the ‘Save’ and let’s have a view with HeidiSQL if something happened!?

OK…So, database bm_admin gets properly created, but there is no table structure (yet!).

Time to save our service container and do a restart of that same service container…Back to HeidiSQL for a refresh…

Nice…A clean table structure for handling BPM data.

I did a quick check on the VM and found some interesting SQL creation scrips which are probably used during this first initialization for my PostgreSQL database: sudo find /opt/opentext/AppWorksPlatform/defaultInst/components/ -type f -name *POSTGRESQL*.sql

Have a view on this file to see the references of the screenshot:
sudo less /opt/opentext/AppWorksPlatform/defaultInst/components/bpmengine/database/createscripts/BPM_POSTGRESQL.sql

So, we have our database, we have our tables, time to retest our service (again!)

Sounds logic, as when we do a select statement on the bpm_model table with SELECT * FROM bpm_model, I don’t get any results!?

Time for the ‘Administration’ manual! #RTFM

In the manual ‘OpenText AppWorks Platform 21.3 Administration Guide.pdf’ I found a section “Changing the database configuration for service containers” in “Chapter 43 - Administration”

Aha!…We just found our next section… 😅

HeidiSQL export/import

Back to the HeidiSQL client and make a connection to our ‘appworks_db’ database. In the public domain you should “see” the exact same tables passing by like the screenshot above. When we call the same select statement on the ‘bpm_model’ table we get exactly the missing data!

Time for an export of the data (incl. all other related tables) and within HeidiSQL we have a nice export tool in the ‘Tools’ menu called (how appropriate!): “Export database as SQL”. Hit it and find yourself an interesting modal popup:

Select the tables to export and let it write the information into an .sql file. The result? See for yourself!

That’s exported data…Time to import it into our new database. We switch connection in HeidiSQL. Once connected, we can call the action ‘Load SQL file’ from the “File” menu.

You see all the INSERT-statements passing by, but we need to execute them is a certain order as there are relations created between them; This is the order I used:

• bpm_model
• bpm_model_revision (has a relation on: bpm_model_revision.model_id)
• bpm_model_translation (has a relation on: bpm_model_translation.model_revision_id)
• bpm_model_activity (has a relation on: bpm_model_activity.model_revision_id)

Let’s have a look if it’s imported fine and understand the structure, and the relations just created!:

1
2
3
4

SELECT * FROM bpm_model;
SELECT * FROM bpm_model_revision;
SELECT * FROM bpm_model_translation;
SELECT * FROM bpm_model_activity;

Time for a retest of our service (without any restart of any service)!

That’s a green light for me with a returned instance ID:

Back to our HeidiSQL tool. Let’s do a query on table ‘process_instance’:

Tadaaaa! Our first running BPM instance saved in the new database. Soda time… 🥤

Now for the other databases!

Back to the ‘bpm_service_container’ in the ‘System Resource Manager’. Get the properties, go to the BPM tab, and within that tab update the database setting of the ‘Scheduler’ database…Just like this (with the creation of a new database configuration as well as the creation of the database itself…Like we did before!):

I see again the same things happening in HeidiSQL. So, database gets created nicely. After service restart, we see a correct filled database, and the correct table structure.

All tables are empty, and those tables are also empty in the main database ‘appworks_db’. That’s because we didn’t schedule anything (yet!).

How to schedule stuff in the “context” of a BPM!?

I just followed my own ‘Schedule’ post on this feature but didn’t see anything inserted into the tables of my brand-new database!?

What I did see is a database configuration for the ‘Scheduling’ service container in the ‘system’ space! So, what happens when we create a new ‘Scheduling’ service container in our own organization and also create a new database config for this one!? I suppose it uses the same tables…right?

So, in my own organization, I create a new service group / container of type ‘Scheduler’. During the wizard, I select all the available service interfaces (2 of the Cordys Scheduler package!). For this test a use a connection string like this
jdbc:postgresql://appworks21-3.mydomain.com:5432/bpm_scheduler2 where I point to a “bpm_schedule2” database to see any differences!

Aha….I see exactly the same table structure passing by with empty data!

So, probably my current deployed schedule from the mentioned post will also fail now:

Time to make an SQL export with HeidiSQL of the corresponding schedule tables of the original ‘appworks_db’ database and import the rows into the new ‘bpm_schedular2’ database of our ‘Scheduler’ service container.

A retest on my schedule gives me a valid response again!

Now for that great question: What about the ‘bpm_scheduler’ database created in my own BPM service container? How to get data in it? Well, let me know in the comments as I couldn’t find a valid action to try-out!?

Another great question would be: Can we move those 2 ‘Cordys Scheduler’ service interfaces from the ‘Schedule’ service group to the ‘BPM’ service group!? 😎

All interesting questions, but I leave the answers with you, as I already made a sufficient insight for this post.

…

Let’s continue for the ‘Rule’ database!…Keep in mind we probably will see a same kind of structure like the ‘Scheduler’ as the ‘system’ space also has a ‘Rule Management’ service group up and running. Let’s find out and with the database update in our organization specific BPM service container.

First enable the ‘Rule Engine’ in the ‘Process Engine’ tab. After this change you’ll get a ‘Rule Repository’ tab where we can make an update like this:

With HeidiSQL, I see this table structure passing by (clean and empty):

(again!) How to ‘Rule’ stuff in the “context” of a BPM!?

Well, the quickest connection point I can imagine is the insertion of a document of type ‘Decision table’. I shall be honest to you, I never used such a document type, but we’ll just see what value it can bring…Sounds like a 2.0 ‘Decision’ construct we can use on a BPM!

So, we just create a new document of type ‘Decision table’; It all looks like this:

Some notes:

• The dragged & dropped ‘mqtt_data’ elements came from an ‘XML schema’ type of document which I have available from my ‘schemas’ folder in the project. We used this schema also for another post

  1 2 3 4 5 6 7 8 9 10 11 12

  <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="mqtt_data"> <xs:complexType> <xs:sequence> <xs:element type="xs:string" name="type"/> <xs:element type="xs:string" name="broker"/> <xs:element type="xs:string" name="topic"/> <xs:element type="xs:string" name="payload"/> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>

• In the top bar, you have icons to show ‘Select Attributes’ and ‘Select Actions’ (you can also switch with the tabs in the left-bottom of the screenshot)

• A decision table needs a ‘Rule Group’; I just created one on the fly and added the decision table to it:

So, what did we just build? A simple document where we can check if the data ‘type’ element of my schema equals value “ws” and then start the ‘Invoke Web Service’ which in my case just calls the service ‘GetAllRoles’.

Just save it all and do a publication; leave it for now as we just would like to see something happening into our database.

Hmmm…during publication you’ll probably get an error: All the service containers that respond to messages for 'Rule Management' in this JVM either stopped or never started

Ahaaa! There we go…That’s the same behavior as our ‘Schedule’! We could start it in the ‘system’ space, but let’s skip it and create a new service group for connector type “Rule Repository” in our own organization! 🤔

• Call it: rules_service_group
  • Select all the available service interfaces
• With service name: rules_service_container
  • Start automatically
  • Assign to OS Process: Application Server
• Create a new database connection: jdbc:postgresql://appworks21-3.mydomain.com:5432/bpm_rules2
  • I use a new database with name ‘bpm_rules2’ to see the difference with our current available ‘bpm_rules’ database!

The conclusions?

• Well, this new service container indeed creates that same database table structure!

• Can I now publish my decision table with an error? Yes…Of-course! 😅

• How about HeidiSQL? Well, after that publish, I see my tables (in the ‘bpm_rules2’ database) updated with the crafted information! Check these tables:

  1 2 3

  SELECT * FROM bizrule; SELECT * FROM br_triggers; SELECT * FROM rulegroup;

Time to use our crafted table in our currently available ‘dummy_bpm’…Like this…I guess!?

I don’t add any message mapping. Just save it, do a publication, and execute it!?

The PIM tells me it is ‘Aborted’…why? DECISIONCASE HANDLER : Rule Engine is not initialized.

OK, but that ‘Rule Engine’ is a setting in my own created BPM service container! Could it be our ‘bpm_rules’ is empty, and we just need to inject it with the data of our ‘bpm_rules2’ database?

This is the export, and the order of insertion in ‘bpm_rules’ was this: rulegroup, br_triggers, bizrule

A retest of the BPM…? hmmm…still the same error!?

Alright…Now what!? I also tried some other service changes, but none of them solved the error?…Is it the lag of decision table knowledge?…Back to the root! I create a snapshot of my VM and tried the above steps again from a clean platform! The result? Well…It eventually works with these notes:

• A BPM call via the generated webservice runs (as I always forget!) through our own created BPM service container! A run from the BPM modeler still uses the BPM service container of the ‘system’ space!

• It also looks like there is some caching involved; As I also needed to do a “Validate & Build” on the BPM, do a save, and also a publication again.

  • I get a warning for the decision table activity and added a simple mapping like this:

• The custom ‘Rules’ database never came to life for my own BPM service container (triggered by my generated BPM webservice). I also though this might be some caching, but even after a re-generation of the service it’s not working for some reason!? Still with the Rule Engine is not initialized.

…A FEW OURS LATER…

These were the steps to make my database config valid only for the BPM run (so, not the webservice call)

• In the ‘system’ space, open the BPM service container, mark the option ‘Enable Rule Engine’ in the ‘Process Engine’ tab

• From the ‘Rule Repository’ tab configure the custom database like we already did.

  • For me this time: jdbc:postgresql://appworks21-3.mydomain.com:5432/bpm_rules3

• Save, and restart the service container

• Back in our own organization where I removed the ‘Enable Rule Engine’ from the of my own BPM service container!

• Do a ‘Validate & build’ on the ‘dummy_bpm’ (where created the mapping for the decision table activity). Save it and publish it.

• When I run my BPM, it gives me a status back of ‘Running’. In the logging I can clearly see why!? NullPointers all over the place….hmmmm

  • sudo tail -999f /opt/opentext/AppWorksPlatform/defaultInst/Logs/Application_Server.xml

• Back again to the ‘system’ space and update the database of the BPM service container back to the original ‘Cordys’ database, save, and restart

• Check the PIM for my ‘Running’ BPMs…WHAT!…They are all ‘Completed’…Instantly! #WTF

OK, my friends…Time to throw the towel into the ring. I did a double check in the ‘appworks_db’ with HeidiSQL for the ‘Rule’ related tables, but don’t see anything inserted from what I already concluded myself.

Did we learn something? O boy, O boy, DID we learn something! Hell Yeah!! At deepest level of the system we now have a far better understanding on how things work together and how to manipulate and migrate information from one database to another one. Maybe it didn’t work out as expected, but we for sure learned something from it and that’s the most important “Rule”! 😣

Manage Database Configuration

One thing I missed telling you all…

It’s an overlooked button (just like the once described “Manger EIS Repository Configurations” button in the same bar of the screenshot). Have a look behind this button in the ‘System Resource Manager’:

All the mess we’ve created in 1 nice overview! 😥

I give it a chop-over-the-ditch “DONE” where we gave a great insight on database level. It’s always a good thing to know what is happening in the back-end of our low-code platform, and we even exposed a small data migration steps from one database to another! 🤗 Will I ever do this experience again!? Well, maybe on a production environment somewhere in the future when it’s overheated, and now I know (and you also) what is required to make it to the next step. Have a great week-end, and I see you in the next post…next week.

Don’t forget to subscribe to get updates on the activities happening on this site. Have you noticed the quiz where you find out if you are also “The AppWorks guy”?