database connection in query service when results has many pages of data

[ricky_ru]

i know dqm is using jdbc to select data and java usually use connection pool to manage each connection. Assuming i have a report with a single query but retreives a large dataset which forces cognos to use paging. the question is when i see the first page of result returned,how cognos deal with the db connection?
1.continue to hold/lock the db conn ?when it will release the conn? is it holding the conn(the cursor) to serve the high affinity request like jump to next page?if so,what is the relationship between affinity setting and db connection?
2.release the db conn immediately?
which one is right? thanks!
the maximum of db conn cognos can use can be set in xqe.provider.xml.

[Francis aka khayman]

just commenting so i can follow this post. sounds interesting

[MFGF]

i know dqm is using jdbc to select data and java usually use connection pool to manage each connection. Assuming i have a report with a single query but retreives a large dataset which forces cognos to use paging. the question is when i see the first page of result returned,how cognos deal with the db connection?
1.continue to hold/lock the db conn ?when it will release the conn? is it holding the conn(the cursor) to serve the high affinity request like jump to next page?if so,what is the relationship between affinity setting and db connection?
2.release the db conn immediately?
which one is right? thanks!
the maximum of db conn cognos can use can be set in xqe.provider.xml.

Hi,

My understanding is that the dynamic query connection pool holds the database connection open until the idle timeout is exceeded - this timeout can be seen and altered in the properties of the query service in the Admin Console. The reason it works like this is that it removes the overhead of establishing the database connection from scratch every time you want to access data (because it can take some time to establish the connection). The connection can be used for both high and low affinity requests - affinity is really more about how a user request is routed to a report server (dispatcher) rather than how a report server opens and manages a connection to a database.

Cheers!

MF.

[ricky_ru]

Dear MFGF,

Thanks for your response.
Actually, I was asking the request or (the query service on behalf of the multiple page request) locks or releases the connection to the pool in that situation. Because the difference will cause the connection can be reused or not for other request  / query.
Here is my conclusion (I tested/verified it):

If the page only contains rows < 20, the connection will be released to the pool after the page returned.
If the page contains multiple pages, when the first page displayed, the connection continues to be hold/locked for the request. So, you click 'next page' , it will be fast and the connection is still locked for this session/request. If you click the 'last page', after the data displayed, the connection will be returned to the pool for reuse. And all the data is in cognos cache because you can still navigate to any page in the entire data set.

How about we close the report(the close button of the tab/browser), the connection will be released to the pool as well.

Based on my testing, cognos is doing very well really.

But I want to go deeper, the reason why cognos does not release the connection to the pool is for secondary request which is the high affinity request.(Cognos know this because the rows>20, right?) .

Now, I have a question, I do not want to continue to lock the connection for the upcoming high affinity request. I want cognos to release the connection to the pool after the requested page has been handled. So,it can be reused by other request. For example, assuming 1000 users run multiple page reports , and most of them just want to check the data in first page and they do not close the page, cognos will create 1000 db connections for this case as no db connection can be shared/reused(each one is locked). If the db connection is only for the requested page,so it is released as soon as the page returned, cognos just needs to create a few connections.

So, my question is how to disable the high affinity functionality to make each request is low affinity ? Or is there any parameter in the URL to tell cognos this request and the upcoming request in this session do not use high affinity?

[MFGF]

So, my question is how to disable the high affinity functionality to make each request is low affinity ? Or is there any parameter in the URL to tell cognos this request and the upcoming request in this session do not use high affinity?

I assume you could do this in the properties of the dispatcher > Settings tab - change the "Number of high affinity connections for the report service during peak period" from its current value to 0 and add the same value to the "Number of low affinity connections for the report service during peak period"

I haven't tried it, but might be worth a go in a non-production environment?

Cheers!

MF.

[ricky_ru]

I assume you could do this in the properties of the dispatcher > Settings tab - change the "Number of high affinity connections for the report service during peak period" from its current value to 0 and add the same value to the "Number of low affinity connections for the report service during peak period"

I haven't tried it, but might be worth a go in a non-production environment?

Cheers!

MF.

.....I had the same idea with you before and tried it . Cognos does not allow it to be value <= 0.
Working on cognos for years, but I have to say my understanding for the affinity connection is still not clear. I am hoping if you can explain it or correct me.

