Tags

, , , , , , ,

——————————————————————————————————————–
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

 

——————————————————————————————————————–

Important SQL CODES and ABEND CODES
SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
KNOW YOUR MAINFRAME
REXX – INITIAL SETUP
DB2 SQL Query to read COMP (COBOL) data stored in CHAR column
DB2 Performance – Using SET vs SYSDUMMY1 table
DB2 Performace – Predicates Processing Order
DB2 External Stored Procedures
DB2 UPDATE QUERY – UPDATE TABLE1 FROM TABLE2 DATA
DB2 PERFORMANCE ISSUE (When using EXISTS)
HOW DOES DB2 INTERNALLY (PHYSICALLY) STORE THE DATA
HOW DO YOU FIND WHO HAS ACTUALLY LOADED THE DB2 TABLE RECENTLY
TERMINATE A STOPPED DB2 UTILITY
SELECT UNIQUE RECORDS USING “GROUP BY”
DB2 and SQL INTERVIEW QUESTIONS
DB2 TIPS
Optimistic Locking vs. Pessimistic Locking
DB2 BIND OPTIONS and ISOLATION
Is SCHEMA name necessary for DYNAMIC QUERY
DB2 SQL – REPLACE CHARACTERS WITH ACCENTS (NON ENGLISH ALPHABETS)
Advertisements