Query Spooled File contents for a specific user

Query Spooled File contents for a specific user.sql

--
-- 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, ORDINAL_POSITION as line_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>%';

Nice work! What a time saver

I added the line_number in the bonus example