Content Manager Information

[tscaria]

Hi Form users: I need help with this issue below.
What we are trying to accomplish is  for each Cognos reports  we need to get the tables and column names.
The issue is that we don't know what tables is each report is using and we have hundreds of tables in our system.


For example:
Here is the source code of one cognos report.

select distinct "CMC_CLCL_CLAIM__XC_"."GRGR_CK" "GRGR_CK", "CMC_CLCL_CLAIM__XC_"."SBSB_CK" "SBSB_CK", "CMC_CLMS_EXT_MISC"."CLCL_ID" "CLCL_ID", "CMC_CLCL_CLAIM__XC_"."CLCL_CL_SUB_TYPE" "CLCL_CL_SUB_TYPE", "CMC_CLCL_CLAIM__XC_"."CLCL_TOT_CHG" "CLCL_TOT_CHG", "CMC_CDML_CL_LINE__XC_"."CDML_FROM_DT" "CDML_FROM_DT", "CMC_CLCL_CLAIM__XC_"."CLCL_RECD_DT" "CLCL_RECD_DT", "CMC_CLCL_CLAIM__XC_"."CSPI_ID" "CSPI_ID", "CMC_CLCL_CLAIM__XC_"."MEME_CK" "MEME_CK", "CMC_CLCL_CLAIM__XC_"."PRPR_ID" "PRPR_ID", "CMC_CDML_CL_LINE__XC_"."CDML_GWID" "CDML_GWID"
from "FACETS_XC"."CMC_CLCL_CLAIM" "CMC_CLCL_CLAIM__XC_", "FACETS_XC"."CMC_CLMS_EXT_MISC" "CMC_CLMS_EXT_MISC", "FACETS_XC"."CMC_CDML_CL_LINE" "CMC_CDML_CL_LINE__XC_"
where "CMC_CLMS_EXT_MISC"."CLMS_TRAD_PARTNER"='ASR' and "CMC_CLCL_CLAIM__XC_"."CLCL_CUR_STS"='15' and "CMC_CDML_CL_LINE__XC_"."CDML_SEQ_NO"=1 and "CMC_CLCL_CLAIM__XC_"."CLCL_CL_SUB_TYPE" in ('H', 'M') and "CMC_CDML_CL_LINE__XC_"."CLCL_ID"="CMC_CLCL_CLAIM__XC_"."CLCL_ID" and "CMC_CLMS_EXT_MISC"."CLCL_ID"="CMC_CLCL_CLAIM__XC_"."CLCL_ID")


We need list like this below.

Table                                             Column
======                                           ============
CMC_CLCL_CLAIM__XC                       GRGR_CK
CMC_CLCL_CLAIM__XC                    SBSB_CK
CMC_CLMS_EXT_MISC                    CLCL_ID
CMC_CLCL_CLAIM__XC                       CLCL_CL_SUB_TYPE
CMC_CLCL_CLAIM__XC                       CLCL_RECD_DT
CMC_CLCL_CLAIM__XC                    CSPI_ID
   

Thank you for  your time and help!
Appreciate it greatly   
   

[chris.chapman]

Hi tscaria

Avnet BSP Software's MetaManager can definitely help you create this sort of documentation. You can use a combination of the Model Documenter and Report Documenter modules.

The Model Documenter module will allow you to document out all the tables used by a particular model.

You can then use the Report Documenter module to document out the specific package, query subjects, and query items a report is using.

These two documents should then give you the complete picture you are looking for. More information about MetaManager can be found here: http://www.bspsoftware.com/products/metamanager/

If you would like a demonstration of the modules described above please use the Contact Us link and we can set up a call for you.

Good luck!
Chris

[TheBrenda]

The SDK can retrieve the SQL. You will then have to parse the sql to get a list of tables/views and columns. Or maybe there is a tool, freeware, something already written that can do it for you. But realize that you will probably have column aliases, not table column names. and going from the alias to the table name is impossible.

simple view creation example
create view view1 select field1 as fieldxx, field2 fieldyy from table1, table2 
create view view2 select r.field3 as fieldaa, s.field3 fieldbb from table1 r , table2 s

now inside framework manager you use view1
select fieldxx, fieldyy from view1

what the heck are the underlying tables and columns for fieldxx, fieldyy, fieldaa, and fieldbb? as far as I know, there is no way to find out. And I have looked and looked.

SDK Sample to retrieve the Query Item for each Data item from a report spec
http://www.ibm.com/support/docview.wss?uid=swg21373042

SDK Sample to extract the SQL statement from a report
http://www.ibm.com/support/docview.wss?uid=swg21344260

[akhanchandani1]

Hello,

It seems like IBM has removed the URL's

http://www.ibm.com/support/docview.wss?uid=swg21373042
and
http://www.ibm.com/support/docview.wss?uid=swg21344260

Can some one please help with the sample code, I am specifically looking for extracting SQL queries from Report.

Thanks

[cogdoc]

The new URLs are:

• SDK Sample to retrieve the Query Item for each Data item from a report spec
  https://supportcontent.ibm.com/support/pages/node/125213
• SDK Sample to extract the SQL statement from a report
  https://supportcontent.ibm.com/support/pages/node/371829
  

   

[dougp]

This appears to be a big secret.  My IBM Support logon won't allow me access to the content.

[MFGF]

This appears to be a big secret.  My IBM Support logon won't allow me access to the content.

Same here. I see a message saying:

Only IBM employees and IBM Business Partners accounts permitted. You can sign out your current IBMID and try another valid one again.

As an IBM business partner I'd assume I should be able to see the content, but it appears not.

MF.