Code:
select oainv from TESTDATA.MR20100823,
wellhole
Last Activity: Aug 24 '10, 03:50 PM
Joined: Jan 24 '08
Location:
-
Union All causing headache
I wrote a query that takes about 4 seconds to run once and subsequent runs take 250 ms. I wrote another query that takes about 7 seconds to run once and 300 ms afterwards. If i UNION ALL them it'll take 90-120 seconds every time. I'm only producing a single result even. That's a tremendous increase in processing time for what seems like a simple operation. Is there a strategy to run it faster?
... -
There is no control center and those commands don't work on the iSeries and as/400. -
I'm not completely familiar with db2, but my question is whether that column is the only key in the index. It might help if this is how its set up.
One way to work around it is to keep track of what records you're interested in. Perhaps another file that is modified whenever table is updated and you'll query on that instead.Leave a comment:
-
I tried something similar, but it runs slower than the original. I'm guessing its because these temporary tables don't use indexes. The query has to return the rows back from both tables, and TABLEA has to be on the left of the outer join which was why I couldn't just use what you wrote above.
...Code:select * from TABLEA left outer join (select * from TABLEB r2 left outer join (select ceseq mseq,Leave a comment:
-
Improving a SQL statement
Is there any way to improve this statement? A is the header table while B has 0 to many records for each record in A. I'm trying to pick only the latest record in B for the associated seq in A.
Code:select * from A left outer join B on B.seq = A.seq and B.order = (select max(order) from B where B.seq = A.seq)
-
Let me expand on this. FUNCA now has varchar and integer parameters and I can call it from iSeries client with:
But, if I try to use this inside a stored procedure, I'll get a complaint "FUNCA in *LIBL type *N not found."Code:SET PATH = DEVA; select FUNCA('a','b',1,2) from SYSIBM/SYSDUMMY1Leave a comment:
-
Calling a UDF
I'm trying to call a user defined function FUNCA that I created in library DEVA. "create function DEVA/FUNCA etc etc etc"
If I go into STRSQL and try to run
I'll get an error message "Column DEVA not in specified tables."Code:select DEVA/FUNCA() from SYSIBM/SYSDUMMY1
If I runI'll get an error message "FUNCA in *LIBL type *N not found."...Code:select FUNCA() from SYSIBM/SYSDUMMY1
-
After some testing with your recommendations , I've found that "for update of COL2" worked, and then I slapped on "with cs" for good measure. Thanks a lot; I've learned a lot.
Oddly, using just "for update" won't block and nor will just "with cs", but "for update of col2" apparently will do it.
...Code:declare v_counter integer default 0; declare val integer;
Leave a comment:
-
(option #1 doesn't compile for me so it must be for a different system).
I'm trying to test the row lock with a waiting loop immediately doing one of the above and running a select statement separately to read the row to see if its locked. The select has no problem reading the record. Do you know what I'm doing wrong?
...Code:create procedure sp_test language SQL begin declare v_counter integer
Leave a comment:
-
You're right I forgot to put in where COL1 = 777 in my update statement. The forum won't let me edit the first post to correct my mistake...
Oh, I wish it was that easy with those statements. I have to do some processing of the COL2 value before I do the update so #1 would not work. Sorry I didn't explain it better.
Would #2 prevent reading from this record for others? What I fear is that after I read from the record and...Leave a comment:
-
Row Locking
I'm trying to create a stored procedure to grab a record from a table and update the record.
But, I fear this will allow anyone to run over the procedure and get the same COL2 value before I update the record. I hear using "lock table in exclusive mode" would be a bad thing to do. What can I...Code:declare val integer; select COL2 into val from TAB1 where COL1 = 777; update TAB1 set COL2 = COL2 + 1;
-
I wish it was as simple as a single sum value.
If I'm properly interpreting your approach, you're suggesting that I use a temp table to store the calculations and pull each sum from that table instead. I hope that'll reduce my timings, because table2 is over a million records and the temp table is going to have to store a few hundred sums.
Thanks Bernd. I'll give it a try.Leave a comment:
-
One easy way is to nest a select statement
select a.rule, b.account, count(*),
(select count(*) from table y where x.month = y.month and x.account_type = y.account_type and y.rule < 1)
from table1 a, table2 b, ............... ..................Leave a comment:
-
Using sum in nested select
Hi,
I'm trying to use use a sum value from table2 in my nested select, but I can't seem to figure a way to not have to calculate it twice.
...Code:select a, b, c, d, e, ( select name from table1 where minv <= (select sum(num) from table2 where a != b) and maxv >= (select sum(num) from table2 where a != b) ) from
No activity results to display
Leave a comment: