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