Configuring Pentaho 3.5 stable Platform to IBM DB2 9.7

[Kahn]

Hello,

I am new in Pentaho. I want to configure Pentaho 3.5 stable user console to IBM DB2 9.7 on XP.I changed the following files:

1. I've copied the JDBC-Driver of DB2 to …..\biserver-ce-3.5.0.stable\administration-console\jdbc and ….\biserver-ce-3.5.0.stable\biserver-ce\tomcat\common\lib directory.

2. I've created quartz,hibernate database and some objects according scripts in \biserver-ce-3.5.0.stable\biserver-ce\data

3.I've changed …..\biserver-ce-3.5.0.stable\biserver-ce\pentaho-solutions\system\applicationContext-spring-security-hibernate.properties:

jdbc.driver=com.ibm.db2.jcc.DB2Driver
jdbc.url=jdbc:db2://localhost:50000/PENTAHO:currentSchema=HIBERNATE;
jdbc.username=db2admin
jdbc.password=mypassword
hibernate.dialect=org.hibernate.dialect.DB2Dialect

4.I've changed …..\biserver-ce-3.5.0.stable\biserver-ce\pentaho-solutions\system\applicationContext-spring-security-jdbc.xml:

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
<property name="driverClassName" value="com.ibm.db2.jcc.DB2Driver" />
<property name="url"
value="jdbc:db2://localhost:50000/PENTAHO:currentSchema=HIBERNATE;" />
<property name="username" value="db2admin" />
<property name="password" value="mypassword" />
</bean>


5.I've changed ….. \biserver-ce-3.5.0.stable\biserver-ce\pentaho-solutions\system\quartz\quartz.properties:

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.DB2v9Delegate


6.I've changed ….. \biserver-ce-3.5.0.stable\biserver-ce\pentaho-solutions\system\hibernate\hibernate-settings.xml:

<config-file>system/hibernate/db2.hibernate.cfg.xml</config-file>


7. I've copied ….. \biserver-ce-3.5.0.stable\biserver-ce\pentaho-solutions\system\hibernate\oracle10g.hibernate.cfg .xml and renamed it as db2.hibernate.cfg.xml. My changes are:


<property name="connection.driver_class">com.ibm.db2.jcc.DB2 Driver</property>
<property name="connection.url">jdbc:db2://localhost:50000/PENTAHO:currentSchema=HIBERNATE;</property>
<property name="dialect">org.hibernate.dialect.DB2Dialect</property>
<property name="connection.username">db2admin</property>
<property name="connection.password">mypassword</property>


8. I've changed ….. \biserver-ce-3.5.0.stable\biserver-ce\tomcat\webapps\pentaho\META-INF\context.xml:

<Context path="/pentaho" docbase="webapps/pentaho/">
<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFa ctory" maxActive="20" maxIdle="5"
maxWait="10000" username="db2admin" password="mypassword"
driverClassName="com.ibm.db2.jcc.DB2Driver" url="jdbc:db2://localhost:50000/PENTAHO:currentSchema=HIBERNATE;"
validationQuery="SELECT 1 FROM SYSIBM.SYSDUMMY1" />

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFa ctory" maxActive="20" maxIdle="5"
maxWait="10000" username="db2admin" password="mypassword"
driverClassName="com.ibm.db2.jcc.DB2Driver" url="jdbc:db2://localhost:50000/PENTAHO:currentSchema=QUARTZ;"
validationQuery="SELECT 1 FROM SYSIBM.SYSDUMMY1"/>
</Context>



When I start the Tomcat server, I got the error below:


