|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
