Raghu On Tech
Pro tips on managing data at scaleLinux Tips And Tricks For DBA’s

In this blog post I am going to discuss few Linux tips and tricks that I use every once in a while to troubleshoot problems or to understand existing environment.
Finding Top 10 Largest Files on the System Including Full Path:
If you run into a file system full condition and need to figure out which files are occupying the most space, below command will save your day. Of course there are many other ways to do this exact same thing.
find / -printf ‘%s %p\n’ 2>/dev/null| sort -nr | head -10
Above command basically says, print the size and name of the file with full path, if any file access resulted in an error throw the error to recycle bin, then perform a numerical sort in descending order, then print the 10 files at the top of the list.
Please pay special attention to “2>/dev/null” at the end of the find command. It will basically remove all the noise or chatter due to file permission issues to /dev/null so that you can focus on the important information on the screen. Here number 2 is the file descriptor, by default it stands for standard error messages.
A sample output of the above command is shown below.
$ find / -printf '%s %p\n' 2>/dev/null| sort -nr | head -10 7477526528 /db2backup/ILMT.0.db2inst1.DBPART000.20160207020010.001 3268063232 /db2backup/TEST.0.db2inst1.NODE0000.CATN0000.20151016114702.001 3247431680 /db2backup/binaries/V10.5.tar 2985742336 /db2backup/TEST.0.db2inst1.NODE0000.CATN0000.20151105113011.001 2915085802 /db2backup/informix/test_ontape_03182015_v11.70.fc5.gz 2099609600 /db2backup/binaries/V10.5/v10.5fp8_linuxx64_server_t.tar 1799803288 /var/tmp/yum-root-LSldLb/temp-nbu-client-x.x.x.0-4.el6.x86_64.rpm 1611950080 /db2backup/binaries/V10.5/v10.5fp6_linuxx64_server_t.tar 1005168640 /db2backup/binaries/V10.1/v10.1fp4_linuxx64_universal_fixpack.tar 959580160 /db2backup/binaries/V10.1/v10.1fp3a_linuxx64_universal_fixpack.tar
How to Get Information About Filesystem Properties:
tune2fs command is a great tool to obtain information about supported filesystem(FS) features, mount info, FS state, FS Block size, FS Crash Info etc. Most probably you will be needing root access to run this command and might be helpful to you when troubleshooting issues related to storage.
$ tune2fs -l /dev/mapper/VolGroup1-lv_db2data tune2fs 1.41.12 (17-May-2010) Filesystem volume name: <none> Last mounted on: <not available> Filesystem UUID: 43f60182-66ac-40f9-9816-aa96d6008bc9 Filesystem magic number: 0xEF53 Filesystem revision #: 1 (dynamic) Filesystem features: has_journal ext_attr resize_inode dir_index filetype needs_recovery sparse_super large_file Filesystem flags: signed_directory_hash Default mount options: (none) Filesystem state: clean Errors behavior: Continue Filesystem OS type: Linux Inode count: 16318464 Block count: 65272832 Reserved block count: 3262953 Free blocks: 63207227 Free inodes: 16318269 First block: 0 Block size: 4096 Fragment size: 4096 Reserved GDT blocks: 1008 Blocks per group: 32768 Fragments per group: 32768 Inodes per group: 8192 Inode blocks per group: 512 Filesystem created: Fri Jan 23 15:33:30 2015 Last mount time: Mon Jan 22 10:45:02 2018 Last write time: Mon Jan 22 10:45:02 2018 Mount count: 60 Maximum mount count: 36 Last checked: Fri Jan 23 15:33:30 2015 Check interval: 15552000 (6 months) Next check after: Wed Jul 22 16:33:30 2015 Reserved blocks uid: 0 (user root) Reserved blocks gid: 0 (group root) First inode: 11 Inode size: 256 Required extra isize: 28 Desired extra isize: 28 Journal inode: 8 Default directory hash: half_md4 Directory Hash Seed: ff52e749-d27e-4a0b-81b5-a408e3ddfa73 Journal backup: inode blocks
grep Command Paragraph Mode Simulation in Linux:
If you were a DBA before commodity hardware and Linux became mainstream for Db2 servers and VM’s, you might have worked a lot on AIX machines. There is a good chance that you may have used grep in paragraph mode by using -p flag.
I am used to querying database info from database directory as follows. Displaying the whole database entry is important to me to look at stuff such as, if its a local or remote database, catalog info and alternate server info etc.
db2 list db directory|grep -ip “<DBNAME>”
However on Linux grep does not support paragraph mode, I can think of multiple ways to overcome this. I am going to show 3 different ways to accomplish this in this blog post.
Method 1 (perl):
In the first case, we are going to use perl. Below perl command simply says that -00 (turn the input into paragraph mode) -n (loop through the input) -e (execute the commands from command line rather than a script file) and finally print if the paragraph matches a regular expression case insensitively (i flag).
$ db2 list db directory|perl -00 -ne 'print if /tEsT/i' Database alias = TEST Database name = TEST Local database directory = /home/db2iadm1/db2inst1 Database release level = 10.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =
Method 2 (awk):
Below awk example is very similar to the perl example above. Here we are changing the input (RS) and output record separators (ORS) to two newlines and input (FS) and output field separators (OFS) to one new line.
$ db2 list db directory|awk 'BEGIN{RS=ORS="\n\n";FS=OFS="\n";}/TEST/' Database alias = TEST Database name = TEST Local database directory = /home/db2iadm1/db2inst1 Database release level = 10.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =
Method 3 (awk):
Finally we play a little trick with awk, here we are using awk range match and print. Below command simply says, match the string TEST and until you find the next blank line keep printing. This approach is not as clean as the other two but works alright.
$ db2 list db directory|awk '/TEST/,/^$/' Database alias = TEST Database name = TEST Local database directory = /home/db2iadm1/db2inst1 Database release level = 10.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =
Monitor Open Ports on Remote Server:
This will be especially useful when you are troubleshooting connection problems. I can think of a couple of ways to check if a port on the remote Db2 server is open or not.
Method 1 (nmap):
Use nmap to scan the port or range of ports on a host or range of hosts. nmap is a very powerful tool used by network admins, you should at least check with your SA before you run this on your environments. Lot of intrusion detection software’s prevent utilities that scans ports from running. nmap comes with a lot of options, I am going to show something basic…
$ nmap -sV -p1025-65535 10.156.172.55 Starting Nmap 5.21 ( http://nmap.org ) at 2018-06-07 10:03 EDT Nmap scan report for 10.156.172.55 Host is up (0.00011s latency). Not shown: 64499 filtered ports PORT STATE SERVICE VERSION 1535/tcp closed vlsi-lm 3500/tcp closed mysql 10050/tcp open tcpwrapped 13710/tcp closed netbackup 13711/tcp closed netbackup 13710/tcp open vnetd Veritas Netbackup Network Utility 13715/tcp open netbackup? 13790/tcp closed netbackup 13785/tcp closed unknown 50000/tcp open unknown Service detection performed. Please report any incorrect results at http://nmap.org/submit/ . Nmap done: 1 IP address (1 host up) scanned in 273.24 seconds
In the above command I am basically telling nmap to scan all the tcp ports on host 10.156.172.55 that are not privileged. Remember on Unix systems ports 1-1024 are known as privileged and should only be used by root for necessary services. -sV option tells nmap to detect the service name for the port if one exists. In the above output you can see that port 50000, the default Db2 port is open.
There are tons of options to play around with nmap command, such as group pings, host discovery, skipping port scans, specifying DNS servers etc. I never had to mess around with many of the options given my technical role, but when troubleshooting an outage or a critical issue your role does not matter. The more you know the better you are equipped to confront the problem.
So nmap is a great tool to know if you are trying to identify all the open ports on a host or to check if a service is running on a host.
Method 2 (Telnet):
This method is more familiar to the DBA’s and less intrusive than nmap command. telnet command lets you remotely connect to a port so that you can validate the connection. Lets take a simple example below, to check the connectivity to host 10.156.172.55 on port 50050.
[root@tester ~]$ telnet 10.156.172.55 50050 Trying 10.156.172.55... Connected to 10.156.172.55. Escape character is '^]'.
As you can see from the above output, telnet was able to connect to the host on port 50050. If you enter a string or input at the prompt, db2sysc will recieve it and probably put some warnings in db2diag.log file. I have observed this behavior in the past with HADR ports.
To terminate the telnet connection you need to enter ^] or (ctrl+v)] on your windows machine.
Monitor the ports locally:
Your friend here is netstat command. In the below command -tpln flags tell netstat command to
-t –> show only tcp ports
-p –> show process id and service name attached to the socket
-l –> show only listening sockets
-n –> show numerical addresses for IP and port numbers rather than (hostnames and service names).
$ netstat -tpln (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:13724 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:50000 0.0.0.0:* LISTEN 14726/db2sysc 0 tcp 0 0 0.0.0.0:10050 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:33093 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:18089 0.0.0.0:* LISTEN - tcp 0 0 127.0.0.1:41740 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:1556 0.0.0.0:* LISTEN - tcp 0 0 127.0.0.1:1557 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:13782 0.0.0.0:* LISTEN - tcp 0 0 0.0.0.0:37720 0.0.0.0:* LISTEN - tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN - tcp 0 0 :::10050 :::* LISTEN - tcp 0 0 :::34058 :::* LISTEN - tcp 0 0 :::4750 :::* LISTEN - tcp 0 0 :::111 :::* LISTEN - tcp 0 0 :::1556 :::* LISTEN - tcp 0 0 :::38295 :::* LISTEN -
From the above output, you can confirm that Db2 is listening on port 50000 locally on your server.
There are lots of other tips and tricks that I can think of, I will be adding them to this blog post every now and then. Keep an eye out…
0 Comments