RE: SPL question about error handling. -- MIDRANGE-L

Ok, now I remember why I had the drop the do it all in one statement approach.

select a.job_name, a.job_end_time
from table(qsys2.job_info(
JOB_STATUS_FILTER => '*OUTQ')) a
left exception join historylib.history_table h
on a.job_name = h.job_name
inner join lateral (select a.job_name as job_name_again, x.* from table(qsys2.joblog_info(a.job_name)) x) jl
on a.job_name = jl.job_name_again;

The last join to joblog_info doesn't just join fail, it aborts with the following:

SQL State: 42704
Vendor code: -443
Message: [SQL0443] JOB LOG NOT DISPLAYED BECAUSE JOB COMPLETED EXECUTION

Can we get back to exception handling in SPL?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Tyler, Matt
Sent: Thursday, February 28, 2019 7:08 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.

-- category: IBM i Services
-- description: Find objects in a library, not included in an authorization list
--
--
select A.OBJNAME, OBJDEFINER, OBJTYPE, SQL_OBJECT_TYPE
from table(QSYS2.OBJECT_STATISTICS('TOYSTORE', 'ALL')) A
left exception
join lateral (select SYSTEM_OBJECT_NAME
from QSYS2.AUTHORIZATION_LIST_INFO X
where AUTHORIZATION_LIST = 'TOYSTOREAL')
B on A.OBJNAME = B.SYSTEM_OBJECT_NAME;

-Matt

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Thursday, February 28, 2019 5:03 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.

Could you show a LATERAL example which actually uses a JOIN instead of a WHERE clause, like the example from the ACS RSS examples?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Tyler, Matt
Sent: Thursday, February 28, 2019 6:57 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.

Maybe you need to do something like this; Select A, B, C from ROBS_TABLE inner join lateral (select C from IBM UDTF where ?=??) X (C) on 1=1

Lateral keyword lets you used inline tables differently.

From ACS RSS examples:

--
-- category: DB2 for i Services
-- description: Interrogate interactive jobs
--
with INTERACTIVE_JOBS(JOBNAME, STATUS, CPU, IO)
as (select JOB_NAME, JOB_STATUS, CPU_TIME, TOTAL_DISK_IO_COUNT
from table(QSYS2.ACTIVE_JOB_INFO('YES', 'QINTER', '*ALL')) as A
where JOB_STATUS in ('LCKW', 'RUN'))
select JOBNAME, STATUS, CPU, IO, PROGRAM_LIBRARY_NAME, PROGRAM_NAME, MODULE_LIBRARY_NAME, MODULE_NAME, hex(bigint(STATEMENT_IDENTIFIERS))
as STMT, PROCEDURE_NAME, ACTIVATION_GROUP_NAME, OBJTEXT, V_CLIENT_IP_ADDRESS from INTERACTIVE_JOBS I, lateral
(select *
from table(QSYS2.STACK_INFO(JOBNAME)) J
where PROGRAM_LIBRARY_NAME not like 'Q%'
order by ORDINAL_POSITION desc limit 1)
X, lateral (select OBJTEXT
from table(QSYS2.OBJECT_STATISTICS(X.PROGRAM_LIBRARY_NAME, '*PGM *SRVPGM', X.PROGRAM_NAME)) as C)
as Y, lateral (select V_CLIENT_IP_ADDRESS
from table(QSYS2.GET_JOB_INFO(JOBNAME)) as D)
as Z order by CPU desc;

-Matt

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Thursday, February 28, 2019 4:46 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.

Some of these tables are IBM i services and require a parameter from the cursor. No, this parameter cannot be done with a JOIN or WHERE clause. It's a parameter to a UDTF.
Cursor is built around one IBM i service.
YetAnotherTable is another IBM i service which requires a parameter from the cursor. In our example let's call it fetchColumn1.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Thursday, February 28, 2019 6:37 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SPL question about error handling.

what's in YetAnotherTable?

select * doesn't seem to make sense in the context you've given...

Are you expecting to insert a single row or multiple rows into AnotherTable?

Charles

On Thu, Feb 28, 2019 at 4:35 PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

Rob,

Why do you need a cursor for this?

instead of
Insert into AnotherTable (
Select fetchColumn1, fetchColumn2, (select * from
YetAnotherTable));

does this work?
Insert into AnotherTable
values (fetchColumn1, fetchColumn2, (select * from
YetAnotherTable));

Charles

On Thu, Feb 28, 2019 at 4:23 PM Rob Berendt <rob@xxxxxxxxx> wrote:

Newbie to SPL.
Really trying to get the basics of error handling.

Basically I have a loop
Declare c1 cursor for select ...
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET ROW_NOT_FOUND = 1;
Open c1;
Fetch c1 into fetchColumn1, fetchColumn2...
SET END_CURSOR = ROW_NOT_FOUND;
WHILE END_CURSOR = 0 DO
Insert into AnotherTable (
Select fetchColumn1, fetchColumn2, (select * from
YetAnotherTable));
Note: rows are often not found in YetAnotherTable. In that case I
just want to skip this insert and fetch the next row from the cursor.
Set row_not_found = 0;
Fetch c1 into fetchColumn1, fetchColumn2...
Set END_CURSOR = row_not_found;
End while;
Close C1;
End p1;

The problem is that when it hits the missing row in YetAnotherTable,
it sets SQLCODE, SQLSTATE, ROW_NOT_FOUND, exits the loop and the procedure.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.dekko.com&d=D
wICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJh
t2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ8
5sj4M&s=U5r0J1nXq7UKx-76N9FtGrv5DdHJzH0p2ditzyyZd10&e=

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.c
om_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTw
olPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2
bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=RVzwH55-R8jpVrUdWFWL2Sym5
BY9l28wF__HP5VALlY&e= or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=DTwoofeVQ0LwGznMQ4CyhIdGRWJhnTtNroKg04n-0zI&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.
com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1T
l8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNS
wLLMaZ85sj4M&s=nBDGWhl2fyu41btm0WO1aReHEyf_UWdJjzxmX6dLUeY&e=