WebSphere related

Roller Weblogger 2.0.2 DB2 issue

There's something to do, to work Roller with DB2.

• Since Roller work with Hibernate, change hibernate.cfg.xml to add org.hibernate.dialect.DB2Dialect to dialect property
  
• Create database in DB2 and make your app. server work with it
  
• Create tables and indices in database using dbscripts/db2/createdb.sql . Take note of two things :1. Command Center GUI don't like SQL-style comments -- 2.In generated script I find a small bug create table(.. pubtime timestamp null) produce error SQL0104N An unexpected token "," was found following "timestamp null". pubtime . Delete null -it's by default.
• When Roller looked for recent entry it generate a loooooong query. In case of in case of COM.ibm.db2.jdbc.DB2Exception:SQL1585N SQLSTATE=54048 create system temporary tablespace with page size 16K. Here is generated script on my computer
  
CONNECT TO ROLLER;
CREATE BUFFERPOOL ROLLERBUFFER IMMEDIATE SIZE 250 PAGESIZE 16 K ;
CREATE SYSTEM TEMPORARY TABLESPACE ROLLERTEMPSPACE PAGESIZE 16 K MANAGED BY SYSTEM USING ('C:\DB2\rollercontainer' ) EXTENTSIZE 8 OVERHEAD 12.67 PREFETCHSIZE 8 TRANSFERRATE 0.18 BUFFERPOOL ROLLERBUFFER ;
ALTER TABLESPACE ROLLERTEMPSPACE PREFETCHSIZE AUTOMATIC SWITCH ONLINE ;
CONNECT RESET;
• In class HibernateRefererManagerImpl method getDaysPopularWebsites have wrong special handler with DB2 raw sql. GROUP BY expression in this case must have all selected items(sqlcode : -119 sqlstate : 42803). Also one item (w.name) selected twice making a wrong link to blog in presentation. Correct code :
  
"select u.username,w.name,w.handle,sum(r.dayhits) as s
from rolleruser as u, website as w, referer as r
where r.websiteid=w.id and w.userid=u.id and w.isenabled=?
group by u.username,w.name,w.handle,w.id order by s desc fetch first" +
Integer.toString(max) + "rows only");
  

posted by s-chro @ 1:38 PM