#db2 performance tips

DB2 Five key areas to improve Performance

In DB2 performance management is the main role of Senior software developers. The real complex point is where you need to focus. I have given here key ares to improve performance.

Performance reduces CPU used and then your application running cost you can reduce.

 Right Access Path…

Here, how to find right access path.

Access path determined by during bind time

Access path at execution time

Stati…

Real idea Left outer JOIN you always need to code in DB2 read now

The story of JOINS in SQL is to get data from many tables. Actually in DB2 there are both LEFT and RIGHT outer JOINS present.

There is no difference between a left outer join and right outer join other than where the starting driver is coded

LEFT JOIN

SELECT D.DEPTNO, D.DEPTNAME, D.MGRNO, E.LASTNAME FROM DEPT D LEFT JOIN EMP E ON D.MGRNO = E.EMPNO

Right JOIN

SELECT D.DEPTNO, D.DEPTNAME,…

Fetch first 1 Row only real usage in DB2

In many projects, the scenario where you need to find, if the rows exist or not for a particular condition. Performance is the main issue, if you write complex SQL queries. The best and latest approach is using Fetch First 1 row only. When you specify it in your SQL query, the result will be 1 or more than one row. Our aim is to find if the rows exist or not. The below Sample SQL query helps you…

5 More DB2 SQL Performance Tips

START Analytics Career

SELECT from INSERT, UPDATE whenever possible.  This feature is great for getting the values associated with identity, sequence, and any other database column keys while doing an INSERT or UPDATE SQL operation. This capability provides for the capturing of generated key values needed to be returned to the application during an UPDATE or INSERT statement.  Instead of executing…

IBM-DB2 ZOS Questions And Answers

1.Why do we have indexes? A). Indexes can be used to enforce uniqueness of keys. Indexes are mainly created to improve SQL performance

2. What’s the cost of indexes ? A). Indexes consume a lot of disk space (DASD). An index adds ~30% of CPU on INSERTs / DELETEs

3. DB2 v10 new feature?

A).ALTER INDEX IX ADD INCLUDE (COL)

4. What is it good for ?

A). The new INCLUDE feature in DB2 v10 allows you to