CSI Database: The Forensics of DB2 Network Traffic

3 Pages
  • There are thousands of active connections with DB2 B. Is there some kind of limit?
  • There are no connections with DB2 B. Is having zero connections occasionally normal for DB2 B? Historical performance data will tell you. Or, are there zero connections because DB2 B is troubled?
  • Is there connectivity between the DB2 B LPAR and client A? Is client A accessible? Does it have any other connections with that LPAR? Does it have any connections at all?

TCP/IP connection lists provide this kind of information about each connection and partner. Sorting connection lists is a fast way to spot the top talkers, longest running connections, longest idle connections, and connections stuck in unhealthy TCP/IP states. All these are possibly useful clues.

A Lab Tour

Forensic analysis uses tools such as microscopes and mass spectrometers to examine the most promising evidence. For DB2 TCP/IP connection problems, key examination tools are IP packet stream viewing and IP packet inspection. After comparing connections on a list, you can hone in on a single suspicious connection and use packet stream viewing to show what’s logically happening over the connection.

Packet stream viewers use data from both the IP packet headers and packet content to provide chronological and annotated lists of all IP packets flowing over a connection—preferably in real-time (see Figure 2). Most DB2 DDF packet streams you see include a mix of these kinds of packet flows:

  • TCP/IP protocol activity
  • DDF partner setup
  • Distributed Relational Database Architecture (DRDA) protocol activity
  • Application SQL.


TCP/IP protocol activity: These packet flows do the mechanics of setting up and taking down TCP/IP connections. While irrelevant to DB2 itself, these activities are highly relevant to DB2 user problems:

  • A user or application can’t connect to DDF. Is this a network or an application problem? If you see the TCP three-way handshake in the packet stream, then the underlying TCP connection was successfully set up and it’s an application problem. But if the handshake failed to complete, it’s a network problem.
  • A successfully established connection is stopping unexpectedly. The packet stream shows whether the DDF server is abnormally terminating the connection (by sending a reset flag, or RST) or the client is ending it in an orderly way (by sending a finished flag, or FIN).
  • ·

DDF partner setup: Once a TCP/IP connection is established, DB2 DDF must control the logical introductions and negotiations with its remote partner. The partner can be anything from another DB2 database, a Java Database Connectivity (JDBC) application, an ordinary workstation, a data aggregator product, or DB2 Connect in one of its many modes. Partner setup includes exchanging product names and versions, security mechanisms, and security check details. Only after the communication conditions are agreed on by both ends can any data start to flow. Proposal and acknowledgement packets—or lack of them—can be seen in the packet stream.

DRDA protocol activity: DB2 DDF communicates over TCP/IP using an open application protocol called DRDA. The DDF address space listens for DRDA commands from requesters, invokes DB2 on their behalf, generates a DRDA reply, and sends this reply to the requester. DRDA is a highly structured protocol built on Distributed Data Management (DDM) commands that provide its command and reply structure. Some DDM commands map directly to SQL commands; others do specific distributed database tasks. The sequence of all DDM commands is clearly visible from a packet stream.

Application SQL: The motive of any DB2 TCP/IP connection is to eventually access a database. Packet stream viewing reveals which packets contain the payload of application SQL queries and their responses. With multi-tier applications, sometimes the exact SQL codes and states are accidentally or deliberately not externalized to users, hindering problem determination. These are always visible in a packet stream. DB2 authorization problems are one example that can cause SQL error responses and apparent failures at the user interface end, even though the underlying TCP/IP connection is fine.

Packet stream viewing also shows you the context of individual SQL request and response packets and their related packets. For example, packet stream viewing lets you pair commit request packets with their matching end unit of work packets; these are important in distributed database processing, where a logical unit of work can span multiple SQL requests and multiple databases.

3 Pages