Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Full Table Read with a concatenated key as unique key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Verma,Raman

New User


Joined: 13 Oct 2006
Posts: 15

PostPosted: Fri Oct 13, 2006 12:18 pm    Post subject: Full Table Read with a concatenated key as unique key
Reply with quote

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
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Fri Oct 13, 2006 12:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Fri Oct 13, 2006 12:34 pm    Post subject: Re: Full Table Read with a concatenated key as unique key
Reply with quote

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
View user's profile Send private message
Verma,Raman

New User


Joined: 13 Oct 2006
Posts: 15

PostPosted: Fri Oct 13, 2006 12:39 pm    Post subject: re:Full Table Read with a concatenated key as unique key
Reply with quote

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
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Fri Oct 13, 2006 1:27 pm    Post subject:
Reply with quote

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
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Sat Oct 14, 2006 8:29 pm    Post subject: Re: Full Table Read with a concatenated key as unique key
Reply with quote

Ekta... Very well explained.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Read of TDQ fails in LENGERR Tracy Hansen CICS 10 Wed Apr 05, 2017 12:10 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us