1

I have a DB2 database consuming a byte encoded zip code. I'm told that the mainframe reads in the standardize US zip/postal code and encodes it to byte format (using COBOL) due to space constraints. The DB2 database reads in the encoded zip code into a VARCHAR(9) field. Is there any way to take the DB2 output and translate it back to the original zip code to represent the data as if it came in as a normal 5-4 format zip/postal code?

Ideally my solution would be in SQL, but python or C# would be helpful as well. Thank you.

Here are some examples of the encoded zip code coming off the mainframe.

134771824 the address is for Hartford, Ct and i would expect it to be 06103.

122900373 for Belfast Me 04915

In this use case, I’m working on the back end Teradata warehouse. I’m ingesting this information from IBM DB2 database and the raw data is stored on IBM COBOL mainframe. The mainframe doesn’t maintain the original zip code.

Seems like I should be able to manipulate the 9 digits back to 5 characters is the goal

Andrew Bourret
  • 11
  • 2
  • 2
    I might assume that "byte-encoded" means the data is COMP-3, but without even one example it's impossible to be sure, especially as 9 digits would take 5 bytes in COMP-3, not 9. Or do you maybe mean it is EBCDIC not Ascii?. Show us what input your program is getting and what output you expect. – BoarGules May 18 '22 at 21:31
  • Where and in what language do you want to do the conversion – Hogstrom May 19 '22 at 15:43
  • converting using Teradata SQL – Andrew Bourret May 25 '22 at 14:02

1 Answers1

1

A character zip code would take up 9 bytes of character data (thus VARCHAR(9)) so the question is what is the column data type that hosts the encoded zip code?

Given that there are missing details this is speculation but I think its a reasonable educated guess that they are storing the value as a COBOL COMP-3 type which is packed decimal on IBM Z. Below are the characters and then the hex representation immediately below them.

Characters    123456789    
Hex Values    FFFFFFFFF
              123456789

Characters       ∨←        
Hex Values    13579
              2468F

You can see that the character values in the first example takes up 9 bytes and the the number of bytes in the second example is 5.

Based on your question you can:

COBOL

77 ENCODED-ZIP PIC 9(9) USAGE COMP-3. 77 CHAR-ZIP PIC 9(9).

MOVE CHAR-ZIP TO ENCODED-ZIP.

READ the Db2 row and place the zip into CHAR-ZIP. Moving it to take the Db2 VARCHAR(9) and place it in a PIC 9(9) field and then move it to a PIC 9(9) COMP-3.

Please see this [StackOverflow answer] (packed decimal to zoned decimal or decimal conversion python) as it more broadly addresses your question

Hogstrom
  • 3,581
  • 2
  • 9
  • 25