Stopping DB queries via BO

[davideart]

Hi everybody.
We have a BO system based on a ZABO architecture that access an IBM DB2 database.

We would like that queries launched against the DB could be managed in order to avoid queries that last too long.
What we look for is to re-create the behaviour of pressing the ESC key during query execution.

What we would like to achieve is one of the followin':
1) DB query stop after x minutes - universe and connection parameters don't seem to be useful for this;
2) everytime an user create a query with an universe, a certain macro is automatically added to the report, so that we could program the macro to send an ESC signal after x minutes;
3)Any other ideas...

Waiting for kind replies, thanks in advance

Bye, David

 

[blom0344]

Hello David,

It is standard fare to set the maximum query time of a applied universe (as a dataprovider) to a maximum number of rows retrieved or max query time. I take it that you do not use a universe dataprovider or does the max query time not function? (Or do you want each query to have a specific run-time ??)
I does not make much sense to me. I observe quite a bit of variation in query-time to start with (I use DB2 too as a source), so why the restriction. You want output , as a result of the query I imagine.
Are you not trying to rebuild standard functionality?
T. Blom
Information analyst
tbl@shimano-eu.com

 

[davideart]

Hi Blom
First of all i really want to thank you for your quick and precise answer.

You are right, maybe I did not explain very well.
I do use universe as dataprovider.
What I'd like is that DB2 queries (executed via BO, of course) stopped running on the server after x minutes: my BO Online Customer Support technician confirmed that parameters in designer/universe parameters/control limit the time (or the rows) that data is sent to BO, but does NOT stop the process on the database.

But i think that should be possible to stop the process on the DB, because it is what happens when you press the ESC key...i just need to automatize it...

I really hope I have been clear and that this can help you helping me...
Thanks again and bye!
Dave

 

[Benouche]

Davideart,

I confirm the limits you give at the Universe level do stop the process (time and row number limit).

The result displayed is then uncomplete as marked by a flag at the bottom of window...

Ciao,
Benouche

 

[blom0344]

Hello David,

I suppose the partial result handling as described by Benouche is familiar stuff. I wonder if you ever checked whether query keeps running at database level within DB2.
I think it is not very wise to take everything online help says for granted (at least that is my experience........)
I imagine you can check connections through control panel to see if the query session is ended.... T. Blom
Information analyst
tbl@shimano-eu.com

 

[davideart]

Hi Blom,

You are right for what you say about online help, but yes, I checked (with Omegamon tool) if queries keep running on the server and yes, the damned ones do!
The only way to stop them (before they are actually finished) is to press ESC key; no other parameter seems to work.

Thanks again,
Davide

 

[blom0344]

Hello David,

I'm out of ideas for the moment, but not out of resources.
I was expecting that by this time some of the top dogs would be kicking in , but try your luck with another forum with real wizards like ..............................
(no names, the ones I leave out would really mind -

http://www.forumtopics.com/busobj/

T. Blom
Information analyst
tbl@shimano-eu.com

 

[MedInfoMan]

There's an awful lot I do not understand about BO, and I don't have access to the developer components, but in our set up, the ESC key doesn't seem to do anything until the query has actually started to return results from the server. If the query on the server side is still running, hitting the escape key does not terminate the query. If the query is complete and the data is being returned, hitting the escape key will allow you to abort the "download" of the returning data that is in progress.

Also in our set up, if a query is taking way to long to execute, aborting the business objects session allows you to relaunch a session, but the query, if it hasn't finished is still running on the server. Only our administrator can actually kill the SQL quueryon the server side.

I don't know how universal the characteristics are that I described above, but thought it might be useful for readers of this thread.

Wish you the best.

 

[blom0344]

Wait a second.....

There is an issue with the connection setting of the used universe. Setting it to Synchroneous should allow you to use the Escape button during the course of a query. I am not at work now, but I wonder if it would do something at the database level as well....
Worth checking out I guess.... T. Blom
Information analyst
tbl@shimano-eu.com

 

[davideart]

Hi

I have checked with our DB2 administrator and, after a long quest, we found out that a feature exsist on DB2, which allows you to limit the duration of a query choosing different values per user and per LUName.
Of course, your DBA must set this value for you.
This, apparently, should solve our problem.
We are now going to try this solution, and I'll give you a feedback as soon as possible.

Thanks to you all for support and discussion

 

[davideart]

As promised, here comes the feedback for this solution.

It seems to work correctly: after having chosen how long you want your queries to last and having correctly set up your DB2 database, everytime a query exceed this time BO user get an error message, the query on the DB is stopped and the thread is freed.

The only thing is now about the error received: it is a richly described error (DA0003), that will probably astonish users (and that will probably raise hundreads of phone calls for you!).

Bye and thanks to you all.
David

 

[blom0344]

Hello David,

Just for the record.... On which set-up was your attempt succesfull. DB2 on mainframe or DB2 UDB on .........
Just making sure before I start setting it up for myself.
Thanks again for sharing the techdata......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[davideart]

Hi Blom
Our setup is DB2 v5 for OS/390, so it is a maniframe environment.

Hope this helps, bye