——————————————————————————————————————–
TIP # 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 # 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 # DB2
If using CURSOR SQL for read only, use FOR FETCH ONLY in the SQL.
——————————————————————————————————————–
TIP # 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 # DB2
To get only the first n rows
select *
from table name
fetch first n rows only
with ur;
——————————————————————————————————————–
TIP # 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 # DB2
How to select the duplicate rows from a table?
SELECT columns
FROM table
GROUP BY columns
HAVING COUNT(*) > 1
——————————————————————————————————————–
TIP # DB2
For SQL declaration of DECIMAL(n,m),
the COBOL equivalent generated is PIC S9(n-m)v9(m) COMP-3
If you would like to Earn Free Stocks, Credit card Points and Bank account Bonuses, Please visit My Finance Blog
You may also like to look at:
F1 for Mainframe 