IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 6 replies
  • Latest Post - ‏2017-07-20T09:27:54Z by bogeybetsy
bogeybetsy
bogeybetsy
16 Posts

Pinned topic "Modular" SQL

‏2017-07-17T03:33:44Z |

Hi,

 

We have the following SQL statement:

 

Select columnA, columnB, etc., etc.,...

   from fileA

   join fileX

      on  fileX.columnC = fileA.columnC

   join fileY

      on  fileY.columnD = fileX.columnD

   join fileZ

      on  fileZ.columnE = fileZ.columnD

   .

   .

   .

   where ...

 

Then we have other files, like fileB, fileC, fileD, etc., etc.,... to which the JOIN part starting from fileX can be used. For example:

 

Select columnA, columnB, etc., etc.,...

   from fileB

   join fileX

      on  fileX.columnC = fileB.columnC

   join fileY

      on  fileY.columnD = fileX.columnD

   join fileZ

      on  fileZ.columnE = fileZ.columnD

   .

   .

   .

   where ...

 

Now...Is there a way to make the join parts from fileX to file Z reusable -  that is, not having to code them for different files to which the JOIN may be applicable?

 

I understand I can use an SQL cursor for fileA, fileB, etc., etc., and perform the logic on those JOIN parts for each row of fileA, fileB, etc., etc., but that would be like NOT choosing set-based logic at all.

 

Thanks

 

 

  • 107D_Dieter_Schroeder
    10 Posts
    ACCEPTED ANSWER

    Re: "Modular" SQL

    ‏2017-07-19T06:47:42Z  

    Hi Allan,

    i think that there is no difference in performance whether you encapsulate sql-selects into a view or not. A view only shortens your sql script. 

    Creating a SQL UDTF of course is more complex than creating a view. You have to code the udtf, so you will control, how the udtf works. You can write it in sql or in RPG. I can't say if that an udtf will perform better or less than a sql script. It depends on how you code it.

    In our company we have a few udtfs. We create an udtf if the solution in sql isn't possible or if the code in sql would be to complex.

    Dieter

  • B.Hauser
    B.Hauser
    320 Posts
    ACCEPTED ANSWER

    Re: "Modular" SQL

    ‏2017-07-20T05:53:18Z  

    Hi Allan,

    i think that there is no difference in performance whether you encapsulate sql-selects into a view or not. A view only shortens your sql script. 

    Creating a SQL UDTF of course is more complex than creating a view. You have to code the udtf, so you will control, how the udtf works. You can write it in sql or in RPG. I can't say if that an udtf will perform better or less than a sql script. It depends on how you code it.

    In our company we have a few udtfs. We create an udtf if the solution in sql isn't possible or if the code in sql would be to complex.

    Dieter

    If an (SQL) UDTF consists only of a single statement (RETURN ... SELECT) the Query Optimizer can handle an UDTF like a view (i.e. it can analyze the SELECT statement and embedd it in the SQL statement that is optimized after.

    If an (SQL) UDTF consists of multiple statements, an UDTF is a black box, so it might perform worse

    If it can be done with a view, use a view.

    For to get the best performance you should provide the right indexes

    Birgitta

  • 107D_Dieter_Schroeder
    10 Posts

    Re: "Modular" SQL

    ‏2017-07-18T09:46:41Z  

    Hi,

    i would suggest to define a view which contains the joins. Then you only have to code a single join (a join to the view). 

    Dieter

  • bogeybetsy
    bogeybetsy
    16 Posts

    Re: "Modular" SQL

    ‏2017-07-18T19:18:59Z  

    Hi,

    i would suggest to define a view which contains the joins. Then you only have to code a single join (a join to the view). 

    Dieter

    Hi Dieter,

     

    I was thinking about that.  But would performance be any issue?  I mean, in the WHERE clause are columns from fileX, fileY and fileZ (equated to host variables) together with columns from fileA/fileB.  When I change the join clauses for those files with a VIEW, I would be changing the WHERE clause columns with the VIEW columns. Or is it just the same thing, that is, the indexes that work on the files fileX, fileY and fileZ will work on the VIEW?

     

    Or how about a SQL UDTF?  (Just not sure if we are allowed to create SQL UDTFs)

     

    Thank you,

     

    Allan

    Updated on 2017-07-18T19:39:02Z at 2017-07-18T19:39:02Z by bogeybetsy
  • 107D_Dieter_Schroeder
    10 Posts

    Re: "Modular" SQL

    ‏2017-07-19T06:47:42Z  

    Hi Allan,

    i think that there is no difference in performance whether you encapsulate sql-selects into a view or not. A view only shortens your sql script. 

    Creating a SQL UDTF of course is more complex than creating a view. You have to code the udtf, so you will control, how the udtf works. You can write it in sql or in RPG. I can't say if that an udtf will perform better or less than a sql script. It depends on how you code it.

    In our company we have a few udtfs. We create an udtf if the solution in sql isn't possible or if the code in sql would be to complex.

    Dieter

  • bogeybetsy
    bogeybetsy
    16 Posts

    Re: "Modular" SQL

    ‏2017-07-19T08:08:23Z  

    Hi Allan,

    i think that there is no difference in performance whether you encapsulate sql-selects into a view or not. A view only shortens your sql script. 

    Creating a SQL UDTF of course is more complex than creating a view. You have to code the udtf, so you will control, how the udtf works. You can write it in sql or in RPG. I can't say if that an udtf will perform better or less than a sql script. It depends on how you code it.

    In our company we have a few udtfs. We create an udtf if the solution in sql isn't possible or if the code in sql would be to complex.

    Dieter

    Thank you, Dieter.  I will try the VIEW. Smile

  • B.Hauser
    B.Hauser
    320 Posts

    Re: "Modular" SQL

    ‏2017-07-20T05:53:18Z  

    Hi Allan,

    i think that there is no difference in performance whether you encapsulate sql-selects into a view or not. A view only shortens your sql script. 

    Creating a SQL UDTF of course is more complex than creating a view. You have to code the udtf, so you will control, how the udtf works. You can write it in sql or in RPG. I can't say if that an udtf will perform better or less than a sql script. It depends on how you code it.

    In our company we have a few udtfs. We create an udtf if the solution in sql isn't possible or if the code in sql would be to complex.

    Dieter

    If an (SQL) UDTF consists only of a single statement (RETURN ... SELECT) the Query Optimizer can handle an UDTF like a view (i.e. it can analyze the SELECT statement and embedd it in the SQL statement that is optimized after.

    If an (SQL) UDTF consists of multiple statements, an UDTF is a black box, so it might perform worse

    If it can be done with a view, use a view.

    For to get the best performance you should provide the right indexes

    Birgitta

  • bogeybetsy
    bogeybetsy
    16 Posts

    Re: "Modular" SQL

    ‏2017-07-20T09:27:54Z  
    • B.Hauser
    • ‏2017-07-20T05:53:18Z

    If an (SQL) UDTF consists only of a single statement (RETURN ... SELECT) the Query Optimizer can handle an UDTF like a view (i.e. it can analyze the SELECT statement and embedd it in the SQL statement that is optimized after.

    If an (SQL) UDTF consists of multiple statements, an UDTF is a black box, so it might perform worse

    If it can be done with a view, use a view.

    For to get the best performance you should provide the right indexes

    Birgitta

    Hi Birgitta,

     

    I actually downloaded your article on UDTFs..."The power of user defined table functions" and read Michael Sansoterra's IT Jungle article "DB2 For i Table Function Performance Considerations".  And yes, I have decided on using a VIEW.

     

    Cheers to you and Michael!

     

    Allan