Jan 17, 2010 12:45:15 PM org.apache.catalina.core.AprLifecycleListener lifecycle
Event
INFO: The Apache Tomcat Native library which allows optimal performance in produ
ction environments was not found on the java.library.path: C:\Dokumente und Eins
tellungen\Ajmal\Desktop\biserver-ce-3.5.0.stable\biserver-ce\jre\bin;.;C:\WINDOW
S\system32;C:\WINDOWS;C:\WINDOWS\system32;C:\WINDO WS;C:\WINDOWS\system32\wbem;c:
\programme\ibm\gsk8\lib;C:\Programme\jEdit;C:\PROG RA~1\IBM\SQLLIB\BIN;C:\PROGRA~
1\IBM\SQLLIB\FUNCTION;C:\PROGRA~1\IBM\SQLLIB\SAMPL ES\REPL
Jan 17, 2010 12:45:16 PM org.apache.coyote.http11.Http11BaseProtocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
Jan 17, 2010 12:45:16 PM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 3000 ms
Jan 17, 2010 12:45:16 PM org.apache.catalina.core.StandardService start
INFO: Starting service Catalina
Jan 17, 2010 12:45:16 PM org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/5.5.26
Jan 17, 2010 12:45:16 PM org.apache.catalina.core.StandardHost start
INFO: XML validation disabled
Jan 17, 2010 12:45:27 PM org.apache.catalina.startup.ContextConfig validateSecur
ityRoles
WARNING: Security role name PENTAHO_ADMIN used in an <auth-constraint> without b
eing defined in a <security-role>
12:46:20,062 ERROR [SchemaUpdate] Unsuccessful: create table CONTENTITEM (CONTIT
EMID varchar(100) not null, REVISION integer not null, NAMECOL varchar(200) not
null, parent_id varchar(100), PATHCOL varchar(1024) not null, TITLE varchar(200)
not null, MIMETYPE varchar(100), URL varchar(254), LATESTVERNUM integer, EXTENS
ION varchar(10) not null, WRITEMODE integer not null, primary key (CONTITEMID),
unique (PATHCOL))
12:46:20,062 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-613, SQLSTATE=54008, S
QLERRMC=PATHCOL..., DRIVER=3.58.82
12:46:20,109 ERROR [SchemaUpdate] Unsuccessful: create table CONTENTLOCATION (CO
NTENTID varchar(100) not null, REVISION integer not null, NAMECOL varchar(200) n
ot null, SOLNID varchar(100) not null, DESCRIPTION varchar(200) not null, DIRPAT
H varchar(1024) not null, primary key (CONTENTID), unique (DIRPATH))
12:46:20,109 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-613, SQLSTATE=54008, S
QLERRMC=DIRPATH..., DRIVER=3.58.82
12:46:20,578 ERROR [SchemaUpdate] Unsuccessful: create table PRO_FILES (FILE_ID
varchar(100) not null, revision integer not null, parent varchar(100), fileName
varchar(255) not null, fullPath varchar(1024) not null unique, data blob(255), d
irectory smallint not null, lastModified bigint not null, CHILD_ID varchar(100),
primary key (FILE_ID))
12:46:20,593 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-613, SQLSTATE=54008, S
QLERRMC=FULLPATH..., DRIVER=3.58.82
12:46:20,703 ERROR [SchemaUpdate] Unsuccessful: create table PRO_SUBCONTENT (SUB
CONTID varchar(100) not null, REVISION integer not null, SUBCONTTYPE varchar(255
) not null, SUBCONTACTREF varchar(1024) not null, primary key (SUBCONTID), uniqu
e (SUBCONTACTREF))
12:46:20,703 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-613, SQLSTATE=54008, S
QLERRMC=SUBCONTACTREF..., DRIVER=3.58.82
12:46:21,140 ERROR [SchemaUpdate] Unsuccessful: alter table CONTENTITEM add cons
traint FK692B5EEC44F32395 foreign key (parent_id) references CONTENTLOCATION
12:46:21,140 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, S
QLERRMC=HIBERNATE.CONTENTITEM, DRIVER=3.58.82
12:46:21,156 ERROR [SchemaUpdate] Unsuccessful: alter table CONTITEMFILE add con
straint FK7FC3F44164D906F3 foreign key (parent_id) references CONTENTITEM
12:46:21,156 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, S
QLERRMC=HIBERNATE.CONTENTITEM, DRIVER=3.58.82
12:46:21,218 ERROR [SchemaUpdate] Unsuccessful: alter table PRO_ACLS_LIST add co
nstraint FKB65646C2B23C5D30 foreign key (ACL_ID) references PRO_FILES
12:46:21,218 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, S
QLERRMC=HIBERNATE.PRO_FILES, DRIVER=3.58.82
12:46:21,218 ERROR [SchemaUpdate] Unsuccessful: alter table PRO_FILES add constr
aint FK94A87E2569FABF5E foreign key (CHILD_ID) references PRO_FILES
12:46:21,218 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, S
QLERRMC=HIBERNATE.PRO_FILES, DRIVER=3.58.82
12:46:21,218 ERROR [SchemaUpdate] Unsuccessful: alter table PRO_FILES add constr
aint FK94A87E25CBBBB0EA foreign key (parent) references PRO_FILES
12:46:21,218 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, S
QLERRMC=HIBERNATE.PRO_FILES, DRIVER=3.58.82
12:46:21,234 ERROR [SchemaUpdate] Unsuccessful: alter table PRO_SUBCONTPARMS add
constraint FKF3CE4C47846761A foreign key (ITEMID) references PRO_SUBCONTENT
12:46:21,234 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, S
QLERRMC=HIBERNATE.PRO_SUBCONTENT, DRIVER=3.58.82
12:46:21,234 ERROR [SchemaUpdate] Unsuccessful: alter table PRO_SUBCONT_SCHEDLIS
T add constraint FKF0D3FBD62593BC9E foreign key (SCHEDULEID) references PRO_SUBC
ONTENT
12:46:21,250 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, S
QLERRMC=HIBERNATE.PRO_SUBCONTENT, DRIVER=3.58.82
12:46:21,250 ERROR [SchemaUpdate] Unsuccessful: alter table PRO_SUBSCRIBE add co
nstraint FK5E7511F89D8AC376 foreign key (SUBSCR_CONTID) references PRO_SUBCONTEN
T
12:46:21,250 ERROR [SchemaUpdate] DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, S
QLERRMC=HIBERNATE.PRO_SUBCONTENT, DRIVER=3.58.82
12:46:21,531 ERROR [JDBCExceptionReporter] DB2 SQL Error: SQLCODE=-204, SQLSTATE
=42704, SQLERRMC=HIBERNATE.PRO_FILES, DRIVER=3.58.82
12:46:21,531 ERROR [JDBCExceptionReporter] DB2 SQL Error: SQLCODE=-727, SQLSTATE
=56098, SQLERRMC=2;-204;42704;HIBERNATE.PRO_FILES, DRIVER=3.58.82
12:46:21,531 ERROR [JDBCExceptionReporter] DB2 SQL Error: SQLCODE=-727, SQLSTATE
=56098, SQLERRMC=2;-204;42704;HIBERNATE.PRO_FILES, DRIVER=3.58.82
12:46:21,531 ERROR [Logger] Error: Pentaho
12:46:21,546 ERROR [Logger] misc-org.pentaho.platform.engine.core.system.Pentaho
System: PentahoSystem.ERROR_0026 - Failed to retrieve object with name "ISolutio
nRepository" from the Pentaho object factory.
org.pentaho.platform.api.engine.ObjectFactoryExcep tion: Failed to create an inst
ance of object with key [ISolutionRepository].
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.instanceClass(AbstractSpringPentahoOb jectFactory.java:81)
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.retreiveObject(AbstractSpringPentahoO bjectFactory.java:117)
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.get(AbstractSpringPentahoObjectFactor y.java:72)
at org.pentaho.platform.engine.core.system.PentahoSys tem.get(PentahoSyst
em.java:508)
at org.pentaho.platform.engine.core.system.PentahoSys tem.get(PentahoSyst
em.java:484)
at org.pentaho.platform.plugin.action.mondrian.Mondri anSystemListener.lo
adMondrianProperties(MondrianSystemListener.java:6 0)
at org.pentaho.platform.plugin.action.mondrian.Mondri anSystemListener.st
artup(MondrianSystemListener.java:46)
at org.pentaho.platform.engine.core.system.PentahoSys tem.notifySystemLis
tenersOfStartup(PentahoSystem.java:306)
at org.pentaho.platform.engine.core.system.PentahoSys tem.notifySystemLis
tenersOfStartup(PentahoSystem.java:291)
at org.pentaho.platform.engine.core.system.PentahoSys tem.init(PentahoSys
tem.java:265)
at org.pentaho.platform.engine.core.system.PentahoSys tem.init(PentahoSys
tem.java:206)
at org.pentaho.platform.web.http.context.SolutionCont extListener.context
Initialized(SolutionContextListener.java:136)
at org.apache.catalina.core.StandardContext.listenerS tart(StandardContex
t.java:3764)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:4
216)
at org.apache.catalina.core.ContainerBase.addChildInt ernal(ContainerBase
.java:760)
at org.apache.catalina.core.ContainerBase.addChild(Co ntainerBase.java:74
0)
at org.apache.catalina.core.StandardHost.addChild(Sta ndardHost.java:544)

at org.apache.catalina.startup.HostConfig.deployDirec tory(HostConfig.jav
a:926)
at org.apache.catalina.startup.HostConfig.deployDirec tories(HostConfig.j
ava:889)
at org.apache.catalina.startup.HostConfig.deployApps( HostConfig.java:492
)
at org.apache.catalina.startup.HostConfig.start(HostC onfig.java:1149)
at org.apache.catalina.startup.HostConfig.lifecycleEv ent(HostConfig.java
:311)
at org.apache.catalina.util.LifecycleSupport.fireLife cycleEvent(Lifecycl
eSupport.java:120)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1022)

