Data Dictionary and Compilation of Reports

[tominboston]

 ???
Does anyone know how to extract the sql from several thousand Cognos Query reports published to Upfront?
I need to compile a list of reports and their sql.

Help!
Much thanks!

[dwood09]

Does Cognos Query support Automation?!  If it does have an object model, then it should be possible to extract these details.  It is certainly possible to traverse the items of upfront.

cheers,
DW

[tominboston]

Man I am stuck on this one ... thanks for the input dwood,
but I don't know how I can get a user friendly view of the datastore/object store ... I can suck out a list of reports from the CQPackage.pkg but the sql is all crapped out .... (eg):
"... N_0aoBETWEEN_0aoNOT_20BETWEEN_0aoIS_0aoIS_20NOT_0an_0a0_0a0_0aClient_20Id_0aarial_2c_20geneva_2c_20helvetica_2c_20sans_2dserif_0a2_0a0_0a8_0accccff_0aarial_2c_20gen "

Any ideas?

(I'll have to buy you a beer if you can figure this out ...)

[dwood09]

Check out the Doco for Cognos Query.  Does it mention anything about macros, object model, automation, batch processing etc?

cheers,
DW

[tominboston]

Nada my friend ... just nada ....  :-[

[dwood09]

I've never used CQ, so am flying in the dark a bit, but can you check out / advise on the following:

* in upfront what details can you get from the CQ items, eg:
    * properties, descriptions, advanced details, file locations, references to definitions etc

* does the sql of CQ items exist in files somewhere on your server, or is it uploaded to a database
* where else can you see the SQL of these items?

cheers,
DW

[tominboston]

very little info about the reports in Upfront.

The sql appears to be saved in a files as a BLOB object - do you know how can I query these?
The info (sql and data) are in the following file formats:

1. querydb.oox (where x is a number) - these appear to be data files
2. rAAAAAzz.chg (where zz are 2 alpha chars) - these appear to be query files

both are not viewable in notepad ....
(crap)

Any ideas?

[Draoued]

Hi ,

Some ideas .

Do you know this link ?? It s showing you the most used queries and the average execution time.
Always good to know which queries are the most used one , isn't it ?

http://MyCognosServer/cognos/cgi-bin/cqcgi.exe/script/history.iws

Then there are 2 options to collect the SQL generated by Cognos Query.

A general one , activating the Trace in cognos Query.
Very disk consuming, and requires a stop/start of Cognos Query, then you need to parse the big file to see all the sql.

A one by one action.
Opening one by one all the queries, and activating the Drill-through option, that will generate an IQD in the server.

[dwood09]

sorry dude, I got nothing   ???

[Draoued]

Hi Dwood09 , Is it me dude ??

Which part you don't get ??

[tominboston]

 :o

Thanks Draoued,  ... crap ...it all seems painful ... little in way of automation.

... I have been able to parse through the _.pkg file (convert to xml 1st) created during a Deployment, and extract the Report Title, description, ... etc. including the Binary for the sql for each report.  I just need to translate this binary and I think we're there!

Cheers