Hi there AppWorks fans,

Welcome to a new installment of AppWorks tips.

Last time, we had a look at the custom configuration settings for the deployed (BPM) models delivered from our package. During this exploration, we stumbled on a term called Published Source Layer (PSL) which are the “descriptions” for our (new) deployed models to an organization. We learned already how to upgrade our PSL data from the ‘/system’ space after a package is deployed, but this week we’ll have a closer look at it; how we can remove it, and how we can clean our database tables from it; Why? because I see lots of organization where this PSL data gets corrupted with errors which makes it nearly impossible to upgrade it for new future packages!

Let get right into it…

We continue where we stopped last time; The steps in short:

• Create a DEPLOY organization
• Create a BPM in your project (monitoring doesn’t matter for this post)
• Create schedule starting the BPM
• Start Schedule service container in ‘system’ under the ‘System Resource Manager’ artifact
• Create package 1.0.0 (organization specific)
• Deploy package in organization (from ‘/system’) under the ‘Application Deployer’ artifact
• Switch to the DEPLOY organization
• Deploy the schedule (if not yet the case), and run the schedule manually
• Check the PIM; on graphical view you’ll get the PSL not upgraded error!

…

Now, what we first would like to know is where this PSL data is stored. For this we open the CAP file with a ZIP-tool like 7-Zip. This CAP file has a folder named ‘pslContent’ and in this folder you’ll find a document called ‘pslContent.xml’; What can we find in this document:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<psl:PublishedSourceLevel pslID="...">
  <!--For each construct (like 'End', 'Start', 'Activity')-->
  <Instance>
    <TranslatedText>...</TranslatedText>
  <Instance>
  <!--For each construct-->
  <Instance>
    <TextIdentifier>...</TextIdentifier>
  <Instance>
  <!--BPM document with name and definition (the flows between activities)-->
  <Instance>
    <BusinessProcess>...</BusinessProcess>
  <Instance>
  <!--Project and Independent Software Vendor (ISV) package information-->
  <Instance>
    <Project>...</Project>
  <Instance>
  <!--The root location of the BPM in the project-->
  <Instance>
    <QualifiedNameRoot>...</QualifiedNameRoot>
  <Instance>
</psl:PublishedSourceLevel>

In short; All details of our BPM flow needed to open the graphical view of a BPM instance in the PIM!

…

Before we continue upgrading our PSL data in ‘/system’, we’ll first make sure to enable some DB logging and get some eyes on insert/update statements:

1
2
3
4
5
6

sudo vi /var/lib/pgsql/data/postgresql.conf
# Update this setting: log_statement = 'all'
# In "Vim" you can search with `/` and use `n|N` for next/previous entries
systemctl restart postgresql
# The '-i' is for case-insensitive; '-E' for expression matching!
sudo tail -999f /var/lib/pgsql/data/log/postgresql-{day}.log | grep -i -E "insert|update" -A 3 -B 3

With our monitoring in place, it’s now time to upgrade the PSL data in ‘/system’ from the ‘Deployed Process Models’ artifact; Like we did last time. What will we see in the backend? Well, how about a lot of calls to the ‘XDS_DOCUMENT’ table. Read all about this table in this post.

I see insert into XDS_DOCUMENT... and update XDS_DOCUMENT...; Also interesting to watch is the status tag: <Status>PENDING|INPROGRESS|COMPLETED"</Status>; AND there is one extra thing I see passing by…It’s called PSL {ID} for ISV Level and I do remember this after a view in the XDS storage with the CMC tool!

A moment of “Ahaaa”!

Note that you can also search in the logging for the ‘pslID’ from the XML-part above! This same ID is found as entry in the PSL upgrade panel.

After this PSL upgrade, you can use your PIM again on the BPM instances without issues.

PSL removal tool

