how to fetch multiple records without using cursor
Answers were Sorted based on User's Feedback
Answer / m4io
it is not possible in db2 v8 : you still need a cursor.
However : you can fetch 100 rows in one fetch.
01 host-variables.
05 HV-col1-ARRAY PIC s9(04) COMP OCCURS 100 TIMES.
05 HV-col2-ARRAY PIC x(40) OCCURS 100 TIMES.
05 HV-vcol3-ARRAY OCCURS 100 TIMES.
49 HV-vcol3-len-ARRAY PIC s9(04) COMP.
49 HV-vcol3-txt-ARRAY PIC x(40).
05 HV-IND2-ARRAY PIC s9(04) COMP OCCURS 100 TIMES.
I
DECLARE csr1 CURSOR WITH ROWSET POSITIONING for select
col1, col2, vcol3 from ...
open cursor
FETCH NEXT ROWSET FROM csr1 FOR 100 ROWS
INTO :HV-col1-ARRAY,
:HV-col2-ARRAY:HV-IND2-Array,
:HV-vcol3-ARRAY
Is This Answer Correct ? | 13 Yes | 2 No |
Answer / om yaddav
hi srini......
how can do in DB@...
?
write with sentax with example.
thans
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / premil
It can be done with the help of dynamic sql queries
(prepare,execute).
but i dont remember the syntax...
sorry ...
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / mahyn
However if we want to fetch and process multiple rows
without using cursor, we can do with the below psuedo code.
assume that seq_num is unique.
h-seq initilized with spaces.
perform until sqlcode = +100 or h-seq >= 500
exec sql
select seq_num into :seq-num from tablename
where seq_num > :h-seq
order by Seq_num with ur;
fetch first 1 row only
end-exec
operations
move seq-num to h-seq
end-perform
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / chakradhar patra
Yes its possible in NATURAL using MULTI-FETCH Option.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ram g
if u r talking about host language app pgm its not
posssible dats d reason we are hving cursor logic...
in select stmt its quite possible by having condtn in whr
clause such a way it can fetch multiple record..
Is This Answer Correct ? | 3 Yes | 4 No |
Answer / sivakumar sekharannair
None of the host languages can handle multiple records at a
time.
But i think if we know the number of rows that will be
retreived then we can load the records into a internal
table and then process it as required...
Any thoughts!!!!!!
Is This Answer Correct ? | 1 Yes | 2 No |
How many buffer pools are there in DB2?
Give some example of statistics collected during RUNSTATS?
What DB2 Catalog column tell you when an index needs table reorganized ?
What are PACKAGES ?
Can we able to find all the Table names under a Particular Plan?
What is the cobol picture clause of the db2 data types date, time, timestamp?
Return code 100 means?
When do you specify the isolation level? How?
What are the various data types available in db2?
What is data manager?
when i am trying to update a table having 1000 rows. the program abends.how we will come to know the last successful updated row was. we are not using any commit operations
How can you quickly find out the # of rows updated after an update statement?