Posts Tagged ‘database’

Monitor WCI Analytics with a DB query

Wednesday, May 9th, 2012

Lately I’ve found myself crafting all kinds of database queries for everything from monitoring to reporting, and I’ll be sharing many of those queries on this blog in the coming weeks. Today’s post answers a simple question: If a WebCenter Analytics service falls in the forest and no one is around to hear it, does it make a sound?

Or, more to the point, occasionally the Analytics Collector service keeps running (so many of your existing monitors don’t see it as “down”), but it stops recording data for one reason or another.

The trick is to create a monitor that basically says “let me know if no user logins have occurred in the past day”, because if nothing has been recorded, either the site you’re tracking sucks or the Analytics Collector is sucking. Using whatever tool you’d like (HP’s SiteScope is a popular one, and we use Function1’s Watcher on some sites), just create a monitor that runs the below query once per day, and notifies you if ZERO results are returned, which would indicate a problem with the collector:

select count(1) 
from asfact_logins 
where occurred > (getdate() - 1)

That way, you won’t have to explain to the boss why your Analytics report looks like this:

WebCenter Document Hit Counts

Tuesday, November 22nd, 2011

Wondering how popular your Plumtree Knowledge Directory documents are? Sure, you could use WebCenter Analytics, but did you know that Plumtree has always captured card download metrics?

Try this query to get hit counts for each card in the KD, and marvel in the awesomeness that is your popular documents – regardless of how many times your Analytics collector dies:

SELECT     ptc.name, ptcs.CARDID, ptcs.POPULARITY, ptcs.HITCOUNT, ptcs.LASTHIT
FROM         PTCARDSTATISTICS ptcs, PTCARDS ptc
WHERE     (LASTHIT > GETDATE() - 30) AND (HITCOUNT > 0) and (ptcs.cardid = ptc.objectid)
ORDER BY HITCOUNT DESC

Interesting side note – notice how all those document hit counts are a multiple of 10? The reason for this is a scalability approach that Plumtree came up with years ago. The idea is that rather than writing to the database every single time a document is downloaded, the code only increases the hit count 10% of the time, but increases the count by 10 each time. So, statistically, the counts are accurate over time, but the database is written to much less frequently.

Increasing the Character Limit for ALI Studio in SQL Server

Sunday, May 22nd, 2011

By now you’re aware of our stated problem – we need to increase the size of text fields in Aqualogic Studio. We’ve already found the file used for the JavaScript, and used a new Cool Tool (HxD) to assist in the recompile, but there’s still a problem: when Studio creates new forms, it also creates a separate table in MS SQL Server. And the size of text fields in those tables that already exist is still 1,000. So if we just update the Javascript, our existing forms aren’t going to have the proper error checking, because our JavaScript is preventing field sizes of, say, 10,000 characters, but at the database layer, the size of those fields is still 1,000 characters. Even worse, if you try to increase the size of the table in the code to >4,000 characters, SQL Server will reject it because the regular nvarchar data type doesn’t go over that limit:

2011-04-21 14:54:54,819 ERROR [rocessor25] AppDesignerHandler: Error processing wizard form post
Error creating new user database ‘Survey Database’.
– [XNRC39]The size (10000) given to the column ‘test_10000’ exceeds the maximum allowed for any data type (8000).
at com.plumtree. studio.model.data. access.TableDAOSQLServer.create (TableDAOSQLServer.java:220)
at com.plumtree. studio.model.app. Table._create (Table.java:585)
at com.plumtree. studio.model.app. Table.save (Table.java:618)

So, we have two problems: first, we need to tell Studio to create these fields with a type of NVARCHAR(MAX) rather than NVARCHAR(10000), and second, we need to update all existing tables.

The first problems is pretty straightforward – we just need to update the TableDAOSQLServer.java file (or, if you’re on Oracle, TableDAOOracle.java). Change:

      sqlBuffer.append(this.mUserColumnType).append("(").append(this.mUserColumnWidth).append(") ");

…to:

      sqlBuffer.append(this.mUserColumnType).append("(MAX) ");

… and recompile as mentioned in the last post.

The second problem requires some SQL Server voodoo – we need to write a SQL Script that generates a SQL Script. So, if you run this script as your studiodbuser:

