DB2 DBA Tip: UNIX script to find column names that have matching string

In this blog post, I will share a quick UNIX script that I frequently use to find column names that have a string in them. This came out of a need a while ago when I had to frequently type a long SQL that would generate matching column names.

grep in AIX vs. LINUX

As mentioned in the comments, LINUX (SUSE) does not allow ‘paragraph’ to be grepped. I ended up using ‘B6’ along with ‘grep’ to grep the previous 6 lines and then filter on the database name. In AIX, you could simply use ‘grep -ip’ option for a paragraph and then filter on the database name.

Script: findcolumn

#!/bin/bash

. $HOME/sqllib/db2profile

COL=`echo $1 | tr '[:lower:]' '[:upper:]'`
## This script lists database objects that have the column $1 in them
database=`db2 list database directory show detail | grep -B6 -i indirect | grep "Database name" | sed "s/.*= //" `
## comment the above line and simply connect to a database if you have multiple databases in the same instance
## The option "B6" will not work in AIX. Use the following line in AIX if you have a single database
## database=`db2 list database directory show detail | grep -ip indirect | grep "Database name" | sed "s/.*= //" 

db2 connect to $database > /dev/null 

db2 -v "select trim(char(tabschema, 10)) || '.' || char(tabname,60) as table_name,char(colname, 40) as COLUMN,       char(typename,30) as datatype from syscat.columns where colname like '%$COL%' order by tabname with ur"

Example:

Assume we want to find all the columns that have ‘card’ in them along with the table each column is associated with. We will pass ‘card’ as an argument for the script ‘findcolumn’ as shown below.

$findcolumn card | more

TABLE_NAME                                                              COLUMN                                   DATATYPE
----------------------------------------------------------------------- ---------------------------------------- ------------------------------
DB2INST1.ADVISE_TABLE                                                   GEN_COLUMNS                              CLOB
SYSIBM.COLUMNS                                                          COLUMN_NAME                              VARCHAR
SYSIBM.COLUMNS                                                          COLUMN_DEFAULT                           VARCHAR
SYSIBM.COLUMNS_S                                                        COLUMN_NAME                              VARCHAR
SYSIBM.COLUMNS_S                                                        COLUMN_DEFAULT                           VARCHAR
EDWQA.COMPL_EVENT_TARGET                                           COMPL_EVENT_TARGET_COLUMN_NAME_CDLKP          INTEGER
EDWDV.COMPL_EVENT_TARGET                                           COMPL_EVENT_TARGET_COLUMN_NAME_CDLKP          INTEGER
EDWSTG2DV.COMPL_EVENT_TARGET_LOAD                                  COMPL_EVENT_TARGET_COLUMN_NAME_CDLKP          INTEGER
EDWSTG2QA.COMPL_EVENT_TARGET_LOAD                                  COMPL_EVENT_TARGET_COLUMN_NAME_CDLKP          INTEGER
CODEDV.CORP_GL_ASSIGN                                          SOURCE_SYSTEM_COLUMN_NAME1                        VARCHAR
CODEDV.CORP_GL_ASSIGN                                          SOURCE_SYSTEM_COLUMN_VALUE1                       VARCHAR
CODEDV.CORP_GL_ASSIGN                                          SOURCE_SYSTEM_COLUMN_DESC1                        VARCHAR
DB2INST1.EXPLAIN_OBJECT                                                 COLUMN_COUNT                             SMALLINT
SYSTOOLS.EXPLAIN_OBJECT                                                 COLUMN_COUNT                             SMALLINT
DB2INST1.EXPLAIN_STREAM                                                 COLUMN_COUNT                             SMALLINT
DB2INST1.EXPLAIN_STREAM                                                 COLUMN_NAMES                             CLOB
DB2INST1.EXPLAIN_STREAM                                                 PARTITION_COLUMNS                        CLOB
SYSTOOLS.EXPLAIN_STREAM                                                 COLUMN_COUNT                             SMALLINT
SYSTOOLS.EXPLAIN_STREAM                                                 COLUMN_NAMES                             CLOB
SYSTOOLS.EXPLAIN_STREAM                                                 PARTITION_COLUMNS                        CLOB
...........................................

Find Columns only from Catalog Tables:

This script could be used in combination with pipe (|) and grep for filtering purposes.

To find matching column names from catalog tables only, use:

$findcolumn column | grep -i sys

When you filter on ‘sys’,  the list will end up in having user tables that have ‘sys’ in them as well… but, I think you get the point.

I have written a number of UNIX scripts over the years working as a DB2 DBA. Simple scripts like this one usually end up being time savers and the ones that help us get to what we want as fast as possible.

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