Use HEX() call to circumvent data exceptions
search cancel

Use HEX() call to circumvent data exceptions

book

Article ID: 9782

calendar_today

Updated On:

Products

IDMS IDMS - Database IDMS - ADS

Issue/Introduction

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.

Environment

CA IDMS/DB all supported releases.

Resolution

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