Managing Your Reporting Server:
Administering, Monitoring, and Debugging
Information Builders Summit 2015 User Conference
June 2015
Author: Maria Trofimova
Company: Information Builders
Presentation Title: Managing Your Reporting Server:
Administering, Monitoring, and Debugging
Presentation Abstract: This lab is intended for WebFOCUS and DataMigrator Server Administrators with various levels of experience and covers Debugging and Server Monitoring topics. We review a few techniques that can be used to fix configuration problems, research performance of server procedures and reproduce agents’ crashes. As part of the lab we configure email notifications to alert server Administrators on error conditions, monitor server activity and review server logs and traces.
What we will cover:
· Testing server components and resolution of bad Adapter configuration
· Research of performance problem.
· Debugging Using Session Log.
· Configuration Event routing and testing of Email notifications
· Data agents Statistics and Monitoring.
· Using Custom Monitor Pages
· Server Log and traces. Collection of Server Diagnostics.
Let’s get started. We will be using 82 WebFOCUS Reporting Server. At the beginning of the lab the server is up and running, it was started by service automatically. Our first step is to start server Web Console.
On the Taskbar click icon Chrome browser and use “82 Serverlab Console” quick link to start Web Console.
Exercise 1. Verification of Server Services and Listeners
Every time you install new server or make an upgrade you need to verify server status and test server components. In this step we will go over server tools helping to verify server components status. In the lab we will test Procedures, Adapters connections, Metadata, Server Listeners and Services.
The same steps would be helpful when you work on a user problem resolution. You would need to check on data and metadata files and verify procedures.
.
To check and test Server’s Listeners and Services you need to follows these steps.
1. Login with userid “srvadmin”, password “srvadmin”
2. Click on Workspace on top menu
3. Right click on Special Services and Listeners and click Processes
Verify all Listeners and Services are active
4. Right click on Java Services and click Agents.
Verify that Status is Active. This is essential for some of the Server features such as Java-based Adapters, reports containing graphs,…etc
If you would like to make sure graphs work you can test it from Java Services
5. Expand java services, you will see DEFAULT service. Right click on DEFAULT service and click Test Graph from the menu.
If you see any of your services show “failure to start” or “stopped” you should start from checking this service Statistics (use right click on the Service name to view statistics page) and then check the server log – edaprint.log for the cause of the problem.
Most common problem for a listener failing to start is the port taken by another process. Java Services fail if Java SDK is not in the path..
In the next exercise we will test procedure that happens to fail with FOC1400. To resolve the situation we will follow several steps and will see how to test metadata and adapter connections.
Exercise 2. Test Procedure - FOC1400 Error.
Resolve Invalid Adapter Configuration.
6. On Web Console Click Applications to navigate to Applications page.
7. Expand application server_lab
8. Right Click on procedure employee_report and choose option Run.
We see that it produce an error FOC1400. This error is accompanied by SQLCODE from DBMS.
We should resolve this problem. In order to do that we need to check all synonyms used by procedure and Adapter connections for all synonyms.
9. Double Click on procedure employee_report to open in editor. Check what synonyms are used. We see the synonyms used are employee_education and employees_department. We need to test all synonyms to see which one is causing DBMS error.
10. To test synonym we use Sample data option available on synonym menu.
Right click on employee_education master file and click Sample data on the menu. You see the same error.
Now we need to find out Adapter and Adapter connection for the failing synonym. This information can be found in Properties of this master file.
11. Right Click on synonym employee_education and choose option Properties
Properties show Adapter type and Connection Name. In our case it is MS SQL Adapter and Connection name ServerLab
12. Click Adapters on the top menu
13. Expand MS SQL Server folder in Configured.
14. Right Click on ServerLab and click Test. Test fails with the same Error
Now let’s go to Adapter Connection Serverlab Properties to verify all attributes
15. Right Click on ServerLab and click Properties
On Properties page you see all connection attributes, environment variables and the system path where server is searching for DBMS Client software that is required for Adapter to work. Check all parameters for Adapter connection. Contact your DBMS Administrator if needed.
.
16. We see that the database name is wrong “serverlab1”.
Should be “serverlab”. Please change it to serverlab.
17. Click “Test” and see successful connection
18. Click “Configure”
19. Now get back to Applications. Let’s retest synonym employee_education.
20. Right click on employee_education to activate menu and click option “Sample Data”
You now get the report.
21. Test report employee_report. Right Click on the report and click Run
Exercise 3. Test Procedure - performance problem.
Debug using Server Session Log.
In the next example we will investigate user’s complain about performance. User runs procedure that takes long time to finish.
There are many potential areas which can affect performance - Front-end Browser, WebFOCUS Client, Web Servers, some Network components, Reporting Server and DBMS. When researching performance problems you should remove some of the components from the picture to concentrate on those that actually cause the delays. In this example we assume that the problem was isolated and the problematic component is Reporting Server. Reporting Server by itself is a complex environment – operating system running the server, DBMS and server itself. Using Server Web Console we will go thru the steps to find the problematic component.
In this example user complains about report named revenue_report.
Step 1. Test Procedure.
The first steps are – verify the problem by running procedure from Web Console and check the status of the server agent servicing this request.
Note: Procedures may not reside on the Reporting Server and instead are within WebFOCUS Managed Reporting. In this case you can create a copy of the procedure on the Reporting Server for debugging purposes. This can be done with a right-click of the Application directory followed by New Procedure. In this lab we will assume the procedures are located on the Reporting Server already.
22. Go to the Application page which contains the Procedure
23. Expand application server_lab and Run revenue_report to confirm reported problem
24. While it’s running on the top menu click Workspace
Step 2. Check Data Service status. Status “DBMS Call”
We now need to check status of the Data Agent servicing execution of this procedure.
Data Services page shows that agent running procedure server_lab/revenue_report is in “DBMS call” state
This means that the server agent processing your request sent SQL query to the DBMS and is waiting for DBMS to process it and send back response (an answer set or an error message). Once server agent gets the control back from DBMS the “DBMS call” state changes to “in use” and finally to “idle”. We would check the time that was spent on DBMS processing.
Step 3. Compare “Response Time” and “DBMS Time” statistics.
Now we can refresh Agents page by clicking in refresh icon and wait for agent get to the idle state which means that the execution is ended and client disconnected from the server. Now we can compare agent time statistics.
Find for the agent running procedure “Response Time” and “DBMS Time” and compare. It shows that around 70% of all time was spent in DBMS. Response time and DBMS time can vary from one machine to another but the proportion should be similar
The next step would be to look at SQL statement generated by the agent and sent to DBMS.
Step 4. Use Session Log to get generated SQL statement
Server Session Log is a feature of Web Console that allows viewing all focus code sent to the server agent for execution, generated by agent SQL or MDX statements sent to DBMS and errors, warnings and messages returned by server agent during execution.
In our case we need to get SQL statement sent to MS SQL Server.
25. Open Session Log
Click “Session Log” icon on the top menu. If you position mouse over this icon it will display “Show Session Log” on the fly-over.
26. Session Log Window will open on the bottom of the Web Console page
27. Click “Refresh” icon in Session log window. You see the log of all your executions in the log window including the last one
28. Click “Expand” icon on the log window to see the full window. This is the full log of all commands running in your session including the execution of the last procedure. The log displays messages, errors and generated SQL Statements. You can scroll down to find your SQL statement or you can use filter to filter out focus code and see SQL statement only
29. Click “Filter”, move cursor down to line called “Focus” and click. This will filter out all focus commands leaving only SQL and errors
The SQL statement generated by procedure revenue_report is the join of 2 tables - sales and customers. Let’s read the messages. These are aggregation and optimization messages that returned by the SQL Server Adapter. The messages indicate that the request was not optimized, function POSIT was not passed to SQL. Let’s see if anything we can find out about this function in Adapters Optimization report that is available from Adapters page.
Step 5. Use SQL Optimization Report.
30 Click “Adapters” on the top menu and then “SQL Optimization Report”.
31 On the report we need to get information on POSIT function for SQL Server Adapter. To display the info choose Adapter Category – 1, Function Category – Legacy (scroll to the button of the list) and check “Show Function Description.
Click “Show Report”
We get the SQL Optimization report that shows that POSIT function is not optimized for MS SQL Server Adapter. It also says “Use POSITION function”. Let’s check if POSITION function would be optimized.
Notice, that some of legacy old functions are not optimized. Function description says which ones to use to get them optimized. In Server release 81 we created a set of simplified functions that are matching to those in SQL and so they should be used in your procedures for maximum optimization.
32 Please close report
33 Choose Function Category “Character – Simplified” to get report on POSITION function. Click “Show Report”. We see that Function POSITION is optimized.
We will use now POSITION in the procedure instead of POSIT and see how it changes performance.
I have already made changes to our revenue_report procedure substituting POSIT() to POSITION() and saved it as revenue_report_opt that we see in server_lab application.
Step 6. Run procedure after using POSITION() function that is optimized in SQL.
34 Click on Applications on the top menu.
35 Expand server_lab application.
36 Right click on procedure revenue_report_opt and select Run. This procedure is the same as
revenue _report but utilizing POSITION function instead of POSIT.
37 See report was returned in 2-3 secs. Check Time statistics on Data Agents page.
38 Click Workspace. View Response Time and DBMS Time.
39 View the SQL Statement generated by changed request on Session Log.
We see that function POSITION has been converted into WHERE clause as SQL function CHARINDEX(). Function POSITION has been optimized by the Adapter and it improved performance significantly from 17 secs to 3 secs. All time saving is from Adapter optimization.
If more improvement required, the next step in this process is to copy SQL statement from Session Log and execute it in the Native Database tool. It is important to use the exact SQL as executed by the Reporting Server for an accurate comparison.
When procedure is optimized on the server and nothing can be done, you can try to work with your DBA to tune the Database, possibly adding keys and indexes to tables involved into the requests.
Step 7. Size of the Session Log.
Default Log size is 500 lines. If your procedure is complicated the session log might not contain SQL Statement you are looking for. In this case you can increase the log size.
25 Click Workspace on the top menu
26 Click Settings on the Ribbon and navigate to “Miscellaneous Settings. (You can close Session Log windows using “close” icon. You will not need it anymore).
27 On the right pane you can see the server parameter that regulates the session log size – seslog_max_lines. You can change it to bigger number and click “Save”. Server will be restarted and new number will be in effect. We will not do this during this lab.