This is a complimentary tip available only to SPSS Direct subscribers.
QuestionWhen I execute a stream in Clementine 10.1, the stream turns purple, indicating that SQL processing is being pushed back to the database. How can I view the SQL that is generated by my Clementine stream on execution?
AnswerYou can view the SQL generated by Clementine in a file. Or, if you are using the Clementine Client, you can view it from within the application itself.
To view the SQL in a file, you must add a parameter to the options.cfg file installed with Clementine. If you are using Clementine Server, you will need to edit the options.cfg file on the server. The file is located in the Clementine Server\config directory. To edit the file, insert the following line:
trace_sql, Y
Then, restart Clementine Server so that this change takes effect. The next time the stream is executed a log file will be created in the Clementine Server\log directory, and it will contain the SQL that was generated by the stream execution.
If you are running Clementine in local mode, you can also update the options.cfg file as previously described. However, the client installation does not include a log directory by default, so you will need to create a subdirectory in Windows® called “log” within your Clementine installation directory. If you used the default installation path for Clementine 10.1, then your new log directory will be C:\Program Files\SPSS Clementine\10.1\log\. Restart Clementine Client so that it can register this change.
The next time the stream is executed, a log file will be created in the new Clementine client\log directory. The log file will contain the SQL that was generated by the stream execution.
To view the SQL from within Clementine Client, you can adjust a parameter by going to Tools > User Options. Choose the Optimization tab and select "Display SQL in the messages log during stream execution" and "Reformat SQL for improved readability” as shown below:
Figure 1: Enable SQL logging options within the User Options dialog box of Clementine Client
(Click to enlarge)
Selecting “Display SQL in the messages log during stream execution” will cause the SQL generated while executing the stream to be passed to the message log. If you choose to select “Display SQL generation details in the messages log during stream preparation” as well, then during stream preparation a preview of the SQL that would be executed is passed to the messages log. When you select “Reformat SQL for improved readability,” Clementine reformats the generated SQL to improve its readability when displayed in the log. If you are going to capture the SQL from the log and are not concerned about reading it in the log window, you may want to leave this option unchecked.
When these options are selected, all of the SQL pushed back during the execution of a stream will be displayed in the Messages tab. You can view this by clicking on the Show Stream Messages tab at the bottom of the Clementine Client interface:
Figure 2: Generate viewable SQL in the Messages tab of Show Stream Messages
(Click to enlarge)
The Optimization tab in the User Options dialog box allows you to optimize Clementine performance and logging during stream execution. Please note: if you connect to a Clementine Server, its performance and optimization settings will override any settings in the client. For more information on user and system options that can be set in Clementine, please refer to Chapter 3 of the Clementine 10.1 User’s Guide.
Predictive Analytics
can make your organization
more
successful