The Case of the Used Unused Object: A Mystery

Published: November 11, 2009

Hey, Ted:

Put on your deerstalker hat. I've got a mystery for you. We are cleaning up disk and stumbled upon something that has us puzzled. The system tells us that a logical file in a test library has been used many times, and according to the object description, was recently used. None of our programs use the file, and no one has used the test library for a long time. Why does the system tell us we're using a file that we don't use?

--Rick

Rick and I solved his mystery with a little effort. There's something to be learned in this weird event, so let's see what happened.

First, running Display File Description (DSPFD) over the logical file in the test library (let's call it library T) revealed the physical file upon which the logical file was built. The physical file turned out to be in a production library (library "P").

Second, running Display Database Relations (DSPDBR) over the physical file listed the logical file in T and also a logical file of the same name in P.

Third, running Display File Description (DSPFD) over the logical file in P showed that the logical file in T owned the access path.

Thus, when an SQL query would run against the physical file, the query optimizer would use the access path of the logical file in T. And when it did so, the system updated the usage date and count in the file's object description. That is, SQL updates object usage figures just as a native open does.

Rick wanted to know how such a situation came about. Obviously the logical file was first created in T. Later someone created the same logical file in P. The system saw no need to build a duplicate access path, so it made the two logical files share an access path.

The solution to Rick's problem was to delete the logical file in the test library. When he did so, the system re-assigned the access path to the logical file in the production library. The SQL query began to update usage figures for the logical file in P. Rick's clean-up crew was able to delete library T.

Another mystery solved. Another good day.

--Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot