How to trace database commands from WebSphere Portal

| | Comments (0)
Hello Folks:

Widespread_ChiB_sm.jpgI'm now blogging about something new: WSP! You jam band dance freaks immediately think Widespread Panic, but no, I'm talking about IBM WebSphere Portal. After so many years of working with the familiar Oracle/BEA/Plumtree portal, it's stimulating to now work with the mysteries of a new product.

But mysteries are meant to be discovered. So here's one for today: how to trace database from WebSphere portal. And it seems appropriate to term this a mystery. When I asked two IBM consultants about help getting oriented to the schema, they both told me they never look directly in the database, with one emphatically saying in ten years he's never done it. Well, the time is right to unveil some new info.

The WSP database schema is harder to immediately understand than Plumtree's was, but the product comes with features to lay it out for you. Plumtree had PTSpy logging that would show you the exact queries and commands executed as the system marched along. With WSP, those who config and dig can find similar information in the trace.log file. Here's the process:

1. Turn on tracing. Navigate to the administrative area Portal Analysis > Enable Tracing, then add these trace strings:

com.ibm.wps.datastore.*=all:   
com.ibm.wps.services.datastore.*=all
set-logging.PNG

2. Perform the actions through the portal whose database queries and commands you're interested in. I recommend you submit some easily traced values. For this example, I created a page with the name "simplicity-name," description "simplicity-description," and so forth.

3. Search the log files for the commands you're interested in. Since the logs rollover quickly from trace.log into timestamped files like trace_15.04.23_20.15.47.log, you may want to search on rolled files too using a pattern like trace*log.

My first search was for this:

grep -3 simplicity ./wp_profile/logs/WebSphere_Portal/*   | grep -i replace

The results included this:

StatementTracer executeUpdate Replaced SQL: INSERT INTO 
customization.PAGE_INST_LOD (PAGE_INST_OID, LOCALE, TITLE, DESCRIPTION) 
VALUES 
(00004957101501150159803225B974D380CC(Z6_9QL0HA40L80I50A659E9N93GC6), 
en, simplicity-title, simplicity-description)
/opt/middleware/wp_profile/logs/WebSphere_Portal/trace_15.04.23_20.15.47.log:[4/23/15 20:15:46:022 PDT] 000000ec SQLStatementT 3 com.ibm.wps.datastore.impl.debug.SQLStatementTracer executeUpdate Replaced SQL: INSERT INTO customization.PAGE_INST_DD (PAGE_INST_OID, NAME, VALUE) VALUES (00004957101501150159803225B974D380CC(Z6_9QL0HA40L80I50A659E9N93GC6), com.ibm.portal.friendly.name, simplicity-friendly)

And from that I could tell my new page had the ID of 00004957101501150159803225B974D380CC. So with that info, I could search for all the SQL related to it:

grep -3 00004957101501150159803225B974D380CC ./wp_profile/logs/WebSphere_Portal/*   | grep -i replace

That gave me many lines. I wanted to know the summary of all the tables that were inserted to as my new page was created, so I searched just the uniq tables with insert statements, stripping out the exact commands:

$ grep -3 00004957101501150159803225B974D380CC ./wp_profile/logs/WebSphere_Portal/*  
 | grep -i replace | grep -i insert | sed s/".*INSERT"//g | sed s/"(.*"//g | sort | uniq
 INTO customization.COMP_INST
 INTO customization.COMP_INST_DD
 INTO customization.PAGE_INST
 INTO customization.PAGE_INST_DD
 INTO customization.PAGE_INST_LOD
 INTO customization.PAGE_INST_MAD
 INTO customization.PROT_RES
 INTO customization.UNIQUE_NAME

I hope that's enough to get you started. After getting the first glimpses of what actions use which tables, you'll then be able to start piecing together relationships between tables and queries that can answer questions for you. For example, here's one I put together to see the RELEASE database's various elements that make up Web Application Bridge portlet entries:

select * from outbound_config, outbound_mapping, outbound_policy, outbound_cookie_rule
where OUTBOUND_MAPPING.OUTBOUND_CONFIG_ID = OUTBOUND_CONFIG.OID
and OUTBOUND_POLICY.OUTBOUND_MAPPING_ID = OUTBOUND_MAPPING.OID
and OUTBOUND_COOKIE_RULE.OUTBOUND_POLICY_ID = OUTBOUND_POLICY.OID;

And what keeps you up at night?

Enjoy!

Leave a comment