Get started Bring yourself up to speed with our introductory content.

Unexpected results after doing OVRDBF then SQL

We have a problem that we don't know whether it is a bug or we just don't understand how should it work. The problem is concerned to OVRDBF and DB2. If you execute the OVRDBF command and then perform any SQL statement you get strange results.

For example: I use "OVRDBF FILE(TAB) TOFILE(TIROSH2/TAB)" and then I start SQL environment (STRSQL) and perform the statement: "SELECT * FROM TIROSH3/TAB". What I really get are rows that come from TIROSH2 collection and NOT from TIROSH3. I don't understand why.

If I perform the override to a collection that does not exist (for example KUKU), then in the select I get the message: TAB in KUKU type *FILE not found. I expect that I get the rows from the qualified file. What is the explanation? I tried the situation on several iSeries machines with releases from 4.3 to 5.1 (latest CUM PTF and group PTF SF99501-07 V5R1M0 Apr. 12, 2002). The results are the same. I haven't found any documentation regarding this situation.

OVRDBF performs many other tasks than just pointing to a file instead of another one. If such an OVRDBF is in place and SQL (or another program) attempts to open the file that is overridden, the override will run even if you qualify that file. If you need this opening to be immune from the override, you will need to open your file before running the override. Another way is to use yet another override to make sure the right file is opened. For instance:



"SELECT * FROM TAB3" will give you TIROSH3/TAB records while "SELECT * FROM TAB" will give you TIROSH2/TAB records.

Please note: The SECURE in the override that makes sure the other OVRDBF is not considered when opening TAB3.


Search400's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: tips, tutorials and more.

Check out this online event, Getting the Most out of SQL & DB2 UDB for the iSeries.

Dig Deeper on iSeries SQL commands and statements