Author Topic: Content Manager Information  (Read 1619 times)

Offline tscaria

  • Associate
  • **
  • Join Date: Jan 2016
  • Posts: 1
  • Forum Citizenship: +0/-0
Content Manager Information
« on: 29 Jan 2016 11:27:19 am »
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   
    

Offline chris.chapman

  • Associate
  • **
  • Join Date: Jul 2015
  • Posts: 4
  • Forum Citizenship: +0/-0
Re: Content Manager Information
« Reply #1 on: 01 Feb 2016 04:57:28 pm »
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

Offline TheBrenda

  • Senior Member
  • ****
  • Join Date: Jul 2013
  • Posts: 72
  • Forum Citizenship: +0/-0
Re: Content Manager Information
« Reply #2 on: 27 Apr 2016 08:58:30 am »
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

 


       
Twittear