DSPFFD may not be sufficient. The DSPFFD does not show all of the
attributes that are included in the hash that is the RcdFmt LvlId. The
"origin" [from my quoted message] of the files means to imply source and
[release and name of] utility used to create the database *FILE object.
If the FROM file was created with DDS, then the actual source(s) are
best to provide in addition to DSPFFD [though possibly also DMPOBJ]; and
if reference objects, then those object, DSPFFD, sources, and origins
[and maybe dumps] -- see how complicated it could be? The simplest
scenario that someone could review, would be having the non-reference
pared [DDS or SQL DDL] source that describes the FROM file and then the
full\exact SQL statement string "CREATE TABLE .. AS ( ... ) ..." which
reproduces the apparently incorrect output.
As to avoiding OPNQRYF... My comment was meant to imply that there
may be little reason to avoid OPNQRYF. That is, why try very hard to
eliminate or avoid OPNQRYF, if the effect is going to be significantly
more work for either or both the programmer and the system? For example
in a prior thread, I had made several responses, at least one with some
text which seems applicable here:
Subject: Re: Must I use OPNQRYF
Date: Mon, 20 Feb 2012 10:01:44 -0800
http://archive.midrange.com/midrange-l/201202/msg00587.html
" ...
IMO the "stop using OPNQRYF" arguments are little better than "stop
using RPG" arguments. In either case, the best argument in support, is
generally the effect of learning something new\different. The scenario
of the OP almost screams for the use of OPNQRYF to minimize the amount
of [re]work; i.e. the RPG can remain unchanged. Suggesting to avoid the
OPNQRYF in that scenario seems a redirection away from the obvious,
though detailed intentions by the OP were lacking."
Other responses I made in that older thread can be located here:
http://archive.midrange.com/midrange-l/201202/threads.html#00559
In this scenario, as described, the OPNQRYF could be much more
efficient even if having created a temporary result, because its
temporaries are true "temporary objects" rather than permanent objects
in a pseudo-temporary [i.e. a permanent object which gives an impression
of temporary existence, the] library named QTEMP. Plus, if the existing
RLA program that expects the input file were expecting a Keyed file, the
CREATE TABLE was creating with an "arrival sequence" access path. And
while a SQE query may be able to select, retrieve, and generate a copy
of the data in a new file quicker than a CQE query, there is nothing in
the described scenario to justify the added overhead of copying all the
data; i.e. there is no selection shown, just ordering.
There are any number of ways to avoid OPNQRYF. What might be the
best choice for any particular scenario depends heavily on the
requirements and the constraints placed on the code and individuals in
effecting the work; e.g. is "must not use OPNQRYF" a dictum, or is
SQLRPG even an option? Certainly an SQL cursor in RPG should be an
option, judged on the capability alone, but only if the parameters
within which the work can be coded\completed allows that option; e.g. if
there is no pre-compiler support installed, then that possibility is
limited, cumbersome, or unavailable.
All that is implied in the scenario for this thread, is the ability
to reorder the data for an apparently existing RLA program. For that,
OPNQRYF is a good match; the ODP generated using an existing index,
generating a temporary index, or a sort-temporary, and merely [and
possibly optionally] a recognition of the Key change for the existing
program. If the given is not an accurate reflection of the real
scenario, then perhaps an example of something that more closely
reflects the actual scenario would be better to explain.
FWiW: While in a reply within the aforementioned thread I had alluded
to using the "CREATE TABLE ... AS", that was partially with regard to
comparison of some of the other /suggestions/ that were being made, plus
regarding my SQE comment made above:
http://archive.midrange.com/midrange-l/201202/msg00613.html
Regards, Chuck
On 08 Jun 2012 13:19, Dave wrote:
I'm home now so I can't do a DSPFFD on the two objects but I remember
that the field lengths and types were all the same. No dates or
varying fields. How else could I have avoided OPNQRYF? Surely not a
cursor in the RPG program?
On 2012/6/8 CRPence wrote:
On 08 Jun 2012 02:54, Dave wrote:
I wanted to avoid using OPNQRYF and did this:
CREATE TABLE QTEMP/mytable AS
(SELECT * FROM myfile ORDER BY myfield)
WITH DATA
RCDFMT myfile_formatname
Making a full permanent [irrespective of "QTEMP"] copy of the data
into QTEMP, just to create an ordered copy :-( Sometimes avoidance
should be avoided ;-)
But the format levels of mytable and myfile are not the same,
despite using RCDFMT.
Why?
That depends... on what is the exact definition and origins of
MYFILE. One example, defaults and identity attributes would not be
included in the new TABLE using the given syntax.
midrange.com
