Data Craft for Data Informer: 2월 2017

MySQL 에서 Date 와 Rownum 을 만드는 쿼리 임

세상에는 나 보다 똑똑 하신 분들이 많네


/**
    make date calander 
*/


--
-- 현재 일자 부터 과거 로 몇일 
--
 SELECT dt.yyyymmdd
    FROM (
           SELECT
               curdate() - INTERVAL (100*a.a + (10 * b.a) + (c.a)) DAY AS yyyymmdd
           FROM (SELECT 0 AS a
                 UNION ALL SELECT 1
                 UNION ALL SELECT 2
                 UNION ALL SELECT 3
                 ) AS a
             CROSS JOIN (SELECT 0 AS a
                         UNION ALL SELECT 1
                         UNION ALL SELECT 2
                         UNION ALL SELECT 3
                         UNION ALL SELECT 4
                         UNION ALL SELECT 5
                         UNION ALL SELECT 6
                         UNION ALL SELECT 7
                         UNION ALL SELECT 8
                         UNION ALL SELECT 9) AS b
             CROSS JOIN (SELECT 0 AS a
                         UNION ALL SELECT 1
                         UNION ALL SELECT 2
                         UNION ALL SELECT 3
                         UNION ALL SELECT 4
                         UNION ALL SELECT 5
                         UNION ALL SELECT 6
                         UNION ALL SELECT 7
                         UNION ALL SELECT 8
                         UNION ALL SELECT 9) AS c
         ) dt
    where yyyymmdd BETWEEN curdate() - INTERVAL 10 day and curdate()
    order by 1
;

--
-- 년도별 칼렌더
--

SELECT
  ym
  , min(CASE dw
        WHEN 1
          THEN d END) Sun
  , min(CASE dw
        WHEN 2
          THEN d END) Mon
  , min(CASE dw
        WHEN 3
          THEN d END) Tue
  , min(CASE dw
        WHEN 4
          THEN d END) Wed
  , min(CASE dw
        WHEN 5
          THEN d END) Thu
  , min(CASE dw
        WHEN 6
          THEN d END) Fri
  , min(CASE dw
        WHEN 7
          THEN d END) Sat
FROM
  (
    SELECT
        date_format(dt, '%Y%m') ym
      , WEEK(dt)                w
      , Day(dt)                 d
      , DAYOFWEEK(dt)           dw
    FROM
      (
        SELECT concat(y, '0101') + INTERVAL a * 100 + b * 10 + c DAY AS dt
        FROM
          (
            SELECT 0 a
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
          ) aa,
          (
            SELECT 0 b
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
            UNION ALL SELECT 4
            UNION ALL SELECT 5
            UNION ALL SELECT 6
            UNION ALL SELECT 7
            UNION ALL SELECT 8
            UNION ALL SELECT 9
          ) bb,
          (
            SELECT 0 c
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
            UNION ALL SELECT 4
            UNION ALL SELECT 5
            UNION ALL SELECT 6
            UNION ALL SELECT 7
            UNION ALL SELECT 8
            UNION ALL SELECT 9
          ) cc,
          (
            SELECT '2018' y
          ) dd
        WHERE a * 100 + b * 10 + c < DayOfYear(concat(y, '1231'))
      ) a
  ) a
GROUP BY ym, w
;



/**
    MySQL Mk Rownum

*/

select
    @rnum := @rnum + 1 as Rownum
from
    ( select @rnum := -1 ) r

간략하게 티베로 디비 만드는 스크립트 입니다.


# vi /etc/sysctl.conf
kernel.shmmax = 1073741824
kernel.shmall = 2097152
kernel.sem = 10000 32000 10000 10000
kernel.shmmni = 4096
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 1024 65000

* /etc/security/limits.conf 를 수정
# vi /etc/security/limits.conf
tibero soft nproc 2047
tibero hard nproc 16384
tibero soft nofile 1024
tibero hard nofile 65536

seforce 0

groupadd dba
useradd -g dba tibero

