IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

VSAM to DB2 migration question


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rao Vivek

New User


Joined: 13 Jan 2015
Posts: 2
Location: India

PostPosted: Thu Jan 15, 2015 1:09 pm
Reply with quote

Hi,

I am currently working on a VSAM to DB2 migration project. I have a question about how to handle OCCURS clause in DB2. Below are some details about what I am doing.

Code:
Data in VSAM file:
            |-----|-----|-----|
|--|--|--|--|--|--|--|--|--|--|
1234001002003011012011013011014
1235004005006012015012016012017

01 WS-RECORD.
   05 WS-REC-KEY                   PIC 9(4).
   05 WS-FIELD-1                   PIC 9(3).
   05 WS-FIELD-2                   PIC 9(3).
   05 WS-FIELD-3                   PIC 9(3).
   05 WS-FIELD-OCC OCCURS 3 TIMES
      10 WS-FIELD-4                PIC 9(3).
      10 WS-FIELD-5                PIC 9(3).


DB2 Tables:

MASTER TABLE (Primary Key: WS-REC-KEY)
|-----------|------------|------------|------------|
|WS-REC-KEY | WS-FIELD-1 | WS-FIELD-2 | WS-FIELD-3 |
|-----------|------------|------------|------------|
|      1234 |        001 |        002 |        003 |
|-----------|------------|------------|------------|
|      1235 |        004 |        005 |        006 |
|-----------|------------|------------|------------|

OCCURS TABLE (Primary Key: SEQ NO + WS-REC-KEY, Foreign Key: WS-REC-KEY)
|-------|------------|------------|------------|
|SEQ NO | WS-REC-KEY | WS-FIELD-4 | WS-FIELD-5 |
|-------|------------|------------|------------|
|    01 |       1234 |        011 |        012 |
|-------|------------|------------|------------|
|    02 |       1234 |        011 |        013 |
|-------|------------|------------|------------|
|    03 |       1234 |        011 |        014 |
|-------|------------|------------|------------|
|    01 |       1235 |        012 |        015 |
|-------|------------|------------|------------|
|    02 |       1235 |        012 |        016 |
|-------|------------|------------|------------|
|    03 |       1235 |        012 |        017 |
|-------|------------|------------|------------|


The question I have is how do I get data from the Master and Occurs table in the same format as that of a VSAM file record. Basically, is there a way to get multiple rows into a single record format in DB2?

I have tried using JOIN but that gives me a result set which I would then have to read using a CURSOR and apply logic within the program specifically to get the data into a single record format.

Let me know if you need further information on this.

Please Help !!!
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Jan 15, 2015 5:28 pm
Reply with quote

You cannot, as far as I know, escape using a cursor. That is what it is there for: processing multiple rows that meete the condition.. So, you will need to process the cursor for each occurrence in the 'occurs' table.

If you held the number of occurrences in the 'master' table you could retrieve all the rows in one FETCH.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2637
Location: NYC,USA

PostPosted: Thu Jan 15, 2015 10:47 pm
Reply with quote

I am not sure if this works but just a thought, Try using CASE stmts and convert these rows of same WS-REC-KEY to multiple columns of WS-FIELD-4 and WS-FIELD-5 and then put a join between these two tables.
Back to top
View user's profile Send private message
Rao Vivek

New User


Joined: 13 Jan 2015
Posts: 2
Location: India

PostPosted: Mon Jan 19, 2015 2:06 pm
Reply with quote

Thank you for your responses. Sounds like I dont have much if a choice than to use a Fetch loop. icon_confused.gif
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Jan 20, 2015 1:17 pm
Reply with quote

It's what loops are for.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jan 23, 2015 3:34 pm
Reply with quote

Code:
select MASTER.REC_KEY , FIELD1 , FIELD2 , FIELD3

,max(case when SEQNO = 1 then FIELD4 end) as FIELD4_1
,max(case when SEQNO = 1 then FIELD5 end) as FIELD5_1
,max(case when SEQNO = 2 then FIELD4 end) as FIELD4_2
,max(case when SEQNO = 2 then FIELD5 end) as FIELD5_2
,max(case when SEQNO = 3 then FIELD4 end) as FIELD4_3
,max(case when SEQNO = 3 then FIELD5 end) as FIELD5_3

from MASTER join OCCURSTB on MASTER.REC_KEY = OCCURSTB.REC_KEY
group by MASTER.REC_KEY , FIELD1 , FIELD2 , FIELD3
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2637
Location: NYC,USA

PostPosted: Mon Jan 26, 2015 8:43 pm
Reply with quote

@GuyC: You read my mind.... icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Assembler test under mask question PL/I & Assembler 5
No new posts VSAM file Storage Calculation JCL & VSAM 3
No new posts FILE STATUS - 39 ON VARIABLE LENGTH V... COBOL Programming 2
No new posts Need opinion on mainframe to cloud mi... General Talk & Fun Stuff 6
No new posts VSAM Alter Command - Cylinder Extent ... JCL & VSAM 3
Search our Forums:

Back to Top