One of the SQL errors I often help others with is SQLCODE -904. In order to solve a -904 you have to display the complete contents of the SQLCA and preferably in a formatted version:
If you only have access to the unformatted SQLCA you need to look for something like this (using the above example): 00C90081 00000200 DB000011.TS000033. Please note that it is not necessarily a space that separates the three pieces of information, namely REASON, TYPE OF RESOURCE and RESOURCE NAME. If you do not have access to the SQLCA the information is available in the DB2-MSTR address space in the JES message log. If you have to use the message log it may be a challenge to find the message related to your -904. Look for the string UNAVAILABLE RESOURCE and look at the time the logging was produced. It may take some time, but it works.
A SQLCODE -904 means that some kind of DB2 resource is unavailable. It may be a plan, a package, a bufferpool, an internal work space, something else and most likely a tablespace. In order to determine why the resource is unavailable you need to locate the REASON in IBM Information Center. Follow the description and type in the actual REASON in the Search: field and press the PC enter button (NOT the mainframe Enter). On the left hand side you locate the DB2 version you are running and click on the link. Now you have found the most in-depth description available from IBM. If you need to know the TYPE OF RESOURCE you can use this link.
In the above example you will reach the conclusion that 00C90081 means that the tablespace has been stopped. You might also find that TYPE OF RESOURCE 00000200 means a tablespace. Now you have the opportunity to see how a stopped tablespace looks like using a -DISPLAY DB command. The easiest way to carry out DB2 commands is to use a TSO DSN SYSTEM(XXXX) where you replace XXXX with the name of your DB2 subsystem. When this command replies DSN you type in (using the above example): -DIS DB(DB000011) and press Enter:
I have removed a couple of lines and indentations in the output if you wonder why the
above result does not look like your own output. Here it is easy to see that the -904 has
produced the correct error message. Sometimes it might happen that the problem meanwhile
has been solved by someone else. This is why it is a good idea to use the -DISPLAY DB
command. Unfortunately some installations does not allow developers to use the -DISPLAY DB
command, so do not be surprised if you are not allowed to use the above command. If you
can the output might consists of a lot of lines. You can limit the output to tablespaces or
indexspaces having problems by using a
This command only displays tablespaces or indexspaces with limited access. To gain access to a stopped tablespace or indexspace someone must carry out a -START DB(DB000011) SPACE(TS000033) (in this example). Most likely you do not have authorisation to this command If you have you need to consider whether someone else stopped the tablespace for a real good reason. In such a case it might cause further problems than yours to start it again without further investigation.