Transcription

2 "What's happening in the engine Monitoring scenarios Presented to better understand requirements Tools available in v8.2 and v9.1 System monitor db2pd db2pd: tool to monitor and troubleshoot DB2 Standalone utility shipped with the DB2 engine starting with the DB2 v8.2 Used by customers to monitor and troubleshoot Gives the user a closer view into the DB2 engine Advantages of using db2pd Tool collects information without acquiring any latches or using any engine resources which has two major benefits Faster retrieval No competition for engine resources 2

6 SQL1226N due to too many db2agent processes If many db2agent processes remain attached to an instance They may use up all of the agents (MAXAGENTS) New connections will trigger SQL1226N The maximum number of client connections are already started ADM7009E error message will be logged in a notify log Use db2pd agents to find ApplHandl, ClientPid, UserId and ClientNm db2pd -agents awk '/Address ^0x/ { print $2, $8, $9, $10}' AppHandl ClientPid Userid ClientNm dabrashk db2bp dabrashk db2bp Force the identified AppHandl off with command db2 "force application (642)" 6

8 Monitoring progress and behavior of applications db2pd applications db sample Use to map application to a coordinator agent Use to determine a status of application Use to map application to the dynamic SQL statement db2pd -activestatements Any statement that is part of the active statement list is reported Gives the user the ability to identify all active dynamic statements for all applications Use to map application ID to IP address and port Simplified in v9.1: no need to convert 8

15 Useful DB2 tools for hangs The db2pd tool Purpose: To gather information quickly and non-intrusively from the DB2 engine The db2cos tool Purpose: To be called inline from DB2 code to collect information about problems Latch tracking Purpose: To track latch ownership Snapshot monitoring Purpose: To understand point in time status of queries or entities in the DB2 engine DB2 trace To investigate possible movement in DB2 agents 15

19 Detecting hangs for specific applications Take a few application snapshots a minute apart to determine what the status of the application is (db2pd app: Status) and whether any work is being done (db2pd agent: RowsRead/Wrtn) It is useful to have turned on all of the monitor switches prior to a re-creatable or recurring problem scenario Use db2pd -app to determine status of an application db2pd -app -db sample awk '/Address ^0x/ { print $6 }' If status is UOW Waiting, the hang is not occurring at the DB2 server The client application should be investigated to find out what it is waiting for. In DPF environment, this may indicate a problem with another partition If status is Executing and counters like rows-read/written are increasing, it is likely a performance issue If status is Lock-wait than it is a locking/concurrency issue Exception is the case when the application being waited on is in UOW Executing and making no progress If status is Executing yet no counters are increasing, then the agent or agents servicing the application may be in an abnormal state More diagnostics is needed 19

25 db2pd -pages (v9.1) db2pd -db sample -pages Pages for all bufferpools db2pd -db sample -pages [<bpid>] Monitor bufferpool behavior Tells which pages are in the bufferpool Use to determine what is in the bufferpool that is the cause for the hit ratio to be lower than you expect Allows user to check how many pages each object (table, index, etc) has within any particular bufferpool Similar to IDS's onstat -b option might help to detect a problem, such as an insufficient number of buffers in the buffer pool or high read aheads 25

27 Monitoring for SQL errors: db2pdcfg -catch Functionality moved from db2pd to db2pdcfg in v9.1 Purpose allow the user to catch any sqlcode (and reason code), zrc or ecf codes (internal error codes) capture the information needed to solve the error code Primary action: execute the db2cos (callout script) template db2cos file is located in sqllib/bin (v9.1) db2cos may be altered to run any command (db2pd,os or other) needed to solve the problem: default is db2pd -db $database in "SQLCODE section) Defaults Up to 10 catch points simultaneously Error catch array is full. Use 'clear' suboption to clear an element" Up to 255 invocations Max Count:

32 Monitoring log usage (FP9 enhancements) db2pd -logs has some new information since v8.2.2: Logs: Current Log Number 5 Pages Written 846 Method 1 Archive Status Success Method 1 Next Log to Archive 5 Method 1 First Failure n/a Method 2 Archive Status Success Method 2 Next Log to Archive 5 Method 2 First Failure n/a Address StartLSN State Size Pages Filename 0x BF58 0x000001B x S LOG 0x BE98 0x000001F x S LOG 0x F58 0x x S LOG Two problems can be identified with this output Problem with archiving if Archive Status is set to Failure, the most recent log archive failed If First Failure is set, ongoing archive failure is preventing logs from archiving Log archiving is proceeding very slowly Next Log to Archive will be behind Current Log Number (this can cause the log path to fill up completely) Monitor Next Log to Archive compared to Current Log Number If next log is 3 and current is 5, then logs 3 and 4 haven t been logged yet Log 5 is the current log being written into 33

