CODEWITHSUNDEEP
sql-servervb.netcobol
Kris
Kris

Reputation: 189

Convert COBOL data to SQL Server

We received data from COBOL as below, and got to convert to SQL Server. I have followed this link

and did create the SSIS package. However i'm facing issue when converting some data from COBOL to SQL.

I marked the input columns as Bytes and Output as Numeric data as 10 and Precision 2

1.Tried creating columns as Decimal(10,2) in SQL
2.Tried it with SQL date data type for COBOL date

the package runs successfully but data is not populated. data layout

CONTRACT-SIZE         pic S9(9)V99 COMP-3         
DIV-REC-DATE-LOBO     PIC  9(0007) COMP-3  
PAR-DATE              PIC S9(0007) COMP-3  
BID-PRICE             PIC S9(0005)V9(2)  COMP-3

Tried below function to unpack, Translate, COBOLZonedPicToDecimal

Public Function Translate(ByVal str As String, sConversionTable As String) As String

    Dim Temp As String, I As Int32
    Temp = Space(Len(str))
    For I = 1 To Len(str)
        Mid(Temp, I, 1) = Mid(sConversionTable, Asc(Mid(str, I, 1)) + 1, 1)
    Next
    Translate = Temp
End Function

Private Function Unpack(ByVal bPacked As Byte(), ByVal iDecimalPlaces As Integer) As Decimal
    Dim i As Integer
    Dim HiNibble As Byte
    Dim LoNibble As Byte
    Dim dResult As Decimal = 0


    For i = 0 To bPacked.Length - 2
        UnpackNibblesToBytes(bPacked(i), LoNibble, HiNibble)
        dResult = Convert.ToDecimal(dResult * 10 ^ 2I + HiNibble * 10 + LoNibble)

    Next
    UnpackNibblesToBytes(bPacked(bPacked.Length - 1), LoNibble, HiNibble)

    dResult = dResult * 10 + HiNibble

    If LoNibble = &HD& Then
        dResult = -dResult
    End If

    Unpack = Convert.ToDecimal(dResult * 10 ^ (-iDecimalPlaces))

End Function

Private Sub UnpackNibblesToBytes(ByVal InputNibbles As Byte, ByRef LoNibble As Byte, ByRef HiNibble As Byte)

    LoNibble = (Me.F And InputNibbles)
    HiNibble = ((Me.F0 And InputNibbles) >> 4)

End Sub

Public Function DecimalToCOBOLZonedPic(ByVal dNumber As Decimal, ByVal iPadLength As Integer, ByVal iDecimalPlaces As Integer, ByVal bModifiedZoned As Boolean) As String
    Dim isNegative As Boolean = False
    Dim strNumber As String
    Dim sPositive As String = "{ABCDEFGHI"
    Dim sNegativeMod As String = "}JKLMNOPQR"
    Dim sNegativeStrict As String = "pqrstuvwxy"

    ' Determine the sign
    If dNumber < 0 Then
        isNegative = True
        dNumber = -dNumber
    End If

    dNumber = Convert.ToDecimal(dNumber * (10 ^ iDecimalPlaces))

    strNumber = dNumber.ToString

    If strNumber.IndexOf(".") > -1 Then
        ' Truncate remaining decimal places
        strNumber = strNumber.Substring(0, strNumber.IndexOf("."))
    End If

    ' Pad with leading zeros
    If strNumber.Length < iPadLength Then
        strNumber = strNumber.PadLeft(iPadLength, "0"c)
    End If

    Dim lastDigit As Integer = Convert.ToInt32(strNumber.Substring(strNumber.Length - 1, 1))

    If bModifiedZoned Then
        If isNegative Then
            strNumber = strNumber.Substring(0, strNumber.Length - 1) & sNegativeMod.Substring(lastDigit, 1).ToString
        Else
            strNumber = strNumber.Substring(0, strNumber.Length - 1) & sPositive.Substring(lastDigit, 1).ToString
        End If
    Else
        If isNegative Then
            strNumber = strNumber.Substring(0, strNumber.Length - 1) & sNegativeStrict.Substring(lastDigit, 1).ToString
        End If
    End If

    Return strNumber