I believe the number of affinity configuration is for each BIBusTKServerMain process.  BIBusTKServerMain  is a Linux process with multiple threads to handle requests.  As I know, the threads for each BIBusTKServerMain is about 26, so, in theory, it can handle 26 requests concurrently. 

With the default affinity setting, high is 2, low is 8(assume in CA 11) for each BIBusTKServerMain. What I do not understanding is why we need to set the low affinity numbers.Because I think it is only needed to set high affinity numbers to let the process to keep the context. why set the low affinity? is there anything to keep in server side for low affinity? If not, the process with its threads can handle as much as low affinity as they can....

[the6campbells]

Database connection pooling is performed by both query engines (C++/CQM and Java/DQM).

With CQM, each ReportServer has it's own in-process query engine and associated query pool. Meanwhile, with DQM the ReportServers will interact with the multi-threaded query service.

When a 'request' is processed, the connection pool is inspected for an available idle connection and returned as soon as the 'request' no longer requires that it use the connection.

All of the above is independent of how request affinity is used to route primary/seconds requests via dispatchers to processes.

Do not alter the XQE file you referenced.

The peak # of database connections in an environment will be driven by several factors
- does each user have to use distinct authentication credentials (ie. signon, tickets etc) vs several business users logically share the same authentication info
- does a vendor only support one active query/resultset on a database connection which may force N connections to be used to allow several queries to execute at the same time
- how may active 'requests' for data are triggered by the "report"/dashboards etc interacting with the query engine
- which 'requests' were served by local result set cache vs had to go back to source
....

[ricky_ru]

Thanks the6campbells.
query service is a single java process and it is multi-threaded.
CQM process is a BIBusTKServerMain process, a single BIBusTKServerMain process is also multi-threaded. The difference is multiple BIBusTKServerMains can not share data/db connections as each maintains a independent CQM query engine.

All of the above is independent of how request affinity is used to route primary/seconds requests via dispatchers to processes.

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_fm.doc/c_dqm_governors.html
See the (DQM) Cursor Mode, which also answered my previous question, cognos can release connection per page which is set in the model

So, how cognos controls db resource / connection may has impact on affinity and sure on performance. My thought is:

1) a request itself(http request)  contains the affinity information(http body), if it is high, it will have the node id(machine) and process id(the process in the machine, here , it should be BIBusTKServerMain not java), right? does anyone check this? I am going to do it.

2) if  [Query Per Page] is set, does that mean the 'next page' request is not a high affinity? Because the sql will be re-run and it does not makes sense the server will keep the previous request context...

3) who keeps the affinity context?  the java dispatcher? the BIBusTKServerMain?

I hope this thread will drive  to make the cognos concepts (especially affinity logic ) clear

[ricky_ru]

If you are use tools to capture http traffic , it is interesting to see how browser sends request and may give me some clue.

just paste a few

SOAPAction: http://www.ibm.com/xmlns/prod/cognos/reportService/201703/.high
bus:conversationContext xsi:type="bus:conversationContext">
               <bus:affinityStrength xsi:type="xs:int">5000</bus:affinityStrength>
               <bus:id xsi:type="xs:string">4lwyG4MlhvwjC99Gv2CqGqhl989wCylwsMlMl8v4</bus:id>
               <bus:nodeID xsi:type="xs:string">2017-11-27-12.51.21.550911</bus:nodeID>
               <bus:processID xsi:type="xs:int">26697</bus:processID>
               <bus:status xsi:type="xs:string">complete</bus:status>
            </bus:conversationContext>

[the6campbells]

The DQM governor you ref. might be used in an environment where

- database is being concurrently written to (OLTP)
- vendors concurrency control system may cause readers to block writers
- users tend to look at data in interactive HTML and may pause for periods of time before closing report etc
- resulting in writers being impeded etc.

In this case, DQM will continue to read the data from the result set on a background thread spooling as required to transient cache.
Again, subject to how a vendor's concurrency control functions etc. this can lessen/eliminate those conflicts.
Similarly, it can force the database to detect an end of resultset condition sooner which allows it to release other resources held for the duration of the result set being open.

Reports which render as PDF, static HTML and Excel will process the data without waiting for user input to page-down etc.