Performance on db2 SP having dynamic sql execute immediate

Hi

I am having hard time to figure out the performance on db2, I have created a stored procedure that will execute a query and the query table will be about half a million rows, I want to find out how db2 will do on performance side.

Having said that the query itself that was sent to db2 to execute need to be tuned, I have problems looking up explain for SQL in db2 as it is easy to read in Oracle and I am little bit more conversant in oracle rather than db2 to read and understand the plan.

could you guys explain in terms of performance by even showing a simple query and explain the explain plan on db2 and also I am going to post the db2 SP code here and could you guys tell me how I can verify the performance if the query table (MyQuery) has about million rows..

I will gladly give more details if you need and would appreciate the experts response on this..

create procedure RUN_STATISTICS()
 
LANGUAGE SQL
 
BEGIN
--Declare generic variables
 
 DECLARE        v_counter               INTEGER DEFAULT 0;
 DECLARE        v_message               varchar(70);
 DECLARE        v_sqlstate              varchar(5);
 DECLARE        SQLSTATE                char(5);
 DECLARE        at_notfound             SMALLINT DEFAULT 0;
 
 DECLARE command1 VARCHAR (32000);
 
 DECLARE not_found
 CONDITION for SQLSTATE '02000';
 
 DECLARE  SQLEXEPTION
 CONDITION for SQLSTATE '23502';
 
 
 DECLARE CONTINUE HANDLER FOR not_found
 BEGIN
 SET at_notfound = 1;
  END;
 
   BEGIN
    DECLARE cursor1 CURSOR FOR
    SELECT QUERY
    from MyQUERY;  --How to test if MyQuery has million rows?
 
    OPEN cursor1;
 
    WHILE at_notfound = 0 DO
 
    FETCH cursor1 INTO command1;
    if (at_notfound = 0)  then
      EXECUTE IMMEDIATE command1;
    end if;
 
    END while;
    CLOSE  cursor1;
  COMMIT;
 
 
END;
 
END

Open in new window

mahjagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi mahjag,

By far, the easiest way to read and understand an explain plan is to have the client display it in the form of a tree or graph.  Reading the plan from the table is cumbersome, awkward, and prone to aggravation.

Try downloading and installing the DB client from Aquafold (www.aquafold.com).  Their product is called Aqua Data Studios.  It has an aggravationware (evaluation) version that works quite well.  It does a very good job of displaying explain plans.


That said, your procedure won't produce a meaningful explain plan.  The parser has no idea what is in your table MyQUERY so the best that the explain plan can do is tell you what to expect for the procedure without the affects of the statements run via EXECUTE IMMEDIATE.


Good Luck,
Kent
0
mahjagAuthor Commented:
Thanks Kent for your response..

So are you saying Explain works only for SQL query and not SP

How do I know how much time it takes for execute immediate (is it doing parsing or any more steps) before execute..? I am trying to find a way to improve the processing since Myquery table will have million query to execute..
0
mahjagAuthor Commented:
I downloaded aquafold now - I am surprised that I do not see db2 for udb ver 8 there - I only see LUW- what is the differnece? can I use that?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Kent OlsenData Warehouse Architect / DBACommented:
Hi mahjag,

The Explain Plan can not evaluate the generated SQL that is stored in MyQUERY.  The timing of the SP, to loop 1,000,000 times is pretty quick.  And even the fetch adds only a modest amount of time.  Depending on the underlying server I would expect that portion of your process to complete in just a minute or two.

But the real time will be taken in the SQL that is run via the EXECUTE IMMEDIATE.  You'll want to identify those queries that are likely to be the longest running and tune them.  They can alway be copied to a client and an have explain plan run on them.


Good Luck,
Kent
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi Mahjag,

What server are you using?

0
mahjagAuthor Commented:
db2 installed on Linux and I know it is UDB and not LUW, but I do not see that option in register server
0
Kent OlsenData Warehouse Architect / DBACommented:

In this case, UDB is LUW.

When IBM moved from the mainframe to the server market, the rechristened DB2 and UDB (Universal DataBase).  They then made three versions.  Mainframe, AS/400, and LUW (Linux / Unix / Windows).

If you've got DB2 or UDB running on Linux, Unix, or Windows, you are running the LUW version.


You should be able to connect to it just fine with Aqua Data Studios.


Kent
0
Dave FordSoftware Developer / Database AdministratorCommented:

LUW = Linux / Unix /  Windows

Therefore, that's what you have.

-- DaveSlash
0
mahjagAuthor Commented:
Now I am registred.. thanks Kent and daveslash..

I wanted to test the explain plan for  a query and I got this error.

