Tool for building views?

Tim Hare

Tool for building views?

I'm sure this has been asked before (maybe even by me but I can't remember doing so on this list). 
Are there tools that can aid in developing complex views?  We have some applications which have, in my opinion, bad patterns:  they do a SELECT on a table,  loop through a FETCH on those rows issuing SELECT on other tables, sometimes even going three or five layers deep.  These are created by a code-generating program so modifying the source isn't really an option, the code has to be regenerated.  It is my believe that a VIEW accomplishing what those loops do would bring the DB2 optimizer into play in a much better way, and I'd like to do a benchmark of that, but as the VIEW would need to include 30+ tables (if even possible) it becomes kind of hard to code manually (and keep track of nested parantheses etc.). 

We have CA's database tools but I haven't seen a 'view builder' utility;  I haven't looked into the 'encyclopedia' for the code-generator or the CA Repository tool yet to see if those have aids for this.

steen rasmussen

Tool for building views?
(in response to Tim Hare)
Tim - you don't have to look further since we don't have a tool assisting building views. In fact I have never bumped into such a solution, so maybe something I can do when I retire and contribute to my 401K :-)
Steen Rasmussen 
On Thursday, May 16, 2019, 11:45:59 AM CDT, Tim Hare <[login to unmask email]> wrote:


I'm sure this has been asked before (maybe even by me but I can't remember doing so on this list). 
Are there tools that can aid in developing complex views?  We have some applications which have, in my opinion, bad patterns:  they do a SELECT on a table,  loop through a FETCH on those rows issuing SELECT on other tables, sometimes even going three or five layers deep.  These are created by a code-generating program so modifying the source isn't really an option, the code has to be regenerated.  It is my believe that a VIEW accomplishing what those loops do would bring the DB2 optimizer into play in a much better way, and I'd like to do a benchmark of that, but as the VIEW would need to include 30+ tables (if even possible) it becomes kind of hard to code manually (and keep track of nested parantheses etc.). 

We have CA's database tools but I haven't seen a 'view builder' utility;  I haven't looked into the 'encyclopedia' for the code-generator or the CA Repository tool yet to see if those have aids for this.

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

steen rasmussen

Tool for building views?
(in response to Tim Hare)
Tim - you don't have to look further since we don't have a tool assisting building views. In fact I have never bumped into such a solution, so maybe something I can do when I retire and contribute to my 401K :-)
Steen Rasmussen 
On Thursday, May 16, 2019, 11:45:59 AM CDT, Tim Hare <[login to unmask email]> wrote:


I'm sure this has been asked before (maybe even by me but I can't remember doing so on this list). 
Are there tools that can aid in developing complex views?  We have some applications which have, in my opinion, bad patterns:  they do a SELECT on a table,  loop through a FETCH on those rows issuing SELECT on other tables, sometimes even going three or five layers deep.  These are created by a code-generating program so modifying the source isn't really an option, the code has to be regenerated.  It is my believe that a VIEW accomplishing what those loops do would bring the DB2 optimizer into play in a much better way, and I'd like to do a benchmark of that, but as the VIEW would need to include 30+ tables (if even possible) it becomes kind of hard to code manually (and keep track of nested parantheses etc.). 

We have CA's database tools but I haven't seen a 'view builder' utility;  I haven't looked into the 'encyclopedia' for the code-generator or the CA Repository tool yet to see if those have aids for this.

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Michael Hannan

RE: Tool for building views?
(in response to Tim Hare)

Tim,

Essentially you are asking for a complex SQL writer, I think. The SQL Generators generally handle very cut down functionality, for example years ago I worked with COOL:Gen (later called Advantage:Gen) etc. It could do joins, but no Outer Joins and no subqueries. I became well aware of it's limitations. When it could not make the desired SQL, one could code a real SQL separately.

Novel idea to put all the SQL into a View, and have the product use that as though it were a table.

Writing of complex SQLs (with good performance) is a skill. A book on how to do it well might be called for. There was a recent book, but I don't think it goes far enough. Automating the writing of complex SQLs would be worth a fortune perhaps if it could avoid all the pitfalls, and if the results could be human modified when necessary.

Now comes a big problem. A View is not the same as a complex SQL. A complex SQL can have predicates with Host Variables buried deep down inside a subquery possibly nested several levels deep. When you code predicates on  a View, the DB2 Optimizer is quite smart and attempts to push down the predicates as far as it can without changing the meaning of the SQL. Still it may not be capable of pushing the predicates down to the correct subquery desired for good performance. 

