DB2 자주 쓰는 함수 모음.

출처 :Tong - nasukjin님의 DB관련통

-기본정보보기
DESCRIBE TABLE table-name [show detail]
DESCRIBE SELECT * FROM tablename;

 

-인덱스 정보보기
DESCRIBE INDEXES FOR TABLE table-name [show detail]

-등록 테이블 리스트 보기
LIST  TABLES  FOR  ALL ;

 

- LOCK상태 확인
GET SNAPSHOT FOR LOCKS ON depsdb
 유지되는 잠금,현재 잠금대기중인 에이전트,응용프로그램명,
 응용프로그램 상태,총대기시간,모드상태 등을 확인

SELECT * FROM staff FETCH FIRST 5 ROWS ONLY
라고 하면 하면 처음5개의row가 나옵니다.

 

SELECT bus_mkt_id, svc_mgmt_num, svc_cd, svc_num, cell_equip_modl_cd, line_num
  FROM coispc.vcell_num
 WHERE svc_mgmt_num = ?
 ORDER BY eff_dt_tm desc FETCH FIRST 1 ROWS ONLY
  WITHUR

SELECT INTEGER(SUBSTR(CHAR(CURRENT DATE),1,1)||SUBSTR(CHAR(CURRENT DATE),3,2)||SUBSTR(CHAR(CURRENT DATE),6,2)||SUBSTR(CHAR(CURRENT DATE),9,2)),
       INTEGER(SUBSTR(CHAR(CURRENT TIMESTAMP),12, 2) || SUBSTR(CHAR(CURRENT TIMESTAMP),15,2) || SUBSTR(CHAR(CURRENT TIMESTAMP),18,2) || SUBSTR(CHAR(CURRENT TIMESTAMP),21,1))
FROM SYSIBM.SYSDUMMY1
WITH UR

CREATE FUNCTION month_between (p_start date, p_end date)
RETURNS SMALLINT
BEGIN atomic
      DECLARE v_year_diff  SMALLINT DEFAULT 0;
      DECLARE v_month_diff SMALLINT DEFAULT 0;
      DECLARE v_diff       SMALLINT DEFAULT 0;
           
      SET v_year_diff  = YEAR(p_start) - YEAR(p_end);
      SET v_month_diff = MONTH(p_start) - MONTH(p_end);
 
      IF v_year_diff != 0 THEN
         set v_diff = v_year_diff * 12;
      END if;
 
      SET v_diff = v_diff + v_month_diff;
 
      RETURN v_diff;
END@

@@@ Oracle의Decode기능
ex1)
SELECT  rownumber,CASE WHEN  zip_code BETWEEN '100091' AND '100091' THEN '91'
                WHEN  zip_code BETWEEN '100092' AND '100092' THEN '92'
                WHEN  zip_code BETWEEN '100093' AND '100093' THEN '93'
                WHEN  zip_code BETWEEN '100094' AND '100094' THEN '94'
                WHEN  zip_code BETWEEN '100095' AND '100095' THEN '95'
             ELSE   '99'
          END
  FROM (
        SELECT zip_code,ROWNUMBER() OVER (ORDER BY zip_code) AS rownumber
          FROM zipcode
       ) AS t
 WHERE ROWNUMBER  BETWEEN 20 AND 30

 

ex2)
SELECT  ROWNUMBER,CASE zip_code
                  WHEN  '100091' THEN '91'
                  WHEN  '100092' THEN '92'
                  WHEN  '100093' THEN '93'
                  WHEN  '100094' THEN '94'
                  WHEN  '100095' THEN '95'
                  ELSE   '99'
                  END
  FROM (                      
        SELECT  zip_code,ROWNUMBER() OVER (ORDER BY zip_code) AS rownumber
          FROM zipcode
       ) AS t
 WHERE ROWNUMBER  BETWEEN 20 AND 30

 

 

@@@ INTEGER형으로 변환

ex)
SELECT INTEGER(zip_code)
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

 

 

@@@ DOUBLE형으로 변환

ex)
SELECT DOUBLE(zip_code)
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

 

 

@@@ SUBSTR

ex)
SELECT SUBSTR(zip_code,1,3)
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

 

 

@@@ CHAR

ex)
SELECT CHAR(doseo)
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

 

 

@@@ COALESCE - Oracle Nvl()기능
컬럼 타입에 따라 인수를 결정한다. COALESCE(문자형,문자형표시)  COALESCE(숫자형,숫자형표시)

ex)
SELECT COALESCE(doseo,'1')
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

 

 

@@@ ||
문자연결기능
SELECT COALESCE(doseo,'1') || zip_code
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

 

 

@@@ page기능
SELECT t.zip_code,page
FROM (
       SELECT zip_code,((ROWNUMBER() OVER() -1)/ 10+1) AS page
         FROM zipcode
     ) AS t
 WHERE t.page = 3
 FETCH FIRST 100 ROWS ONLY
  WITH UR

 

 

@@@ year구하기
ex1)
SELECT  YEAR(CURRENT TIMESTAMP)
  FROM SYSIBM.SYSDUMMY1
  WITH UR

 

ex2)
SELECT  YEAR('2004-05-16')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

 

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

 

 

@@@ month구하기

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

 

ex2)
SELECT  MONTH('2004-08-16')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

 

 

@@@ day구하기

ex1)
SELECT  DAY(CURRENT TIMESTAMP)
  FROM SYSIBM.SYSDUMMY1
  WITH UR
