Happy Easter to all of you. The next tip will be published on the 20st of April.
Jeg vil starte med at ønske alle mine læsere en god påske. Det næste tip udkommer den 20. april.
One of the most irritating SQL errors is SQLCODE -805. This error has no relation to the actual SQL statement being executed and may occur at the most inconvenient times. In the following tip I will do my best to clarify why SQLCODE -805 occurs and how you solve the problem. The first thing to look at is the information DB2 makes available for you on a -805. If the error handler on your installation formats the contents of the SQLCA area, you will receive an error text looking like this:
If the SQLCA has not been formatted you can locate the Location-name.Collection-id.Dbrm-name.Consistency-token, Plan-name and Reason in the SQLERRMT field. Especially the Consistency-token is easy to recognise and in most case it is also the most important information.The Consistency-token is a formatted hexadecimal string of 16 characters representing an 8 bytes timestamp.
You have now found the Location-name, Collection-id (which is almost always empty), Dbrm-name (name of the program issuing the SQL statement), Consistency-token, Plan-name and Reason. First of all you must examine if the Dbrm-name is part of the package list for Plan-name. You do that simply by examining the value of Reason. If Reason is 01 or 02 then Dbrm-name is not part of the package list. You have to make a new BIND of the plan where you include the Dbrm-name in the package-list. Alernatively you execute the program using another plan which already has the Dbrm-name included in the package-list.
If Reason is neither 01 nor 02 your package list is probably OK and the problem is more likely that the load module you are executing contains a wrong Consistency-token. And now you must listen very carefully: the 16 bytes long formatted consistency-token comes from the load module receiving SQLCODE -805 and not from DB2. Now you have two options. Either you executed another load module than you thought (mot likely a new load module version alternatively another load module from another load library) or the Dbrm-name exists in more than one collection and the collection used in your plan is the wrong collection.
The most common reason to receive a SQLCODE -805 is the execution of another load module than you thought. Using the Consistency-token you can find the load module you executed. Please browse the load module you thought you were executing when SQLCODE -805 occured and execute a FIND X'Consistency-token' FIRST. If you do not get any hit then you must swap the first 8 characters of Consistency-token with the last 8 and try again. If you still do not find anything, you know for sure that it was another load module that was executing when you received the SQLCODE -805. Somehow the search for the load module was mislead. In batch you must examine your STEPLIB/JOBLIB concatenation, in TSO you must examine your ISPLLIB/STEPLIB concatenation and in CICS you must examine the DFHRPL concatenation.
If you found the Consistency-token in the load module, the error is most likely occuring because the package bind coresponding to the program failed. Many installations has developed an unhealthy habit of LINK'ing the LOAD-module before they BIND the package. If the package BIND fails you immediately expose yourself to the risk of receiving SQLCODE -805. I can only encourage you to BIND first and LINK afterwards. If the package BIND did not fail then the only explanation for the SQLCODE -805 is that the package list for the plan does not contain the collection which Dbrm-name was BIND'ed into.
Debugging SQLCODE -805 is a complicated task. There is more help to get by clicking here. At this point you might really like to know why on earth you must search for the Consistency-token where the first and last 8 bytes are swapped. The reason is that the first 8 bytes of the Consistency-token really is the four bytes of the first of two fields containing the consistency token in binary format. Depending on the programming language the two fields are stored in different order. In COBOL the order is swapped while PL/I keeps the correct order. If you know that your Dbrm-name is a COBOL program you may start the search by swapping the first and last 8 bytes and save a search. I do not know the order for other programming languages. Please perform your own research.