My previous blog introduced the reader to the Distributed Relational Database Architecture (DRDA) protocol, one of the "big 3" when it comes to SQL application layer protocols used by commercial DBMS. (Oracle's TNS and SQL Server's TDS are the others.) DRDA is an Open Group standard, but found almost exclusively in IBM DB2 environments. I'll conclude with a look at how DRDA looks when captured and decoded at the packet level.
A Sequence of Packets
If your analyzer doesn't have a decoder for DRDA (as of this writing the "big three" don't have it), all you would see is something like this in the TCP payload:
You can see the select statement but there's so much more! Thanks to Wireshark, we can see additional clues about each packet in the summary:
As we can see in Packet #2, multiple SQL commands can be sent in a single DRDA packet. In this, each of three commands or more correctly, "code points", show up as a prepare SQL statement (PRPSQLSTT), followed by attributes (SQLATTR), and finally, the statement itself (SQLSTT) that we were able to make out in the raw data.
The detailed decode pane breaks out the TCP payload for packet #2 like this:
Each command is highlighted in gray. Note that code point is formatted according to the rules of the DRDA Distributed Data Management (DDM) architecture as mentioned in my prior blog. We have the length in bytes (of the entire command including the length field), a fixed "magic ID" of hex D0, formatting information, the actual code point (hex 2414 for SQLSTT), and finally, optional data for that code point.
Also note that Wireshark will show the data in both ASCII and EBCDIC, since the character sets can change. I've even seen the character set change from command-to-command inside the same packet.
Putting it All Together
What are we accomplishing in this example? We are simply preparing a request to get the timestamp from SYSIBM.SYSDUMMY1 in packet #2, receiving SQL Reply Data (SQLDARD) in packet #3, and opening the query or essentially requesting that the DB2 host execute the SQL statement in packet #4 (you will often see this in the packet containing the SQLSTT, eliminating an extra packet exchange).
Our result came back in packet #5. As we can see from the delta time between packets #4 and #5, it took 0.2 ms to return the result (the trace is taken from the DB2 side, so the response time is the as close as we can get to the actual server processing time.) The result packet contains five code points: the Open Query Reply Message (OPNQRYRM) including a severity code (hint: this is a good field to filter or trigger on a non-zero value which indicates an error), the answer set description (QRYDSC) and data (QRYDTA), End of Query Reply Message (ENDQRYRM), and finally the SQL Communications Reply Area (SQLCARD) containing our timestamp that we asked for.
Packet #6 is a Relational Database Commit Unit of Work (RDBCMM) command, since the application is done with the "work" for the aforementioned SQL statement.
Understanding which DRDA packets are commands are which ones are responses are critical to analyzing response times to SQL commands and queries. If you are new to DRDA, the nomenclature for responses (and many of the commands for that matter) are not immediately obvious, but easy to pick up on. For instance, SQLDARD in packet 3 stands for SQLDA reply data. SQLCARD is the SQL communications area reply data. If you are looking at a busy server without filtering, you will not always see the reply immediately following a command, so be vigilant. The complete list of reply packets can be found in table 1-4 on page 42 of the DRDA Specification Volume 1. The specifications can be downloaded for free here.
Responses can also come in multiple packets. Look at the delta time from the command to the first SQL response packet (not the TCP ack) to gauge the DB2 server's response time.
Conclusion
When you get into analyzing DRDA, you'll see a myriad of ways in which the protocol, and subsequent SQL commands can be used. Hopefully I've whet your appetitive with but one small example. Grab some traces, dig into the spec, and happy analyzing!
Comments