DB2 In Built Functions - Column function and Scalar Function

Column functions
1.Column functions can be executed in SELECT Statement
2. Each Column function returns only one value for the set of rows
3.If we apply a column function to one column in a select Statement,we must apply column functions to all the columns specified in the same select statement, unless the GROUP BY clause is used.
4. Result of the column function will have the same data type like the column on which it was applied.
5. Column function will never return a SQLCODE +100. It returns NULL value if the where clause does not fetches any data. 
But COUNT and COUNT_BIG function returns value, not NULL value. 
So lets look into the below query which theoretically is doing the same thing, but results will be different.
Thus AVG(SALARY)   and   SUM(SALARY)/ COUNT(*)  will be different.
The reason being, count function takes all the rows into account, whereas SUM function ignores the rows that has null values.

The COUNT function and COUNT_BIG function: The COUNT_BIG function is same as COUNT function except that it returns a decimal value.
Ex: select COUNT(*) from EMP_TBL   or Select COUNT_BIG(*) from EMP_TBL

The MAX and MIN functions: MAX will give the largest value in the expression and MIN will give the smallest one.The Result is of the same data type on which it is applied.
It can not be applied on data types like CLOB, BLOB, DBCLOB

STDDEV function returns the standard deviation of  a set of number
ex:  select stddev(salary)  from emp where dept='cse'

SUM function gives the total of values specified in the expression.
ex:  select SUM(SALARY+BONUS) FROM EMP  where month='Oct'

Scalar  functions :
Scalar functions operate on single value as compared to column function.
Below are the Queries along with their output for few scalar functions.

SELECT LEFT('THIS IS VALUE',2) FROM SYSIBM.SYSDUMMY1;
TH
                                                           
SELECT (RAND()*100) FROM SYSIBM.SYSDUMMY1;
+0.3388811137242620E+02

SELECT LOCATE('S','INDIA IS GREAT') FROM SYSIBM.SYSDUMMY1;
 8  
Returns the position of first occurrence of 'S' in the string

SELECT NEXT_DAY(CURRENT DATE,'FRIDAY') FROM SYSIBM.SYSDUMMY1;
2013-11-15-00.00.00.000000

SELECT REPLACE('BATATA','TA','NA')  FROM SYSIBM.SYSDUMMY1;
BANANA

