Desperately seeking the Rosetta Stone
-
I'm am trying to find a "Rosetta Stone" for SQL Server data types as they would be coded in a mainframe COBOL program. We're converting an ancient mainframe application that has Sequential flat files, VSAM files, IMS Database files and some DB2 data over to SQL Server 2000 (This will be interesting).
Thanks,
Gary
Ancient Chinese Curse: May you live in interesting times 🙂
-
Are you looking for the data conversions? Can you give some examples of the COBOL datatypes?
I would look at DTS and using whatever drivers you have and then see how it converts. Might be easier to move everything as char and then decide what to move them to.
Steve Jones
-
Why not to use the Host Integration Server (HIS) from Microsoft?
I also have data on our mainframe (VSAM and DB2 V6.1) and I can access them trough HIS.
I am even queriing from SQLServer my Host based DB2 as a linked server.
Otherwise you just have to FTP somewhere readable by your SQLServer with a defined format and after it is quite easy to import it via DTS or BCP
Bye
Gabor -
For example:
DB2 defines Integer as
-2,147,483,648 to +2,147,483,647.
In COBOL this translates to
PIC S9(9) USAGE COMP.
-
God I miss Cobol, try going from DMSII(hiearcle database) to EBCDIC flat file to tape to Ascii to SQL Server.!!!
Try this site http://www.rosetta-stone.co.uk They're the best in this field.
It doesn't pay to reinvent the wheel unless you can sell it!
(Even if it's cheaper, is it worth the aggravation?)
John Zacharkan
John Zacharkan -
Thanks John, but what I'm really looking for is a cross reference table like this:
SQL Server DB2 COBOL
Integer Integer PIC S9(9) COMP.
Smallint Smallint PIC S9(4) COMP.
I can't hire it done.
-
First what driver are you using to talk to SQL Server from mainframe thru COBOL as this will have berring on what you can do?
-
A mainframe will never talk to SQLServer.
a mainframe is a "BIG" machine.
SQLServer has to learn to speak to the mainframe through OLEDB for example
Bye
Gabor -
Mainframe will not be UPDATING to SQL server directly. We are writing data extract programs to create files to ftp down to server and will import via DTS package for initial load. Need cross reference of MSSQL data types to Mainframe COBOL data definitions (i.e. MSSQL Money = PIC S9(9)V99 in COBOL).
-
Then I think you may be going overboard. If you are writing to flat file then you can use any text data type and use either tab delimination or comma seperated value. No magic involved, DTS can be set up to parse the way you put together.
-
Having been there, done that, my suggestion would be to evaluate the data itself rather than attempting to translate. The mindset and thinking processes between the systems and systems design are quite radically different. From my perspective, I have seen companies go the phase and convert route, and the re-think route, and the re-think route ALWAYS results in a better end system. In many of the cases, the phase and convert route left serious design flaws in an otherwise solid application.
-
Gary I do on a daily basis something similar to what it sounds like you are trying to do. I do have to FTP out the VSAM flat files before using DTS. Is the main problem with converting the packed decimal format for numeric values? If that is the case I may be able to help.
Ross Sines
-
Thanks Ross! That's it exactly and any other differences you may have encountered like dates, etc. to be able to ftp and dts the files into MSSQL.
-
Gary what I do is kind of a pain but it works and does't take very long. I have to use a product called DSDesigner to query the extract from DB2 into a host file. Once the mainframe has extracted the data into a host file I use another app WS_FTP95 to download the host file onto SQL Server as a text file. Finally I get to use DTS to import from the text file into a tempdb where I run the following prcedure.
CREATE PROCEDURE usp_UnPackDollarAmt AS
UPDATE Tmp_tbAdvGenLed
Set Amount =
CASE SignChar
WHEN "{" THEN CONVERT(Float, (DollarAmt + "0"))/100
WHEN "}" THEN CONVERT(Float, (DollarAmt + "0"))/(-100)
WHEN "a" THEN CONVERT(Float, (DollarAmt + "1"))/100
WHEN "b" THEN CONVERT(Float, (DollarAmt + "2"))/100
WHEN "c" THEN CONVERT(Float, (DollarAmt + "3"))/100
WHEN "d" THEN CONVERT(Float, (DollarAmt + "4"))/100
WHEN "e" THEN CONVERT(Float, (DollarAmt + "5"))/100
WHEN "f" THEN CONVERT(Float, (DollarAmt + "6"))/100
WHEN "g" THEN CONVERT(Float, (DollarAmt + "7"))/100
WHEN "h" THEN CONVERT(Float, (DollarAmt + "8"))/100
WHEN "i" THEN CONVERT(Float, (DollarAmt + "9"))/100
WHEN "j" THEN CONVERT(Float, (DollarAmt + "1"))/(-100)
WHEN "k" THEN CONVERT(Float, (DollarAmt + "2"))/(-100)
WHEN "l" THEN CONVERT(Float, (DollarAmt + "3"))/(-100)
WHEN "m" THEN CONVERT(Float, (DollarAmt + "4"))/(-100)
WHEN "n" THEN CONVERT(Float, (DollarAmt + "5"))/(-100)
WHEN "o" THEN CONVERT(Float, (DollarAmt + "6"))/(-100)
WHEN "p" THEN CONVERT(Float, (DollarAmt + "7"))/(-100)
WHEN "q" THEN CONVERT(Float, (DollarAmt + "8"))/(-100)
WHEN "r" THEN CONVERT(Float, (DollarAmt + "9"))/(-100)
END
That at least gets the dollar amount unpacked. It is probably preferable to use decimal datatype here rather than float. I haven't had to deal with converting dates. We leave the date as a text field because I am not using dates in any calculations. I hope this helps.
Ross Sines
-
I was under the impression you needed translation for data types, not conversion routines. That's a whole different animal altogether. Converting values is no problem. Mapping field definitions was what I was talking about above. I have a full EBCDIC to ASCII character mapping chart for Cobol Comp 3 data that if you think would help, I'd be glad to send you. And the dates are no issue either, if they are in the pseudo-julian format from an AS/400.
Seems like an awful lot of people are doing EBCDIC to ASCII conversions lately. There must be a run on it or something.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply