DB2 Query to get object folder path

[Klaus Lintz]

Hi Team

I require a DB2 query to get the folder path for an object.

I require this for IMPORT objects on a different system.  Transport case is not an option due to customer security policy

Below is my attempt but its not working.

AEADMIN is the the schema

-------------------------------------------------------------------------------------------------------------------------------

WITH recursive AllFolderPaths (folder, lvl, OFS_OH_IDNR_O, OFS_OH_IDNR_F) AS (
    -- Anchor part: starting node
SELECT 
        SUBSTR(OH.OH_NAME, LOCATE('\', OH.OH_NAME) + 1) AS folder,
        1 AS lvl,
        OFS.OFS_OH_IDNR_O,
        OFS.OFS_OH_IDNR_F
    FROM 
        AEADMIN.OFS OFS
        JOIN AEADMIN.OH OH ON OFS.OFS_OH_IDNR_O = OH.OH_IDNR
    WHERE 
        OFS.OFS_OH_IDNR_O = (
            SELECT 
                OFS.OFS_OH_IDNR_O
            FROM 
                AEADMIN.OFS OFS
                JOIN AEADMIN.OH OH ON OFS.OFS_OH_IDNR_O = OH.OH_IDNR
            WHERE 
                OH.OH_NAME = 'TEST1_JOB1' AND 
                OH.OH_CLIENT = 120
        )
    
    UNION ALL
    
    -- Recursive part: walk the tree
    SELECT 
        SUBSTR(OH.OH_NAME, LOCATE('\', OH.OH_NAME) + 1) AS folder,
        H.lvl + 1,
        OFS.OFS_OH_IDNR_O,
        OFS.OFS_OH_IDNR_F
    FROM 
        AEADMIN.OFS OFS
        JOIN AEADMIN.OH OH ON OFS.OFS_OH_IDNR_O = OH.OH_IDNR
        JOIN AllFolderPaths H ON OFS.OFS_OH_IDNR_O = H.OFS_OH_IDNR_F
)
SELECT 
    '\' || LISTAGG(folder, '\') WITHIN GROUP (ORDER BY lvl DESC) AS full_path
FROM 
    AllFolderPaths;

-------------------------------------------------------------------------------------------------------------------

Thank you 

Klaus

[Philipp Elmer]

Hi Klaus,

I don't have DB2, but here are SQLs für MSSQL and Oracle that do what you require, hopefully these help you to find a version that runs on DB2:

--Oracle
with objectpath (OH_IDNR, OH_NAME, LVL) as (
	--the concatenation is autmatically cast to a large enough datatype
	select OH_IDNR '\' || OH_Name, 0
	from OH
	where OH_Client = 0
	and OH_Name = 'UC_SYSTEM_SETTINGS'
	UNION ALL
	select OFS_OH_IDNR_F,
	--the 3rd parameter for the length is optional in SUBSTR
	SUBSTR(OH.OH_Name, INSTR(OH.OH_Name, '\')) ||
	objectpath.OH_Name,
	LVL + 1
	from objectpath
	inner join OFS on objectpath.OH_Idnr = OFS_OH_Idnr_O
	inner join OH on OFS_OH_IDNR_F = OH.OH_Idnr
)
--Oracle has no TOP operator,
--a derived table with a filter on rownum in the outer qurey does the job
select * from (
	select OH_Name
	from objectpath
	order by LVL desc
) where rownum = 1;

--MSSQL
with objectpath (OH_IDNR, OH_NAME, LVL) as (
	--the concatenation needs to be CAST explicitly to a VARCHAR(MAX)
	select OH_IDNR, CAST('\' + OH_Name AS VARCHAR(MAX)), 0
	from OH
	where OH_Client = 0
	and OH_Name = 'UC_SYSTEM_SETTINGS'
	UNION ALL
	select OFS_OH_IDNR_F, 
	--SUBSTRING requires the 3rd paramter for length
	--Hardcoded to 200 because OH.OH_NAME is a VARCHAR(200)
	SUBSTRING(OH.OH_Name, CHARINDEX('\', OH.OH_Name), 200) + 
		objectpath.OH_Name, LVL + 1
	from objectpath
	inner join OFS on objectpath.OH_Idnr = OFS_OH_Idnr_O
	inner join OH on OFS_OH_IDNR_F = OH.OH_Idnr
)
--TOP 1 -> get the top record ordered by level descending
--This ist the record from the highest LVL, where the path is complete
select TOP 1 OH_Name
from objectpath 
order by LVL desc;

------------------------------
Philipp Elmer

Learn Automic!
https://www.pemautomic.com
------------------------------

[Klaus Lintz]

Hi Philipp 

Thank you.

I struggling to convert it to DB2

Regards

Klaus

[Philipp Elmer]

Hi Klaus,

I don't have DB2 so I can't really adapt this myself.

I asked ChatGPT to convert my Oracle-SQL to DB2, this is what the AI came up with, maybe it helps:

WITH RECURSIVE objectpath (OH_IDNR, OH_NAME, LVL) AS (
    -- Anchor member
    SELECT OH_IDNR, OH_Name, 0
    FROM OH
    WHERE OH_Client = 0
      AND OH_Name = 'UC_SYSTEM_SETTINGS'
    UNION ALL
    -- Recursive member
    SELECT OFS.OFS_OH_IDNR_F,
           SUBSTR(OH.OH_Name, LOCATE('\', OH.OH_Name)) || objectpath.OH_NAME,
           LVL + 1
    FROM objectpath
    INNER JOIN OFS ON objectpath.OH_IDNR = OFS.OFS_OH_IDNR_O
    INNER JOIN OH ON OFS.OFS_OH_IDNR_F = OH.OH_IDNR
)
SELECT OH_NAME
FROM objectpath
ORDER BY LVL DESC
FETCH FIRST 1 ROW ONLY;

------------------------------
Philipp Elmer

Learn Automic!
https://www.pemautomic.com
------------------------------

[Klaus Lintz]

Hi Phillip

I get the following error using the ChatGTP version

Regards

Klaus

[Philipp Elmer]

I recommend trying it in a db client tool before using it in AWI.

------------------------------
Philipp Elmer

Learn Automic!
https://www.pemautomic.com
------------------------------