 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Table indexes [solved] | Login/Join |
| Silver Member |
I tried searching through the forum but have not been able to find much of anything of what I am looking for. Has anyone built or know a way, besides being an admin, to find out which fields are key fields and what indexes exist on a given table? I know I can see a key field in the report assistant in the MRE, but I cannot see the indexes and usually have to call up my system admin every time to find out. It would be really nice to be able to pull this info by running a fex and imputing a table name.This message has been edited. Last edited by: j42p11, WebFocus 7.7.03 - 7703_hotfix - Gen: 284 Dev Studio 7.7.03 - Gen: 06062011 Mainframe Focus 7.7.03 Windows WF Client Server zOS Reporting server (hub-sub config) Windows Reporting server Windows RC Client Server | ||
|
| Expert |
I assume these are relational tables ? Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Silver Member |
Waz - yes, they are relational tables, however my company has chosen to not do any data governance, which makes things very difficult on the report developers. Majority of our tables are DB2, SQL, & Fix format flat files. In what I am after, is finding the keys & indexes on the DB2 & SQL tables. This way when I am joining tables together, I can join on the keys/indexes to keep performance optimal. WebFocus 7.7.03 - 7703_hotfix - Gen: 284 Dev Studio 7.7.03 - Gen: 06062011 Mainframe Focus 7.7.03 Windows WF Client Server zOS Reporting server (hub-sub config) Windows Reporting server Windows RC Client Server | |||
|
| Expert |
You don't have access to DB2 through some sort of management console ?. You could easily get the indexes there. Therewise a quick google search found this to show the inedexes on a specified table name. (Untested) SELECT INDNAME, DEFINER, TABSCHEMA, TABNAME, COLNAMES, COLCOUNT, UNIQUERULE, INDEXTYPE FROM SYSCAT.INDEXES WHERE TABSCHEMA NOT LIKE 'SYS%' AND TABNAME = 'TABLE_NAME' Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Silver Member |
No, my IT department has the DB2 console tool but they do not provide it to the business users who actually are developing the reports. I tried writing SQL in WebFocus using that code that you found, but it did not find the SYSCAT.INDEXES table for some reason. I did find SYSCOLUMN, which got me part way to the info that I am looking for. However, it only seems to provide information that is listed on the master. I guess I can try to put in a request to get access to the management console and see if they will change their minds about allowing access to it. WebFocus 7.7.03 - 7703_hotfix - Gen: 284 Dev Studio 7.7.03 - Gen: 06062011 Mainframe Focus 7.7.03 Windows WF Client Server zOS Reporting server (hub-sub config) Windows Reporting server Windows RC Client Server | |||
|
| Master |
I used the FOCUS DB2 read/write interface, at my previous workplace, so going by memory.. As I recall, there is an IB MFD called 'DB2CAT'. DB2CAT describes the DB2 system catalogue tables. I imagine the MFD would be install dependent, but you should see it, in your master library, as I seem to remember the AUTODB2 focexec uses it. Your TABLE command would print out the columns naming/describing the indexes, and you would filter on things like tablename. I support you, and your fellow developers, wanting to understand the table structure, keys, indexes, etc. If you can read the DB2 catalogue, through the IB MFD, you should be closer to where you want to go.
| ||||
|
| Expert |
j42p11, perhaps you need to set the default database connection (the WF DB adapter connection). Here's a working example: SET PAGE=NOLEAD SET LINES=999999 SET HTMLCSS=ON SET BYDISPLAY=ON -RUN SET SQLENGINE=DB2 -RUN ENGINE DB2 SET DEFAULT_CONNECTION CCM -RUN SQL SELECT INDNAME, DEFINER, TABSCHEMA, TABNAME, COLNAMES, COLCOUNT, UNIQUERULE, INDEXTYPE FROM SYSCAT.INDEXES WHERE -*TABSCHEMA NOT LIKE 'SYS%' -* AND TABNAME = 'VWTRANSACTION' TABSCHEMA = 'SRS' FOR FETCH ONLY; TABLE HOLD AS H001 FORMAT ALPHA END -RUN TABLE FILE H001 PRINT UNIQUERULE INDEXTYPE COLCOUNT COLNAMES INDNAME DEFINER BY TABSCHEMA BY TABNAME ON TABNAME SUBHEAD " " ON TABLE SET STYLE * TYPE=REPORT, SQUEEZE=ON, FONT='ARIAL', SIZE=8, BORDER=1, BORDER-COLOR=SILVER, $ ENDSTYLE END -RUN Francis  Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Expert |
I would suggest that you tell your IT dep that to created these reports in FOCUS or SQL you need to know the DB schema, the key and the indexes, its a must. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Silver Member |
I agree Waz. Thanks for your help. WebFocus 7.7.03 - 7703_hotfix - Gen: 284 Dev Studio 7.7.03 - Gen: 06062011 Mainframe Focus 7.7.03 Windows WF Client Server zOS Reporting server (hub-sub config) Windows Reporting server Windows RC Client Server | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |