I am having a hard time getting the application listing to feed into a variable using nested loops, and the for each application get a variable for any masters in there, and then refreshing all.


I am trying to write a FEX to refresh every synonym, at least. Ideally, examine each synonym and determine if it needs to be updated/refreshed, possibly through a SQL pass-thru call.

I have been looking on Focal Point at

http://forums.informationbuild...1057331/m/4231036813

http://forums.informationbuild...1057331/m/4231036813

http://forums.informationbuild...1057331/m/2271022192

and

http://documentation.informati...xwin_523/04meta3.htm

without success. Can you help?

This is what I want, in pseudo-Fex

APP LIST HOLD
-RUN
TABLE FILE FOCAPPL
PRINT APPNAME
ON TABLE HOLD AS APPNAME FORMAT ALPHA
END
-RUN

-* Read each Application into a variable (outer nested loop)
-READ APPNAME NOCLOSE &APPNAME;
-REPEAT OUTERLOOP WHILE &IORETURN EQ 0;

-* Read each Master into a variable (inner nested loop)
APP QUERY &APPNAME HOLD

TABLE FILE FOCAPPQ
PRINT *
-* Get Name of Synonym to refresh
BY FILENAME
WHERE FILENAME LIKE '%.mas'
ON TABLE HOLD AS MASTERNAME FORMAT ALPHA
END

-READ FILENAME NOCLOSE &FILENAME;
-REPEAT INNERLOOP WHILE &IORETURN EQ 0;
-*Possibly Execute SQL pass-thru here to determine if REFRESH is required
-REFRESH SYNONYM &FILENAME
-INNERLOOP


-READ APPNAME NOCLOSE &APPNAME ;
-OUTERLOOP

This message has been edited. Last edited by: TOM SCHULTE,

Tom,

You were missing a READ for the innerloop.

Here's a two-loop process that works - very similar to yours. I've started using -READFILE - you don't specify the variable lengths and NOCLOSE is not necessary.

-SET &ECHO=ON;

SET MSG=OFF
-RUN


APP LIST HOLD
-RUN

-* Read each Application into a variable (outer nested loop)
-*-READ FOCAPPL NOCLOSE &ADATE.A10. &ATIME.A8. &APPNAME.A64.
-READFILE FOCAPPL
-RUN

-REPEAT OUTERLOOP WHILE &IORETURN EQ 0;

-TYPE APPLICATION TO PROCESS: &APPNAME

-* Read each Master into a variable (inner nested loop)
APP QUERY '&APPNAME' HOLD
-RUN

-* Get Name of Synonym to refresh
TABLE FILE FOCAPPQ
PRINT
FILENAME
BY FILENAME NOPRINT
WHERE FILENAME LIKE '%.mas'
ON TABLE HOLD AS HMASTER FORMAT ALPHA
END
-RUN

-*-READ HMASTER NOCLOSE &FILENAME.A70.
-READFILE HMASTER

-REPEAT INNERLOOP WHILE &IORETURN EQ 0;

-*-- Create metadata name and database table name --------------------
-TYPE   MASTER TO PROCESS: &FILENAME
-*-SET &TAB_NAME = TRUNCATE(&TABSCHEMA) || '.' || TRUNCATE(&TABNAME);
-*-SET &SYN_NAME = &CONNECTION_NAME || '_' || TRUNCATE(&TABSCHEMA) || '_' || TRUNCATE(&TABNAME);
-*
-*CREATE SYNONYM &APPNAME/&SYN_NAME FOR &TAB_NAME DBMS DB2 AT CONN_NAME DROP
-RUN

-*-READ HMASTER NOCLOSE &FILENAME.A70.
-READFILE HMASTER
-INNERLOOP

-*-READ FOCAPPL NOCLOSE &ADATE.A10. &ATIME.A8. &APPNAME.A64.
-READFILE FOCAPPL
-OUTERLOOP

Using your sample code. Also I don't recommend refreshing all of the master files. Some master files may have been manually edited by other developers. Only refresh the masters that need to be refreshed. See the WHERE clause in the first table request.

  
SET ASNAMES=ON
SET HOLDLIST=PRINTONLY

APP LIST HOLD
-RUN

TABLE FILE FOCAPPL
PRINT
     COMPUTE APP_ID/I8 = APP_ID + 1;
     APPNAME
