Handling data exceptions in IDMS/SQL
Article ID: 127533
Updated On:
Products
IDMS
IDMS - Database
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.
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:
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:
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
Feedback
Yes
No
Powered by
