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()
--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);
CONDITION for SQLSTATE '02000';
CONDITION for SQLSTATE '23502';
DECLARE CONTINUE HANDLER FOR not_found
SET at_notfound = 1;
DECLARE cursor1 CURSOR FOR
from MyQUERY; --How to test if MyQuery has million rows?
WHILE at_notfound = 0 DO
FETCH cursor1 INTO command1;
if (at_notfound = 0) then
EXECUTE IMMEDIATE command1;