Post reply

Desperately seeking the Rosetta Stone

  • January 7, 2003 at 10:41 am

    #64488

    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 🙂

  • January 7, 2003 at 10:54 am

    #445744

    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

    sjones@sqlservercentral.com

    http://qa.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 7, 2003 at 11:06 am

    #445746

    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

  • January 7, 2003 at 11:45 am

    #445753

    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.

  • January 7, 2003 at 12:22 pm

    #445756

    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

  • January 7, 2003 at 12:32 pm

    #445760

    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.

  • January 7, 2003 at 3:00 pm

    #445780

    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?

  • January 7, 2003 at 4:10 pm

    #445803

    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

  • January 8, 2003 at 12:15 pm

    #445875

    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).

  • January 8, 2003 at 2:52 pm

    #445887

    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.

  • January 8, 2003 at 8:39 pm

    #445916

    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.

  • January 9, 2003 at 9:58 am

    #445954

    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

    rsines@mail.maricopa.gov

  • January 9, 2003 at 10:29 am

    #445958

    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.

  • January 13, 2003 at 2:38 pm

    #446222

    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

    rsines@mail.maricopa.gov

  • January 13, 2003 at 11:08 pm

    #446258

    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