CMS Tuning: ndbqthreads or Number of Requested Database Connections
If you are interested in maximizing the performance of your system please review this article and also its companion: CMS Tuning: maxobjectsincache and MaximumObjectsToKeepInMemory.
Observing/Measuring Tuning Impacts
The parameter discussed in this article requires more tuning and closer observation than other CMS Tuning technique may require. I highly suggest that you do a load test and that you have a serious discussion with your DBA on tuning this parameter.
CMS Database Connections, a.k.a. ndbqthreads
The number of requested database connections is another place you might improve performance. This setting is one that will only improve performance at times of high load on the CMS. It prevents your CMS’s number of connections to its database (a.k.a. CMS InfoStore, metadata repository; don’t confuse this with report data source connections) from becoming a bottleneck to your system. It opens more conduits between the CMS and the CMS’s database. This setting will be limited by the number of connections that your CMS’s database accepts, so be careful not to exceed that number (this is something that a DBA or DBA tools can help determine. This setting is controlled by different parameters depending on your version of Business Objects Enterprise:
BO XI 3.1: System Database Connections Requested (CMC Property)
In Business Objects XI 3.1 you will find in the properties of the CMS (CMC > Servers > Your_CMS > Properties) a parameter/setting named “System Database Connections Requested“. This setting is set to “14″ by default. I have heard that 99 might be the maximum, but your database’s configuration will probably restrict you before you reach 99.
A Quick CMS DB Thread Sizing Calculation
A recommended baseline is to have one connection for every 20 expected peak concurrent users. I am not speaking of the “Concurrent User” versus “Named User” user type, but I am speaking of the max number of users that you expect to access the system concurrently at any one point in time. Take this number and divide it by 20. Now take that number a divide it by the number of CMS that your cluster is running. The final number is the value that you should put in the “System Database Connections Requested” parameter value of each CMS in the cluster. This a good baseline for you to begin testing (testing this one can be elusive and will certainly require DBA skills to detect the activity on all open connections). If the per CMS number you calculated is less than the default 14 then you could reduce this setting and test for any effects; you consume less database connections which other applications or processes might appreciate.
XI R2: ndbqthreads (CMS Command-Line Parameter)
In XIR2 there is this thing called NDBQ threads. According to the Administrator’s Guide the setting exists in BO XI 3.1 as well (although I don’t understand how it plays/conflicts with the “System Database Connections Requested” setting there). The setting is made in the CCM on the command line for the CMS. That parameter is specifically “-ndbqthreads #“. I believe that “2″ is the default setting and I know that “10″ is the maximum (even in XI 3.1 go figure). This can be sized with the same calculation given above, but your limit will still be 10. Given this limit, some Business Objects administrators will choose to add a CMS to the cluster just to be able to add more CMS DB Connections to the cluster. It is possible that the limit of “10″ does not apply to Business Objects XI 3.1 as the Administrator’s Guide states. It certainly would not be the first error/omission in the guide.
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
