We're looking for a solution to the following problem:
Using MS Access 97 to query an ODBC linked DB2 table on OS/390, we get incorrect results when the query joins a subquery. The same problem occurs when one table is linked from DB2 on OS/390 and the second table is local to MS Access 97.
You can reproduce this problem very easily with DB2 system tables (problem is not related to what tables are being used).
Define a query in MS Access: qry_Not_SYSIBM
Link DB2 table SYSCOLUMNS via ODBC
Select * (all columns for viewing)
Select column TBCREATOR and set criteria to <> "SYSIBM"
Run this query and note the results -- I get 2552 rows returned.
Now define another query: qry_Not_Finding_Unmatched
Link table SYSCOLUMNS
Select * (all columns for viewing)
Joint (outer) the first query qry_Not_SYSIBM
Join on columns NAME, TBNAME and TBCREATOR
Set the join properties to type 2 (include all records from SYSIBM.SYSCOLUMNS
and only those records from qry_Not_SYSIBM
where joined fields are equal)
Select column qry_Not_SYSIBM.COLNO for viewing and set criteria to "Is Null"
Now run the query. If I run with the linked DB2 table, my result set is empty which is flat wrong, and there are NO error messages whatever. If I create a local copy (local to Access 97) of the SYSCOLUMNS table, my result set has 1000 rows which is correct (the difference between the whole of SYSCOLUMNS minus those records not containing "SYSIBM" in column TBCREATOR.
If the joined subquery is turned into a view in DB2 on OS/390 and linked, the results are correct. The problem is that this approach, if used in the system we're trying to develop, severly limits ad hoc reporting by the user community. That is unacceptable.
Does anyone know of any patches to correct this problem?
Using MS Access 97 to query an ODBC linked DB2 table on OS/390, we get incorrect results when the query joins a subquery. The same problem occurs when one table is linked from DB2 on OS/390 and the second table is local to MS Access 97.
You can reproduce this problem very easily with DB2 system tables (problem is not related to what tables are being used).
Define a query in MS Access: qry_Not_SYSIBM
Link DB2 table SYSCOLUMNS via ODBC
Select * (all columns for viewing)
Select column TBCREATOR and set criteria to <> "SYSIBM"
Run this query and note the results -- I get 2552 rows returned.
Now define another query: qry_Not_Finding_Unmatched
Link table SYSCOLUMNS
Select * (all columns for viewing)
Joint (outer) the first query qry_Not_SYSIBM
Join on columns NAME, TBNAME and TBCREATOR
Set the join properties to type 2 (include all records from SYSIBM.SYSCOLUMNS
and only those records from qry_Not_SYSIBM
where joined fields are equal)
Select column qry_Not_SYSIBM.COLNO for viewing and set criteria to "Is Null"
Now run the query. If I run with the linked DB2 table, my result set is empty which is flat wrong, and there are NO error messages whatever. If I create a local copy (local to Access 97) of the SYSCOLUMNS table, my result set has 1000 rows which is correct (the difference between the whole of SYSCOLUMNS minus those records not containing "SYSIBM" in column TBCREATOR.
If the joined subquery is turned into a view in DB2 on OS/390 and linked, the results are correct. The problem is that this approach, if used in the system we're trying to develop, severly limits ad hoc reporting by the user community. That is unacceptable.
Does anyone know of any patches to correct this problem?