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
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
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
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
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 (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 (https://supportcontent.ibm.com/support/pages/node/371829)
This appears to be a big secret. My IBM Support logon won't allow me access to the content.
Quote from: dougp on 09 Sep 2020 12:10:29 PM
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.