Raghu On Tech

Pro tips on managing data at scale

Linux Tips And Tricks For DBA’s

by | Jun 7, 2018 | 0 comments

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

Submit a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shares
Share This

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close