Re: CPF428A on a JDE Table

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.