Table Functions are an alternative to Views, where you call the Table Function with arguments that can supply the Host Variable values. There are still limitations with the Optimizer chosen access paths, although they have been improved. Not many sites are using Table Functions as yet. I have tried them but my experience with them is still limited, mainly because I can't always guarantee I will be given privileges to create them. I did wish to use them for very complex SQLs. They would scare many sites, just from being something unusual.

I think Views are very well suited to simpler SQLs without too any layers of sub-queries where you can guarantee the predicate push down. For complex SQLs, not really suitable. So a complex View might be untunable due to the predicate pushdown problem.

Writing of complex SQLs can be done well, if the writer understands the Optimizer well enough. I recommend using a series of Common Table Expressions to simplify each stage of the process rather than the old style Nested Subqueries.

Indeed one way to make SQL as efficient as possible is to do all things required for the query transaction in a single Cursor, as far as is practical. Reduction in the total number of SQLs executed can be important for very high volume transactions, but not necessary beyond your top x number of Packages for CPU consumption. When all your indexes are perfect, savings can still be made by SQL call reductions. Multiple SQL calls are allowed and quite suitable for lower cost processes.

Getting back to your aims. You don't want the program doing the table joins, or accessing one table at a time like it was a VSAM File of the old days. Program joins that filter are "Stage 3" predicates (Terry Purcell's term for the worst type of filtering). So you could certainly use Views to incorporate Joins, if your SQL Generator product does not do that. If the View includes Outer Joins predicates on the Left hand tables will be fine, but any predicates on the Outer Join tables won't be easily handled, if can be done at all.

So use Views, yes with Joins, but in a limited way for not so complex SQLs, or even with subqueries if there are no hostvar predicates required in the subquery, e.g. correlated EXISTS. Be careful in case your View will have a worse access path than a standard SQL. You can use Explain to check that.

For performance critical stuff, consider using native SQL instead of the application generator.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 17, 2019 - 06:01 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 17, 2019 - 06:16 AM (Europe/Berlin)

Peter Backlund

Tool for building views?
(in response to Michael Hannan)
Michael,

I memory serves me right, the expression "Stage 3"  was coined by Bonnie Baker

Best regards,

Peter

On 2019-05-17 05:58, Michael Hannan wrote:

Tim,
Essentially you are asking for a complex SQL writer. The SQL Generators generally handle very cut down functionality, for example years ago I worked with COOL:Gen (later called Advantage:Gen) etc. It could do joins, but no Outer Joins and no subqueries. I became well aware of it's limitations. When it could not make the desired SQL, one could code a real SQL separately.
Novel idea to put all the SQL into a View, and have the product use that as though it were a table.
Writing of complex SQLs (with good performance) is a skill. A book on how to do it well might be called for. There was a recent book, but I don't think it goes far enough. Automating the writing of complex SQLs would be worth a fortune perhaps if it could avoid all the pitfalls, and if the results could be human modified when necessary.
Now comes a big problem. A View is not the same as a complex SQL. A complex SQL can have predicates with Host Variables buried deep down inside a subquery possibly nested several levels deep. When you code predicates on  a View, the DB2 Optimizer is quite smart and attempts to push down the predicates as far as it can without changing the meaning of the SQL. Still it may not be capable of pushing the predicates down to the correct subquery desired for good performance. 
Table Functions are an alternative to Views, where you call the Table Function with arguments that can supply the Host Variable values. There are still limitations with the Optimizer chosen access paths, although they have been improved. Not many sites are using Table Functions as yet. I have tried them but my experience with them is still limited, mainly because I can't always guarantee I will be given privileges to create them. I did wish to use them for very complex SQLs. They would scare many sites, just from being something unusual.
I think Views are very well suited to simpler SQLs without too any layers of sub-queries where you can guarantee the predicate push down. For complex SQLs, not really suitable. So a complex View might be untunable due to the predicate pushdown problem.
Writing of complex SQLs can be done well, if the writer understands the Optimizer well enough. I recommend using a series of Common Table Expressions to simplify each stage of the process rather than the old style Nest subqueries.
Indeed one way to make SQL as efficient as possible is to do all things required for the query transaction in a single Cursor, as far as is practical. Reduction in the total number of SQLs executed can be important for very high volume transactions, but not necessary beyond your top x number of Packages for CPU consumption. When all your indexes are perfect, savings can still be made by SQL call reductions. Multiple SQL calls are allowed and quite suitable for lower cost processes.
Getting back to your aims. You don't want the program doing the table joins, or accessing one table at a time like it was a VSAM File of the old days. Program joins that filter are "Stage 3" predicates (Terry Purcell's term for the worst type of filtering). So you could certainly use Views to incorporate Joins, if your SQL Generator product does not do that. If the View includes Outer Joins predicates on the Left hand tables will be fine, but any predicates on the Outer Join tables won't be easily handled, if can be done at all.
So use Views, yes with Joins, but in a limited way for not so complex SQLs, or even with subqueries if there are no hostvar predicates required in the subquery, e.g. correlated EXISTS. Be careful in case your View will have a worse access path than a standard SQL. You can use Explain to check that.
For performance critical stuff, consider using native SQL instead of the application generator.
Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2
--

