From the timestamps in the IP packet headers, TCP response time metrics can be calculated. A key metric for DB2 is TCP server response time—the time difference between when the packet containing the SQL request was received and when the first packet containing the response was sent. This is how long DB2 took to respond to the request. Comparing this with other metrics such as network Round Trip Time (RTT) and data transfer time indicates whether the mainframe or the network is contributing more to poor response time.
Packet stream analysis can become quite complex. If the problem is that you don’t have a TCP/IP connection, you have to trace something common, then search for your connection attempt among all the other packets. One TCP/IP connection can be supporting simultaneous work for many database users, with functions such as thread pooling, connection concentration, and different Database Access Thread (DBAT) processing modes.
Under the Microscope
In contrast to packet stream viewing, IP packet inspection concentrates on the data content of one individual packet. Packet inspection products will decode the packet data content, which means translating it from hex to something easily readable by a DB2 person. Packet inspection reveals the full text of SQL queries and their matching result set values and SQL communication area.
Other details available from packet inspection include:
- Client accounting fields such as end-user workstation and user ID name, end-user ID password flags (and password values if not encrypted)
- DB2 plan and package names
- Product release details of remote DB2 subsystems.
The Logical Unit-of-Work Identifier (LUWID) is also available; it’s used to correlate IP partner details with DB2 threads, which Workload Manager (WLM) uses to assign priorities to application work arriving from DDF.
Obscuring the Evidence
Forensics can only do its best with what it’s given. Often, this isn’t perfect; fingerprints can be wiped off, bleach can be used to destroy DNA, or crowds may accidently trample footprints.
Packet encryption will obviously obscure evidence for well-intended activities such as in-house network forensics as well as the ill-intended activities that it’s deployed against. Commercial mainframe packet stream viewing and inspection products don’t decrypt any packet content data. There are two approaches to mainframe TCP/IP encryption. The following summarizes their effects on network investigations:
SSL/TLS provides security and encryption for TCP connections. Generally, the TCP applications perform the SSL processing, and use different TCP ports for their normal and encrypted connections. HTTP and HTTPS are the best-known examples, but DDF also supports SSL.
TCP SSL connections still transmit their IP and TCP packet headers in the clear. They must; otherwise, every router in the network would need to be able to decrypt and re-encrypt them. Only their TCP packet content is encrypted. This means TCP connection setup, traffic statistics calculation, and response time calculations can continue to be visible because these are based on fields in the TCP headers.
One frequent use of packet stream viewing is during the initial setup of the SSL environment. Connection failures between a TCP application and partners are varied and common when doing the complex certificate setup tasks SSL requires. SSL handshake failures and their exact reasons can be highlighted with packet stream viewing.
IPSec securely connects private IP hosts over insecure public networks, possibly including the Internet. IPSec does this by encrypting the data and using Virtual Private Network (VPN) tunnels. The IP stack performs IPSec processing; applications such as DDF aren’t even aware of it. IPSec can protect all IP protocols, not only TCP.
When TCP connections use IPSec, they cease to be TCP at all; they use special IPSec IP protocols and different IP port numbers. Packet stream viewing recognizes these IPSec protocol packets—the IP packet header is always in the clear—and can identify and decode events such as key exchange negotiation failures. However, information normally obtained from the TCP headers isn’t available.
The CSI Effect
Network traffic forensics has it much easier than the complex, life-changing investigations resolved every week on television shows such as “CSI,” where well-groomed people generate a breakthrough with a single lab test. Instead of the gamut of human behavior, network forensics deals only with a small set of far more predictable, repetitious activities. IP connection setup and application data transfers follow set rules, standards, and protocols. Network and packet data reveal the hidden information you need to help nail the culprits of DB2 connection and performance problems. So, make friends with your mainframe networking group. They’re your expert crime lab consultants.