Hi,

Does anyone know how one could benchmark SQL statements with parameter
markers?

e.g.: SELECT * FROM SYSCAT.TABLES WHERE TABNAME=?

I can provide values for the parameter makers, but I want it to be run (and
optimized) with the parameter
marker and not with a value like:
SELECT * FROM SYSCAT.TABLES WHERE TABNAME='test'

I thought I would be able to do this using db2batch but it does not look to
me like I can. Does anyone know of any other method? The plan chosen is
different when using parameter markers compared to values. So I need to be
able to benchmark this.
And I'm not a developer, just a simple DBA.

Thanks.

Quick answer, visual explain and dynexpln support dynamic SQL.

"Erik Hendrix" <hendrix_erik@hotmail.com> wrote in message
news:898d50e8a279efa79cc2ab92f3fc64cb@free.teranew s.com...
> Hi,
>
> Does anyone know how one could benchmark SQL statements with parameter
> markers?
>
> e.g.: SELECT * FROM SYSCAT.TABLES WHERE TABNAME=?
>
> I can provide values for the parameter makers, but I want it to be run
(and
> optimized) with the parameter
> marker and not with a value like:
> SELECT * FROM SYSCAT.TABLES WHERE TABNAME='test'
>
> I thought I would be able to do this using db2batch but it does not look
to
> me like I can. Does anyone know of any other method? The plan chosen is
> different when using parameter markers compared to values. So I need to be
> able to benchmark this.
> And I'm not a developer, just a simple DBA.
>
> Thanks.
>
>

"Erik Hendrix" <hendrix_erik@hotmail.com> wrote in message news:<898d50e8a279efa79cc2ab92f3fc64cb@free.terane ws.com>...
> Hi,
>
> Does anyone know how one could benchmark SQL statements with parameter
> markers?
>
> e.g.: SELECT * FROM SYSCAT.TABLES WHERE TABNAME=?
>
> I can provide values for the parameter makers, but I want it to be run (and
> optimized) with the parameter
> marker and not with a value like:
> SELECT * FROM SYSCAT.TABLES WHERE TABNAME='test'
>
> I thought I would be able to do this using db2batch but it does not look to
> me like I can. Does anyone know of any other method? The plan chosen is
> different when using parameter markers compared to values. So I need to be
> able to benchmark this.
> And I'm not a developer, just a simple DBA.
>
> Thanks.


Check out for example:

http://groups.google.com/groups?hl=e...abases.ibm-db2

HTH
/Lennart

Thanks to both Ray and Lennart, but I'm not looking for a explain plan. I'm
looking to actually run the
query and benchmark it. But I noticed through explain that the plan chosen
by the database is different between providing parameter markers and putting
the values in the statement itself. Thus I want to find out
by benchmarking it how much difference in runtime it will give me.

Thanks.
"Lennart Jonsson" <lennart@kommunicera.umea.se> wrote in message
news:6dae7e65.0307180806.264e893e@posting.google.c om...
> "Erik Hendrix" <hendrix_erik@hotmail.com> wrote in message
news:<898d50e8a279efa79cc2ab92f3fc64cb@free.terane ws.com>...
> > Hi,
> >
> > Does anyone know how one could benchmark SQL statements with parameter
> > markers?
> >
> > e.g.: SELECT * FROM SYSCAT.TABLES WHERE TABNAME=?
> >
> > I can provide values for the parameter makers, but I want it to be run
(and
> > optimized) with the parameter
> > marker and not with a value like:
> > SELECT * FROM SYSCAT.TABLES WHERE TABNAME='test'
> >
> > I thought I would be able to do this using db2batch but it does not look
to
> > me like I can. Does anyone know of any other method? The plan chosen is
> > different when using parameter markers compared to values. So I need to
be
> > able to benchmark this.
> > And I'm not a developer, just a simple DBA.
> >
> > Thanks.
>
>
> Check out for example:
>
>
http://groups.google.com/groups?hl=e...abases.ibm-db2
>
> HTH
> /Lennart

Erik,

You might have a look to the samples/java, and arrange a java script.
Hope this helps,

Jean-Marc


"Erik Hendrix" <hendrix_erik@hotmail.com> a écrit dans le message news:
898d50e8a279efa79cc2ab92f3fc64cb@free.teranews.com...
> Hi,
>
> Does anyone know how one could benchmark SQL statements with parameter
> markers?
>
> e.g.: SELECT * FROM SYSCAT.TABLES WHERE TABNAME=?
>
> I can provide values for the parameter makers, but I want it to be run
(and
> optimized) with the parameter
> marker and not with a value like:
> SELECT * FROM SYSCAT.TABLES WHERE TABNAME='test'
>
> I thought I would be able to do this using db2batch but it does not look
to
> me like I can. Does anyone know of any other method? The plan chosen is
> different when using parameter markers compared to values. So I need to be
> able to benchmark this.
> And I'm not a developer, just a simple DBA.
>
> Thanks.
>
>

Hi Erik,

WinSQL supports paramaters. They look like named parameters, but they
are sent to the server as markers.

Check it out at:

http://www.synametrics.com/winsql


Christian.

Erik,

Statistics are updateable, so
I would try to tweak the statistics so that the plan is the same no matter if
it's a parameter marker or the actual value is supplied.
Then use db2batch

don't forget to rerun the statistics when you are done with your benchmarks

good luck!

another option would be to wrap your parameterized statement in a stored procedure

and again: db2batch