Use HEX() call to circumvent data exceptions

Document ID : KB000009782
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

In the SQL Option of CA-IDMS/DB, the HEX() function can be used to circumvent a data exception in a numeric field.

Background:

This can be useful if you have corrupted numeric fields.

Environment:
CA IDMS/DB all supported releases.
Instructions:

Consider the following data and SQL result:

SELECT ROWID, * FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=1000;
*+
*+                ROWID  DEPT_ID_0410
*+                -----  ------------
*+  X'0125990100000008'          1000
*+
*+ DEPT_NAME_0410                                 DEPT_HEAD_ID_0410
*+ --------------                                 -----------------
*+ PERSONNEL                                                     13
*+
*+ 1 row processed

Now use FIX PAGE to corrupt the DEPT_HEAD_ID_0410 field:

FIX PAGE 75161
VER 0050 40F0,F0F1,F300
REP 0050 4040,4040,4040;

Obviously when you try and SELECT that field you will get a data exception:

SELECT DEPT_HEAD_ID_0410 FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=1000;
*+ Status = -4       SQLSTATE = 22005        Messages follow:
*+ DB001025 T105 C-4M322: Data exception

However, if you use the HEX() function, the SELECT will return whatever hex data is on the database:

SELECT HEX(DEPT_HEAD_ID_0410) FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=1000;
*+
*+ HEX(FUNCTION)
*+ -------------
*+ 40404040
*+
*+ 1 row processed

 

This can be useful if you know you have some corrupt data and are trying to narrow down where it is.

Additional Information:

For more information, see the following CA IDMS DocOps page:

 

HEX-function