Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2012
    Posts
    30

    Post Unanswered: SQL Cursor vs XMLAGG(?)

    Hi all!

    The purpose is to get concatenated string from varchar field.

    There're 2 ways - use combination of XML2CLOB(XMLAGG(XMLELEMENT(?)))
    or use cursor with looping throgh each sql query row. (or may be there's some db2 built-in function)

    Which way will get more performance?

    Here's sample code?

    Code:
    CREATE FUNCTION DLIB.Concat_Subjects(pDocid CHAR(16) FOR BIT
     DATA, IN pLen INT)
    
    -- 
    
    RETURNS VARGRAPHIC(500)
    
    BEGIN
      
    
      DECLARE len	int;--
    
      DECLARE subjects VARGRAPHIC(200);--
    
      SET subjects = (SELECT REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME a, s.name))),'<A>',' '),'</A>',',') 
    
    	 	 FROM DLIB.dl_docsubjects ds INNER JOIN DLIB.dl_subjects s ON s.id = ds.subjectid WHERE ds.documentid = pDocid);--
    
    
    
      SET len = LENGTH(subjects);--
    
    
      -- remove start space & ending comma
    
      SET subjects = SUBSTRING(subjects, 2, LEN - 2, CODEUNITS16);--
    
    
    
      IF (pLen > 0) AND (len > pLen) THEN
    
        SET subjects = SUBSTRING(subjects, 1, pLen, CODEUNITS16)||'...';--
    
      END IF;--
    
    
    
      RETURN subjects;--
    
    END;

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release/fixpack and platform OS are you using?

    There're 2 ways - use combination of XML2CLOB(XMLAGG(XMLELEMENT(?)))
    or use cursor with looping throgh each sql query row. (or may be there's some db2 built-in function)
    Other ways are LISTAGG or recursive common table expression.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You should not define the intermittent variables except when the expression(variable) was referenced more than once in your routine.
    I leaned the principle in older programmming languages too, like Assembler for System/360, PL/I or C so on...
    And I thought the principle was rational.
    More generally, it might be a habit in mathematics.

    Anyway,
    your code might be not so appropriate in developing/maintenance and execution efficiency.

    I prefer to use nested expressions by ommitting the intermittent variables, like...
    Code:
    CREATE FUNCTION DLIB.Concat_Subjects
    ( pDocid CHAR(16) FOR BIT DATA
    , IN pLen INT
    )
    RETURNS VARGRAPHIC(500)
    
    RETURN
    SELECT LEFT(
              SUBSTR(
                 REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME a, s.name))),'<A>',' '),'</A>',',')
               , 2
              )
            , CASE WHEN pLen > 0 THEN pLen ELSE 500 END
           )
     FROM  DLIB.dl_docsubjects ds
     INNER JOIN
           DLIB.dl_subjects    s
      ON   s.id = ds.subjectid
     WHERE ds.documentid = pDocid
     GROUP BY
           pLen
    ;

  4. #4
    Join Date
    Oct 2012
    Posts
    30
    Quote Originally Posted by tonkuma View Post
    What DB2 version/release/fixpack and platform OS are you using?


    Other ways are LISTAGG or recursive common table expression.
    I'm using DB2 v.9.7 with win 2008 Server x 64

    p.s. Recursive expressions cost more expensive performance, i need less.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Recursive expressions cost more expensive performance, ...
    Are you shure that are always right?
    (any nymber of rows in a group, any length of a column to be aggregated, so on...)
    Do you have any examples/proofs with your insistence?

    I'm using DB2 v.9.7 ...
    So, LISTAGG may be a choice for you, especially if you are considering performance.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The following example might be one of the simpler ways, as far as I know now.

    A question was left for me.
    The question was what result do you want, if pLen was less than zero?
    Code:
    CREATE FUNCTION DLIB.Concat_Subjects
    ( pDocid CHAR(16) FOR BIT DATA
    , pLen   INT
    )
    RETURNS VARGRAPHIC(500)
    NO EXTERNAL ACTION
    
    RETURN
    SELECT LISTAGG(s.name , ', ')
     FROM  DLIB.dl_docsubjects ds
     INNER JOIN
           DLIB.dl_subjects    s
      ON   s.id = ds.subjectid
     WHERE ds.documentid = pDocid
     GROUP BY
           pLen;
    I'm not sure wheather the following clause was neccesary or not?
    Code:
     GROUP BY
           pLen;

    Anyway,
    the foundamental thinking might be not so far from an answer.
    Last edited by tonkuma; 07-20-13 at 14:34.

  7. #7
    Join Date
    Oct 2012
    Posts
    30
    Quote Originally Posted by tonkuma View Post
    A question was left for me.
    The question was what result do you want, if pLen was less than zero?
    If param
    1. 500 > pLen > 0 - result length = pLen
    2. pLen <= 0 - result length = 500

    Code:
    CREATE FUNCTION DLIB.Concat_Subjects(pDocid CHAR(16) FOR BIT
     DATA, IN pLen INT)
    
    -- 
    
    RETURNS VARGRAPHIC(500)
    
    BEGIN
      
    
      DECLARE len	int;--
    
      DECLARE subjects VARGRAPHIC(500);--
    
      SET subjects = (SELECT LEFT(LISTAGG(s.name, ', '), 500, CODEUNITS16) FROM DLIB.dl_docsubjects ds JOIN DLIB.dl_subjects s ON s.id = ds.subjectid WHERE ds.documentid = pDocid);--
    
    
    
      SET len = LENGTH(subjects);--
    
      IF (pLen > 0) AND (len > pLen) THEN
    
        SET subjects = LEFT(subjects, pLen, CODEUNITS16)||'...';--
    
      END IF;--
    
    
    
      RETURN subjects;--
    
    END;

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that I might be understand the reason why you want to use temporary variables(i.e. len and subjects).
    You want to add '...', if the result(subjects) was truncated.

    Here is an example generating the result by a statement.

    Example 1:
    Code:
    CREATE FUNCTION DLIB.Concat_Subjects
    ( pDocid CHAR(16) FOR BIT DATA
    , pLen   INT
    )
    RETURNS VARGRAPHIC(500)
    NO EXTERNAL ACTION
    RETURN 
    SELECT CASE
           WHEN pLen > 0
            AND LENGTH(subjects) > pLen THEN
                LEFT(subjects , pLen , CODEUNITS16) || '...'
           ELSE subjects
           END
     FROM  (SELECT LISTAGG(s.name , ', ') AS subjects
             FROM  DLIB.dl_docsubjects ds
             INNER JOIN
                   DLIB.dl_subjects    s
              ON   s.id = ds.subjectid
             WHERE ds.documentid = pDocid
           ) AS s
    ;
    Last edited by tonkuma; 08-03-13 at 14:05. Reason: Replace function body(remove norn_pLen)

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Black Moon View Post
    If param
    1. 500 > pLen > 0 - result length = pLen
    2. pLen <= 0 - result length = 500
    I thought the result length might be sometimes inconsistent with your code
    Code:
    IF (pLen > 0) AND (len > pLen) THEN
    
        SET subjects = LEFT(subjects, pLen, CODEUNITS16)||'...';--
    In that case result length might be pLen + 3(not pLen in 1.).


    And, I'm curious to know how to do if pLen > 500.
    3-1. pLen > 500 and LENGTH(subjects) < 500 - (may be) result length = 500

    3-2. pLen > 500 and pLen > LENGTH(subjects) > 500 - ?
    Note: You specified RETURNS VARGRAPHIC(500).
    So, returned value(i.e. subjects) must be truncated to length = 500 without trailing '...'.

    3-3. pLen > 500 and LENGTH(subjects) > pLen - ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •