SQL table access sequence

table_access_sequence.sql

-- When indexes are created in one languate - they will not be usable in other languages.
--
-- This table function returns a list of access to tables by
-- national char features. By analysing the SQSSEQ and language 
-- combined with the job CCSID and language, is can be determind
-- if an inex will be used on not.
--
-- Simply paste this gist into ACS SQL and step through the code.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--
-- It is a cool example how far you can go with SQL: Have fun 😀 
-- (C) Niels Liisberg 2022
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------

create or replace function qusrsys.table_access_sequence  ( 
  schema_name char(10)
) 
returns table (
    SQFILE CHARACTER(10),
    SQLIB CHARACTER(10),
    SQFTYP CHARACTER(1),
    SQFILA CHARACTER(4),
    SQMXD CHARACTER(3),
    SQFATR CHARACTER(6),
    SQCSEQ CHARACTER(1),
    SQSEQ CHARACTER(512),
    SQSSEQ CHARACTER(10),
    SQSSLB CHARACTER(10),
    SQLGID CHARACTER(10),
    SQTBSH CHARACTER(1),
    SQTBSU CHARACTER(1),
    SQTRTB CHARACTER(1),
    SQUCEN DECIMAL(5, 0)
)
modifies sql data
external action
begin 
    declare continue handler for sqlstate '38501' begin end; 
   
    call qcmdexc ('dltf  file(qtemp/ZZZSEQLST)');
    call qcmdexc ('dspfd file(' concat trim(schema_name) concat  '/*ALL) type(*SEQ) output(*OUTFILE) outFile(QTEMP/ZZZSEQLST)');
    return 
        select 
            SQFILE,
            SQLIB,
            SQFTYP,
            SQFILA,
            SQMXD,
            SQFATR,
            SQCSEQ,
            SQSEQ,
            SQSSEQ,
            SQSSLB,
            SQLGID,
            SQTBSH,
            SQTBSU,
            SQTRTB,
            SQUCEN
    from qtemp.ZZZSEQLST;
end;

-- Usecase

select * 
from table (
    table_access_sequence ( schema_name => 'ICEBREAK')
);