Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS

Norbert Wolf

Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
Hello,

In our company we have many application programmers using Visual Explain (VE) with DB2 Version 9 NFM. Some of them are explaining only SQL-statements, where they do expect performance problems and few of them are using the very helpful filtering features of VE to filter out only the static SQL statements, that exceeds some cost limits.

The most of them are happy with VE - because it is easy to understand and use - and they don't want to change to Optimization Service center or Datastudio. These new tools (OSC and DATASTUDIO) - OSC will be deprecated in the future - have many, many feature. But if you use that tools not permanently, it is really not easy for developers to find exactly the features - for example filtering of SQL - they do need. The effect is, that they are frustated and they are not using the new tools.

What are our experiences with DATASTUDIO as a tool for developers and DBAs to identify access path problems in advance?


Norbert Wolf

Datev eG

Nürnberg

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Myron Miller

Re: Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
(in response to Norbert Wolf)
Norbert,
At my client, my programmers have been using VE for some time very
successfully as well. I have moved a few of the more advanced programmers
(technically) to OSC. And they can use it pretty well as well.

So far, I have no one on DataStudio (DS) nor in the interim do I plan to, even
though that WILL BE THE ONLY TOOL IBM Supports on DB2 V10. I'm using the very
latest version of DS 2.2.1. It's a lot lot better than earlier versions but I'm
still finding lots of bugs in it and a number of confusing parts, especially
when I switch from the data studio development side (like creating/working with
stored procedures) to the query tuning side of Data Studio.

To be honest, in my own opinion (and these are my own and do not reflect
management, etc. etc), Data Studio is not ready for prime time. It's not ready
for casual programmers to use. It's too buggy and inconsistent in its
interfaces and definitely not as easy to use as VE was.

Yes, it has a ton more features than VE, but the implementation was NOT well
implemented for simplicity, in my personal opinion. And the footprint is much
larger on the PC than VE and it takes lots more resources, both on the PC and
the mainframe.

IBM is well aware of my opinions and issues with this tool.

If you're moderately experienced with tuning and very patient, you can most of
the time, do a lot of significant tuning with Data Studio. But not all queries
can be tuned with Data Studio currently. Some do not work well. But some of
the new features do provide very useful and informative mechanisms for query
tuning. Query annotation that shows how DB2 rewrites the query and processes
that rewritten query. That can be extremely handy for complex queries.

That's just my experience for what it's worth. Your mileage may vary. And of
course, everyone knows I'm highly opinionated.

Myron





________________________________
From: Norbert Wolf <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, January 21, 2011 8:21:47 AM
Subject: [DB2-L] Visual Explain, OSC or Datastudio for Explain Analysis in DB2
z/OS

Hello,

In our company we have many application programmers using Visual Explain (VE)
with DB2 Version 9 NFM. Some of them are explaining only SQL-statements, where
they do expect performance problems and few of them are using the very helpful
filtering features of VE to filter out only the static SQL statements, that
exceeds some cost limits.

The most of them are happy with VE - because it is easy to understand and use -
and they don't want to change to Optimization Service center or Datastudio.
These new tools (OSC and DATASTUDIO) - OSC will be deprecated in the future -
have many, many feature. But if you use that tools not permanently, it is really
not easy for developers to find exactly the features - for example filtering of
SQL - they do need. The effect is, that they are frustated and they are not
using the new tools.

What are our experiences with DATASTUDIO as a tool for developers and DBAs to
identify access path problems in advance?



Norbert Wolf

Datev eG

Nürnberg

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the
home of IDUG's Listserv


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Richard Gugel

Re: Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
(in response to Myron Miller)
Myron

Your answer was spot on.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Max Scarpa

