Data Import through DTS problem

Posted on 2006-05-29
Last Modified: 2013-11-30
I am importing data from mainframe using DTS the problem is that I am getting some COMP fields from the mainframe these are compressed data fields that are used to reduce storage space on the main frame. Can some one please help me understand how we can use DTS to solve this problem that is decompress it before transferring to table in SQL server.

Question by:atwork2003
    LVL 5

    Expert Comment

    Hi atwork2003,

    I am very familiar with the COMP-3 (signed) fields coming from MVS and we also go back and have the mainframe developers move the data to a normal field before creating our maintenance files. I was somewhat intrigued by the thought of trying to convert from COMP-3 to ASCII in SQL and did some research. It is a tough subject to find and I would venture to guess that even if it is possible, it would not be easy. An interesting article that I did find states that the conversion is not possible:

    (I will be keeping an eye on this post to see if someone has some type of solution (other than one of the EBCDIC to ASCII conversion programs) to do the conversion.)

    LVL 30

    Expert Comment

    If there is an algorithm to decompress it and there are no magic decrompession keys that you don't have then yes you can write an algortihm on SQL Server to uncompress it.

    Normally you would import it into a staging table, fix it in the staging table then copy the staged data into a live table.
    LVL 30

    Expert Comment

    ...though I know absolutely nothing about COMP-3.
    LVL 30

    Expert Comment

    I guess what I am saying is that if you find the algorithm then I can help you convert that algorithm to SQL.

    Author Comment

    I don't have any algorithm for it there fore I am here. pls help

    Author Comment

    I was just searching on the web and found that there are conversion charts for EBCDIC to ASCII is there one for Comp to ASCII. this may help in developing a code that can do the translation based on the conversion chart.
    LVL 5

    Expert Comment

    Hi atwork2003,

    The EBCDIC to ASCII conversions work on a 1 to 1 relationship... In EBCDIC an 'A' is hex value C1, in ASCII the hex value is 65. This works great for 1 hex value to 1 hex value. COMP-3 fields are different, and there is no way to use the conversion to decompress the value into a readable form. The strange characters are typically based on the sign of the value (+ or -). There is some speculation that you could ignore the sign and somehow convert the remainder to an integer or decimal value but I have yet to ever see this.

    I created a small copybook in OS/390 with comp-3 signed fields and sent some data down to a SQL Server. The values are here:


    05  PC-SEQUENCE-CNTRL                   COMP-3.
         10  PC-FILE-NUMBER      PIC S9(5).
         10  PC-TYPE-BUSINESS    PIC S999.

    Data in EBCDIC:


    ASCII Translation:


    Things get much more complicated when there are NULL values associated with the data because it fails to send the information with the file, causing any fixed record length layouts to become unstructured.

    Your best bet is to have the information translated into readable information (non comp fields) on the mainframe before the file is sent to you for processing. This is a very simple task for the mainframe programmers. They simply create a copybook with non-compressed fields, read in the original data into the comp-3 copy book, move the data to the non-comp-3 fields in the new copybook and output the data into a new file for you.

    LVL 7

    Accepted Solution


    Below is vb code that we have that converts Comp3 packed signed packed data, to unsigned.

    We convert the value to a string, then manipulate the string and convert it back to a number.

    Since the final character of the comp3 string is the only packed piece, we initialized the conversion lookups in three public string variables, then we call the unpack function as needed.

    Since you are doing this in DTS, hopefully you can reuse the code fairly easily. It would not be too hard to make it a SQL function either.

    Good Luck,


    Public Comp3SourceValue As String
    Public Comp3OutputValue As String
    Public Comp3OutputSignMult As String

    Function InitComp3()
    'Input Values, may contain Comp3 values that have an embedded sign
    'We must convert these values to separate sign values.
    'For conversion speed we will load the conversion table into a collection
    'Value Character
    '  +0         {
    '  +1         A
    '  +2         B
    '  +3         C
    '  +4         D
    '  +5         E
    '  +6         F
    '  +7         G
    '  +8         H
    '  +9         I
    '  -0         }
    '  -1         J
    '  -2         K
    '  -3         L
    '  -4         M
    '  -5         N
    '  -6         O
    '  -7         P
    '  -8         Q
    '  -9         R

    Comp3SourceValue = "{ABCDEFGHI0123456789}JKLMNOPQR"
    Comp3OutputValue = "012345678901234567890123456789"
    Comp3OutputSignMult = "++++++++++++++++++++----------"

    End Function

    Public Function UnpackSignedValue(strPackedValue As String, Optional intReturnDecimalPlaces As Variant) As Double

    'Packed Values have the sign embedded in the last character.
    Dim SignCharacter As String
    Dim MatchPOS As Integer
    Dim strUnpacked As String

    If IsMissing(intReturnDecimalPlaces) Then intReturnDecimalPlaces = 0
    If Len(Comp3SourceValue) = 0 Then InitComp3

    SignCharacter = Right(strPackedValue, 1)
    MatchPOS = InStr(Comp3SourceValue, SignCharacter)

    If MatchPOS > 0 Then
       strUnpacked = (Left(strPackedValue, (Len(strPackedValue) - 1)) & Mid(Comp3OutputValue, MatchPOS, 1))
       UnpackSignedValue = CDbl(strUnpacked) / CInt(Left("1000000", (CInt(intReturnDecimalPlaces) + 1)))
       If Mid(Comp3OutputSignMult, MatchPOS, 1) = "-" Then UnpackSignedValue = UnpackSignedValue * -1
       MsgBox "Invalid Packed Signed Field" & vbCrLf & "Packed Value: " & strPackedValue, vbCritical, "Unable to Translate Field Value"
    End If

    End Function

    Author Comment

    I appreciate all your help but isn't there a difference in COMP AND COMP3.
    so can I apply the same code to the comp fields.
    LVL 7

    Expert Comment


    If memory serves me right, Comp-3 and Comp-5 were the only data types that used the packed value. Comp-3 being a decimal and Comp-5 being a integer. Other comp types were stored in a display format.

    The following link has more info,

    Hope this helps,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now