1 / 37

Agenda

ITM 6.1 Warehouse Deep Dive: Troubleshooting and Best Practices Toben Nelson - Caterina Perri (Tivoli L2 Customer Support). Agenda. Warehousing Solution Overview Managing Historical Data Warehouse Proxy Agent (WPA) Summarization & Pruning Agent (S&PA) Troubleshooting Q&A. Agenda.

louis
Download Presentation

Agenda

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ITM 6.1 Warehouse Deep Dive: Troubleshooting and Best PracticesToben Nelson - Caterina Perri (Tivoli L2 Customer Support)

  2. Agenda • Warehousing Solution Overview • Managing Historical Data • Warehouse Proxy Agent (WPA) • Summarization & Pruning Agent (S&PA) • Troubleshooting • Q&A

  3. Agenda • Warehousing Solution Overview • Managing Historical Data • Warehouse Proxy Agent (WPA) • Summarization & Pruning Agent (S&PA) • Q&A

  4. ITM 6.1 Warehouse Architecture • Main components: • Monitoring Agent: collect data • WPA to load into DB • S&PA to summarize data & prune obsolete data 2 3 1

  5. Warehouse RDB Schema • An attribute is a characteristic of a managed object (for example Available_Bytes, Cache_Bytes, are attributes for memory) . • Each attribute belongs to an attribute group which includes attributes that are related (NT_Memory). • Each attribute item stores data for a particular property of an attribute group. • One different table for each attribute group One different table for each type of aggregation defined in the Summarization and Pruning Agent

  6. Agenda • Warehousing Solution Overview • Managing Historical Data • Warehouse Proxy Agent (WPA) • Summarization & Pruning Agent (S&PA) • Troubleshooting • Q&A

  7. History Data Collection Configuration Choose the collection location for short term binary files (TEMS or TEMA) Select the attribute group Choose the collection interval (5,10,15,30 or 60 minutes) WH interval: 1 hour, 1 day, OFF (i.e. WPA won’t populate WH DB) Define the summarization rules Define the pruning rules

  8. Seeing historical data When an attribute group has been configured for historical collection, the corresponding views within the workspaces associated with that attribute group show a calendar-clock icon. Clicking on this icon allows you to select historical data for viewing, both raw and summarized.

  9. Seeing historical data • Selectingless than 24 hours will direct the query to the historical binary file (short term). • Selecting more than 24 hours will direct the query to the warehouse database (long term).

  10. Useful parameters • To control the amount of short term raw historical data and consequently the binary file sizes, set the following params: • on TEMS / TEMA: KHD_HISTRETENTION=<# hours> • on TEPS: KFW_REPORT_TERM_BREAK_POINT=<# secs> • If KFW_REPORT_TERM_BREAK_POINT is not added to KFWENV and the KHD_HISTRETENTION variable has been used to customize the number of hours of locally retained historical data, the following error could be seen in the TEPS RAS1 logs: "CTSQLEvaluatorODBC_i::Connection::sqlErrorCheck") SQLAllocHandle STMT rc=-1: SQL_ERRORSQLAllocHandle STMT rc=-1: SQL_ERROR • See the following solution for more detail: http://www-1.ibm.com/support/docview.wss?uid=swg21263578

  11. Agenda • Warehousing Solution Overview • Managing Historical Data • Warehouse Proxy Agent (WPA) • Summarization & Pruning Agent (S&PA) • Troubleshooting • Q&A

  12. Warehouse Proxy Agent • The warehouse proxy agent (WPA) is a multi-threaded server process handling concurrent requests from multiple agents. We can have one or more worker threads per binary file depending on the number of rows to export per binary file. By default the maximum number of worker threads is set to 10: KHD_EXPORT_THREADS =10 • If the WPA is unreachable, the agent retries the transmission at the next data collection interval. If something fails in the middle of the transaction, the transaction is stopped and restarted. • Useful params:KHD_QUEUE_LENGTH: The maximum size of the export thread queueKHD_CNX_POOL_SIZE: The number of connections in the pool for ODBC connection

  13. 1. Create the database and the user Installation 2. Configure the ODBC/JDBC 3. Install & Configure the WPA • The Warehouse database needs to be created with UTF-8 encoding (default database name is WAREHOUS) • Setup the listener for DB2: • Create the user to access the TDW database (default userid/pw are itmuser/itmpswd1)This user is an Operating System user with adequate privileges: the WPA looks for a buffer pool of page size 8k and for 3 Table spaces, if not there the agent tries to create them (as “itmuser”). Two choices: • make the OS user member of the Administrators (DB2) group on Windows (Unix) • create BP and TBSP upfront so that the WPA finds them. In this option you can restrict the privileges of the “itmuser’ to CREATETAB and CONNECT db2 create database WAREHOUS using codeset utf-8 territory US db2set -i db2inst1 DB2COMM=tcpipdb2 update dbm cfg using SVCENAME 50000db2stop forcedb2start

  14. 1. Create the database and the user 2. Configure the ODBC/JDBC 3. Install & Configure the WPA Installation ODBC configuration (Windows) Control Panel  Administrative Tools  Data Sources (ODBC) Link the DSNto the DB previously created!!

  15. 1. Create the database and the user 2. Configure the ODBC/JDBC 3. Install & Configure the WPA Installation JDBC configuration (Linux / AIX) • Copy the JDBC driver JAR files that come with your DB product to the local system where you installed WPA. During WPA configuration you will be prompted for their location • DB2: <db2installdir>/java/db2jcc.jar <db2installdir>/java/db2jcc_license_cu.jar where <db2installdir> is the directory where DB2 was installed • Oracle: <oracleinstalldir>/jdbc/lib/ojdbc14.jar • MSSQL: <mssql2005installdir>/sqljdbc_1.0/enu/sqljdbc.jar

  16. 1. Create the database and the user 2. Configure the ODBC/JDBC 3. Install & Configure the WPA Configuration (Windows) If you have created an ODBC data source with a different name, you should update this field. if you have not created the ODBC DataSource and the DB, a database called “WAREHOUS” will be created for you by the DBADMIN, as well as the Windows user and the ODBC DS. The WPA connection information will be updated for the TEPS if TEPS+WPA on the same windows box.

  17. 1. Create the database and the user 2. Configure the ODBC/JDBC 3. Install & Configure the WPA Configuration (Windows) Like all the other ITM 6.1 agents.... remember to specify the HUB TEMS!!

  18. Design and Data Flow (high level) WPA registration on the TEMS: • When it starts, the WPA registers its socket info, <ip>:<port>, with the Global Location Broker on the HUB TEMS. • Collection is the TEMS: the TEMS will check the GLB for WPA location when export starts. • Collection is the TEMA: the agent’s TEMS will check the GLB every 60 minutes for the WPA location. The agent then looks up the WPA socket info in its TEMS’s Local Location Broker to know where to export its data. Historical Situations: • Every collection time (5,15,30 or 60 minutes), new data are collecting and stored in the binary file on the agent or the TEMS. Export service (where collection has been configured i.e. TEMS or TEMA): • Every warehousing time (1 hour or 1 day) the export service use worker threads to first find the WPA address and then export the rows from each binary file.

  19. Environment variables to speed up export • KHD_EXPORT_DEBUG = Y • Set this variable for any agent for which you want the export to occur at the collection interval ( such a severy 5 minutes) instead of at the warehouse load interval (such as every hour) • If you collect the data at the TEMS, set the variable at the TEMS • KPX_WAREHOUSE_REGCHK = 5 • To allow the TEMS to check every 5 minutes the network address of the Warehouse Proxy instead of waiting at least 1 hour.

  20. Multiple Warehouse Proxy Agent Configuration • All WPAs register with the HUBTEMS’ global location broker and must export data to the same warehouse RDBMS. • Each WPA can be associated with a subset of the enterprise’s R-TEMS by adding the KHD_WAREHOUSE_TEMS_LIST agent environment variable. • The HUB has the list of associations between each R-TEMS and each WPA. Every R-TEMS queries the global location broker on the hub to determine which WPA it is associated with. • When using ip.pipe or ip.spipe as the primary protocol an optimal configuration associates no more than 2000 clients • For very complex configurations, set KPX_WAREHOUSE_LOCATION to one or more specific WPAs so that a fixed route(s) can be used by all agents; this replaces the GLB paradigm:KPX_WAREHOUSE_LOCATION=ip.pipe:#192.168.0.14[18303];ip:#192.168.0.14[34543];

  21. Best Practices • Only collect history data the user is interested in and avoid enabling ALL historical collection for a product unless it is really needed. • Carefully consider where history data is to be collected (TEMS or agent). • History Collection at the TEMA (normally recommended): • Reduces agent to TEMS network traffic since history data will not be sent to TEMS. • Decreases TEMS workload, especially when doing warehousing. • Reduces history data file size since data for only that agent or node exists in the file. • History Collection at the TEMS • Single point of file management when roll off scripts or programs used instead of warehousing. • Needed when sites require restricted resource usage on the agent machines. • Sites using history warehousing with agents outside of firewalls don’t require an additional network port be opened to firewall traffic for the Warehouse Proxy agent. • Management of History Data • The History Warehousing process will ensure that history data files retain only the last 24 hours worth of history data. • Ensure the Warehouse Proxy agent is running when warehousing is enabled so the data file management can occur. • If history warehousing fails, so will the data file management process. • When warehousing is not enabled, you must schedule and use the roll off programs (like krarloff) to manage the history data files.

  22. Warehousing Across a Firewall - 1 • To guarantee that the WPA always obtains the same listening port that was opened by the firewall/network administrator, use the KDC_FAMILIES variable’s COUNT and SKIP options with all ITM agents running on same machine as the WPA. Two general methods to using COUNT and SKIP: METHOD 1 WPA uses COUNT:1 with no SKIP param to obtain 1st listening port All other non-TEMS agents running on the WPA machine (including TEPS) use the SKIP:# option to bypass attempts at using this 1st listening port that the WPA will claim; “#” must be 2 or higher. METHOD 2  WPA uses COUNT:1 and SKIP:#, where number is high, i.e. 15  All other non-TEMS agents don’t use either COUNT or SKIP and come up on lower ports • Network administrator must open access for inbound data for the WPA port through the firewall.

  23. Warehousing Across A Firewall - 2 Multiple ITM agents running on SERVER01 machine SERVER01 TEMS port 1918 Open thru firewall HUB TEMS IP.PIPE PORT:1918 (listens on port 1918) WPA IP.PIPE COUNT:1 (listens on port 6014) FIREWALL UNIX Agent KHDXCL1 Export Service Windows Agent IP.PIPE SKIP:2 (listens on port 10110) WHProxy port 6014 Open thru firewall for export unixcpu TEPS IP.PIPE SKIP:3 (listens on port 14206) All ITM agents using KDC_FAMILIES= IP.PIPE PORT:1918

  24. Agenda • Warehousing Solution Overview • Managing Historical Data • Warehouse Proxy Agent (WPA) • Summarization & Pruning Agent (S&PA) • Troubleshooting • Q&A

  25. Summarization and Pruning Agent (SPA) • The SPA aggregates and prunes the raw data in the Data Warehouse. It is HIGHLY recommended that the SPA always reside on the same machine as the Data Warehouse. • With IF4, a flexible option was introduced to schedule the SPA to run more than once per a day—it’s highly advisable to not run it more than every 60 minutes:KSY_FIXED_SCHEDULE=Y/NKSY_EVERY_N_DAYS=1 KSY_HOUR_TO_RUN=1KSY_HOUR_AM_PM=PMKSY_MINUTE_TO_RUN=35KSY_EVERY_N_MINS=60KSY_BLACKOUT= KSY_FIXED_SCHEDULE=Y KSY_FIXED_SCHEDULE=N

  26. Installation Add the S&PA Agent Support on TEMS

  27. Post Installation Step • Don’t forget to add the SPA (sy) Application Support on the HUB TEMS! This adds the UADVISOR_KSY_ENABLE hidden situation without which the ksy610 process would never “wake up”, even though it may be running. Note: SY is the two letters product code for S&P agent

  28. Configuration Configure the DataSource for the Warehouse Database DB2 URL: jdbc:db2:<database> Driver: COM.ibm.db2.jdbc.app.DB2Driver MS SQL ServerURL: jdbc:microsoft:sqlserver://<server>:<port>;DatabaseName=<database> Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver Default port: 1433 Oracle URL: jdbc:oracle:thin:@<server>:<port>:<database> Driver: oracle.jdbc.driver.OracleDriver Default port: 1521Running

  29. Historical Collection Configuration WPA SPA

  30. Useful Custom SQL for TDW Self Monitoring • Recent INSERTs culled from warehouselog:select ORIGINNODE, OBJECT, ROWSINSERTED, DateForm(EXPORTTIME,'H') exporttime, ERRORMSG from WAREHOUSELOG order by EXPORTTIME desc fetch first 100 rows only; • Which tables (a.k.a. attribute groups) are currently receiving data:select dateform(max(exporttime),'H') lastdate, object from warehouselog group by object order by lastdate asc; • In last 24 hours, the number of rows received and inserted by table:select object, sum(rowsinserted) inserted, sum(rowsreceived) received from warehouselog where date(substr(endtime,4,2) || '/' || substr(endtime,6,2) || '/20' || substr(endtime,2,2)) >= CURRENT DATE group by object order by inserted desc fetch first 20 rows only; • Latest export time by node:select dateform(max(exporttime),'H') as lastdate,originnode from warehouselog group by originnode; • Export errors by table and node:select object,originnode,DateForm(exporttime,'H') exporttime,errormsg from warehouselog where errormsg <> '' order by exporttime desc;

  31. Agenda • Warehousing Solution Overview • Managing Historical Data • Warehouse Proxy Agent (WPA) • Summarization & Pruning Agent (S&PA) • Troubleshooting • Q&A

  32. WPA - Log Files • WPA Logs • <hostname>_hd_nnnnnnnnnn.log (WPA) • <hostname>_hd_java_nnnnnnn-xx.log (AIX or Linux) • <hostname>_Warehouse.LG0 (*) (Operations log) (*) After an install of ITM 6.1 Fixpack 5 IF0003, you will notice that the WPA does not seem to update the Warehouse.LG0. This was changed to prevent the file size being increased indefinetly. To get the equivalent information formerly found in the Agent Operations file showing up most recent inserts sorted to be at the top of the list you can run the following query against the WH DB: select ORIGINNODE,OBJECT, EXPORTTIME, ROWSINSERTED, ERRORMSG, WPSYSNAME from WAREHOUSELOG order by EXPORTTIME desc • Related components logs: TEMS or Agents (for the export process) • <hostname>_ms_nnnnnnnn.log  TEMS log • <hostname>_<pc>_nnnnnnnn.log  Agent log

  33. WPA Trace Levels KBB_RAS1= ERROR (UNIT:khdx ST,ER) • Trace warehouse export issue from TEMS and Agent Display process of reading the history files and transmitting data to the WPA • Trace Warehouse Proxy Agent issues Display status and processing of all history data being inserted into History tables For Unix/Linux WPA also: KBB_RAS1= (UNIT: khdjava1 ALL) • Binary Files issue RAS1 Trace  All short-term history reports are directed to the ITM history files ( or binary file) at either the TEMS or the Agent, depending on how the history situation has been configured. • For the TEMS: • “ERROR (UNIT:kpxhsloc ALL) (UNIT:krabhsco ALL)” • For the Agents: • “ERROR (UNIT:kra ALL)”.

  34. SPA Logs • Files: • <hostname>_sy_nnnnnnnnnn.log (C-based IRA agent) • <hostname>_sy_java_nnnnnnnnnn.log (Detailed log for Java Component) • Highly recommended to set KBB_RAS1=ERROR (UNIT:ksy1 ALL)

  35. Useful Additional Docs & Tools * TDW storage requirement Use the ITM 6.1 /TDW 2.1 Warehouse Load Projections spreadsheet on OPAL website for estimating database requirements http://catalog.lotus.com/wps/portal/tm/ • 6.1 Warehouse Redbook—extremely useful for performance and tuning:http://www.redbooks.ibm.com/abstracts/sg247290.html?Open

  36. Questions?

  37. Thank YOU !!

More Related