473,729 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Long running of DB2 Queries

14 New Member
Hello! If there’s anyone who can help with this problem…

We created two working environments, creating the same table (TABLEX), same indexes (INDEX1, INDEX2 & INDEX3) and executing the same SQL Statements (SQL1, SQL2 & SQL3) thru spufi. Here is exactly what we did.

For both environments, these SQL statements were created. The ‘where clause’ of the first SQL statement is patterned after INDEX1. The ‘where clause’ of the second SQL statement is patterned after INDEX2. The ‘where clause’ of the third SQL statement is patterned after INDEX3.

Environment 1: We created three indexes (INDEX1, INDEX2 & INDEX3). Then we ran SQL1, this ran for 5 seconds and TMONDB2 displayed INDEX1. Then we ran SQL2, this ran for 5 seconds and TMONDB2 displayed INDEX2. Then we ran SQL3, this also ran for 5 seconds and TMONDB2 displayed INDEX3.

Environment 2: At first, we did not create any index, then we ran the three SQLs. SQL1 ran for 28 seconds, SQL2 for 26 seconds and SQL3 for 30 seconds.

Then we created INDEX3. SQL1 ran for 38 seconds, TMONDB2 displayed INDEX3. SQL2 ran for 28 seconds, TMONDB2 displayed INDEX3. SQL3 ran for 5 seconds, TMONDB2 displayed INDEX3.

Then we created INDEX2. So, we now have INDEX2 & INDEX3. SQL1 ran for 26 seconds, TMONDB2 displayed INDEX2 & INDEX3. SQL2 ran for 28 seconds, TMONDB2 displayed INDEX2 & INDEX3. SQL3 ran for 9 seconds, TMONDB2 ONLY displayed INDEX3.

Then we created INDEX1. Now, we have all indexes. SQL1 ran for 7 seconds, TMONDB2 displayed INDEX1. SQL2 ran for 58 seconds, TMONDB2 displayed INDEX1. SQL3 ran for 41 seconds, TMONDB2 displayed INDEX1.

The following are the SQL statements:
1. SELECT *
FROM TABLEX
WHERE BANKCD = 12345
AND MONTHENDDT = 20070131
AND BATCHSYS = ‘SYS’
AND ACCTCD = 123

2. SELECT *
FROM TABLEX
WHERE BANKCD = 12345
AND BATCHSYS = ‘SYS’
AND CLIENT = 12345
AND ACCTCD = 123
AND MONTHENDDT = 20070131

3. SELECT *
FROM TABLEX
WHERE MONTHENDDT = 20070131
AND UNIT = 5423
AND ACCTOFF = 111112
AND BATCHSYS = ‘SYS’

Why is the runtime of environment 2 different from environment 1 when all the indexes are created? Why is the display of TMONDB2 different from environment 1 and environment 2 after creating the three indexes? What could be the other factors that may affect this difference?
Jun 29 '07 #1
6 4735
Ben Neffendorf
3 New Member
Not sure, but at a guess I'd say Environment 2 is attempting to cluster by index 3, where-as env 1 would be clustering by index 1 (assuming you created them in 1-2-3 order). At least I think that's how db2 works - unless you specify one of the indexes as clustering, it will pick the 1st one created. Even if runstats aren't collected, I guess that would alter the access paths.
Jun 29 '07 #2
Snib
24 New Member
Ben is correct in what he has stated. If you check the the following URL:

Index Design for Performance

Under the section titled "Recommendation s" it confirms that DB2 will select the first index added to the table as the clustering index if that is the only index being added at that time.

What is not clear from your statements is if/when you ran a RE-ORG, when you loaded the tables and whether you ran RUNSTATs.

If you are creating a new table which you are going to load rows onto after it is created (i.e. you are not starting off with an empty table and then adding rows via an application as they are created), then one option is as follows:

1/ Define the table structure
2/ Define the clustering index
3/ Load the data (use the DB2 load facility as this will ensure the rows are sorted in the clustering index order as they are stored on the data portion of the table)
4/ Define second index
5/ Define third index
6/ Execute RUN-STATs
7/ If the SQL is being executed from a program and not as dynamic SQL then execute a REBIND to allow DB2 to re-analyse the most efficient access paths for the SQL.

If you load the data onto a table before creating the primary index then it will be loaded in the order of the input file used. Even when you create the clusting index if you do not RE-ORG the table the data stays in this order on the data portion of the table. If you do not execute RUNSTATs then the details used during the REBIND/BIND will not be up-to-date and may again impact performance.

There are a number of fields that affect the access paths for "static" SQL - I susgest you runs some searches on the Internet to find the list of the latest ones for your version of DB2.

One thing to remember - if you do not RE-ORG and RUNSTAT then access paths may not be as you expected. If you do not specify the clustering index for a TABLE then access paths may not be as you expected.

I suspect that if you dropped the indexes from Environment 2, create them again but define Index 1 as clustering, execute a RE-ORG, execute RUNSTATS, BIND the SQL if it is static and in a program and then run the SQL again you will finds things are bettter.

