Tuesday, April 21, 2015

How do you enable reclaimable storage or the lower high water mark after a 9.5 migration?


The high water mark (HWM) refers to the page number of the first page in the extent following the last allocated extent. For example, if a table space has 1000 pages and an extent size of 10, there are 100 extents. If the 42nd extent is the highest allocated extent in the table space that means that the high-water mark is 420.

Reclaimable storage is a feature of nontemporary automatic storage and DMS table spaces in DB2® V9.7. You can use it to consolidate in-use extents below the high water mark and return unused extents in your table space to the system for reuse.

With table spaces created before DB2 V9.7, the only way to release storage to the system was to drop containers, or reduce the size of containers by eliminating unused extents above the high water mark. There was no direct mechanism for lowering the high water mark. It could be lowered by unloading and reloading data into an empty table space, or through indirect operations, like performing table and index reorganizations. With this last approach, it might have been that the high water mark could still not be lowered, even though there were free extents below it.
To enable the inplace reduce HWM after a 9.5 to 9.7 migration the tablespaces must be dropped and recreated with a new tablespace created using DB2 9.7. Once this is done the following command can be used to lower the HWM.


ALTER TABLESPACE TS LOWER HIGH WATER MARK ALTER TABLESPACE REDUCE with the database-container-clause or all-containers-clause for non-automatic storage table spaces.

Saturday, November 8, 2014

Reorg History on DB2

How to get history of reorg on a particular table

SELECT
        smallint(DBPARTITIONNUM) as PARTITION,
        CASE OPERATIONTYPE
         WHEN 'F' THEN 'OFFLINE'
         WHEN 'N' THEN 'ONLINE'
        END as REORG_TYPE,
        substr(char(TIMESTAMP(START_TIME)),1,19) AS START_TIME,
        substr(char(TIMESTAMP(END_TIME)),1,19) AS END_TIME,
        TIMESTAMPDIFF(4,CHAR(TIMESTAMP(END_TIME)-TIMESTAMP(START_TIME))) as "ELAPSED(MIN)",
        coalesce(SQLCODE,0) as SQLCODE,
        CASE WHEN SQLCODE < 0  THEN 'FAILED' ELSE 'SUCCEEDED' END as STATUS
        FROM
          sysibmadm.db_history as a
          WHERE OPERATION = 'G'
            AND (TIMESTAMP(START_TIME) + 5 DAYS) >
                CURRENT TIMESTAMP
            AND TABSCHEMA = 'SCHEMA'
            AND TABNAME = 'TAB'
          ORDER BY START_TIME, PARTITION
        for fetch only with ur;

How to get status of any utility ( reorg,backup,load) running on DB2.

How to get status of any utility ( reorg,backup,load) running on DB2.

SELECT
          substr(char(UTILITY_START_TIME),1,19) as start_time /*==> TIMESTAMP 10,0 */
          ,u.DBPARTITIONNUM as dp#
          ,substr(u.UTILITY_TYPE,1,12) as UTILITY_TYPE
          ,u.UTILITY_STATE /*==> VARCHAR 16,0 */
          ,char(PROGRESS_DESCRIPTION,10) as prog /*==> VARCHAR 2048,0 */
          ,PROGRESS_TOTAL_UNITS  as tot_units /*==> BIGINT 8,0 */
          ,PROGRESS_COMPLETED_UNITS as tot_comp_units /*==> BIGINT 8,0 */
          ,TIMESTAMPDIFF(4,CHAR(TIMESTAMP(current timestamp)-TIMESTAMP(PROGRESS_START_TIME))) AS elp_min
          ,substr(UTILITY_DESCRIPTION,1,120) as desc
        FROM SYSIBMADM.SNAPUTIL u
        LEFT OUTER JOIN SYSIBMADM.SNAPUTIL_PROGRESS up
        on u.UTILITY_ID = up.UTILITY_ID
        and u.DBPARTITIONNUM = up.DBPARTITIONNUM
        order by u.DBPARTITIONNUM, u.UTILITY_TYPE, u.UTILITY_STATE
        for fetch only with ur

Thursday, August 1, 2013

Qry Tuning Tips

1) E.g. Table1 has 1000 rows and Table2 has 1 row.
 Select * from table1, table2 will give faster result than Select * from table2, table1

2) If 3 tables are joined, select the intersection table as the driving table. Intersection table is the table that has many tables dependent on it.

3) Joins: Table joins should be written first before any condition in the Where clause. The condition that filters the max records should be at the end below the joins. Parsing is done from BOTTOM to TOP.