at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:736)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1014)

at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:443
)
at org.apache.catalina.core.StandardService.start(Sta ndardService.java:4
48)
at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:700
)
at org.apache.catalina.startup.Catalina.start(Catalin a.java:552)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootst rap.java:295)
at org.apache.catalina.startup.Bootstrap.main(Bootstr ap.java:433)
Caused by: java.lang.NullPointerException
at org.pentaho.platform.repository.solution.dbbased.D bBasedSolutionRepos
itory.internalGetRootFolder(DbBasedSolutionReposit ory.java:972)
at org.pentaho.platform.repository.solution.dbbased.D bBasedSolutionRepos
itory.init(DbBasedSolutionRepository.java:111)
at org.pentaho.platform.repository.solution.dbbased.D bBasedSolutionRepos
itory.<init>(DbBasedSolutionRepository.java:96)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance0(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.instanceClass(AbstractSpringPentahoOb jectFactory.java:78)
... 35 more
12:46:21,562 ERROR [Logger] Error end:
12:46:21,562 ERROR [Logger] Error: Pentaho
12:46:21,562 ERROR [Logger] misc-org.pentaho.platform.engine.core.system.Pentaho
System: PentahoSystem.ERROR_0014 - Error while trying to execute startup sequenc
e for org.pentaho.platform.plugin.action.mondrian.Mondri anSystemListener
org.pentaho.platform.api.engine.PentahoSystemExcep tion: PentahoSystem.ERROR_0014
- Error while trying to execute startup sequence for org.pentaho.platform.plugi
n.action.mondrian.MondrianSystemListener
at org.pentaho.platform.engine.core.system.PentahoSys tem.notifySystemLis
tenersOfStartup(PentahoSystem.java:311)
at org.pentaho.platform.engine.core.system.PentahoSys tem.notifySystemLis
tenersOfStartup(PentahoSystem.java:291)
at org.pentaho.platform.engine.core.system.PentahoSys tem.init(PentahoSys
tem.java:265)
at org.pentaho.platform.engine.core.system.PentahoSys tem.init(PentahoSys
tem.java:206)
at org.pentaho.platform.web.http.context.SolutionCont extListener.context
Initialized(SolutionContextListener.java:136)
at org.apache.catalina.core.StandardContext.listenerS tart(StandardContex
t.java:3764)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:4
216)
at org.apache.catalina.core.ContainerBase.addChildInt ernal(ContainerBase
.java:760)
at org.apache.catalina.core.ContainerBase.addChild(Co ntainerBase.java:74
0)
at org.apache.catalina.core.StandardHost.addChild(Sta ndardHost.java:544)

at org.apache.catalina.startup.HostConfig.deployDirec tory(HostConfig.jav
a:926)
at org.apache.catalina.startup.HostConfig.deployDirec tories(HostConfig.j
ava:889)
at org.apache.catalina.startup.HostConfig.deployApps( HostConfig.java:492
)
at org.apache.catalina.startup.HostConfig.start(HostC onfig.java:1149)
at org.apache.catalina.startup.HostConfig.lifecycleEv ent(HostConfig.java
:311)
at org.apache.catalina.util.LifecycleSupport.fireLife cycleEvent(Lifecycl
eSupport.java:120)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1022)

at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:736)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1014)

at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:443
)
at org.apache.catalina.core.StandardService.start(Sta ndardService.java:4
48)
at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:700
)
at org.apache.catalina.startup.Catalina.start(Catalin a.java:552)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootst rap.java:295)
at org.apache.catalina.startup.Bootstrap.main(Bootstr ap.java:433)
Caused by: java.lang.NullPointerException
at org.pentaho.platform.plugin.action.mondrian.Mondri anSystemListener.lo
adMondrianProperties(MondrianSystemListener.java:6 1)
at org.pentaho.platform.plugin.action.mondrian.Mondri anSystemListener.st
artup(MondrianSystemListener.java:46)
at org.pentaho.platform.engine.core.system.PentahoSys tem.notifySystemLis
tenersOfStartup(PentahoSystem.java:306)
... 28 more
12:46:21,578 ERROR [Logger] Error end:
Pentaho BI Platform server failed to properly initialize. The system will not be
available for requests. (Pentaho Platform Engine Core 3.5.0.stable.39705) Base
Url = http://localhost:8080/pentaho/, Solution Path = C:\Dokumente und Einstellu
ngen\Ajmal\Desktop\biserver-ce-3.5.0.stable\biserver-ce\pentaho-solutions
Jan 17, 2010 12:46:22 PM org.apache.coyote.http11.Http11BaseProtocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
Jan 17, 2010 12:46:22 PM org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
Jan 17, 2010 12:46:22 PM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/47 config=null
Jan 17, 2010 12:46:22 PM org.apache.catalina.storeconfig.StoreLoader load
INFO: Find registry server-registry.xml at classpath resource
Jan 17, 2010 12:46:22 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in 66469 ms




Can somebody help me please?

Thanks for your reply

Elmar

[TomS]

Hi Elmar,

according to:
http://mail.opencms.org/pipermail/op...q2/032146.html

The DB2 Errorcode 613 (which appears in your log) refers to the database pagesize.
Could you verify this?

The "204" Codes seem to be follow-up errors because of preceding errors...

HTH,

Tom

[Kahn]

Hi Elmar,

according to:
http://mail.opencms.org/pipermail/op...q2/032146.html

The DB2 Errorcode 613 (which appears in your log) refers to the database pagesize.
Could you verify this?

The "204" Codes seem to be follow-up errors because of preceding errors...

HTH,

Tom

Hi Tom,

first of all thank you for your immediat relpy. I will try it later and let you know, if it worked or not.

Elmar

[Kahn]

Hi Tom,

I have increased the pagesize of the tablespace as you recommended. That problem was solved, but I got another error as you can see in the log file. The Pentaho Server is ready but when I log in, I can't see the Pentaho Solution directory in the Platform.


