Allow CHGPF and ALTER TABLE to drop columns

Due to processing by IBM, this request was reassigned to have the following updated attributes:
Brand - Servers and Systems Software
Product family - Programming Languages
Product - Developer for Power Systems

For recording keeping, the previous attributes were:
Brand - Rational
Product family - Design & development
Product - Developer for Power Systems

After investigation, it has been determined that there is no safe way for RDi to support this because changes to things like *SYSRPYL are not local to the current job. The right way to address this is to have CHGPF/ALTERPF add a parameter to suppress this message. Please continue to pursue a requirement on the operating system.

On 29-May-2015 19:46 -0500, CRPence wrote:
> On 28-May-2015 10:57 -0500, Buck Calabro wrote:
>> IBM i insists on issuing a message to warn you if your CHGPF is
>> about to drop a column - but only if issued interactively. Issue a
>> CHGPF in a batch job and IBM i behaves differently - IBM i does not
>> issue CPA32B2 at all in batch. Instead, it issues a CPD32CC *DIAG,
>> CPD32CE *DIAG (which falsely claims an inquiry message was replied
>> to!), and a CPF7304 *ESCAPE which terminates the CHGPF. The upshot
>> of that is that not even changing the reply list will help!
>>
>> In a last ditch effort to try a workaround, I did a CHGJOB
>> INQMSGRPY(*RQD) and tried it. No dice. IBM i says 'Hey, you're in
>> batch so you get auto-killed'.
>>
>> This is a situation with IBM i itself, not RDi. It'll probably
>> take a DCR or a COMMON requirement to change CHGPF to allow a
>> batch process to issue CPA32B2.
>
> Hmm. Odd. I did not recall that behavior. I just tested on v5r3 and
> saw exactly that. I thought the effect had always been the inquiry
> msg CPA32B2, and that an auto-reply [with the message default] was
> the effect for batch with Inquiry Message Reply Handling (INQMSGRPY)
> of required (*RQD); and that the System Reply List (*SYSRPYL) option
> was also available in batch. Given the clearly incorrect implication
> of msg CPD32CE, per the conspicuous contradiction of the claim of the
> prior inquiry that is not visible and only the msg CPD32CC being
> logged prior, [if it were me affected, then] I would choose to report
> the issue as defect.
>

I can not use trace nor the reply list entry feature, plus I have
only v5r3, so I can only speculate; I really do not recall any specifics
of this or similar inquiry message coding in the DB feature of the OS.

The situation may be that the QDBCHGFI program, procedure VFYALTER
codes an inquiry messaging processing that is not a common System
Program Interface (SPI) supplied by the Message Handling (MH) component
whereby an inquiry is sent to *EXT for the interactive job, but
redirected to *SYSOPR (QSYSOPR) message queue for jobs that do not have
the 5250 workstation at which to provide a reply. Effectively that
would be an OS interface to the Send User Message (SNDUSRMSG), with the
asterisk as special\single value for the To Message Queue (TOMSGQ)
parameter specification, for which the meaning is "In an interactive
job, the message is to be sent to the external message queue (*EXT). In
a batch job, the message is to be sent to the system operator (message
queue QSYSOPR in library QSYS)."

Possibly the issue is that the effective specification by VFYALTER of
TOPGMQ(*EXT) via whatever is the chosen MH interface to send the inquiry
does not log the Sender Copy of the inquiry nor the Reply despite the
effect is legitimately that the /default reply/ was sent by the MH due
to no WS available in a batch job. Or the VFYALTER is removing the
message(s), and the removals effect the loss of both the Sender Copy and
the Reply as well as the Inquiry message from the joblog.

Whatever...

