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.
midrange.com
