Thanks for the response Truss...
I'm in the business area so we don't get to tune the database, that job falls to our DBAs. We make recommendations to them and sometimes they listen and other times they ignore us. So, I don't know how much attention was paid to Memory parameters on V8. Sounds...
We are currently in the midst of a somewhat massive consolidation project to combine our 2 databases. When complete, we will have a DB in the neighborhood of 1.5 terabytes. Our monthly production cycle is a fairly extensive "batch" process consisting of feeds from various admin systems, large...
We're trying to quantify the performance gains we'll see in 2008 when we convert to Version 9. To those of you who have already gone to V.9, have you seen significant performance improvements? If so, would you say it's as much as 25 or 30% ?
Any feedback is greatly appreciated. We're using...
Do you really have tables in this DB where the Key relations can only return multiple rows? Commonly there will be a secondary key to allow the return of one row.
I managed to do it with the following code:
with Order_rows(org_id, row_num)
as (
select accum_amt, row_number() over(order by org_id)
from prst.nvt_org
order by org_id)
,
trim
As (
Select org_id, row_num
from Order_rows
where row_num < 101 )
Select sum(case when row_num <...
I doubt it's a problem with too many nested sub queries, which is really what you have here. I'd guess it's a syntax error of some kind but it's difficult to tell from the SQL you present in your post.
From what I've read, sub queries can have unpredictable effects on the optimizer, specially...
And if you don't have OLAP capability, this Solution works anywhere:
select org_id
From org o1
Where 50 < (Select count(*)
From org o2
where o1.org_id > o2.org_id)
and 100 >= (select count(*)
From _org o2
Where o1.org_id > o2.org_id)...
I was able to fun the following so it appears you can do fetch in a subquery. We're using Version 8, AIX UDB.
select value_basis ,
(select kind_code
from pfmc.nvt_agmt_elem_prod
fetch first 1 rows only)
from pfmc.nvt_agmt_elem_prod
fetch first 1 rows only
Does your platform allow you to create Global Temp Tables? If so, you could take the code from the subquery and put it in a step 1 which loads the GTT. Then in step 2 you could match Price_Table with the GTT.
Should be a lot faster. I've converted jobs with subqueries that wouldn't run at...
dd,
I was responding to JF's request to try a joined query and then turn it into a subquery, the theory being that if the joined went quicker than the subquery, then DB2 had a problem. Based on my experiment, the correlated subquery caused the optimizer to pick a less efficient path than the...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.