IBM Support

Collecting data for performance issues for InfoSphere Federation Server

Question & Answer


Question

What information should be collected when I experience a performance problem with my software? Collecting data for performance issues. Collecting this information before calling IBM support will help you understand the problem and save time analyzing the data.

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:
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>

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/

[{"Product":{"code":"SS2K5T","label":"InfoSphere Federation Server"},"Business Unit":{"code":"BU001","label":"Analytics Private Cloud"},"Component":"Performance","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.5;9.7;10.1;10.5","Edition":"All Editions"}]

Product Alias/Synonym

WebSphere Information Integrator;WebSphere Federation Server;Information Integrator;InfoSphere Federated Server;Federation Server;Federated Server

Document Information

Modified date:
16 June 2018

UID

swg21321045