'Converting mainframe byte zip code to varchar or string

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

pythonsqldatabasemainframejavascriptvue.jsvuejs3bootstrap-5


Solution 1:[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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Hogstrom

Related Questions