Recent content by PruSQLer

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)...

Ah yes, I got snookered by the old advertising highlight! :-)

Marc, Your link sent me to the Web Traffic Analysis Module of the Web CEO site.

It's not clear to me what you're trying to do. Could you elaborate...

Can you use global temp tables on the AS 400? That's what I would use to solve this performance problem.

Chris, I converted your SQL to process in my database and it ran fine. Is it possible the AS 400 doesn't allow FETCH in a subquery?

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...