Hi all,

We are wanting to implement a way of querying an archive as well as our ‘live’ files if the user has chosen to search through a larger set of data.

My initial thoughts were that I would have to effectively double the length of all our codes, with a Part 2 section referencing the Archive. However this would mean that any change to the code would have to be replicated in the first part and the second part.

So if the user has selected to ‘Search Archive’ we would:

1: Run our big report over the live file and hold the data in a 1st hold file.
2: Run through a copy of the code again, but this time querying the archived file, hold data in 2nd file.
3: Finally append the two files together using a MORE function and output the report.


However, I was wondering if it would be more efficient and cleaner to have some sort of loop and return to the beginning of the code and run through for the second time, but overriding the ‘Live’ file with the ‘Archive’ one? I am not well practised with FILEDEF but I am hoping this could do the job?

So events would now be:
1, Run code over Live file, hold data,
2, Return to top of code, overide Live file to Archive file, run the same code again and hold in 2nd file.
3, Finally append the two files together using a MORE function and output the report.

I have run a quick test to see if I could override a file name to point to a different file, but it doesn’t seem to work. Could someone clarify for me?

This following gives me the ‘Live’ order data.

TABLE FILE ORDERS
PRINT *
END
-RUN

I was hoping that the following FILEDEF would overide the file location and pick up the old data, but still under reference to the Live file:

FILEDEF ORDERS DISK OLD_ORDERS

TABLE FILE ORDERS
PRINT *
END
-RUN

However this still returns the Live data?

Sorry to ramble on and hopefully someone can understand what I am trying to achieve!

Many thanks as always

Mark

What format are the files? If they're FOCUS DB's then FILEDEF will not work, instead, use the USE command:

USE
C:\IBI\APPS\IBISAMP\CAR.FOC AS CAR
C:\IBI\APPS\IBISAMP\CAROLD.FOC AS CAR
END
-RUN

TABLE FILE CAR
PRINT COUNTRY
BY COUNTRY
END
-RUN

I created a copy of the CAR FOCUS DB and called it CAROLD.FOC You don't need a master for CAROLD as long as you use the CAR master for it. The USE command above concatenates the two files and the TABLE FILE reads them as one. This is proved by the result - each country is displayed twice in the report, once form CAR.FOC and once from CAROLD.FOC.

This should simplify your approach if the data is FOCUS DB's.

Theres no path on OLD_ORDERS using your existing FILEDEF you are looking in EDATEMP and unless you created it that session it wont find it.

Why not do something like

  
TABLE FILE ORDERS
PRINT *
WHERE date_field LT &SOMECUTOFF
MORE
FILE OLD_ORDERS
WHERE date_field GE &SOMECUTOFF
END

Then it won't matter if the data is in the live or in the archive file, you will get the full answer set. The master files for ORDERS and OLD_ORDERS should be identical.

Thanks for your inputs guys.

The files are on our AS400. I did consider using a MORE function to just append them together first, but from my understanding this will then loose the multiple indexes we have. The files are also very large (2m records for live and 10m records for Archive) so the MORE function takes a long time.

As the reports are very complex and have multiple queries over the files, I would like to avoid replicating all the code again using the Archive, but to just run through again, but instead of accessing the Live file, swap to the Archie.

I still don't understand the FILEDEF function. Is it used to tell WebFocus where the File location (.acx) is?

If you can bear with me I will quote our actual setup:

Live file:

f5502.acx (location /home/iadmindev/ibi/apps/metadata_de_p/f5502.axc)

SEGNAME=F5502 ,
TABLENAME=FOXBEQDTAH/F5502,
KEYS=0
,$

f5502.mas (location /home/iadmindev/ibi/apps/metadata_de_p/f5502.mas)

FILE=F5502 , SUFFIX=SQL ,$
SEGNAME=F5502 , SEGTYPE=S0 ,$
FIELD=OCR_ORDER_CONTROL_NUMBER,ALIAS=QB@OCR,USAGE=P10,ACTUAL=P5,TITLE='Order Control Number ',$
ETC
ETC
ETC


Archive file:

H5502.acx (location /home/iadmindev/ibi/apps/metadata_de_p/h5502.axc)

SEGNAME=H5502 ,
TABLENAME=FOXBEQDTAH/H5502,
KEYS=0
,$

h5502.mas (location /home/iadmindev/ibi/apps/metadata_de_p/h5502.mas)

FILE=H5502 , SUFFIX=SQL ,$
SEGNAME=H5502 , SEGTYPE=S0 ,$
FIELD=OCR_ORDER_CONTROL_NUMBER,ALIAS=QB@OCR,USAGE=P10,ACTUAL=P5,TITLE='Order Control Number ',$
ETC
ETC
ETC


Can the FILEDEF command override the report to when I reference F5502 it uses H5502?

I have tried many different combinations:

FILEDEF F5502 DISK /home/iadmindev/ibi/apps/metadata_de_p/h5502
FILEDEF F5502 DISK FOXDEPDTAH/H5502

But I always end up reading the Live file (F5502)?

