How to extract data from VSAM Files

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Aruna Gutti
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 21, 2007 9:35 am
Location: Boston

How to extract data from VSAM Files

Post by Aruna Gutti »

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
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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, Parallel Job Developer's Guide)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Aruna Gutti
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 21, 2007 9:35 am
Location: Boston

Post by Aruna Gutti »

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.
Aruna Gutti
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 21, 2007 9:35 am
Location: Boston

Post by Aruna Gutti »

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.
pradkumar
Charter Member
Charter Member
Posts: 393
Joined: Wed Oct 18, 2006 1:09 pm

Post by pradkumar »

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
pradkumar
Charter Member
Charter Member
Posts: 393
Joined: Wed Oct 18, 2006 1:09 pm

Post by pradkumar »

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
Aruna Gutti
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 21, 2007 9:35 am
Location: Boston

Post by Aruna Gutti »

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.
pradkumar
Charter Member
Charter Member
Posts: 393
Joined: Wed Oct 18, 2006 1:09 pm

Post by pradkumar »

Thanks a lot Aruna,iam working on that .If i have any more questions i will be in contact with you.
Pradeep Kumar
pradkumar
Charter Member
Charter Member
Posts: 393
Joined: Wed Oct 18, 2006 1:09 pm

Post by pradkumar »

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
Aruna Gutti
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 21, 2007 9:35 am
Location: Boston

Post by Aruna Gutti »

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.
Aruna Gutti
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 21, 2007 9:35 am
Location: Boston

Post by Aruna Gutti »

I hope your job is parallel job.
pradkumar
Charter Member
Charter Member
Posts: 393
Joined: Wed Oct 18, 2006 1:09 pm

Post by pradkumar »

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
Aruna Gutti
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 21, 2007 9:35 am
Location: Boston

Post by Aruna Gutti »

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.
pradkumar
Charter Member
Charter Member
Posts: 393
Joined: Wed Oct 18, 2006 1:09 pm

Post by pradkumar »

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.
Aruna Gutti
Premium Member
Premium Member
Posts: 145
Joined: Fri Sep 21, 2007 9:35 am
Location: Boston

Post by Aruna Gutti »

Do you have any Comp or Comp-3 fields in your data ? Is there any specific reason for creating a Server job vs Parallel job in version 8.0.1 ? My understanding is with the realease of version 8x IBM's focus is more on Parallel than server.

I played with server CFF Stage in version 7.5.2 and didn't liked it that much. In that job my options were DataFormat 'ASCII', Record Style 'CR/LF'.
Post Reply