Re: SQL db2 usage/overhead on your system questions.

Thanks for the information, I stumbled on this today:

The governor in DB2 Universal Database for iSeries is based on two
measurements:
The estimated runtime for a query.
The estimated temporary storage consumption for a query.

How could you measure which jobs/programs/sql statements are exceeding
this, are we pretty much back to what I have read to use in the answers to
this post.

On Mon, Dec 3, 2012 at 11:39 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>wrote:

To add to Charles' reply -

Jack

Your best bet is to get a couple recent redbooks on SQL performance,
seems you are already looking at some resources.

As to CQE vs SQE - IBM have removed more and more of the factors that
prevent the use of SQE - how do like my "not" logic? :) My hidden
double negative?

And to learn more, just go into the database component of Navigator
(windows client, not the browser-based Navigator - DB function is still
limited there, although we are pushing for getting it there). Set up a
monitor there - you can use wildcards, etc., to filter jobs. Or run the
thing over ALL - not usually a good idea, though.

As to which jobs over TCPIP are doing SQL - it's usually a QZDASOINIT
job - that is what processes the database host server requests. In
netstat, look for the database host server (as-database). The only job I
see there now is QZDASRVSD, which is probably a director - it must start
other jobs for incoming requests, but I haven't dug too deeply into
that, so use your grain of salt here.

The ports are known - they might even be in this sites wiki. as-database
appears to be 8471.

There are Visual Explain APIs, IIRC. Easily verified. And lots of fields
specific to VE in the output of the DB monitor.

HTH
Vern

On 12/3/2012 8:01 AM, Charles Wilt wrote:

The system always captures information on all SQL queries in the SQL Plan
cache

http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzajq/rzajqdisplayplancache.htm

http://www-03.ibm.com/systems/resources/systems_i_software_db2_PlanCache_Basics.pdf

The plan cache can help you identify problem areas for which you can use
the DB monitor tools to dig into more deeply

http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzajq/queryopt.htm

I am not aware of any differences between SQE and CQE with respect to the
functionality of the DB tools.

HTH,
Charles



On Sat, Dec 1, 2012 at 12:22 PM, Jack Kingsley <iseriesflorida@xxxxxxxxx
wrote:

Is SQL overhead measured in the performance tools or must you use pex

and

or the sql components to setup various versions of monitors and such

using

iseries access to gather and report on what is taking place on your

system.

What is an easy way to drill down to jobs that are using sql on your
system, I have a tool called wrkodbcjob that somewhat gets you your
odbc/jdbc jobs etc.

Using netstat, how can you quickly determine which jobs are using sql

and

by what ports, another words how many jobs/users are currently

performing

some sort of sql on my box.

Created a new file as a clob, that has ended up clobbering my system

cpu,

anyone seen anything like this, all versions of i5/os and it makes no
difference.

If your designing applications using client side sql and or embedded sql
what are the performance metrics that should be used to determine that
various amount of remote clients and systems performing various types of
sql against your database should be allowed, is this a
model/cpw/memory/processor type situation that could effect this.

I have been reading the sqe vs the cqe information and it seems that

most

of what I have going on is sqe based.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.