Using system views

[VKLein]

My goal was (is) to provide a list of all modules that are part of a given program. To do this, I use the DspPgmRef command to populate a table then run a query joining that table and the qsys2.sysprogramstat view.

The resulting query runs fine in ACS, but I cannot figure out how to get it to run in Nitro Query App Builder.

Please forgive the long explanation.

Here’s what I did…

Begin with this command:
CL: DSPPGMREF PGM(<myLib>/<*ALL or a specific program>) OUTPUT(*OUTFILE) OBJTYPE(*PGM *SRVPGM *MODULE) OUTFILE(<myLib>/PGMREF);

You will now have data in a table called PGMREF and this query should run fine for you….

select distinct program_schema as pgmlib, program_name as pgmname,
program_schema as objlib, program_name as objname,
program_type as objtype, module_name as modname,
source as source, creator as creator, timestamp
from qsys2.sysprogramstat p
where program_type = '*PGM' and program_schema in (select whlib from pgmref)
union
select whlib as pgmlib, whpnam as pgmname, whlnam as objlib,
name as objname, pgmtype as objtype,
module_name as modname, source as source,
creator as creator, timestamp
from pgmref
join qsys2.sysprogramstat p
on program_schema = whlnam and program_name = whfnam
where whspkg = 'P' and whotyp = '*SRVPGM' and whlnam <> 'QSYS'
and program_type = '*SRVPGM';

I was not able to use it as a query-sourced data source in Nitro Query App Builder, because it cannot figure out how to parse it and keeps moving the where clause.

I then tried an alternated tack and created a view using the query:

create view<myLib>.our_program_reference_view for system name ourpgmrefv as(
select distinct program_schema as pgmlib, program_name as pgmname,
program_schema as objlib, program_name as objname,
program_type as objtype, module_name as modname,
source as source, creator as creator, timestamp
from qsys2.sysprogramstat p
where program_type = '*PGM' and program_schema in (select whlib from pgmref)
union
select whlib as pgmlib, whpnam as pgmname, whlnam as objlib,
name as objname, pgmtype as objtype,
module_name as modname, source as source,
creator as creator, timestamp
from pgmref
join qsys2.sysprogramstat p
on program_schema = whlnam and program_name = whfnam
where whspkg = 'P' and whotyp = '*SRVPGM' and whlnam <> 'QSYS'
and program_type = '*SRVPGM');

Then I tried to build a data source in Nitro Query App Builder using the view... simple, right?
This also failed. I could run this query: select * from ourpgmrefv; in ACS, but in Nitro Query App Builder, when I got to the step of trying to preview the view, it never succeeded, it just said ‘Loading’ until I stopped it. I did not get a bug report, but I do see some messages in the log.

First this one:

Lock enforcement rules broken when trying to access object QQFENDSVR.

Additional Message Information

Message ID . . . . . . : MCH2601
Date sent . . . . . . : 10/09/18 Time sent . . . . . . : 09:00:33

Message . . . . : Lock enforcement rules broken when trying to access object
QQFENDSVR.

Cause . . . . . : An existing lock on object QQFENDSVR is preventing you
from accessing the object. The lock holder type is 1. The lock holder name
is QQFWFSVR QUSER 790805. The lock holder thread identifier is
X'0000000000000001'.
The lock holder types are:
0 - The lock holder is a Licensed Internal Code (LIC) task. The lock
holder name and thread identifier do not apply.
1 - The lock holder is a job.
2 - The lock holder is a transaction control structure. The lock holder
name and thread identifier do not apply.

Followed by this one:
Procedure or user-defined function PROGRAM_STATISTICS in QSYS2 returned a
warning SQLSTATE.

Additional Message Information

Message ID . . . . . . : SQL0462
Date sent . . . . . . : 10/09/18 Time sent . . . . . . : 09:00:41

Message . . . . : Procedure or user-defined function PROGRAM_STATISTICS in
QSYS2 returned a warning SQLSTATE.

Cause . . . . . : An SQLSTATE of the form 01xxx was returned by the
procedure or user-defined function PROGRAM_STATISTICS in QSYS2 (with
specific name QSQPKSTAT), along with message text ?.
Recovery . . . : The user must understand the meaning of the warning. See
your database administrator, or the author of the user-defined function or
procedure.

We went so far as to reboot the iSeries to remove any locks, with no success. Can you see a work-around that I have missed? Any insight you can offer would be appreciated.

Thanks,

Virginia Klein
vklein@wrightservicecorp.com

[robert.swanson]

Hi Virginia,

We're able to get your SQL statement to work on our system without any problems.

There were quite a few enhancements made to the SQL data source parser over the past year, particularly in Valence 5.2, to better facilitate the use of UNION clauses. Are you running the latest 5.2 build? If not, could you try downloading it and see if it works for you?

-Rob

[VKLein]

Thanks, we will give it a try.