Monday, July 22, 2013

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

1 comment:

  1. Good info.
    For real time interview questions on DB2,you can refer
    http://clearmainframeinterview.blogspot.com/

    ReplyDelete