Ftp problem after upgrade to V7R2

[erickfr...@gmail.com]

A couple of our Ftp process is now failing after upgrading to V7R2.

Specifically when ftping a database view which uses a user defined function.

View is built like :
CREATE VIEW MYLIB/MYVIEW AS
SELECT FUNCTION(parm1) fld1
FROM MYLIB/MYFILE;


FTP COMMAND:

GET MYLIB/MYVIEW.MYVIEW TEST.txt

This fails with error message

Member MYVIEW in file MYFILE in library MYLIB not found.

I checked multiple times that the object exists before executing FTP.

Thanks in advance for any assistance.

[CRPence]

On 16-Nov-2015 09:17 -0600, erickfr...@gmail.com wrote:
> A couple of our Ftp process is now failing after upgrading to V7R2.
>
> Specifically when ftping a database view which uses a user defined
> function.
>
> View is built like :
> CREATE VIEW MYLIB/MYVIEW AS
> SELECT FUNCTION(parm1) fld1
> FROM MYLIB/MYFILE;
>

What does the Display File Description (DSPFD) of that VIEW show for
the "SQL view create statement", for which the qualified FUNCTION
invocation will appear explicit vs unqualified? And then, does that UDF
still exist? Does the request made at the server, to CPYF MYLIB/MYVIEW
*PRINT log a similar error? If so, then collect the [debug] joblog from
that request [similar to described below for doing that within the
failing FTP session].

>
> FTP COMMAND:
>
> GET MYLIB/MYVIEW.MYVIEW TEST.txt
>
> This fails with error message
>
> Member MYVIEW in file MYFILE in library MYLIB not found.

Obtain the joblog with full logging; optionally also to include debug
messaging. That can be done by:

In the FTP session before the GET, issue the following requests:

quote rcmd ovrprtf *prtf hold(*yes) ovrscope(*job) splfown(*usrprf)
quote rcmd chgjob log(4 0 *seclvl)
quote rcmd strdbg updprod(*yes)

In the FTP session after the failing GET, issue the following request:

quote rcmd dspjoblog output(*print)

In an interactive session on the server, signed in as the user that
requested the FTP GET, issue the following request and then act
according to the comment:

wrksplf /* find the QPJOBLOG data and share that output here */

>
> I checked multiple times that the object exists before executing
> FTP.
>
> Thanks in advance for any assistance.

A WAG, that possibly required to circumvent the issue, will be to
effect /turning off/ the new capability that fully implements the open
and I\O requests for that data via the SQE instead of via the CQE; i.e.
forcing a different code path might be able to circumvent what is the
actual\specific cause of the failure. I do not have IBM i 7.2 so use of
that feature is not by actual experience, but I believe that the
capability is described in an addendum to, but outside of the Memo To
Users; a Database-specific document.

--
Regards, Chuck

[erickfr...@gmail.com]

On Monday, November 16, 2015 at 9:33:16 AM UTC-8, CRPence wrote:

> On 16-Nov-2015 09:17 -0600, wrote:
> > A couple of our Ftp process is now failing after upgrading to V7R2.
> >
> > Specifically when ftping a database view which uses a user defined
> > function.
> >
> > View is built like :
> > CREATE VIEW MYLIB/MYVIEW AS
> > SELECT FUNCTION(parm1) fld1
> > FROM MYLIB/MYFILE;
> >
>
> What does the Display File Description (DSPFD) of that VIEW show for
> the "SQL view create statement", for which the qualified FUNCTION

> invocation will appear explicit vs unqualified? I'm assuming when you say explicit or unqualified - it is the library where the UDF is located. MYLIB.FUNCTION(param1)... This is what I see in the DSPFD screen.

And then, does that UDF

> still exist? Yes

Does the request made at the server, to CPYF MYLIB/MYVIEW

> *PRINT log a similar error? No. It works just fine.

I'm gonna have to ask my vendor to work with me on this. I don't have all the necessary access to the system.

The vendor also said they will install a cumulative PTF this weekend. They think there is something in there that might solve this problem. They also opened a ticket with IBM.

Anyway I shared a link to the job log below as per your recommendation above.


https://drive.google.com/folderview?id=0B01dYbutWDLlejZYMDQ4Z0NxVkk&usp=sharing

Please let me know if you can't access it.

Thanks for the assistance Chuck. Very much appreciated.

Erick

[CRPence]

On 16-Nov-2015 20:59 -0600, erickfr...@gmail.com wrote:
> <<SNIP>>

> Anyway I shared a link to the job log below as per your

> recommendation <<SNIP>>
>

The joblog shows symptom msgMCH4430 F/AiEagerActivator x/001328
T/QQQUDFAC TM/QQQUDFAC TP/QQQUDFAC stmt/2034 with target program
QQQSVUSR [possibly arguably recorded as one of rcQQQSVUSR tpQQQSVUSR or
TgtPgmQQQSVUSR] noted, as unable to be activated from System State and
Single Level Storage. A number of web searches varying the tokens [to
identifiers vs as symptoms] yielded nothing for an APAR or PTF, so
nothing already provided by IBM conspicuously should be preventive of
that error.