DB2 for Linux, UNIX, and Windows Performance Tuning and Monitoring Workshop Duration: 4 Days What you will learn Learn how to tune for optimum performance the IBM DB2 9 for Linux, UNIX, and Windows relational

Proactive Technologies Inc. presents Version 4.0 The Ultimate Remote Database Administration Tool for Oracle, SQL Server and DB2 UDB The negative impact that downtime can have on a company has never been

Module 15: Monitoring Overview Formulate requirements and identify resources to monitor in a database environment Types of monitoring that can be carried out to ensure: Maximum availability Optimal performance

Getting Started with IntelleView POS Administrator Software Administrator s Guide for Software Version 1.2 About this Guide This administrator s guide explains how to start using your IntelleView POS (IntelleView)

HP OO Community Guides Monitoring HP OO 10 This document describes the specifications of components we want to monitor, and the means to monitor them, in order to achieve effective monitoring of HP Operations

WhatsUp Gold v11 Features Overview This guide provides an overview of the core functionality of WhatsUp Gold v11, and introduces interesting features and processes that help users maximize productivity

Database Administration with MySQL Suitable For: Database administrators and system administrators who need to manage MySQL based services. Prerequisites: Practical knowledge of SQL Some knowledge of relational

Welcome to the presentation. Thank you for taking your time for being here. Few success stories that are shared in this presentation could be familiar to some of you. I would still hope that most of you

Is your database application experiencing poor response time, scalability problems, and too many deadlocks or poor application performance? One or a combination of zparms, database design and application

Backup and Recovery Table of Contents If you're viewing this document online, you can click any of the topics below to link directly to that section. 1. Introduction... 2 2. Database recovery concepts...

A White Paper Metron Technology Ltd Metron-Athene Inc 2003 This paper is based on the Author s experiences in performance analysis and tuning for DB2 UDB on distributed platforms. The paper is intended

Oracle University Contact Us: 1.800.529.0165 Oracle Database 12c: Performance Management and Tuning NEW Duration: 5 Days What you will learn In the Oracle Database 12c: Performance Management and Tuning

EZManage V4.0 Release Notes Document revision 1.08 (15.12.2013) Release Features Feature #1- New UI New User Interface for every form including the ribbon controls that are similar to the Microsoft office

Oracle Database Concepts Database Structure The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed

Microsoft SQL Server OLTP Best Practice The document Introduction to Transactional (OLTP) Load Testing for all Databases provides a general overview on the HammerDB OLTP workload and the document Microsoft

Oracle Database 10g: New Features for Administrators Course ON10G 5 Day(s) 30:00 Hours Introduction This course introduces students to the new features in Oracle Database 10g Release 2 - the database for

DB2 running on Linux, Unix, and Windows (LUW) continues to grow at a rapid pace. This rapid growth has resulted in a shortage of experienced non-mainframe DB2 DBAs. IT departments today have to deal with

Course Length: 2 days CEUs 1.2 AUDIENCE After completion of this course, you should be able to: Administer the IBM PDA/Netezza Install Netezza Client Software Use the Netezza System Interfaces Understand

Using SNMP to Obtain Port Counter Statistics During Live Migration of a Virtual Machine Ronny L. Bull Project Writeup For: CS644 Clarkson University Fall 2012 Abstract If a managed switch is used during

Database Administration Guide SAP on IBM DB2 for Linux, UNIX, and Windows Valid for the Following DB2 and SAP Releases: Version 10.1, 9.7, 9.5, and 9.1 of the IBM DB2 database SAP NetWeaver 7.0 and higher

12. User-managed and RMAN-based backups. Abstract: A physical backup is a copy of the physical database files, and it can be performed in two ways. The first is through the Recovery Manager (RMAN) tool

SAP HANA - Main Memory Technology: A Challenge for Development of Business Applications Jürgen Primsch, SAP AG July 2011 Why In-Memory? Information at the Speed of Thought Imagine access to business data,

1 2 3 4 Database Studio is the new tool to administrate SAP MaxDB database instances as of version 7.5. It replaces the previous tools Database Manager GUI and SQL Studio from SAP MaxDB version 7.7 onwards

Migrating to vcloud Automation Center 6.1 vcloud Automation Center 6.1 This document supports the version of each product listed and supports all subsequent versions until the document is replaced by a

: Technologies for Promoting Use of Open Source Software that Contribute to Reducing TCO of IT Platform HeapStats: Your Dependable Helper for Java Applications, from Development to Operation Shinji Takao,

Configuring Backup Settings Objectives After completing this lesson, you should be able to: Use Enterprise Manager to configure backup settings Enable control file autobackup Configure backup destinations

MANJRASOFT PTY LTD Aneka 3.0 Manjrasoft 5/13/2013 This document describes in detail the steps involved in installing and configuring an Aneka Cloud. It covers the prerequisites for the installation, the