Now we’re talking! What we can add should also be possible to remove; For this, our beloved platform is delivered with a CLI tool called ‘RemovePSL’. We saw it passing by our journey before in this post, but that was a totally different topic about CI/CD…

To make it work, follow these bash commands:

1
2
3
4
5
6
7
8
9

cd /opt/opentext/AppWorksPlatform/defaultInst/bin/
sudo ./cws.sh help
./cws.sh help RemovePSL
#For a test run first!
./cws.sh  RemovePSL -h 10
#After this your PIM gives and error again:
#  The BPM cannot be displayed because either it was deleted in design time, 
#  the PSL content was deleted, or you do not have the permission to view it!
./cws.sh  RemovePSL -h 10 -v -x

This is the output:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Starting local CWS client...
Starting to remove PSLs...

1 PSLs have been selected for processing.

PSL 'PSL 00001 for ISV Level' is removed.
Completed 1 out of 1.
Processing of all PSLs is complete.

1 PSLs out of 1 selected PSLs are removed.
>> -------------START---------------
         1 *        36.51ms  self [       36.51ms]; total [     5439.18ms]  : CWSPSLRemovalTool.doOperation history: 10
         1 *       154.35ms  self [      154.35ms]; total [      154.35ms]  : getPSLUsageCheckers
         1 *       429.24ms  self [      429.24ms]; total [      429.24ms]  : pslUsageChecker.isUsed com.cordys.bpm.pslremoval.checker.BPMPSLUsageChecker
         1 *       739.18ms  self [      739.18ms]; total [      739.18ms]  : pslUsageChecker.isUsed com.cordys.rule.pslremoval.checker.RulePSLUsageChecker
         1 *      4079.90ms  self [     4079.90ms]; total [     4079.90ms]  : RemovePSL: PSL 00001 for ISV Level
<< --------------END----------------

Notes:

• It looks like the -h 10 is not appreciated…All PSL data is removed; Or I don’t understand the flag!? Comment me…
• From the CMC tool perspective, the “PSL {ID} for ISV Level” entries are gone as well!
• Entries in the PSL Upgrade panel in ‘/system’ are still there!? Read about it below…

…

WARNING…The database manipulation from below is under your own responsibility; So #CYA!! I have a sandbox to do this stuff safely!

I just did a text search in my Postgres DB (that’s an option in HeidiSQL) for the PSL ID and could only find entries in ‘bpm_model_revision’ (our BPM template), ‘process_instance’ (our BPM instance run from the schedule), and ‘xds_document’ (PSL leftovers in the XDS store as the ‘PSL {ID} for ISV Level’ node is already removed!).
It’s not a recommendation, but I removed the entries from the ‘xds_document’ (2 of them; one ‘ID_TYPE_XMLDOCUMENT’ and one of type id ‘ID_TYPE_FOLDER’). This removed the entry from the PSL Upgrade panel as well; At lease, the ‘ID_TYPE_XMLDOCUMENT’ row; The other was just because it felt good.
To make sure I didn’t break stuff, I did a deployment of a new versioned package as upgrade on the previous package. This one deployed smoothly, entered a new PSL ID as entry to upgrade, and the package deployment was again a success!

FYI: I also played with rollback/undeploy of the package and redeploy, but that gave errors…Not sure if they are related, but I give it 99% chance; So, don’t remove rows like this!

…

At customers (that’s why I wanted to try this all out in a sandbox!), I see also ‘Failed’ states in the PSL upgrade panel with a stack trace as an error. This also prohibits the PSL upgrade of new packages:

To solve these “failed” PSL rows, I see two solutions:

1. Remove the failed rows from the database table
2. Update the failed rows in the database table to a “completed” state with a nice comment

You can imagine the second option is my favorite…It takes some smart querying, but you can do something like this (at least for a Postgres DB):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

/*To find one*/
SELECT *
FROM xds_document
WHERE NAME = '{pslId}'
  AND type_id = 'ID_TYPE_XMLDOCUMENT'
  AND content LIKE '%FAILED%'

