3 Replies Latest reply on Jul 16, 2005 4:09 PM by Elias Ross

    Oracle DDL for JBossMQ Jms Tables

    john sisson Newbie

      We are deploying a JBoss 4.0.2 appServer against Oracle9i for our application and want to configure it to use the same Oracle database for JMS.

      Summary:
      We would like to get DDL for the tables used by JBossMQ.Jms in some explicit (vendor neutral) format such as an SQL script.

      Issues:
      1. Oracle config seems to not be complete:
      The JBoss Jms configs for Oracle seem to be incomplete; the TIMERS and HILO tables are not specified in these config files and sem to be never created (on Unix) - so the 'out of the box' config for Oracle under 4.0.2 fails for us.

      2. the Oracle strategy does not meet corporate standards:
      The jms db user (configured in the jboss setup) has to have 'dba' authorities to create tables and indexes.
      This must be a problem comon to many Oracle sites.

      Soooo....:
      1. We would like to create an ourApp_j2ee tablespace and an ourApp_j2ee user explicitly for the Jms activities; and to create the tables in a script executed by an authorized user (ourApp_dba), supplying Oracle synonyms and grants to the j2ee user.

      2. This will have to be done with full knowledge of the tables that JBossMQ Jms will need. We can reverse engineer much of this from poking about in xml files. But there must be a DDL for these tables that is used to create the xml 'sql property' segments in the first place.
      That is what we would like to locate; I imagine such must exist in the JBossMQ.Jms projects somewhere.

      Risks:
      - Is there any known reason why this should not work?
      - Are any of the tables 'required' to be destroyed and recreated at startup of the server (if so, why)?
      - It seems that this may be the case for the HiLoSequence table

      Your guidance on this requested