Recreate a logical with a date range that changes

[64waves]

Good afternoon. I wanted to see if there is a way to create a logical over a large physical file that would be able to be created each time with a range that has a certain time frame. My problem: I have a pgm that copies from live to our test system weekly. A couple files have 7 years of records, when I only want to refresh the last month or so. I see the 'range' keyword for DDS, but if this process runs weekly, how to automate creating the file with the type of syntax - range for a certain date field is >= "today - 30 days". Is there a way to code it in the logical DDS that if I delete and recreate just before my process that would have the data I needed. Thanks in advance.

[tomholden]

Re: Recreate a logical with a date range that changes

not without changing the DDS source. you could use an SQL view and dynamically select a date range however...

[64waves]

Re: Recreate a logical with a date range that changes

I was wondering if a Create Table command would work or not. Or probably a create index in this case. I will look into that.

[Viking]

Re: Recreate a logical with a date range that changes

If you have another table with a date that you update each night, then your logical can be a join logical that selects based on comparing the dates in the two physicals that are being joined. Might not be the most efficient way performance-wise, but I know it works because we have a logical file like that here that limits the records to just the last two days. And it doesn't have to be deleted or re-created.

[TedHolt]

Re: Recreate a logical with a date range that changes

When I left the S/36 world and moved into the S/38 world in 1988, IBM had already added OPNQRYF to CPF, so I don't know if the following is true or not.

I am told that before OPNQRYF, people used to create logical-file DDS with placeholders in the select/omit specs. People wrote programs to copy the DDS to a temporary file member, replace the place holders with real values, and create a logical file, which would be deleted after use.

I do the same sort of thing with FTP scripts. I have a general purpose utility that replaces strings in a source physical file member, and I can use the same script template, replacing user profile, password, from-file & to-file tokens, to cover a lot of FTP transfers.

I'm not necessarily recommending this approach in your situation. I only bring it up as a possibility.

[kitvb1]

Re: Recreate a logical with a date range that changes

Is there any reason why you can't use CPYF?

CPYF FROMFILE(LIVELIB/MYFILE) TOFILE(TESTLIB/MYFILE) MBROPT(*REPLACE) 
INCREL((*IF DATEFLD *GE MYDATE))

I do not know if CPYF uses any indexes (under the covers when using any of the filter options) that may speed up this process.

BTW... what we do here is save the data libraries, restore on the test machine, and then CLRPFM most of the larger unneeded files e.g. audit logs.

[TedHolt]

Re: Recreate a logical with a date range that changes

Is there any reason why you can't use CPYF?

CPYF FROMFILE(LIVELIB/MYFILE) TOFILE(TESTLIB/MYFILE) MBROPT(*REPLACE) 
INCREL((*IF DATEFLD *GE MYDATE))

I do not know if CPYF uses any indexes (under the covers when using any of the filter options) that may speed up this process.

AFAIK, CPYF does not use indexes. However, OPNQRYF/CPYFRMQRYF will use indexes.

There's also RUNSQL.

[tomliotta]

Re: Recreate a logical with a date range that changes

CPYF should normally use the index of the FROMFILE(). That's why FROMRCD() can be effective for some CPYF operations and why it can alter the sequencing.

But I'm pretty sure CPYF doesn't search for appropriate indexes from other LFs.

A DDM file on the TEST system could point back to LIVE. Running CPYF on TEST could copy desired records as needed from the DDMF to the test file. If there is a "date" field in the FROMFILE(), it might be specified in the CPYF INCREL() parameter. (Or I suppose LIVE and TEST could be reversed, but I'd prefer creating secondary objects like the DDM file on TEST.)

Tom

[64waves]

Re: Recreate a logical with a date range that changes

We currently do a CPYF and it currently takes about 8 hours total. Some of our files are in the millions and hundred of millions. I tried to make a logical with a date range of 6 months, didn't seem to help. The DDM file sounds interesting, haven't worked with them for a while. Still trying to find a more efficient way to do it.

[Scott Klement]

Re: Recreate a logical with a date range that changes

CPYF will not use the index to optimize an INCREL() parameter. If that's what you're after, you can use the FROMKEY and TOKEY parameters to tell it a range of keys to copy between. It will only use the index/key defined on the filename that you specify in the FROMFILE parameter, though. It won't examine other LFs, for example, you'd have to specify the LF you wanted to use.

SQL, on the other hand, will do all this in the most efficient manner possible by figuring out the best index for the WHERE and ORDER BY clauses. You should use SQL in preference to CPYF unless you have a good reason to use CPYF.

[64waves]

Re: Recreate a logical with a date range that changes

Thanks Scott, I did write an alternate process with queries but it takes almost the same amount of time to copy the data. I am looking at using a logical with a date range as a driving file in some of the queries. Not making any indexes yet, but not seeing any significant improvement with what I have tried so far.

[DeadManWalks]

Re: Recreate a logical with a date range that changes

1) create a view. No need to ever go back to change a date range.
2) create better indexes. Sure 300 hundred mil is a big file but with the correct indexes for the date fields and other where clauses will greatly speed up the process.

3) possible? reduce the size of the large file. Try and sell the speed or need.