Thanks for your patience all

Mark66,

A FILEDEF is not going to work in your case relative to pointing to the data because your data source is relational. However, I do have an idea but you will have to translate it into AS400 terms for me.

Many years ago I had a customer that had different sets of tables by division. Each devision's tables was in its own data space. So we created an access file library for each set of tables with the insides pointing to the correct data space connection. There was only one master library. Based on the division id, we were able to reallocate the access file library to point the the tables for the requested division. This may have been mainframe FOCUS and not WebFOCUS at the time.

So how to we translate this into WebFOCUS terms? I am assuming this is self-service and we can do something with APP commands. You might have to split up the .mas and the .acx files. Then have a set of .acx files for the live data sources and another set for the archive data sources. For our example, we are up to 3 sets of directories or libraries.

mas_data ==> contains the masters
acx_live ==> contains the access files for the live data
acx_archive ==> contains the access files for the archive data

Now in your program, when you are looking at the live data, you could have a command like this:

APP PREPENDPATH acx_live mas_data

Then you'd do your extract for the live data.

Keeping your loop, you could go back to the top, and you could use amper variables for this, and say

APP PREPENDPATH acx_archive

Then run the same exact code which should now be pointing to the archive data.

WebFOCUS will use the first file that it finds in the path. After the second prepend, your path should look like this:

acx_archive acx_live mas_data

So based on how you do your libraries in AS400 land, you should be able to do something like this and get it to work.

Let us know how it works out for you.

Your files are DBMS tables, so, as Ginny points out, FILEDEF does not work, nor is it needed.

The MORE command is the simplest method to access the two tables in one request, and will not "loose the multiple indexes" you have.

Use the SQL tracing capabilities of WebFOCUS to ensure your request is efficiently written, turn XRETRIEVAL to OFF to verify the code.

I forgot to answer your FILEDEF question. FILEDEF is used to point to the location of the data for non-relational and non-FOCUS files.

Relational files have access files to point to the data location but your application pathing must be proper in order to find the masters. No filedef is needed.

FOCUS files are generally referenced by proper application pathing. However, if you need to reference a FOCUS file in another place, you would use the USE command.

Let me know if you have any further questions on this topic. I spend half my professional life explaining pathing to my developers.

One more thing. In my example above, the access files for both the live and archive data need to be named the same and identical to the master.

Ginny thank you very much! I used that approach and I have got the report working just as I had envisioned.

I created a 2nd metadata folder (application?) using the same naming convention as our normal metadata folder, but with ‘_hist’ appended on the end. This folder contains the .acx files which point to the AS400 library that contains our archived data.

We run through the report once over the standard ‘Live’ data, and then if the user has selected to ‘search archive’ we return to the top and appended ‘_hist’ to the metadata path, using the command APP PREPENDPATH. The report then runs through again, but using the archive files. Finally I append the two data sets together using MORE and we have the report J

Thank you also for you explanation of the FILEDEF command. We do have a process that writes out report runtime stats to a FOCUS file where we do use a FILEDEF command, so I saw this and thought ‘this is what I need to point to my archived files’, not realising that it did not apply to relational files!

Francis, I am surprised to read that a MORE command will not loose the indexes, however I am more than happy to trust your knowledge than mine! So if we append to large files together and place in a HOLD file, are all the indexes rebuilt first? We did a simple test on appending the Live and Archive together (2m + 10m = 12m records) and it took about 5 minutes.

Anyway, thank you all for you help again!

Mark.

Mark,

Using MORE with DB2 tables works and is easy to test.

This is a test program that reads data in two different DB2 tables using MORE:

-*-- Set up SQL tracing ----------------------------------------------
-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL
-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS
-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT
-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT
-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF
-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78
-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

-*-- Turn off data retrieval (for testing purposes) ------------------
SET XRETRIEVAL=OFF
-RUN

-*-- Test the MORE command on two DB2 tables -------------------------
TABLE FILE BASEL_TIME_D
SUM
NO_DAYS_IN_PRD
BY
TIME_DIM_KEY
WHERE TIME_DIM_KEY FROM 50 TO 100
ON TABLE HOLD AS H001
MORE
FILE BSLC_TIME_D
WHERE TIME_DIM_KEY FROM 1 TO 25
END
-RUN

The SQL generated by this request:

AGGREGATION DONE ...
SELECT T1."TIME_DIM_KEY", SUM(T1."NO_DAYS_IN_PRD") FROM
BASEL.TIME_D T1 WHERE (T1."TIME_DIM_KEY" BETWEEN 50 AND 100)
GROUP BY T1."TIME_DIM_KEY" ORDER BY T1."TIME_DIM_KEY" FOR FETCH
ONLY;
...RETRIEVAL KILLED
AGGREGATION DONE ...
SELECT T1."TIME_DIM_KEY", SUM(T1."NO_DAYS_IN_PRD") FROM
BSLC.TIME_D T1 WHERE (T1."TIME_DIM_KEY" BETWEEN 1 AND 25) GROUP
BY T1."TIME_DIM_KEY" ORDER BY T1."TIME_DIM_KEY" FOR FETCH ONLY;
...RETRIEVAL KILLED

