In a message dated 98-08-07 16:59:11 EDT, you write:
Does anyone know a way to capture the SQL when a query is submitted to
DAS, or force an optimize hint to the SQL, possibly for certian user id’s?
Unfortunately, 4.x DAS does not work the same way as the 3.1 batch feature. Under DAS, the SQL is not submitted - the document is. And since you can’t find the SQL in the document, there is no way to edit or otherwise process it prior to running the schedule. (There may be a way with scripting, but I am not familiar with all of the scripting features.)
This is a major departure from the way batch used to work. Under 3.1 batch processing, only the SQL was sent to the server. It required a UNIX server to process the scripts, so other platforms were not supported. (Although I imagine you could work up a substitute.) The data was captured from the SQL (via a CREATE TABLE AS… statement) and stored in a temporary database table. Importing the results simply was a matter of selecting rows from the temporary table and downloading them into the report on the client workstation.
Now to Document Agent… instead of sending SQL you send the complete document. There is a server configured with DAS… which is essentially a big schedule monitor (like cron). When a document is ready to run, DAS spawns a copy of the complete client BusinessObjects software. In other words, instead of just getting the data (like batch) the entire report is refreshed. It’s as if you hired a person to sit at a console and click on the Refresh button at scheduled intervals and then do a Send To User operation when the report finished. That’s it.
One side problem is this: under 3.1 Macro Objects could be processed by the Batch system. Since the Macro Object was evaluated by the client and just the SQL code sent to the batch processor, there were no problems. Using User Defined Objects under 4.x this is no longer possible. When the report is sent to DAS, unless the UDO file is already present on that server the report will fail.
The bottom line is this: to optimize your SQL, you will need to evaluate tricks that can be done by the universe Designer. Or, you could learn how to update the SQL using the scripting language and send the document out with a custom script. Good luck with either method that you choose!
Regards,
Dave Rathbun
Integra Solutions
www.islink.com See you in Orlando in… a few weeks!
Listserv Archives (BOB member since 2002-06-25)