at com.ibm.db2.jcc.am.tm.b(tm.java:2192)
at com.ibm.db2.jcc.am.tm.c(tm.java:2175)
at com.ibm.db2.jcc.t4.fb.k(fb.java:355)
at com.ibm.db2.jcc.t4.fb.a(fb.java:61)
at com.ibm.db2.jcc.t4.t.a(t.java:50)
at com.ibm.db2.jcc.t4.vb.b(vb.java:218)
at com.ibm.db2.jcc.am.um.jc(um.java:2860)
at com.ibm.db2.jcc.am.um.b(um.java:3795)
at com.ibm.db2.jcc.am.um.b(um.java:3949)
at com.ibm.db2.jcc.am.um.ac(um.java:709)
at com.ibm.db2.jcc.am.um.executeUpdate(um.java:692)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(N onBatchingBatcher.j
ava:23)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2252)
... 60 more
10:56:33,609 ERROR [JDBCExceptionReporter] DB2 SQL Error: SQLCODE=-302, SQLSTATE
=22001, SQLERRMC=null, DRIVER=3.58.82
10:56:33,609 ERROR [AbstractFlushingEventListener] Could not synchronize databas
e state with session
org.hibernate.exception.DataException: could not insert: [org.pentaho.platform.r
epository.solution.dbbased.RepositoryFile]
at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.j
ava:77)
at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelp
er.java:43)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2272)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2665)
at org.hibernate.action.EntityInsertAction.execute(En tityInsertAction.ja
va:60)
at org.hibernate.engine.ActionQueue.execute(ActionQue ue.java:279)
at org.hibernate.engine.ActionQueue.executeActions(Ac tionQueue.java:263)

at org.hibernate.engine.ActionQueue.executeActions(Ac tionQueue.java:167)

at org.hibernate.event.def.AbstractFlushingEventListe ner.performExecutio
ns(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener. onFlush(DefaultFlus
hEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.j ava:1000)
at org.hibernate.impl.SessionImpl.managedFlush(Sessio nImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(J DBCTransaction.java
:106)
at org.pentaho.platform.repository.hibernate.Hibernat eUtil.commitTransac
tion(HibernateUtil.java:447)
at org.pentaho.platform.repository.solution.dbbased.D bBasedSolutionRepos
itory.loadSolutionFromFileSystem(DbBasedSolutionRe pository.java:866)
at org.pentaho.platform.repository.solution.dbbased.D bBasedSolutionRepos
itory.init(DbBasedSolutionRepository.java:114)
at org.pentaho.platform.repository.solution.dbbased.D bBasedSolutionRepos
itory.<init>(DbBasedSolutionRepository.java:96)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance0(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.instanceClass(AbstractSpringPentahoOb jectFactory.java:78)
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.retreiveObject(AbstractSpringPentahoO bjectFactory.java:117)
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.get(AbstractSpringPentahoObjectFactor y.java:72)
at org.pentaho.platform.engine.core.system.PentahoSys tem.get(PentahoSyst
em.java:508)
at org.pentaho.platform.engine.core.system.PentahoSys tem.get(PentahoSyst
em.java:484)
at org.pentaho.platform.engine.services.solution.Solu tionEngine.createAc
tionSequence(SolutionEngine.java:455)
at org.pentaho.platform.engine.services.solution.Solu tionEngine.executeI
nternal(SolutionEngine.java:364)
at org.pentaho.platform.engine.services.solution.Solu tionEngine.execute(
SolutionEngine.java:316)
at org.pentaho.platform.engine.services.solution.Solu tionEngine.execute(
SolutionEngine.java:192)
at org.pentaho.platform.engine.core.system.PentahoSys tem.globalStartup(P
entahoSystem.java:680)
at org.pentaho.platform.engine.core.system.PentahoSys tem.globalStartup(P
entahoSystem.java:635)
at org.pentaho.platform.engine.core.system.PentahoSys tem.init(PentahoSys
tem.java:275)
at org.pentaho.platform.engine.core.system.PentahoSys tem.init(PentahoSys
tem.java:206)
at org.pentaho.platform.web.http.context.SolutionCont extListener.context
Initialized(SolutionContextListener.java:136)
at org.apache.catalina.core.StandardContext.listenerS tart(StandardContex
t.java:3764)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:4
216)
at org.apache.catalina.core.ContainerBase.addChildInt ernal(ContainerBase
.java:760)
at org.apache.catalina.core.ContainerBase.addChild(Co ntainerBase.java:74
0)
at org.apache.catalina.core.StandardHost.addChild(Sta ndardHost.java:544)

at org.apache.catalina.startup.HostConfig.deployDirec tory(HostConfig.jav
a:926)
at org.apache.catalina.startup.HostConfig.deployDirec tories(HostConfig.j
ava:889)
at org.apache.catalina.startup.HostConfig.deployApps( HostConfig.java:492
)
at org.apache.catalina.startup.HostConfig.start(HostC onfig.java:1149)
at org.apache.catalina.startup.HostConfig.lifecycleEv ent(HostConfig.java
:311)
at org.apache.catalina.util.LifecycleSupport.fireLife cycleEvent(Lifecycl
eSupport.java:120)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1022)

at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:736)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1014)

at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:443
)
at org.apache.catalina.core.StandardService.start(Sta ndardService.java:4
48)
at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:700
)
at org.apache.catalina.startup.Catalina.start(Catalin a.java:552)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootst rap.java:295)
at org.apache.catalina.startup.Bootstrap.main(Bootstr ap.java:433)
Caused by: com.ibm.db2.jcc.am.mo: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, S
QLERRMC=null, DRIVER=3.58.82
at com.ibm.db2.jcc.am.ed.a(ed.java:668)
at com.ibm.db2.jcc.am.ed.a(ed.java:60)
at com.ibm.db2.jcc.am.ed.a(ed.java:127)
at com.ibm.db2.jcc.am.tm.b(tm.java:2192)
at com.ibm.db2.jcc.am.tm.c(tm.java:2175)
at com.ibm.db2.jcc.t4.fb.k(fb.java:355)
at com.ibm.db2.jcc.t4.fb.a(fb.java:61)
at com.ibm.db2.jcc.t4.t.a(t.java:50)
at com.ibm.db2.jcc.t4.vb.b(vb.java:218)
at com.ibm.db2.jcc.am.um.jc(um.java:2860)
at com.ibm.db2.jcc.am.um.b(um.java:3795)
at com.ibm.db2.jcc.am.um.b(um.java:3949)
at com.ibm.db2.jcc.am.um.ac(um.java:709)
at com.ibm.db2.jcc.am.um.executeUpdate(um.java:692)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(N onBatchingBatcher.j
ava:23)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2252)
... 58 more
10:56:33,640 ERROR [HibernateUtil] HIBUTIL.ERROR_0008 - commitTransaction() fail
ed.
org.hibernate.exception.DataException: could not insert: [org.pentaho.platform.r
epository.solution.dbbased.RepositoryFile]
at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.j
ava:77)
at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelp
er.java:43)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2272)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2665)
at org.hibernate.action.EntityInsertAction.execute(En tityInsertAction.ja
va:60)
at org.hibernate.engine.ActionQueue.execute(ActionQue ue.java:279)
at org.hibernate.engine.ActionQueue.executeActions(Ac tionQueue.java:263)

at org.hibernate.engine.ActionQueue.executeActions(Ac tionQueue.java:167)

