Provide a technique whereby if a data queue becomes damaged, the data queue details are available such that it can be deleted and recreated.

Generate SQL for Data Queues.sql

--
-- Author: Scott Forstie
-- Date:   April, 2025
--
-- The request:
-- When a data queue becomes damaged, that data queue cannot be evaluated or used to create a new data queue.
-- Provide a technique whereby if a data queue becomes damaged, the data queue details are available such that it can be deleted and recreated.
--
-- Purpose:
-- This Gist shows how to establish a data mart of the existence and characteristics of all data queues.
-- If a data queue ever needs to be replaced or recreated, this data mart can be used as the source for reestablishing the data queue(s).
-- This example does not do anything in regards to the entries within any data queue.
-- The user of these techniques needs to have authorizations as described in the SQL services used below.
--
-- Features Used : This Gist uses data_queue_info and the object_privileges (UDTF)
--
-- Resources:
-- https://www.ibm.com/docs/en/i/7.5.0?topic=services-data-queue-info-view
--
stop;
--
-- Data Queues that exist
--
select * from qsys2.data_queue_info;
stop;

--
-- Data Queues definitions (saved daily)
--
create or replace table coolstuff.all_dtaqs as (
select * from qsys2.data_queue_info
) with data on replace delete rows;
stop;

--
-- Review the data queues and their attributes
--
select * from coolstuff.all_dtaqs;
stop;

--
-- Demonstrate how the data mart can be used...
--
SELECT 'QSYS/CRTDTAQ DTAQ(' CONCAT dtaq_lib CONCAT '/' concat dtaq CONCAT ') TYPE(' CONCAT
    CASE dtaq_type
      WHEN 'STANDARD' THEN '*STD'
      WHEN 'DDM' THEN '*DDM'
      ELSE '*DSP'
    END CONCAT ') ' concat 
    case when dtaq_type = 'DDM' 

    then -- DDM Data Queue 
    'RMTDTAQ(' concat rmt_dtaql CONCAT '/' concat rmt_dtaq concat ') ' concat
    'RMTLOCNAME(' concat remote_loc concat ') ' concat 
    case when rdbname is not null then 'RDB(' concat rdbname concat ') ' else ' ' end concat
    case when appc_devd is not null then 'DEV(' concat appc_devd concat ') ' else ' ' end concat
    case when local_loc is not null then 'LCLLOCNAME(' concat local_loc concat ') ' else ' ' end concat
    case when "MODE" is not null then 'MODE(' concat "MODE" concat ') ' else ' ' end concat 
    case when remote_net is not null then 'RMTNETID(' concat remote_net concat ') ' else ' ' end concat 
    case when text is not null and length(text) > 0 then 'TEXT(''' concat text concat ''') ' else ' ' end 
    
    else -- STD or DSP Data Queue
    case when msg_length is not null and msg_length > 0 then 'MAXLEN(' concat msg_length concat ') ' else ' ' end concat 
    case when force = 'NO' then 'FORCE(*NO) ' else 'FORCE(*YES) ' end concat 
    case "SEQUENCE" when 'KEYED' then 'SEQ(*KEYED) ' when 'FIFO' then 'SEQ(*FIFO) ' else 'SEQ(*LIFO) ' end  concat
    case sender_id when 'YES' then 'SENDERID(*YES) ' else 'SENDERID(*NO) ' end concat
    case spec_max when -1 then 'SIZE(*MAX16MB ' when -2 then 'SIZE(*MAX2GB ' else 'SIZE(' concat spec_max concat ' ' end concat case when init_aloc is null then ') ' else init_aloc concat ') ' end concat 
    case reclaim when 'YES' then 'AUTORCL(*YES) ' else 'AUTORCL(*NO) ' end  concat
    case when text is not null and length(text) > 0 then 'TEXT(''' concat text concat ''') ' else ' ' end
    end
  FROM coolstuff.all_dtaqs;
stop;


--
-- Data Queues authorizations (saved daily)
--
CREATE OR REPLACE TABLE coolstuff.all_dtaq_permissions FOR SYSTEM NAME dtaq_perms (
      dtaq_lib, dtaq, user_name, obj_auth, autl, "GROUP", "OWNER", autl_mgmt, obj_owner, objoper, objmgt,
      objexist, objalter, objref, data_read, data_add, data_upd, data_del, data_exec) AS
      (SELECT data_queue_library, data_queue_name, dtaqp.authorization_user, dtaqp.object_authority,
              dtaqp.authorization_list, dtaqp.primary_group, dtaqp.owner,
              dtaqp.authorization_list_management, dtaqp.object_owner, dtaqp.object_operational,
              dtaqp.object_management, dtaqp.object_existence, dtaqp.object_alter,
              dtaqp.object_reference, dtaqp.data_read, dtaqp.data_add, dtaqp.data_update,
              dtaqp.data_delete, dtaqp.data_execute
          FROM qsys2.data_queue_info, LATERAL (
                 SELECT *
                   FROM TABLE (
                       qsys2.object_privileges(
                         system_object_schema => data_queue_library,
                         system_object_name => data_queue_name, object_type => '*DTAQ')
                     )
               ) dtaqp)
      WITH DATA
  ON REPLACE delete ROWS;
stop;

--
-- Review the authorizations for the data queues
--
SELECT *
  FROM coolstuff.all_dtaq_permissions;
stop;

--
-- Review the non-owner authorizations for the data queues
--
select * from coolstuff.all_dtaq_permissions where obj_owner = 'NO';
stop;

--
-- Create CL commands that can adjust the non-owner authorizations for the data queues
--
SELECT 'QSYS/GRTOBJAUT OBJ(' CONCAT dtaq_lib CONCAT '/' CONCAT dtaq CONCAT ') OBJTYPE(*DTAQ) USER('
    CONCAT user_name CONCAT ') AUT(' CONCAT obj_auth CONCAT ')' as dtaq_authority_cmds
  FROM coolstuff.all_dtaq_permissions
  WHERE obj_owner = 'NO';
stop;