Implementing Siebel Business Applications on DB2 UDB for z/OS > Maintenance Considerations for z/OS > Dynamic SQL in the Siebel Application >

Advantages of Spooling SQL in the Siebel Application

Using spooling with the Siebel application can be useful for a number of reasons:

• It allows you to identify the SQL statement being executed when an SQL error code is returned.
• It can help you in assessing the time taken by the various SQL statements when you encounter performance issues.
• When data appears to be missing from a view, you can identify the SQL used to retrieve that data.

You can then run the SQL manually, either from the midtier machine, for example using the DB2 Command Center, or on the DB2 for z/OS host, for example using tools such as SPUFI, to see what data is being returned.

To minimize the amount of SQL tracing produced, and, therefore, the size of the log files, limit the tests to the minimum number of screen changes needed to allow the issue to be reproduced. If possible, spool SQL on a developer client as you have more control over the testing which makes it easier to minimize the amount of SQL traced.

Turning on SQL Spooling for the Object Manager and the Database Connector

You can turn on component event logging for the Developer and Mobile Web Clients and the server component. Within this category, you can spool the SQL generated by the Siebel Object Manager and by the database connector. You can have either the object manager spooling or the SQL spooling, but not both, as the amount of data returned will be confusing.

To turn on SQL spooling for the Object Manager

• To turn on SQL spooling for the Object Manager, set the component event Object Manager SQL Log to 4.

To turn on SQL spooling for the database connector

• To turn on SQL spooling for the database connector, set the component event SQL Parse and Execute to 5.

For some components, the SQL Profiling event can be used to identify the most poorly performing queries.

For Developer clients, you can also set these component events as environment variables. They work in addition to the spooling options.