![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Silver Member |
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 countThis 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 |
What sort of files are they ? .FOC, .FTM, something else ? Waz...
| |||||||||||||||||||||||||||
|
Virtuoso |
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 | |||
|
Silver Member |
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 | |||
|
Expert |
Assuming the stats are up to date, you could try to query the DB for the number of rows. Waz...
| |||||||||||||||||||||||||||
|
Silver Member |
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 | |||
|
Expert |
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...
| |||||||||||||||||||||||||||
|
Silver Member |
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 | |||
|
Expert |
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 -ENDOFLOOPOf course you can format the output line, -WRITE REC_COUNT NOCLOSE &RECORDS records in &TABLENAME,anyway you desire... | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|