DB2 함수 정리

출처 : http://www.zetswing.com/bbs/board.php?bo_table=dbms_02&wr_id=16 

- 상단의 ROW를 가져오는 함수

SELECT * FROM maxapps FETCH FIRST 5 ROWS ONLY;

-SELECT APP, DESCRIPTION, APPTYPE FROM MAXAPPS

WHERE MAXAPPSID = 105

WITH UR; 

-SELECT SUBSTR(TABLENAME, 1,2) FROM MAXTABLE FETCH FIRST 5 ROWS ONLY;

-|| 문자연결기능 :  

SELECT COALESCE(DOSE,'1') || MAXTABLE FROM MAXTABLE FETCH FIRST 5 ROWS ONLY;

-?

select TABLENAME, CASE WHEN MAXTABLEID BETWEEN '100' AND '200' THEN '10'

WHEN MAXTABLE BETWEEN '201' AND '300' THEN '20'

WHEN MAXTABLE BETWEEN '301' AND '400' THEN '30'

FROM MAXTABLE;

- 정수형 

SELECT INTEGER(MAXTABLEID) FROM MAXTABLE FETCH FIRST 5 ROWS ONLY;

- 실수형

SELECT DOUBLE(MAXTABLEID) FROM MAXTABLE FETCH FIRST 5 ROWS ONLY;

- SUBSTR(대상, 시작위치, 자르고 싶은 길이)

SELECT SUBSTR(MAXTABLEID, 1,3) FROM MAXTABLE FETCH FIRST 5 ROWS ONLY;

-YEAR 구하기

--YEAR --2015