EX2)
SELECT  DAY('2004-08-16')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

 

 

@@@ week구하기
ex)
SELECT  WEEK('2004-05-16')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

@@@ time구하기

ex)
SELECT  CURRENT TIME
  FROM SYSIBM.SYSDUMMY1
  WITHUR

 

 

@@@ dayofyear구하기(오늘이365일중 몇번째 날짜)
ex)
SELECT  DAYOFYEAR(CURRENT DATE)
  FROM SYSIBM.SYSDUMMY1
  WITH UR

 

 

@@@ dayname구하기(요일 이름)

ex)
SELECT  DAYNAME(CURRENT DATE)
  FROM SYSIBM.SYSDUMMY1
  WITHUR

 

 

@@@ CONCAT문자연결함수

ex)
SELECT  CONCAT('111','22222 ')
  FROM SYSIBM.SYSDUMMY1
  WITHUR

 

 

@@@ MOD나머지 함수

ex)
SELECT  MOD(11111,100)
  FROM SYSIBM.SYSDUMMY1
  WITHUR

 

 

@@@ value함수- COALESCE와 동일한 기능

ex)
SELECT  VALUE(CURRENT DATE,'2004-08-16')
  FROM SYSIBM.SYSDUMMY1
  WITHUR

 

 

@@@ abs함수 절대값 함수

ex)
SELECT  ABS(-51234)
  FROM SYSIBM.SYSDUMMY1
  WITHUR

 

 

@@@ lcas함수 대문자를 소문자로

ex)
SELECT  LCASE('ABCD')
  FROM SYSIBM.SYSDUMMY1
  WITHUR;

 

 

@@@ ucase함수 소문자를 대문자로
ex)
SELECT  LCASE('abcd')
  FROM SYSIBM.SYSDUMMY1
  WITH UR;

 

 

@@@ multiply_alt두 수를 곱한다.
ex)
SELECT MULTIPLY_ALT(3,20)
  FROM SYSIBM.SYSDUMMY1
  WITHUR;

 

 

@@@ round
ex)
SELECT  ROUND(873.726, 2), ROUND(873.726, 1), ROUND(873.726, 0), ROUND(873.726,-1),

        ROUND(873.726,-2), ROUND(873.726,-3), ROUND(873.726,-4)
  FROM SYSIBM.SYSDUMMY1
  WITHUR;

 

 

@@@ week_iso함수
ex)
SELECT  WEEK_ISO(CURRENT DATE)     SELECT  WEEK_ISO('1997-12-28')
  FROM SYSIBM.SYSDUMMY1              FROM SYSIBM.SYSDUMMY1        
  WITH UR;                           WITH UR;                    

 

 

@@@ dayofweek_iso해당주에서 몇일에 해당하는지
ex)
SELECT   DAYOFWEEK_ISO(CURRENT DATE)    SELECT   DAYOFWEEK_ISO('2004-08-16')
  FROM SYSIBM.SYSDUMMY1                   FROM SYSIBM.SYSDUMMY1              
  WITH UR;                                WITH UR;                          

 

SELECT  callback_dt,                                          
        callback_tm,                                        
        COUNT(seqno),                                        
        COUNT(custname),                                    
        telno_1||'-'|| telno_2||'-'|| telno_3  AS tel_number
  FROM callback
 GROUP BY  callback_dt,callback_tm,telno_1||'-'|| telno_2||'-'|| telno_3
FETCH FIRST 5 ROWS ONLY;

SELECT  *  FROM
(  SELECT  ROWNUMBER() OVER() AS rownum,statement_text
   FROM  explain_statement
) AS t
WHERE t.rownum = 2
FETCH  FIRST 100  ROWS  ONLY

 

 

@@@ outer join
SELECT CASE  WHEN a.relation = '1' THEN '본인'
             WHEN a.relation = '2' THEN '배우자'
             WHEN a.relation = '3' THEN '자녀'  
             WHEN a.relation = '4' THEN '부모'
             WHEN a.relation = '5' THEN '형제자매'
             WHEN a.relation = '6' THEN '기타'
             ELSE '기타'
        END AS kwan,a.name,a.fsocial_no  AS  fsocial_no,
 CASE SUBSTR(a.fsocial_no,7,1)
      WHEN '1' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
      WHEN '2' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
      WHEN '3' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
      WHEN '4' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
      WHEN '5' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
      WHEN '6' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
      WHEN '7' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
      WHEN '8' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
      WHEN '9' THEN YEAR(CURRENT DATE - DATE('18'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
  ELSE YEAR(CURRENT DATE - DATE('18'||SUBSTR(a.fsocial_no,1,2)||'-'||SUBSTR(a.fsocial_no,3,2)||'-'||'01'))
         END  AS  YEARS,
  VALUE(b.company_nm,'') AS COMPANY_NM,
  VALUE(b.dept,'') AS DEPT,
  VALUE(b.duty,'') AS DUTY,
  VALUE(b.offi_tel_1,'') || VALUE(b.offi_tel_2,'') || VALUE(b.offi_tel_3,'') AS offi_tel,
  CASE WHEN a.live_yn = '1' THEN '동거'
       ELSE ''
  END AS home
 FROM cust.family_info A LEFT OUTER JOIN euc20.customer b ON ( a.fsocial_no =  b.social_no )
WHERE a.social_no = '6611211010815'
ORDER BY  fsocial_no