DB2 Tips.

A Mainframe-Tip a Day keeps the bugs away!
Forum rules
All of these Tips/Tuning-suggestions should be tested your own, at your shop, prior to use in Prod.
User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

DB2 Tips.

Post by Anuj Dhawan » Fri May 24, 2013 12:20 pm

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


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Akshya Chopra
Registered Member
Posts: 56
Joined: Mon May 20, 2013 11:32 pm
Zodiac: Leo

Re: DB2 Tips.

Post by Akshya Chopra » Sat May 25, 2013 1:08 pm

Thanks Anuj. Here is another tip, hope this helps:

2. If using CURSOR SQL for read only, use FOR FETCH ONLY in the SQL.



User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Tue May 28, 2013 8:22 pm

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


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Wed May 29, 2013 11:46 pm

4. How to select the duplicate rows from a table?

Code: Select all

SELECT columns
FROM table
GROUP BY columns
HAVING COUNT(*) > 1


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Tue Jul 16, 2013 6:43 pm

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.


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Sat Sep 21, 2013 4:00 pm

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’


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.


LearnMainframe
Registered Member
Posts: 23
Joined: Fri Jul 05, 2013 11:52 am
Zodiac: Libra

Re: DB2 Tips.

Post by LearnMainframe » Sat Sep 21, 2013 4:12 pm

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



User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Sat Sep 21, 2013 5:30 pm

Thanks LearnMainframe, much appreciate your contribution.

Regards,


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Sat Sep 21, 2013 10:57 pm

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 


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Sun Sep 22, 2013 11:04 am

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

Regards,


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Sun Sep 22, 2013 12:35 pm

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.


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

AD991
Registered Member
Posts: 76
Joined: Wed May 08, 2013 7:36 pm
Zodiac: Sagittarius

Re: DB2 Tips.

Post by AD991 » Sun Sep 22, 2013 5:02 pm

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



User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Mon Sep 23, 2013 8:15 am

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

Regards,


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

AD991
Registered Member
Posts: 76
Joined: Wed May 08, 2013 7:36 pm
Zodiac: Sagittarius

Re: DB2 Tips.

Post by AD991 » Sun Nov 03, 2013 11:36 pm

It's my pleasure Anuj.




zprogrammer
Global Moderator
Global Moderator
Posts: 605
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars
Zodiac: Libra

Re: DB2 Tips.

Post by zprogrammer » Thu Nov 21, 2013 7:02 pm

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>


zprogrammer

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Wed Nov 27, 2013 3:14 pm

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

Thanks again for the contribution... :)


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Wed Nov 27, 2013 3:18 pm

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


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Wed Nov 27, 2013 3:20 pm

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.


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Wed Nov 27, 2013 3:22 pm

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.


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

User avatar

Topic Author
Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: DB2 Tips.

Post by Anuj Dhawan » Wed Nov 27, 2013 3:22 pm

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.


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

Return to “Tip Of the Day.”