sql

Db2 Sql Tuning Tips

List most critical tips from DB2 SQL Tuning Tips for z/OS Developers

  1. Stay Away from Distinct if Possbile Details: group by or in or exists subquery if duplicates due to one-to-many relationship

  2. Try Rewriting Range Predicates as Between Predicates

    1
    2
    WHERE HIREDATE > :HV-DATE
    WHERE HIREDATE BETWEEN :HV-DATE and '9999-12-31'

  3. Use Tools for Monitoring Details: Some of the most common z/OS DB2 monitoring tools are OMEGAMON®, TMon, MainView, Apptune, Insight for DB2, and DB2PM. Some of the most common DB2 LUW monitoring tools are IBM’s Optim™ Query Tuner, Precise for DB2, Quest, DBI Software, Embarcadero’s Performance Analyst, and ITGAIN.

  4. Avoid Discrepancies with Non-Column Expressions

    1
    2
    WHERE EDLEVEL = 123.45 * 12
    WHERE EDLEVEL = SMALLINT(ROUND(123.45*12,0))

  5. Ensure That Subquery Predicates Involving Min and Max Have the Possibility of Nulls Being Returned Handled

  6. Always Code For Fetch Only or For Read Only with Cursor Processing When a Query Is Only Selecting Data

  7. Avoid Selecting a Row from a Table to Help Decide Whether the Logic in the Code Should Execute an Update or an Insert

  8. Use the DB2 V9 MERGE Statement

  9. Keep Table and Index Files Healthy and Organized Details: SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS tables

  10. Consider OLTP Front-End Processing

    1
    OPTIMIZE FOR 14 ROWS

  11. Take Advantage of Materialized Query Tables to Improve Response Time (Dynamic SQL Only)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    CREATE TABLE DEPT_SUM_MQT_TABLE AS
    (SELECT  WORKDEPT, AVG(SALARY) AS AVG_SAL,
    AVG(BONUS) AS AVG_BONUS
    AVG(COMM)  AS AVG_COMM,
    SUM(SALARY) AS SUM_SAL,
    SUM(BONUS) AS SUM_BONUS
    SUM(COMM)  AS SUM_COMM
    FROM EMP
    GROUP BY  WORKDEPT)
    DATA INITIALLY DEFERRED REFRESH DEFERRED
    MAINTAINED BY SYSTEM
    ENABLE QUERY OPTIMIZATION;
    --original query
    SELECT SUM(BONUS)
    FROM EMP
    WHERE  WORKDEPT = 'A00'
    --rewrite by optimize
    SELECT AVG_BONUS
    FROM DEPT_SUM_MQT_TABLE
    WHERE  WORKDEPT = 'A00'

  12. Insert with Select

    1
    2
    3
    4
    5
    6
    SELECT EMPNO, LASTNAME, MIDINIT, FIRSTNME,
    DEPTNO, SALARY, BONUS, COMM
    FROM FINAL TABLE
    (INSERT   (EMPNO, LASTNAME, MIDINIT, FIRSTNME, DEPTNO)
    VALUES ('123456', 'SMITH', 'A', 'JOE', 'A00')
    )

  13. Identify Times for Volatile Tables Details: Using the VOLATILE keyword on the Create or Alter Table statement identifies tables whose data volumes fluctuate.

    1
    2
    3
    4
    CREATE TABLE EMP_TEMP
     (EMPNO    CHAR(6)  Not Null,
       LASTNAME VARCHAR(15)  Not Null )
       VOLATILE

  14. Use the ON COMMIT DROP Enhancement

    1
    2
    3
    4
    5
    6
    7
    8
    DECLARE GLOBAL TEMPORARY TABLE
    SESSION.EMP_TEMP1  (COL1 CHAR (20)
    COL2 CHAR (10)
    COL3 SMALLINT )
    ON COMMIT DROP TABLE
    ON COMMIT DELETE ROWS
    ON COMMIT PRESERVE ROWS

  15. Use Multiple Distincts Details: This is an automatic performance gain that alleviates the need for separate calls to DB2.

    1
    2
    3
    SELECT SUM (DISTINCT SALARY),
         AVG(DISTINCT BONUS)
         FROM EMP;

  16. Take Advantage of Backward Index Scanning Details: For each column that is in the Order By clause, the ordering that is specified in the index has to be exactly the opposite of the ordering that is specified in the Order By clause.

  17. Set Your Clustering Index Correctly

  18. Take Advantage of DB2 V8 Enhanced DISCARD Capabilities When It Comes to Mass Deletes

  19. Consider Parallelism https://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.perf/src/tpc/db2z_enableparallelprocess.dita

  20. Consider Direct Row Access Using ROWID Datatype (V8) or RID Function (V9) Details: If the ROWID value is then used in the search condition of subsequent updates or deletes, DB2 may navigate directly to the row, bypassing any index processing or tablespace scans.

    1
    EMP_ID     ROWID    NOT NULL GENERATED ALWAYS

  21. Specify the Leading Index Columns in WHERE Clauses Details: For a composite index, the following query would use the index and execute matching processing as long as the leading column of the index is specified in the WHERE clause.

  22. Keep in Mind Index Only Processing Whenever Possible Many times, developers code queries that need to execute one of the four column aggregate functions (Sum, Avg, Min, or Max). They can get some great results by including an extra column as part of an index. Take, for example, the following query and associated index, Index1 = (YEAR, PERIOD): 

    1
    2
    3
    SELECT SUM(JRNL_AMT)
    FROM LEDGER_TABLE
    WHERE YEAR = 2008 AND PERIOD IN (1,2,3)
    DB2 is good at recognizing the JRNL_AMT column in the index, even though there is no specific predicate on it, and it will choose index only processing.

  23. Take Advantage of DB2 V9 Optimistic Locking

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE EMP
     (EMPNO  CHAR(6)  NOT NULL,
       ....
       .....
       EMP_UPD  TIMESTAMP NOT NULL
       GENERATED BY DEFAULT
       FOR EACH ROW ON UPDATE
       AS ROW CHANGE TIMESTAMP)
    )

  24. Use the DB2 V9 MERGE Statement https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0010873.html

  25. Code Boolean Term Predicates Whenever Possible Details: A Boolean term predicate is a predicate that is connected by And logic, so when one predicate is evaluated as false for a row, it then makes the entire Where clause evaluate to false.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT EMPNO, LASTNAME, SALARY
    FROM EMP
    WHERE (LASTNAME > 'SMITH')
    OR (LASTNAME = 'SMITH' and FIRSTNME > 'BOB')
    SELECT EMPNO, LASTNAME, SALARY
    FROM EMP
    WHERE  LASTNAME >= 'SMITH'
      AND (LASTNAME >  'SMITH'   OR
          (LASTNAME = 'SMITH' AND FIRSTNME > 'BOB'))

  26. Avoid Sorts with Order By Details: DB2 may avoid sorts if there are leading columns in an index to match the Order By or if the query contains an equal predicate on the leading column(s) of an index.

  27. Watch Out for Case Logic 

    1
    2
    3
    4
    5
    6
    SELECT EMPNO, LASTNAME, SALARY,
           CASE WHEN SALARY < ...
                WHEN SALARY > ...
           ELSE
           END
    FROM EMP
    Details: If most of the rows have a Salary < 50000.00, then that piece of logic should be coded first.

  28. Know Your Version of DB2

    1
    2
    SELECT GETVARIABLE('SYSIBM.VERSION')
    FROM SYSIBM.SYSDUMMY1

  29. If You Need True Uniqueness, Try the V8 Generate_Unique Function

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE TABLE ORDER_TBL
     (ORDER_ID        CHAR(13) FOR BIT DATA,
      ORDER_DATE      DATE,
      ORDER_LOC       CHAR(03) );
    INSERT INTO ORDER_TBL
      VALUES(GENERATE_UNIQUE(), CURRENT DATE, 'NY');
    SELECT TIMESTAMP(ORDER_ID), ORDER DATE, ....
    FROM ORDER_TBL;

  30. Update and Delete with Select (V9) Details: The words Old Table are SQL DB2 reserved words that need to be coded in order for this statement to work.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT SALARY, BONUS
    FROM OLD TABLE
     (UPDATE EMP
        SET SALARY = :HV1-SALARY,
        BONUS = :HV2-BONUS
        WHERE EMPNO = :HV-EMPNO)
    SELECT LASTNAME, FIRSTNME, WORKDEPT
    FROM OLD TABLE
      (DELETE FROM EMP
       WHERE EMPNO = :HV-EMPNO)

  31. Avoid Unnecessary Sorting Details: Data sorting can be caused by Group By, Order By, Distinct, Union, Intersect, Except, and Join processing.