I do recall there was some /strange/ System Reply List Entry
(SYSRPYLE) added long ago as part of the default system-supplied for
CPA32B2; odd, because the comparison data made no sense, CMPDTA('iSeries
Navigator'). I believe the MH feature must actually have a special-case
for that specific message for work running within the iNav interface;
i.e. as a server job for that interface. Perhaps the same effect can be
extended to an RDi server job [if someone were to pursue]? That might
actually be easier than getting DB to _change_ the inquiry processing to
act in the more conventional manner [implicitly; either via alternate
specifications on the current SNDPgmMSG-like invocation, or changing to
use a different SNDUSRMSG-like interface] whereby the inquiry sent to
*EXT is implicitly redirected to *SYSOPR for /batch/ jobs. I would have
expected however, that anyone making the change to the MH to effect such
a special-case would have recognized the /flaw/ in the DB code and just
said... "Code this instead of that to be consistent with other code
whereby the inquiry redirects to *SYSOPR in non-interactive."

--
Regards, Chuck

On 30-May-2015 00:35 -0500, CRPence wrote:
> <>
> FWiW there is also an inquiry message Exit feature that could be
> used to supply the reply.

Searching on the procedure name VFYALTER that sends the inquiry
[apparently to Program Message Queue *EXT; the external program message
queue], I found the following article Carsten wrote about using an
Environment Variable (ENVVAR) to establish the preference to effect
'I'=Ignore for the msg CPA32B2 inquiry:


APIs by Example: Exit Points, APIs, and Environment Variables
Control inquiry and reply messages in an interactive job—here's how!
Jul 18, 2013 Carsten Flensburg
"...
the environment variable SQL_VFY_ALTER_IGNORE. If this variable is
present and has the value Y, both exit programs will override and change
the reply value of the CPA32B2 inquiry message to I so that the ALTER
TABLE DROP COLUMN SQL statement can process and complete successfully.
..."


July 2013 Source Code Files
"Issue's Articles and Programs Type Page Figure
Exit Points, APIs, and Environment Variables, pg.19
CBX2611 RPGLE 19
CBX2612 RPGLE 19
CBX261M CLP 19
..."

On 30-May-2015 00:35 -0500, CRPence wrote:
> <>
> FWiW there is also an inquiry message Exit feature that could be
> used to supply the reply.

Searching on the procedure name VFYALTER that sends the inquiry
[apparently to Program Message Queue *EXT; the external program message
queue], I found the following article Carsten wrote about using an
Environment Variable (ENVVAR) to establish the preference to effect
'I'=Ignore for the msg CPA32B2 inquiry:


APIs by Example: Exit Points, APIs, and Environment Variables
Control inquiry and reply messages in an interactive job—here's how!
Jul 18, 2013 Carsten Flensburg
"...
the environment variable SQL_VFY_ALTER_IGNORE. If this variable is
present and has the value Y, both exit programs will override and change
the reply value of the CPA32B2 inquiry message to I so that the ALTER
TABLE DROP COLUMN SQL statement can process and complete successfully.
..."


July 2013 Source Code Files
"Issue's Articles and Programs Type Page Figure
Exit Points, APIs, and Environment Variables, pg.19
CBX2611 RPGLE 19
CBX2612 RPGLE 19
CBX261M CLP 19
..."

Re: [WDSCI-L] CHGPF - Cannot drop fields

CRPence
to:
wdsci-l
05/30/2015 01:39 AM


Sent by:
"WDSCI-L"




From: CRPence

To: wdsci-l@midrange.com

Sent by: "WDSCI-L"

Please respond to Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries



On 27-May-2015 10:55 -0500, Mark Murphy/STAR BASE Consulting Inc. wrote:
> I am having issues dropping fields with CHGPF and RDi 9.1.1. It
> tells me it worked, but the file does not change and the log includes
> a CPD32CE indicating that a C was used to reply to CPA32B2.

If "It tells me it worked" means to imply that the RDi interface to
the command entry reported that the request was successful rather than
reporting that the request had failed with an escape msg CPF7304, and
that in fact the joblog shows that error CPF7304 logged as sev-40
Escape, then that would seem to suggest a defect with the RDi for
failing to notify that the command request had failed.

> If I use CHGPF directly from a 5250 interactive command line, I get
> the option to ignore the error. Is there a way to do that in RDi? Not
> sure how you would redirect the inquiry message to the RDi job as it
> is a batch, and takes the default reply which is C. Just wondering
> if anyone had solved this without changing the system reply list.
>

Should be able to Add [System] Reply List Entry (ADDRPYLE) that is
specific to that file, so as not to worry about a conflict; include
Compare Data (CMPDTA) for that file and library name, and ensure the
server job runs with Inquiry Message Reply [Handling] (INQMSGRPY) set to
use the System Reply List (*SYSRPL). The following scripted requests,
assuming the issue for /batch job/ noted by Buck does not also persist
for the RDi server job, for example:

ADDRPYLE SEQNBR(32) MSGID(CPA32B2) CMPDTA('THE_FILE THE_LIBR ')
RPY('I') DUMP(*NO) /* ....+....1....+....2 */

CHGJOB JOB( qualified_RDiJob ) INQMSGRPY(*SYSRPYL)

Too bad that there is [still] no User Reply List object support :-(

As BKBratager mentions there is also the option to effect the default
reply from an alternate message file; the following scripted actions,
for example:

CRTMSGF MSGF(QTEMP/MYCPFMSGF) TEXT('Ovr CPA32B2 default reply')

MRGMSGF FROMMSGF(*LIBL/QCPFMSG) TOMSGF(QTEMP/MYCPFMSGF)
RPLMSGF(*NONE) SELECT(CPA32B2) OMIT(*N)

CHGMSGD MSGID(CPA32B2) MSGF(QTEMP/MYCPFMSGF) DFT('I')

OVRMSGF QCPFMSG QTEMP/MYCPFMSGF

CHGJOB JOB( qualified_RDiJob ) INQMSGRPY(*DFT)


FWiW there is also an inquiry message Exit feature that could be used
to supply the reply.

--
Regards, Chuck