Sometimes because of business needs, whenever an update is performed to a primary table, changes are recorded in a HISTORY table. At this point, to save space, multiple fields will be placed in a single comments column. These COBOL fields can be COMP values as well. Once these COMP values are stored in the DB2 CHAR column, it becomes difficult if you want to know the value of that field from the HISTORY table. But below is a query that helps you finding the numeric value of the COMP field.

10 COMP_NUMBER PIC S9(9) USAGE COMP.

Above field is stored in first 4 bytes of the column COMMENT_CLM in the table SCHEMA.DB2_TABLE.

Query to get the numeric value of the COMP field is below.

SELECT

(CASE SUBSTR(CHAR(HEX(SUBSTR(COMMENT_CLM,1,4))),1,1)

WHEN ‘A’ THEN 10

WHEN ‘B’ THEN 11

WHEN ‘C’ THEN 12

WHEN ‘D’ THEN 13

WHEN ‘E’ THEN 14

WHEN ‘F’ THEN 15

WHEN ‘0’ THEN 0

WHEN ‘1’ THEN 1

WHEN ‘2’ THEN 2

WHEN ‘3’ THEN 3

WHEN ‘4’ THEN 4

WHEN ‘5’ THEN 5

WHEN ‘6’ THEN 6

WHEN ‘7’ THEN 7

WHEN ‘8’ THEN 8

WHEN ‘9’ THEN 9

END) * POWER(16,7) +

(CASE SUBSTR(CHAR(HEX(SUBSTR(COMMENT_CLM,1,4))),2,1)

WHEN ‘A’ THEN 10

WHEN ‘B’ THEN 11

WHEN ‘C’ THEN 12

WHEN ‘D’ THEN 13

WHEN ‘E’ THEN 14

WHEN ‘F’ THEN 15

WHEN ‘0’ THEN 0

WHEN ‘1’ THEN 1

WHEN ‘2’ THEN 2

WHEN ‘3’ THEN 3

WHEN ‘4’ THEN 4

WHEN ‘5’ THEN 5

WHEN ‘6’ THEN 6

WHEN ‘7’ THEN 7

WHEN ‘8’ THEN 8

WHEN ‘9’ THEN 9

END) * POWER(16,6) +

(CASE SUBSTR(CHAR(HEX(SUBSTR(COMMENT_CLM,1,4))),3,1)

WHEN ‘A’ THEN 10

WHEN ‘B’ THEN 11

WHEN ‘C’ THEN 12

WHEN ‘D’ THEN 13

WHEN ‘E’ THEN 14

WHEN ‘F’ THEN 15

WHEN ‘0’ THEN 0

WHEN ‘1’ THEN 1

WHEN ‘2’ THEN 2

WHEN ‘3’ THEN 3

WHEN ‘4’ THEN 4

WHEN ‘5’ THEN 5

WHEN ‘6’ THEN 6

WHEN ‘7’ THEN 7

WHEN ‘8’ THEN 8

WHEN ‘9’ THEN 9

END) * POWER(16,5) +

(CASE SUBSTR(CHAR(HEX(SUBSTR(COMMENT_CLM,1,4))),4,1)

WHEN ‘A’ THEN 10

WHEN ‘B’ THEN 11

WHEN ‘C’ THEN 12

WHEN ‘D’ THEN 13

WHEN ‘E’ THEN 14

WHEN ‘F’ THEN 15

WHEN ‘0’ THEN 0

WHEN ‘1’ THEN 1

WHEN ‘2’ THEN 2

WHEN ‘3’ THEN 3

WHEN ‘4’ THEN 4

WHEN ‘5’ THEN 5

WHEN ‘6’ THEN 6

WHEN ‘7’ THEN 7

WHEN ‘8’ THEN 8

WHEN ‘9’ THEN 9

END) * POWER(16,4) +

(CASE SUBSTR(CHAR(HEX(SUBSTR(COMMENT_CLM,1,4))),5,1)

WHEN ‘A’ THEN 10

WHEN ‘B’ THEN 11

WHEN ‘C’ THEN 12

WHEN ‘D’ THEN 13

WHEN ‘E’ THEN 14

WHEN ‘F’ THEN 15

WHEN ‘0’ THEN 0

WHEN ‘1’ THEN 1

WHEN ‘2’ THEN 2

WHEN ‘3’ THEN 3

WHEN ‘4’ THEN 4

WHEN ‘5’ THEN 5

WHEN ‘6’ THEN 6

WHEN ‘7’ THEN 7

WHEN ‘8’ THEN 8

WHEN ‘9’ THEN 9

END) * POWER(16,3) +

(CASE SUBSTR(CHAR(HEX(SUBSTR(COMMENT_CLM,1,4))),6,1)

WHEN ‘A’ THEN 10

WHEN ‘B’ THEN 11

WHEN ‘C’ THEN 12

WHEN ‘D’ THEN 13

WHEN ‘E’ THEN 14

WHEN ‘F’ THEN 15

WHEN ‘0’ THEN 0

WHEN ‘1’ THEN 1

WHEN ‘2’ THEN 2

WHEN ‘3’ THEN 3

WHEN ‘4’ THEN 4

WHEN ‘5’ THEN 5

WHEN ‘6’ THEN 6

WHEN ‘7’ THEN 7

WHEN ‘8’ THEN 8

WHEN ‘9’ THEN 9

END) * POWER(16,2) +

(CASE SUBSTR(CHAR(HEX(SUBSTR(COMMENT_CLM,1,4))),7,1)

WHEN ‘A’ THEN 10

WHEN ‘B’ THEN 11

WHEN ‘C’ THEN 12

WHEN ‘D’ THEN 13

WHEN ‘E’ THEN 14

WHEN ‘F’ THEN 15

WHEN ‘0’ THEN 0

WHEN ‘1’ THEN 1

WHEN ‘2’ THEN 2

WHEN ‘3’ THEN 3

WHEN ‘4’ THEN 4

WHEN ‘5’ THEN 5

WHEN ‘6’ THEN 6

WHEN ‘7’ THEN 7

WHEN ‘8’ THEN 8

WHEN ‘9’ THEN 9

END) * POWER(16,1) +

(CASE SUBSTR(CHAR(HEX(SUBSTR(COMMENT_CLM,1,4))),8,1)

WHEN ‘A’ THEN 10

WHEN ‘B’ THEN 11

WHEN ‘C’ THEN 12

WHEN ‘D’ THEN 13

WHEN ‘E’ THEN 14

WHEN ‘F’ THEN 15

WHEN ‘0’ THEN 0

WHEN ‘1’ THEN 1

WHEN ‘2’ THEN 2

WHEN ‘3’ THEN 3

WHEN ‘4’ THEN 4

WHEN ‘5’ THEN 5

WHEN ‘6’ THEN 6

WHEN ‘7’ THEN 7

WHEN ‘8’ THEN 8

WHEN ‘9’ THEN 9

END) * POWER(16,0) AS COMP_NUMBER

FROM SCHEMA.DB2_TABLE

WHERE CONDITION

——————————————————————————————————–

In United States, If you would like to Earn Free Stocks, Credit card Points and Bank account Bonuses, Please visit My Finance Blog

——————————————————————————————————–

You may also like to look at: