Download data from Mainframe DB2 table

noorkh · New User Joined: 06 Mar 2006 Posts: 76 Location: Chennai

Do we have any tool to download data from Mainframe DB2 table to a spreadsheet? If any freeware is there please pass me link.

Noor.

dick scherrer · Posted: Tue May 15, 2007 2:20 am

Hello,

Other than ftp no software 'tool' is needed.

From the db2 table, create a delimited file on the mainframe making sure that all fields are in the output file are "displayable" - there should be no packed-decimal (comp-3) or binary (comp) fields in the output.

When the file is ftp'd (pushed or pulled) to the win-based system, it will go directly into a spreadsheet, database, etc.

noorkh · New User Joined: 06 Mar 2006 Posts: 76 Location: Chennai

Hi Dick,

I agree with your point. But all of my tables are containing COMP-3 fields or binary. So i might need to write program to make it displayable first and then need to ftp it to excel.

I can do it if there are 1 or 2 tables to be downloaded, what if i need to download more than 25 tables. For all the 25 tables, writing delimited programs will become little vague work.

I heard from my colleagues that there are some tools available in market which gives u direct download to excel. And it even displays COMP-3 values.

They are not aware of the tool names. Can you suggest me if you have any idea of that.

dick scherrer · Posted: Tue May 15, 2007 7:43 pm

Hello,

I'm not positive, but i believe that the DB2 UNLOAD will create a delimited file.

My preference has been to use COBOL - almost every database table, vsam file, and major flat file has a copybook. It is trivial to take a simple sleketon bit of code, use the copybook, and STRING the desired fields interspersed with the delimiter-character into an output area and write. The advantage of COBOL is that when something other than a "dumb dump" of the data is needed, code may be easily added (quite often data to be downloaded is de-normalized for ease of use in a spreadsheet or data warehouse) and data from additional tables may be incorporated into the data being sent to a win-based product.

In your case 25 may sound like many pieces of work. In the past several months, i've created, transferred, and loaded several hundred different formats of data. This is between mainframe, unix, and win-based systems.

As far as 3rd-party software, i know of no free mainframe tools and (so far) no tools for purchase that do all or even most of what is needed when transferring data to/from the mainframe. Several vendors have tools that work quite well between SQLServer and Oracle etc - some even do some DB2 (that will grow). From my experience, the tools are just not there yet but there is growing requirement.

superk · Posted: Tue May 15, 2007 8:17 pm

I seem to recall that most of the popular data mapping/transformation products had this capability - Gentran, Mercator, Cloverleaf. I'm sure there are others as well.