extract data from mainframe

times29 · Post by **times29** » Fri Jun 14, 2013 4:12 pm

Hi,
I have a requirement to pull data from mainframe any idea what steps we need to do or any document or link i can see which can give me idea who it can be done.

Thanks

chulett · Post by **chulett** » Fri Jun 14, 2013 4:57 pm

First I would verify that you do actually have to "pull" the data from the mainframe. A more typical solution would be for the mainframe to create an extract file of the data you need and push it to the ETL server, where you would then consume it. And hopefully it's "dumbed down" for you so it comes over in ASCII with any packed fields unpacked.

To do an actual pull, I'm assuming you'd need the "390/Mainframe" version of DataStage but I'll leave that to others with actual experience there to confirm/deny.

ray.wurlod · Post by **ray.wurlod** » Sat Jun 15, 2013 2:58 am

Most of the "pull from mainframe" activities of which I'm aware have used the FTP stage type. But the mainframe application needs to have created one or more files that can be transferred via FTP.

I am also aware of some cases where DB2 Connect has been installed on the DataStage engine and the DB2 stage used to retrieve data from DB2/z.

clmhwybe · Post by **clmhwybe** » Sun Jun 16, 2013 6:05 am

best thing is to determine size of the data sets that you want to pull if >1gigs then flatten data for packed fields (convert to ASCII from EBCDIC) on mainframe and then ftp/sftp over to the datastage. this will save you the processing time for conversion on datastage.
another alternative approach is using ftp stage to pull latest generation from mainframe.
if pulling from db2 then add the connection entry for db2 z/os and then u can fetch the data directly from db2 on z/os.

ArndW · Post by **ArndW** » Sun Jun 16, 2013 7:11 am

While those suggestions can have an effect, they are not universal. Usually one has no control over the data types so one cannot unpack packed fields and thus cannot convert from EBCDIC to ASCII on the way from the host to the server.

Likewise the connectivity between host DB2 and server requires using software that IBM doesn't provide free and thus not every site can read the host DB2 tables directly.

chulett · Post by **chulett** » Sun Jun 16, 2013 8:08 am

ArndW wrote:While those suggestions can have an effect, they are not universal. Usually one has no control over the data types so one cannot unpack packed fields and thus cannot convert from EBCDIC to ASCII on the way from the host to the server.

Sure... but if they are going to prepare a file for transfer, can't hurt to ask.

FranklinE · Post by **FranklinE** » Mon Jun 17, 2013 8:54 am

Given the added expense being covered for the connector channels, we routinely implement direct access from mainframe to DS jobs, the method being determined by the source platform.

For z/OS (MVS) datasets, we use FTP Enterprise as the first stage. Yes, you can substitute isolated creation of files on the DS server, but that adds a minimum of doubling of the I/O and another point of failure, not to mention the need for substantial storage on the server destination(s). We use DB2 Connect in similar fashion.

The COBOL FD import works very well for us. I tend to edit the copybook before input to clean up certain things (remove extraneous comments, most 88 level clauses are unnecessary for us, resolve redefines based on how those data items will be used, etc.)

We don't yet have something like Classic Federation to allow us to use Complex Flat File directly, but I see that as the ideal especially for mainframe files with multiple record formats. For now, I use FTP and position-lenght parsing based on record type. It's tedious but works very well.

For an overview, see also "Using Mainframe Source Data" on the FAQ site: viewtopic.php?t=143596

rameshrr3 · Post by **rameshrr3** » Mon Jun 17, 2013 1:04 pm

I use an ODBC stage to read data from DB2 on Z/OS. There are config options in the odbc wire protocol driver to specify an OS/390 or AS/400 data source. Not absolutely necessary to have DB2/Connect unless you want to use teh Db2 specific stages ( API, DB2 Enterprise or DB2 connector) . For files we mostly ftp files as ASCII text files ( Fixed or Delimited) . But we do get some files which require a complex flat file stage. That being said we also have a license for DS/390 so we have no issues to worry about

- but most of our ETL process do not require much work happening off the mainframe directly.