12 Replies Latest reply on Sep 23, 2008 3:44 PM by dd_la_frime

    JBM and informix

    dd_la_frime

      Hello,

      I need to know if JBM runs with informix database as there is no informix-persistence-service.xml file ?
      If JBM can run with such a database could you please give me the xml file for the SQL schema to be created.

      Cheers.

      Christophe.

        • 1. Re: JBM and informix
          timfox

          Informix is currently not one of our supported databases.

          The full list of supported databases is on the user wiki page.

          JBM 2.0 - when configured for JDBC persistence will use Hibernate so will support any database that Hibernate supports.

          For JBM 1.x, you need to write your own config file. If you do this, feel free to contribute it back to the project.

          • 2. Re: JBM and informix
            dd_la_frime

            Ok thanks.

            I started to work on my own config file but I have problems with the size of the index in the JBM_POSTOFFICE.
            I had to modify the size of the varchar(255) to varchar(192) so that the sum of the two varchar fields and the integer field fit the total size of informix 9.4. The two columns which had to be modified are : POSTOFFICE_NAME and QUEUE_NAME.
            Is the size of 255 characters long very important ?

            Cheers.

            PS : as soon as the file is written and validated I will contribute.

            • 3. Re: JBM and informix
              timfox

              As long as your queue names can fit inside that field it shouldn't pose a problem.

              • 4. Re: JBM and informix
                dd_la_frime

                Ok, same for the postoffice_name I guess ?

                Do you know, when do you plan to release JBM 2.x ?

                Thanks.

                • 5. Re: JBM and informix
                  timfox

                  We aim to get a JBM 2.0 GA release out towards the end of the year, a beta in the summer, and an alpha in four to six weeks time,

                  • 6. Re: JBM and informix
                    dd_la_frime

                    Ok, thank you very much.

                    • 7. Re: JBM and informix
                      abendt

                      is your config publically available somewhere?

                      thanks

                      • 8. Re: JBM and informix
                        dd_la_frime

                         

                        "abendt" wrote:
                        is your config publically available somewhere?

                        thanks


                        Sorry, no.
                        I just had to modified some data type in the DDL file to be compatible with informix.

                        • 9. Re: JBM and informix
                          abendt

                          would it be possible to post your config here?

                          if not: did you change more columns than the ones you've mentioned?

                          thanks

                          • 10. Re: JBM and informix
                            dd_la_frime

                            Here is the xml file :


                            <?xml version="1.0" encoding="UTF-8"?>

                            <!--
                            Postgresql persistence deployment descriptor.

                            Tested with PostgresSQL 8.2.3

                            $Id$
                            -->



                            <!-- Persistence Manager MBean configuration
                            ======================================== -->

                            <mbean code="org.jboss.messaging.core.jmx.JDBCPersistenceManagerService"
                            name="jboss.messaging:service=PersistenceManager"
                            xmbean-dd="xmdesc/JDBCPersistenceManager-xmbean.xml">

                            jboss.jca:service=DataSourceBinding,name=InformixDS

                            <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager

                            <!-- The datasource to use for the persistence manager -->

                            java:/InformixDS

                            <!-- If true will attempt to create tables and indexes on every start-up -->

                            true

                            <!-- If true then will use JDBC batch updates -->

                            true

                            <![CDATA[
                            CREATE_DUAL=CREATE TABLE JBM_DUAL (DUMMY INTEGER, PRIMARY KEY (DUMMY)) LOCK MODE ROW
                            CREATE_MESSAGE_REFERENCE=CREATE TABLE JBM_MSG_REF (MESSAGE_ID INT8, CHANNEL_ID INT8, TRANSACTION_ID INT8, STATE CHAR(1), ORD INT8, PAGE_ORD INT8, DELIVERY_COUNT INTEGER, SCHED_DELIVERY INT8, PRIMARY KEY(MESSAGE_ID, CHANNEL_ID)) LOCK MODE ROW
                            CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID, STATE)
                            CREATE_IDX_MESSAGE_REF_ORD=CREATE INDEX JBM_MSG_REF_ORD ON JBM_MSG_REF (ORD)
                            CREATE_IDX_MESSAGE_REF_PAGE_ORD=CREATE INDEX JBM_MSG_REF_PAGE_ORD ON JBM_MSG_REF (PAGE_ORD)
                            CREATE_IDX_MESSAGE_REF_MESSAGE_ID=CREATE INDEX JBM_MSG_REF_MESSAGE_ID ON JBM_MSG_REF (MESSAGE_ID)
                            CREATE_IDX_MESSAGE_REF_SCHED_DELIVERY=CREATE INDEX JBM_MSG_REF_SCHED_DELIVERY ON JBM_MSG_REF (SCHED_DELIVERY)
                            CREATE_MESSAGE=CREATE TABLE JBM_MSG (MESSAGE_ID INT8, RELIABLE CHAR(1), EXPIRATION INT8, TIMESTAMP INT8, PRIORITY SMALLINT, TYPE SMALLINT, HEADERS BYTE, PAYLOAD BYTE, PRIMARY KEY (MESSAGE_ID)) LOCK MODE ROW
                            CREATE_TRANSACTION=CREATE TABLE JBM_TX (NODE_ID INTEGER, TRANSACTION_ID INT8, BRANCH_QUAL BYTE, FORMAT_ID INTEGER, GLOBAL_TXID BYTE, PRIMARY KEY (TRANSACTION_ID)) LOCK MODE ROW
                            CREATE_COUNTER=CREATE TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID INT8, PRIMARY KEY(NAME)) LOCK MODE ROW
                            INSERT_DUAL=INSERT INTO JBM_DUAL VALUES (1)
                            CHECK_DUAL=SELECT 1 FROM JBM_DUAL
                            INSERT_MESSAGE_REF=INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                            DELETE_MESSAGE_REF=DELETE FROM JBM_MSG_REF WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
                            UPDATE_MESSAGE_REF=UPDATE JBM_MSG_REF SET TRANSACTION_ID=?, STATE='-' WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
                            UPDATE_PAGE_ORDER=UPDATE JBM_MSG_REF SET PAGE_ORD = ? WHERE MESSAGE_ID=? AND CHANNEL_ID=?
                            COMMIT_MESSAGE_REF1=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='+'
                            COMMIT_MESSAGE_REF2=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='-'
                            ROLLBACK_MESSAGE_REF1=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='+'
                            ROLLBACK_MESSAGE_REF2=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='-'
                            LOAD_PAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, PAGE_ORD, SCHED_DELIVERY FROM JBM_MSG_REF WHERE CHANNEL_ID = ? AND PAGE_ORD BETWEEN ? AND ? ORDER BY PAGE_ORD
                            LOAD_UNPAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? AND PAGE_ORD IS NULL ORDER BY ORD
                            LOAD_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? ORDER BY ORD
                            UPDATE_REFS_NOT_PAGED=UPDATE JBM_MSG_REF SET PAGE_ORD = NULL WHERE PAGE_ORD BETWEEN ? AND ? AND CHANNEL_ID=?
                            SELECT_MIN_MAX_PAGE_ORD=SELECT MIN(PAGE_ORD), MAX(PAGE_ORD) FROM JBM_MSG_REF WHERE CHANNEL_ID = ?
                            SELECT_EXISTS_REF_MESSAGE_ID=SELECT MESSAGE_ID FROM JBM_MSG_REF WHERE MESSAGE_ID = ?
                            UPDATE_DELIVERY_COUNT=UPDATE JBM_MSG_REF SET DELIVERY_COUNT = ? WHERE MESSAGE_ID = ? AND CHANNEL_ID = ?
                            UPDATE_CHANNEL_ID=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ?
                            MOVE_REFERENCE=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE MESSAGE_ID = ? AND CHANNEL_ID = ?
                            LOAD_MESSAGES=SELECT MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, HEADERS, PAYLOAD, TYPE FROM JBM_MSG
                            INSERT_MESSAGE=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                            INSERT_MESSAGE_CONDITIONAL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE) SELECT ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
                            UPDATE_MESSAGE_4CONDITIONAL=UPDATE JBM_MSG SET HEADERS=?, PAYLOAD=? WHERE MESSAGE_ID=?
                            INSERT_MESSAGE_CONDITIONAL_FULL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
                            MESSAGE_ID_COLUMN=MESSAGE_ID
                            DELETE_MESSAGE=DELETE FROM JBM_MSG WHERE MESSAGE_ID = ? AND NOT EXISTS (SELECT JBM_MSG_REF.MESSAGE_ID FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = ?)
                            INSERT_TRANSACTION=INSERT INTO JBM_TX (NODE_ID, TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID) VALUES(?, ?, ?, ?, ?)
                            DELETE_TRANSACTION=DELETE FROM JBM_TX WHERE NODE_ID = ? AND TRANSACTION_ID = ?
                            SELECT_PREPARED_TRANSACTIONS=SELECT TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID FROM JBM_TX WHERE NODE_ID = ?
                            SELECT_MESSAGE_ID_FOR_REF=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '+' ORDER BY ORD
                            SELECT_MESSAGE_ID_FOR_ACK=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '-' ORDER BY ORD
                            UPDATE_COUNTER=UPDATE JBM_COUNTER SET NEXT_ID = ? WHERE NAME=?
                            SELECT_COUNTER=SELECT NEXT_ID FROM JBM_COUNTER WHERE NAME=? FOR UPDATE
                            INSERT_COUNTER=INSERT INTO JBM_COUNTER (NAME, NEXT_ID) VALUES (?, ?)
                            SELECT_ALL_CHANNELS=SELECT DISTINCT(CHANNEL_ID) FROM JBM_MSG_REF
                            UPDATE_TX=UPDATE JBM_TX SET NODE_ID=? WHERE NODE_ID=?
                            ]]>

                            <!-- The maximum number of parameters to include in a prepared statement -->

                            500



                            <!-- Messaging Post Office MBean configuration
                            ========================================= -->

                            <mbean code="org.jboss.messaging.core.jmx.MessagingPostOfficeService"
                            name="jboss.messaging:service=PostOffice"
                            xmbean-dd="xmdesc/MessagingPostOffice-xmbean.xml">

                            <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer

                            jboss.jca:service=DataSourceBinding,name=InformixDS

                            <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager

                            <!-- The name of the post office -->

                            JMS post office

                            <!-- The datasource used by the post office to access it's binding information -->

                            java:/InformixDS

                            <!-- If true will attempt to create tables and indexes on every start-up -->

                            true

                            <![CDATA[
                            CREATE_POSTOFFICE_TABLE=CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(192), NODE_ID INTEGER, QUEUE_NAME VARCHAR(192), COND LVARCHAR(1023), SELECTOR LVARCHAR(1023), CHANNEL_ID INT8, CLUSTERED CHAR(1), ALL_NODES CHAR(1), PRIMARY KEY(POSTOFFICE_NAME, NODE_ID, QUEUE_NAME)) LOCK MODE ROW
                            INSERT_BINDING=INSERT INTO JBM_POSTOFFICE (POSTOFFICE_NAME, NODE_ID, QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                            DELETE_BINDING=DELETE FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=? AND QUEUE_NAME=?
                            LOAD_BINDINGS=SELECT QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=?
                            ]]>

                            <!-- This post office is clustered. If you don't want a clustered post office then set to false -->

                            false




                            <!-- Messaging JMS User Manager MBean config
                            ======================================= -->

                            <mbean code="org.jboss.jms.server.plugin.JDBCJMSUserManagerService"
                            name="jboss.messaging:service=JMSUserManager"
                            xmbean-dd="xmdesc/JMSUserManager-xmbean.xml">
                            jboss.jca:service=DataSourceBinding,name=InformixDS
                            <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager
                            java:/InformixDS
                            true
                            <![CDATA[
                            CREATE_USER_TABLE=CREATE TABLE JBM_USER (USER_ID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USER_ID)) LOCK MODE ROW
                            CREATE_ROLE_TABLE=CREATE TABLE JBM_ROLE (ROLE_ID VARCHAR(32) NOT NULL, USER_ID VARCHAR(32) NOT NULL, PRIMARY KEY(USER_ID, ROLE_ID)) LOCK MODE ROW
                            SELECT_PRECONF_CLIENTID=SELECT CLIENTID FROM JBM_USER WHERE USER_ID=?
                            POPULATE.TABLES.1 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('guest', 'guest')
                            POPULATE.TABLES.2 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('j2ee', 'j2ee')
                            POPULATE.TABLES.3 = INSERT INTO JBM_USER (USER_ID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
                            POPULATE.TABLES.4 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('nobody', 'nobody')
                            POPULATE.TABLES.5 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('dynsub', 'dynsub')
                            POPULATE.TABLES.6 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('guest','guest')
                            POPULATE.TABLES.7 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('j2ee','guest')
                            POPULATE.TABLES.8 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('john','guest')
                            POPULATE.TABLES.9 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('subscriber','john')
                            POPULATE.TABLES.10 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','john')
                            POPULATE.TABLES.11 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','dynsub')
                            POPULATE.TABLES.12 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','john')
                            POPULATE.TABLES.13 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','dynsub')
                            POPULATE.TABLES.14 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('noacc','nobody')
                            ]]>




                            • 11. Re: JBM and informix
                              abendt

                              thanks a lot!

                              • 12. Re: JBM and informix
                                dd_la_frime

                                 

                                "abendt" wrote:
                                thanks a lot!


                                your welcome.