We recently ran into severe problems with command RCLDBXREF & as a result would like to see a few changes made.
Last week (11/01/18) we noticed that system catalogue table QADBFDEP was missing entries for certain joint logical files on our production IBM i called HEM9A. This is currently at 7.1 of the o/s, technical refresh 11. The error with QADBFDEP was found against many different files in many different libraries. HEM9A holds 3,272 libraries (at the time of writing), around 1.9 million objects. Many database libraries hold the same set of files in them.
RCLDBXREF *CHECK found no errors. RCLDBXREF *FIX against a single library known to have missing entries corrected the error. As so many entries in QADBFDEP were incorrect, RCLDBXREF *FIX was attempted over all libraries on HEM9A, one after another, in alphabetical order & in quick succession. Around 1,800 libraries had RCLDBXREF *FIX applied before this process was stopped by ENDJOB *IMMED.
The consequence was that our ODBC jobs then hung for more than 24 hours - so PMR 02009,001,866 was raised.
The reason why these ODBC jobs hung was as follows:
1. RCLDBXREF *FIX rebuilds certain system catalogue tables like QADBXREF and QADBFDEP for the specified library, but it then relies on the solitary QDBSRVXR2 job to complete the rebuild of system catalogue tables QADBIFLD and QADBKFLD.
2. As QDBSRVXR2 is a system job its priority can't be improved.
3. While QDBSRVXR2 is rebuilding QADBIFLD and QADBKFLD they can't be read by SQL, query, etc. Applications will just hang. Even command DSPPFM just hangs. This also applies to the views based of these system catalogues, such as SYSCOLUMNS. The reason for this behaviour given by IBM in a conference call meeting on PMR 02009,001,866 (12/01/18) was is that it doesn't make any sense to allow applications to read the data in the system catalogues before they are completely rebuilt & a true reflection of the database.
4. As a result, on this occasion to rebuild took many hours & had severe impact on our ODBC jobs because ODBC uses view SYSCOLUMNS. These jobs only continued once the rebuild had completed.
Also note that we were advised in to run RCLSTG SELECT(*DBXREF), which meant bringing down HEM9A into the restricted state. What we didn't realise at the time was that this RCLSTG also relies on job QDBSRVXR2 to rebuild QADBIFLD and QADBKFLD, running this after HEM9A was back up again. This took several hours overnight, meaning our ODBC jobs were still down.
If there was a document explaining how RCLDBXREF *FIX and RCLSTG *DBXREF work then the attempt to fix all our HEM9A libraries wouldn't have been made. The only IBM document that I know of, or has been offered to me by IBM, is this found on the knowledge centre: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/cl/rcldbxref.htm
This is the same text as the command help. This text includes the caveat "When the command is used to recover cross-reference information, the user must not allow applications to use or modify objects in a library that is being reclaimed. Failure to do this could cause unpredictable results.", which means the user can't really use RCLDBXREF with any confidence, aware that if there are problems then IBM may just quote this statement as cover.
This leads me to the request. It's in four parts, in order of difficultly:
1. Initially, we would like IBM to provide better information about how RCLDBXREF *FIX and RCLSTG *DBXREF work. Specifically, we want what we have learnt recently & described above spelt out to the user so they can truly gauge the risks in using these commands.
2. Then it would be better if RCLDBXREF *FIX didn't rely on the single QDBSRVXR2 job to complete the rebuild of QADBIFLD and QADBKFLD. This should be done by the instance of RCLDBXREF *FIX being run for the library it's being run over so the work doesn't take too long to do.
3. Also, the rebuild process shouldn't prevent the reading of the system catalogues tables. I understand the logic behind the reason given by IBM, but it causes too many problems. Also, while the system catalogues have errors they aren't a true reflection of the database anyway.
4. Ultimately the system catalogues should be self-repairing, given their importance to ODBC. They are also used by the database engine to prevent duplicate long file names when a file is created, renamed or restored, so it's in everyone's interest that their data can never go stale. The onus shouldn't be on the user to ensure the data in the system catalogues is up-to-date & to then try to repair them when errors are found.
Use Case: As described above, the system catalogue tables are used by applications such as ODBC, They are also used by database engine to ensure the uniqueness of long file names. Finally, they are a important database analysis tool.
IBM does not intend to provide a solution to this request at this time, so it is being closed.
For your first request, we have published a description of the reclaim options in the 7.3 and 7.4 versions of IBM Documentation. You can access that information here:
https://www.ibm.com/docs/en/i/7.4?topic=database-reclaim-cross-reference-files
For part 2, we agree that this would be possible to do. Since the cross reference files do not usually become damaged, investing in a performance enhancement for this situation is a low priority. The database team has many higher priority items to implement, so this suggestion is unlikely to be provided any time soon.
There is no intention to implement the ideas for parts 3 and 4. Part 3 could cause potential application failures because the cross reference information might not describe a consistent snapshot. Part 4 would require a background job that continually looks for problems, which would use considerable system resources.
Submitter, and others, please respond to question from Mark Anderson below by posting a Comment in this RFE.
Almost any DB XREF problem is a bug (with the possible exception of damage). Luckily, such problems are pretty rare.
We have several possible ideas on what could be done to improve performance and potentially reduce contention, but
since XREF problems are not very common, the priority of these is low.
However, we should provide better documentation and have plans to add addiitonal information to Knowledge Center later this year. We are also considering documenting the same information in an Info APAR.
The information will cover:
1) the effects on queries of XREF files (ODBC, JDBC, .NET, and user queries) i.e. if we do not wait, applications can get unpredictable results.
2) the performance of DBXREF (the number of files in the library is the prime factor)
3) a short comparison of the three options to fix DB XREF but note that all three can take some time
4) any best practice if the DB XREF does have a problem
Lastly, your problem occurred on 7.1 which is almost out of service, I assume you have moved to a later release.
Have you had any other problems with XREF after upgrading?