Using MS Access to read DB2 z/Os generates too many threads

Tami Van Dreese

Using MS Access to read DB2 z/Os generates too many threads
We have an MS Access application that sends an excessive number of sql
threads when it reads from our DB2 tables.
To get an understanding of what is going on, I set up a simple query in MS
Access that joins 2 tables
SELECT cat.CATEGORY_NUM, gmm.GMM_NUM
FROM CATEGORY_TBL cat INNER JOIN GMM_TBL gmm
ON cat.GMM_NUM = gmm.GMM_NUM;

There are less than 100 categories and 10 gmms . I set up an sql trace in
TMON and opened the query. It returned the data immediately, but continued
to generate 64 threads over the course of about 15 minutes. It did the
expected Prepare/open/fetch for the query above, but then followed with a
succession of prepares/opens/fetches like these below.
SELECT "GMM_NUM"
FROM "GMM_TBL"
WHERE "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?

SELECT "CATEGORY_NUM" ,
"GMM_NUM"
FROM "CATEGORY_TBL"
WHERE "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?

Each execution of the GMM query would have host variables like
(1,1,1,1,1,1…), then (2,2,2,2,2..) etc . However the host variables showing
for category don’t bear any resemblance to actual category numbers.
Has anyone had similar experience to this? Is there something that we
should be configuring differently within MS Access?

Thanks for any advice you can give.
Tami Van Dreese
Lands End Inc

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

Colin M Fay

Re: Using MS Access to read DB2 z/Os generates too many threads
(in response to Tami Van Dreese)
Hi,

My recommendation is to use the 'PASSTHRU' option on building
the query SQL.

This bypasses the (in)famous 'Jet Engine' that is rewritinging
your SQL's

Colin

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Tami Van
Dreese
Sent: Tuesday, December 15, 2009 3:15 PM
To: [login to unmask email]
Subject: [DB2-L] Using MS Access to read DB2 z/Os generates too many
threads

We have an MS Access application that sends an excessive number of sql
threads when it reads from our DB2 tables.
To get an understanding of what is going on, I set up a simple query in
MS Access that joins 2 tables SELECT cat.CATEGORY_NUM, gmm.GMM_NUM FROM
CATEGORY_TBL cat INNER JOIN GMM_TBL gmm ON cat.GMM_NUM = gmm.GMM_NUM;

There are less than 100 categories and 10 gmms . I set up an sql trace
in TMON and opened the query. It returned the data immediately, but
continued to generate 64 threads over the course of about 15 minutes.
It did the expected Prepare/open/fetch for the query above, but then
followed with a succession of prepares/opens/fetches like these below.
SELECT "GMM_NUM"
FROM "GMM_TBL"
WHERE "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?

SELECT "CATEGORY_NUM" ,
"GMM_NUM"
FROM "CATEGORY_TBL"
WHERE "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?

Each execution of the GMM query would have host variables like
(1,1,1,1,1,1...), then (2,2,2,2,2..) etc . However the host variables
showing for category don't bear any resemblance to actual category
numbers.
Has anyone had similar experience to this? Is there something that we
should be configuring differently within MS Access?

Thanks for any advice you can give.
Tami Van Dreese
Lands End Inc

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free
technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Tami Van Dreese

Re: Using MS Access to read DB2 z/Os generates too many threads
(in response to Colin M Fay)
Thanks Colin! That made a huge difference in my query. We're meeting
with the application developers later today. Hopefully this is the
solution.
Tami

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Fay, Colin M
Sent: Wednesday, December 16, 2009 5:06 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Using MS Access to read DB2 z/Os generates too many
threads

Hi,

My recommendation is to use the 'PASSTHRU' option on building
the query SQL.

This bypasses the (in)famous 'Jet Engine' that is rewritinging
your SQL's

Colin

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Tami Van
Dreese
Sent: Tuesday, December 15, 2009 3:15 PM
To: [login to unmask email]
Subject: [DB2-L] Using MS Access to read DB2 z/Os generates too many
threads

We have an MS Access application that sends an excessive number of sql
threads when it reads from our DB2 tables.
To get an understanding of what is going on, I set up a simple query in
MS Access that joins 2 tables SELECT cat.CATEGORY_NUM, gmm.GMM_NUM FROM
CATEGORY_TBL cat INNER JOIN GMM_TBL gmm ON cat.GMM_NUM = gmm.GMM_NUM;

There are less than 100 categories and 10 gmms . I set up an sql trace
in TMON and opened the query. It returned the data immediately, but
continued to generate 64 threads over the course of about 15 minutes.
It did the expected Prepare/open/fetch for the query above, but then
followed with a succession of prepares/opens/fetches like these below.
SELECT "GMM_NUM"
FROM "GMM_TBL"
WHERE "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?
OR "GMM_NUM" = ?

SELECT "CATEGORY_NUM" ,
"GMM_NUM"
FROM "CATEGORY_TBL"
WHERE "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?
OR "CATEGORY_NUM" = ?

Each execution of the GMM query would have host variables like
(1,1,1,1,1,1...), then (2,2,2,2,2..) etc . However the host variables
showing for category don't bear any resemblance to actual category
numbers.
Has anyone had similar experience to this? Is there something that we
should be configuring differently within MS Access?

Thanks for any advice you can give.
Tami Van Dreese
Lands End Inc

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free
technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
magazine now on-line!
_____________________________________________________________________

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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