Sunday, June 10, 2007

DB2

TIP # 093
DB2

Use the OS/390 – MVS solution ‘Move current-date to ……’ to obtain a date or
to do a date calculation instead of DB2’s ‘SET CURRENT TIMESTAMP’.

TIP # 094
DB2

Avoid joins that involve more than two TABLES. Break it up into multiple SQL statements.
In a case study, a 5-table join used 4190 CPU hours in a month for a weekly process.
Breaking it up dropped the usage to 9 CPU hours per month!!

TIP # 095
DB2

If using CURSOR SQL for read only, use FOR FETCH ONLY in the SQL.

TIP # 096
DB2

How To Match Contoken Across Loadlib & Dbrmlib
Step 1.
Go to SYSIBM.SYSPACKAGE for that program - look at the latest bind -
you can look at the time stamp column to find the latest. There will be a
column called CONTOKEN. This will have a value.
Ex -
-+---------+---------+---------+---------+---------+---------+---------+----
COLLID NAME CONTOKEN OWNER CREATOR TIMESTAMP
-+---------+---------+---------+---------+---------+---------+---------+----
CBPR39WO PGMNAME .îÒ#.¹". DFDBS ABC
2004-04-21-06.42.00.
Step 2 . Covert the CONTOKEN to zoned decimal format. From the above example it
is X'1756ed7b02da7f32'. You can use HEX on to find the ZD format.
Step 3. Go to the DBRMLIB - Search for this String like - F x'1756ed7b02da7f32'
. If it is the correct DBRM lib using which it is bound then you will find a
match.
Also you can get the DBRMLIB used for binding from the above Query o/p
also if you scroll towards the end. There is a Column for that also.
Now How to match it to load lib .

Step 4. Go to your load lib in browse mode. Search for the First 8 bytes in the
above ZD format of the CONTOKEN
Like =-- F '1756ed7b'. If you find a match then it is your matching
load !!!
The twist here is - on the SYSIBM.SYSPACKAGE and The DBRMLIB the
contoken is stored in the Same format, But in the Loadlib the date & time part of
the CONTOKEN is swapped and stored. This is why you need to search for the
8 bytes separately. Otherwise you need to swap the 16 bytes of the
CONTOKEN and search.

TIP # 097
DB2

To get only the first n rows
select *
from table name
fetch first n rows only
with ur;

TIP # 098
DB2

Performance Tuning
Use JOIN instead of Sub-Queries
SELECT EMPNO, LASTNAME FROM EMP, DEPT
WHERE WORKDEPT =DEPTNO
AND DEPTNAME = ‘PLANNING’
SELECT EMPNO, LASTNAME FROM EMP
WHERE WORKDEPT IN
( SELECT DEPTNO FROM DEPT
WHERE DEPTNAME = ‘PLANNING’ )
TIP # 099
DB2
How to select the duplicate rows from a table?
SELECT columns
FROM table
GROUP BY columns
HAVING COUNT(*) > 1

TIP # 100
DB2

For SQL declaration of DECIMAL(n,m),
the COBOL equivalent generated is PIC S9(n-m)v9(m) COMP-3

3 comments:

Mihir said...

umm what was dat again dood?
:P

Joy said...

Thanks for the post

You can find some good DB2 Techical Interview Question Answers on in the below link

DB2 Technical Interview Question Databse

Thanks
Joya

Anonymous said...

Dude you are doing awesome job !! keep it up