Re: System i Navigator error when accessing Schemas

On 08-Oct-2015 10:43 -0600, Mike Cunningham wrote:

On 08-Oct-2015 10:22 -0600, rob wrote:

On 08-Oct-2015 10:08 -0600, Mike Cunningham wrote:

We are migrating from a power 6 to a power 8 system and did a
full system save on the old system and a full restore on the new
system. Same level of i OS on both (7.1) and same patch level on
both. On the old system we can access the Schemas section under
Databases without error but on the new system we get this error.

[cid:image001.png@01D101B7.8AB892D0]

"An error occurred while attempting to initialize the list.
[SQL0443] Trigger program or external routine detected an error.
Cause Either a trigger program external procedure or external
function detected and returned an error to SQL. If the error occurred
in the trigger program the trigger was on table QDBSSUDF2 in schema
QSYS."

That is just the manifestation of the SQL error, in response to the likely prior and generic CPF503E "An error occurred..."; just like what is included below:

We get a similar error when trying to use the Database
Maintenance tool in Navigator but that error window lets us see
the joblog which is showing this additional detail

Cause . . . . . : An error occurred while invoking user-defined
function OBJECT_STATISTICS in library QSYS2. The error occurred
while invoking the associated external program or service program
QDBSSUDF2 in library QSYS, program entry point or external name
QSQOBSTAT, specific name QSQOBSTAT. The error occurred on member
QAUGDBNAV file QAUGDBNAV in library QUSRSYS. The error code is 1.
The error codes and their meanings follow:
1 -- The external program or service program returned SQLSTATE
ŸŸ000. The text message returned from the program is:

That is apparently the text for CPF503E [symptom msgCPF503E rc1 ec1]. Conspicuously missing, is any text following the "is:".? If the information is really missing vs merely omitted, then like the SQLSTATE replacement data, the replacement-data is apparently corrupted; the best way to track down that information, is to go to the joblog.

Has anyone ever seen this before?

I know that some feel that a RCLSTG is an archaic tool which
should never be run anymore but I disagree and believe that after
such a restore is an excellent time to run it. Did you?

Ugh! Hopefully not. As why would that be appropriate? A simple restore entire system from another system, given the request was successful, should rarely have cause to perform a Reclaim Storage request; excepting perhaps when messaging suggests, but then the first inference should be that there is a defect, because the fresh restore should not be exhibiting such problems [already], so the more sane path is contacting a service provider to examine what has gone awry... because the reclaim would wipe-out most of anything that could be gathered for the problem determination of what went wrong prior to the reclaim request.

<<SNIP>> Can you post the actual error message id from the joblog?
<<SNIP>>

<<SNIP>>

I'm not sure what joblog to look at on the system since this work is
handled by one of the service programs.

The /server job/? Use WRKOBJLCK the_user *USRPRF to track down in which job the SQL\database requests are running; i.e. the job /servicing/ the requests. The /last-SQL/ statement issued in that job also would be of interest, to correlate with the errors that were logged.

Or for the UDF error, try the invocation of the OBJECT_STATISTICS in library QSYS2 UDTF on a green-screen, e.g. in a STRSQL to test the results asking for information from the same library the request failed on the client request. Separately, query the data in the file QAUGDBNAV in library QUSRSYS for a sanity-check. The failure can be inferred to be a query on which the UDTF is code as a TABLE() invocation, presumably with values of field(s) from the QAUGDBNAV supplied as input arguments on the table-function invocation.

Ran the same DSPOBJD on our systems and got something weird
On the "old" system 5770SS1 V7R1M0 PTF number SI55620
On the "new" system 5770SS1 V7R1M0 PTF number SI56083

Different PFTs but we did not apply patches on the new system after
the upgrade. Our contractor did say he put on microcode/firmware
patches specific to the new processor/hardware but did not mention OS
or other program product patches

Differing PTF levels for the *SRVPGM QDBSSUDF2 suggests what was implied about the restore to a new system was something more. But since the latter is a supersede of the former, that would be presumed to be a good thing.

p.s. we did not do a RCLSTG. Were advised that it was not necessary.

Likely accurate. Although some may hold-on dearly to that Reclaim Storage command as some panacea, unless there is an understood reason why the request should indeed assist, probably best to *not* invoke the command. The use of the RCLSTG command likely will severely inhibit progress on other fronts, and if no joy comes, the loss of all that time will have been expensed, but for no quantifiable good; i.e. investigate and attempt to understand and solve a problem, for which a logical conclusion might be that some form of RCLSTG invocation is required to resolve the problm, but please do not issue the reclaim merely _hoping_ a problem will go away.