4) Avoid using Select * from

5) Use DECODE to improve performance. (DECODE is like COALESCE)

6) Count(*) is faster than Count(1). Count(pkey) is the fastest though.

7) Restrict the records with WHERE clause instead of using HAVING clause.

8) Minimize Table lookup in Query:
 e.g. Select *  from tab1
 where col1 = (select col1 from tab2 where colx = 3)
 and col2 = (select col2 from tab2 where colx = 4)
 an efficient way to this is:
 Select * from tab1
 where (col1, col2) = (select col1, col2 from tab2 where col2 = 3)
 The same approach can be used for updates.

9) Use Exists clause instead of In clause and Not Exists instead of Not In clause

10) Use Exists in place of Distinct clause
 E.g. Select Distinct a.col1, a.col2 From tab1 a, tab2 b
 Where a.col3 = b.col3
 Instead the query can be written as:
 Select a.col1, a.col2
 From tab1 a
 Where Exists (select 'X' from tab2 b
 Where a.col3 = b.col3)

 11) Use Explain Plan to get the query execution process.

 12) Use Indexes for faster retrieval of data.

 13) Index will be used if the query has the column on which the index is created. If the columns that are not present in the index are selected, the index is not used.

 14) Avoid use of UNION clause as far as possible

 15) Avoid Is Null and Is Not Null on indexed columns

 16) Using Hints helps in performance improvement

 17) Avoid typecasting of indexed columns

 18) Not, !=, <, || will disable use of Indexes

 19) Arithmetic operations in the Where clause will disable indexes

 20) Use OR clause instead of In clause
 e.g. Select * from tab where col1 in ('a','b')
 instead use: Select * from tab where col1 = 'a' or col1 = 'b'

 21) Avoid unnecessary use of Union, Distinct, minus, intersect, order by and group by

22) DISTINCT - always results in a sort
      UNION - always results in a sort
      UNION ALL - does not sort, but retains any duplicates

23) ORDER BY
may be faster if columns are indexed
use it to guarantee the sequence of the data

24) GROUP BY
specify only columns that need to be grouped
may be faster if the columns are indexed
do not include extra columns in SELECT list or GROUP BY because DB2 must sort the rows

24) Create indexes for columns you frequently:
ORDER BY
GROUP BY (better than a DISTINCT)
SELECT DISTINCT
JOIN

25) When the results of a join must be sorted -
limiting the ORDER BY to columns of a single table can avoid a sort
specifying columns from multiple tables causes a sort

 26) Favor coding explicit INNER and LEFT OUT joins over RIGHT OUTER joins
EXPLAIN converts RIGHT to LEFT join


27)BETWEEN is usually more efficient than <= predicate and the >= predicate . Except when comparing a host variable to 2 columns

28) Avoid the % or the _ at the beginning because it prevents DB2 from using a matching index and may cause a scan. Use the % or the _ at the end to encourage index usage

29) For Subquery - when using negation logic:
Use NOT Exists  (DB2 tests non-existence)
Instead of NOT IN (DB2 must materialize the complete result set)

30) Use EXISTS to test for a condition and get a True or False returned by DB2 and not return any rows to the query

31) After the indexes, place the predicate that will eliminate the greatest number of rows first

32) Hi,

Check this points...It may help U.

1)         Avoid distinct where ever possible. Check whether distinct is
required or not. No distinct when PK or UK are retrieved.

2)         One can consider usage of union where OR condition exits &
eliminate distincts.

3)         Conditions which are likely to fail should be kept first in a set
of conditions separated by AND.

4)         Always use aliases.

5)         Do not involve columns in an expression.
      select * from emp where salary/12 >= 4000;
The query should be:
select * from emp where salary >= 4000 * 12;
i.e. Avoid using Arithmetic within SQL statements.Arithmetic in a SQL
statement will cause DB2 to avoid the use of an index.

6)         Try to avoid usage of in-built or user defined functions.
select * from employee where substr(name,1,1) = 'G';
The query should be:
select * from employee where name = 'G%';

7)         Avoid datatype mismatch since it may lead to implicit/explicit
casting.
select * from emp where sal = '1000';
The query should be:
select * from emp where sal = 1000;

8)         Substitute unnecessary group by & having with where clause.
select avg(salary) as avgsalary, dept from employee group by dept
having dept = 'information systems';
The query should be:
select avg(salary) as avgsalary, dept from employee where dept =
'information systems';






Tuesday, July 9, 2013

