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
 

 

VSAM to DB2 migration question

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: VSAM to DB2 migration question
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: 1712
Location: UK

PostPosted: Thu Jan 15, 2015 5:28 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Jan 15, 2015 10:47 pm    Post subject:
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    Post subject:
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: 1712
Location: UK

PostPosted: Tue Jan 20, 2015 1:17 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Fri Jan 23, 2015 3:34 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Jan 26, 2015 8:43 pm    Post subject:
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    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 BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts VSAM define for large file jerryte JCL & VSAM 9 Wed Oct 05, 2016 1:51 am
No new posts CICS VSAM RLS READ akodakka CICS 3 Tue Oct 04, 2016 3:09 pm
No new posts Fault Analyzer - listings question. egrove IBM Tools 4 Thu Aug 11, 2016 5:31 pm
No new posts dataset copy question - REPRO or some... atulxp TSO/ISPF 2 Wed Aug 03, 2016 10:56 pm


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