DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic is not resolved, but there is a WORKAROUND.
Author Message
Aruna Gutti



Group memberships:
Premium Members

Joined: 21 Sep 2007
Posts: 145
Location: Boston
Points: 989

Post Posted: Tue Oct 02, 2007 12:26 pm Reply with quote Back to top

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
Hello,

I am looking for any information on how to extract data from a VSAM file. I could not find any hits on 'VSAM' when I searched the forum. Is anyone out there extracting data from MainFrame VSAM files ? If so, could they please let me know how it is being done either in 8.x or earlier DS versions ?

Thanks a lot,

Aruna.
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51684
Location: Canberra, Australia
Points: 280688

Post Posted: Tue Oct 02, 2007 2:19 pm Reply with quote Back to top

You can use an I-WAY Connector (in version 8 only). The Complex Flat File stage can "read data from one of more complex flat files, including MVS datasets with QSAM and VSAM files." (Chapter 10 ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
Aruna Gutti



Group memberships:
Premium Members

Joined: 21 Sep 2007
Posts: 145
Location: Boston
Points: 989

Post Posted: Wed Oct 03, 2007 10:51 am Reply with quote Back to top

Hi Ray,

Thanks for your reply. What do I specify for 'file type' and 'record type' in the 'file options' tab for a VSAM file in a CFF stage ?

Could you please let me know ?

Many Thanks,

Aruna.
Rate this response:  
Aruna Gutti



Group memberships:
Premium Members

Joined: 21 Sep 2007
Posts: 145
Location: Boston
Points: 989

Post Posted: Wed Nov 28, 2007 11:28 am Reply with quote Back to top

I could find another way to work around extract data from VSAM files.

This is what I did:

Created a small routine in Mainframe to convert VSAM file to sequential file and ftp to DataStage server.

Use Complex Flat file stage to map the data, transform and load.

The reason for the work around is, we don't have federation server or i-way server and we still need to play with VSAM files.
Rate this response:  
Not yet rated
pradkumar


since November 2006

Group memberships:
Premium Members

Joined: 18 Oct 2006
Posts: 393

Points: 4233

Post Posted: Wed Feb 27, 2008 3:53 pm Reply with quote Back to top

Hi Aruna,

Iam also facing the same problem of extracting the data from VSAM files.Iam new to datastage and working on version 8 windows. Could you help me how to extract the vsam files .Do i need to convert the VSAM files to sequential file and then ftp to datastage server . or can i directly ftp the VSAM file on to the datastage server. Because the mainframe guy is saying the vsam file is large .

Thanks a lot

Sree
Rate this response:  
Not yet rated
pradkumar


since November 2006

Group memberships:
Premium Members

Joined: 18 Oct 2006
Posts: 393

Points: 4233

Post Posted: Wed Feb 27, 2008 3:54 pm Reply with quote Back to top

Hi Aruna,

Iam also facing the same problem of extracting the data from VSAM files.Iam new to datastage and working on version 8 windows. Could you help me how to extract the vsam files .Do i need to convert the VSAM files to sequential file and then ftp to datastage server . or can i directly ftp the VSAM file on to the datastage server. Because the mainframe guy is saying the vsam file is large .

Thanks a lot

Sree
Rate this response:  
Not yet rated
Aruna Gutti



Group memberships:
Premium Members

Joined: 21 Sep 2007
Posts: 145
Location: Boston
Points: 989

Post Posted: Wed Feb 27, 2008 6:02 pm Reply with quote Back to top

Hi Sree,

Do you have Federation Server ? If so, you can directly connect to VSAM files in version 8. If not, please follow the following steps.

1) First you need to convert a VSAM file to a Seq file. This can be done by running simple IDCAMS JCL on the Mainframe side.

2) FTP the Sequential VSAM file to your DataStage server in binary format.

3) You can use Complex Flat File Stage to extract (import) this Sequential file into your DataStage jobs.

Good luck,
Aruna.
Rate this response:  
Not yet rated
pradkumar


since November 2006

Group memberships:
Premium Members

Joined: 18 Oct 2006
Posts: 393

Points: 4233

Post Posted: Thu Feb 28, 2008 11:56 am Reply with quote Back to top

Thanks a lot Aruna,iam working on that .If i have any more questions i will be in contact with you.

