Skip to main content

Top 10 DB2 SQL quries

RETRIEVE ONLY DUPLICATE ROWS FROM A TABLE:


SELECT A, B, C
FROM TABLE_A
GROUP BY B
HAVING COUNT(*) > 1


QUERY:


IF THE CHAR FIELD HAS FIRST DIGIT AS NUMBER
E.G. 1AA THEN THE VALUE SHOULD BE CONSIDERED AS '1'
12A THEN THE VALUE SHOULD BE CONSIDERED AS '12'
123 THEN THE VALUE SHOULD BE CONSIDERED AS '123'
A11 THEN THE VALUE SHOULD BE CONSIDERED AS '0'


ANSWER:

SELECT

CASE

SUBSTR(F1,1,3) BETWEEN '000' AND '999'

THEN INTEGER(SUBSTR(F_1,1,3))

WHEN SUBSTR(F1,1,2) BETWEEN '00' AND '99'

THEN INTEGER(SUBSTR(F_1,1,2))

WHEN SUBSTR(F1,1,1) BETWEEN '0' AND '9'

THEN INTEGER(SUBSTR(F_1,1,1))

ELSE 0

END

AS OUT


How to get the MAX count when group by some other field


SELECT USER_UPDATED, COUNT (USER_UPDATED) AS COUNT1

FROM CGDEV4.TWC39

GROUP BY USER_UPDATED

HAVING COUNT (USER_UPDATED) >= ALL

(SELECT COUNT (USER_UPDATED)

FROM CGDEV4.TWC39

GROUP BY USER_UPDATED);



How to Subtract the years, months and days separately from DATE


SELECT DATE ('2003-10-13') - DEC ('03') YEARS - DEC ('07') MONTHS - DEC ('13') DAYS FROM SYSIBM.SYSDUMMY1;


SELECT DATE ('2003-10-13') - DEC ('2000') YEARS –

DEC ('27') MONTHS - DEC ('45') DAY

FROM SYSIBM.SYSDUMMY1;


How To eliminate duplicate records except one


DELETE FROM TZCHC TD1

WHERE HOLIDAY_DATE<> (SELECT MIN (HOLIDAY_DATE)

FROM TZCHC TD2

WHERE TD1.LONG_DESC = TD2.LONG_DESC

AND TD2.LONG_DESC = 'CHRISTMAS DAY’);


How to find N th max or min


SELECT DISTINCT A.TIMESTAMP_UPDATED, A.USER_UPDATED

FROM CGDEV4.TWC39 A

WHERE 5 =

(SELECT COUNT (DISTINCT B.TIMESTAMP_UPDATED)

FROM CGDEV4.TWC39 B

WHERE B.TIMESTAMP_UPDATED >= A.TIMESTAMP_UPDATED)




How to Execute the SQL through JCL using utility


//CREATE EXEC PGM=IKJEFT01,DYNAMNBR=20

//SYSTSPRT DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SYSTSIN DD *

DSN SYSTEM(DB2T)

RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) LIB('DB2T.RUNLIB.LOAD')

END

