RE: Question Re OPNQRYF

To: midrange-l@xxxxxxxxxxxx
From: CRPbottle@xxxxxxxxx
Subject: Re: Question Re OPNQRYF
Date: Sat, 24 Mar 2012 11:49:03 -0700

On 24-Mar-2012 01:44 , Raja Ayyer wrote:

<<SNIP>> a question re OPNQRYF: One of my CL program creates 2
temporary files in QTEMP:

one containing a before image of data (FILE_1) from a physical file
and the

other containing after image (FILE_2) from the same physical file.

So both FILE_1 and FILE_2 have the same Record Format?Yes, both files have the same record format. Basically, they result from a CPYF of a physical file, one that is done before updating data in the physical file and another after data has been updated in the physical file.The FILE 3 that is a resultant is like an audit file for purpose of verifiying the updates. The OPNQRYF maps all required fields from FILE 1 and then attempting to map the UPDATED field from FILE 2. Because the format is the same i.e all the fields are derived from the same Physical File (into FILE 1 and FILE 2), OPNQRYF is giving an error when mapping this field from FILE 1 as well as from FILE 2 as 2 different fields into FILE 3.

After creation of these temporary files in QTEMP,

Are these files created using DDS sources, or CRTDUPOBJ from existing
database *FILE objects? What is the status of the source or objects
[from which the files in QTEMP are derived] as far as restrictions on
/promotion/ with this scenario?

the program then does an OPNQRYF joining these two temporary files to
extract data into a third file (FILE_3).

So did the program create FILE_3 also? Same questions as above, for how.

Perhaps to clarify, that join described using SQL?

So CPYFRMQRYF TOFILE(FILE_3) from the join query ODP.? And either
OPNQRYF FORMAT(FILE_3) was used, or FORMAT(*FILE) causes the query to
use the RcdFmt of FILE_1, per FILE_1 being the first file in the join?
Is the record format of FILE_3 different than the FILE_1 and\or FILE_2,
and if so, how?The FILE 3 is created (as a *OUTFILE) using the OPNID of the OPNQRYF (CPYFRMQRYF) from the same CL Program that issues the OPNQRYF commandThe record format will be different in FILE 3 (with the added field from FILE 2 i.e the field ECESDATE containing updated data). However, FILE 3 is yet to get created

I have a requirement whereby the third file (as a result of the
OPNQRY) contains most of the information from FILE_1 and one field
(ECESDATE) from FILE_2.