at org.hibernate.event.def.AbstractFlushingEventListe ner.performExecutio
ns(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener. onFlush(DefaultFlus
hEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.j ava:1000)
at org.hibernate.impl.SessionImpl.managedFlush(Sessio nImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(J DBCTransaction.java
:106)
at org.pentaho.platform.repository.hibernate.Hibernat eUtil.commitTransac
tion(HibernateUtil.java:447)
at org.pentaho.platform.repository.solution.dbbased.D bBasedSolutionRepos
itory.loadSolutionFromFileSystem(DbBasedSolutionRe pository.java:866)
at org.pentaho.platform.repository.solution.dbbased.D bBasedSolutionRepos
itory.init(DbBasedSolutionRepository.java:114)
at org.pentaho.platform.repository.solution.dbbased.D bBasedSolutionRepos
itory.<init>(DbBasedSolutionRepository.java:96)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(Unknown Sou
rce)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance0(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.instanceClass(AbstractSpringPentahoOb jectFactory.java:78)
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.retreiveObject(AbstractSpringPentahoO bjectFactory.java:117)
at org.pentaho.platform.engine.core.system.objfac.Abs tractSpringPentahoO
bjectFactory.get(AbstractSpringPentahoObjectFactor y.java:72)
at org.pentaho.platform.engine.core.system.PentahoSys tem.get(PentahoSyst
em.java:508)
at org.pentaho.platform.engine.core.system.PentahoSys tem.get(PentahoSyst
em.java:484)
at org.pentaho.platform.engine.services.solution.Solu tionEngine.createAc
tionSequence(SolutionEngine.java:455)
at org.pentaho.platform.engine.services.solution.Solu tionEngine.executeI
nternal(SolutionEngine.java:364)
at org.pentaho.platform.engine.services.solution.Solu tionEngine.execute(
SolutionEngine.java:316)
at org.pentaho.platform.engine.services.solution.Solu tionEngine.execute(
SolutionEngine.java:192)
at org.pentaho.platform.engine.core.system.PentahoSys tem.globalStartup(P
entahoSystem.java:680)
at org.pentaho.platform.engine.core.system.PentahoSys tem.globalStartup(P
entahoSystem.java:635)
at org.pentaho.platform.engine.core.system.PentahoSys tem.init(PentahoSys
tem.java:275)
at org.pentaho.platform.engine.core.system.PentahoSys tem.init(PentahoSys
tem.java:206)
at org.pentaho.platform.web.http.context.SolutionCont extListener.context
Initialized(SolutionContextListener.java:136)
at org.apache.catalina.core.StandardContext.listenerS tart(StandardContex
t.java:3764)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:4
216)
at org.apache.catalina.core.ContainerBase.addChildInt ernal(ContainerBase
.java:760)
at org.apache.catalina.core.ContainerBase.addChild(Co ntainerBase.java:74
0)
at org.apache.catalina.core.StandardHost.addChild(Sta ndardHost.java:544)

at org.apache.catalina.startup.HostConfig.deployDirec tory(HostConfig.jav
a:926)
at org.apache.catalina.startup.HostConfig.deployDirec tories(HostConfig.j
ava:889)
at org.apache.catalina.startup.HostConfig.deployApps( HostConfig.java:492
)
at org.apache.catalina.startup.HostConfig.start(HostC onfig.java:1149)
at org.apache.catalina.startup.HostConfig.lifecycleEv ent(HostConfig.java
:311)
at org.apache.catalina.util.LifecycleSupport.fireLife cycleEvent(Lifecycl
eSupport.java:120)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1022)

at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:736)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1014)

at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:443
)
at org.apache.catalina.core.StandardService.start(Sta ndardService.java:4
48)
at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:700
)
at org.apache.catalina.startup.Catalina.start(Catalin a.java:552)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootst rap.java:295)
at org.apache.catalina.startup.Bootstrap.main(Bootstr ap.java:433)
Caused by: com.ibm.db2.jcc.am.mo: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, S
QLERRMC=null, DRIVER=3.58.82
at com.ibm.db2.jcc.am.ed.a(ed.java:668)
at com.ibm.db2.jcc.am.ed.a(ed.java:60)
at com.ibm.db2.jcc.am.ed.a(ed.java:127)
at com.ibm.db2.jcc.am.tm.b(tm.java:2192)
at com.ibm.db2.jcc.am.tm.c(tm.java:2175)
at com.ibm.db2.jcc.t4.fb.k(fb.java:355)
at com.ibm.db2.jcc.t4.fb.a(fb.java:61)
at com.ibm.db2.jcc.t4.t.a(t.java:50)
at com.ibm.db2.jcc.t4.vb.b(vb.java:218)
at com.ibm.db2.jcc.am.um.jc(um.java:2860)
at com.ibm.db2.jcc.am.um.b(um.java:3795)
at com.ibm.db2.jcc.am.um.b(um.java:3949)
at com.ibm.db2.jcc.am.um.ac(um.java:709)
at com.ibm.db2.jcc.am.um.executeUpdate(um.java:692)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(N onBatchingBatcher.j
ava:23)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2252)
... 58 more
10:56:33,656 ERROR [SolutionEngine] 21a0e097-0420-11df-8693-8537c7a07994:SOLUTIO
N-ENGINE:global-department-list.xaction: SolutionEngine.ERROR_0007 - Action sequ
ence execution failed
Pentaho BI Platform server is ready. (Pentaho Platform Engine Core 3.5.0.stable.
39705) Base Url = http://localhost:8080/pentaho/, Solution Path = C:\Dokumente u
nd Einstellungen\Ajmal\Desktop\biserver-ce-3.5.0.stable\biserver-ce\pentaho-solu
tions
Jan 18, 2010 10:56:34 AM org.apache.coyote.http11.Http11BaseProtocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
Jan 18, 2010 10:56:34 AM org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
Jan 18, 2010 10:56:34 AM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/47 config=null
Jan 18, 2010 10:56:34 AM org.apache.catalina.storeconfig.StoreLoader load
INFO: Find registry server-registry.xml at classpath resource
Jan 18, 2010 10:56:34 AM org.apache.catalina.startup.Catalina start
INFO: Server startup in 80860 ms


Thanks a lot for your help

[TomS]

Hi again,

according to:
http://publib.boulder.ibm.com/infoce...codes/n302.htm

it seems like the hibernate-dialect is not correct, trying to insert values which
are too large..

I would suggest checking all hibernate-related files for the correct dialect-setting
and verifying the column-sizings in comparison to other database-schemes.
(e.g. biserver-ce\data\oracle10g\create_repository_ora.sql)

Perhaps you just need to increase the column-sizes a bit...
let´s say: double them and retry.

HTH,

Tom

[mm2009]

Hi Kahn,
I configured Pentaho 3.5 state platform to IBM DB2 for Z/OS version 8 successfully. I ran into lot of sql errors like you got. I remember seeing errors about column sizes for numeric columns. BITINT is not there in DB2 version 8, so I got to change them to DECIMAL(19,0).

If you still have any outstanding sql errors, reply so we can compare column definitions.

[Kahn]

Hi Kahn,
I configured Pentaho 3.5 state platform to IBM DB2 for Z/OS version 8 successfully. I ran into lot of sql errors like you got. I remember seeing errors about column sizes for numeric columns. BITINT is not there in DB2 version 8, so I got to change them to DECIMAL(19,0).

If you still have any outstanding sql errors, reply so we can compare column definitions.

Hi,

I have got a lots error because of the hibernate database. Tom recommended to increase the size of the columns but without success. At the moment I got the following error:

at com.ibm.db2.jcc.am.tm.b(tm.java:2192)
at com.ibm.db2.jcc.am.tm.c(tm.java:2175)
at com.ibm.db2.jcc.t4.fb.k(fb.java:355)
at com.ibm.db2.jcc.t4.fb.a(fb.java:61)
at com.ibm.db2.jcc.t4.t.a(t.java:50)
at com.ibm.db2.jcc.t4.vb.b(vb.java:218)
at com.ibm.db2.jcc.am.um.jc(um.java:2860)
at com.ibm.db2.jcc.am.um.b(um.java:3795)
at com.ibm.db2.jcc.am.um.b(um.java:3949)
at com.ibm.db2.jcc.am.um.ac(um.java:709)
at com.ibm.db2.jcc.am.um.executeUpdate(um.java:692)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(N onBatchingBatcher.j
ava:23)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2252)
... 60 more
21:36:26,937 ERROR [JDBCExceptionReporter] DB2 SQL Error: SQLCODE=-302, SQLSTATE
=22001, SQLERRMC=null, DRIVER=3.58.82
21:36:26,937 ERROR [AbstractFlushingEventListener] Could not synchronize databas
e state with session
org.hibernate.exception.DataException: could not insert: [org.pentaho.platform.r
epository.solution.dbbased.RepositoryFile]
at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.j
ava:77)
at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelp
er.java:43)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2272)
at org.hibernate.persister.entity.AbstractEntityPersi ster.insert(Abstrac
tEntityPersister.java:2665)
at org.hibernate.action.EntityInsertAction.execute(En tityInsertAction.ja
va:60)

..................

21:36:26,984 ERROR [SolutionEngine] daebfdf5-060b-11df-b1c5-1d7ba557753b:SOLUTIO
N-ENGINE:global-department-list.xaction: SolutionEngine.ERROR_0007 - Action sequ
ence execution failed
Pentaho BI Platform server is ready. (Pentaho Platform Engine Core 3.5.0.stable.
39705) Base Url = http://localhost:8080/pentaho/, Solution Path = C:\Dokumente u
nd Einstellungen\Ajmal\Desktop\biserver-ce-3.5.0.stable\biserver-ce\pentaho-solu
tions
Jan 20, 2010 9:36:27 PM org.apache.coyote.http11.Http11BaseProtocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
Jan 20, 2010 9:36:27 PM org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
Jan 20, 2010 9:36:27 PM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/15 config=null
Jan 20, 2010 9:36:27 PM org.apache.catalina.storeconfig.StoreLoader load
INFO: Find registry server-registry.xml at classpath resource
Jan 20, 2010 9:36:27 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in 51812 ms



I use the "DB2v8Delegate" for the jdbcjobstore in ....\biserver-ce-3.5.0.stable\biserver-ce\pentaho-solutions\system\quartz\quartz.properties because I could't find any other definition. Is that correct?


Here is my column definition:


CREATE TABLE HIBERNATE.AUTHORITIES (
AUTHORITY VARCHAR(100) NOT NULL,
DESCRIPTION VARCHAR(200),
/* Keys */
PRIMARY KEY (AUTHORITY)
) IN PENTAHO@



