I need to extract, transform, and load a file from Mainframe (MVS) into two tables in DB2. There are 200 million records at the source. Each record contains header information in the first 440 bytes, and 1 to 106 detail records in the remaining bytes of each source record. I need a tool that will split the source record into its component records for DB2, which can then be loaded by invoking the db2 LOAD command.
Ideally, it would be a program that would take two inputs, the data file and a COBOL definition file (copybook), and its output would be a named pipe, which could then be referenced by the LOAD utility.
I can spend thousands on the right tool, but not tens of thousands. I know I could do this with something like C++, but I don't have time for the learning curve. Can anyone help?
Solutions that use the least amount of drive space are preferred, because disk space in my environment costs $300 per gig (no that is not a misprint).
--Forgive the fool for he knows not what he asks
Originally posted by damru
You've not specified the type of MVS file and on which basis you want to split the file.
Haven't I? There are two destination tables, header and detail. Header records will come from the first 440 bytes of the source record. Detail records will come from bytes 441-675, 676-910, etc. There is a detail count field in position 164:165. All data is in EBCDIC (text, numbers, dates), packed decimal, and zoned.
I cannot post any sample data because of HIPAA restrictions.
You can use TSO PRINTDS command with COLUMNS keyword to fetch from one col position to next col position into a new dataset.
Then you can load that new dataset to the corrosponding table using EXTERNAL keyword.
use of EXTERNAL keyword is bit difficult as you have to take care of each and every column pos in control structure.
Thanks for the input, but I doubt that's going to help me. First, the DB2 server is on a completely different platform than the mainframe. Second, mainframe processing is prohibitively expensive (our dept of 24 people was charged US$600,000 last year for what little we got out of it). Third, creating a new dataset would require over 60gig of hard drive space, which would cost an additional $18,000.
I need an off-mainframe solution. That is, I want to ftp the dataset to a local workstation and go from there.