export  TB_BASE=/opt/tibero
export  TB_HOME=/opt/tibero/tibero6
export  TB_SID=tibero
export  TB_VER=tibero6
export  TB_PROF_DIR=$TB_HOME/bin/prof
export  JAVA_HOME=/opt/java

export  PATH=$TB_HOME/bin:$TB_HOME/client/bin:$JAVA_HOME/bin:/bin:/usr/bin:/usr/local/bin:.
export  LD_LIBRARY_PATH=$TB_HOME/lib:$TB_HOME/client/lib:$JAVA_HOME/lib:/lib64:/usr/lib64:/usr/local/lib:.

######## TIBERO alias ########
alias tbhome='cd $TB_HOME'
alias tbbin='cd $TB_HOME/bin'
alias tblog='cd $TB_HOME/instance/$TB_SID/log'
alias tbcfg='cd $TB_HOME/config'
alias tbcfgv='vi $TB_HOME/config/$TB_SID.tip'
alias tbcli='cd ${TB_HOME}/client/config'
alias tbcliv='vi ${TB_HOME}/client/config/tbdsn.tbr'

cp license.xml $TB_HOME/license
cd $TB_HOME/bin
sh $TB_HOME/config/get_tip.sh

/*

############################################
### Database Identification
############################################
DB_NAME=TBTAC
LISTENER_PORT=8629

############################################
### File Configuration
############################################
DB_CREATE_FILE_DEST="/tibero/tbdata"
CONTROL_FILES="/dev/mapper/sdiskvg1-lvctl_20m_01vg1","/dev/mapper/sdiskvg2-lvctl_20m_01vg2"

############################################
### Memory & Session
############################################
MAX_SESSION_COUNT=50
TOTAL_SHM_SIZE=768M
MEMORY_TARGET=1536M

DBMS_LOG_TOTAL_SIZE_LIMIT=120M
TRACE_LOG_TOTAL_SIZE_LIMIT=300M
############################################
### Undo and Rollback Segments
############################################
#UNDO_RETENTION=900
UNDO_TABLESPACE="UNDOTBS0"

############################################
### Cache and I/O
############################################
DB_BLOCK_SIZE=8192
#DB_FILE_MULTIBLOCK_READ_COUNT=64

###########################################
### Archive Log Mode
############################################
LOG_ARCHIVE_FORMAT="arcTAC%t_%s_%r.arc"
LOG_ARCHIVE_DEST="/tibero/tibarch"
#_ARCHIVE_LAG_TARGET=21600
#_ARCHIVE_CLOSED_THREAD=Y
#_ARCHIVE_CLOSED_THREAD_DIE_ON_FAIL=N

############################################
### Statistics
############################################
#AUDIT_TRAIL=NONE
#AUDIT_SYS_OPERATIONS=N
#AUDIT_FILE_DEST="/tibero/tiblog/tac/audit"

############################################
### APM Report
############################################
#AUTOMATIC_PERFORMANCE_MONITORING=Y
#APM_SNAPSHOT_SAMPLING_INTERVAL=60
#APM_SNAPSHOT_RETENTION=7
#APM_SNAPSHOT_TOP_SQL_CNT=10

############################################
### Checkpoint
############################################
#_LOG_INC_CHECKPOINT_TIMEOUT=1
#_LOG_INC_CKPT_LAG_LIMIT_PCT=25

############################################
### Log Dest
############################################
#CM_LOG_DEST="/tibero/tiblog/tac/log/cm"
#DBMS_LOG_DEST="/tibero/tiblog/tac/log/dbmslog"
#EVENT_TRACE_DEST="/tibero/tiblog/tac/log/event"
#LSNR_LOG_DEST="/tibero/tiblog/tac/log/lsnr"
#SQL_TRACE_DEST="/tibero/tiblog/tac/log/sqltrace"
#TRACE_DUMP_DEST="/tibero/tiblog/tac/dump/tracedump"
#TRACE_LOG_DEST="/tibero/tiblog/tac/log/tracelog"

############################################
### Cluster Database
############################################
CLUSTER_DATABASE=Y
THREAD=0
LOCAL_CLUSTER_ADDR=10.10.1.101
LOCAL_CLUSTER_PORT=8631
LOCAL_CLUSTER_VIP=192.167.90.111
LOCAL_CLUSTER_NIC=enp0s8
LOCAL_CLUSTER_VIP_NETMASK=255.255.255.0
LOCAL_CLUSTER_VIP_BROADCAST=192.167.90.255

############################################
### TBCM Parameter
############################################
CM_CLUSTER_MODE=ACTIVE_SHARED
CM_FILE_NAME="/dev/mapper/sdiskvg1-lvtbcm_10m_01vg1","/dev/mapper/sdiskvg2-lvtbcm_10m_01vg2"
CM_PORT=8632
CM_HEARTBEAT_EXPIRE=60
CM_WATCHDOG_EXPIRE=50
#CM_NET_EXPIRE_MARGIN=10
CM_AUTO_VIP_FAILBACK=N
CM_ENABLE_CM_DOWN_NOTIFICATION=Y
CM_ENABLE_FAST_NET_ERROR_DETECTION=Y
CM_FENCE=Y
#_CM_ENABLE_VIP_ALIAS_CHECK=N
#_CM_ENABLE_VIP_ALIAS_RETRY=N
#_CM_REBOOT_RETRY_COUNT=3

############################################
### Miscellaneous
############################################
#ACTIVE_SESSION_HISTORY=Y
USE_NET_KEEPALIVE=Y
USE_RECYCLEBIN=Y
NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"
#BIND_VARIABLE_CAPTURE=Y
#JOB_HISTORY_LOG=Y

    TBTAC1=(
        (INSTANCE=(HOST=192.167.90.101)
              (PORT=8629)
              (DB_NAME=TBTAC)
        )
    )

    TBTAC=(
        (INSTANCE=(HOST=192.167.90.111)
              (PORT=8629)
              (DB_NAME=TBTAC)
        )
        (INSTANCE=(HOST=192.167.90.211)
              (PORT=8629)
              (DB_NAME=TBTAC)
        )
        (LOAD_BALANACE=Y)
        (USE_FAILOVER=Y)
    )

*/

