Tags

, , , , , , , , , ,

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

Important SQL CODES and ABEND CODES
SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
KNOW YOUR MAINFRAME
REXX – INITIAL SETUP
DB2 SQL Query to read COMP (COBOL) data stored in CHAR column
DB2 Performance – Using SET vs SYSDUMMY1 table
DB2 Performace – Predicates Processing Order
DB2 External Stored Procedures
DB2 UPDATE QUERY – UPDATE TABLE1 FROM TABLE2 DATA
DB2 PERFORMANCE ISSUE (When using EXISTS)
HOW DOES DB2 INTERNALLY (PHYSICALLY) STORE THE DATA
HOW DO YOU FIND WHO HAS ACTUALLY LOADED THE DB2 TABLE RECENTLY
TERMINATE A STOPPED DB2 UTILITY
SELECT UNIQUE RECORDS USING “GROUP BY”
DB2 and SQL INTERVIEW QUESTIONS
DB2 TIPS
Optimistic Locking vs. Pessimistic Locking
DB2 BIND OPTIONS and ISOLATION
Is SCHEMA name necessary for DYNAMIC QUERY
DB2 SQL – REPLACE CHARACTERS WITH ACCENTS (NON ENGLISH ALPHABETS)

 

Advertisements