CREATE TABLE HIBERNATE.BDPARAMS (
ITEMID VARCHAR(200) NOT NULL,
PARAMVALUE VARCHAR(200),
PARAMKEY VARCHAR(200) NOT NULL,
/* Keys */
PRIMARY KEY (ITEMID, PARAMKEY),
/* Foreign keys */
CONSTRAINT FK61733C48FA34BFDC
FOREIGN KEY (ITEMID)
REFERENCES HIBERNATE.RTELEMENT(INSTANCEID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.BGCONTENTID (
BGCONTID VARCHAR(200) NOT NULL,
REVISION INTEGER NOT NULL,
BGUSER VARCHAR(200) NOT NULL,
/* Keys */
PRIMARY KEY (BGCONTID)
) IN PENTAHO@



CREATE TABLE HIBERNATE.CONTENTITEM (
CONTITEMID VARCHAR(300) NOT NULL,
REVISION INTEGER NOT NULL,
NAMECOL VARCHAR(400) NOT NULL,
PARENT_ID VARCHAR(200),
PATHCOL VARCHAR(500) NOT NULL,
TITLE VARCHAR(400) NOT NULL,
MIMETYPE VARCHAR(200),
URL VARCHAR(600),
LATESTVERNUM INTEGER,
EXTENSION VARCHAR(100) NOT NULL,
WRITEMODE INTEGER NOT NULL,
/* Keys */
PRIMARY KEY (CONTITEMID),
UNIQUE (PATHCOL),
/* Foreign keys */
CONSTRAINT FK692B5EEC44F32395
FOREIGN KEY (PARENT_ID)
REFERENCES HIBERNATE.CONTENTLOCATION(CONTENTID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.CONTENTLOCATION (
CONTENTID VARCHAR(200) NOT NULL,
REVISION INTEGER NOT NULL,
NAMECOL VARCHAR(400) NOT NULL,
SOLNID VARCHAR(200) NOT NULL,
DESCRIPTION VARCHAR(400) NOT NULL,
DIRPATH VARCHAR(600) NOT NULL,
/* Keys */
PRIMARY KEY (CONTENTID),
UNIQUE (DIRPATH)
) IN PENTAHO@


CREATE TABLE HIBERNATE.CONTITEMFILE (
CONTIFILEID VARCHAR(200) NOT NULL,
REVISION INTEGER NOT NULL,
OSFILENAME VARCHAR(400) NOT NULL,
OSPATH VARCHAR(1024) NOT NULL,
ACTNAME VARCHAR(200) NOT NULL,
PARENT_ID VARCHAR(200),
FILESIZE BIGINT,
FILEDATETIME TIMESTAMP NOT NULL,
ISINITIALIZED INTEGER,
/* Keys */
PRIMARY KEY (CONTIFILEID),
/* Foreign keys */
CONSTRAINT FK7FC3F44164D906F3
FOREIGN KEY (PARENT_ID)
REFERENCES HIBERNATE.CONTENTITEM(CONTITEMID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.CPLXPARAMS (
ITEMID VARCHAR(200) NOT NULL,
PARAMVALUE BLOB(600) INLINE LENGTH 68,
PARAMKEY VARCHAR(200) NOT NULL,
/* Keys */
PRIMARY KEY (ITEMID, PARAMKEY),
/* Foreign keys */
CONSTRAINT FKD6D6E97FFA34BFDC
FOREIGN KEY (ITEMID)
REFERENCES HIBERNATE.RTELEMENT(INSTANCEID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.DTPARAMS (
ITEMID VARCHAR(300) NOT NULL,
PARAMVALUE TIMESTAMP,
PARAMKEY VARCHAR(300) NOT NULL,
/* Keys */
PRIMARY KEY (ITEMID, PARAMKEY),
/* Foreign keys */
CONSTRAINT FK7F994A16FA34BFDC
FOREIGN KEY (ITEMID)
REFERENCES HIBERNATE.RTELEMENT(INSTANCEID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.GRANTED_AUTHORITIES (
USERNAME VARCHAR(200) NOT NULL,
AUTHORITY VARCHAR(200) NOT NULL,
/* Keys */
PRIMARY KEY (USERNAME, AUTHORITY),
/* Foreign keys */
CONSTRAINT FK7471775D41B6DA97
FOREIGN KEY (AUTHORITY)
REFERENCES HIBERNATE.AUTHORITIES(AUTHORITY)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT FK7471775DD9EDC77F
FOREIGN KEY (USERNAME)
REFERENCES HIBERNATE.USERS(USERNAME)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.LNGPARAMS (
ITEMID VARCHAR(300) NOT NULL,
PARAMVALUE BIGINT,
PARAMKEY VARCHAR(200) NOT NULL,
/* Keys */
PRIMARY KEY (ITEMID, PARAMKEY),
/* Foreign keys */
CONSTRAINT FKE304FCCBFA34BFDC
FOREIGN KEY (ITEMID)
REFERENCES HIBERNATE.RTELEMENT(INSTANCEID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.LSPARAMS (
ITEMID VARCHAR(200) NOT NULL,
PARAMVALUE CLOB(600) INLINE LENGTH 68,
PARAMKEY VARCHAR(200) NOT NULL,
/* Keys */
PRIMARY KEY (ITEMID, PARAMKEY),
/* Foreign keys */
CONSTRAINT FK89BC75CDFA34BFDC
FOREIGN KEY (ITEMID)
REFERENCES HIBERNATE.RTELEMENT(INSTANCEID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.PARAMTYPESMAP (
ITEMID VARCHAR(300) NOT NULL,
PARAMVALUE VARCHAR(200),
PARAMKEY VARCHAR(200) NOT NULL,
/* Keys */
PRIMARY KEY (ITEMID, PARAMKEY),
/* Foreign keys */
CONSTRAINT FKD3EDA1B0FA34BFDC
FOREIGN KEY (ITEMID)
REFERENCES HIBERNATE.RTELEMENT(INSTANCEID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.PRO_FILES (
FILE_ID VARCHAR(300) NOT NULL,
REVISION INTEGER NOT NULL,
PARENT VARCHAR(300),
FILENAME VARCHAR(600) NOT NULL,
FULLPATH VARCHAR(600) NOT NULL,
"DATA" BLOB(600) INLINE LENGTH 68,
DIRECTORY SMALLINT NOT NULL,
LASTMODIFIED BIGINT NOT NULL,
CHILD_ID VARCHAR(300),
/* Keys */
PRIMARY KEY (FILE_ID),
UNIQUE (FULLPATH),
/* Foreign keys */
CONSTRAINT FK94A87E2569FABF5E
FOREIGN KEY (CHILD_ID)
REFERENCES HIBERNATE.PRO_FILES(FILE_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT FK94A87E25CBBBB0EA
FOREIGN KEY (PARENT)
REFERENCES HIBERNATE.PRO_FILES(FILE_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.PRO_SCHEDULE (
SCHEDULEID VARCHAR(300) NOT NULL,
REVISION INTEGER NOT NULL,
SCHEDTITLE VARCHAR(400) NOT NULL,
SCHEDREF VARCHAR(1024) NOT NULL,
SCHEDDESC VARCHAR(200) NOT NULL,
CRONSTRING VARCHAR(600),
REPEATCOUNT INTEGER,
REPEATINTERVAL INTEGER,
STARTDATE TIMESTAMP,
ENDDATE TIMESTAMP,
GROUPNAME VARCHAR(200),
LAST_TRIGGER TIMESTAMP,
/* Keys */
PRIMARY KEY (SCHEDULEID)
) IN PENTAHO@



CREATE TABLE HIBERNATE.PRO_SUBCONTENT (
SUBCONTID VARCHAR(200) NOT NULL,
REVISION INTEGER NOT NULL,
SUBCONTTYPE VARCHAR(600) NOT NULL,
SUBCONTACTREF VARCHAR(600) NOT NULL,
/* Keys */
PRIMARY KEY (SUBCONTID),
UNIQUE (SUBCONTACTREF)
) IN PENTAHO@



CREATE TABLE HIBERNATE.PRO_SUBCONTPARMS (
ITEMID VARCHAR(300) NOT NULL,
PARAMVALUE VARCHAR(600) FOR BIT DATA,
PARAMKEY VARCHAR(300) NOT NULL,
/* Keys */
PRIMARY KEY (ITEMID, PARAMKEY),
/* Foreign keys */
CONSTRAINT FKF3CE4C47846761A
FOREIGN KEY (ITEMID)
REFERENCES HIBERNATE.PRO_SUBCONTENT(SUBCONTID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.PRO_SUBCONT_SCHEDLIST (
SCHEDULEID VARCHAR(200) NOT NULL,
ELT VARCHAR(200) NOT NULL,
SCHEDID INTEGER NOT NULL,
/* Keys */
PRIMARY KEY (SCHEDULEID, SCHEDID),
/* Foreign keys */
CONSTRAINT FKF0D3FBD62593BC9E
FOREIGN KEY (SCHEDULEID)
REFERENCES HIBERNATE.PRO_SUBCONTENT(SUBCONTID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT FKF0D3FBD69E226721
FOREIGN KEY (ELT)
REFERENCES HIBERNATE.PRO_SCHEDULE(SCHEDULEID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.PRO_SUBSCRIBE (
SUBSCRID VARCHAR(300) NOT NULL,
REVISION INTEGER NOT NULL,
SUBSCRTYPE INTEGER NOT NULL,
SUBSCRUSER VARCHAR(300) NOT NULL,
SUBSCRTITLE VARCHAR(400) NOT NULL,
SUBSCR_CONTID VARCHAR(200),
SUBSCRDEST VARCHAR(400),
/* Keys */
PRIMARY KEY (SUBSCRID),
/* Foreign keys */
CONSTRAINT FK5E7511F89D8AC376
FOREIGN KEY (SUBSCR_CONTID)
REFERENCES HIBERNATE.PRO_SUBCONTENT(SUBCONTID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.PRO_SUBSCRPARMS (
ITEMID VARCHAR(300) NOT NULL,
PARAMVALUE BLOB(600) INLINE LENGTH 68,
PARAMKEY VARCHAR(300) NOT NULL,
/* Keys */
PRIMARY KEY (ITEMID, PARAMKEY),
/* Foreign keys */
CONSTRAINT FK95DADE9355DC7DE8
FOREIGN KEY (ITEMID)
REFERENCES HIBERNATE.PRO_SUBSCRIBE(SUBSCRID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.PRO_SUBS_SCHEDLIST (
SCHEDULEID VARCHAR(300) NOT NULL,
ELT VARCHAR(300) NOT NULL,
SCHEDID INTEGER NOT NULL,
/* Keys */
PRIMARY KEY (SCHEDULEID, SCHEDID),
/* Foreign keys */
CONSTRAINT FK2A8C749B7329C46C
FOREIGN KEY (SCHEDULEID)
REFERENCES HIBERNATE.PRO_SUBSCRIBE(SUBSCRID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT FK2A8C749B9E226721
FOREIGN KEY (ELT)
REFERENCES HIBERNATE.PRO_SCHEDULE(SCHEDULEID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.RTELEMENT (
INSTANCEID VARCHAR(300) NOT NULL,
REVISION INTEGER NOT NULL,
PARID VARCHAR(600),
PARTYPE VARCHAR(200),
SOLNID VARCHAR(600),
READONLY SMALLINT,
CREATED TIMESTAMP NOT NULL,
/* Keys */
PRIMARY KEY (INSTANCEID)
) IN PENTAHO@



CREATE TABLE HIBERNATE.SSPARAMS (
ITEMID VARCHAR(300) NOT NULL,
PARAMVALUE VARCHAR(600),
PARAMKEY VARCHAR(200) NOT NULL,
/* Keys */
PRIMARY KEY (ITEMID, PARAMKEY),
/* Foreign keys */
CONSTRAINT FK60E4AFE6FA34BFDC
FOREIGN KEY (ITEMID)
REFERENCES HIBERNATE.RTELEMENT(INSTANCEID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.USER_SETTINGS (
USER_SETTINGS_ID BIGINT NOT NULL,
USERNAME VARCHAR(200) NOT NULL,
SETTING_NAME VARCHAR(300) NOT NULL,
SETTING_VALUE VARCHAR(2048) NOT NULL,
/* Keys */
PRIMARY KEY (USER_SETTINGS_ID)
) IN PENTAHO@



CREATE TABLE HIBERNATE.DATASOURCE (
NAME VARCHAR(200) NOT NULL,
MAXACTCONN INTEGER,
DRIVERCLASS VARCHAR(200),
IDLECONN INTEGER,
USERNAME VARCHAR(200),
PASSWORD VARCHAR(200),
URL VARCHAR(400),
"QUERY" VARCHAR(300),
WAIT INTEGER,
/* Keys */
PRIMARY KEY (NAME)
) IN PENTAHO@



CREATE TABLE HIBERNATE.PRO_ACLS_LIST (
ACL_ID VARCHAR(500) NOT NULL,
ACL_MASK INTEGER NOT NULL,
RECIP_TYPE INTEGER NOT NULL,
RECIPIENT VARCHAR(800) NOT NULL,
ACL_POSITION INTEGER NOT NULL,
/* Keys */
PRIMARY KEY (ACL_ID, ACL_POSITION),
/* Foreign keys */
CONSTRAINT FKB65646C2B23C5D30
FOREIGN KEY (ACL_ID)
REFERENCES HIBERNATE.PRO_FILES(FILE_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) IN PENTAHO@



CREATE TABLE HIBERNATE.USERS (
USERNAME VARCHAR(400) NOT NULL,
PASSWORD VARCHAR(400),
DESCRIPTION VARCHAR(500),
ENABLED INTEGER,
/* Keys */
PRIMARY KEY (USERNAME)
) IN PENTAHO@



Thanks a lot for your help in advance.


Elmar

[mm2009]

I used "DB2v8Delegate" for jdbcjobstore since our host DB2 is version 8.

For debugging purpose you can enable the sql trace ("showsql") in "biserver-ce\pentaho-solutions\system\hibernate\db2.hibernate.cfg.xml" to see what sql statement is being fired. This will give some clue. Also, you can trap sql statement in the host DB2 system.

Also, some problems for me when away when I used the new JDBC driver since the driver I was using was old. I am using "IBM Data Server Driver for JDBC and SQLJ" version 9.1 fixpack 7 (JCC version 3.8.47). which you can download from IBM website.

Hope this helps.

[Kahn]

I used "DB2v8Delegate" for jdbcjobstore since our host DB2 is version 8.

For debugging purpose you can enable the sql trace ("showsql") in "biserver-ce\pentaho-solutions\system\hibernate\db2.hibernate.cfg.xml" to see what sql statement is being fired. This will give some clue. Also, you can trap sql statement in the host DB2 system.

Also, some problems for me when away when I used the new JDBC driver since the driver I was using was old. I am using "IBM Data Server Driver for JDBC and SQLJ" version 9.1 fixpack 7 (JCC version 3.8.47). which you can download from IBM website.

Hope this helps.

Hi,

which version of the jdbcjobstore "DB2Delegate" do you use with DB2 Version 9.1 fixpack 7?

Why do you use the IBM Data Server Driver for JDBC and SQLJ because there is a JDBC-Driver delivered with the DB2 Data base (.....\IBM\SQLLIB\java)? I use "db2jcc" and "db2jcc_license_cu" from this directory. Is that correct?

Thanks for your help

Elmar

[mm2009]

Quartz does not show as "org.quartz.impl.jdbcjobstore.DB2v9Delegate" as valid so I guess you use "org.quartz.impl.jdbcjobstore.DB2v8Delegate" for job store. The SQL errors you were getting may not be all for quartz database. You will need to figured out which table is having issue (debugging methods that I suggested).

IBM Data Server Driver for JDBC and SQLJ is new naming of the driver since it now applies to DB2, Informix etc., which is the same you were referring to in (.....\IBM\SQLLIB\java). Check what version of JCC version (db2jcc.jar) you are using. It seems like you are using JCC version 3.58.82. I am using version 3.8.47.

I am not sure if this "JDBC/JCC" version makes any difference since your version is close, but mainly trap the SQL statement and find out the offending table to debug further.