Dave, In 2006 I did a project in Ohio to offload a bunch of data files to MS SQL. They had completed a SAP implementation, but had not bothered to deal with all the historical data left behind on the old mainframe. They had VSAM files, QSAM disk files, thousands of tapes, and even a few IMS databases. Altogether they had nearly a terabyte of data to preserve.
The goal was to make the migrated data accessible in the MS SQL environment. There would be no going back to the mainframe. It was thought that only a handful of IT staff would ever have cause to look at the preserved data, but they were reluctant to just scrap it all. A big problem was that the data records contained a mix of text and binary fields (COMP and COMP-3). This implied that I needed to do field level transformations. I had budget constraints. The client would not pay for any special ETL tools (e.g. Informatica), and the budget for the SQL server was under $10K. This is how I performed the project: (1) Had the client purchase a modest Windows Server, a few SATA hard disks of 750GB each, and installed MS SQL Server 2005 Workgroup Edition. (2) Took a survey of all the mainframe datasets within scope. (3) Loaded information about the mainframe datasets into an MS Access database. (4) Analyzed the Access DB to try to identify dataset families that share the same record layouts. (5) Matched COPYBOOKS against the dataset families (a mostly manual task). (6) Wrote a bunch of little data extract programs for each of the COPYBOOKS. These programs would transform the data from mainframe format to comma-separated-value (CSV) format. The programs also created SQL table DDL, SQL BCP format files, FTP commands, etc. (7) Ran the extract programs. (8) FTP'ed the extracted data to the SQL Server. Also FTPed the DDL, BCP Formats, etc. (9) Ran the DDL to define the SQL tables. (10) Ran the BCP scripts to load the tables. (11) Wrote some DOC so future IT staff could relate old mainframe DSNAMES to the new SQL Tables. John ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
