Re: ? about SQL Long table names and funky member names

On 31-Mar-2015 09:48 -0500, rob@xxxxxxxxx wrote:

You can list all your partitions that do not match their table name
this way:

with
t1 as
( SELECT
SYSTEM_TABLE_SCHEMA
, SYSTEM_TABLE_NAME
, count(system_table_member) as Partition_count
from qsys2.syspartitionstat
WHERE system_table_member<>system_table_name
group by
SYSTEM_TABLE_SCHEMA
, SYSTEM_TABLE_NAME
having count(system_table_member)=1
)
SELECT
SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_MEMBER
, TABLE_SCHEMA, TABLE_NAME, TABLE_PARTITION
FROM qsys2.syspartitionstat
WHERE (SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_MEMBER)
in (select
SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_MEMBER
from t1)

The above excludes multi partition tables like QRPGLESRC

For the special case of just files with just one member, there should be just one row returned [by the QSYS2.PARTITION_STATISTICS UDTF]. As such, the values for a MIN or MAX aggregate for any of the other [non-grouped] columns would match the detail [non-aggregate] value; i.e. there should be no reason to have two subqueries, just the one summary query:


SELECT
SYSTEM_TABLE_SCHEMA
, SYSTEM_TABLE_NAME
, MIN(SYSTEM_TABLE_MEMBER) as SYSTEM_TABLE_MEMBER
, MIN(TABLE_SCHEMA) as TABLE_SCHEMA
, MIN(TABLE_NAME) as TABLE_NAME
, MIN(TABLE_PARTITION) as TABLE_PARTITION
from qsys2.syspartitionstat
group by
SYSTEM_TABLE_SCHEMA
, SYSTEM_TABLE_NAME
having count(*)=1
and system_table_name<>MIN(SYSTEM_TABLE_MEMBER)


I am not aware of the actual data in SYSTABLESTAT (SYSTSTAT) and SYSPARTITIONSTAT (SYSPSTAT), but if limited to only the SQL TABLE variants of the database *FILE objects, then that might be too limiting for the task; i.e. database *FILE objects of the other SQL file-attributes might similarly be inquired of whether the first\only member name is different from the file name. The following query [using a simple External Scalar UDF; trivial CLP with RTVMBRD or any HLL using QUSRMBRD to get the *FIRST member name is not included here] should probably be much quicker [than using the SYSPSTAT view], and aside from the loss of the SQL partition name in the output, should provide much the same result [though possibly including output for more than just TABLEs]:

select
dbxlib as SCHEMA_NAME
, dbxfil as FILE_NAME
, case dbxatr
when 'VW' then 'VIEW '
when 'IX' then 'INDEX'
when 'MQ' then 'MQT '
when 'TB' then 'TABLE'
end as SQL_DBF
, MEMBER_NAME
, dbxlfi as SQL_NAME
from
qsys.qadbxatr as sqf
, lateral
( values ( mbrname(sqf.dbxlib, sqf.dbxfil) ) as MEMBER_NAME
) as mbr
where dbxatr in ('IX', 'MQ', 'TB', 'VW')
and dbxfil<>ifnull(MEMBER_NAME,'')


Note: if the values-row-clause is incorrect as coded, then replace those two lines with:

( select mbrname(sqf.dbxlib, sqf.dbxfil) as MEMBER_NAME
from qsys2.qsqptabl
) as mbr