/*

//SYSIN DD *

SELECT COUNT(USER_UPDATED)

FROM CGDEV4.TWC39

WHERE USER_UPDATED='DPPLSPP'

GROUP BY USER_UPDATED

/*


How to count occurrence of letter in a string


SELECT (LENGTH('SUBASH') - LENGTH(REPLACE('SUBASH','S',''))) AS S_COUNT FROM SYSIBM.SYSDUMMY1


How to delete duplicated based on certain column retaining one

DELETE FROM TABLE1 A WHERE A.PRIMARYKEY<>(SELECT MIN(PRIMARYKEY) FROM TZCHC B WHERE A.COLUMN1= B.COLUMN1);

Correlated Sub Query


SELECT A.DRIVER_REFERENCE

FROM CGDEV4.TU4ML A

WHERE A.LICENSE_NUMBER = 'POTAPA*355B2' AND

A.DRIVER_REFERENCE =

(SELECT C.CLIENT_REFERENCE

FROM CGDEV4.TZ1SR C

WHERE A.DRIVER_REFERENCE = C.CLIENT_REFERENCE AND

SUBSTR(C.Z_UPPER_FIRST_NAME,1,1) = 'A' AND

SUBSTR(C.Z_UPPER_LAST_NAME,1,3) = 'JZB')


How to get the value with leading zeros


SELECT POLICY_NUMBER, DIGITS (BASE_PREM_TOT)

FROM DB2K.CGIST.TW4T0


If the value of BASE_PREM_TOT is 472.00 then it will be selected as like this 00000047200



How to get the characters after space including first character with in one string?


SELECT SUBSTR (LAST_NAME_COY_NAME, 1, 1)

SUBSTR (LAST_NAME_COY_NAME, POSSTR (LAST_NAME_COY_NAME,' ') +1, 1)

SUBSTR (LAST_NAME_COY_NAME,

LOCATE (' ', LAST_NAME_COY_NAME, POSSTR (LAST_NAME_COY_NAME,' ') +1) +1, 1)

FROM CGDEV4.TZ1SR WHERE CLIENT_REFERENCE <= '0000000001';


Here POSSTR (LAST_NAME_COY_NAME,' ') +1 will give the position after space.

LOCATE (' ', LAST_NAME_COY_NAME, POSSTR (LAST_NAME_COY_NAME,' ') +1) +1, 1)

Will Returns the starting position of the first occurrence of expression1 within expression2. If the optional expression3 is specified, it indicates the character position in expression2 at which the search is to begin. If expression1 is not found within expression2, the value 0 is returned



How to get the fields which contain low values OR high values at any place


SELECT ADDR

FROM TABLE1

WHERE LENGTH (ADDR) > LEBGTH (REPLACE (ADDR, X’00’,’’) ;


If you want high values then replace X’00’ with X’FF’

For best performance you can use this.


SELECT TOWN_SUBURB

FROM DB2T.CGDEV5.TZ1SR

WHERE TOWN_SUBURB LIKE '% %'

FETCH FIRST 20 ROWS ONLY


After writing this, go to command prompt and turn on hex. I.e. HEX ON

Then you will get like this

DCDC4764674 39250DC0CD0


Go and change above 4 (highlighted in red color) as 0 and save the changes. Then it will become low value in that position. Then turn off hex. I.e. HEX OFF


Then it will select the all TOWN_SUBURB fields which contain low value at any place


How to get seat_no from which continuously 4 seats available


SELECT A.seat_no

FROM Table1 A

WHERE A.availabilty = 'Y'

AND EXISTS

( SELECT COUNT(*) FROM Table1 B

WHERE B.seat_no BETWEEN

A.seat_no AND A.seat_no + 2

AND B.availability = 'Y'

HAVING COUNT(*) = 4 )

FETCH FIRST 1 ROWS ONLY WITH UR


How to left pad?

SQL>create table test( id number);

SQL>insert into test values(1);
SQL>insert into test values(2);
SQL>insert into test values(3);
SQL>insert into test values(4);

SQL> select * from test;

ID
----------
1
2
3
4


SQL> select id,lpad(id,8,'00') from test;

ID LPAD(ID,
---------- --------
1 00000001
2 00000002
3 00000003
4 00000004

Syntax of lpad function is Lpad(String, max length, character to be padded);

SQL> select id,lpad(id,8,'0') from test;

ID LPAD(ID,
---------- --------
1 00000001
2 00000002
3 00000003
4 00000004




Comments

IBM DB2 Interview Questions and Answers
http://allinterviewquestionsandanswerspdf.blogspot.in/2016/06/top-32-ibm-db2-interview-questions-and.html

Popular posts from this blog

NULL VALUES and NULL INDICATORS in DB2

In DB2, the columns defined as NULL needs to be handled carefully else it will throw null exception error, in order to over come this error data type can be handled by using null indicator.
NULL is stored using a special one-byte null indicator that is "attached" to every nullable column. If the column is set to NULL, then the indicator field is used to record this. Using NULL will never save space in a DB2 database design - in fact, it will always add an extra byte for every column that can be NULL. The byte is used whether or not the column is actually set to NULL. The indicator variable is transparent to an end userConsider below Table :
Create Table SAMP_TABSN CHAR (10) SNAME CHAR (10) STATUS CHAR (2) NOT NULL BY DEFAULTCITY CHAR (10) NOT NULL
Note :: Unless you specify NOT NULL, the default is to allow for NULLIn above table SN and SNAME columns holds null values by default, in order to handle these null variables we need to have NULL-INDICATORS declares in the Program as

How to Solve SOC7 Abend - with screen shots

Below process helps to find out the statement, caused the SOC7 error.


Check the Sysout of RUNJCL. This shows the error statement and lists offset valueTake the Offset Value 000003C0Got to respective Compilation Job listing, check the sysprint Search for the offset value 0003C0 (delete +00 -- initial 3 letters of Offset value and search for it) check below 2 screen shotsThis Offset value is listed under line no 0045 – which refers to Move statement.
Take this no. 045 and find for it in same sysprint. This points to the exact statement, caused SOC7 This 045 pints to the Move statement 1526, this is the exact line in the programCheck for the above line no. In source program. This points to the statement highlighted below. Check the statement, variable check-4, which is added to check-6. These are having different Picture clause.check-4 is alfhanumaric, holding some junk data, when this data is moved to Chcek-6 variable(of comp-3) creates SOC7 error.This is just an example to explain one ca…

DB2 Utilities and Commands (helpful for DB2 Certification)

• Utilities
– Image Copy
– CDB SuperCopy
– Quiesce
– Load
– CDB SuperLoad
– Check
– BSCTIAUL
– CDB SuperUnload
– Recover
– CDB SuperRestore
– Reorg
– CDB SuperReorg
– Runstats
– Report Recovery
– Repair

• Commands
– Display/Stop/Start Database
– Display/Term Utility


DB2 Logging

• DB2 Logs
– A ‘journal’ of all activity for a subsystem
– Undo/redo records for table updates
– Each event in the log is identified by its RBA(relative byte address)
– RBA denotes a point in time: the greater the RBA, the later in time the event occurred
– Data Sharing - LRSNs along with RBAs

– Some events:
• Full image copy
• Quiesce
• Load


Data Sharing

• Benefits of Data Sharing
– High availability
– Workload balancing
– Sysplex parallelism
– Better use of resources


Backing Up Tables

• Image Copy
– Backup for DB2 table spaces
– Can also copy index spaces
– Can perform full (all data) or incremental (changed data) copies
– Can prevent or allow updates by others during copy
– SAMPLIB: DB2COPY

• CDB SuperCopy
– Faster method of creating stan…