can I do this with a logical file?
mark
a logical view over a detail file selecting the current date processed
records only. Can I use something like a reserved word for a
selection? Thank you for any help.
Mercury
I'd use either an OPNQRYF command or write an SQL embedded program to select the date.
Mercury
"mark" <morga...@worldnet.att.net> a écrit dans le message de news: ccd2f16.03051...@posting.google.com...
Steve Landess
> > I am not a DBA but I need to create a program and I would like to use
> > a logical view over a detail file selecting the current date processed
> > records only. Can I use something like a reserved word for a
> > selection? Thank you for any help.
>"Mercury" wrote:
> No, IMHO you can't create a logical view (file) by selecting the current
date in the record.
Hmmm. I guess that means that those times I created a logical file keyed by
a date field and wrote an RPG program that used SETLL & READE to process
ONLY the records for a given date I just didn't know what I was doing, huh?
Better yet, if there is a _processed_ flag in the record, you can also you
Select/Omit DDS specifications to limit the logical file to records that are
processed...
Simple example -
DDS for Physical file for time sheets. For this example, assume dates are
stored as a numeric 8-digit value, in the form YYYYMMDD (could just as
easily be a date type field).
A R RTIMSHT
A TSNBR 9S 0 COLHDG('Time Sheet' 'Number')
DFT(0)
A TSSEQ 3S 0 COLHDG('Time Sheet' +
A 'Sequence Number')
DFT(0)
A TSHRSW 3S 0 COLHDG('Hours' 'Worked')
DFT(0)
A TSDATW 8S 0 COLHDG('Date ' 'Worked')
DFT(0)
A TSDATP 8S 0 COLHDG('Date ' 'Processed')
DFT(0)
A TSTIMP 6S 0 COLHDG('Time ' 'Processed')
DFT(0)
A TSPRCS 1A COLHDG('Processed' 'Y/N')
DFT(' ')
A K TSNBR
Logical file DDS for logical that is keyed by date processed, selecting ONLY
processed records (TSPRCS = 'Y'):
**********************************************************
* TIME SHEETS *
* KEYED BY DATE PROCESSED, SELECTS PROCESSED RECORDS. *
**********************************************************
A R RTIMSHT PFILE(TIMSHT)
A K TSDATP
A S TSPRCS COMP(EQ 'Y')
Now - write an RPG program to process today's records:
1) Prime the key field of the logical file (TSDATP) with the appropriate
date value
2) Use TSDATP with SETLL operation to position the file
3) Use TSDATP with READE operation (in a loop) to read and process all
records containing this date.
If you need more help than this, send me a private email.
HTH
Steve Landess
Austin, Texas
(512) 423-0935
Jonathan Ball
>
> > "mark" asked:
> > > I am not a DBA but I need to create a program and I would like to use
> > > a logical view over a detail file selecting the current date processed
> > > records only. Can I use something like a reserved word for a
> > > selection? Thank you for any help.
>
> >"Mercury" wrote:
> > No, IMHO you can't create a logical view (file) by selecting the current
> date in the record.
>
> Hmmm. I guess that means that those times I created a logical file keyed by
> a date field and wrote an RPG program that used SETLL & READE to process
> ONLY the records for a given date I just didn't know what I was doing, huh?
What's up with the snide tone, pal? This isn't that kind of
newsgroup.
I think you've misunderstood what the original poster was asking,
and what "Mercury" was addressing.
In fact, I _know_ you misunderstood, because the question was,
can you put the selection (on current date) _into_ the
select/omit of the logical file, and your answer is based on
doing the selection in RPG. You ought to read a little more
carefully before adopting such a snide tone.
What the original poster clearly had in mind was something like a
hypothetical
A R SOMEFILE
PFILE(MYPF)
A SOMESTUFF 30
A SOMEDATE L
A S SOMEDATE COMP(EQ *CURRENT_DATE)
where *current_date would be some kind of special register value.
Jonathan Ball
You can't do it in a logical file described using DDS, but you
could do it in a SQL view.
Assuming you have a DDS described file or a SQL table, MYTABLE,
with a hypothetical date field called ENTRYDATE, you could create
a view (unkeyed) as follows:
create view mylib/myview as
select *
from mylib/mytable
where entrydate = current_date
You could then write a program that could do simple READs from
the view, and only the records whose date field values were equal
to the current date, whatever date you run the program, would be
selected.
Birgitta Hauser
if you save and update the process date in your file, you can use a
view to select all records processed at the current date:
CREATE VIEW MyLib/MyView
AS SELECT * FROM MyLib/MyFile
WHERE MyDate = current date
Birgitta
morga...@worldnet.att.net (mark) wrote in message news:<ccd2f16.03051...@posting.google.com>...
Mercury
"Steve Landess" <steve_...@hotmail.com> a écrit dans le message de news: JpZwa.9356$ql5.3...@twister.austin.rr.com...
Hi,
I guess Steve that you want to teach me a lesson and you failed because you did not carefully read what the original Poster was
asking.
Read it again thoroughly please.
And also perharps simply you feel some aversion for the French ?
Anyway I have no spite against you.
Regards
Mercury
Paris France
mark
in detail so there might have been some confusion in the answers. The
file that I am trying to process is a very large detail file and I was
trying to avoid the overhead in the program looking for the date. The
processed field contains several values for different stages of being
processed and they can remain unprocessed for a few days so I can't
count on that value. I am not too familiar with SQL but it sounds like
it might work. Can I put this in a CL program and create the view each
time before I run the program? Do I need to drop the view after each
run? Thank you all again for your help.
Mercury
"mark" <morga...@worldnet.att.net> a écrit dans le message de news: ccd2f16.03051...@posting.google.com...
Mark,
A SQL view is like a logical file, you need not to drop and create the view each time you run the program except if you need to
change the selections in the view which can support compound conditions in the WHERE clause.
Mercury
Jonathan Ball
>
> Thank everybody for their help. I probably didn't explain the problem
> in detail so there might have been some confusion in the answers.
I thought you explained the issue just fine.
> The file that I am trying to process is a very large detail file and I
> was trying to avoid the overhead in the program looking for the date.
You're going to incur some overhead either way. Your program can
be simpler if you move that overhead to the database engine.
> The
> processed field contains several values for different stages of being
> processed and they can remain unprocessed for a few days so I can't
> count on that value. I am not too familiar with SQL but it sounds like
> it might work. Can I put this in a CL program and create the view each
> time before I run the program? Do I need to drop the view after each
> run?
You probably don't need to create and drop the view in each run.
In order for the view to work efficiently, you should have either
a keyed logical file (DDS) or an index (SQL) built over the date
field; otherwise, the database engine is going to perform what's
called a table scan when you try to read through the view, and a
table scan - reading every record in the file to see if it meets
the selection criteria - is a very bad idea if there is a lot of
data.
If you have a permanent LF or SQL index built over the table in
order to index by the date field, there is the issue of when the
index is to be maintained. If it's maintained immediately, it
means incurring a little bit of extra overhead any time a record
is written to the file. If the process that's going to read the
view is, say, a batch process that runs when no one else is on
the system, AND if there is no other requirement to search the
file by date during the day, then you could specify delayed
access path maintenance.
jb
midnight. unless someone can tell you for certain what the results will be &
be sure its the same for the next OS version as well. It may be that the SQL
evaluates current date just once wheras I would expect curdate() to evaluate
for every row. Then again one may just be a synonym for the other ?
Jonathan.
"Jonathan Ball" <jon...@whitehouse.not> wrote in message
news:3EC50089...@whitehouse.not...
Michael
As reported by the group, DDS does not allow a variable in a select clause so unless you want to
create DDS source code on the fly that alternative is out. However, you said the detail file is
"very large" so you probably need to consider the processing overhead. If so, even though DB2/400
is very efficient, a dynamic SQL query or an OPNQRY would have a significant overhead. This is
because all records must be scanned to grab the ones you want or you must maintain a keyed access
path on the transaction date which will add a small overhead on adds and updates.
Here are some alternatives that avoid some or all of these penalties but have other issues to
consider.
A) As Steve Landess said, if you maintain a key file in date order, you can start at date value
greater than or equal to X with little or no file open or first read time. This does mean
continuous maintenance of another access path but is probably much less costly than a daily run of
an SQL or OPNQRY scan.
B) Your job could store, in a small file with one record, the relative record number of the last
record processed in each days run against the detail file. Your program could then start at that
RRN and read forward. While very efficient, this is not a recommended design because a
reorganization of the detail file would invalidate the RRN pointer you last saved. You could only
reorganize the detail file just after your job was run and before any new transactions were added to
the detail file. Further, since deleted records could be dropped in a reorganization, you would
need to recreate the RRN pointer with the file record count at the end of the reorganization.
C) You could attach a trigger program to the detail file and have it copy each new recorded to a
daily work file. Your daily job would then process this work file and clear it.
D) If your database is journaled, especially if new receivers are generated each day, you could use
the DSPJRN command to copy all added records to a work file that your job would process and clear.
I hope this was useful.
Mike
"mark" <morga...@worldnet.att.net> wrote in message
news:ccd2f16.03051...@posting.google.com...
Steve Landess
My feeble attempt at snide humor was inappropriate. However, I did
understand the question. I just didn't expect the kind of reaction that I
got.
I was just trying to point out that in most iSeries shops (and MOST of them
don't have a DBA) where I have worked the standard is to determine how the
data needs to be organized and build permanent logical files (often with
select/omit) that are keyed various ways. This way you can process records
easily in RPG using SETLL and READE. It is not necessary to create the
file(s) each time that you want to process records, particularly if the
based-on PF(s) contain million of records.
As other posters have (or should have) mentioned, when you have a permanent
logical file or a keyed PF that has the appropriate key structure, the file
manager will share the access path of an existing file rather than build a
new one when creating the new access path. This is true for creating new
LF's, OPNQRYF, interactive SQL, and embedded SQL used in your programs
(regardless of language).
As for you, Mercury:
I apologize if I offended you, but why the heck would I care if you are
French? I don't see anything in my original post that would indicate ANY
prejudice on my part toward anything French. Seems like you're a little
thin-skinned, dude.
Bill Z Bubb
Mark, one option not offered yet would be to build a new DDS statement at
the start of every day. Just create a program the recreates the DDS
statements as necessary, after all a DDS file is basically just a data
file.
To make it easy on yourself, create the logical file statement manually and
compile it to make sure it works. Then create a program that will recreate
those statements, substituting the current date value where appropriate.
Bill
Jonathan Ball
> mark wrote:
>
>>I am not a DBA but I need to create a program and I would like to use
>>a logical view over a detail file selecting the current date processed
>>records only. Can I use something like a reserved word for a
>>selection? Thank you for any help.
>
>
> Mark, one option not offered yet would be to build a new DDS statement at
> the start of every day. Just create a program the recreates the DDS
> statements as necessary, after all a DDS file is basically just a data
> file.
It could be done, but it's *really* dirty code.
Embedded SQL is much cleaner.
Bill Z Bubb
>> Mark, one option not offered yet would be to build a new DDS
>> statement at the start of every day. Just create a program the
>> recreates the DDS statements as necessary, after all a DDS file is
>> basically just a data file.
>
> It could be done, but it's *really* dirty code.
> Embedded SQL is much cleaner.
I don't know what you mean by dirty code, we're doing something like this
here - we build a logical file over the inventory transaction history file
that filters to show only those transactions for "last month". The code is
anything other than dirty and is downright simplistic.
Bill
Jonathan Ball
> Jonathan Ball wrote:
>
>>>Mark, one option not offered yet would be to build a new DDS
>>>statement at the start of every day. Just create a program the
>>>recreates the DDS statements as necessary, after all a DDS file is
>>>basically just a data file.
>>
>>It could be done, but it's *really* dirty code.
>>Embedded SQL is much cleaner.
>
>
> I don't know what you mean by dirty code,
I mean you have to change the DDS every day, or even
worse, you generate it from scratch every day. That
can't be pretty. Then, you're deleting and rebuilding
a logical file every day. If you had a permanent SQL
view that selected rows:
where some_date = current date
you'd have a permanent system object that you wouldn't
have to fiddle with daily.
Bill Z Bubb
>> I don't know what you mean by dirty code,
>
> I mean you have to change the DDS every day, or even
> worse, you generate it from scratch every day. That
> can't be pretty. Then, you're deleting and rebuilding
> a logical file every day. If you had a permanent SQL
> view that selected rows:
>
> where some_date = current date
>
> you'd have a permanent system object that you wouldn't
> have to fiddle with daily.
And then I'd be interested in the performance of such a beast since there
seems to be only one of two possible implementations by the system:
1. Whenever accessed by the system it dynamically creates the view
according to the current system date.
2. It recreates the view whenever the date has changed since the last
access.
Either situation requires a rebuild and most likely a rebuild when
someone's waiting for access. With the LF situation, it can be set to
rebuild during the early morning hours and ready for access for the first
user that needs access.
Bill
Charles Wilt
billzb...@netscape.net says...
There is another option, the access path could be built over all records
and in conjunction with an index or somthing the DB could simply return
only those records with the current date.
Just my .02
Charles