Results 1 to 2 of 2
  1. #1
    Join Date
    May 2006
    Posts
    9

    Unanswered: DB2 for z/OS & UDF or Stored Procedure

    I have 3 tables and T1,T2,T3 and T1 has a million rows T2 have 300 rows and T3 has 400 Rows. Also I just need only 1 Column from T2 and T3 tables.

    Now, which is the best performed SQL stmts (Response time and I/O)

    Option 1)

    SELECT t1.*,
    t4.Desc,
    t3.StatusDesc
    FROM Product t1,
    Language t2,
    Lookup t3,
    Description t4
    WHERE t1.description_id = t4.ID AND
    t1.Status_id = t3.ID AND
    t2.ID = t4.Language_ID AND
    t2.ID = t3.Language_ID AND
    t2.ID = 'en'

    Option 2)

    SELECT t1.*,
    (SELECT Desc
    FROM Description t4,
    WHERE t4.Language_ID = t2.ID AND
    t1.description_id = t4.ID ) Desc,
    (SELECT StatusDesc
    FROM Lookup t3,
    WHERE t4.Language_ID = t2.ID AND
    t1.Status_id = t3.ID ) StatusDesc,
    FROM Product t1, Language t2
    WHERE t2.ID = 'en'





    Option 3)

    FUNCTION UDF_getDESC(id, language_id)
    {
    SELECT Desc
    FROM Description t4,
    WHERE t4.Language_ID = t2.ID AND
    t1.description_id = t4.ID ;
    return DESC;
    }

    FUNCTION UDF_getStatus(id, language_id)
    {
    SELECT StatusDesc
    FROM Lookup t3,
    WHERE t4.Language_ID = t2.ID AND
    t1.Status_id = t3.ID ;

    return StatusDesc;

    }

    Now,

    SELECT t1.*,
    UDF_getDESC(id, language_id) Desc,
    UDF_getStatus(id, language_id) StatusDesc,
    FROM Product t1, Language t2
    WHERE t2.ID = 'en'

  2. #2
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Did you run Explain on these SQLs to see which option is better choice?

Posting Permissions

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