|
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