With some of the other messages logged about "ILE Debug" however, and
lacking any messaging indicative of the "Member MYVIEW in file MYFILE in
library MYLIB not found.", I am unsure if the request to effect Start
Debug (STRDBG) might not have _caused_ the above symptoms as an entirely
different failure than what the OP had described as being experienced.?
Yet as I had alluded in my prior reply, there is the possibility that
whatever is the failure being manifest by the Open requested for the FTP
GET [whether that is described by the above symptoms or something
entirely different] might be generically [and thus incorrectly]
diagnosed as "Member not found" rather than being diagnosed with a more
appropriate message that reflects the actual failure of "Something bad
happened during open of member &x of file &y in library &z, for which
this FTP server code has no specific monitor and corresponding handler."

I suppose another joblog of the FTP server job could be taken, but
without first activating debug; and that joblog might be worthwhile to
make available for review if something different than first that was shared.

One note of possible concern, is that the CPI4339 recorded in the FTP
server joblog revealed that there was a system-wide QAQQINI file in
effect; i.e. the file QAQQINI in QUSRSYS exists. That is generally not
desirable, as the effects from QAQQINI, generally should be quite
limited in scope. Having requested in the FTP to "turn off" the query
override feature might be something else to attempt [though given the
CPYF of the VIEW was noted to have exhibited no error, probably doing so
will see no change in effect]; i.e. prior to the GET, issue the following:

quote rcmd chgqrya qryoptlib(qtemp)

--
Regards, Chuck

[CRPence]

On 16-Nov-2015 23:04 -0600, CRPence wrote:
> On 16-Nov-2015 20:59 -0600, erickfr...@gmail.com wrote:
>> <<SNIP>> Anyway I shared a link to the job log below as per your
>> recommendation <<SNIP>>
>>
>
> The joblog shows symptom msgMCH4430 F/AiEagerActivator x/001328
> T/QQQUDFAC TM/QQQUDFAC TP/QQQUDFAC stmt/2034 with target program
> QQQSVUSR [possibly arguably recorded as one of rcQQQSVUSR tpQQQSVUSR
> or TgtPgmQQQSVUSR] noted, as unable to be activated from System State
> and Single Level Storage. A number of web searches varying the
> tokens [to identifiers vs as symptoms] yielded nothing for an APAR or
> PTF, so nothing already provided by IBM conspicuously should be
> preventive of that error.

Something seemingly odd that I figured might be worth recording as an
eventual symptom [from that same joblog as the above symptom], is that
the failure is eventually manifest in what appears to be some apparent
"put" processing of the FTP server code, despite the failing scenario
was described as a failing FTP GET subcommand and the symptom described
the from-file named as what apparently experienced an open failure [with
no mention of the to-file name in any messaging]:

msgCPF4204 F/QQQQUERY FM/QQQQUERY FP/QQQQUERY stmt/34043
T/QTMFCOM2 TM/QTMFFXFR TP/PutFile__Fv [TP/PutFile] stmt/774

--
Regards, Chuck

[erickfr...@gmail.com]

Hello Chuck,

Sorry for the late reply...

I will try to capture the joblog without the debug and see what comes-up.

I will also try tweaking the QAQQINI in a test machine to see if that makes a difference.

Will report back once I get fresh results.

Thanks for your response.

Regards,
Erick

[paul.w...@gmail.com]

On Monday, November 23, 2015 at 11:28:16 PM UTC-5, erickfr...@gmail.com wrote:
> On Monday, November 16, 2015 at 9:04:29 PM UTC-8, CRPence wrote:

Hi,

This past weekend we have run into the exact same issue described here. We are trying to FTP a file that has a user defined function.

Has there been any progress as to a resolution to this issue?

Thanks,
Paul

[CRPence]

On 13-Dec-2015 12:54 -0600, paul.w...@gmail.com wrote:
> This past weekend we have run into the exact same issue described
> here. We are trying to FTP a file that has a user defined function.
>
> Has there been any progress as to a resolution to this issue?

I did some more general searching and found a possible match for
origin of difficulties, albeit the joblog from the OP does not contain a
referenced msg CPF426A [after the msg MCH4430] that was described by the
IBM i 7.1 Memo To Users [the rzaq9.pdf for that release]; presumably
making changes to ensure the Storage Model (STGMDL) is as v7r1 intends
to create the SQL User Defined Function, the UDF will be able to be
invoked without the MCH4430 error:

Normally I would include a link, but despite my best efforts while
searching the v7r1 KnowledgeCenter, the searches would return only v7r2
doc links, so here is a snippet of text without a preceding URL to the pdf:
"...
_SQL programming changes_

_Teraspace User Default Activation Group considerations for SQL_

Teraspace user default activation group support was added in IBM i 7.1.
This activation group works seamlessly with the single-level store user
default activation group and provides a much larger capacity for
automatic storage required by user programs and service programs. Before
7.1, language SQL routines (procedures, functions, and triggers) were
built with activation group *CALLER and storage model *SNGLVL. Starting
with 7.1, language SQL routines are built with activation group *CALLER
and storage model *INHERIT. By making this change, users will be able to
get their language SQL routines to run within the teraspace user default
activation group by building their application with STGMDL(*TERASPACE).
When the application calls the procedure, function or trigger built with
STGMDL(*INHERIT), the SQL routine will use the application storage model
choice.
..."

--
Regards, Chuck