Expand sp with additional query

[snufse1]

Below is a sp (on AS400) being called from Sql server 2005 using linked server. The table on the AS400 contains more than 17 mill records and 89 records are being returned in less than 1 second for a specifc job number selection)

create procedure get_jobqty
(in @jobnumber char(12))
result set 1
language sql
reads sql data
begin
declare c1 scroll cursor with return for
select gbmcu, gbsub, DECIMAL(SUM(gban01 + gban02 + gban03 +
gban04 + gban05 + gban06 + gban07 + gban08 +
gban09 + gban10 + gban11 + gban12 + gban13 +
gban14)/FLOAT(100.00),38,2) as sum_qty
from vgiprddta/f0902lc where gbmcu = @jobnumber
and gblt = 'AU' and gbobj = ' '
GROUP BY gbsub;
open c1;
set result sets cursor c1;
end;

Now I need to expand my query and obtain description for my job. New table is f0901lb and I need to match gbmcu = gmmcu (new field) and gbsub = gmsub (new field). Description field is dmdl01. I have tried using join and sub select. It returned more that 16k records. How can I do this and not get more that 89 records that my query now returns? Thank you.

 

[Mercury2]

...
	declare c1 scroll cursor with return for                  
         select  gbmcu, gbsub, dmdl01, 
         DECIMAL(SUM(gban01 + gban02 + gban03 +     
         gban04 + gban05 + gban06 + gban07 + gban08 +              
         gban09 + gban10 + gban11 + gban12 + gban13 +              
         gban14)/FLOAT(100.00),38,2) as sum_qty                    
         from vgiprddta/f0902lc  
         join MyLib/f0901lb   
         on  gbmcu =  gmmcu
         and gbsub =  gmsub
         where gbmcu = @jobnumber           
         and gblt = 'AU' and gbobj = ' '                           
         GROUP BY gbmcu, gbsub, dmdl01;

That should work.

 

[snufse1]

Get error:

[iSeries Access ODBC Driver][DB2 UDB]SQL0666 - SQL query exceeds specified time limit or storage limit.".

Remember the primary file (f0902lc) has 17 mill + records. When I run the sp prior to the join 89 records get selected in less than 1 second. Is there a way to join after I have read the 89 records? Or do I need a temp table, load my first read into this table (which will be 89 records), then select from the temp table and then join to f0901lb?

Thank you.

 

[snufse1]

Mercury,

I was thinking something like this

create procedure get_jobqt2
(in @jobnumber char(12))
result set 1
language sql
reads sql data

begin
declare global temporary table tempjobs
(jobnumber char(12),
costcode char(15),
quantity decimal(15,2),
description char(30))

insert into tempjobs(jobnumber, costcode, quantity)
select gbmcu, gbsub, DECIMAL(SUM(gban01 + gban02 + gban03 +
gban04 + gban05 + gban06 + gban07 + gban08 +
gban09 + gban10 + gban11 + gban12 + gban13 +
gban14)/FLOAT(100.00),38,2) as sum_qty
from vgiprddta/f0902lc where gbmcu = @jobnumber
and gblt = 'AU' and gbobj = ' '
group by gbsub;
Here the link to f0901lb would go......................


declare c1 scroll cursor with return for
select * from tempjobs
open c1;
set result sets cursor c1;

drop tempjobs;

end;

 

[Mercury2]

No, I don't think creating a new table is the way to go. SQL would do it if needed.
You could try to check out Index Advise in Iseries Nav if indexes were created. Then you should create them if any created by SQL to gain time, this is very important. In addition create your own QAQQINI file from QSYS/QAQQINI, update the QUERY_TIME_LIMIT parameter to an acceptable time and use it on CHGQRYA in the current job.

 

[snufse1]

Mercury2, I will try to do that. Thank you for all your help.