Problem Note 37611: Incorrect results are possible when you use the INDEX function without a comparison in the z/OS environment
Previously, the SAS® System allowed the use of the INDEX function without a comparison when you access the DB2 database with a LIBNAME statement. For the z/OS environment only, SAS automatically adds a comparison to the query before submitting it to DB2 because DB2 does not permit queries that do not have a comparison. However, it is possible that SAS might add an incorrect comparison, potentially leading to incorrect results. To avoid this possibility, a required fix has been created so that SAS no longer adds a comparison automatically under z/OS.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Note: After you apply this hot fix, if you do not include a comparison in your query, you will receive the following error:
ERROR: DB2 prepare error: DSNT408I SQLCODE = -199, ERROR:
ILLEGAL USE OF KEYWORD FOR. TOKEN MICROSECONDS MICROSECOND
SECONDS SECOND MINUTES MINUTE HOURS WAS EXPECTED DSNT418I
SQLSTATE = 42601 SQLSTATE RETURN CODE.
This occurs because, as mentioned previously, DB2 does not permit queries that do not have a comparison operation. For example, you will receive the error if you use a WHERE clause similar to the following:
where index(last_name,'ST')
To avoid the error, you must include a comparison, as shown in this example:
where index(last_name,'ST) > 0
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to DB2 | z/OS | 9.1 TS1M3 SP4 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Problem Note |
| Priority: | medium |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> DB2
|
| Date Modified: | 2009-10-28 14:24:20 |
| Date Created: | 2009-10-27 11:16:29 |