+--------------------------------+---------------------------------+
| Peter G Backlund | E-mail: [login to unmask email] |
+--------------------------------+---------------------------------+
| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
+--------------------------------+---------------------------------+
| May - September | October - April |
+------------------------------------------------------------------+
| Råsundavägen 94 | Calle Alella 55, Apt 2B |
| S-169 57 SOLNA | 03185 Torrevieja |
| Sweden | Spain |
+--------------------------------+---------------------------------+
| Playing with DB2 since 1981 ... and forever! |
+------------------------------------------------------------------+

bernd oppolzer

Tool for building views?
(in response to Peter Backlund)
"Stage 3" is so obvious ...

I myself used this to describe programs doing the filtering,
when I was in charge to do DB2 classes (including DB2 performance)
here in Germany ... and I never heard anyone using this before, IIRC.

BTW:

"You don't want the program doing the table joins, or accessing one
table at a time like it was a VSAM File of the old days."

We once (mid 1990s) had a real problem with a purchased application ...
ok, it was Oracle, not DB2, but anyway.
The application was written in Fortran, and the SLA was, that a certain
action would not take more
than 20 seconds to complete. But it took 50 seconds (wait time for the
user).

We found out, that there was a join operation involved, but the join was
implemented as nested
Fortran loops. I turned out that the application was moved from a (sort
of) index file system to
Oracle, without changing the access functions :-)

We changed the Fortran join to a real Oracle join, and the elapsed time
dropped down to 5 seconds :-)

Kind regards

Bernd