SELECT DATE('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013-11-15

SELECT WEEK('2013-12-25-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
52
SELECT YEAR('2013-12-25-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013

SELECT DAY('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
15 

SELECT LAST_DAY('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013-11-30


SELECT MONTH('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
11

SELECT TIME('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
00.00.00

SELECT HOUR('2013-11-15-23.22.21.056788') FROM SYSIBM.SYSDUMMY1;
23

SELECT SECOND('2013-11-11-00.00.13.0000') FROM  SYSIBM.SYSDUMMY1
13

SELECT MICROSECOND('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
889924 

SELECT JULIAN_DAY('2013-11-15') FROM SYSIBM.SYSDUMMY1;
2456612

SELECT DAYOFWEEK('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
6 
Represents the day of the week.Like 1 for Sunday,2 Monday

SELECT DAYOFYEAR('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
319 

SELECT GRAPHIC('319') FROM SYSIBM.SYSDUMMY1;
.â3â1â9.

SELECT LCASE('JIM') FROM SYSIBM.SYSDUMMY1;
jim

SELECT LTRIM('     JIM') FROM SYSIBM.SYSDUMMY1;
JIM
Trims the space from the string.

SELECT HEX('RI5') FROM SYSIBM.SYSDUMMY1;
D9C9F5

SELECT INT(123.90) FROM SYSIBM.SYSDUMMY1;
123 

SELECT CHAR(CURRENT DATE,USA) FROM SYSIBM.SYSDUMMY1
11/11/2013
Displays  the current date in the USA date format 

SELECT CONCAT('CON','CAT') FROM SYSIBM.SYSDUMMY1
CONCAT

SELECT INSERT('JIM  PRAT', 5, 0 ,'NORM') FROM SYSIBM.SYSDUMMY1 
JIM NORM PRAT 
Accepts 4 arguments. Returns the string which contains the 4th argument inserted into the 1st argument at the position specified by the 2rd argument. 3rd arg specifies the number of bytes to delete

SELECT QUARTER('2013-11-11-00.00.00.0000') FROM  SYSIBM.SYSDUMMY1
4
Vaue of 1=1s quarter,2=2nd quarter,3=3rd quarter,4=4th quarter

Lock Escalation and Lock Promotion in DB2

Lock escalation:
 Locks are data structures that indicate which part of the system (row, page, table, tablespace, database, ...) is protected in some way.  DB2 remembers this information in memory. Blocking the whole database all the time when an application accesses a small part of it doesn't make much sense. So if you have an application that deals with a lot of data, it potentially has to have many locks. Those locks accumulate and need space in memory.

So when the list is almost full DB2 does a "lock escalation". That means, many row-level locks held by one transaction on a table are escalated to a single table-level lock, for example. Instead of having 1000s or millions of locks (per row), it is better to have lock on that table , there by relieving the pressure on the lock list.

When lock Escalation Happens:
As the number of locks held by any one application reaches near to MAXLOCKS percentage on the create tablespace statement.(If we remeber we use this paramter on table space DDL statement). Database manager will trigger lock escalation and will continue until the percentage of the lock list held is below the value of MAXLOCKS. Lock escalation also occurs if the LOCKLIST runs out of space.  

How DB2 Handles this scenario:
The database manager determines which locks to escalate by looking through the lock list for the application and finding the table with the most row locks.
Row locks will be replacing with a single table lock. It will keep repeating till MAXLOCKS value is no longer exceeded.

Summary  :
Lock escalation can occur for objects defined with LOCKSIZE ANY, PAGE, or ROW. Value ANY just gives DB2 the choice of what lock to take initially.
If we use LOCKSIZE ANY, LOCKMAX SYSTEM , it means means it uses the number set by the NUMLKTS DSNZPARM.We can turn off lock escalation by setting LOCKMAX to zero.If we choose this approach, be sure the applications that access the objects are committing frequently, and adjust DSNZPARM NUMLKTS to permit more locks to be taken. Otherwise, we risk hitting negative SQL codes when you reach the maximum number of locks.
Lock escalation is thus a way to protect from taking excessive system resources, so if we turn it off, we need to control it.
If we remember the locking modes and the lock sizes, this is how it works:
Page lock and Row lock escalate to Table level and then to Tables space level for Segmented tables space.
But for partioned tables spaces, the page/row lock escalates straight to the Tables space level.
A page and Row lock occurs at the same level.They can not be escalated to each other, ie ROW<-->PAGE

Lock promotion:
Lock promotion is the action of exchanging one lock on a resource for a more restrictive lock on the same resource, held by the same application process. We can say, it is the process of changing the lock mode or lock size to a more restrictive level. See the below example .


Example:An application reads data, which requires an IS lock on a table space. Based on further calculation, the application updates the same data, which requires an IX lock on the table space. The application is said to promote the table space lock from mode IS to mode IX.(ie Intent share to intent Exclusive)
Note:  LOCKSIZE ANY will cause DB2 to do page level locking
Want to refresh about the lock modes and intent lock? check out Locking  again.

Inportant information about DB2 LOCK
1. DSNZPARM starts up the parameters for DB2.
2.The LOCKMAX parameter of the create  or ALTER TABLESPACE can be seen in the
MAXROWS column of the SYSIBM.SYSTABLESPACE.
3. If a single user accumulates more page locks than allowed by DSNZPARM, we get a SQLCODE of -904.(resource unavailable)
4.All the page locks held for data in segmented table space are escalated to table locks.
 All the page locks held for data in partitioned table space are escalated to table space locks.

5. Why do we get SQLCODE -904 ?
NUMLKUS parameter in DSNZPARM defines the threshold for the total number of page locks across all table space that can be held concurrently by a single DB2 application.When any other application tries to attempt lock that cause the application to exceed the NUMLKUS threshold,the application receives SQLCODE -904.