Re: Question Re OPNQRYF

Responses inline. The comments will surely begin to sound repetitive or otherwise redundant. So for a simple and concise reply [instead], and an alternate suggestions, up front:

The FORMAT() parameter for the OPNQRYF must specify a database file name that has the Record Format which defines all of the field names and their data types\attributes for the intended\desired output file [FILE_3 in this scenario]. That database file with the desired RcdFmt must exist before the OPNQRYF runs.

To see what changed, why not just journal the file being changed, and then use the Compare Journal Images [CMPJRNIMG] command to get a report of what the changes were?

The Compare Physical File Member [CMPPFM] might also suffice to report changes for this scenario, without using journaling.?

Regards, Chuck

On 25-Mar-2012 01:20 , Raja Ayyer wrote:

Please read my answers in red <ed: inline> to your questions below:

CRPence on Date: Sat, 24 Mar 2012 11:49:03 -0700 wrote:

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 verifying the updates.

FILE_3 has to be created explicitly to some specifications, both outside of and prior, to the OPNQRYF [and eventual CPYFRMQRYF], else the Record Format for FILE_3 will be identical to FILE_1 and FILE_2. This is further explained, later.

The OPNQRYF maps all required fields from FILE 1 and then attempting
to map the UPDATED field from FILE 2.

Assuming "all required fields..." means "all fields...", then:

Because FILE_3 does not have any additional fields, beyond those having already been assigned from FILE_1, there is no remaining column to which any data from FILE_2 can be mapped to FILE_3.

Assuming "all required fields" means that some fields in FILE_3 are not yet explicitly mapped [from either FILE_1 or FILE_2]:

Any such remaining unmapped fields in FILE_3 that are compatible with the data type of the field(s) that remain to be mapped from FILE_2 [after all of FILE_1 fields have been mapped already], then the data from field(s) in FILE_2 can be mapped into those fields of FILE_3 [even though the field /name/ might not be any of desirable, accurate, or apropos]. Consider for example the following scenario where the file F3 can accommodate the data of field "B" from both file F1 and F2 [and for lack of %NULL or *DFT for the extra field "D", that is assigned a value of zero]:

create table f1 (a char, b int)
create table f2 (a char, b int)
create table f3 (a char, b int, c char(10), d dec)
opnqryf file((f1) (f2)) format(f3) jfld((1/a 2/a *eq))
mapfld( (a '1/a') (b '1/b') (c '2/b') (d 0) )

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.

Given three files all having the identical format, as described by the SQL request to CREATE TABLE with a column list (f1 char, f2 char), only the following possibilities for mapping like-named fields into FILE_3 would be available:

(1/f1,1/f2) -- both fields from file_1
(1/f1,2/f2) -- first field from file_1, second field from file_2
(2/f1,1/f2) -- first field from file_2, second field from file_1
(2/f1,2/f2) -- both fields from file_2

To enable more choices, the FILE_3 must have *additional fields* defined; above and beyond what fields are in the two identical files FILE_1 and FILE_2. Refer to the prior example using FORMAT(F3), in which the INT field "B" was represented by the CHAR(10) field "C"; and if all of the values for "B" were known to be less than five digits, the data for the INT field "B" could have been represented instead by the DEC(5) field "D".

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 FILE 3 is created (as a *OUTFILE) using the OPNID of the OPNQRYF
from the same CL Program that issues the OPNQRYF command

If FILE_3 is created using CRTFILE(*YES) with CPYFRMQRYF, then the Record Format of FILE_3 is obtained from the file name that was either specified or defaulted for the FORMAT() parameter of the OPNQRYF request. Presumably the default FORMAT(*FILE) was the effect. The FORMAT(*FILE) means that [see the help text for that parameter] the external query Record Format is derived from the /first file/ specified on the FILE() parameter of the OPNQRYF; i.e. FILE_3 will be created identical to the FILE_1 [which is identical to FILE_2], when CRTFILE(*YES) is used on the eventual CPYFRMQRYF.

That is a conundrum, because there are only the field names from the Record Format of FILE_1, into which all of the desired data\fields from the relevant fields of both FILE_1 *and* FILE_2 must be mapped.

No response was provided for the possibility of getting sources [DDS or DDL] or Objects promoted, from which these objects in QTEMP can be created. Since having an existing named database file available for and specified for the FORMAT() is the means to resolve the duplicate field names issue, that is a very important detail.

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
command. The 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

There was no response giving either or both the OPNQRYF command string to include the FORMAT() specification and the JOIN related parameter information, nor the SQL equivalent JOIN specifications. Knowing the equivalent SQL variation for what is the desired effect, better describes what the intent is, for an OPNQRYF that obviously is not yet fully understood\functional.

Understood that the file is yet able to be created. The OPNQRYF must be able to complete, before that file can be created; i.e. awaits resolution of the error CPD3107 for the duplicate field name. And the step that actually creates FILE_3, the CPYFRMQRYF which can only operate against the specified Open Identifier, requires that an ODP was previously created, which of course awaits the ability of OPNQRYF to complete without errors.

Aside from various /tricks/ to get a FILE_3 created.... Effectively, the FILE_3 must be pre-created. A Record Format that should define the FILE_3 must exist in the form of another database *FILE [a model file], perhaps created from source before the OPNQRYF or be an altered [ALTER TABLE] version of the currently identical formats. By whatever means that FORMAT() file is created [and\or altered] *prior to* the OPNQRYF, that file must be named on the FORMAT() [or directed-to by overrides, because that parm honors overrides IIRC] of the OPNQRYF request, to avoid the CPD3107 when trying to map the data.

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.

Pre-create FILE_3 with a field for both the original ECESDATE and for the changed [e.g. field ECESDATEX with same attributes as ECESDATE] and use FORMAT(FILE_3); include the MAPFLD specification (ECESDATEX '2/ECESDATE') on the Open Query File request.

In trying to create FILE 3 from FILE 1 and FILE 2, my OPNQRYF joins
key fields from FILE 1 and 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.

In order to use MAPFLD specifications (ECESDATE1 '1/ECESDATE') and (ECESDATE2 '2/ECESDATE') requires that the file named on the FORMAT() keyword has the two field names ECESDATE1 and ECESDATE2 defined in its named [or defaulted *ONLY] Record Format.

Tomorrow, I shall send the entire OPNQRYF script used by the
program.

I have not received this reply [to my private email]... I am responding via the NewsGroup.

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. <<SNIP>>

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.

Again I have a question about just how near impossible to get something promoted to production. Doing this OPNQRYF as a compare is somewhat limited in capability [inserted and\or deleted rows are easily overlooked; SQL queries or programs are probably easier and better], if a FORMAT() file can not be promoted. Having to scrounge for a FORMAT() to accommodate the field data is not a preferable solution, and depending on what some other programs do with FILE_3, that may not even be an option anyhow.

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?

The /trick/ used to ALTER TABLE, AFaIK [from what I have read] requires that the QSHELL feature is installed. If promoting any alternative objects is not acceptable, and this /trick/ might be something to achieve the results, the answer to that question is pertinent.

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.