Re: Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
(in response to Richard Gugel)
I'm using Data Studio since some weeks for Db2 Express-C (the latest
available) and I agree, it 'heavy'. I realized immediately it as my
notebook was slower in every action I did using dataStudio. It crashed
twice, so probably Myron is quite right when he says there are some bugs
(I'm using windows 7)..
And I agree with him, my impression is it's not user friendly in managing
DB2 object and development, I think GUI interface is too 'rich' and can be
confusing for inexperienced or 'young' developer.
At the very end I had the same impression when using first versions of
OSC, but it looks more promising for the future. And if it's the only
tool supporting DB2 10.......you're freely obliged to use it...

Just my 0,0002 euro cents experience

Max Scarpa


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Leslie

Re: Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
(in response to Max Scarpa)
Myron

you opinionated .. never!

I think you worded this well.

I have not used DataStudio yet but my opinion is the same as yours for OSC,
it works, it is stable and it offers some nice features that work well. It
is a shame the index advice is not given away free , but otherwise the
product works well.

Over the years I have seen many attempts by IBM to write GUI interface tools
to z/Os and I'm yet to see one as stable as OSC ....

I would push everyone towards OSC for the time being if it was me.

Leslie

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: 21 January 2011 13:57
To: [login to unmask email]
Subject: Re: [DB2-L] Visual Explain, OSC or Datastudio for Explain Analysis
in DB2 z/OS


Norbert,
At my client, my programmers have been using VE for some time very
successfully as well. I have moved a few of the more advanced programmers
(technically) to OSC. And they can use it pretty well as well.

So far, I have no one on DataStudio (DS) nor in the interim do I plan to,
even though that WILL BE THE ONLY TOOL IBM Supports on DB2 V10. I'm using
the very latest version of DS 2.2.1. It's a lot lot better than earlier
versions but I'm still finding lots of bugs in it and a number of confusing
parts, especially when I switch from the data studio development side (like
creating/working with stored procedures) to the query tuning side of Data
Studio.

To be honest, in my own opinion (and these are my own and do not reflect
management, etc. etc), Data Studio is not ready for prime time. It's not
ready for casual programmers to use. It's too buggy and inconsistent in its
interfaces and definitely not as easy to use as VE was.

Yes, it has a ton more features than VE, but the implementation was NOT well
implemented for simplicity, in my personal opinion. And the footprint is
much larger on the PC than VE and it takes lots more resources, both on the
PC and the mainframe.

IBM is well aware of my opinions and issues with this tool.

If you're moderately experienced with tuning and very patient, you can most
of the time, do a lot of significant tuning with Data Studio. But not all
queries can be tuned with Data Studio currently. Some do not work well.
But some of the new features do provide very useful and informative
mechanisms for query tuning. Query annotation that shows how DB2 rewrites
the query and processes that rewritten query. That can be extremely handy
for complex queries.

That's just my experience for what it's worth. Your mileage may vary. And
of course, everyone knows I'm highly opinionated.

Myron



_____

From: Norbert Wolf <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, January 21, 2011 8:21:47 AM
Subject: [DB2-L] Visual Explain, OSC or Datastudio for Explain Analysis in
DB2 z/OS

Hello,

In our company we have many application programmers using Visual Explain
(VE) with DB2 Version 9 NFM. Some of them are explaining only
SQL-statements, where they do expect performance problems and few of them
are using the very helpful filtering features of VE to filter out only the
static SQL statements, that exceeds some cost limits.

The most of them are happy with VE - because it is easy to understand and
use - and they don't want to change to Optimization Service center or
Datastudio. These new tools (OSC and DATASTUDIO) - OSC will be deprecated in
the future - have many, many feature. But if you use that tools not
permanently, it is really not easy for developers to find exactly the
features - for example filtering of SQL - they do need. The effect is, that
they are frustated and they are not using the new tools.

What are our experiences with DATASTUDIO as a tool for developers and DBAs
to identify access path problems in advance?


Norbert Wolf

Datev eG

Nürnberg

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv


_____


< http://www.idug.org > Independent, not-for-profit, User Run - the IDUG
difference!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Roy Boxwell

Re: Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
(in response to Leslie)
That reminds me of a song - "He aint heavy - he's my performance analyser"
or some such....but I also agree - trying to find VE in the Data Studio is
quite fun and the install is pretty "hairy" - I lost all my defs for host
DB2 but they are still "there" - I have to define new dummy ones to
actually connect...I would be a very happy bunny if they just re-released
Visual Explain - Wonderfully simple to use - did everything I wanted it to
do and was small!! Less than 138MB's of space and FAST! Perhaps IBM are in
cahoots with Microsoft and Intel to force us all to upgrade to Windows 7
64 Bit and then upgrade all of our PCs so that they run faster than a
crawl and then upgrade all of our graphics cards because they are
incompatible and then upgrade all of our .... repeat until you get bored!

Try not to think about this until Monday...recursive loops on fridays are
bad for the head! Have a great weekend!

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Max Scarpa <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
21.01.2011 15:56
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
Re: [DB2-L] Visual Explain, OSC or Datastudio for Explain Analysis in DB2
z/OS






I'm using Data Studio since some weeks for Db2 Express-C (the latest
available) and I agree, it 'heavy'. I realized immediately it as my
notebook was slower in every action I did using dataStudio. It crashed
twice, so probably Myron is quite right when he says there are some bugs
(I'm using windows 7)..
And I agree with him, my impression is it's not user friendly in managing
DB2 object and development, I think GUI interface is too 'rich' and can be
confusing for inexperienced or 'young' developer.
At the very end I had the same impression when using first versions of
OSC, but it looks more promising for the future. And if it's the only
tool supporting DB2 10.......you're freely obliged to use it...

Just my 0,0002 euro cents experience

Max Scarpa




The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Ron Johnson

Re: Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
(in response to Roy Boxwell)
I agree with a lot of what Myron and Roy have said. Our developers are using VE. It works well and is easy to use. When I introduced VE, OSC was just becoming available and I thought VE was the way to go at the time. I have been using Data Studio lately since it seems to be the future for this task. The latest version is better than it was but I still like VE best for the developers. I was going to offer Data Studio to the developers but actually was held up with some Java install/update issues with our PC support people. We are still on V8 with plans for V9 next month so obviously we don't have to make the move right away. I was just trying to get this area up to speed while I had some time. It is interesting to hear others having the same opinion.

Ron Johnson
Data-Tronics Corp (ABF Freight System)

________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Roy Boxwell
Sent: Friday, January 21, 2011 9:17 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS



That reminds me of a song - "He aint heavy - he's my performance analyser" or some such....but I also agree - trying to find VE in the Data Studio is quite fun and the install is pretty "hairy" - I lost all my defs for host DB2 but they are still "there" - I have to define new dummy ones to actually connect...I would be a very happy bunny if they just re-released Visual Explain - Wonderfully simple to use - did everything I wanted it to do and was small!! Less than 138MB's of space and FAST! Perhaps IBM are in cahoots with Microsoft and Intel to force us all to upgrade to Windows 7 64 Bit and then upgrade all of our PCs so that they run faster than a crawl and then upgrade all of our graphics cards because they are incompatible and then upgrade all of our .... repeat until you get bored!

Try not to think about this until Monday...recursive loops on fridays are bad for the head! Have a great weekend!

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Max Scarpa <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>

21.01.2011 15:56
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie
Thema
Re: [DB2-L] Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS






I'm using Data Studio since some weeks for Db2 Express-C (the latest available) and I agree, it 'heavy'. I realized immediately it as my notebook was slower in every action I did using dataStudio. It crashed twice, so probably Myron is quite right when he says there are some bugs (I'm using windows 7)..
And I agree with him, my impression is it's not user friendly in managing DB2 object and development, I think GUI interface is too 'rich' and can be confusing for inexperienced or 'young' developer.
At the very end I had the same impression when using first versions of OSC, but it looks more promising for the future. And if it's the only tool supporting DB2 10.......you're freely obliged to use it...