SELECT 'ALTER TABLE ' +  syo.name 
    + ' ALTER COLUMN ' + syc.name + ' NVARCHAR(MAX);'
   FROM sysobjects syo
   JOIN syscolumns syc ON
     syc.id = syo.id
   JOIN systypes syt ON
     syt.xtype = syc.xtype
   WHERE 
     syt.name = 'nvarchar' 
    and syo.xtype='U'

… it will produce a SQL Script that looks like this:

ALTER TABLE PTU_SHPR2_Progress_Revi ALTER COLUMN Additional_Comments NVARCHAR(MAX);
ALTER TABLE PTU_SHPR2_Progress_Revi ALTER COLUMN U__Is_the_work_proceeding_i NVARCHAR(MAX);
ALTER TABLE PTU_SHPR2_Progress_Revi ALTER COLUMN U__Is_progress_towards_the_ NVARCHAR(MAX);

So basically, you’re using s script to find all the existing text fields, and creating a new one to actually increase the size limits on those fields.

Cool? Cool.

Office 2010 WebEdit Corrupts Document Downloads

Friday, March 18th, 2011

We’ve now discussed why Office 2010 doesn’t work with Oracle WebCenter Collaboration Server, how it can be patched, and even the enigmatic post about Office 2010 document formats.  So we know that Office 2010 WebDAV can work with Collab.  But even though the Apache/AJP hack fixes WebEdit for Office 2010, unfortunately it breaks something else.  Specifically, when you close a document that you’re “Web-Editing”, Office 2010 sends the file back to Collab, and it doesn’t seem to include a Content-Type header (damn headers…).

Because Collab isn’t told what the Content Type is, it has to guess, and as mentioned, the Office 2010 document format is a .zip file that contains XML.  What ends up happening is that, after a successful WebEdit operation, if you try a simple download of that document in the portal, you get a .zip file with all those .xml docs in there.  This threw me off for a bit, thinking that the original document was lost to this crazy .xml file.  But, once I realized that renaming the file to .docx and opening in Word 2010 still worked, it was clear the problem was with the MIME type of the document, not the bits themselves.

This is good news – it means that Collab’s not really broken, but Office 2010 is sending the wrong Content Type when the document is closed (strangely, this happens when the doc is closed, not when it’s saved), and Collab is recording that Content Type to its CSFILES table in the database.  Specifically, the ContentType field in the CSFILES gets set to text/xml; charset=”utf-8″:

 

The best fix (hack?) I’ve found here is to create a trigger on this table to cancel out any changes that set this content type to that value.  In other words, the MS SQL trigger looks for the value of “text/xml; charset=”utf-8″” during and update, and if it’s there, the trigger rolls back the value to the original value.  As usual, proceed at your own risk, and while I can’t foresee any problems – even when uploading XML files to Collab, which are likely to have a different Content Type – this is a high-risk DB update that Oracle definitely won’t support.

(more…)

WCI Collaboration Search Server re-indexing

Thursday, March 10th, 2011

Oracle’s Upgrade Guide for WebCenter Interaction Collaboration Server include “Rebuild the Oracle WebCenter Collaboration search collection“.

A while back, I ran into an issue where the rebuild process was spiking the CPU on the Collab Server at 100% forever (which, I suppose, is more of a plateau than a spike).  In viewing the PTSpy logs, I saw hundreds of thousands of messages that included this SQL statement:

select * from csIndexBulkDeletes where status = 1

Checking that table, I found over 110 MILLION rows. Which is particularly odd, given that this client only had 42,000 Collab Docs. Now, I have no idea how the table got that enormous, but it’s clear that Collab’s Search Rebuild process uses that table to determine which documents to update, much like the Search Update job uses the PTCARDSTATUS table – which, incidentally, can also get messed up.

It was clear that if the search rebuild process goes haywire, Collab starts queuing up search server updates in this table, and if the table gets too big, cascading failures start to occur where the queue grows faster than it can get purged.

The solution is: before starting the Collab Search Re-index process, clear this entire table, which is rebuilt during the re-index process anyway. To do so, just run:

truncate table csIndexBulkDeletes

