As we did have big performances issues in portal v5 due to the high number of WCM content items in our database, we have decided to continue monitoring this count also in v6.
Of course the new JCR design in v6, the WCM software is now supposed to support about 500.000 content entries....but anyway I think it is still a good idea to monitor the volume of data you have in your system (this could be useful to report that to your management, and/or just to prevent reaching the limit).
Now, in v6 counting and analyzing the number of items is little bit more complex than in v5, mainly because the JCR database design is really different compare to a standard SQL schema design.
Actually, the v5 database request for counting the total number of content items are quite similar:
- Old request (in v5):
select count(*) from AJPE where COMPONENT_TYPE='AJPE_CONTENTS'
- New request (in v6.x):
SELECT COUNT(*) FROM JCR.EV_ENTRY WHERE ITEMCLASS=1762875877
But the problem is that in JCR database, there is no simple 1 to 1 correspondancy between the WCM content items and the number of entries in db...
In v5, when creating a new content or deleting it, you can see that the count is simply increased (+1) or decreased (-1) accordingly, whatever the status of the content.
Now in v6 JCR model, here is the behaviour of the system:
Let's assume at start you have:
select count(ITEMID) from JCR.EV_ENTRY -> 39004
select count(ITEMID) from JCR.EV_ENTRY where ITEMCLASS=1762875877 -> 27818
I will give you more details in the next section below, but basically:
- select count(ITEMID) from JCR.EV_ENTRY : this request gives you the total number of WCM objects in the db (could be content, but also design elements).
- select count(ITEMID) from JCR.EV_ENTRY where ITEMCLASS=1762875877 : give you only the WCM content items (like news, etc), assuming in your db "1762875877" also corresponds to the content item object type.
After creating 1 Published content, you will see that the count is increased by "+2":
select count(ITEMID) from JCR.EV_ENTRY -> 39006
select count(ITEMID) from JCR.EV_ENTRY where ITEMCLASS=1762875877 -> 27820
After creating 1 new Draft content, you will see that the count is increased only by "+1":
select count(ITEMID) from JCR.EV_ENTRY -> 39007
select count(ITEMID) from JCR.EV_ENTRY where ITEMCLASS=1762875877 -> 27821
Also, please note that deleting content does not affect the above count values.
All these results clearly show that:
- 1 WCM content item can correspond to several JCR nodes,
- Deleting a WCM content will not necessarily decrease the database count (as content is probably not deleted physically, but its status might only be updated).
To validate this last hypothesis (regarding delete management), I did the following tests:
Get the total number of content items:
db2 => SELECT COUNT(*) FROM JCR.EV_ENTRY WHERE ITEMCLASS=1762875877
Get the distinct 'ITEMTYPE' values that exist in the db:
db2 => SELECT DISTINCT ITEMTYPE FROM JCR.EV_ENTRY
Then check the corresponding content items count, depending on the 'ITEMTYPE':
SELECT COUNT(*) FROM JCR.EV_ENTRY WHERE ITEMCLASS=1762875877 AND ITEMTYPE='D'
SELECT COUNT(*) FROM JCR.EV_ENTRY WHERE ITEMCLASS=1762875877 AND ITEMTYPE='L'
SELECT COUNT(*) FROM JCR.EV_ENTRY WHERE ITEMCLASS=1762875877 AND ITEMTYPE='P'
Now we can see that the count of ITEMTYPE='P' and ITEMTYPE='D' is equal to the total 27818 of content items.
An obvious assumption is that 'D' corresponds to DRAFT contents, and 'P' to PUBLISHED content. These results match the status of our contents in our db : we have about 500 draft content items, and about 13.500 published content items (remind that a published item = 2 entries in JCR.EV_ENTRY table).
So basically, to evaluate the count of WCM content items, I think the following equation makes sense:
[SELECT COUNT(*) FROM JCR.EV_ENTRY WHERE ITEMCLASS=1762875877 AND ITEMTYPE='D'] + 0,5*[SELECT COUNT(*) FROM JCR.EV_ENTRY WHERE ITEMCLASS=1762875877 AND ITEMTYPE='P']
Note: I have no idea for the moment of what could be the meaning of ITEMTYPE='L' ? (if 'P' is published, and 'D' is draft, then it could correspond to EXPIRED status, but this count does not match our number of expired content....).
Hope this will help you to better understand the structure of the WCM JCR database. Also please find below the detail of the database requests I made to understand the model.
To connect to the JCR database, you first have to get the following values (in our case, we are using DB2):
# DbType: The type of database to be used for WebSphere Portal JCR domain
v6.x PORTAL DATABASES configuration file can be found here:
The table JCR.EV_ENTRY contains the reference to all the content items.
db2 => DESCRIBE TABLE JCR.EV_ENTRY
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
IGID SYSIBM CHARACTER 64 0 No
ITEMID SYSIBM CHARACTER 64 0 No
ITEMCLASS SYSIBM INTEGER 4 0 Yes
ITEMTYPE SYSIBM CHARACTER 1 0 No
STATE SYSIBM BIGINT 8 0 Yes
DELETED SYSIBM CHARACTER 1 0 Yes
PARENTID SYSIBM CHARACTER 64 0 Yes
TSTAMP SYSIBM TIMESTAMP 10 0 Yes
The table JCR.EV_TYPES gives you the list of WCM object types:
SELECT TYPENAME FROM JCR.EV_TYPES
The first field of the JCR.EV_TYPES tables is the object type ID than can be used to identify content in the JCR.EV_ENTRY table. Based on the list below, the value 1762875877 corresponds to the ITEMCLASS column in the JCR.EV_ENTRY table.
SELECT * FROM JCR.EV_TYPES