DB2 SQL Frequently used Queries

How to fetch last n rows only in DB2

You can use ORDER BY clause ... the syntax is as below

SELECT COL_A

FROM YOUR.TABLE

WHERE KEY_A = :KEY-A

ORDER BY COL_A  DESC

FETCH FIRST n ROWS ONLY

How to look for LOW VALUES in DB2

SELECT CLIENT_NAME FROM QUALIER.TABLE_NAME
where CLIENT_NAME  like X'6C006C' 

 The 6C is the hex equivalent of the '%' sign, This will return any rows with low values anywhere in the column

How to find relationships between tables

Suppose we have a table with a column name 'SITE_CD'. We want to find out what all table have the same cloumn name in the system.

SELECT SUBSTR(TBNAME,1,18) AS TABLE FROM SYSIBM.SYSCOLUMNS

WHERE NAME = 'SITE_CD'  

AND TBCREATOR = 'schema-name'

ORDER BY  TABLE

***AND TBCREATOR = 'schema-name'  :   is optional
like RBM00.CLINT WHERE RBM00 IS OPTIONAL

Find the PRIMARY KEY of a Table

SELECT TBCREATOR, TBNAME, NAME, KEYSEQ
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'BZK00'
AND TBNAME = 'CLIENY_NAME'
AND KEYSEQ > 0
ORDER BY KEYSEQ

DATE/TIMSTAMP

SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1

SELECT CURRENT DATE  FROM SYSIBM.SYSDUMMY1

SELECT CURRENT TIME  FROM SYSIBM.SYSDUMMY1

SELECT DAYS('2010-10-31') - DAYS('1990-10-10')  FROM SYSIBM.SYSDUMMY1

Change the values of column using TRANSLATE in DB2

Translate option can be used to change a character to another in a column.

UPDATE  Schema.TBNAME
set COLUMN_NAME = TRANSLATE (COLUMN_NAME,'A','C')
where TRN_ID ='2000-09-21-01.09.14.828223'
It will scan all the characters in the column 'COLUMN_NAME' which are 'C' and change them to 'A'.
'A' , 'C' can be numeric/non-numeric combination also.

Find out DB2 locks holding up table space or tables
 

Go to DB2 command window and give the below command

-dis db(XXXXXX0 sp(XXXXXX) USE

you also can change the parameter USE to LOCKS or also CLAIMERS

SP means the Tablespace name or INDEXNAME