Page 1 of 2
DB2 Tips.
Posted: Fri May 24, 2013 12:20 pm
by Anuj Dhawan
1. 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’.
Re: DB2 Tips.
Posted: Sat May 25, 2013 1:08 pm
by Akshya Chopra
Thanks Anuj. Here is another tip, hope this helps:
2. If using CURSOR SQL for read only, use FOR FETCH ONLY in the SQL.
Re: DB2 Tips.
Posted: Tue May 28, 2013 8:22 pm
by Anuj Dhawan
3. If you're using CURSOR for "read only", use FOR FETCH ONLY in the SQL.
Re: DB2 Tips.
Posted: Wed May 29, 2013 11:46 pm
by Anuj Dhawan
4. How to select the duplicate rows from a table?
Code: Select all
SELECT columns
FROM table
GROUP BY columns
HAVING COUNT(*) > 1
Re: DB2 Tips.
Posted: Tue Jul 16, 2013 6:43 pm
by Anuj Dhawan
5. Do you know that - for SQL declaration of DECIMAL(n,m),the COBOL equivalent generated is PIC S9(n-m)v9(m) COMP-3.
Re: DB2 Tips.
Posted: Sat Sep 21, 2013 4:00 pm
by Anuj Dhawan
6. If possible, take out any (read it as 'all') Scalar functions coded on columns in predicates.
For example:
Code: Select all
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE YEAR(HIREDATE) = 2013
Should be coded as:
Code: Select all
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE HIREDATE BETWEEN ‘2013-01-01’ and ‘2013-12-31’
Re: DB2 Tips.
Posted: Sat Sep 21, 2013 4:12 pm
by LearnMainframe
7. On similar lines, one should avoid using all mathematics coded on columns in predicates.
For example:
Code: Select all
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE SALARY * 2.5 > 50000.00
Should be coded as:
Code: Select all
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE SALARY > 50000.00 / 2.5
Re: DB2 Tips.
Posted: Sat Sep 21, 2013 5:30 pm
by Anuj Dhawan
Thanks LearnMainframe, much appreciate your contribution.
Regards,
Re: DB2 Tips.
Posted: Sat Sep 21, 2013 10:57 pm
by Anuj Dhawan
8. Avoid using ‘Distinct’, if possible.
For example: This DISTINCT
Code: Select all
SELECT DISTINCT A.FIRSTNAME, A.LASTNAME
FROM TABLE A, TABLE B
WHERE A.ID = B.ID_NO
can be rewritten as:
Code: Select all
SELECT A.FIRSTNAME, A.LASTNAME
FROM TABLE A, TABLE B
WHERE A.ID = B.ID_NO
GROUP BY A.FIRSTNAME, A.LASTNAME
Re: DB2 Tips.
Posted: Sun Sep 22, 2013 11:04 am
by Anuj Dhawan
Thanks Bill - thanks for bringing the error to attention, the code is corrected now. Hope it looks good now.
Regards,
Re: DB2 Tips.
Posted: Sun Sep 22, 2013 12:35 pm
by Anuj Dhawan
9. It's advisable not to use JOINs that involve more than two TABLES. In such a case, break the query into multiple SQL statements.
Re: DB2 Tips.
Posted: Sun Sep 22, 2013 5:02 pm
by AD991
This is a nice collection, well done. Thanks for sharing them.
Re: DB2 Tips.
Posted: Mon Sep 23, 2013 8:15 am
by Anuj Dhawan
Thanks for the feedback. Feedback(s) always helps to keep you on track. Hopefully they are helpful, one way or other.
Regards,
Re: DB2 Tips.
Posted: Sun Nov 03, 2013 11:36 pm
by AD991
It's my pleasure Anuj.
Re: DB2 Tips.
Posted: Thu Nov 21, 2013 7:02 pm
by zprogrammer
10. How to find how many occurances of values
Code: Select all
SELECT <FIELD_NAME>,COUNT(FIELD_NAME>
FROM <TABLE_NAME>
<WHERE CLAUSE IF ANY>
GROUP BY <FIELD_NAME>
Re: DB2 Tips.
Posted: Wed Nov 27, 2013 3:14 pm
by Anuj Dhawan
Thanks Pk,, I've edited to add a serail number in the
tip you've shared.
Thanks again for the contribution...
Re: DB2 Tips.
Posted: Wed Nov 27, 2013 3:18 pm
by Anuj Dhawan
11. Mostly SQL queries are written with little importance to performance tuning - sometimes because the programmer does not know, s/he is putting in a CPU hogger and possibly, at time, they just don't care.
I believe, often, this is not a problem when programs are tested in test environment, where performance is not an issue. However, in production environments inefficient tuned queries might lead to longer execution time resulting in lock escalation, deadlocks etc. By tuning the query, applications can be made to run faster. Keeping this in mind, I'll share some of the general considrations while writing a SQL query:
- If possible, use BETWEEN instead of using <= and >= conditions.
- Use IN instead of LIKE
Re: DB2 Tips.
Posted: Wed Nov 27, 2013 3:20 pm
by Anuj Dhawan
12. DISTINCT and COUNT considerations:
- If you can thisnk other way round, try not to use DISTINCT as it adds to overhead while filtering duplicates.
- Do not use COUNT(*) in application programming. Use COUNT(COL_NAME) or EXISTS if you need to perform existence check.
Re: DB2 Tips.
Posted: Wed Nov 27, 2013 3:22 pm
by Anuj Dhawan
13. NOT IN and ORDER BY considerations:
- It's advised to use "NOT EXISTS" instead of "NOT IN"
- It's better to use ORDER BY clause when sequence is important.
Re: DB2 Tips.
Posted: Wed Nov 27, 2013 3:22 pm
by Anuj Dhawan
14. JOIN and UNION considerations:
- If you can make it work - use JOINs instead of sub queries. At times, a JOIN can be more efficient than a correlated subquery or a subquery using IN.
- If you've got too many OR conditions in the query, use UNION ALL.