-*WHERE APPNAME EQ 'ibisamp';
ON TABLE HOLD AS HAPPFLDR
END
-RUN
-SET &FLDR_CNT = &LINES;

-REPEAT APP_LOOP FOR &N FROM 1 TO &FLDR_CNT STEP 1

TABLE FILE HAPPFLDR
PRINT
     APPNAME
WHERE APP_ID EQ &N;
ON TABLE SAVE AS HAPP
END
-RUN
-READ HAPP &APPNAME.A64.
-RUN


APP QUERY &APPNAME HOLD
-RUN

TABLE FILE FOCAPPQ
PRINT
    COMPUTE MAS_ID/I8 = MAS_ID + 1;
    COMPUTE MASFILE/A70 = GETTOK(FILENAME, 70, 1, '.', 70, MASFILE);
	COMPUTE SYNNAME/A250 = '&APPNAME' || '/' || MASFILE;
BY FILENAME NOPRINT
WHERE FILENAME LIKE '%.mas'
ON TABLE HOLD AS HMASNAME
END
-RUN
-SET &MAS_CNT = &LINES;


-REPEAT MAS_LOOP FOR &I FROM 1 TO &MAS_CNT STEP 1

TABLE FILE HMASNAME
PRINT
     SYNNAME
WHERE MAS_ID EQ &I;
ON TABLE SAVE AS HMAS
END
-RUN
-READ HMAS &SYNNAME.A250.
-RUN

REFRESH SYNONYM &SYNNAME
END
-RUN

-MAS_LOOP
-APP_LOOP

Thanks, both of you. I think Francis you may have noticed I started down this path reading a few of your earlier posts on this topic. Mighty Max, you are right, universal synonym refreshing would not be in order considering developer activity but I am working with a specialty WFRS serving up solely synonyms to InfoAssist users. I just need to keep them fresh.

I am now examing the two approaches, both of which look like they will work with appropiate changes, but I have sticking points on each:

MM's doesn't see into the nested applications, it only returns top-level applications...

Francis' sees all the way (I am nested four deep), but each time I run it prompts for values for APPNAME and FILENAME each time, and I ultimately need this to run unattended.

I have made some progress, but am stuck at:

1] I keep getting the parameter prompts, but I need this to eventually run unexecuted.
2] My WHERE not in literal list seems to be ignored

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET MSG=OFF

APP LIST HOLD
-RUN

TABLE FILE FOCAPPL
PRINT APPNAME
BY APPNAME NOPRINT
WHERE NOT APPNAME IN ( 'foccache', 'baseapp', 'ibinccen', 'testodbc', 'plexpoc', 'SQW' );
ON TABLE HOLD AS APPS FORMAT ALPHA
END
-RUN

-* Read each Application into a variable (outer nested loop)
-READFILE APPS
-RUN

-REPEAT OUTERLOOP WHILE &IORETURN EQ 0;

-TYPE APPLICATION TO PROCESS: &APPNAME

-* Read each Master into a variable (inner nested loop)
APP QUERY '&APPNAME' HOLD
-RUN

-* Get Name of Synonym to refresh
TABLE FILE FOCAPPQ
PRINT
COMPUTE MASFILE/A70 = GETTOK(FILENAME, 70, 1, '.', 70, MASFILE);
COMPUTE SYNNAME/A250 = '&APPNAME' || '/' || MASFILE;
BY FILENAME NOPRINT
WHERE FILENAME LIKE '%.mas'
ON TABLE HOLD AS HMASTER FORMAT ALPHA
END
-RUN

-READFILE HMASTER

-REPEAT INNERLOOP WHILE &IORETURN EQ 0;

-*-- Create metadata name and database table name --------------------
-TYPE REFRESH SYNONYM &SYNNAME
-*REFRESH SYNONYM &SYNNAME

-RUN

-READFILE HMASTER
-INNERLOOP

-READFILE FOCAPPL
-OUTERLOOP

Selection is not ignored, but:
-READFILE FOCAPPL? Should be -READFILE APPS...

Parameter prompt, apparently you have that enabled.
And then all &'s will be prompted for which no value has been established.
You can get around that by issuing a -DEFAULTH &varname=' '

