|
View previous topic :: View next topic
|
| Author |
Message |
Verma,Raman
New User
Joined: 13 Oct 2006
Posts: 15
|
|
|
|
Hi All,
I want to access a DB2 table whose key is a combination of two fields (say A and B).There can be multiple B's under A and both of them combined form a unique combination.
My code needs to read right from the first row till the last and each time process the data that has been fetched from the table.
(It is something like get next call in IMS Db for a full Db extract)
Both A and B are numeric where A is 19 digit and B is 2 digit long.Please advise on what shall be the select statement structure and how shall i use the where clause.
Please revert to me if anything is ambiguous herein.
Raman/Verma |
|
| Back to top |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi There,
I want some clarification.
1) U want to access all the record from db2 table?
if yes then u can simply write
select *
from table
2) U want to access all the record in some special order like order of ur primary key ?
then u can give
select *
from table
order by a,b
Please answer this question so that I will be able to help u |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
| Quote: |
| My code needs to read right from the first row till the last and each time process the data that has been fetched from the table. |
Thats where Cursors come into the picture. Fetch a row, process the row.
| Quote: |
| Both A and B are numeric where A is 19 digit and B is 2 digit long.Please advise on what shall be the select statement structure and how shall i use the where clause. |
You probably need to tell what is the use of the key here, means do you want to accomplish the below process.
1> Get first record.
2> look out for the records having some specific values in A and B.
3> If found process else skip
If this is not the understanding, what is that you need. |
|
| Back to top |
|
|
Verma,Raman
New User
Joined: 13 Oct 2006
Posts: 15
|
|
|
|
Hi Ekta,
The second option will be more useful i.e
select *
from table
order by a,b
Also, please advise if the following idea is correct as I have less kowledge of cursors....
Can we use cursors in this case. Like,extracting full data in a cursor first and then process the rows by incrementing the cursor.
Thanks,
Raman/Verma |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi There,
Cursor processing is mandatory. When multiple rows are returned by an SQL statement, a cursor must be coded. If multiple rows are returned by a SELECT statement not coded using a cursor, DB2 returns a -811 SQLCODE Curosr:
1) Place the DECLARE CURSOR Statement First
The DECLARE CURSOR statement must precede any other commands (such as OPEN, CLOSE, and FETCH) relating to the cursor because of the way the DB2 precompiler parses and extracts the SQL statements from the program.
The DECLARE CURSOR statement is not an executable statement and should not be coded in the PROCEDURE DIVISION of an application program. Although doing so does not cause a problem, it makes your program difficult to understand and could cause others to think that DECLARE is an executable statement.
You should place all cursor declarations in the WORKING-STORAGE section of the application program, immediately before PROCEDURE DIVISION. All host variable declarations must precede the DECLARE CURSOR statement in the application program.
For ur problem
| Code: |
EXEC SQL
DECLARE CURSOR_1 CURSOR FOR
SELECT *
FROM TABLE1
ORDER BY COLA,COLB |
(I am assuming that ur TABLE1 have 3 coloum COLA,COLB,COLC)
2)Open Cursors Before Fetching
Similar to a sequential file, a cursor must be opened before it can be fetched from or closed. You also cannot open a cursor twice without first closing it.
For ur problem
| Code: |
EXEC SQL
OPEN CURSOR_1
END-EXEC.
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN OTHER
raise an error
END-EVALUATE. |
3)Use Care when Specifying Host Variables Used with FETCH
The FETCH statement retrieves data one row at a time .Synchronize the host variables fetched (or selected) with the SELECT list specified in the cursor declaration (or singleton SELECT). If the data type of the columns does not match the host variable, and the data cannot be converted, a compilation error results.
| Code: |
EXEC SQL
FETCH CURSOR_1
INTO :TABLE1-VIEW.COLA,
:TABLE1-VIEW.COLB,
:TABLE1-VIEW.COLC
END-EXEC.
EVALUATE SQLCODE
WHEN +0
Add the code for processing the reocrd
WHEN +100
Set end of cursor
WHEN OTHER
Raise an db2 error
END-EVALUATE. |
here rememeber u have fetch in a loop to process all the record until end of cursor.
4)Explicitly Close Cursors
When a DB2 program is finished, DB2 implicitly closes all cursors opened by the program. To increase performance, however, you should explicitly code the CLOSE statement for each cursor when the cursor is no longer required. The CLOSE statement can be executed only against previously OPEN ed cursors
| Code: |
EXEC SQL
CLOSE cursor_1
END-EXEC.
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN OTHER
raise an error
END-EVALUATE. |
|
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
| Ekta... Very well explained. |
|
| Back to top |
|
|
|
|
