Handling data exceptions in IDMS/SQL

Products

• CA IDMS
• CA IDMS - Database
• CA IDMS - ADS

Issue/Introduction

This article describes some possibilities for dealing with data exceptions in IDMS/SQL.

The CA-IDMS SQL/Option is less tolerant of non-numeric data in numerically defined fields than network-defined databases are.
When it encounters such data, a DB001025 DATA EXCEPTION abend is issued.

Environment

CA IDMS, all supported releases.

Resolution

If the SQL command in question is being executed in a CA-IDMS/Server environment, there are some settings which will allow the SQL engine to ignore the bad data and return a particular value.
See Handling Invalid Numeric Data.

To set this attribute in an ODBC datasource, see Setting Advanced Data Source Options.

To set it in a JDBC environment, use the InvalidDecimal value in the Properties File Information and use the valid values specified in Configuration Information.

The problem with those options is that the actual value can not be retrieved, only the predefined values of 0 or NULL can be returned to the application program.

Another option, in any environment, is to use the HEX() scalar function, which returns the internal hexadecimal value of a numeric field as a character string and will do so even if the passed parameter is a numeric field which has invalid data.
For example, assuming that the column in this example has an internal value of c'****', or x'5C5C5C5C', the following result is returned:

SELECT DEPT_HEAD_ID_0410 FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=5300;     
*+ Status = -4       SQLSTATE = 22005        Messages follow:                
*+ DB001025 T70 C-4M322: Data exception                                      
SELECT HEX(DEPT_HEAD_ID_0410) FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=5300;
*+                                                                           
*+ HEX(FUNCTION)                                                             
*+ -------------                                                             
*+ 5C5C5C5C                                                                  
*+                                                                           
*+ 1 row processed

Unfortunately there is no scalar function to do the reverse of HEX().
A user-written SQL function could be created, or something like this could be done if a subset of expected invalid values are anticipated:

SELECT SUBSTR('*?%',                                           
(LOCATE(SUBSTR(HEX(DEPT_HEAD_ID_0410),1,2), '5C,6F,6C')+2)/3,1)
FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=5300;                
*+                                                             
*+ SUBSTR(FUNCTION)                                            
*+ ----------------                                            
*+ *                                                           
*+                                                             
*+ 1 row processed

The function call above will look at the first character in the DEPT_HEAD_ID_0410 field and return c'*', c'?' or c'%' depending on whether it is x'5C', x'6F' or x'6C' respectively.

Additional Information

DB001025
Defining and Using (SQL) Functions