Describe Error: Failed to execute EXPLAIN plan: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: partiti
on;row_number() over (;PARTITION

what does this mean? I cannot run explain on a sql?
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi mahjag,

ADS is a bit picky about schemas.  Make sure that the schema in the dropdown is the default schema for your user.


Good Luck,
Kent
0
mahjagAuthor Commented:
It does show correct schema name..dont know what else to look next..
0
mahjagAuthor Commented:
ignore my message before - I got it to work - that was the line break that was causing the problem

Now I got some output for explain - I still cannot read that ..looks the same before except color coded.
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi mahjag,

There should be a "pretty" graph that shows the execution path and approximate cost of each operation.  There should also be a table that details each "box" in the graph.

0
mahjagAuthor Commented:
I only got operation - which is color coded - subtree cost , node cost , IO cost which has some numbers.. do I have chose some option to get grahical view?

I right clicked on query analyzer and copy past my sql query and hit execute explain plan button -
0
mahjagAuthor Commented:
what I am looking for is to tell me full table access or which index or partioon it is using - suggest ways to tune the query by giving suggestions on changing indexes or partitions - give execution time before and after to see if the query is tuned.. can we get that info - ?
0
Kent OlsenData Warehouse Architect / DBACommented:
The Plan should tell you where DB2 is using index scans, table scans, sorts, join operations, etc.
0
mahjagAuthor Commented:
I am having trouble reading the plan - it does say table join - but does not say full table join -

could you guys post a query and the plan details to understand how to read them.. otherwise I have to post my complex query which might not be a good start..
0
Kent OlsenData Warehouse Architect / DBACommented:

Post the text part of the plan.  It should fit pretty well and explaining the key pieces shouldn't be too tough.

0
mahjagAuthor Commented:
sorry to be more specific - how do I do that?
0
Kent OlsenData Warehouse Architect / DBACommented:

Cut/Copy and Paste?
0
mahjagAuthor Commented:
I gurantee that is not as simple as what you described ctrl c and ctrl p - execution plan is in tabular form and when copied and pasted nothing can be read clearly on this -
0
Kent OlsenData Warehouse Architect / DBACommented:

rats
0
Kent OlsenData Warehouse Architect / DBACommented:

Right click on the plan table and select "Explain Diagram".

The visual should help a lot.
0
mahjagAuthor Commented:
OK.. Thanks -  a lot better now.
in terms of cost only nested loop join shows higher percentage, some of the others are group by and filters..
Is there a way from here I can find out the offending sql and tune them?
0
Kent OlsenData Warehouse Architect / DBACommented:
You'll have to look at each SQL (1 at a time).

At a glance, you should have a good idea which are likely to be problems, though it's pretty easy to miss an index.

0
mahjagAuthor Commented:
Also there are 2 rectangular boxes Table scan and Fetch that points to Nested loop join, the table scan shows table name SYSIBM.GENROW - I did not use this tablle - what is that?

Also how do I find out what contributed to the Nested loop join that has cost percentage of 92%?
0
Kent OlsenData Warehouse Architect / DBACommented:

GENROW.

Do you have column (row_number () over (partition by ...)) or load an identity column or a sequence?


0
mahjagAuthor Commented:
what is the difference between Sort and Group by as shown in the diagram so I can relate that too the SQL
0
mahjagAuthor Commented:
Also I am trying to tune this part of the query that has
select  col1,
row_number() over (partition by t1.codes,t1.region order by t1.value desc) as rnum

and then I am using group by having rnum = <fixed number value>, this fixed number value can be obtained based on the last day minus first day of the month for example for feb the last day is 29 and first day is 1 and then the value is 28
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi mahjag,

row_number() over (...)  probably can't be tuned much more than it is.  IBM built these OLAP functions (the 'over partition by' syntax) in a couple of years back and made them more efficient than the old style syntax.


Kent
0
mahjagAuthor Commented:
The only question I have is how do I tune some of the query based on the given indexes and partitions for a table, is there a tool that can rewrite and suggest alternative indexes? Also I want to volume test them for performance how do I do that..

Thanks a lot Kent for your responses!!
0
Kent OlsenData Warehouse Architect / DBACommented:

There are a number of tools out there that will help you tune / rewrite queries.

I'm very partial to the Quest tool, though it's not free.  If your office is willing to spend a few bucks on a good client tool, check out www.quest.com


Kent
0
mahjagAuthor Commented:
I dont think my company will spend any bucks on quest.. how do I volume test it?
0
Kent OlsenData Warehouse Architect / DBACommented:

Hmmm....  A good client tool is a lot cheaper than a server upgrade.

The Quest tool literally rewrites the query several different ways and creates explain plans for all of them.  It then displays all of the sources, and expected run times.

Without creating your own data, I don't know how you would volume test it.


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.