Distributed Relational Database Architecture™ (DRDA®) is a set of protocols that permits multiple database systems, both IBM® and non-IBM, as well as application programs, to work together.
DRDA coordinates communication between systems by defining what must be exchanged and how it must be exchanged.
If you want to study about DB2 networking behavior, you would need to deal with DRDA a lot. Below is a short article on how you can study the DRDA using a network protocol analyzer called Wireshark
. The Wireshark software can be downloaded from the link. In this article, I am going to run a simple SQL select command and see what Wireshark has captured from it.
Two machines are used for this tutorial. Below is the environment setup.Machine A
a) DB2 Enterprise Edition 9.7
b) Wireshark Version 1.4.7 Windows Installer (32bit).
c) WinPcap 4.1.2 .
WinPcap can be installed during the Windows Installation of Wireshark. Check the Install Wincap box during the Wireshark installation. Refer to the figure 1.
Figure 1: Wireshark WinPcap Setup
a) Window XP SP 3
b) Processor : Intel(R) Petium(R) 4 CPU 2.8 GHz
c) RAM : 2 GBMachine B
a) DB2 Enterprise Edition 9.7
b) Processor: Intel(R)Core(TM) Duo CPU 1.83 GHz
c) RAM: 3GB
In addition, you will need to make sure that you are able to ping from Machine A to Machine B.
The Machine A will act as client while Machine B will be the server. I have created a Sample database in Machine B and catalogued the database in Machine A.The alias for the Sample database in Machine A is SAMPLEL
. In this tutorial, we are not going to talk about how we can create the Sample database and configure a client-server database connection. However, below the links on how you can achieve this:-
Start the network analysis
1. Open you Wireshark Network Analyzer. Look for capture interface table. There is an Interface List there. Choose the network adapter your are using to connect to your LAN or Internet.
Figure 2: Wireshark start-up screen
2. Now you should be able to see that your Wireshark is starting to capture your network activities. Now, what we really want is the activity between the DB2 client-server. Now start your connecting to the sample database from your client and begin some DB2 activities.
3. Go to your DB2 Command Editor.
4. Next, click add and select SAMPLEL (which is the alias for the sample database in client). Enter your User ID and password for that database.
5. Now we are going to run some select statement. I will be running the following select statement and click execute.select * from dylan.employee
6. Go back to the Wireshark.
There is a Filter box. In the Filter box type DRDA.
7. You will only see the DRDA protocol in your Wireshark. You can now stop the capture activities. Go to Capture and click Stop. This will stop Wireshark from collecting data from your network.
It is just that simple. Install Wireshark and Wincap, run you DB2 and let Wireshark do the work.
Decoding the SQL query in DRDA
Below are just some important notes for you when you are analyzing using Wireshark.
Figure 3 shows the interface you will see when you start capturing your network activities. The most top box is the summary of each packet. The second and third boxes, is actually the detail information of each packet.
We are going to use the summary of the network packet you see in Figure 4. Each row in Figure 4 represents one network packet.
The IP 18.104.22.168 belongs to Machine A, which is the client, while the IP 22.214.171.124 belong to Machine B which is the server.
Here you can actually see what is happening in your network when you submit a query from a client to a server.
1. The query select * from dylan.employee
was sent to the client at packet #38. In this packet, the Prepare SQL Statement (PRPSQLSTT), dynamically binds the SQL statement to a section number into an existing package in a relational database. You will see the SQL statement (SQLSTT) which contains our select statement.
2. Next, in packet #42 a SQL Reply Data (SQLDARD) is sent from the server as a reply to packet #38
3. You have the Query Data (QRYDTA) in packet #47. This the data of the query.
4. In packet #48 Relational Database Commit Unit of Work (RDBCMM), indicates all the required work for the client request has completed.
5. You will usually see the End of Query Reply Message (ENDQRYRM) where the server sends a reply to the client that it has
completed the unit of work and ENDQRYRM is usually followed by the SQL Communications Reply Area (SQLCARD).
These are just very simple instructions on how you can use Wireshark and study the packet. I hope this will provide a start to you who will be interested in analyzing the DB2 networking activities.
1. DB2 9.7 information center:
2. DRDA Version 4, Volume 1: Distributed Relational Database Architecture: https://www2.opengroup.org/ogsys/jsp/publications/PublicationDetails.jsp?catalogno=c066
3. Network Analysis Unplugged, DRDA Unraveling the DB2 Decodes: http://thenetworkguy.typepad.com/nau/2009/06/drda-unraveling-the-db2-decodes.html
4. Wireshark: http://www.wireshark.org/