tbboot nomount

tbsql sys/tibero

/*

CREATE DATABASE "tibero"
    USER SYS IDENTIFIED BY TIBERO
MAXDATAFILES 4096
maxinstances 8
CHARACTER SET MSWIN949
LOGFILE GROUP 0 ('redo01a.redo','redo01b.redo') SIZE 1024M,
        GROUP 1 ('redo11a.redo','redo11b.redo') SIZE 1024M,
        GROUP 2 ('redo21a.redo','redo21b.redo') SIZE 1024M,
        GROUP 3 ('redo31a.redo','redo31b.redo') SIZE 1024M,
        GROUP 4 ('redo41a.redo','redo41b.redo') SIZE 1024M
MAXLOGFILES 100
maxloggroups 255
MAXLOGMEMBERS 10
NOARCHIVELOG
DATAFILE 'system001.dtf' SIZE 1024M
AUTOEXTEND ON NEXT 16M MAXSIZE 3072M
DEFAULT TABLESPACE USR
DATAFILE 'usr001.dtf' SIZE 128M
AUTOEXTEND ON NEXT 16M MAXSIZE 3072M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE 'temp001.dtf' SIZE 1024M
AUTOEXTEND ON NEXT 16M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
UNDO TABLESPACE UNDO
DATAFILE 'undo001.dtf' SIZE 1024M
AUTOEXTEND ON NEXT 16M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

*/

tbdown normal

tbboot 
sh $TB_HOME/scripts/system.sh -p1 tibero -p2 syscat -a1 y -a2 y -a3 y -a4 y

Data Craft for Data Informer

2017년 2월 20일 월요일

과거의 기억에서 부터

2017년 2월 15일 수요일

MySQL 의 날짜와 로우넘버

2017년 2월 10일 금요일

Tibero DB Create

2017년 2월 9일 목요일

우리 회사 데이터베이스를 티베로로 변경하기

우리 회사 데이터베이스를 티베로로 변경하기

PostgreSQL 성능 최적화

블로그 보관함