Re: DB2 Services - object_lock

It has taken me some time to get around to looking into this, but I'm
struggling a little to get my head around it and would appreciate a nod in
the right direction.
I use allocated data areas to indicate that batch server jobs are running.
I'd like to use some of the DB2 for i Services to check them and present
the information in a dashboard.

There is a view QSYS2/OBJECT_LOCK_INFO which has the information I need,
but it is very slow to run, as Birgitta pointed out above it is making use
of multiple UDTFs and other views to get it's information and is therefore
pretty expensive to use.

So, I've been trying to use the UDTF QSYS2/OBJECT_LOCK_INFO() instead.
I need the information from a few different data area names from a few
different libraries.

Given a file DASH00 which just has OBJ and LIB in for the data areas I'm
interested in;

I can demonstrate that this code (cobbled together from googling examples)
will give me almost all of what I need:

with A as ( select lib, obj from dash00 )
select a.obj, a.lib, c.lock_state, c.lock_status, c.lock_scope, c.job_name
from A,
Lateral (
select * from Table(Qsys2/object_lock_info(A.lib, A.Obj, '*DTAARA', 0))
as X)
as C;

But I confess that I only have a rudimentary understanding of what is going
on.
I believe the lateral keyword allows the object_lock_info() UDTF to access
the column references in CTE A and the statement in general terms to me
looks like join of the flavour:

select a.msgtype, b.msgsts from wsmsgtyp00 a, wsmsgsts00 b

except that in this case the second table is being derived by calling a
UDTF with values (made visible via the lateral keyword) from the first
table.

I'm not sure if that is entirely accurate, but it's my take on it as best I
understand at the moment.

But I have two problems with this.
1) I don't like using code I don't completely understand, so if anyone
could give me a better description or mental model of what is going on I
would be very grateful. I've tried reading around a bit and will continue
to do so, but I am going around in circles a little between usage of UDTF
(I haven't seen a lot of documentation on the way I'm selectively trying to
call it multiple times for specific values) and the lateral keyword which
has more wide-ranging scope that my particular case of trying give the UDTF
access to references at the same level.
2) This is my main problem. The current example above only gives me rows
where there is a lock on the (DTAARA) objects. I'd like a LEFT JOIN with
the DASH00 file (in the above example) so that I see all of the control
data areas, not just the ones that were locked.

Sorry this email is a bit long but I've tried to break it down to the
minimal case and question.
I'd be grateful if anyone has a moment to enlighten me a little.

thanks kindly,
Craig

On Mon, 8 Apr 2019 at 12:27, Craig Richards <craig@xxxxxxxxxxxxxxxx> wrote:

Ah ok - that makes sense

Thanks Birgitta

On Mon, 8 Apr 2019 at 12:25, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

The view join multiple UDTFs and other views together and select ALL and
then your selection is acced.
You may have a look at the UDTFs directly and check whether they return
all
information you want. Passing parameters instead of selecting from the
complete result should be much faster.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they
don't want to.“ (Richard Branson)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Craig
Richards
Sent: Montag, 8. April 2019 12:11
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: DB2 Services - object_lock_info

Hi all,

I am trying to use
OBJECT_LOCK_INFO view
to get some information for a dashboard.

In the following SQL:

select job_name from object_lock_info where system_object_schema in(
select
datalib from cisys/msgsrv00) and system_object_name in('WS010R', 'WS090R',
'GL010R', 'GL090R',
'GL050R')

the msgsrv00 table only contains 7 rows.

I'm only selecting one column (job_name)

But this SQL takes approx 1 minute to run.

Is there some other way I should be using this view to get locking
information?

I found a similar issue when using
OUTPUT_QUEUE_ENTRIES view
in that the response was very slow.

I guess I must be doing something foolhardy as the response times I'm
getting are way higher than I'd expect.

Any comments / advice gratefullly accepted.
thanks,
Craig
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

Re: DB2 Services - object_lock_info