I should note that this isn’t all that common, as I’ve only seen it once, but at least now you know one possible solution if your rebuild process can’t seem to gain escape velocity.

WCI database Access Levels

Wednesday, November 10th, 2010

If you’re a regular reader of this blog, you know we like getting under the Plumtree covers and figuring out what’s going on behind the scenes.  The ALUI databases are sometimes confusing – particularly the newer half-baked ones like the security database.  But the old legacy PT tables have undergone years of refinement, and every now and then show a well-thought-out design.

Today’s post is a quick lesson in binary math and how the ALUI security tables work. 

As you know, WebCenter Interaction object security includes READ/SELECT/EDIT/ADMIN privileges, and while there are some challenges to manipulating these security settings in the database (and some products to overcome the limitations), the underlying database structure is pretty straight-forward:  In tables like PTOBJECTSECURITY and PTCARDSECURITY you’ll find records that look like this:

While ObjectID, ClassID, and GroupID might be obvious in the context of the portal, AccessLevel is a bit-wise representation of the security level for that object, and contains either a 1, 3, 7, or 15.

Why these numbers?  Binary math.  Any number can be represented in binary (a base-2 numeric system) using bits; you’d represent the number 7 with a binary number of 0111, because:

Value: 8 4 2 1
Bit: 0 1 1 1

In other words: 8*0 + 4*1 + 2*1 + 1*1 = 7.

So if we look at the above table in the context of ALUI security privileges, EDIT access would be:

Value ADMIN EDIT SELECT READ
Bit: 0 1 1 1

i.e., a value of “7” in the database means “edit”, and you can calculate the values for the other privileges.  Interestingly, you can’t have EDIT privileges without having SELECT and READ (which is why you don’t see any values of, say, “4” in these tables). I wonder what would happen in the code if you manipulated the DB to give someone edit privileges WITHOUT giving them SELECT or READ? 

I guarantee this:  if you muck up this table, you are not going to get official support any time soon…

Update WebCenter Content Crawler and Job Owners

Tuesday, October 26th, 2010

As the portal evolved from Plumtree to ALUI (AquaLogic User Interaction) to WCI (WebCenter Interaction), there’s been a legacy feature that was born with good intentions, but like many things in human physical anatomy, it has survived evolution with little to no value.

This feature is object “owners” for Content Crawlers and Automation Jobs.  I’m sure there was a grand intent at some point for “owners” to mean something, but I haven’t found it yet.

The “owner” is the portal user that is scheduled to run a job.  But if that user is later deleted, the portal doesn’t clean up after itself – and all jobs that the user created are “orphaned” and won’t run, showing an error in PTSpy like:

Sep 12, 2010 10:07:02 PM- *** Job Operation #1 of 1 with ClassID 38 and ObjectID 202 cannot be run, probably because the operation has been deleted.

The fix – which I will condone until I can figure out why jobs need “owners” in the first place – is to just make the owner of all jobs and crawlers the Administrator user. The Administrator user can’t be deleted, and since I haven’t found any problems with running a crawler as this user, you can just do a portal-wide update making this change with the following SQL:

UPDATE PTCRAWLERS SET OWNERID=1
UPDATE PTJOBS SET OWNERID=1

Helpful SQL to determine if you’re being bitten by this “vestigal organ” after the break.

(more…)

Cool Tools 7: Benthic Software’s Golden

Wednesday, July 21st, 2010

For those of you that use the Oracle DB in your portal stack (or for pretty much anything), you know what an atrocity Oracle’s SQL*Plus is (it’s more dated than Plumtree / ALUI!).  I’ve looked on and off over the years for a simple Oracle client that works as well as Microsoft’s SQL Server Management Studio, and I want to thank Hani Atalla for turning me on to this one: Benthic Software’s Golden.  It’s hyper-simple to use, and even has all the “creature comforts” like being able to copy a result set into an Excel Spreadsheet (try doing THAT with SQL*Plus!).  It does require Oracle’s Instant Client to work, but even I (as a non-Oracle DBA) was able to install both in a matter of minutes.

If you’ve sweated through SQL*Plus sessions for way too long, definitely check this tool out – it’s cheap, at only $40.  If you’ve got a better tool for quick and easy Oracle DB queries, I’d love to hear about it in the comments!