Thanks, GamP - that helps.

It definitely is not passing over applications I am excluding in WHERE NOT APPNAME IN ( ... ), which means it is probably something as obvious as what GamP pointed out. Also, I can see Mad Max is supplying some very helpful tips on having the correct syntax to call REFRESH SYNONYM, but with the masters nested 3- or 4-deep, I am thinking I need some way to get the full path to them. Here is the current version that I have:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET MSG=OFF

-DEFAULTH &SYNNAME=''
-DEFAULTH &APPNAME=''

APP LIST HOLD
-RUN

TABLE FILE FOCAPPL
PRINT APPNAME
BY APPNAME NOPRINT
-*WHERE NOT APPNAME IN ( 'foccache', 'baseapp', 'ibinccen', 'testodbc', 'plexpoc', 'SQW' )
WHERE APPNAME NE 'SQW' OR 'ibisamp''
ON TABLE HOLD AS APPS FORMAT ALPHA
END
-RUN

-* Read each Application into a variable (outer nested loop)
-READFILE APPS
-RUN

-REPEAT OUTERLOOP WHILE &IORETURN EQ 0;

-TYPE APPLICATION TO PROCESS: &APPNAME

-* Read each Master into a variable (inner nested loop)
APP QUERY '&APPNAME' HOLD
-RUN

-* Get Name of Synonym to refresh
TABLE FILE FOCAPPQ
PRINT
COMPUTE MASFILE/A70 = GETTOK(FILENAME, 70, 1, '.', 70, MASFILE);
COMPUTE SYNNAME/A250 = '&APPNAME' || '/' || MASFILE;
BY FILENAME NOPRINT
WHERE FILENAME LIKE '%.mas'
ON TABLE HOLD AS HMASTER FORMAT ALPHA
END
-RUN

-READFILE HMASTER

-REPEAT INNERLOOP WHILE &IORETURN EQ 0;

-*-- Create metadata name and database table name --------------------
-TYPE REFRESH SYNONYM &SYNNAME
-*REFRESH SYNONYM &SYNNAME

-RUN

-READFILE HMASTER
-INNERLOOP

-READFILE APPS
-OUTERLOOP

I see there is a case sensitivity issue in what I was excluding, i.e. 'SQW' had to be replaced with 'sqw'

sheesh

Also, ideally, I'd like to really only iterate through active applications in the search path: the output of APP SHOWPATH. (In the WHERE NOT APPNAME IN ... is just my way to exclud einactive applications, shouldn't there be a better way?)

and then (and what I really need) is to "walk the application tree" (thinking of it like a folder hierarchy) down to the lowest level of nesting. With APP LIST, I am only seeing applications in APP ROOT, not their "sub-applications"

I have been searching Focal Point and Info Response looking for getting into the nested applications, which is critical to my task. Automatically focusing only in active applications would be a nicety and be more efficient.

I don't think the nested applications are reachable with the APP LIST ... APP QUERY. I enabled traces and browsed the application tree on the WFRS and from seeing the generated FEX, I came up with this approach, which looks like it will work for me:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET MSG=OFF

-DEFAULTH &SYNNAME=''
-DEFAULTH &APPNAME=''

TABLEF FILE _EDAHOME/CATALOG/SYSAPPS
PRINT -*APPFULL FNAME
COMPUTE SYNNAME/A1250 = APPFULL || '/' || FNAME;
-*APPNAME FLIST APPTYPE APPNEST APPEXST FTYPNAM TABNAME
WHERE CONNECT EQ 'odbc.plexonline.com';
WHERE NOT APPNAME IN ( 'ibisamp', 'foccache', 'baseapp', 'ibinccen', 'testodbc', 'plexpoc', 'sqw', 'odbcinsql' );
WHERE APPFULL NOT LIKE 'odbcinsql%';
WHERE FEXT EQ 'mas';
ON TABLE HOLD AS HMASTER FORMAT ALPHA
END
-RUN
-*EXIT

-READFILE HMASTER

-REPEAT INNERLOOP WHILE &IORETURN EQ 0;

-*-- Create metadata name and database table name --------------------
-TYPE REFRESH SYNONYM &SYNNAME
REFRESH SYNONYM &SYNNAME

-RUN

-READFILE HMASTER
-INNERLOOP