Collecting data for performance issues for InfoSphere Federation Server

Answer

If you have already contacted support, continue on to begin collecting data. The following information is required by the InfoSphere Federation Server service team to troubleshoot a product nickname statistic collection issue.

Collecting Performance specific information

1. Is this a production, development, or test environment?

2. What is the business impact of this problem?
Examples:
a. Cannot roll out a new application
b. Cannot monitor in-store security systems
c. Losing money due to applications not talking to each other

3. Have there been any recent changes to your environment (hardware, operating system, software version, db configuration, dbm configuration)?

4. Has this problem occurred before? If the problem has occurred before, do you have a previous PMR number?

5. How often does the problem occur?

6. What is the data source used? Provide data source client and server exact release and version.

DB2 for LUW
Enter the following from an operating system command prompt:
db2level

Informix
Enter following from an operating system command prompt:
cd $INFORMIXDIR/bin
./ifx_getversion clientsdk
./ifx_getversion libasf.a
./ifx_getversion libasf.so

Oracle
Enter the following from an operating system command prompt:
cd $ORACLE_HOME/bin
./sqlplus

Microsoft SQL Server
Issue the following SQL statement (from the Microsoft Query Analyzer):
select @@version

IBM Branded DataDirect ODBC driver
Go to directory <IBM_branded_odbc_install_dir>/bin and execute below command:
ddtestlib <libname>

For example:
i) Get ODBC driver manager version:
ddtestlib <IBM_branded_odbc_install_dir>/lib/odbc.so
ii) Get Microsoft SQL Server ODBC driver version:
ddtestlib <IBM_branded_odbc_install_dir>/lib/VMmsss23.so

Sybase
sybase client version:
Go to directory $SYBASE/$SYBASE_OCS/bin and execute below command:
isql -v

Sybase server version:
Issue the following SQL statement after connecting database using isql command:
select @@version
go

Teradata
Start DWB
issue get version command

7. Can the problem be reproduced? If so, provide exact details of problem; exact error number and text; steps performed when problem occurred; jobs, scripts, applications executing when problem occurred.

8. When did the problematic SQL run without performance problem?
How long did it take to run when it ran fine?
How long does it take to run now?
What's your expectation?

9. Provide EXPLAIN (access plan) output for SQL you ran as outlined below:

db2 connect to db_name (where db_name is the name of the database)
db2 set current explain snapshot explain
db2 set current explain mode explain
db2 "<put problematic SQL statement here>"
db2 set current explain mode no
db2 set current explain snapshot no
db2exfmt -d <dbname> -g TIC -w -1 -s % -n % -o db2exfmt.out

Tips:

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.kc.doc/welcome.html
a. You can enter "db2exfmt" in the search window to get syntax and specific details. b. Also, for information about the explain tables needed enter "explain tables" in the search. The DDL to create the explain tables is located in <install dir>sqllib/misc. You can create the explain tables using the following command:
db2 -tf EXPLAIN.DDL

10. Search "RMTQTXT" in above db2exfmt.out to find out the remote statements that Federation Server sends to datasource server and do the following experiments:

How long does each remote statement run on datasource Server directly?
How many rows are returned for each remote statement?
How long does each remote statement run on Federation Server with datasource client or passthru mode?

The above questions will help to determine where the performance bottleneck is: Federation Server, network or datasource server.

11. Reproduce problem with db2trace as outlined below:

db2trc on -f trace.trc -t # turn on tracing and trace
db2 "explain plan for <problematic SQL >"
db2trc off # turn off tracing
db2trc flw trace.trc trace.flw # format the trace flow into trace.flw
db2trc fmt trace.trc trace.fmt # format the trace data into trace.fmt

12. Collect db2pd output while problem occurs:

db2pd -alldbs -applications -dynamic

13. Please run following command to collect db2support data on the federated server.

Issue the command without options to display command syntax.

db2support . -d <dbname> -cl 0

Note: If you have or know sql causing the problem execute db2support with one of following options:
db2support <output_path> -d <db name> -st <sql_statement>
db2support <output_path> -d <db name> -sf <sql_file>

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0004503.html

14. Collect server options that have been set:

db2 "select * from syscat.servers a, syscat.serveroptions b
where a.servername = b.servername and
a.servername like '%<SERVER_NAME>%'" 

Note: <SERVER_NAME> is the name for the server of nicknames referenced in the query, it should be capital in the query.


15. Try setting db2_maximal_pushdown for the specific query with the following commands:

a. Temporarily set the option to 'Y':
db2 "set server option db2_maximal_pushdown to 'Y' for server <server_name>"

<server_name> is the server used in the query.
If multi servers are referenced, please set the server option for all of them.
Note: You can change the option permanently with the following command:

db2 "alter server <server_name> options (add db2_maximal_pushdown 'Y')"


b. Perform the query, and check if the performance has been improved.
Get EXPLAIN output as described in Step 9

c. Set db2_maximal_pushdown back to 'N'
db2 "set server option db2_maximal_pushdown to 'N' for server <server_name>"

16. Zip, or compress, all files and rename with PMR number as prefix before submitting ; example 12345.678.999.files.zip.

Submitting information to IBM Support

Once you have collected your information, you can begin Problem Determination through the product Support web page, or simply submit the diagnostic information to IBM support. Use the document below for submitting information to IBM Support.

Submitting diagnostic information to IBM Technical Support for problem determination

For a listing of all technotes, downloads, and educational materials specific to the Infosphere Federation Server component, search the product support site at the following link : http://www-01.ibm.com/software/data/integration/support/federation_server/