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 do you pull up a query which was previously saved in qmf?
Can we declare DB2 HOST variable in COBOL COPY book?
Can you use a select query in a loop to fetch multiple rows? If so what is the advantage of using a cursor?
What is sqlcode -811?
7 Answers Accenture, CTS, TCS,
What can the SET option of the Repair Utility accomplish?
Suppose if I need to update a column, how you do that using cursor?
insert into tablename a column1, column2 select column1, column2 from tablename b i am getting abend -104..can any one help?
What will the FREE command do to a plan?
What is the use of runstats in db2?
What are the benefits of using the db2 database?
How do you leave the cursor open after issuing a COMMIT? (for DB2 2.3 or above only)
How will you return the number of records in table?