Am 17.05.2019 um 10:18 schrieb Peter Backlund:
> Michael,
>
> I memory serves me right, the expression "Stage 3"  was coined by
> Bonnie Baker
>
> Best regards,
>
> Peter
>
>
>
> On 2019-05-17 05:58, Michael Hannan wrote:
>>
>> Tim,
>>
>> Essentially you are asking for a complex SQL writer. The SQL
>> Generators generally handle very cut down functionality, for example
>> years ago I worked with COOL:Gen (later called Advantage:Gen) etc. It
>> could do joins, but no Outer Joins and no subqueries. I became well
>> aware of it's limitations. When it could not make the desired SQL,
>> one could code a real SQL separately.
>>
>> Novel idea to put all the SQL into a View, and have the product use
>> that as though it were a table.
>>
>> Writing of complex SQLs (with good performance) is a skill. A book on
>> how to do it well might be called for. There was a recent book, but I
>> don't think it goes far enough. Automating the writing of complex
>> SQLs would be worth a fortune perhaps if it could avoid all the
>> pitfalls, and if the results could be human modified when necessary.
>>
>> Now comes a big problem. A View is not the same as a complex SQL. A
>> complex SQL can have predicates with Host Variables buried deep down
>> inside a subquery possibly nested several levels deep. When you code
>> predicates on  a View, the DB2 Optimizer is quite smart and attempts
>> to push down the predicates as far as it can without changing the
>> meaning of the SQL. Still it may not be capable of pushing the
>> predicates down to the correct subquery desired for good performance.
>>
>> Table Functions are an alternative to Views, where you call the Table
>> Function with arguments that can supply the Host Variable values.
>> There are still limitations with the Optimizer chosen access paths,
>> although they have been improved. Not many sites are using Table
>> Functions as yet. I have tried them but my experience with them is
>> still limited, mainly because I can't always guarantee I will be
>> given privileges to create them. I did wish to use them for very
>> complex SQLs. They would scare many sites, just from being something
>> unusual.
>>
>> I think Views are very well suited to simpler SQLs without too any
>> layers of sub-queries where you can guarantee the predicate push
>> down. For complex SQLs, not really suitable. So a complex View might
>> be untunable due to the predicate pushdown problem.
>>
>> Writing of complex SQLs can be done well, if the writer understands
>> the Optimizer well enough. I recommend using a series of Common Table
>> Expressions to simplify each stage of the process rather than the old
>> style Nest subqueries.
>>
>> Indeed one way to make SQL as efficient as possible is to do all
>> things required for the query transaction in a single Cursor, as far
>> as is practical. Reduction in the total number of SQLs executed can
>> be important for very high volume transactions, but not necessary
>> beyond your top x number of Packages for CPU consumption. When all
>> your indexes are perfect, savings can still be made by SQL call
>> reductions. Multiple SQL calls are allowed and quite suitable for
>> lower cost processes.
>>
>> Getting back to your aims. You don't want the program doing the table
>> joins, or accessing one table at a time like it was a VSAM File of
>> the old days. Program joins that filter are "Stage 3" predicates
>> (Terry Purcell's term for the worst type of filtering). So you could
>> certainly use Views to incorporate Joins, if your SQL Generator
>> product does not do that. If the View includes Outer Joins predicates
>> on the Left hand tables will be fine, but any predicates on the Outer
>> Join tables won't be easily handled, if can be done at all.
>>
>> So use Views, yes with Joins, but in a limited way for not so
>> complex SQLs, or even with subqueries if there are no hostvar
>> predicates required in the subquery, e.g. correlated EXISTS. Be
>> careful in case your View will have a worse access path than a
>> standard SQL. You can use Explain to check that.
>>
>> For performance critical stuff, consider using native SQL instead of
>> the application generator.
>>
>> Michael Hannan,
>> DB2 Application Performance Specialist
>> CPT Global Ltd
>>
>>
>> -----End Original Message-----
>
> --
>
> +--------------------------------+---------------------------------+
> | Peter G Backlund | E-mail:[login to unmask email] |
> +--------------------------------+---------------------------------+
> | Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
> +--------------------------------+---------------------------------+
> | May - September | October - April |
> +------------------------------------------------------------------+
> | Råsundavägen 94 | Calle Alella 55, Apt 2B |
> | S-169 57 SOLNA | 03185 Torrevieja |
> | Sweden | Spain |
> +--------------------------------+---------------------------------+
> | Playing with DB2 since 1981 ... and forever! |
> +------------------------------------------------------------------+
>
> -----End Original Message-----

Michael Hannan

RE: Tool for building views?
(in response to Peter Backlund)

In Reply to Peter Backlund:

Michael,

I memory serves me right, the expression "Stage 3"  was coined by Bonnie Baker

Best regards,

Peter

Thanks Peter,

I would have to ask Terry Purcell if he made it up himself independently or got it from Bonnie Baker. I was attending Terry's presentations instead I guess. I remember learning from Bonnie though, that one reason for high overhead on passing rows to Stage 2 processing, was Data Manager latching and unlatching the page(s) for each row passed back to RDS. A latch has to be such short duration, since there is only one at a time (normally I think) and hence no deadlatch detection. Latch wait has to be kept very low. That was a big highlight for me. Ha ha. Maybe I am weird, but I like technical detail. 

Bernd points out that different people could have coined the term independently too, quite easily.

I often used the term "Death by random I/O" years ago, but had lost all memory of who I pinched that term from. Could have been Joel Goldstein or Chuck Hoover, or ......? Now we have such large BPools, I tend to modify it a bit to "death by random Getpage". Ha ha. Any I/Os are just a by product.

On another Tangent:
Who first invented the scroll reposition method (I first saw it in an early IBM redbook):

AND  COL1 >= :hv1
AND  NOT (COL1 = :hv1 AND COL2 < :hv2)
etc.

This technique is superseded now we have NR access paths, but I still would have liked to know as was useful in the early days, to avoid List Prefetch. Still appears in applications.

I still see some very old suspect techniques in Application SQL, like a column equals itself, and get a surprise.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 18, 2019 - 09:52 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 18, 2019 - 09:55 AM (Europe/Berlin)

Joe Geller

RE: Tool for building views?
(in response to Michael Hannan)

Hi Michael,

You asked:

"

Who first invented the scroll reposition method (I first saw it in an early IBM redbook):

AND  COL1 >= :hv1
AND  NOT (COL1 = :hv1 AND COL2 < :hv2)
etc.

This technique is superseded now we have NR access paths, but I still would have liked to know as was useful in the early days, to avoid List Prefetch."

There are several ways to code scroll repositioning (when you want to fetch a small - say 20 - rows at a time), but they pre-dated Db2 (or even DB2).  It would be the standard way to reposition a multi-column/field key of a VSAM file, IMS database, etc.  And, they did not avoid List Prefetch.  They pre-date List Prefetch in Db2. In fact, List Prefetch defeated this scroll technique when using a non-clustered index.  To avoid List Prefetch (which reads all the index entries and sorts them), Db2 invented OPTIMIZE FOR 1 ROW ONLY.

Joe



In Reply to Michael Hannan:

In Reply to Peter Backlund:

Michael,

I memory serves me right, the expression "Stage 3"  was coined by Bonnie Baker

Best regards,

Peter

Thanks Peter,

I would have to ask Terry Purcell if he made it up himself independently or got it from Bonnie Baker. I was attending Terry's presentations instead I guess. I remember learning from Bonnie though, that one reason for high overhead on passing rows to Stage 2 processing, was Data Manager latching and unlatching the page(s) for each row passed back to RDS. A latch has to be such short duration, since there is only one at a time (normally I think) and hence no deadlatch detection. Latch wait has to be kept very low. That was a big highlight for me. Ha ha. Maybe I am weird, but I like technical detail. 

Bernd points out that different people could have coined the term independently too, quite easily.

I often used the term "Death by random I/O" years ago, but had lost all memory of who I pinched that term from. Could have been Joel Goldstein or Chuck Hoover, or ......? Now we have such large BPools, I tend to modify it a bit to "death by random Getpage". Ha ha. Any I/Os are just a by product.

On another Tangent:
Who first invented the scroll reposition method (I first saw it in an early IBM redbook):

AND  COL1 >= :hv1
AND  NOT (COL1 = :hv1 AND COL2 < :hv2)
etc.

This technique is superseded now we have NR access paths, but I still would have liked to know as was useful in the early days, to avoid List Prefetch. Still appears in applications.

I still see some very old suspect techniques in Application SQL, like a column equals itself, and get a surprise.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Michael Hannan

RE: Tool for building views?
(in response to Joe Geller)



In Reply to Joe Geller:

Hi Michael,

You asked:

"

Who first invented the scroll reposition method (I first saw it in an early IBM redbook):

AND  COL1 >= :hv1
AND  NOT (COL1 = :hv1 AND COL2 < :hv2)
etc.

This technique is superseded now we have NR access paths, but I still would have liked to know as was useful in the early days, to avoid List Prefetch."

There are several ways to code scroll repositioning (when you want to fetch a small - say 20 - rows at a time), but they pre-dated Db2 (or even DB2).  It would be the standard way to reposition a multi-column/field key of a VSAM file, IMS database, etc.  And, they did not avoid List Prefetch.  They pre-date List Prefetch in Db2. In fact, List Prefetch defeated this scroll technique when using a non-clustered index.  To avoid List Prefetch (which reads all the index entries and sorts them), Db2 invented OPTIMIZE FOR 1 ROW ONLY.

Joe

Thanks Joe,

I did not know it was that old. Yes I recall the poor performance with List Prefetch. This technique, I had thought, was specifically to avoid an MX/MU with List Prefetch access path. So I had not thought of List Prefetch defeating it, but yes OPTIMIZE FOR n ROWS was to avoid the List Prefetch with MX/MU paths, and n=1 to avoid all List Prefetch possibility, since we needed the Optimizer to know that a limited set of rows would be fetched. I was never in favor of OPTIMIZE FOR 1 ROW as a general technique. Only for coding the true estimated number of rows. 

Why would the Negative form of coding (with NOTs) be useful for VSAM file access (since it does not have List prefetch and MX/MU access paths)? The more straight forward ways of coding the reposition predicates would seem suited to VSAM. It looked to me like someone invented this technique (applying De Morgan's law) specifically against MX/MU access paths.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd