Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Record Count on Tables [SOLVED]
Go
New
Search
Notify
Tools
Reply
  
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, 2009Reply With QuoteReport 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.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6072 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport 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: 1932 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport 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, 2009Reply With QuoteReport 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.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6072 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport 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, 2009Reply With QuoteReport 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.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6072 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport 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, 2009Reply With QuoteReport 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...


   See you at Summit! In FOCUS Since 1983 ~ from PC FOCUS to WebFOCUS.
   Currrently using 8204 Gen 48 in Test and Production.
   KGPCo at CenturyLink. October 2017 to Present...
 
Posts: 2967 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

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

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.