Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Query Spooled File contents for a specific user
--
-- description: What spooled files does the current user own?
--
select job_name, spooled_file_name, file_number, user_data,
create_timestamp
from qsys2.output_queue_entries_basic
where user_name = user;
stop;
--
-- description: Query the contents of RUNSQLSTM spooled files for the current user
--
with my_sql_spooled_files (job, file, file_number,
user_data, create_timestamp) as (
select job_name, spooled_file_name, file_number,
user_data, create_timestamp
from qsys2.output_queue_entries_basic
where user_data = 'SQL'
and user_name = 'SCOTTF'
),
my_spool_detail (line_number, data, job, file,
file_number, user_data, create_timestamp) as (
select ordinal_position, spooled_data, job, file,
file_number, user_data, create_timestamp
from my_sql_spooled_files sp, lateral (
select *
from table (
systools.spooled_file_data(
job_name => job,
spooled_file_name => file,
spooled_file_number => file_number)
) x
) s
)
select *
from my_spool_detail;
stop;
-- Bonus example
-- description: Search all of the spooled files that I own
with my_spooled_files (job, file, file_number, user_data, create_timestamp) as (
select job_name, spooled_file_name, file_number, user_data, create_timestamp
from qsys2.output_queue_entries_basic
where user_name = session_user
order by create_timestamp desc)
select job, file, file_number, spooled_data
from my_spooled_files, table (
systools.spooled_file_data(
job_name => job, spooled_file_name => file,
spooled_file_number => file_number)
)
where upper(spooled_data) like '%<search-for-this>%';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment