DB2 LINUX and DB2 AIX — Empty line makes a difference in a shell script

I have had an interesting observation this week and it is about subtle difference between AIX and LINUX (SUSE in this case) from DB2 standpoint. In this blog I will share about this rather surprising encounter I had while I was working on a simple database task.I  had altered 2 tables in one of our DEV environment and wanted to reorg them. It was DB2 9.7 on SUSE LINUX 10.4.

I usually use UNIX scripts to do repetitive tasks (Lazy DBA !) and this task was not an exception. I had schema and tablenames saved in a file and then used a combination of cat and awk commands to pass as arguments to my script (rightly named reorg) to do reorg. File ‘list’ has schema and tablenames in it.

## cat list | awk '{print "reorg "$0}' | sh

But the output of this was rather surprising.. I got a SQL1024N indicating database connection was not made for each table in the list.

connect to TESTDB
Database Connection Information
Database server = DB2/LINUXX8664 9.7.7
SQL authorization ID = DB2INST1
Local database alias = TESTDB

reorg table SCHEMA1.TABLE1
SQL1024N A database connection does not exist. SQLSTATE=08003

I looked at my UNIX script and it was evident that I was connecting to my database just before running the reorg command.. Here it was:

db2 -v "connect to $database"

db2 -v "reorg table $SCHEMA.$TBNAM "

Initially, I wasn’t sure why it was not working as expected.. When I researched further, I found a UNIX script for “runstats” that was similar to this “reorg” script and that “runstats” script worked fine (without connection related problems).

Here is how runstats script was:

db2 -v "connect to $database"
db2 -v "runstats on table $SCHEMA.$TBNAM with distribution on all columns and detailed indexes all allow write access"
           -------> There was an empty line after the above line


The only difference was that “runstats” script had an “empty line” as the last line… I really did not think that could make a difference but it did! How could presence of an empty line make a script run successfully? Very interesting..

One of my colleagues who was equally intrigued by this pointed to this URL which seems to explain the problem. Here is what it says:

Some users have ksh scripts that run on AIX however when moving these same scripts to Linux they fail with error when doing a CONNECT RESET……This issue is due to the inherent differences between AIX and Linux and how they handle ksh scripts.

So I added an empty line (as the last line of the script) and there it worked just fine ! Also, I noticed that adding a commented out line also worked fine.

db2 -v "connect to $database"
db2 -v "reorg table $SCHEMA.$TBNAM "
## db2 -v "connect reset" <=== This also could be an empty line with at least one white space in it.

I tested this on AIX 7.1 DB2 10.1 and did not encounter this problem. As the IBM’s URL rightly points out, this behavior is due to the inherent differences between AIX and LINUX. Sometimes this could end up being a nasty surprise (as was the case for me)…

Just thought of sharing this with you folks.. Hope that helps.  Happy Scripting 🙂

Thanks for reading.

Pavan Kristipati.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s