Changing default Database on JBoss 4.0

[sysuser1]

Hello everybody,

I'm rather new to JBoss and wonder how difficult it is to change the default database. I try to use Informix as database and did everything mentioned in the JBoss startguide in the section "Using other databases".

1.) I exchanged the file hsqldb-ds.xml by informix-ds.xml (where I put the right database-url, user and password)
jndi-name of the database is "informix"
2.) I added the informix driver to the lib directory of the server
3.) I modified the standardjbosscmp-jdbc.xml
java:/informix
<datasource-mapping>InformixDB</datasource-mapping>

4.) I modified the standardjaws.xml
java:/informix
<type-mapping>InformixDB</type-mapping>

When I start JBoss, the Informix database is recognized and seems to be ok (looking at the jmx-console) but anyway, I get the following error during startup:

MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM:
ObjectName: jboss.jca:name=DefaultDS,service=DataSourceBinding
state: NOTYETINSTALLED
I Depend On:
Depends On Me: jboss.ejb:persistencePolicy=database,service=EJBTimerService
jboss:service=Ebxmlrr
jboss:service=KeyGeneratorFactory,type=HiLo
jboss.mq:service=StateManager
jboss.mq:service=PersistenceManager

Does anybody know what's the cause of this problem and how to solve it (so that jBoss starts without errors).

Thanks in advance.

[robisz]

If you want to completely remove hsqldb from JBoss 4.0, you also
need to modify additional xmls...

Check these pages first:

http://www.jboss.org/wiki/Wiki.jsp?page=ConfigJBossMQDB
http://www.jboss.org/wiki/Wiki.jsp?page=ChangeTheDatabase

[sysuser1]

First of all thanks for your answer.

I now made the modifications in the additional xmls, but how should the informix-jdbc2-service.xml(?) look like. There is no example file in the examples/jms directory for informix.

[robisz]

It's true but you can create your own easily.

You only need to change DefaultDS to your datasource name,
and modify the SQL statements if needed.

You also need to create an informix-jdbc-state-service.xml
in the same way.

[sysuser1]

I also added this two files and deleted the one's of hsql. Now i get the message:

ObjectName: jboss.ejb:persistencePolicy=database,service=EJBTimerService
state: FAILED
I Depend On: jboss.jca:name=informix,service=DataSourceBinding

Anyway...should I put the xxx-service files etc. in the deploy directory or in the deploy-hasingleton directory? Which is the difference. The default configuration of jboss e.g. has no deploy-hasingleton directory.

I'm rather confused...

[sysuser1]

Sorry, I think the real error message is this one:

MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM:
ObjectName: jboss.mq:service=PersistenceManager
state: FAILED
I Depend On: jboss.jca:name=informix,service=DataSourceBinding

Depends On Me: jboss.mq:service=DestinationManager
org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Messag
e recovery may not be accurate; - nested throwable: (java.sql.SQLException: The
specified table (jms_messages) is not in the database.)
ObjectName: jboss.jca:name=DefaultDS,service=DataSourceBinding
state: NOTYETINSTALLED
I Depend On:
Depends On Me: jboss:service=Ebxmlrr
jboss:service=KeyGeneratorFactory,type=HiLo

[robisz]

I think you haven't change the configuration everywhere...
You need to modify the following files also:

deploy/ejb-deployer.xml
Change DefaultDS to your datasource. Modify CREATE TABLE statement.
(Use BLOB type instead of "other").

uuid-key-generator.sar/META-INF/jboss-service.xml
Change DefaultDS to your datasource. Modify CREATE TABLE statement.

ebxmlrr-service.sar/META-INF/jboss-service.xml
Change DefaultDS to your datasource (in depend element too).

conf/login-config.xml
Change DefaultDS to your datasource.

This should be enough... I've tried it with Oracle.

The "all" configuration contains services for clustering.
Use "default" if you don't need it.

[sysuser1]

After making the modifications I still get the error:

15:02:10,203 ERROR [DatabasePersistencePolicy] Starting failed jboss.ejb:persist
encePolicy=database,service=EJBTimerService
java.sql.SQLException: A syntax error has occurred.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:348)
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3000)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3310)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2263)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2183)
at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:692)
at com.informix.jdbc.IfxResultSet.executeUpdate(IfxResultSet.java:308)
at com.informix.jdbc.IfxStatement.executeUpdateImpl(IfxStatement.java:85

Maybe this occurs because I did not modify the create table statement? But I don't know how.
The content of this statement in jboss-service.xml is


create table HILOSEQUENCES (
SEQUENCENAME varchar(50) not null,
HIGHVALUES integer not null,
constraint hilo_pk primary key (SEQUENCENAME)
)


What da I have to change here? Sorry, but I'm not very familiar with databases.

[robisz]

I think the problem is with the CREATE TABLE statement in
ejb-deployer.xml.

You need to change the SQL type "other" to your database's BLOB
type. (I haven't known Informix, but you probably should change it
to "BLOB").

[sysuser1]

Hmm, the SQL-type is "BLOB" and I changed it in the ejb-deployer.xml.

This cannot be the cause of the exception.

[robisz]

Maybe there are other issues with your SQL statements...
Try to run them in your database manually and check errors...

[sysuser1]

Maybe I have to change the sql-properties in informix-jdbc-state-service.xml.

POPULATE.TABLES.01 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('guest', 'guest')
POPULATE.TABLES.02 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('j2ee', 'j2ee')
POPULATE.TABLES.03 = INSERT INTO JMS_USERS (USERID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
POPULATE.TABLES.04 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('nobody', 'nobody')
POPULATE.TABLES.05 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('dynsub', 'dynsub')
POPULATE.TABLES.06 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('guest','guest')
POPULATE.TABLES.07 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('j2ee','guest')
POPULATE.TABLES.08 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('john','guest')
POPULATE.TABLES.09 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('subscriber','john')
POPULATE.TABLES.10 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','john')
POPULATE.TABLES.11 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','dynsub')
POPULATE.TABLES.12 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','john')
POPULATE.TABLES.13 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','dynsub')
POPULATE.TABLES.14 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('noacc','nobody')

could cause problems, because the tables cannot/shoulc not be created in the database.
Is it a problem if i delete these lines?

[robisz]

If you want to use JMS and need persistence, you must keep these files.
If you don't need JMS you can remove the jms directory.

If you can't modify these SQL statements succesfully, I recommend you
return to original default configuration and let JBoss use hsqldb for
its own services (don't delete hsqldb-ds.xml).

[sysuser1]

Does JBoss really (try to) create those tables, e.g.

create table TIMERS (
TIMERID varchar(50) not null,
TARGETID varchar(50) not null,
INITIALDATE timestamp not null,
INTERVAL bigint,
INSTANCEPK blob,
INFO blob,
constraint timers_pk primary key (TIMERID)

in the database? Is it possible to prevent this behaviour? It wouldn't be good if JBoss creates such tables in my database because I'm not the only one using it.

[sysuser1]

Ok, I think it's better to keep hsql for the jms service. Which files do I have to "remodify"? I deleted my informix-jdbc2-service.xml, etc. and put the hsql files instead.
What's with the ejb-deployer and those modifications?
I want to keep Informix as current "working" database and JBoss shall use hsql for the jms service.