List most critical tips of store procedure related from DB2 SQL Tuning Tips for z/OS Developers

Mongo Db Crud Summary

This post aims to list the basic operation of mongo database in the mongodb shell prompt.

1
2
w, {Number/String, > -1 || ‘majority’ || tag name} the write concern for the operation where < 1 is no acknowlegement of write and w >= 1, w = ‘majority’ or tag acknowledges the write
j, (Boolean, default:false) write waits for journal sync before returning

system admin

1
2
3
4
5
mongod --fork --logpath /root/mongodb/log/mongodb.log
mongod —shutdown
mongo hostname:port/dbschema -u username -p password
db.serverStatus()
db.serverStatus().connections

Read More

Sql Sybase Ase Alternative Way to Support Lead/Lag Function

There is audit trial requirement that need to be captured into log table these day. But we suffer from the limited function provided by Sybase ASE DB in order to achieve our goals. SybaseASE 12.5 does not support neither analytic function nor row number column, and therefore we have to implement cross comparison by ourselves via basic semantics.

1
2
3
4
5
6
7
8
9
10
11
--create rownum column
select rownum = identity(10), * from #temp_table from table where 1=2
insert into #temp_table
select * from table where pid = @pid
insert into #temp_table
select * from table_history where pid = @pid order by audit_update_date desc
--use self-join to match the cross rows
select a.rownum,
		case when a.col1 = b.col1 then 'equal' else 'inequal' end
