DB2 Tips.
Forum rules
All of these Tips/Tuning-suggestions should be tested your own, at your shop, prior to use in Prod.
All of these Tips/Tuning-suggestions should be tested your own, at your shop, prior to use in Prod.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
DB2 Tips.
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.
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.
- Akshya Chopra
- Registered Member
- Posts: 77
- Joined: Mon May 20, 2013 11:32 pm
Re: DB2 Tips.
Thanks Anuj. Here is another tip, hope this helps:
2. If using CURSOR SQL for read only, use FOR FETCH ONLY in the SQL.
2. If using CURSOR SQL for read only, use FOR FETCH ONLY in the SQL.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
6. If possible, take out any (read it as 'all') Scalar functions coded on columns in predicates.
For example:
Should be coded as:
For example:
Code: Select all
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE YEAR(HIREDATE) = 2013
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.
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.
-
- Registered Member
- Posts: 26
- Joined: Fri Jul 05, 2013 11:52 am
Re: DB2 Tips.
7. On similar lines, one should avoid using all mathematics coded on columns in predicates.
For example:
Should be coded as:
For example:
Code: Select all
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE SALARY * 2.5 > 50000.00
Code: Select all
SELECT EMPNO, LASTNAME
FROM EMPLOYEE
WHERE SALARY > 50000.00 / 2.5
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
Thanks LearnMainframe, much appreciate your contribution.
Regards,
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
8. Avoid using ‘Distinct’, if possible.
For example: This DISTINCT
can be rewritten as:
For example: This DISTINCT
Code: Select all
SELECT DISTINCT A.FIRSTNAME, A.LASTNAME
FROM TABLE A, TABLE B
WHERE A.ID = B.ID_NO
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
Thanks Bill - thanks for bringing the error to attention, the code is corrected now. Hope it looks good now.
Regards,
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
Thanks for the feedback. Feedback(s) always helps to keep you on track. Hopefully they are helpful, one way or other.
Regards,
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.
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.
-
- Global Moderator
- Posts: 588
- Joined: Wed Nov 20, 2013 11:53 am
- Location: Mars
Re: DB2 Tips.
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
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
Thanks Pk,, I've edited to add a serail number in the tip you've shared.
Thanks again for the contribution...
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
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:
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
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
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.
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
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.
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.
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.
- Anuj Dhawan
- Founder
- Posts: 2803
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: DB2 Tips.
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.
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.
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.
Create an account or sign in to join the discussion
You need to be a member in order to post a reply
Create an account
Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute