In the SQL Option of CA-IDMS/DB, the HEX() function can be used to circumvent a data exception in a numeric field.
This can be useful if you have corrupted numeric fields.
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.