[DB2] Simple DB2 Functions

▶ 간단한 DB2 함수들 (Simple DB2 Functions)

- 기본정보보기

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
 WITH UR

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기능

eg1)
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

eg2)
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형으로 변환

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

-----------------------------------------------------

@@@ DOUBLE형으로 변환

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

-----------------------------------------------------

@@@ SUBSTR

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

-----------------------------------------------------

@@@ CHAR

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

-----------------------------------------------------

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

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

-----------------------------------------------------

@@@ || - 문자연결기능

eg)
SELECT COALESCE(doseo,'1') || zip_code
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

-----------------------------------------------------

@@@ page기능

eg)
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 구하기

eg1)
SELECT YEAR(CURRENT TIMESTAMP)
  FROM SYSIBM.SYSDUMMY1
WITH UR

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

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

-----------------------------------------------------

@@@ month 구하기

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

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

-----------------------------------------------------

@@@ day 구하기

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

eg2)
SELECT DAY('2004-08-16')
  FROM SYSIBM.SYSDUMMY1
WITH UR

-----------------------------------------------------

@@@ week 구하기

eg)
SELECT WEEK('2004-05-16')
  FROM SYSIBM.SYSDUMMY1
WITH UR

-----------------------------------------------------

@@@ time 구하기

eg)
SELECT CURRENT TIME
  FROM SYSIBM.SYSDUMMY1
WITH UR

-----------------------------------------------------

@@@ day of year 구하기 (오늘이 365일중 몇번째 날짜)

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

-----------------------------------------------------

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

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

-----------------------------------------------------

@@@ CONCAT - 문자연결함수

eg)
SELECT CONCAT('111','22222 ')
  FROM SYSIBM.SYSDUMMY1
WITH UR

-----------------------------------------------------

@@@ MOD - 나머지 함수

eg)
SELECT MOD(11111,100)
  FROM SYSIBM.SYSDUMMY1
WITH UR

-----------------------------------------------------

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

eg)
SELECT VALUE(CURRENT DATE,'2004-08-16')
  FROM SYSIBM.SYSDUMMY1
WITH UR

-----------------------------------------------------

@@@ abs 함수 절대값 함수

eg)
SELECT ABS(-51234)
  FROM SYSIBM.SYSDUMMY1
WITH UR

-----------------------------------------------------

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

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

-----------------------------------------------------

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

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

-----------------------------------------------------

@@@ multiply_alt 두 수를 곱한다.

eg)
SELECT MULTIPLY_ALT(3,20)
  FROM SYSIBM.SYSDUMMY1
WITH UR;

-----------------------------------------------------

@@@ round

eg)
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
WITH UR;

-----------------------------------------------------

@@@ week_iso 함수

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

eg2)
SELECT WEEK_ISO('1997-12-28')
  FROM SYSIBM.SYSDUMMY1
WITH UR;

-----------------------------------------------------

@@@ dayofweek_iso 해당주에서 몇일에 해당하는지

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

eg2)
SELECT DAYOFWEEK_ISO('2004-08-16')
  FROM SYSIBM.SYSDUMMY1
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;