Packet sniffing the DRDA protocol – A very low impact way to view incoming statements going into DB2

I’m currently in the process of making major changes to the application I wrote earlier this year for IDUG North America. This is a result of some great feedback and a number of new requirements to create a production quality application. The biggest of the changes is to no longer front the port and simply view a copy of TCP packets. This prevents any problems in my application from affecting availability of DB2.

I figured I would post some small applications along with way that people might find them useful and can modify them for their own needs. This first small application makes use of libpcap to be able to capture all the statements coming in to the DB2 server over DRDA and prints them to the screen. You can turn statement tracing on and off at anytime without impacting availability and the applications uses less than 1% of CPU. This is much lower than the statement event monitor which from past presentations I attended can add 5-15% overhead.


Requirements to use:

  1. UNIX Operating System
  2. libpcap installed
  3. gcc 3.2 or higher
  4. make

Build instructions

  1. Download application here
  2. Extract .tar.gz
  3. cd to extracted directory/Release
  4. make

To run as root: ./statement_pcap  eth0 50000 (or whatever device you want to trace. Use ifconfig to find device)

void parsedrda(const u_char * packet,pcap_pkthdr & header);

int main(int argc, char *argv[]) {
  pcap_t *handle;/* Session handle */
  char *dev = argv[1];/* The device to sniff on */
  char errbuf[PCAP_ERRBUF_SIZE];/* Error string */
  struct bpf_program fp;/* The compiled filter */

  char filter_exp[9 + strlen(argv[2])] ;/* The filter expression */
  strcpy(filter_exp,"dst port ");

  bpf_u_int32 mask;/* Our netmask */
  bpf_u_int32 net;/* Our IP */
  struct pcap_pkthdr header;/* The header that pcap gives us */
  const u_char *packet;/* The actual packet */

  /* Find the properties for the device */
  if (pcap_lookupnet(dev, &net, &mask, errbuf) == -1) {
    fprintf(stderr, "Couldn't get netmask for device %s: %s", dev, errbuf);
    net = 0;
    mask = 0;

   /* Open the session in promiscuous mode */
  handle = pcap_open_live(dev, BUFSIZ, 1, NULL, errbuf);
  if (handle == NULL) {
    fprintf(stderr, "Couldn't open device %s: %s", dev, errbuf);

  /* Compile and apply the filter */
  if (pcap_compile(handle, &fp, filter_exp, 0, net) == -1) {
    fprintf(stderr, "Couldn't parse filter %s: %s", filter_exp, pcap_geterr(handle));

  if (pcap_setfilter(handle, &fp) == -1) {
    fprintf(stderr, "Couldn't install filter %s: %s", filter_exp, pcap_geterr(handle));

  /* PProcess */

  while((packet = pcap_next(handle, &header)) != (const u_char *)0){

  /* And close the session */


void parsedrda(const u_char * packet1,pcap_pkthdr & header){

  char packet[header.len];
  memcpy(packet,packet1,header.len);   // Make a copy of the packet to work with

  unsigned int ddm_start = (sizeof(struct ether_header) +
  sizeof(struct ip) + sizeof(struct tcphdr))+12; // Start of the DDM info
  while(ddm_start < header.caplen)
    int ddm_length = (packet[ddm_start]<<2) +  packet[ddm_start+1];
    unsigned short ddm_type =  (unsigned short)packet[ddm_start+3];

    if(ddm_length == 0){


    if(ddm_type == 0x03){   // Identifier for SQLSTT (SQL Statement)
    packet[ddm_start+ddm_length-1] = 0;


  ddm_start += ddm_length;


Recent Stories
Mysql_real_escape_string/addslashes for DB2 CLI

Packet sniffing the DRDA protocol – A very low impact way to view incoming statements going into DB2

Disk Throughput on Amazon EC2, VM Ware, Slicehost, Internal Storage, and SANs