Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
In this gist, there was a mystery to be solved.... why did rows get eliminated when lateral correlation was used?
--
-- Subject: Using lateral correlation to combine SQL services
-- Author: Scott Forstie
-- Date : January 9, 2022
-- Features Used : This Gist uses active_job_info, joblog_info, lateral correlation, and the values statement
--
--
stop;
--
-- What if we needed to:
-- a) Find active jobs using some filtering critera
-- (for example subsystem name)
-- b) Once we found the active jobs of interest, we needed to drill into the joblogs of these jobs and find something of interesst
-- (for example the most recent request type message)
-- c) Follow the best practice of going to the database one time with our query (when possible)
--
stop;
--
-- ========================================================
-- Step 1: Find the active jobs
-- ========================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME,
FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
from table (
qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
);
stop;
--
-- =================================================================
-- Step 2: Laterally correlate to qsys2.joblog_info
--
-- Lateral allows values returned earlier in the query to be
-- referenced later in the query.
-- =================================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME,
message_text as last_request,
FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
from table (
qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
) AJ, lateral (
select message_text
from table (
qsys2.joblog_info(AJ.JOB_NAME)
) jl
where jl.MESSAGE_TYPE is not distinct from 'REQUEST'
order by ordinal_position desc
limit 1
);
stop;
--
-- ========================================================
-- Look at the results closely! There are fewer jobs.
--
-- We lost the rows that have zero request messages!
-- ========================================================
--
stop;
--
-- =============================================================
-- Step 3: Adjust the lateral correlation to avoid losing rows
--
-- Regain the lost rows by unioning in a placeholder row using the VALUES statement
-- =============================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME,
message_text as last_request,
FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
from table (
qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
) AJ, lateral (
select ordinal_position, message_text
from table (
qsys2.joblog_info(AJ.JOB_NAME)
) jl
where jl.MESSAGE_TYPE is not distinct from 'REQUEST'
union all
values (0, cast(null as varchar(80)))
order by ordinal_position desc
limit 1
);
stop;
--
-- =============================================================================================================
-- Alternative: Use LOJ in combination with LATERAL
--
-- A left outer join (LOJ) includes the rows from the left table that were missing from the inner join.
-- =============================================================================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME,
message_text as last_request,
FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
from table (
qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
) AJ left outer join lateral (
select message_text, aj.job_name as msg_job
from table (
qsys2.joblog_info(AJ.JOB_NAME)
) jl
where jl.MESSAGE_TYPE is not distinct from 'REQUEST'
order by ordinal_position desc
limit 1
) on aj.job_name = msg_job;
stop;
@bobcozzi
Copy link

bobcozzi commented Jan 10, 2022

Neat idea :)
I ended up moving the joblog_info to a (select ...) column in the main select.
That "is not distinct" is "new to me" so I'm going to look into that one.
Is there any potential performance advantage of one style over the other?

@forstie
Copy link
Author

forstie commented Jan 11, 2022

I would guess that the performance is data dependent, based upon the number of jobs and the number of messages in the joblogs.
So.... it depends.

@GlennGundermann
Copy link

GlennGundermann commented May 3, 2022

This works even at 7.3 TR8 with the exception of JOB_NAME_SHORT. Thank you for this Scott.

@forstie
Copy link
Author

forstie commented May 3, 2022

You're very welcome Glenn. Its great to get this sort of feedback from the community.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment