candidate_key.sql

-- Returns the best fit for a candidate key for a library/file
-- for SQL tables, please convert the name to the physical filename first.

-- Simply paste this gist into ACS SQL and run it to create the UDTF.

-- 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 2021

-- 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.candidate_key (
    library char(10),
    file char(10) default null,
    table varchar(64) default null
)
returns varchar (1024) 
set option output=*print, commit=*none, dbgview = *list
begin   

    declare candidatekeys varchar(1024); 
    
    if file is null then 
        select system_table_name 
        into file
        from systables 
        where system_table_schema = library 
        and  table_name = table; 
    end if; 
    
    -- Prefere unique index if any
    -- and the the shortest key length (in bytes) in the index
    -- Filter out any select / omit 
    with a as ( 
        Select case when DBXUNQ = 'U' then 1 else 2 end prefered , a.* 
            from  qsys.qadbxref a 
            where a.dbxlib = library  and a.dbxfil = file 
            and dbxinsert= 'Y' 
        union all  
            Select case when DBXUNQ = 'U' then 1 else 2 end prefered, a.* 
            from   qsys.qadbxref a 
            inner join qsys.QADBFDEP b 
            on     a.dbxlib = b.dbfldp 
            and    a.dbxfil = b.dbffdp 
            and dbxinsert= 'Y' 
            and    dbflib= library and  dbffil = file  
    ), b as (  
        Select dbxlib, dbxfil , k.*, a.* 
        from a
        join lateral ( 
            Select listagg (rtrim (dbkfld) ,',')  within group (order by dbkpos)  as keys, 
                   sum(dbiiln) as bytes, 
                   sum(case when dbxidxsel = 'Y' then 1 else 0 end) noselection
            from qsys.QADBKFLD c 
            left join QADBIATR 
                on  dbifil = dbxfil
                and dbilib = dbxlib
                and dbifld = dbkfld 
            where dbklib = dbxlib 
            and dbkfil = dbxfil
        ) k on 1=1 
        where keys is not null
        and noselection = 0
        order by prefered, bytes 
        limit 1
    )
    Select keys into candidatekeys 
    from b;
    return candidatekeys;
end;

-- Test cases:
values ( 
    qusrsys.candidate_key ( 
        file =>    'QAPMDISK',
        library => 'QSYS'
    ) 
); 

-- As rows:
select * from table (systools.split(  
    qusrsys.candidate_key ( 
        file =>    'QAPMDISK',
        library => 'QSYS'
    ) , ',')
); 
-- Using table ( not file) 
values(qusrsys.candidate_key ( 
   table =>    'SYSROUTINES' ,
   library => 'QSYS2')
);