[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Data Import through DTS problem

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

Thanks
0
atwork2003
Asked:
atwork2003
  • 3
  • 3
  • 2
  • +1
1 Solution
 
bwdowhanCommented:
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:

http://dbforums.com/showthread.php?t=731556

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

Brian
0
 
nmcdermaidCommented:
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.
0
 
nmcdermaidCommented:
...though I know absolutely nothing about COMP-3.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
nmcdermaidCommented:
I guess what I am saying is that if you find the algorithm then I can help you convert that algorithm to SQL.
0
 
atwork2003Author Commented:
Hi
I don't have any algorithm for it there fore I am here. pls help
Thanks
0
 
atwork2003Author Commented:
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.
thanks
0
 
bwdowhanCommented:
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:

Copybook:

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

Data in EBCDIC:

Ûóf
Ûó²
Ûó*°

ASCII Translation:

154825
154822
154821

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.

Brian
0
 
jaanthCommented:
Atwork2003,

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,

jaanth
 


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
Else
   MsgBox "Invalid Packed Signed Field" & vbCrLf & "Packed Value: " & strPackedValue, vbCritical, "Unable to Translate Field Value"
End If

End Function
0
 
atwork2003Author Commented:
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.
Thanks
0
 
jaanthCommented:
Atwork2003:

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, http://www.discinterchange.com/TechTalk_COBOL_comp_.html

Hope this helps,
jaanth
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now