Can I Connect/Use Oracle 11g Database with Business Objects XI?
The answer is “Yes”, but it depends on which version of BOXI you are running.
Business Objects Enterprise XI Release 2
Any version of BOXI R2 equal to or greater than Business Objects XI R2 Service Pack 5 includes an Oracle 11g driver. However, if you find your instance is still lurking below SP5 you are not without hope. According to Oracle, 10g drivers can successfully be used to connect to Oracle 11g databases. Business Objects does not support using R2 SP4, or lower, to connect to and Oracle 11g database, but that doesn’t mean that you can’t do it. It only means they won’t take any “Customer Messages” on any issues related to such connections, unless the issues can be recreated on database supported for your BO XI service pack level.
Be sure to check your service pack’s “Support Platforms” for the particular supported implementation of Oracle 11g middleware. For example, XI 3.1 SP2 states clearly for Linux/Unix machines running CMS you must install Oracle 10.2 middleware to connect to your Oracle 11.1 database. 
Business Objects Enterprise XI 3.X
Starting with Business Objects XI Release 3.1 Business Objects has included an Oracle 11g driver. Now if you are on XI 3.0 and you can’t jump to 3.1 just yet, you might want to borrow a trick from Administrator’s of XIR2 systems lower than XI R2 SP5 and use an Oracle 10g driver to connect to that cutting edge 11g database. Just don’t expect BO to support you on that venture.
Querying the Busines Objects XI CMS InfoStore Database Tables
The question has come to my mind many times over the years and this month it has come to my inbox.
Thanks to Pluto for inspiring me to write this article.
The question: Is it possible to peer into the Business Objects XI CMS InfoStore (a.k.a. database repository) without Query Builder or the BOXI SDK????
The answer: No
All of my research and experience tells me the following:
Much of the meta data, the useful part especially, is stored in the CMS InfoStore in an encrypted binary format. No one has been able to decrypt this format yet, but I can’t really say that anyone has tried very hard. I certainly haven’t.
Some Things You Can Do with the CMS InfoStore’s Database Tables
The CMS InfoStore is mostly held in the database table named “CMS_INFOOBJECTS5″. Some other less significant data is held in the following tables: CMS_RELATIONS5, CMS_IDNUMBERS5, CMS_ALIASES5, and CMS_VERSION_INFO.
The structure of the CMS_INFOOBJECTS5 table in Oracle is as follows:
OBJECTID INTEGER NOT NULL,
PARENTID INTEGER NOT NULL,
TYPEID INTEGER NOT NULL,
OWNERID INTEGER NOT NULL,
LASTMODIFYTIME VARCHAR2(32 BYTE) NOT NULL,
OBJFLAGS INTEGER NOT NULL,
USERFLAGS INTEGER,
SCHEDULESTATUS INTEGER,
NEXTRUNTIME VARCHAR2(32 BYTE),
ALIASES RAW(255),
CRC VARCHAR2(32 BYTE) NOT NULL,
PROPERTIES BLOB NOT NULL,
SI_GUID VARCHAR2(56 BYTE),
SI_CUID VARCHAR2(56 BYTE),
SI_RUID VARCHAR2(56 BYTE),
SI_INSTANCE_OBJECT INTEGER,
SI_PLUGIN_OBJECT INTEGER,
SI_TABLE INTEGER,
SI_HIDDEN_OBJECT INTEGER,
SI_NAMEDUSER INTEGER,
SI_RECURRING INTEGER,
SI_RUNNABLE_OBJECT INTEGER,
SI_TYPEID_MACHINE INTEGER,
SI_KEYWORD VARCHAR2(255 BYTE),
SI_KEYWORDISTRUNCATED INTEGER,
LOV_KEY VARCHAR2(18 BYTE),
OBJNAME VARCHAR2(255 BYTE),
OBJNAMEISTRUNCATED INTEGER
The “properties”, “aliases”, and “objname” fields are where most of the goodies are to be found and these are the fields that are encrypted or in some way pretty much unusable to us without the SDK or Query Builder.
A New Hope for CMS_INFOOBJECTS5
Don’t completely give up on querying this table directly. There are still quite a few valuable data that can be retrieved from the table. Firstly, you should know that the following fields are indexed and there for with a large CMS InfoStore database you will want to use these for filtering your query:
PARENTID
OWNERID
TYPEID
LASTMODIFYTIME
NEXTRUNTIME
SCHEDULESTATUS
OBJECTID
SI_GUID
SI_CUID
SI_RUID
SI_INSTANCE_OBJECT
SI_PLUGIN_OBJECT
SI_TABLE
SI_HIDDEN_OBJECT
SI_NAMEDUSER
SI_RECURRING
SI_RUNNABLE_OBJECT
SI_TYPEID_MACHINE
SI_KEYWORD
SI_KEYWORDISTRUNCATED
LOV_KEY
OBJNAME (this is encrypted though)
OBJNAMEISTRUNCATED
Not all of these fields will have data, but some will and those that sporadically have data might meet your needs perfectly. I don’t really have a strong background in querying this table directly. So we are going to have to crack this nut together. Hopefully we can share our progress in the comments of this post and through updates from me to this article.
A Sample Use of a Direct Query on the CMS InfoStore
For example, I may want to extract a list of all si_cuid (the most unique identifier of an object) and objectid for objects that were created or modified on January 24th. To do this I would use the following query:
SELECT
objectid,
si_cuid
FROM
CMS_INFOOBJECTS5
WHERE
lastmodifytime LIKE ('2009 01 24%')
If you look to the table structure you will see that the field lastmodifytime is of a character type and therefore it can be queried as presented above.
Why would you want to do this query directly on the database and not in Business Objects’ Query Builder?
Short answer: performance and possibility. What I mean to say is that you can control more precisely the performance of the query you write against the table. In fact, a similar query done through Query Builder might perform so slow that you it would not be possible except if you ran it directly against the database table. The truth is that many valid reasons to directly query the database will still end up with you taking the output of that query and then running it through the SDK or Query Builder to obtain all of the properties corresponding to that output.
What other database tables can I query?
CMS_RELATIONS5 and CMS_VERISON_INFO seem to have the only other bit of useful data. CMS_VERSION_INFO has a single row with the repositories version info; I am not certain that you can detect service pack or fix pack level here, in fact, I think you cannot. The CMS_RELATIONS5 table seems to be interesting, but I have not come up with a useful reason to query it beyond tinkering.
For your reference the CMS_RELATIONS5 table has the following structure in Oracle:
PARENTID INTEGER NOT NULL,
CHILDID INTEGER NOT NULL,
ISMEMBER INTEGER NOT NULL,
ORDINAL INTEGER,
RELATIONSHIPID INTEGER NOT NULL
None of the data appears to be encrypted. It also appears to have almost a 1-to-1 ratio between its record count and the count of the objects in the repository as reported in Business Objects Central Management Console. The value of this table might increase if we were to discover the meaning of different relationshipid values.
Conclusion: Where do we go from here?
I feel that I have only given you enough information to advance your tinkering on the true back-end. Honestly, this is a new frontier for me. I hope to post more information here as I find the time to experiment with directly with the database or if I hear from anyone else that has experience they would like to share. I invite you to share your findings as you play with querying the database. Use BO Query Builder concurrently to help you understand more about what you are seeing. If you find you need some help with Query Builder then I do recommend that you take a look at our Business Objects Query Builder Guide