End Function

Public Function COBOLZonedPicToDecimal(ByVal strNumber As String, ByVal decimalPlaces As Integer) As Decimal
    Dim sZoneChar As String
    Dim convertedNumber As Decimal
    Dim sPositive As String = "{ABCDEFGHI"
    Dim sNegativeMod As String = "}JKLMNOPQR"
    Dim sNegativeStrict As String = "pqrstuvwxy"

    strNumber = strNumber.Trim

    If strNumber = "" Then
        Return 0
    End If

    sZoneChar = strNumber.Substring(strNumber.Length - 1)

    Select Case True
        Case sPositive.IndexOf(sZoneChar) > -1
            strNumber = strNumber.Substring(0, strNumber.Length - 1) & sPositive.IndexOf(sZoneChar)
            convertedNumber = Convert.ToDecimal(strNumber)

        Case sNegativeMod.IndexOf(sZoneChar) > -1
            strNumber = strNumber.Substring(0, strNumber.Length - 1) & sNegativeMod.IndexOf(sZoneChar)
            convertedNumber = -Convert.ToDecimal(strNumber)

        Case sNegativeStrict.IndexOf(sZoneChar) > -1
            strNumber = strNumber.Substring(0, strNumber.Length - 1) & sNegativeStrict.IndexOf(sZoneChar)
            convertedNumber = -Convert.ToDecimal(strNumber)
        Case Else
            convertedNumber = Convert.ToDecimal(strNumber)
    End Select

    Return Convert.ToDecimal(convertedNumber / (10 ^ decimalPlaces))
End Function

Upvotes: 1

Views: 3080

Answers (1)

Bruce Martin
Bruce Martin

Reputation: 10543

Current File

0x0200EAA4 converted into EBCDIC is x0200529F which is a valid comp-3 value (2020-May-29).

Running an ascii conversion on a binary EBCDIC file (like this file) will corrupt the File Do not try and process the current file.

Solutions

  • Convert the file to Text on the source compute (Mainframe ??) and transmit/translate the file

  • If processing a Mainframe Cobol File have a look at cb2xml. It will convert a Cobol Copybook into a Xml file (and calculate position / length). The Xml can be processed in many languages.

  • Convert to fixed length on source compute (probably is already Fixed length) and transfer as an EBCDIC file. The file will not have any carriage return line-Feed characters in it. You would need to process it as an EBCDIC file.

If you can

  • run java
  • Get the Cobol Copybook
  • There are no redefines in the Cobol copybook

Some thing like CobolToCsv can convert the file from Cobol-Ebcdic into an Ascii-Csv file

EBCDIC

There are multiple EBCDIC character-sets Cp037/IBM037 is US EBCDIC; CP273/IBM273 is German EBCDIC you will need to find out which EBCDIC dialect is being used

Note: CP037 stands for Coded-Page 037

Mainframe Fixed width

With mainframe fixed width, all records are the same length and there are no Carriage Return / Line Feed characters. For a file with 26 byte records it is

<-- 26 bytes --><-- 26 bytes --> ... <-- 26 bytes -->

cb2xml

cb2xml will convert a Cobol Copybook to xml. This allows for generic processing of Cobol data in most modern languages

cb2xml will convert

 01 Location-Record.
     03 Record-Type            pic xx.
     03 location occurs 10.
        05 DC-Number           pic 9(4).
        05 Pack-Quantity       pic 9(8).

to 

<item display-length="122" level="01" name="Location-Record" position="1" storage-length="122">
    <item display-length="2" level="03" name="Record-Type" picture="xx" position="1" storage-length="2"/>
    <item display-length="12" level="03" name="location" occurs="10" position="3" storage-length="12">
        <item display-length="4" level="05" name="DC-Number" numeric="true" picture="9(4)" position="3" storage-length="4"/>
        <item display-length="8" level="05" name="Pack-Quantity" numeric="true" picture="9(8)" position="7" storage-length="8"/>
    </item>
</item>

Upvotes: 2

Related Questions