Merge Command eg

 MERGE INTO LIQUIDITY.REP_STG_PRODUCT_DATA STG USING
          (Select VALID_TO ,PRODUCT FROM LIQUIDITY.REP_PRODUCT_DATA SCD WHERE SCD.VALID_TO > ? AND SCD.VALID_FROM <=?  ) ad
          on STG.PRODUCT =ad.PRODUCT
 WHEN MATCHED
  THEN
  UPDATE SET (STG.PROCESSED,STG.VALID_TO)=('D',ad.VALID_TO)

  ELSE IGNORE;
  --


MERGE INTO LMFR_REPORT.REF_PRODUCT_DATA  R
 USING
          (SELECT    ms.VALUE2 , fp.SK_FP
          FROM LMFR_REPORT.R_REF_FUNDING_POOL fp
                    inner join LIQUIDITY.LIST_MEMBER_SNAPSHOT ms
                    on ms.LIST_ID = 43 and ms.VALUE1 = fp.SECURITY_POOL and ms.DATA_DATE = '2013-01-31'
) ad
       
                 on ad.SK_FP = R.SK_FP   AND R.VALID_FROM <= '2013-01-31'  AND R.VALID_TO > '2013-01-31'
       AND R.FED_CATEGORY != ms.VALUE2
                  AND ((R.FED_CATEGORY = 'A-II' AND VALUE2='A-I')
                  OR R.FED_CATEGORY IN ('B','D'))
                   AND R.PRODUCT_S_P_LT_RATING IS NULL
                   AND R.PRODUCT_MOODY_LT_RATING IS NULL
                   AND R.PRODUCT_S_P_LT_RATING IS NULL
                   AND R.PRODUCT_HIERARCHY_LEVEL_2 in ( 'DO NOT POST', 'Government')
 WHEN MATCHED
  THEN
  UPDATE SET (R.FED_CATEGORY, R.BASEL_CATEGORY)=(AD.VALUE2, CASE WHEN AD.VALUE2= 'A-I' THEN '0% risk - issued by sovereigns'
WHEN AD.VALUE2='A-II' THEN '20% risk - issued by sovereigns' ELSE 'greater 20% risk - issued by sovereigns' END )
ELSE IGNORE;

Shell Script tips

====================
How to get execution time bw 2 statements in a shell script:
====================
t1=$( perl -e "print time")
sleep 10
t2=$( perl -e "print time")
timeConsumed=$(($t2-t1))

echo "time taken : $timeConsumed"



====================
find and delete files older then 10 days
====================
find subdebt*.dat.Z -mtime +00 -exec ls -lrt  {} \;
find *.* -mtime +10 -exec rm -f {} \;


====================
use of awk command
====================
tabname=ENH_BAAC
rows_deleted=10323244
enh_age=56
echo TABLE_NAME ROWS_DELETED  ENH_AGE | awk '{printf "%-30s %-20s %-20s \n",$1,$2,$3 }' >test
echo $tabname $rows_deleted $enh_age | awk '{printf "%-30s %-20s %-20s \n",$1,$2,$3 }' >>test
tabname=ENH_LETTERS_OF_CREDIT
rows_deleted=0
enh_age=98
echo $tabname $rows_deleted $enh_age | awk '{printf "%-30s %-20s %-20s \n",$1,$2,$3 }' >>test
cat test


====================
Extension of file
====================
ls  /v/region/na/appl/corptsy/lmfr/data/qa/sourced/brm_repo/History/20120730.20120803.174759.dat.Z | read file
echo ${file##*.}

file="thisfile.yogesh.txt"
echo "filename: ${file%.*}"
echo "extension: ${file##*.}"

====================
For loop
====================
for  i in 2 3 4 5 6
do
  export DB2NODE=$i
  db2 terminate
  db3 connect to nypd_lmfr
  db2 load query table liquidity.ETL_UK_MORtGAGES

done

====================
If Else Stmt
====================
PREV_TABNAME=dummy1
if [ "${PREV_TABNAME}" != "dummy" ]; then
    echo $PREV_TABNAME
else 
    echo "incor"
fi
====================
Cut Command: to get 4th col with delimeter as |
====================
cut -d "|" -f-4 test



How to get explain plan

Below qry will give you explain plan for a qry:
Make sure the "Org_SL" file has qry and has semi colon at the end.

db2expln -d DBNAME -f Org_SL -z \; -t -g -o Org_SL.out

Below qry will give you explain plan for a procedure.
db2expln -d DBNAME -package P6472415 -schema SCHEMANAME -g -o PROC.out