Re: CPF428A on a JDE Table

[CRPence <CRPbottle@xxxxxxxxx>]

  I would have created an LF omitting the unsupported data types, as 
matter of course, in anticipation of that error msg CPF428A RC1, because 
I know I might want to use non-SQL access.  Perhaps JDE may have done 
the same for its PF, possibly, because they may have their own /native/ 
I\O requirements which would have encountered the same difficulty 
[restriction].  Perhaps review the DSPFFD of the relations in the output 
of DSPDBR RCDFMT(*NONE) that are not in the output of DSPDBR 
RCDFMT(*ALL) against the JDE physical file.  Note: A query of the 
*DBXREF dependencies and column data could be done instead of using the 
Display Database Relationships (DSPDBR) output, to determine if a there 
exists a record format defined without the unwanted column name(s) or 
data types.

  The C language has a capability to effect an open using the "process 
large object fields specified in the program" for the UFCB.  That is of 
no help directly to the RPG unless the same capability is exposed via 
the RPG as well, because a shared open would still fail, just like the 
full open, but with the error msg CPF417C RC6 instead of the CPF428A. 
And deferring the work to the C vs to the SQL is probably not 
necessarily any better as a solution.  And obviously, if the capability 
is [were to be] exposed via the RPG, then no reason to defer to the C 
[for its _Ropen lobptrs=Y capability].
http://archive.midrange.com/c400-l/201111/msg00013.html

  The LF could be created into QTEMP versus being created as a 
permanent file [in the library of the PF, or spanning libraries in its 
relations].  While Mark alluded to CRTLF or CREATE VIEW, the CREATE 
INDEX has the capability to define the record format as well.  Given the 
same or otherwise compatible access path definition, the AccPth of the 
existing file is shared; i.e. no actual access path is built\created for 
the SQL INDEX, so there would be no storage or CPU concerns.  I had 
written a procedure that I called VIEW_OF effectively to issue CREATE 
VIEW QTEMP/specified-name ON a TALBE (or VIEW) name given in one 
parameter, and with a parameter was a capability to define the columns 
to be omitted; an omission could be implied however, to implicitly 
effect removing any columns from the select-list for which their data 
types prevent access via typical RLA.  And the same idea could be 
extended to the CREATE INDEX, but instead of a select-list, using its 
RCDFMT clause; i.e. ADD KEYS ONLY or ADD column-name syntax to specify 
the list of columns to be included beyond those implicitly included on 
the key-expression clause.
<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzxcindx.htm>
_CREATE INDEX_
"The CREATE INDEX statement creates an index on a table at the current 
server. ..."

  Instead of an LF, and much like the SQL UPDATE vs native\RLA I/O as 
seemingly silly, the use of an Open Query File (OPNQRYF) creates only a 
temporary ODP.  The FORMAT() specified on the OPNQRYF request would be 
the equivalent of what the LF would be, if that Logical File were used 
to resolve the issue instead.  The Record Format could be a PF, even 
without a member; of course the same keys as the LF.  The KEYFLD(*FILE) 
and really nothing more than the QRYFILE() specification, the /query/ 
ODP should act almost exactly like an LF; i.e. like and more explicitly 
than with CREATE INDEX, there should be no access paths built, so no 
storage or CPU concerns as compared to using an existing\permanent LF. 
Of course the query ODP must be effected using sharing, SHARE(*YES), so 
the RPG open connects with the ODP referring to the Record Format that 
omits the undesirable columns; lacking a CLP controlling the activation 
of the RPG, a named activation is best, so the [presumed to be 
necessary] override can be scoped to the named ACTGRP vs scoping to the 
job which is undesirable.
http://archive.midrange.com/rpg400-l/201204/msg00319.html

Regards, Chuck

On 21-Dec-2013 10:05 -0800, Richard Schoen wrote:
> <<SNIP>>
> I ended up just using an SQL update, but silly to have to compose an
> SQL update when I have a unique key already :-)
>
> I like your idea and it has merit too. I just didn't want to be
> creating logicals over a JDE table since this is a process that will
> run regularly and needs filtering.
>
> I guess I'll just have to get use to the fact that sometimes using
> SQL for all the I/O is the way to go :-)
>
> On Sat, 21 Dec 2013 12:25:44 -0500 Mark S Waterbury wrote:
>
> > How about this approach:
> >
> >  1. create a logical file (or view) over that table that omits the
> > BLOB field, since you say your program will not be using it?
> >  2. then, your program just uses the logical file, instead of
> > directly accessing the physical file.
> >  3. if needed, you could even provide QDDSSRC and issue CRTLF to
> > create the (temporary) logical file (or SQL DDL source to create
> > the SQL view) in QTEMP, when the program runs...
> > <<SNIP>>
> >
> >  On 12/21/2013 12:14 PM, Richard Schoen wrote:
> >
> > > Working to create an RPGLE program that uses a JD Edward/Oracle
> > > E1 V9.1 table and it has a blob field in it.
> > >
> > > Getting CPF428A when the program attempts to implicitly open the
> > > file as defined in the F spec.
> > >
> > > I can get around it by using SQLRPGLE and omitting the blob
> > > field, but is there a way to get a regular RPGLE program to
> > > process a table with a blob field ?
> > >
> > > I don't plan to use the field, but it's in the table.
> > >
> > > I did google and checked the list, but didn't see a clear
> > > resolution for this.