from #temp_table a left out join #temp_table b on ( a.pid = b.pid and (a.rownum + 1) = b.rownum )
where ...

In contrast, we also provide oracle version of implementation.

Sql Switch/Transpose Columns and Rows

In practice, we will meet the requirement to rotate the table including convert cols to rows, convert rows to cols. In this session, I'll produce the solution in common method as well as db-specified features.

Convert columns to rows

1
2
3
4
5
6
7
8
9
10
--common solution
select * from (
	select id, 'col_1' as col_name, col_1 as col_val
	from table
	union all
	select id, 'col_2' as col_name, col_2 as col_val
	from table
	union all
	... 
) order by id
1
2
3
4
5
6
7
--oracle 11g unpivot query
select *
from table
unpivot (
	col_val for col_name in (col_1 as 'col_1', col_2 as 'col_2')
)
order by id

Concert rows to columns

1
2
3
4
5
6
7
--common solution
select id,
	max(case when col_name = 'col_1' then col_val else null end) as col_1,
	max(case when col_name = 'col_2' then col_val else null end) as col_2,
	...
from table
order by id
1
2
3
4
5
6
7
8
--oracle 11g pivot query
select *
from table
pivot (
	max(col_val) for col_name in ('col_1' as col_1,'col_2' as col_2)
)
where ...
order by id

Excel pivot table

For non-sql approaches, we can use excel pivot table. Excel spreadsheets are a great way to pivot and analyze Oracle data, and tools.