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

3 comments:

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

    ReplyDelete
  2. Online psychology essay writing services are very difficult to complete and many students are always searching for Help with Psychology Coursework Writing services to help them complete their psychology research paper writing services and psychology case study writing services.

    ReplyDelete
  3. Accounting assignment writing services have become very popular for students studying finance & accounting coursework writing services as they engage the best online Accounting Writing Services.

    ReplyDelete