DBClean for Guest Users does not perform well when
there are many Guest Users in the Database
|
|
Technote (troubleshooting)
| | Problem(Abstract)
| | When a WebSphere Commerce database contains a large number
of guest users, DBClean may take an extremely long time to run when used
to clean out those guest users. This can be the case even if the database
statistics and tables are in a well-maintained state.
|
|
|
|
| Cause
|
| The SQL that is used to delete guest users is extremely
complicated and contains several unnecessary predicates.
|
|
|
| Resolving the
problem
|
The SQL has been re-written in order to remove the
unnecessary predicates. A different version of the new query is needed,
depending on whether a DB2 or an Oracle database is being used.
DB2 Database:
The new version of the SQL that is used to delete guest users is this:
delete from member where member_id in (select users_id from users T1
where registertype='G' and (days(CURRENT TIMESTAMP) -
days(prevlastsession)) >= ? And not Exists (select 1 from orders where
orders.member_id=t1.users_id and status != 'Q') and (users_id > 0))
For comparison purposes, here is the old version:
delete from member where member_id in (select users_id from users where
registertype='G' and (days(CURRENT TIMESTAMP) - days(prevlastsession))
>= ? and (users_id not in (select member_id from orders where status !=
'Q')) and (users_id > 0) and (users_id not in (select member_id from
address where address_id in (select address_id from orderitems where
address_id is not null and status != 'Q') or address_id in (select
allocaddress_id from orderitems where allocaddress_id is not null and
status != 'Q') or address_id in (select address_id from orders where
address_id is not null and status !='Q'))))
Oracle Database:
The new version of the SQL that is used to delete guest users is this:
delete from member where member_id in (select users_id from users T1
where registertype='G' and (sysdate - ?) >= prevlastsession And not
Exists (select 1 from orders where orders.member_id=T1.users_id and status
!= 'Q') and (users_id > 0))
For comparison purposes, here is the old version:
delete from member where member_id in (select users_id from users where
registertype='G' and (sysdate - ?) >= prevlastsession And (users_id
not in (select member_id from orders where status != 'Q')) and (users_id
> 0) and (users_id not in (select member_id from address where
address_id in (select address_id from orderitems where address_id is not
null and status != 'Q') or address_id in (select allocaddress_id from
orderitems where allocaddress_id is not null and status != 'Q') or
address_id in (select address_id from orders where address_id is not null
and status !='Q'))))
Contact WebSphere Commerce Support and request an APAR for this new query.
Alternatively, the new query is included in both the WebSphere Commerce
6.0.0.2 Fix Pack and the WebSphere Commerce 5.6.1.3 Fix Pack.
|
| |
| Segment
| Product
| Component
| Platform
| Version
| Edition
|
| Commerce
| WebSphere Commerce Professional Edition
| Performance
| AIX, i5/OS, Linux, Linux iSeries, Linux pSeries, Linux xSeries, Linux
zSeries, Solaris, Windows
| 5.6.1, 6.0
| Professional Edition
|
| Commerce
| WebSphere Commerce Business Edition
| Performance
| AIX, i5/OS, Linux, Linux iSeries, Linux pSeries, Linux xSeries, Linux
zSeries, Solaris, Windows
| 5.6.1
| Business Edition
|
| Commerce
| WebSphere Commerce - Express
| Performance
| AIX, i5/OS, Linux, Linux iSeries, Linux pSeries, Linux xSeries, Linux
zSeries, Solaris, Windows
| 5.6.1, 6.0
| Express
|
|
Current web document: http://www.ibm.com/support/docview.wss?uid=swg21283886
|