_________________
Pradeep Kumar
Rate this response:  
Not yet rated
pradkumar


since November 2006

Group memberships:
Premium Members

Joined: 18 Oct 2006
Posts: 393

Points: 4233

Post Posted: Tue Mar 04, 2008 2:09 pm Reply with quote Back to top

Hi Aruna,

Iam working on version 8.0.1 on windows server. So in CFF stage i could only see

Dataformat : as NLS

Record Style as : Binary or CR/LF.

what do i need to tell to mainframe person to convert VSAM file in to ???I( which format )

Is it fine if the mainframe person converts VSAM file to text file ?

Thanks a lot

Sree
Rate this response:  
Not yet rated
Aruna Gutti



Group memberships:
Premium Members

Joined: 21 Sep 2007
Posts: 145
Location: Boston
Points: 989

Post Posted: Tue Mar 04, 2008 2:56 pm Reply with quote Back to top

Hi,

Where are you seeing those options ? The options as per my CFF job are as follows for 'Record Options' in Stage properties.

Byte order : Native-endian

Character set : EBCDIC

Data format : Binary

As far as converting VSAM file is concerned, just ask the Mainframe person to convert VSAM into a flat file. The flat file can be either a Fixed Block or Variable block depending on your VSAM file record type.

A flat file is not technically a text file as there won't be any field delimiters and quote characters. It is just a stream of characters that can be mapped to a given record format.

After creating this flat file, FTP it to your Windows server in 'binary' mode.

The VSAM conversion can be done using existing IBM Utilities like IDCAMS in the JCL.

Regards,

Aruna.
Rate this response:  
Not yet rated
Aruna Gutti



Group memberships:
Premium Members

Joined: 21 Sep 2007
Posts: 145
Location: Boston
Points: 989

Post Posted: Tue Mar 04, 2008 2:59 pm Reply with quote Back to top

I hope your job is parallel job.
Rate this response:  
Not yet rated
pradkumar


since November 2006

Group memberships:
Premium Members

Joined: 18 Oct 2006
Posts: 393

Points: 4233

Post Posted: Tue Mar 04, 2008 3:40 pm Reply with quote Back to top

Hi ,

First of all thanks for your quick reply.

My job is server job.I could only see Data format as NLS and Record style as Binary and CR/LF , Record Length , a check box for verify sign value for decimal comp-3 and a check box for preserve NULL in General tab of CFF stage of the server job.

The mainframe person converted the VSAM file to text file . I copied that text file on to my datastage server which is on windows. In the CFF stage i gave Data format as default NLS and record style as CR/LF .After that i am able to view the data but some records contains junk characters.
i would like to confirm am i doing in a correct way or not.

Iam new to datastage so please understand.

Thanks

Sree
Rate this response:  
Not yet rated
Aruna Gutti



Group memberships:
Premium Members

Joined: 21 Sep 2007
Posts: 145
Location: Boston
Points: 989

Post Posted: Tue Mar 04, 2008 6:05 pm Reply with quote Back to top

I haven't worked on Server CFF stage in version 8.0.1. I don't have access to try it right now. I will try it tomorrow morning at work and will let you know what options you need to specify.
Rate this response:  
Not yet rated
pradkumar


since November 2006

Group memberships:
Premium Members

Joined: 18 Oct 2006
Posts: 393

Points: 4233

Post Posted: Tue Mar 04, 2008 6:33 pm Reply with quote Back to top

No problem .

This is the text file with some sample records.

2007050710319990000401HVD01Z C
2007051411412240000101CMA14Z ) & C
2007051411412240000102EXC01Z C
2007051411412240000103FSC14Z - - X
2007051411412240000104SEA01T q  C
2007051411412240000105SEL01C q  I

I dont know whether this file works in CFF stage or not ?



Thanks

Sreekanth
Rate this response:  
Not yet rated
pradkumar


since November 2006

Group memberships:
Premium Members

Joined: 18 Oct 2006
Posts: 393

Points: 4233

Post Posted: Tue Mar 04, 2008 6:34 pm Reply with quote Back to top

Hi Aruna,

Did you check the CFF stage in server and please let me know how to work out this issue.

Many Thanks

Sreekanth


Last edited by pradkumar on Wed Mar 05, 2008 5:42 pm; edited 1 time in total
Rate this response:  
Not yet rated
Display posts from previous:

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours