Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by PruSQLer

  1. PruSQLer

    DB2 Version 9 vs Version 8

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

    DB2 Version 9 vs Version 8

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

    DB2 Version 9 vs Version 8

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

    Help With SQL query

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

    Group rows 1-50, 51-75, 76-100

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

    Levels of nested table expression allowed

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

    Select X number of Rows

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

    What does this mean

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

    What does this mean

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

    Concatened Parametes

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

    In subquery, can I limit the fetch to 1 row?

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

    In subquery, can I limit the fetch to 1 row?

    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?
  13. PruSQLer

    In subquery, can I limit the fetch to 1 row?

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

    Innovation ways to get the last date less than or equal to an input da

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

    [DB2- AS400] S F WHERE col1 IN (SELECT ...) very very very slow !

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

Part and Inventory Search

Back
Top