We had strange Problem
with Menu Components after syndicating content and design elements to new WCM
environment
We have lot of menu’s with following three
options selected or defined in search criteria
1.
“authoring template”,
2.
“sitearea” and
3.
“categories” .
Once we syndicate the lot stuff from lower
environment to staging /prod environments , most of the times these components
were not working as expected. Some times
it displays the results properly and some times no results at all.
Enabled WCM traces to find out actual error and
query details , When it is trying to execute menu’s , I see the exceptions like
below in the logs
[7/25/12 12:29:04:641 EDT] 00000045 Query E com.ibm.icm.da.portable.query.Query
openQueryCursor() Executing actual query: SELECT DISTINCT Links_4.TIID , 1 WSID
, NodesTab_17.VID , NodesTab_17.CTID , NodesTab_17.UUID , NodesTab_17.COMPID ,
Properties_20.PROPVAL ORDERVAL21 FROM
jcr.ICMSTJCRLV00001 Links_4, jcr.ICMSTJCRLV00001 Links_1, jcr.ICMSTJCRN00001
NodesTab_17, (SELECT ibmcontentwcm_18.ITEMID , ibmcontentwcm_18.VERSIONID ,
ibmcontentwcm_18.ATTR0000001068 PROPVAL
FROM jcr.ICMUT01580001 ibmcontentwcm_18
UNION ALL SELECT ibmcontentwcm_19.ITEMID , ibmcontentwcm_19.VERSIONID ,
ibmcontentwcm_19.ATTR0000001068 PROPVAL
FROM jcr.ICMUT01331001 ibmcontentwcm_19 ) Properties_20 WHERE ((((((Links_4.TCTID = ?) OR
(Links_4.TCTID = ?)) AND ((Links_1.TCTID = ?) AND (EXISTS (SELECT CAST(NULL AS
CHAR(1)) FROM (SELECT NodesTab_2.IID ITEMID
, NodesTab_2.VID VERSIONID , NodesTab_2.UUID PROPVAL FROM jcr.ICMSTJCRN00001 NodesTab_2 )
Properties_3 WHERE ((Properties_3.ITEMID
= Links_1.TIID) AND (Properties_3.VERSIONID = Links_1.TVID)) AND
(Properties_3.PROPVAL IN (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? )))))) AND (Links_4.SIID = Links_1.TIID)) AND (((EXISTS
(SELECT CAST(NULL AS CHAR(1)) FROM
(SELECT ibmcontentwcm_5.ITEMID , ibmcontentwcm_5.VERSIONID , ibmcontentwcm_5.ATTR0000001125
PROPVAL FROM jcr.ICMUT01580001
ibmcontentwcm_5 UNION ALL SELECT
ibmcontentwcm_6.ITEMID , ibmcontentwcm_6.VERSIONID ,
ibmcontentwcm_6.ATTR0000001125 PROPVAL
FROM jcr.ICMUT01331001 ibmcontentwcm_6 ) Properties_7 WHERE ((Properties_7.ITEMID = Links_4.TIID)
AND (Properties_7.VERSIONID = Links_4.TVID)) AND (Properties_7.PROPVAL IN (? ,
? )))) AND ((EXISTS (SELECT CAST(NULL AS CHAR(1)) FROM (SELECT ibmcontentwcm_8.ITEMID ,
ibmcontentwcm_8.VERSIONID , ibmcontentwcm_8.ATTR0000001337 PROPVAL FROM jcr.ICMUT01580001 ibmcontentwcm_8 UNION ALL SELECT ibmcontentwcm_9.ITEMID ,
ibmcontentwcm_9.VERSIONID , ibmcontentwcm_9.ATTR0000001337 PROPVAL FROM jcr.ICMUT01331001 ibmcontentwcm_9 )
Properties_10 WHERE ((Properties_10.ITEMID
= Links_4.TIID) AND (Properties_10.VERSIONID = Links_4.TVID)) AND
(Properties_10.PROPVAL = ?))) AND (EXISTS (SELECT CAST(NULL AS CHAR(1)) FROM (SELECT ibmcontentwcm_11.ITEMID ,
ibmcontentwcm_11.VERSIONID , ibmcontentwcm_11.ATTR0000001336 PROPVAL FROM jcr.ICMUT01583001 ibmcontentwcm_11 UNION ALL SELECT ibmcontentwcm_12.ITEMID ,
ibmcontentwcm_12.VERSIONID , ibmcontentwcm_12.ATTR0000001336 PROPVAL FROM jcr.ICMUT01334001 ibmcontentwcm_12 )
Properties_13 WHERE
((Properties_13.ITEMID = Links_4.TIID) AND (Properties_13.VERSIONID =
Links_4.TVID)) AND (Properties_13.PROPVAL = ?))))) AND (EXISTS (SELECT
CAST(NULL AS CHAR(1)) FROM (SELECT
ibmcontentwcm_14.ITEMID , ibmcontentwcm_14.VERSIONID ,
ibmcontentwcm_14.ATTR0000001129 PROPVAL
FROM jcr.ICMUT01580001 ibmcontentwcm_14
UNION ALL SELECT ibmcontentwcm_15.ITEMID , ibmcontentwcm_15.VERSIONID ,
ibmcontentwcm_15.ATTR0000001129 PROPVAL
FROM jcr.ICMUT01331001 ibmcontentwcm_15 ) Properties_16 WHERE ((Properties_16.ITEMID = Links_4.TIID)
AND (Properties_16.VERSIONID = Links_4.TVID)) AND (Properties_16.PROPVAL =
?))))) AND (Links_4.TIID = NodesTab_17.IID)) AND ((Properties_20.ITEMID =
Links_4.TIID) AND (Properties_20.VERSIONID = Links_4.TVID)) ORDER BY ORDERVAL21
DESC WITH UR
[7/25/12 12:29:04:642 EDT] 00000045 Query E com.ibm.icm.da.portable.query.Query
openQueryCursor() Param Marker 1: Type = Long, Value = 1580
…
[7/25/12
12:29:04:652 EDT] 00000045 Query
E com.ibm.icm.da.portable.query.Query openQueryCursor() Param Marker 27:
Type = Long, Value = 1
[7/25/12 12:29:04:652 EDT] 00000045 Query E com.ibm.icm.da.portable.query.Query
openQueryCursor() Actual query length: 3165
[7/25/12 12:29:04:653 EDT] 00000045 Query E com.ibm.icm.da.portable.query.Query
openQueryCursor() com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-101,
SQLSTATE=54001,
……
By looking at query length ,felt its kind of so
lengthy and started troubleshooting it from the DB2 side and realized that we didn’t
run the “RUNSTATS” on DB2.
After executing the “RUNSTATS” on the DB2 and resetting WCM event log resolved the
issue.
For RUNSTATS
>>>db2
connect to jcrdb
>>>db2 -x
-r "runstats.db2" "select rtrim(concat('runstats on table
',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),' on all columns
with distribution on all columns and sampled detailed indexes all allow write
access'))))) from syscat.tables where type='T'"
>>>db2 -v
-f "runstats.db2"
>>>db2
disconnect all
For WCM event log resetting
./ConfigEngine.sh
run-wcm-admin-task-reset-event-log -Dlibrary="libname" -Dfix=true
NOTE : You may
also need to run “reorg” for the heavily used tables and indexes .
Resources:
No comments:
Post a Comment