SELECT YEAR(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

--YEAR --2015

SELECT YEAR('2015-08-04') FROM SYSIBM.SYSDUMMY1 WITH UR;

--2015

SELECT YEAR(CURRENT DATE) FROM SYSIBM.SYSDUMMY1 WITH UR;

-MONTH

SELECT MONTH(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1 WITH UR;

SELECT MONTH('2015-08-04') FROM SYSIBM.SYSDUMMY1 WITH UR;

-DAY

SELECT DAY(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1 WITH UR;

SELECT DAY('2015-08-04') FROM SYSIBM.SYSDUMMY1 WITH UR;

--WEEK

SELECT WEEK(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1 WITH UR;

SELECT WEEK('2015-09-04') FROM SYSIBM.SYSDUMMY1 WITH UR;

-현재 시간

SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1 WITH UR;

-DAYOFYEAR 구하기/오늘이 365일중 몇번째 날짜

SELECT DAYOFYEAR(CURRENT DATE) FROM SYSIBM.SYSDUMMY1 WITH UR;

-DAYNAME 구하기(요일 이름)

SELECT DAYNAME(CURRENT DATE) FROM SYSIBM.SYSDUMMY1 WITH UR;

--CONCAT 문자연결함수 --411489DF

SELECT CONCAT('4114', '89DF') FROM SYSIBM.SYSDUMMY1 WITH UR;

-MOD 나머지 함수

SELECT MOD(250000, 4) FROM SYSIBM.SYSDUMMY1 WITH UR;

-현재 정확한 날짜

--SHOW CORRECT DATE

SELECT VALUE(CURRENT DATE, '2018-08-03') FROM SYSIBM.SYSDUMMY1 WITH UR;

-절대값

SELECT ABS(-456) FROM SYSIBM.SYSDUMMY1 WITH UR;

-대문자로 (UCASE)/ 소문자로(LCASE) 

SELECT UCASE('abl') FROM SYSIBM.SYSDUMMY1 WITH UR;

SELECT LCASE('ASB') FROM SYSIBM.SYSDUMMY1 WITH UR;

-MULTIPLY_ALT 두 수를 곱한다.

SELECT MULTIPLY_ALT(5,40) FROM SYSIBM.SYSDUMMY1 WITH UR;

-ROUND 자리수 버림

SELECT ROUND(123.123, 2), ROUND(123.123, 1), ROUND(123.123, 0), ROUND(123.123, -1)

, ROUND(123.123, -2), ROUND(123.123, -3), ROUND(123.123, -4) FROM SYSIBM.SYSDUMMY1 WITH UR;

-WEEK _iso 함수 : 지금이 몇 째주 인지 알려주는 함수.

select week_iso(current date) from sysibm.sysdummy1 with ur;

select week_iso('2015-08-04') from sysibm.sysdummy1 with ur;

--?

SELECT *  FROM ( SELECT ROWNUMBER() OVER() AS rownum, statement_text from explain_statement) AS T

where T.rownum = 2 fetch first 100 rows only;

- WITH UR

WITH UR을 사용할 경우 Commit이 안된 Dirty Data가 읽혀질 수가 있으므로

금액 Update를 위한 SELECT 문이나 중요한 정보를 Update하기 위한 SELECT

문에는 WITH UR을 절대 사용하시면 안됩니다.그러면 무엇을 쓰는지 출처에가서 물어보자!

해당 프로그램이 조회성 프로그램일 경우에는 모든 SELECT문에 "WITH UR" Option을

기술하도록 합니다.

 

UR(언커미트 읽기)

1) 조회작업 동안 읽어들인 모든 행을 다른 응용프로그램 프로세스가 변경 가능하도록

합니다.

 

2) 해당 응용프로그램 프로세스에서 변경사항을 커미트하지 않은 경우에도 다른 응용프로그

램 프로세스가 커밋하기 전의 모든 행을 읽어들이도록 합니다.

3) 예를들어 삽입 트랜잭션 완료전인 원장을 조회할때 WITH UR를 사용하면 삽입하고 있는

테이블을 삽입된 시점까지 데이터를 조회 할수가 있습니다.

하지만 WITH UR을 빼고 조회하면 트랜잭션이 완료될때까지 대기한다.

 

※ IBM에서는 조회시에 가능한 WITH UR 이나 FOR READ ONLY WITH UR를

쓰는것을권장합니다.

 

'WITH UR'은 isolation(격리성) level의 지정방법중 하나입니다.

SELECT 문에서 WITH UR은 다른 유저(Connection)에서 변경중인 데이타를 읽을 수

있습니다.

오라클에서만 개발을 한 개발자들은 이를 모르고 문제를 발생시키는 application개발을

하는 경우가 있습니다.

예를들어 크기가 크고 변경이 매우 빈번한 중요 테이블에 full scan select를 한 경우 이 SQL

이 끝날 때까지 테이블에 lock이 발생하면서 대기하는 Application들이 많이 발생하게 됩니다.

이를 방지하기 위해 UR isolation을 사용합니다. 말그대로 commit이 발생하지 않아도 당시의

데이터를 읽어 오겠다는 겁니다.

※ 오라클은 조회시 해당 row에 대한 변경을 할 수가 있지만 DB2는 조회시 해당 row에

대한 변경을 할수 없습니다.

※ 오라클은 해당 row에 대한 변경을 할때 조회를 할수 있지만 DB2는 row에 대한 변경을

할때 조회를 할수 없습니다.

※ 'FOR READ ONLY WITH UR'은 SELECT 쿼리에만 사용하며 INSERT, UPDATE,

DELETE 쿼리에는 사용하지 않습니다.

※ 오라클은 RBS라는 곳에 작업 대상 데이터를 따로 보관하기 때문에 데이터의 변경과는

무관하게 조회작업을 할 수 있는 것으로 알고 있습니다.

조회 쿼리의 맨 끝에 WITH UR을 붙히면 조회 테이블이 현재 트랜잭션이 진행중일때 대기하지 않고 실시간 변경중인 데이타를 가져올수 있게 되지만 신뢰성은 떨어진다.

조회 쿼리의 맨끝에 WITH UR을 안붙히면 조회 테이블이 현재 트랜잭션이 진행중일때 대기하고 있다가 해당 트랜잭션이 커밋 처리되면 그때 데이타를 가져온다.

조회 쿼리의 맨끝에 WITH UR을 안붙히면 조회 테이블이 현재 트랜잭션이 진행중일때 대기하고 있다가 해당 트랜재션을 종료하지 않고 떨어져나가면 조회쿼리는 타임아웃에 의해 정지되고 "현재의 트랜잭션이 교착상태 또는 시간종료에 인해 롤백되었습니다." 메시지를 출력한다.

이때 해당 테이블은 트랜잭션 주체가 커밋 처리를 해주지 않았기 때문에 풀어주기 전까지는 계쏙 락상태를 유지한다.

테이블에 트랜잭션중일때 커밋을 안때리고 죽어버리면 해당 테이블은 락이 걸려버린다.

테이블 락은 테이블설계가 잘되어 있다면 발생하지 않는다.

락이 자주 생기는건 테이블 설계에 문제가 있는것이다.

WITH UR을 안 붙인것은 해당 원장의 트랜잭션이 진행일땐 자체 테이블락이 걸려 대기상태로 있다가 커밋처리를 하게된후에 해당원장을

조회하게 된다.

락이 걸린 테이블은 다른 세션에서 조회쿼리에 FOR READ ONLY with ur을 붙여도 조회는 가능하다.

락이 걸린 테이블은 insert, update, delete, import 가 불가능하다

※ 락 걸린 테이블은 "현재의 트랜잭션이 교착 상태 또는 시간종료로 인해 롤백되었습니다." 에러 메시지 출력

락 테이블은 트랜잭션을 사용하는 dbms에서만 걸린다.

처리성격에 따라서 WITH UR의 사용을 달리 할 필요가 있다. 왜냐하면 아래현상이 발생

할수도 있기 때문이다.