Translate hierarchical Oracle query to DB2 query

Question

I work primarily with SAS and Oracle and am still new to DB2. Im faced with needing a hierarchical query to separate a clob into chunks that can be pulled into sas. SAS has a limit of 32K for character variables so I cant just pull the dataset in normally.

I found an old stackoverflow question about the best way to pull a clob into a sas data set but it is written in Oracle. Import blob through SAS from ORACLE DB

Since I am new to DB2 and the syntax for this type of join seems very different I was hoping to find someone that could help convert it and explain the syntax. I find the Oracle syntax to be much easier to understand. I'm not sure in DB2 if you would use a CTE recursion like this https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/apsg/src/tpc/db2z_xmprecursivecte.html or if you would use hierarchical queries like this https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/sqlp/rbafyrecursivequeries.htm

Here is the Oracle query.

SELECT    
      id  
    , level as chunk_id
    , regexp_substr(clob_value, '.{1,32767}', 1, level, 'n') as clob_chunk
FROM (
    SELECT id, clob_value
    FROM schema.table
    WHERE id = 1
)
CONNECT BY LEVEL <= regexp_count(clob_value, '.{1,32767}',1,'n')
order by id, chunk_id;

The table has two fields the id and the clob_value and would look like this.

ID    CLOB_VALUE
1     really large clob
2     medium clob
3     another large clob

The thought is I would want this result. I would only ever be doing this one row at a time where id= which ever row I am processing.

ID  CHUNK_ID   CLOB
1   1       clob_chunk1of3
1   2       clob_chunk2of3
1   3       clob_chunk3of3

Thanks for any time spent reading and helping.

Answer 1

Here is a solution that should work in DB2 with few changes (but please be advised that I don't know DB2 at all; I am just using Oracle features that are in the SQL Standard, so they should be implemented identically - or almost so - in DB2).

Below I create a table with your sample data; then I show how to chunk it into substrings of length at most 8 characters. Although the strings are short, I defined the column as CLOB and I am using CLOB tools; this should work on much larger CLOBs.

You can make both the chunk size and the id into bind parameters, if needed. In my demo below I hardcoded the chunk size and I show the result for all IDs in the table. In case the CLOB is NULL, I do return one chunk (which is NULL, of course).

Note that touching CLOBs in a query is very expensive; so most of the work is done without touching the CLOBs. I only work on them as little as possible.

PREP WORK

drop table tbl purge;    -- If needed

create table tbl (id number, clob_value clob);

insert into tbl (id, clob_value)
  select 1, 'really large clob'  from dual union all
  select 2, 'medium clob'        from dual union all
  select 3, 'another large clob' from dual union all
  select 4, null                 from dual             -- added to check handling
;

commit;

QUERY

with
  prep(id, len) as (
    select id, dbms_lob.getlength(clob_value)
    from   tbl
  )
,  rec(id, len, ord, pos) as (
    select  id, len, 1, 1
      from  prep
    union all
    select  id, len, ord + 1, pos + 8
      from  rec
      where len >= pos + 8
  )
select   id, ord, dbms_lob.substr(clob_value, 8, pos)
from     tbl inner join rec using (id)
order by id, ord
;

  ID  ORD CHUNK   
---- ---- --------
   1    1 really l
   1    2 arge clo
   1    3 b       
   2    1 medium c
   2    2 lob     
   3    1 another 
   3    2 large cl
   3    3 ob      
   4    1        

Answer 2

Another option is to enable the Oracle compatibility in Db2 and just issue the hierarchical query.

This GitHub repository has background information on SQL recursion in DB2, including the Oracle-style syntax and a side by side example (both work against the Db2 sample database):

-- both queries are against the SAMPLE database
-- and should return the same result
SELECT LEVEL, CAST(SPACE((LEVEL - 1) * 4) || '/' || DEPTNAME
       AS VARCHAR(40)) AS DEPTNAME
FROM DEPARTMENT
     START WITH DEPTNO = 'A00'
     CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPT;


WITH tdep(level, deptname, deptno) as (
    SELECT 1, CAST( DEPTNAME AS VARCHAR(40)) AS DEPTNAME, deptno
    FROM department 
    WHERE DEPTNO = 'A00'
    UNION ALL
    SELECT t.LEVEL+1, CAST(SPACE(t.LEVEL  * 4) || '/' || d.DEPTNAME
       AS VARCHAR(40)) AS DEPTNAME, d.deptno
    FROM DEPARTMENT d, tdep t
    WHERE d.admrdept=t.deptno and d.deptno<>'A00')
SELECT level, deptname
FROM tdep;