What about the scenario and\or the join makes that a requirement?
Perhaps because the field ECESDATE is a\the join field? Or perhaps as
an effect /compare/ of before\after effects, the desire is to show a
copy of both the before and after values for column ECESDATE in the
final output [file]?FILE 1 contains ECESDATE with un-updated data and the same field in FILE 2 contains updated data. I would like to have ECESDATE from FILE 1 and FILE 2 in the resultant file FILE 3. In trying to create FILE 3 from FILE 1 & FILE 2, my OPNQRYF joins key fields from FILE 1 & FILE 2 and then MAPS required fields from FILE 1, just like how you have illustrated below (MAPFLD(A '1/A'...then ECESDATE1 '1/ECESDATE' and ECESDATE2 '2/ECESDATE') The blue highlighted field above is the one that OPNQRYF doesn't like. Tomorrow, I shall send the entire OPNQRYF script used by the program.

Perhaps a very simplified script showing the CREATE TABLE requests to
describe each of the three files, i.e. their record formats, and what
join type\logic will be performed, would help to clarify.?

Here I give an example of such a script. This script assumes:
- FILE_1 and FILE_2 are effectively the same; minor differences
- FILE_3 is as-yet undefined; i.e. coder still struggling
- desire to show before\after of column ECESDATE including deleted
rows, but ignoring added rows

<code>

create table qtemp/file_1
( c char(1), d dec(1), dt dec(8), mab char)
; -- MAB is a column in FILE_1 not in FILE_2
insert into qtemp/file_1 (d) values(1),(2),(3),(4),(5),(6)
;
update qtemp/file_1 set c=d, dt=d, mab=d
;
create table qtemp/file_2
( c char(1), d dec(1), dt dec(8), mbn numeric)
; -- MBN is a column in FILE_2 not in FILE_1
insert into qtemp/file_2 (d) values(1),(3),(5),(6),(7)
; -- drop two rows, add one
update qtemp/file_2 set c=d, dt=d, mbn=d
;
update qtemp/file_2 set dt=d*100 where d in (3, 5)
; -- after image of 3 and 5 are changed for DT
create table qtemp/file_3
( c char(1), d dec(1), dt dec(8), mab char)
; -- unclear on requirement or possibilities for FILE_3
select t1.*, t2.dt /* CREATE TABLE AS mimics CPYFRMQRYF */
from qtemp/file_1 t1 left outer join qtemp/file_2 t2
on t1.c = t2.c
where t1.dt is distinct from t2.dt
/* or: t1.dt<>t2.dt or t2.dt is null */
; -- Perhaps this is the desired effect of the OPNQRYF ?
OPNQRYF FILE((FILE_1) (FILE_2)) FORMAT(FILE_1) OPNID(LOJO)
JFLD((1/C 2/C *EQ)) JDFTVAL(*YES) JORDER(*FILE)
MAPFLD((MAB '2/dt') /* avoid CPD3107 dup flds */
(C '1/C') (D '1/D') (DT '1/DT') )
QRYSLT('((1/dt *NE 2/dt) *OR (2/dt *EQ %NULL))')
; -- Almost, but MAB as Char(1) can not represent 2/DT data
call QZDFMDB2 /* per QTEMP, use undocumented CALL */
PARM('alter table qtemp.file_3 add column dtchg dec(8);')
; -- Undesirable; alternative desired, per QTEMP not via QSH
OPNQRYF FILE((FILE_1) (FILE_2)) FORMAT(FILE_3) OPNID(LOJO)
JFLD((1/C 2/C *EQ)) JDFTVAL(*YES) JORDER(*FILE)
MAPFLD((MAB '2/dt') /* use new field DTCHG */
(C '1/C') (D '1/D') (DT '1/DT') )
QRYSLT('((1/dt *NE 2/dt) *OR (2/dt *EQ %NULL))')
; -- Now both before and after for DT available as w/ SQL
cpyfrmqryf lojo tofile(qtemp/file_3) mbropt(*replace)
; -- destination for [report-like] data is FILE_3 in QTEMP
runqry *n qtemp/file_3
; -- the report of the data; same as what the SQL effected:
....+....1....+....2....+....3....+.
C D DT MAB DTCHG
2 2 2 2 -
3 3 3 3 300
4 4 4 4 -
5 5 5 5 500
******** End of data ********

</code>

This field also exists in FILE_1 which is also required to be
extracted into FILE_3.

The OPNQRYF file allows file qualifiers for the fields. The MAPFLD
allows mapping the data from one field into another.

However, during runtime, OPNQRYF is complaining about the same field
(ECESDATE) existing in both the files (FILE_1 and FILE_2).

Qualify the field with the file number in the Map Field just like
with the Join Fields.

Due to restrictions in promoting to the production environment, I
have not considered using RUNQRY or even RUNSQLSTM (because of other
object overheads involved in promotion). Can this be achieved in
OPNQRYF?

Very likely possible. Probably significantly easier to effect the
promotion of a *QMQRY or some program for the CLP to CALL.? Mostly
because the CLP is making the final or a temporary copy instead of the
data instead of passing the shared join query ODP to another program.

Although likely possible to accomplish whatever within the CL without
/promotion/ of more objects, more specifics about what is available
already without promoting new objects into production would be of value
to know first, to better explain how, without too much guessing. Also,
is the QSHELL available\installed for use if desirable?

Anyhow... Perhaps answer some of the questions about the record
formats, and give some examples of differences between them if they
exist, plus give the OPNQRYF request being used [attempted] to
accomplish the join. IMO, *best* [as in my example <code>] to provide
the answers as the scripted SQL that would do all of the work, followed
by what is attempted as the equivalent CL requests [or as best as can be
described by the OP] that should mimic that scripted SQL.

Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.