Focal Point Banner


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
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Record Count on Tables [SOLVED]

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Record Count on Tables [SOLVED]
 Login/Join
 
Silver Member
posted
I have a list of 2500+ table files from an application in a WF domain that I need to go through. Several of the tables do not have any records on them. Is there a quick way to use the SYSFILES/SYSTABLE retrieval method and get a record/line count to be able to seperate the tables that do and do not have data on them?

Example: table name record count

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
 
Posts: 30 | Location: Portland, Oregon, USA | Registered: September 11, 2009Report This Post
Expert
posted Hide Post
What sort of files are they ?

.FOC, .FTM, something else ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
SYSFILES will give you a list of all the MASTER files in the search path.
From this you can isolate the files from your specific application (if you mean that "application" is a WF application.
If these file don't need a FILEDEF then you can SAVE the names of the files and use DM to loop through the names, issue a COUNT * for each, check whether any records were retrieved and print out a list.
If a FILEDEF is necessary, then you will have to specify it.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Silver Member
posted Hide Post
Waz - The table types are SQLMSS with MAS extension.

Danny-SRL - When I said application, I was refering to a completely seperate program that the tables are from. I can isolate them as the location on the SYSTABLE is specific to those files. I haven't ever done a DM loop reading records out of a file. I tried searching the form to get some sample loop code but haven't really found much. How would I go about doing something like that?


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
 
Posts: 30 | Location: Portland, Oregon, USA | Registered: September 11, 2009Report This Post
Expert
posted Hide Post
Assuming the stats are up to date, you could try to query the DB for the number of rows.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
That sounds like what I am looking for. How would I do something like that on a mass level? I can do this on a one by one basis but not sure how to set up a looping program to go through them all? Any help is greatly appreciated.


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
 
Posts: 30 | Location: Portland, Oregon, USA | Registered: September 11, 2009Report This Post
Expert
posted Hide Post
Where are probaly a lot of ways to do this.

I did a Google search for SQL server row count, and found this.

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC


They say that it potentially isn't accurate, as it works on the indexes.

How accurate does this need to be ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
So I ended up enlisting the assistance of another report person here at my work. We ended up coming up with the following code. We never were able to get it far enough along to save the data to a file but I was able to do a work around with this to get what I needed.

TABLE FILE SYSTABLE
PRINT * AND COMPUTE
CNT1/I5 = IF CNT1 EQ 0 THEN 1 ELSE CNT1 + 1;
ON TABLE HOLD AS TABLELST
-*WHERE RECORDLIMIT EQ 300
END

-SET &LOOPCNT = &RECORDS + 1;

-SET &CNT = 1;
-SET &WFDESCRIBE = ON;
-RUN

-REPEATLOOP

DEFINE FILE TABLELST
CNT2/I5 = IF CNT2 EQ 0 THEN 1 ELSE CNT2 + 1;
END
TABLE FILE TABLELST
PRINT NAME CNT1 CNT2
BY CNT1 NOPRINT
WHERE CNT1 EQ &CNT
ON TABLE HOLD AS LSTOTBLS FORMAT ALPHA
WHERE RECORDLIMIT EQ 1
END
-RUN

-READ LSTOTBLS &TABLE.A64.
-RUN

-TYPE TABLE = &TABLE

TABLE FILE &TABLE
PRINT *
ON TABLE HOLD AS NEWTABLE
WHERE RECORDLIMIT EQ 300
END
-RUN
-SET &RCRDCNT = &RECORDS;
-SET &CNT = &CNT + 1;
-SET &TABLECNT = &TABLE || &CNT;
-RUN
-TYPE TABLECNT = &TABLECNT
-TYPE RECORDS = &RCRDCNT
-TYPE &TABLE = &RCRDCNT

-IF &CNT = &LOOPCNT THEN GOTO EXITLOOP ELSE GOTO REPEATLOOP

-EXITLOOP
-EXIT



The work around was copying all the text, pasting it into excel, sort to remove all the lines but (-TYPE &TABLE = &RCRDCNT). Did a text to columns and put the table name next to record count. Since I had so many records I did a max limit of 300 read records per table, so anything that had more than 300 showed as 300. Ended up solving my problem. If anyone else is smart enough to convert the outputs into a file that can be saved into excel, that would make this all the better.


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
 
Posts: 30 | Location: Portland, Oregon, USA | Registered: September 11, 2009Report This Post
Expert
posted Hide Post
Consider this, as another (similar) approach:
FILEDEF REC_COUNT DISK C:\IBI\APPS\FOCALPOINT\REC_COUNT.TXT
-RUN
TABLE FILE SYSTABLE
PRINT NAME
WHERE RECORDLIMIT EQ 10
ON TABLE HOLD AS TABLENAMES FORMAT ALPHA
END
-RUN
-SET &LOOPCNTR = 0 ;
-TYPE *** Counting Records in &RECORDS Tables...
-REPEAT ENDOFLOOP &RECORDS TIMES ;
-READ TABLENAMES NOCLOSE &TABLENAME.A64. 
-SET &TABLENAME=UPCASE(64,&TABLENAME,&TABLENAME) ;
-RUN
TABLE FILE &TABLENAME
PRINT *
ON TABLE HOLD
END
-RUN
-TYPE *** &RECORDS records in &TABLENAME 
-WRITE REC_COUNT NOCLOSE &RECORDS records in &TABLENAME 
-ENDOFLOOP
Of course you can format the output line,
-WRITE REC_COUNT NOCLOSE &RECORDS records in &TABLENAME,
anyway you desire...




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Record Count on Tables [SOLVED]

Copyright © 1996-2020 Information Builders