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 derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
-
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... -
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... -
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... -
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. -
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 <... -
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... -
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)... -
What does this mean
Ah yes, I got snookered by the old advertising highlight! :-) -
What does this mean
Marc, Your link sent me to the Web Traffic Analysis Module of the Web CEO site. -
Concatened Parametes
It's not clear to me what you're trying to do. Could you elaborate... -
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. -
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? -
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 -
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... -
[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... -
[DB2- AS400] S F WHERE col1 IN (SELECT ...) very very very slow !
Well, I ran the subquery vs the join in UDB AIX, first in test (small) and then production (real big). In test there was no difference. In production, the subquery took about 3 times as long. I remember reading that a subquery can have varioius effects on the path that the Optimizer picks... -
CONVERSION OF THE STRING TO DATE
Right, if you combine what Ties wrote with Int(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)) < Year('2005-7-1') Or (Int(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,1,4)) = Year('2005-7-1') and Int(SUBSTR(MEMBER_WORK_PERIOD.WORK_PERIOD,5,2)) < Month('2005-7-1') ) It should work fine. -
Structured way of building sql query?
The best advice I can give you is to get a good book and learn everything you can. General SQL books are good but ones geared specifically to your database platform are usually the best. Learn the basics and the nuances and then practice practice practice. For each problem there can be many... -
DB2 SQL query
Is the intent to grab rows only when there is no match between your TAB1 and TAB4 parameters? If so, then yes, you can Left Outer Join TAB1 to TAB4 and put "d.col1 is NULL" in the WHERE clause. At least it appears that way from a quick perusal -
Joining tables wth non-matching fields..
Substr("EPRFVulnerableAdult"."VAEprfRef",1,16)=Substr("EPRFMain"."EprfRef",1,16) should do the trick, as long as your RDB supports Substring. It might not be too efficient though. If the field is Indexed, I believe this function will negate the benefit of said Index.