There are no SQL translator warnings/errors regarding indexes, just nice "aggregation done" messages - you can't get any more efficient that that. This shows that two individual SQL requests were generated, WebFOCUS merges the data together after retrieval.

I am not sure what you mean by "are all the indexes rebuilt first". If you mean, are the DBMS indexes used in the data retrieval, then yes, they are - the MORE has nothing to do with this. If you're creating a WebFOCUS database as the output file with indexes, then yes the indexes will be built - the MORE has nothing to do with this.

Your "test on appending the Live and Archive together" took about 5 minutes - did you turn SQL tracing on to see if your WebFOCUS request generated efficient SQL?

In my opinion, the approach I suggested is the much simpler method - you do not require prepending app folders and Dialogue Manager looping to rerun WebFOCUS code.

From Mark's original post:

quote:

My initial thoughts were that I would have to effectively double the length of all our codes, with a Part 2 section referencing the Archive. However this would mean that any change to the code would have to be replicated in the first part and the second part.

Francis,

I don't mean to quibble with you but Mark didn't want two copies of the TABLE because of the complexity and maintenance issues. My technique eliminates that problem.

Also, he is MOREing two hold files which are sequential files. Indexes are not relevant to that discussion. I don't know if the 5 minutes Mark mentioned was just putting the two files together or included the retrieval of data from the relational tables.

I would suggest that Mark put the tracing commands you recommended in his code while testing to see if there are any generated errors like join optimization being turned off. I would also add

SET XRETRIEVAL=OFF

and put a -EXIT after the first query then examine the trace by doing a View Source on the html page that comes up. The SET command parses the TABLE request but does not retrieve data.

If errors are generated, then Mark can post them here along with his complex TABLE and we can help him make it more efficient.

No quibbles Ginny, but he did mention that he has two tables, "an archive as well as our ‘live’ files".

I meant he didn't want 2 TABLE requests. I know he had 2 files.

Ginny, doesn't this sound like two TABLE requests?

quote:

APP PREPENDPATH acx_live mas_data

Then you'd do your extract for the live data.

Keeping your loop, you could go back to the top, and you could use amper variables for this, and say

APP PREPENDPATH acx_archive

Then run the same exact code which should now be pointing to the archive data.

There's a loop being done to extract the data twice using masters in different app folders - two TABLE requests.

While using MORE is only one TABLE request:

TABLE FILE BASEL_TIME_D 
SUM NO_DAYS_IN_PRD 
BY TIME_DIM_KEY 
WHERE TIME_DIM_KEY FROM 50 TO 100 
ON TABLE HOLD AS H001 
MORE 
FILE BSLC_TIME_D 
WHERE TIME_DIM_KEY FROM 1 TO 25 
END 
-RUN

Yes, but he doesn't have to code it twice.

Oops. I misinterpreted your post.

The TABLE requests I'm talking about are the ones he is using to extract the data from the relational tables. He then creates the 2 hold files which he then MOREs together.

It is the first set that he doesn't want to code twice.

Hi Ginny, Francis,

Sorry I have been away all day and have only just been able to check the thread. Unfortunately I haven't got the time today to try the test that Francis suggested and I am on holiday next week. However I will try and pick this up after my holiday as I am interested to see if there is a better solution.

In the meantime can you help clarify my understanding of APP PREPENDPATH that I would like to know before I disappear…

We were already using the command to point the user to their correct territories data when they log in (UK, France, Germany). We have different metadata folders for each of these territories and these contain the .acx files that point to the relating AS400 libraries.

metadata_uk
metadata_fr
metadata_de

So we look up the users territory code, place it into &V_ENV and then append to the variable &LANG_PATH, which contains 'metadata'.

-SET &LANG_PATH=&LANG_PATH | '_' | &V_ENV;
APP PREPENDPATH &LANG_PATH

This way the user only ever sees the data belonging to their country.

Now with my new 'archive' code, I am appending '_h' to &LANG_PATH. The program then finds the archive files in the newly added AS400 library. However, it also still finds the other files, that were in the original metadata application below. How does this work? If I have set the application path to 'metadata_uk_h', it still finds the normal non-archived files in 'metadata_uk'? I am not complaining, as this is exactly what I want it to do, but I would like to understand the logic it applies.

Also, I am not sure if I am doing something wrong, but when I try to search Help in Developer Studio for 'APP PREPENDPATH' or variants, I cannot find any documents?

Thanks again

The documentation for the APP commands is in the System Administrator's Guide. There are two, one for the mainframe and one for the others.

APP PREPENDPATH amends the path and puts the new directory in front of the existing directories in the path. That is why you are still seeing the non-archived masters. If this is not a problem, I would leave it the way it is. I know of no easy way to remove a directory from the path right off the top of my head. You'd have to do sleight-of-hand with other APP commands. I'm not sure it is worth the effort. But that is your decision to make.

Have fun on your vacation.