DB2 Tips.

[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’.

[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.

[Anuj Dhawan]

3. If you're using CURSOR for "read only", use FOR FETCH ONLY in the SQL.

[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

[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.

[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’

[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

[Anuj Dhawan]

Thanks LearnMainframe, much appreciate your contribution.

Regards,

[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 

[Anuj Dhawan]

Thanks Bill - thanks for bringing the error to attention, the code is corrected now. Hope it looks good now.

Regards,

[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.

[AD991]

This is a nice collection, well done. Thanks for sharing them.

[Anuj Dhawan]

Thanks for the feedback. Feedback(s) always helps to keep you on track. Hopefully they are helpful, one way or other.

Regards,

[AD991]

It's my pleasure Anuj.

[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>

[Anuj Dhawan]

Thanks Pk,, I've edited to add a serail number in the tip you've shared.

Thanks again for the contribution...

[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:

1. If possible, use BETWEEN instead of using <= and >= conditions.
2. Use IN instead of LIKE

[Anuj Dhawan]

12. DISTINCT and COUNT considerations:

1. If you can thisnk other way round, try not to use DISTINCT as it adds to overhead while filtering duplicates.
2. Do not use COUNT(*) in application programming. Use COUNT(COL_NAME) or EXISTS if you need to perform existence check.

[Anuj Dhawan]

13. NOT IN and ORDER BY considerations:

1. It's advised to use "NOT EXISTS" instead of "NOT IN"
2. It's better to use ORDER BY clause when sequence is important.

[Anuj Dhawan]

14. JOIN and UNION considerations:

1. 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.
2. If you've got too many OR conditions in the query, use UNION ALL.