Programming Languages on Power

When working with SQL naming convention the unqualified specified SQL routines (Stored Procedures, User Defined Functions) are searched within the SQL Path.

However, the unqualifed specified tables, views, indexes are expected to be in the Default/Current schema. If you want to know the schema in which a logical file or an SQL view is located.

When using System Naming convention both unqualified specified files (tables, views, indexes) and unqualified Routines are searched within the (current) library list.

The schema in which the files are located can be determined from the SYSFILES view.

Have a look at the SYSPARTITIONINDEXSTAT view in the QSYS2 schema

Thank you Birgitta,

I use your solution for my request :

WITH TB_INDEXSTAT AS(
    SELECT INDEX_SCHEMA, INDEX_NAME, INDEX_TYPE, NUMBER_KEY_COLUMNS, COLUMN_NAMES
    FROM QSYS2.SYSPARTITIONINDEXSTAT
    WHERE TABLE_SCHEMA = 'my_schema'
    AND TABLE_NAME = 'my_table'
    AND INDEX_NAME = 'my_index'
)
SELECT A.INDEX_SCHEMA, A.INDEX_NAME, A.INDEX_TYPE, A.NUMBER_KEY_COLUMNS,  
B.ORDINAL_POSITION AS KEY_NUMBER, B.ELEMENT AS KEY_FILED
FROM TB_INDEXSTAT A
CROSS JOIN TABLE(SYSTOOLS.SPLIT(A.COLUMN_NAMES, ', ')) B
ORDER BY B.ORDINAL_POSITION;

It's perfect...

But do you know where can i find the sort key information (ASCENDING or DESCENDING) ?

Thank's you

Ascending / Descanding sequence can be found in the SYSKEYS catalog view

Thank you Birgitta, it's exactly what i need...

The new request :

SELECT INDEX_SCHEMA, INDEX_NAME, ORDINAL_POSITION, SYSTEM_COLUMN_NAME, KEY_EXPRESSION, 
CASE WHEN ORDERING = 'A' THEN 'ASCENDING'
     ELSE 'DESCENDING'
     END
FROM QSYS2.SYSKEYS
WHERE INDEX_SCHEMA = 'my_schema'
AND INDEX_NAME = 'my_index'
Order by ORDINAL_POSITION;

It's more simple...
(I was happy to use le SPLIT SQL but it's realy more simple like this).

Thank you !