If they are still not the same as Env 1 then I would suggest you repeat the same process against Env 1 to ensure the indexes, runstats and access paths are as up-to-date as possible.

Regards

Snib
Jun 30 '07 #3
karpalmera
14 New Member
thank you so much, snib and ben...!!!!
i'l update you, guys...thank you!!!
Jul 1 '07 #4
karpalmera
14 New Member
hello...i just found out that both environments were clustered with INDEX1.
:)
Jul 2 '07 #5
Snib
24 New Member
OK, so does that mean you still have a problem or is everything no working ok?

If you still have a problem it would help if you could run an EXPLAIN for each piece of SQL against each environment and then post the details from the PLANTABLE. We can then review exactly what order the SQL is being executed in and the indexes DB2 has selected.

One other thing, before you run the EXPLAIN ensure you have executed RUNSTATS and is the SQL is in a program then execute a REBIND. If you do a REBIND you can request this to produce the EXPLAIN output at the same time.

Regards

Snib
Jul 5 '07 #6
karpalmera
14 New Member
Yes! it worked for both development and production environments..

Here is what we did....
in our development environment:
  1. we dropped all indexes and tables
  2. created the table and defined the primary index with clustering
  3. restored all data
  4. created other indexes
  5. RUNSTATS
  6. rebind programs
  7. run programs

in our production environment:
  1. we dropped all indexes
  2. defined the primary index with clustering
  3. REORG
  4. created other indexes
  5. RUNSTATS
  6. run programs

thanks a lot!
Jul 10 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
5660
by: sqlserver yeahbaby | last post by:
I can't seem to find a step-by-step guide to how to detect or continuously monitor for long-running queries that is suitable for a comparative SQL Server novice. I know that it is possible to monitor for such with other database products - can anyone enlighten me as to how this is done with SQL Server? Ideally, I'd like to snapshot running queries (preferably with query text) at a particular instant via a script, although any help to show...
4
3366
by: Gary | last post by:
I am having a problem executing long running queries from an ASP application which connects to SQL Server 2000. Basically, I have batches of queries that are run using ADO in a loop written in VBScript. This works pretty well until the execution time of a single query starts to exceed some threshold, which I am trying to narrow down. I can typically run 2 - 10 queries in a loop, with the run time being anywhere from under a minute to an...
29
5811
by: pb648174 | last post by:
I have a very long transaction that runs on the same database that other users need to use for existing data. I don't care if they see data from the transaction before it is done and am only using the transaction because I need a way to roll it back if any errors happen during the transaction. Unfortunately all tables affected in the long running transaction are completely locked and nobody else can access any of the affected tables while...
2
1917
by: peteraguard-google | last post by:
Hi, I have a 120M record table that needs to have indexes added as a background process. While running the CREATE INDEX command, which is taking several days, all other SELECT queries on the same machine (on a different database) are slowed dramatically. Is there a way to reduce the CREATE INDEX priority so that other SELECT queries can still function at normal speed?
12
3436
by: strict9 | last post by:
Hello all, I'm writing several queries which need to do various string formating, including changing a phone number from (123) 456-7890. After some problem with data mismatches, I finally got it to work only to see that it takes 30-60 seconds to run the query, instead of the usual .5 seconds when I use a query without a function. Here is the code for the function. I call it using
2
2009
by: Gershon | last post by:
I have an ASP.NET/C# web application running against a SQL Server database using ADO.NET. Whenever there is a long-running database query, the web application hangs until the database query is complete. Even clicking the browser's stop button and clicking on other links within the web application fails. The only thing the user can do is close the browser and open up a new browser window. Is there any other way for a user to interupt a...
4
1966
by: ImSoLost | last post by:
I'm running a really long process from ASP.NET and need some help... I am making a method call when the user presses a button from my webpage, which goes into a database and parses a file. This process could exceed an hour, so the user is expected to press the button to start the process and minimize the IE window and do other work while the application is processing. Anyway, after a certain period in time, the page becomes "Page could...
9
2527
by: Fish Womper | last post by:
I am at best a part time developer of Access databases. I use Access 2.0, as this is all my employer has on its computers. Even so, to use this ancient version requires a fairly convoluted installation procedure on each PC on which it is used. I am self-taught from the help files that come with Access 2.0 and from painful experience. I've never attended any type of training course on how to develop databases in Access. I have no idea...
1
3480
by: Aaron West | last post by:
Try this script to see what queries are taking over a second. To get some real output, you need a long-running query. Here's one (estimated to take over an hour): PRINT GETDATE() select count_big(*) from sys.objects s1, sys.objects s2, sys.objects s3, sys.objects s4, sys.objects s5 PRINT GETDATE()
3
2608
by: rfuscjr via AccessMonster.com | last post by:
This is truly bizzare. I have a query that runs for hours in one Access db. When I import it into another Access db, it runs in minutes. I compacted and repaired the original, relinked tables etc. Nothing makes it run faster. I have imported the query in several dbs. I did find another where it appeared to run very long. Still in others it runs in minutes. Ideas? -- Message posted via AccessMonster.com...
0
8925
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8763
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9428
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9288
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9206
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9154
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8156
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6026
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
3
2166
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.