Do you know that it is possible to turn on a trace using the DSN command processor? Or that you can turn on trace for DB2 programs using the CAF interface. It is quite simple, but the outcome is rather limited, unfortunately.

I will start by showing you how it works for the CAF interface. If you are running TSO the simplest solution is to execute the command TSO ALLOC FI(DSNTRACE) DA(*) and afterwards your screen will turn red with trace output every time you execute a program that uses the CAF interface. QMF uses the CAF interface as well as most of the REXX/DB2 interfaces. When you are tired of tracing you execute a TSO FREE FI(DSNTRACE) and everything is back to normal. As long as your TSO session has a connection to DB2 you cannot free DSNTRACE because the CAF interface has opened DSNTRACE. If you want to use the same facility in batch, you simply allocate a DD-name of DSNTRACE, like //DSNTRACE DD SYSOUT=*. Your trace output will go to this file and TSO doesn't need to be involved at all.

Trace on the DSN command processor is acheived by issuing a DSN SYSTEM(<ssid>) TEST(101). The trace output will be written to SYSTSPRT. In other words all trace output will be directed to your screen if DSN is invoked online. The number 101 I have chosen because two digit numbers only gives you a limited trace output. When you use a two digit number, only trace output from the DSN-modules ending on the specified number will be produced. All values greater than 100 indicates tracing of all DSN-modules. The module executing the SQL statements is called DSNETRAP and a value of 16 makes it possible to get trace output only when SQL statements are executed. The trace output stops when the DSN command processor stops executing. If you allocate DSNTRACE the trace output goes to DSNTRACE instead of SYSTSPRT. This is quite convenient especially in batch.

When you have tried these trace facilities you may be a little disappointed. The output is simply tremendous and most of it seems to be absolutely useless. The most important part is usually the execution of SQL statements and the CAF interface produces the resulting sqlcode. The DSN command processor only allows you to see that SQL statements are executed. Every single line of trace output is prefixed with a DSN-message. All these different messages are documented in the messages and codes manual for DB2. In this way you can learn more about the trace output.