/*To update one*/
UPDATE xds_document
SET content = REPLACE(content,'FAILED','COMPLETED')
WHERE NAME = '{pslId}' 
  AND type_id = 'ID_TYPE_XMLDOCUMENT' 
  AND content LIKE '%FAILED%';

/*Or even smarter with a regular expression and nice "Manual FIX" message*/
UPDATE xds_document
SET content = content = REGEXP_REPLACE(content,'<Status>(.*?)<\/ErrorDetails>','<Status>COMPLETED</Status><PercentageOfCompletion>0</PercentageOfCompletion><ErrorMessage/><ErrorDetails>Manual FIX</ErrorDetails>')
WHERE NAME = '{pslId}' 
  AND type_id = 'ID_TYPE_XMLDOCUMENT' 
  AND content LIKE '%FAILED%';

AGAIN…Keep in mind the stuff above is dangerous…Especially in a production environment…#CYA!

Q/A time

1. When we remove the PSL, can we also reconstruct it with a redeployment of the same package?
   Well, not what I’ve experienced. What I do see is after removing all the PSL data, you can deploy a new version of the package with new PSL data which you can upgrade again!

2. Can we find also some PSL services with the ‘Web Service Interface Explorer’ artifact?
   Yes, have a search for namespace http://schemas.cordys.com/cws/runtime/types/workspace/upgrade/PSLRepositoryUpgradeHelper/1.0 with services like ‘doUpgradePSL’, ‘getAllPSLStatus’, or ‘getPSLStatusById’…All part of the CI/CD strategy!

3. What about the auto-deployment of the PSL data on deployment?
   Well, that’s an option; Only it takes resources during deployment. Better upgrade your PSL data in the evening when the rest of the world is sleeping and pretend something magic happened overnight. At least that’s how a ‘Cleaner’ would manage it!

Yes, I’m reading an interesting book called “Relentless” from Tim S. Grover

…

FYI: Auto deploy the PSL data…

Add this development.publish.to.psl.enabled=true to this file /opt/opentext/AppWorksPlatform/defaultInst/components/cws/config/cws.properties and restart the ‘Collaborative Workspace’ service container in ‘/system’ or just restart TomEE…I haven’t tried it!
The documentation on this setting is telling me the default is TRUE, but it looks to me like the default is FALSE; Otherwise, I wouldn’t need to upgrade the PSL myself!

After some “secret” research I found that it’s really FALSE by default:

1
2
3
4
5
6
7
8
9
10

#To find the JAR containing the setting
find /opt/opentext/AppWorksPlatform/defaultInst/components/cws/ -iname '*.jar' -printf "unzip -c %p | grep -q 'development.publish.to.psl.enabled' && echo %p\n" | sh

#To find the Class (in the JAR) containing the setting :)
find /opt/opentext/AppWorksPlatform/defaultInst/components/cws/ -iname '*.jar' -print | while read jar; do
  echo "$jar:"
  unzip -qq -l $jar | sed 's/.* //' | while read cls; do
    unzip -c $jar $cls | grep -q 'development.publish.to.psl.enabled' && echo "   "$cls
  done
done

This is the decompiled code; It’s definitely FALSE! 🤠

1
2
3
4
5

//JAR:      opt/opentext/AppWorksPlatform/defaultInst/components/cws/cws.jar
//CLASS:    com.cordys.cws.internal.settings.SettingsManager.class
public static boolean isTempPublishToPSLEnabled() {
    return checkBooleanProperty("development.publish.to.psl.enabled", false);
}

That’s a busted “DONE” where we learned all the ins and outs of the PSL data for what (at least I) wanted to see. With this again some interesting Unix commands which we can use for the future. Again, cover your ass when you manipulate things directly in the database. Have a great PSL weekend and join me next week for a new post on (again) an interesting question passing my own AppWorks journey.

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”?