Now that we know the convention, it should be easy to get the list from the db2 catalog tables.
Here is an untested SQL (I just modified an existing sql that I had) which may* give you the information you are looking for.
PS: I defaulted to D (for VSAM data components) for the 2nd qualifier. You can also have C (for VSAM clusters)
Code:
SELECT SUBSTR(VCATNAME,1,LENGTH(VCATNAME)) ||
CHAR('.') ||
CHAR('DSNDBD') ||
CHAR('.') ||
SUBSTR(DBNAME,1,LENGTH(DBNAME)) ||
CHAR('.') ||
SUBSTR(TSNAME,1,LENGTH(TSNAME)) ||
CHAR('.') ||
IPREFIX ||
CHAR('0001.') ||
(CASE WHEN PARTITION >= 0000 AND PARTITION < 1000 THEN 'A'
WHEN PARTITION >= 1000 AND PARTITION < 2000 THEN 'B'
WHEN PARTITION >= 2000 AND PARTITION < 3000 THEN 'C'
WHEN PARTITION >= 3000 AND PARTITION < 4000 THEN 'D'
WHEN PARTITION >= 4000 AND PARTITION < 4097 THEN 'E'
END) ||
(CASE WHEN PARTITION = 0000
THEN '001'
WHEN PARTITION > 0000 AND PARTITION < 1000
THEN SUBSTR(DIGITS(PARTITION),3,3)
WHEN PARTITION >= 1000
THEN SUBSTR(DIGITS(PARTITION),2,4)
END)
FROM SYSIBM.SYSTABLEPART A
WHERE A.VCATNAME > ' '
AND A.VCATNAME <> '00000001'
UNION ALL
SELECT SUBSTR(B.VCATNAME,1,LENGTH(VCATNAME)) ||
CHAR('.') ||
CHAR('DSNDBD') ||
CHAR('.') ||
SUBSTR(DBNAME,1,LENGTH(DBNAME)) ||
CHAR('.') ||
SUBSTR(INDEXSPACE,1,LENGTH(INDEXSPACE)) ||
CHAR('.') ||
IPREFIX ||
CHAR('0001.') ||
(CASE WHEN PARTITION >= 0000 AND PARTITION < 1000 THEN 'A'
WHEN PARTITION >= 1000 AND PARTITION < 2000 THEN 'B'
WHEN PARTITION >= 2000 AND PARTITION < 3000 THEN 'C'
WHEN PARTITION >= 3000 AND PARTITION < 4000 THEN 'D'
WHEN PARTITION >= 4000 AND PARTITION < 4097 THEN 'E'
END) ||
(CASE WHEN PARTITION = 0000
THEN '001'
WHEN PARTITION > 0000 AND PARTITION < 1000
THEN SUBSTR(DIGITS(PARTITION),3,3)
WHEN PARTITION >= 1000
THEN SUBSTR(DIGITS(PARTITION),2,4)
END)
FROM SYSIBM.SYSINDEXPART B
,SYSIBM.SYSINDEXES C
WHERE B.IXCREATOR = C.CREATOR
AND B.IXNAME = C.NAME
AND B.VCATNAME > ' '
AND B.VCATNAME <> '00000001'
;
Joined: 26 Nov 2002 Posts: 12399 Topics: 75 Location: San Jose
Posted: Thu Nov 13, 2025 12:35 pm Post subject:
bauer wrote:
Building the path from the single qualifiers is necessary.
bauer,
I am not sure I understand what you meant by Single Qualifiers? Do you mean you just want based on a table name you provide as an input? _________________ Kolusu
www.linkedin.com/in/kolusu
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