Just my 0,0002 euro cents experience

Max Scarpa



________________________________


< http://www.idug.org/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



________________________________


Independent, not-for-profit, User Run - the IDUG difference! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Terry Purcell

Re: Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
(in response to Ron Johnson)
As someone who also has a vested interest in the query tooling (because it impacts the DB2 for z/OS optimizer experience) - I shared this thread with the Data Studio Development team.

I was pleased to hear that they are already actively engaged with Myron and other customers on these issues. Earlier feedback resulted in an improved interface for Data Studio - so you know the team is listening.

With regard to the VE to OSC to Data Studio evolution - there is a logical explanation. The bottom line is that for integration with other IBM products (database, developer, data architect tools, monitors etc) and to deliver cross platform tooling, we needed to have a consistent (and IBM standard) architecture. To go through an explanation in more detail is too much to cover in this reply. But I would be happy to share that offline.

Regards
Terry Purcell

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Edward Long

Re: Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
(in response to Terry Purcell)
Please don't forget the DB2/PE explain in this discussion.
I have a PMR open presently because PE and OSC/Data Studio disagree on an access path.
Of the 4, the batch PE explain has been the most reliable. The others have been and are quality challenged.

Edward Long

--- On Mon, 1/24/11, Terry Purcell <[login to unmask email]> wrote:


From: Terry Purcell <[login to unmask email]>
Subject: Re: [DB2-L] Visual Explain, OSC or Datastudio for Explain Analysis in DB2 z/OS
To: [login to unmask email]
Date: Monday, January 24, 2011, 9:52 AM


As someone who also has a vested interest in the query tooling (because it impacts the DB2 for z/OS optimizer experience) - I shared this thread with the Data Studio Development team.

I was pleased to hear that they are already actively engaged with Myron and other customers on these issues. Earlier feedback resulted in an improved interface for Data Studio - so you know the team is listening.

With regard to the VE to OSC to Data Studio evolution - there is a logical explanation. The bottom line is that for integration with other IBM products (database, developer, data architect tools, monitors etc) and to deliver cross platform tooling, we needed to have a consistent (and IBM standard) architecture. To go through an explanation in more detail is too much to cover in this reply. But I would be happy to share that offline.

Regards
Terry Purcell

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA *
*   Your only source for independent, unbiased, and trusted DB2 information.   *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv