Re: DB2 Services - object_lock

Hi Scott,

Thank you very much - that works fine and does seem a lot cleaner.

I'm still struggling to understand exactly what is going on.
Whenever I write SQL, I always try to think of sets of data, what they look
like and how they join together.

A LEFT JOIN is straightforward.
But I've not used a Table Function before.

I can see that running something like:

select * from table(users()) u

calls the UDTF users() and returns a table.

I don't really have the mechanics of it clear in my mind as it pertains to
the SQL above - as you say, feeding the data though the CTE and also with
the join on 1=1 which if I understand it correctly would be some kind of a
cross join, which makes me wonder if it's kind of doing a cross join at
each row level.

If you have the patience and a bit more free time at some point I would be
grateful if you could help me understand the mechanics of exactly what is
going on here.

In any case, thanks so much for taking the time to look at my question and
provide a helpful and clear solution.
best regards,
Craig

On Tue, 22 Oct 2019 at 15:40, Scott Mildenberger <smildenberger@xxxxxxxxxxxx>
wrote:

Here is another option that removes the need for the multiple subselects
and the cte that I believe works. I tested by feeding data through a cte
at the top level and the results are what I interpret you want.

select a.obj, a.lib, c.lock_state, c.lock_status, c.lock_scope, c.job_name
from dash00 a
left outer join table (Qsys2.object_lock_info(A.lib, A.Obj, '*DTAARA',
0)) c on 1=1

Scott Mildenberger | Software Developer | Washington Corporations | 101
International Drive | Missoula, MT 59808 | Office: (406) 523-1536 |
www.washcorp.com

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Craig
Richards
Sent: Tuesday, October 22, 2019 7:46 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: DB2 Services - object_lock_info

Hi Kevin,

Thankyou for taking the time to look at the question and reply.

That's interesting - quite different from the way I was thinking about it.
I was going to reply that I could probably just LEFT JOIN the object / lib
table (dash) to that SQL but I was struggling to see how to do that.

So, I ran the SQL that you provided, and actually that does appear to do
the equivalent of a left join on the dash table, it gives me all of the
rows from dash, with nulls for the columns where the UDTF had no lock info.

thanks kindly - I appreciate your response, Craig

On Tue, 22 Oct 2019 at 14:10, Kevin Bucknum <Kevin@xxxxxxxxxxxxxxxxxxx>
wrote:

On #1 the lateral is just simplifying your correlated sub selects. It
allows you to have one select and return multiple fields instead of
having a bunch of sub selects. This is the equivalent.
WITH A AS (
SELECT flib,
fobj
FROM dash00
)
SELECT a.fobj,
a.flib,
(SELECT lock_state
FROM TABLE (
Qsys2.object_lock_info(A.flib, A.fObj,
'*DTAARA', 0)
) AS X) AS lock_state,
(SELECT lock_scope
FROM TABLE (
Qsys2.object_lock_info(A.flib, A.fObj,
'*DTAARA', 0)
) AS X) AS lock_scope,
(SELECT job_name
FROM TABLE (
Qsys2.object_lock_info(A.flib, A.fObj,
'*DTAARA', 0)
) AS X) AS job_name
FROM A;

The problem with #2 is that the table function object_lock_info
doesn't return anything you can left join on. Without going back to
the view, I'm not sure how you could get what you are looking for.

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Craig Richards
Sent: Tuesday, October 22, 2019 5:48 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: DB2 Services - object_lock_info

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

--
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

[https://www.medtronsoftware.com/img/MedtronMinilogo.bmp] Kevin

Bucknum

Senior Programmer Analyst
MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550
Toll Free: 877-893-2550
https://www.medtronsoftware.com

CONFIDENTIALITY NOTICE

This document and any accompanying this email transmission contain
confidential information, belonging to the sender that is legally
privileged. This information is intended only for the use of the
individual or entity named above. The authorized recipient of this
information is prohibited from disclosing this information to any
other party and is required to destroy the information after its
stated need has been fulfilled. If you are not the intended
recipient, or the employee of agent responsible to deliver it to the
intended recipient, you are hereby notified that any disclosure,
copying, distribution or action taken in reliance on the contents of
these documents is STRICTLY PROHIBITED. If you have received this
email in error, please notify the sender immediately to arrange for

return or destruction of these documents.

--
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

This email has been scanned by Washington Corporations using Message Labs
Spam Filtering Technology. If this e-mail is SPAM that you no longer want
to receive, please refer to the Spam or Junk Email Handling Process (
https://washcorp.service-now.com/kb_view.do?sysparm_article=KB0011399).
If you are experiencing any other e-mail problems, please call the IT
Service Center at 855-WC-IT-NOW or x1212 from any office phone.
--
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