DB 별 TPC-H 로드 방법

IT_Engineer/DBA|2023. 10. 19. 10:54
728x90

# 참고 페이지

1. Mysql


2. Hive:


3. Oracle:

 

>  로드(데이터 적재)
- TPC-H 데이터베이스 성능 측정 : https://ycseo.tistory.com/21
- TPC-H #1 . 준비 : https://www.haedongg.net/?p=355

> 확인
- tablespace에 속한 table 조회(owner) :  
 
- 오라클 테이블 스페이스 사용법(조회, 생성, 삭제)등 총정리 :  

- TableSpace 용량 및 관련내용 조회  

4. Sybase:

## 쿼리 가이드

-- Query 21)
        select s_name, n_name,
        count(*) as numwait
        from supplier, lineitem l1, orders, nation
        where s_suppkey = l1.l_suppkey
        and o_orderkey = l1.l_orderkey
        and o_orderstatus = 'F'
        and l1.l_receiptdate > l1.l_commitdate
        and exists ( select *
        from lineitem l2
        where l2.l_orderkey = l1.l_orderkey
        and l2.l_suppkey <> l1.l_suppkey)
        and s_nationkey = n_nationkey
        and n_name = 'SAUDI ARABIA'
        group by s_name,n_name

     
-- Query 22)     
          select cntrycode,
          count(*) as numcust,
          sum(c_acctbal) as totacctbal
          from ( select substring (c_phone ,1,2) as cntrycode,
          c_acctbal
          from customer
          where substring (c_phone ,1,2) in('13', '31', '23', '29', '30', '18', '17')
          and c_acctbal > ( select avg(c_acctbal)
          from customer
          where c_acctbal > 0.00
          and substring (c_phone ,1,2) in('13','31', '23', '29', '30', '18', '17') )
          and not exists ( select *
          from orders
          where o_custkey = c_custkey) ) as custsale
          group by cntrycode
          order by cntrycode
 

- tbl 만들고 (dbgen -s 10) -> 10G DB 생성 

[oracle@oracle-server tpch]$ ls -al
합계 10968908
drwxr-xr-x  2 root root        156  2월 16 15:49 .
drwxrwxr-x+ 6 root root         56  2월 16 15:47 ..
-rw-r--r--  1 root root  244847642  2월 16 15:39 customer.tbl
-rw-r--r--  1 root root 7775727688  2월 16 15:39 lineitem.tbl
-rw-r--r--  1 root root       2224  2월 16 15:39 nation.tbl
-rw-r--r--  1 root root 1749195031  2월 16 15:39 orders.tbl
-rw-r--r--  1 root root  243336157  2월 16 15:39 part.tbl
-rw-r--r--  1 root root 1204850769  2월 16 15:39 partsupp.tbl
-rw-r--r--  1 root root        389  2월 16 15:39 region.tbl
-rw-r--r--  1 root root   14176368  2월 16 15:39 supplier.tbl

[oracle@oracle-server tpch]$ sqlplus tpch/tpch
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 16 15:52:23 2022
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- CREATE TABLE tpch.ext_part
-- (
--     p_partkey       NUMBER(10, 0),
--     p_name          VARCHAR2(55),
--     p_mfgr          CHAR(25),
--     p_brand         CHAR(10),
--     p_type          VARCHAR2(25),
--     p_size          INTEGER,
--     p_container     CHAR(10),
--     p_retailprice   NUMBER,
--     p_comment       VARCHAR2(23)
-- )
-- ORGANIZATION EXTERNAL
--     (TYPE oracle_loader
--           DEFAULT DIRECTORY tpch_dir
--               ACCESS PARAMETERS (
--                   FIELDS
--                       TERMINATED BY '|'
--                   MISSING FIELD VALUES ARE NULL
--               )
--           LOCATION('part.tbl'));

DROP TABLE tpch.ext_part CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_supplier CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_partsupp CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_customer CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_orders CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_lineitem CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_nation CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_region CASCADE CONSTRAINTS;

DROP TABLE tpch.part CASCADE CONSTRAINTS;
DROP TABLE tpch.supplier CASCADE CONSTRAINTS;
DROP TABLE tpch.partsupp CASCADE CONSTRAINTS;
DROP TABLE tpch.customer CASCADE CONSTRAINTS;
DROP TABLE tpch.orders CASCADE CONSTRAINTS;
DROP TABLE tpch.lineitem CASCADE CONSTRAINTS;
DROP TABLE tpch.nation CASCADE CONSTRAINTS;
DROP TABLE tpch.region CASCADE CONSTRAINTS;


CREATE OR REPLACE DIRECTORY TPCH_DIR AS '/home/oracle/tpch/tpch/TPC-H_Tools_v3.0.0/dbgen/oracle20g';

CREATE TABLE tpch.ext_part
(
    p_partkey       NUMBER(10, 0),
    p_name          VARCHAR2(55),
    p_mfgr          CHAR(25),
    p_brand         CHAR(10),
    p_type          VARCHAR2(25),
    p_size          INTEGER,
    p_container     CHAR(10),
    p_retailprice   NUMBER,
    p_comment       VARCHAR2(23)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('part.tbl'));


CREATE TABLE tpch.part
(
    p_partkey       NUMBER(10, 0) NOT NULL,
    p_name          VARCHAR2(55) NOT NULL,
    p_mfgr          CHAR(25) NOT NULL,
    p_brand         CHAR(10) NOT NULL,
    p_type          VARCHAR2(25) NOT NULL,
    p_size          INTEGER NOT NULL,
    p_container     CHAR(10) NOT NULL,
    p_retailprice   NUMBER NOT NULL,
    p_comment       VARCHAR2(23) NOT NULL
);


CREATE TABLE tpch.ext_supplier
(
    s_suppkey     NUMBER(10, 0),
    s_name        CHAR(25),
    s_address     VARCHAR2(40),
    s_nationkey   NUMBER(10, 0),
    s_phone       CHAR(15),
    s_acctbal     NUMBER,
    s_comment     VARCHAR2(101)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('supplier.tbl'));


CREATE TABLE tpch.supplier
(
    s_suppkey     NUMBER(10, 0) NOT NULL,
    s_name        CHAR(25) NOT NULL,
    s_address     VARCHAR2(40) NOT NULL,
    s_nationkey   NUMBER(10, 0) NOT NULL,
    s_phone       CHAR(15) NOT NULL,
    s_acctbal     NUMBER NOT NULL,
    s_comment     VARCHAR2(101) NOT NULL
);

CREATE TABLE tpch.ext_partsupp
(
    ps_partkey      NUMBER(10, 0),
    ps_suppkey      NUMBER(10, 0),
    ps_availqty     INTEGER,
    ps_supplycost   NUMBER,
    ps_comment      VARCHAR2(199)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('partsupp.tbl'));

CREATE TABLE tpch.partsupp
(
    ps_partkey      NUMBER(10, 0) NOT NULL,
    ps_suppkey      NUMBER(10, 0) NOT NULL,
    ps_availqty     INTEGER NOT NULL,
    ps_supplycost   NUMBER NOT NULL,
    ps_comment      VARCHAR2(199) NOT NULL
);


CREATE TABLE tpch.ext_customer
(
    c_custkey      NUMBER(10, 0),
    c_name         VARCHAR2(25),
    c_address      VARCHAR2(40),
    c_nationkey    NUMBER(10, 0),
    c_phone        CHAR(15),
    c_acctbal      NUMBER,
    c_mktsegment   CHAR(10),
    c_comment      VARCHAR2(117)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('customer.tbl'));

CREATE TABLE tpch.customer
(
    c_custkey      NUMBER(10, 0) NOT NULL,
    c_name         VARCHAR2(25) NOT NULL,
    c_address      VARCHAR2(40) NOT NULL,
    c_nationkey    NUMBER(10, 0) NOT NULL,
    c_phone        CHAR(15) NOT NULL,
    c_acctbal      NUMBER NOT NULL,
    c_mktsegment   CHAR(10) NOT NULL,
    c_comment      VARCHAR2(117) NOT NULL
);

CREATE TABLE tpch.ext_orders
(
    o_orderkey        NUMBER(10, 0),
    o_custkey         NUMBER(10, 0),
    o_orderstatus     CHAR(1),
    o_totalprice      NUMBER,
    o_orderdate       CHAR(10),
    o_orderpriority   CHAR(15),
    o_clerk           CHAR(15),
    o_shippriority    INTEGER,
    o_comment         VARCHAR2(79)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('orders.tbl'));

CREATE TABLE tpch.orders
(
    o_orderkey        NUMBER(10, 0) NOT NULL,
    o_custkey         NUMBER(10, 0) NOT NULL,
    o_orderstatus     CHAR(1) NOT NULL,
    o_totalprice      NUMBER NOT NULL,
    o_orderdate       DATE NOT NULL,
    o_orderpriority   CHAR(15) NOT NULL,
    o_clerk           CHAR(15) NOT NULL,
    o_shippriority    INTEGER NOT NULL,
    o_comment         VARCHAR2(79) NOT NULL
);

CREATE TABLE tpch.ext_lineitem
(
    l_orderkey        NUMBER(10, 0),
    l_partkey         NUMBER(10, 0),
    l_suppkey         NUMBER(10, 0),
    l_linenumber      INTEGER,
    l_quantity        NUMBER,
    l_extendedprice   NUMBER,
    l_discount        NUMBER,
    l_tax             NUMBER,
    l_returnflag      CHAR(1),
    l_linestatus      CHAR(1),
    l_shipdate        CHAR(10),
    l_commitdate      CHAR(10),
    l_receiptdate     CHAR(10),
    l_shipinstruct    CHAR(25),
    l_shipmode        CHAR(10),
    l_comment         VARCHAR2(44)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('lineitem.tbl'));


CREATE TABLE tpch.lineitem
(
    l_orderkey        NUMBER(10, 0),
    l_partkey         NUMBER(10, 0),
    l_suppkey         NUMBER(10, 0),
    l_linenumber      INTEGER,
    l_quantity        NUMBER,
    l_extendedprice   NUMBER,
    l_discount        NUMBER,
    l_tax             NUMBER,
    l_returnflag      CHAR(1),
    l_linestatus      CHAR(1),
    l_shipdate        DATE,
    l_commitdate      DATE,
    l_receiptdate     DATE,
    l_shipinstruct    CHAR(25),
    l_shipmode        CHAR(10),
    l_comment         VARCHAR2(44)
);


CREATE TABLE tpch.ext_nation
(
    n_nationkey   NUMBER(10, 0),
    n_name        CHAR(25),
    n_regionkey   NUMBER(10, 0),
    n_comment     VARCHAR(152)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('nation.tbl'));

CREATE TABLE tpch.nation
(
    n_nationkey   NUMBER(10, 0),
    n_name        CHAR(25),
    n_regionkey   NUMBER(10, 0),
    n_comment     VARCHAR(152)
);

CREATE TABLE tpch.ext_region
(
    r_regionkey   NUMBER(10, 0),
    r_name        CHAR(25),
    r_comment     VARCHAR(152)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('region.tbl'));

CREATE TABLE tpch.region
(
    r_regionkey   NUMBER(10, 0),
    r_name        CHAR(25),
    r_comment     VARCHAR(152)
);


TRUNCATE TABLE tpch.part;
TRUNCATE TABLE tpch.supplier;
TRUNCATE TABLE tpch.partsupp;
TRUNCATE TABLE tpch.customer;
TRUNCATE TABLE tpch.orders;
TRUNCATE TABLE tpch.lineitem;
TRUNCATE TABLE tpch.nation;
TRUNCATE TABLE tpch.region;

ALTER SESSION SET nls_date_format='YYYY-MM-DD';

INSERT /*+ APPEND */ INTO  tpch.part     SELECT * FROM tpch.ext_part;
INSERT /*+ APPEND */ INTO  tpch.supplier SELECT * FROM tpch.ext_supplier;
INSERT /*+ APPEND */ INTO  tpch.partsupp SELECT * FROM tpch.ext_partsupp;
INSERT /*+ APPEND */ INTO  tpch.customer SELECT * FROM tpch.ext_customer;
INSERT /*+ APPEND */ INTO  tpch.orders   SELECT * FROM tpch.ext_orders;
INSERT /*+ APPEND */ INTO  tpch.lineitem SELECT * FROM tpch.ext_lineitem;
INSERT /*+ APPEND */ INTO  tpch.nation   SELECT * FROM tpch.ext_nation;
INSERT /*+ APPEND */ INTO  tpch.region   SELECT * FROM tpch.ext_region;


--- ORACLE 30G 

CREATE TABLE G1.ext_part
(
    p_partkey       NUMBER(10, 0),
    p_name          VARCHAR2(55),
    p_mfgr          CHAR(25),
    p_brand         CHAR(10),
    p_type          VARCHAR2(25),
    p_size          INTEGER,
    p_container     CHAR(10),
    p_retailprice   NUMBER,
    p_comment       VARCHAR2(23)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('part.tbl'));

CREATE TABLE g1.part
(
    p_partkey       NUMBER(10, 0) NOT NULL,
    p_name          VARCHAR2(55) NOT NULL,
    p_mfgr          CHAR(25) NOT NULL,
    p_brand         CHAR(10) NOT NULL,
    p_type          VARCHAR2(25) NOT NULL,
    p_size          INTEGER NOT NULL,
    p_container     CHAR(10) NOT NULL,
    p_retailprice   NUMBER NOT NULL,
    p_comment       VARCHAR2(23) NOT NULL
);


CREATE TABLE g1.ext_supplier
(
    s_suppkey     NUMBER(10, 0),
    s_name        CHAR(25),
    s_address     VARCHAR2(40),
    s_nationkey   NUMBER(10, 0),
    s_phone       CHAR(15),
    s_acctbal     NUMBER,
    s_comment     VARCHAR2(101)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('supplier.tbl'));

CREATE TABLE g1.supplier
(
    s_suppkey     NUMBER(10, 0) NOT NULL,
    s_name        CHAR(25) NOT NULL,
    s_address     VARCHAR2(40) NOT NULL,
    s_nationkey   NUMBER(10, 0) NOT NULL,
    s_phone       CHAR(15) NOT NULL,
    s_acctbal     NUMBER NOT NULL,
    s_comment     VARCHAR2(101) NOT NULL
);

CREATE TABLE g1.ext_partsupp
(
    ps_partkey      NUMBER(10, 0),
    ps_suppkey      NUMBER(10, 0),
    ps_availqty     INTEGER,
    ps_supplycost   NUMBER,
    ps_comment      VARCHAR2(199)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('partsupp.tbl'));

CREATE TABLE g1.partsupp
(
    ps_partkey      NUMBER(10, 0) NOT NULL,
    ps_suppkey      NUMBER(10, 0) NOT NULL,
    ps_availqty     INTEGER NOT NULL,
    ps_supplycost   NUMBER NOT NULL,
    ps_comment      VARCHAR2(199) NOT NULL
);

CREATE TABLE g1.ext_customer
(
    c_custkey      NUMBER(10, 0),
    c_name         VARCHAR2(25),
    c_address      VARCHAR2(40),
    c_nationkey    NUMBER(10, 0),
    c_phone        CHAR(15),
    c_acctbal      NUMBER,
    c_mktsegment   CHAR(10),
    c_comment      VARCHAR2(117)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('customer.tbl'));

CREATE TABLE g1.customer
(
    c_custkey      NUMBER(10, 0) NOT NULL,
    c_name         VARCHAR2(25) NOT NULL,
    c_address      VARCHAR2(40) NOT NULL,
    c_nationkey    NUMBER(10, 0) NOT NULL,
    c_phone        CHAR(15) NOT NULL,
    c_acctbal      NUMBER NOT NULL,
    c_mktsegment   CHAR(10) NOT NULL,
    c_comment      VARCHAR2(117) NOT NULL
);

-- read date values as yyyy-mm-dd text

CREATE TABLE g1.ext_orders
(
    o_orderkey        NUMBER(10, 0),
    o_custkey         NUMBER(10, 0),
    o_orderstatus     CHAR(1),
    o_totalprice      NUMBER,
    o_orderdate       CHAR(10),
    o_orderpriority   CHAR(15),
    o_clerk           CHAR(15),
    o_shippriority    INTEGER,
    o_comment         VARCHAR2(79)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('orders.tbl'));

CREATE TABLE g1.orders
(
    o_orderkey        NUMBER(10, 0) NOT NULL,
    o_custkey         NUMBER(10, 0) NOT NULL,
    o_orderstatus     CHAR(1) NOT NULL,
    o_totalprice      NUMBER NOT NULL,
    o_orderdate       DATE NOT NULL,
    o_orderpriority   CHAR(15) NOT NULL,
    o_clerk           CHAR(15) NOT NULL,
    o_shippriority    INTEGER NOT NULL,
    o_comment         VARCHAR2(79) NOT NULL
);

-- read date values as yyyy-mm-dd text

CREATE TABLE g1.ext_lineitem
(
    l_orderkey        NUMBER(10, 0),
    l_partkey         NUMBER(10, 0),
    l_suppkey         NUMBER(10, 0),
    l_linenumber      INTEGER,
    l_quantity        NUMBER,
    l_extendedprice   NUMBER,
    l_discount        NUMBER,
    l_tax             NUMBER,
    l_returnflag      CHAR(1),
    l_linestatus      CHAR(1),
    l_shipdate        CHAR(10),
    l_commitdate      CHAR(10),
    l_receiptdate     CHAR(10),
    l_shipinstruct    CHAR(25),
    l_shipmode        CHAR(10),
    l_comment         VARCHAR2(44)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('lineitem.tbl'));

CREATE TABLE g1.lineitem
(
    l_orderkey        NUMBER(10, 0),
    l_partkey         NUMBER(10, 0),
    l_suppkey         NUMBER(10, 0),
    l_linenumber      INTEGER,
    l_quantity        NUMBER,
    l_extendedprice   NUMBER,
    l_discount        NUMBER,
    l_tax             NUMBER,
    l_returnflag      CHAR(1),
    l_linestatus      CHAR(1),
    l_shipdate        DATE,
    l_commitdate      DATE,
    l_receiptdate     DATE,
    l_shipinstruct    CHAR(25),
    l_shipmode        CHAR(10),
    l_comment         VARCHAR2(44)
);

CREATE TABLE g1.ext_nation
(
    n_nationkey   NUMBER(10, 0),
    n_name        CHAR(25),
    n_regionkey   NUMBER(10, 0),
    n_comment     VARCHAR(152)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('nation.tbl'));

CREATE TABLE g1.nation
(
    n_nationkey   NUMBER(10, 0),
    n_name        CHAR(25),
    n_regionkey   NUMBER(10, 0),
    n_comment     VARCHAR(152)
);

CREATE TABLE g1.ext_region
(
    r_regionkey   NUMBER(10, 0),
    r_name        CHAR(25),
    r_comment     VARCHAR(152)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('region.tbl'));

CREATE TABLE g1.region
(
    r_regionkey   NUMBER(10, 0),
    r_name        CHAR(25),
    r_comment     VARCHAR(152)
);


TRUNCATE TABLE g1.part;
TRUNCATE TABLE g1.supplier;
TRUNCATE TABLE g1.partsupp;
TRUNCATE TABLE g1.customer;
TRUNCATE TABLE g1.orders;
TRUNCATE TABLE g1.lineitem;
TRUNCATE TABLE g1.nation;
TRUNCATE TABLE g1.region;

ALTER SESSION SET nls_date_format='YYYY-MM-DD';

INSERT /*+ APPEND */ INTO  g1.part     SELECT * FROM g1.ext_part;
INSERT /*+ APPEND */ INTO  g1.supplier SELECT * FROM g1.ext_supplier;
INSERT /*+ APPEND */ INTO  g1.partsupp SELECT * FROM g1.ext_partsupp;
INSERT /*+ APPEND */ INTO  g1.customer SELECT * FROM g1.ext_customer;
INSERT /*+ APPEND */ INTO  g1.orders   SELECT * FROM g1.ext_orders;
INSERT /*+ APPEND */ INTO  g1.lineitem SELECT * FROM g1.ext_lineitem;
INSERT /*+ APPEND */ INTO  g1.nation   SELECT * FROM g1.ext_nation;
INSERT /*+ APPEND */ INTO  g1.region   SELECT * FROM g1.ext_region;


--- (만약의 상황) 데이터를 다시 넣기

ALTER TABLE tpch20.lineitem drop CONSTRAINT chk_lineitem_ship_rcpt ----check(l_shipdate <= l_receiptdate);
;
ALTER TABLE tpch20.lineitem
DROP CONSTRAINT chk_lineitem_discount -- --check(l_discount >= 0.00 AND l_discount <= 1.00);
;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT chk_lineitem_tax ----check(l_tax >= 0);
    ;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT chk_lineitem_extendedprice ----check(l_extendedprice >= 0);
;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT chk_lineitem_quantity --check(l_quantity >= 0);
;
ALTER TABLE tpch20.orders
    drop CONSTRAINT chk_orders_totalprice --check(o_totalprice >= 0);
;
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT chk_partsupp_supplycost --check(ps_supplycost >= 0);
;
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT chk_partsupp_availqty --check(ps_availqty >= 0);
;
ALTER TABLE tpch20.part
    drop CONSTRAINT chk_part_retailprice --check(p_retailprice >= 0);
;
    ALTER TABLE tpch20.part
    drop CONSTRAINT chk_part_size --check(p_size >= 0);
;

ALTER TABLE tpch20.nation
    drop CONSTRAINT chk_nation_nationkey --check(n_nationkey >= 0);
 ;   
ALTER TABLE tpch20.region
    drop CONSTRAINT chk_region_regionkey --check(r_regionkey >= 0);
;
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT chk_partsupp_partkey --check(ps_partkey >= 0);
;
ALTER TABLE tpch20.customer
    drop CONSTRAINT chk_customer_custkey --check(c_custkey >= 0);
;
ALTER TABLE tpch20.supplier
    drop CONSTRAINT chk_supplier_suppkey --check(s_suppkey >= 0);
;
    ALTER TABLE tpch20.part
    drop CONSTRAINT chk_part_partkey --check(p_partkey >= 0);
;

 ALTER TABLE tpch20.region
    drop CONSTRAINT pk_region -- --primary key(r_regionkey);
;
 ALTER TABLE tpch20.nation
    drop CONSTRAINT pk_nation --primary key(n_nationkey);
;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT pk_lineitem --primary key(l_linenumber, l_orderkey);
;
ALTER TABLE tpch20.orders
    drop CONSTRAINT pk_orders --primary key(o_orderkey);
;
ALTER TABLE tpch20.customer
    drop CONSTRAINT pk_customer --primary key(c_custkey);
;
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT pk_partsupp --primary key(ps_partkey, ps_suppkey);
;
ALTER TABLE tpch20.supplier
    drop CONSTRAINT pk_supplier --primary key(s_suppkey);
;
ALTER TABLE tpch20.part
    drop CONSTRAINT pk_part --primary key(p_partkey);
;


-- 1.4.2.3


ALTER TABLE tpch20.lineitem
    drop CONSTRAINT fk_lineitem_partsupp; -- FOREIGN KEY(l_partkey, l_suppkey);
--        REFERENCES tpch20.partsupp(ps_partkey, ps_suppkey);
 ;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT fk_lineitem_supplier ;--FOREIGN KEY(l_suppkey) REFERENCES tpch20.supplier(s_suppkey);
;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT fk_lineitem_part ; -- FOREIGN KEY(l_partkey) REFERENCES tpch20.part(p_partkey);
 ;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT fk_lineitem_order ; --FOREIGN KEY(l_orderkey) REFERENCES tpch20.orders(o_orderkey);
;
ALTER TABLE tpch20.orders
    drop CONSTRAINT fk_orders_customer -- FOREIGN KEY(o_custkey) REFERENCES tpch20.customer(c_custkey);
  ;  
ALTER TABLE tpch20.customer
    drop CONSTRAINT fk_customer_nation -- FOREIGN KEY(c_nationkey) REFERENCES tpch20.nation(n_nationkey);
 ;   
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT fk_partsupp_supplier -- FOREIGN KEY(ps_suppkey) REFERENCES tpch20.supplier(s_suppkey);
;
         ALTER TABLE tpch20.partsupp
    drop CONSTRAINT fk_partsupp_part -- FOREIGN KEY(ps_partkey) REFERENCES tpch20.part(p_partkey);
; 
 
 -----

INSERT /*+ APPEND */ INTO  tpch20.region   SELECT * FROM tpch20.ext_region;
INSERT /*+ APPEND */ INTO  tpch20.customer SELECT * FROM tpch20.ext_customer;


ALTER TABLE tpch20.part
    ADD CONSTRAINT pk_part PRIMARY KEY(p_partkey);

ALTER TABLE tpch20.supplier
    ADD CONSTRAINT pk_supplier PRIMARY KEY(s_suppkey);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT pk_partsupp PRIMARY KEY(ps_partkey, ps_suppkey);



ALTER TABLE tpch20.customer
    ADD CONSTRAINT pk_customer PRIMARY KEY(c_custkey);

ALTER TABLE tpch20.orders
    ADD CONSTRAINT pk_orders PRIMARY KEY(o_orderkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT pk_lineitem PRIMARY KEY(l_linenumber, l_orderkey);

ALTER TABLE tpch20.nation
    ADD CONSTRAINT pk_nation PRIMARY KEY(n_nationkey);

ALTER TABLE tpch20.region
    ADD CONSTRAINT pk_region PRIMARY KEY(r_regionkey);

 
 ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT fk_partsupp_part FOREIGN KEY(ps_partkey) REFERENCES tpch20.part(p_partkey);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT fk_partsupp_supplier FOREIGN KEY(ps_suppkey) REFERENCES tpch20.supplier(s_suppkey);

ALTER TABLE tpch20.customer
    ADD CONSTRAINT fk_customer_nation FOREIGN KEY(c_nationkey) REFERENCES tpch20.nation(n_nationkey);

ALTER TABLE tpch20.orders
    ADD CONSTRAINT fk_orders_customer FOREIGN KEY(o_custkey) REFERENCES tpch20.customer(c_custkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT fk_lineitem_order FOREIGN KEY(l_orderkey) REFERENCES tpch20.orders(o_orderkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT fk_lineitem_part FOREIGN KEY(l_partkey) REFERENCES tpch20.part(p_partkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT fk_lineitem_supplier FOREIGN KEY(l_suppkey) REFERENCES tpch20.supplier(s_suppkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT fk_lineitem_partsupp FOREIGN KEY(l_partkey, l_suppkey)
        REFERENCES tpch20.partsupp(ps_partkey, ps_suppkey);

-- 1.4.2.4 - 1

ALTER TABLE tpch20.part
    ADD CONSTRAINT chk_part_partkey CHECK(p_partkey >= 0);

ALTER TABLE tpch20.supplier
    ADD CONSTRAINT chk_supplier_suppkey CHECK(s_suppkey >= 0);

ALTER TABLE tpch20.customer
    ADD CONSTRAINT chk_customer_custkey CHECK(c_custkey >= 0);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT chk_partsupp_partkey CHECK(ps_partkey >= 0);

ALTER TABLE tpch20.region
    ADD CONSTRAINT chk_region_regionkey CHECK(r_regionkey >= 0);

ALTER TABLE tpch20.nation
    ADD CONSTRAINT chk_nation_nationkey CHECK(n_nationkey >= 0);

-- 1.4.2.4 - 2

ALTER TABLE tpch20.part
    ADD CONSTRAINT chk_part_size CHECK(p_size >= 0);

ALTER TABLE tpch20.part
    ADD CONSTRAINT chk_part_retailprice CHECK(p_retailprice >= 0);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT chk_partsupp_availqty CHECK(ps_availqty >= 0);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT chk_partsupp_supplycost CHECK(ps_supplycost >= 0);

ALTER TABLE tpch20.orders
    ADD CONSTRAINT chk_orders_totalprice CHECK(o_totalprice >= 0);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_quantity CHECK(l_quantity >= 0);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_extendedprice CHECK(l_extendedprice >= 0);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_tax CHECK(l_tax >= 0);

-- 1.4.2.4 - 3

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_discount CHECK(l_discount >= 0.00 AND l_discount <= 1.00);

-- 1.4.2.4 - 4

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_ship_rcpt CHECK(l_shipdate <= l_receiptdate);

 

728x90
저작자표시 비영리 변경금지

'IT_Engineer > DBA' 카테고리의 다른 글

[Sybase IQ] 데모 설치하기  (0) 2022.03.30
[Tibero] java.sql.SQLException: JDBC-5072, 티베로 버그  (3) 2022.03.01
[Oracle] ORA-01045: user lacks CREATE SESSION privilege; logon denied  (0) 2022.01.20
[DB/ORACLE] 테이블 및 컬럼 조회  (1) 2021.08.10
[DB/Tibero] JDBC-8026:Invalid identifier 에러  (0) 2020.12.22

댓글()

[Sybase IQ] 데모 설치하기

IT_Engineer/DBA|2022. 3. 30. 18:06
728x90

SYBASE IQ 설치

  • Sybase IQ를 설치하고 활용하다보니 너무 가이드도 없고, 커뮤니티도 적어서 누군가에게 도움이 되길 바라며 기록을 한다.

0. 환경

  • OS (Linux)
    [root@hd8 ~]# uname -a Linux hd8 3.10.0-1160.11.1.el7.x86_64 #1 SMP Fri Dec 18 16:34:56 UTC 2020 x86_64 x86_64 86_64 GNU/Linux`
  • Binary
  • SAP 홈페이지(https://www.sap.com/korea/products/sybase-iq-big-data-management.html)
  • Request a demo (Software Free Trials) 클릭 후 신청양식 제출을 통해 demo binary 다운
  • 본 가이드에서는 "sybase_bin64.tar" 파일 사용
  • tar 풀기

1. 설치하기

[root@g1 ~\]# cd /data/sybase/SYBASE\_HOME/

[root@g1 SYBASE\_HOME\]# ls -al


합계 24  
drwxr-xr-x 17 root root 4096 2월 16 14:10 .  
drwxrwxr-x 4 sybase root 91 2월 16 14:02 ..  
drwxrwxr-x 14 root root 170 2월 16 14:11 COCKPIT-4  
drwxrwxr-x 17 root root 262 2월 16 14:11 IQ-16\_1  
lrwxrwxrwx 1 root root 21 2월 16 14:05 IQ.csh -> ./IQ-16\_1/IQ-16\_1.csh  
lrwxrwxrwx 1 root root 20 2월 16 14:05 IQ.sh -> ./IQ-16\_1/IQ-16\_1.sh  
drwxrwxr-x 15 root root 197 2월 16 14:06 OCS-16\_0  
\-rwxr-xr-x 1 root root 1172 2월 16 14:10 SYBASE.csh  
\-rw-r--r-- 1 root root 764 2월 16 14:10 SYBASE.env  
\-rwxr-xr-x 1 root root 1010 2월 16 14:10 SYBASE.sh  
drwxrwxr-x 6 root root 59 2월 16 14:09 SYSAM-2\_0  
drwxr-xr-x 2 root root 24 2월 16 14:04 Sybase\_Install\_Registry  
drwxrwxr-x 59 root root 4096 2월 16 14:05 charsets  
drwxrwxr-x 3 root root 21 2월 16 14:05 collate  
drwxrwxr-x 2 root root 65 2월 16 14:06 config  
drwxrwxr-x 9 root root 162 2월 16 14:09 jConnect-16\_0  
drwxr-xr-x 4 27940 sybase 146 8월 12 2015 jre  
drwxrwxr-x 3 root root 18 2월 16 14:09 jutils-3\_0  
drwxrwxr-x 4 root root 55 2월 16 14:06 locales  
drwxr-xr-x 2 root root 4096 2월 16 14:14 log  
drwxrwxr-x 4 root root 62 2월 16 14:04 shared  
drwxr-xr-x 4 root root 33 2월 16 14:04 sybuninstall

\[root@g1 SYBASE\_HOME\]# cd ./IQ-16\_1/  
\[root@g1 IQ-16\_1\]# ls -al  
합계 36  
drwxrwxr-x 17 root root 262 2월 16 14:11 .  
drwxr-xr-x 17 root root 4096 2월 16 14:10 ..  
drwxrwxr-x 3 root root 26 2월 16 14:08 DBACOCKPIT  
\-rwxr-xr-x 1 root root 1617 2월 16 14:05 IQ-16\_1.csh  
\-rwxr-xr-x 1 root root 1649 2월 16 14:05 IQ-16\_1.sh  
drwxrwxr-x 2 root root 28 2월 16 14:04 Sysam  
drwxrwxr-x 4 root root 4096 2월 17 17:29 bin64  
drwxrwxr-x 4 root root 135 2월 16 14:08 demo  
drwxr-xr-x 2 root root 34 2월 16 14:11 install  
drwxrwxr-x 2 root root 4096 2월 16 14:08 java  
drwxrwxr-x 2 root root 4096 2월 16 14:08 lib64  
drwxrwxrwx 2 root root 4096 2월 21 14:50 logfiles  
drwxrwxr-x 2 root root 156 2월 16 14:08 lsunload  
drwxrwxr-x 2 root root 4096 2월 16 14:08 res  
drwxrwxr-x 5 root root 56 2월 16 14:08 samples  
drwxrwxr-x 2 root root 115 2월 16 14:08 scripts  
drwxrwxr-x 11 root root 122 2월 16 14:08 sdk  
lrwxrwxrwx 1 root root 9 2월 16 14:08 shared -> ../shared  
drwxrwxr-x 2 root root 25 2월 16 14:08 tix  
drwxrwxr-x 2 root root 4096 2월 16 14:08 unload

SYBASE 경로 변수 export

[root@g1 bin64\]# export IQDIR16='/data/sybase/SYBASE\_HOME/IQ-16\_1/'  
[root@g1 bin64\]# export SYBASE='/data/sybase/SYBASE\_HOME/'

# DEMO 구축 shell 파일 - dbisqlc, stop\_iq, start\_iq, dbstop 경로 지정
[root@g1 bin64\]# vi ./mkiqdemo.sh

# IQ DEMO 구축
# mkiqdemo.sh 쉘파일 실행 -dba 계정(User ID) -pwd 비밀번호(6글자 이상)
[root@g1 bin64\]# sh ./mkiqdemo.sh -dba DBA -pwd sybase

Warning: You are running this script as 'root'.  
Sybase strongly recommend that servers not be run with root privileges.

This script will create the iqdemo database in the current  
directory. An existing iqdemo database in this directory  
will need to be shutdown and over-written.

# Y로 진행 (대문자 Y로 답할 것!)
Continue <Y/N>? Y

Checking system ...
The following 1 server(s) are owned by 'root'

## Owner PID Started CPU Time Additional Information

---

1: root 31692 14:58 00:00:02 SVR:bld\_demo\_9000 DB:none PORT:9000  
/data/sybase/SYBASE\_HOME/IQ-16\_1/bin64/iqsrv16 @bld\_demo\_9000.cfg -gc 20 -gl all -ti 4400 -gn 25 -o /data/sybase/SYBASE\_HOME/IQ-16\_1/logfiles/bld\_dem  
\--

Please note that 'stop_iq' will shut down a server completely
without regard for users, connections, or load process status.
For more control, use the 'dbstop' utility, which has options
that control stopping servers based on active connections.


Do you want to stop the server displayed above <Y/N>? Y

Shutting down server (31692) ...  
Checkpointing server (31692) ...  
Server shutdown.

\-rw-r--r-- 1 root root 546 2월 21 14:59 iqdemo.cfg  
\-r--r--r-- 1 root root 4505600 2월 21 14:59 iqdemo.db  
\-rw-r--r-- 1 root root 104857600 2월 21 14:59 iqdemo.iq  
\-rw-r--r-- 1 root root 41542 2월 21 14:59 iqdemo.iqmsg  
\-rw-r--r-- 1 root root 26214400 2월 21 14:59 iqdemo.iqtmp  
\-rw-r--r-- 1 root root 469 2월 21 14:59 iqdemo.lmp  
\-r-------- 1 root root 327680 2월 21 14:59 iqdemo.log  
\-rw-r--r-- 1 root root 104857600 2월 21 14:59 iqdemo\_main.iq

The iqdemo database already exists. Overwrite <Y/N>? Y

Removing current iqdemo database ...  
Removed.

Starting Server ... bld\_demo\_9332

Starting server bld\_demo\_9332 on g1 at port 9332 (02/21 15:01:01)

Run Directory : /data/sybase/SYBASE\_HOME/IQ-16\_1/bin64  
Server Executable : /data/sybase/SYBASE\_HOME/IQ-16\_1/bin64/iqsrv16  
Server Output Log : /data/sybase/SYBASE\_HOME/IQ-16\_1/logfiles/bld\_demo\_9332.0001.srvlog  
Server Version : 16.1.030.1031/sp03  
Open Client Version : N/A  
User Parameters : '@bld\_demo\_9332.cfg'  
Default Parameters : -gc 20 -gl all -ti 4400 -gn 25

I. 02/21 15:01:02.757645 SAP IQ  
I. 02/21 15:01:02.757719 Version 16.1  
I. 02/21 15:01:02.757744 (64bit mode)  
I. 02/21 15:01:02.757776 Copyright 1992-2017 by SAP AG or an SAP affiliate company. All rights reserved  
I. 02/21 15:01:02.757795 Copyright (c) 2018 SAP SE or an SAP affiliate company.  
I. 02/21 15:01:02.757811 All rights reserved.  
I. 02/21 15:01:02.757833 Use of this software is governed by the SAP Software Use Rights Agreement.  
I. 02/21 15:01:02.757854 Refer to [http://www.sap.com/about/agreements.html](http://www.sap.com/about/agreements.html).  
I. 02/21 15:01:02.757869  
I. 02/21 15:01:02.757899 Processors detected: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)  
I. 02/21 15:01:02.757941 This server is licensed to use: all logical processors in the system  
I. 02/21 15:01:02.757964 Processors in use by server: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)  
I. 02/21 15:01:02.757999 Running Linux 3.10.0-1160.11.1.el7.x86\_64 #1 SMP Fri Dec 18 16:34:56 UTC 2020 on X86\_64  
I. 02/21 15:01:02.758040 Server built for X86\_64 processor architecture  
I. 02/21 15:01:02.761191 49720K of memory used for caching  
I. 02/21 15:01:02.761237 Minimum cache size: 49728K, maximum cache size: 262144K  
I. 02/21 15:01:02.761276 Using a maximum page size of 4096 bytes  
I. 02/21 15:01:02.763989 Multiprogramming level: 25  
I. 02/21 15:01:02.764016 Automatic tuning of multiprogramming level is disabled

\=============================================================  
IQ server starting with:  
10 connections ( -gm )  
26 cmd resources ( -iqgovern )  
460 threads ( -iqmt )  
512 Kb thread stack size ( -iqtss )  
235520 Kb thread memory size ( -iqmt \* -iqtss )  
8 IQ number of cpus ( -iqnumbercpus )  
0 MB maximum size of IQMSG file ( -iqmsgsz )  
0 copies of IQMSG file archives ( -iqmsgnum )  
64 MB maximum size of main buffer cache ( -iqmc )  
64 MB maximum size of temp buffer cache ( -iqtc )  
2048 MB maximum size of large memory pool ( -iqlm )  
0 MB maximum size of heap memory ( -iqmem )  
2048 MB maximum size of RLV memory ( -iqrlvmem )  
\=============================================================

I. 02/21 15:01:02.785503 Database server started at Mon Feb 21 2022 15:01  
I. 02/21 15:01:02.786100 Trying to start SharedMemory link ...  
I. 02/21 15:01:02.786343 SharedMemory link started successfully  
I. 02/21 15:01:02.786392 Trying to start TCPIP link ...  
I. 02/21 15:01:02.787082 Starting on port 9332  
I. 02/21 15:01:07.787128 TCPIP link started successfully  
I. 02/21 15:01:07.787546 Now accepting requests  
New process id is 526

Server started successfully

Started.  
Connecting to server via TCPIP ...  
Using: uid=DBA;pwd=sql123;eng=bld\_demo\_9332;dbn=utility\_db;links=tcpip{host=g1;port=9332}

Creating database ...  
Demo database created.

Shutting down server ...  
SQL Anywhere Stop Server Utility Version 17.0.9.1031

\-rw-r--r-- 1 root root 546 2월 21 15:01 iqdemo.cfg  
\-r--r--r-- 1 root root 4505600 2월 21 15:01 iqdemo.db  
\-rw-r--r-- 1 root root 104857600 2월 21 15:01 iqdemo.iq  
\-rw-r--r-- 1 root root 41538 2월 21 15:01 iqdemo.iqmsg  
\-rw-r--r-- 1 root root 26214400 2월 21 15:01 iqdemo.iqtmp  
\-rw-r--r-- 1 root root 469 2월 21 15:01 iqdemo.lmp  
\-r-------- 1 root root 327680 2월 21 15:01 iqdemo.log  
\-rw-r--r-- 1 root root 104857600 2월 21 15:01 iqdemo\_main.iq

# DB 생성 성공!!
Demo database created successfully

# iqdemo.cfg 파일을 통해서 서버 생성
\[root@g1 bin64\]# ./start\_iq @iqdemo.cfg iqdemo.db

Starting server g1\_iqdemo on g1 at port 2638 (02/25 22:23:06)

Run Directory : /home/sybase/SAP/IQ-16\_1/bin64  
Server Executable : /home/sybase/SAP/IQ-16\_1/bin64/iqsrv16  
Server Output Log : /home/sybase/SAP/IQ-16\_1/logfiles/g1\_iqdemo.0001.srvlog  
Server Version : 16.1.030.1031/sp03  
Open Client Version : N/A  
User Parameters : '@iqdemo.cfg' 'iqdemo.db'  
Default Parameters : -ti 4400 -gn 25

I. 02/25 22:23:08.440692 SAP IQ  
I. 02/25 22:23:08.440752 Version 16.1  
I. 02/25 22:23:08.440777 (64bit mode)  
I. 02/25 22:23:08.440806 Copyright 1992-2017 by SAP AG or an SAP affiliate company. All rights reserved  
I. 02/25 22:23:08.440836 Copyright (c) 2018 SAP SE or an SAP affiliate company.  
I. 02/25 22:23:08.440857 All rights reserved.  
I. 02/25 22:23:08.440880 Use of this software is governed by the SAP Software Use Rights Agreement.  
I. 02/25 22:23:08.440904 Refer to [http://www.sap.com/about/agreements.html](http://www.sap.com/about/agreements.html).  
I. 02/25 22:23:08.440925  
I. 02/25 22:23:08.443179 Processors detected: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)  
I. 02/25 22:23:08.443661 This server is licensed to use: all logical processors in the system  
I. 02/25 22:23:08.443693 Processors in use by server: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)  
I. 02/25 22:23:08.444032 Running Linux 3.10.0-1160.11.1.el7.x86\_64 #1 SMP Fri Dec 18 16:34:56 UTC 2020 on X86\_64  
I. 02/25 22:23:08.444070 Server built for X86\_64 processor architecture  
I. 02/25 22:23:08.539513 49720K of memory used for caching  
I. 02/25 22:23:08.539553 Minimum cache size: 49728K, maximum cache size: 262144K  
I. 02/25 22:23:08.539575 Using a maximum page size of 4096 bytes  
I. 02/25 22:23:08.542228 Multiprogramming level: 25  
I. 02/25 22:23:08.542263 Automatic tuning of multiprogramming level is disabled

\=============================================================  
IQ server starting with:  
10 connections ( -gm )  
26 cmd resources ( -iqgovern )  
460 threads ( -iqmt )  
512 Kb thread stack size ( -iqtss )  
235520 Kb thread memory size ( -iqmt \* -iqtss )  
8 IQ number of cpus ( -iqnumbercpus )  
0 MB maximum size of IQMSG file ( -iqmsgsz )  
0 copies of IQMSG file archives ( -iqmsgnum )  
64 MB maximum size of main buffer cache ( -iqmc )  
64 MB maximum size of temp buffer cache ( -iqtc )  
2048 MB maximum size of large memory pool ( -iqlm )  
0 MB maximum size of heap memory ( -iqmem )  
2048 MB maximum size of RLV memory ( -iqrlvmem )  
\=============================================================

I. 02/25 22:23:08.816707 Starting database "iqdemo" (/home/sybase/SAP/IQ-16\_1/bin64/iqdemo.db) at Fri Feb 25 2022 22:23  
I. 02/25 22:23:08.828570 Database recovery in progress  
I. 02/25 22:23:08.828598 Last checkpoint at Tue Feb 22 2022 11:35  
I. 02/25 22:23:08.828643 Checkpoint log...  
I. 02/25 22:23:09.223211 Transaction log: iqdemo.log...  
I. 02/25 22:23:09.299830 Rollback log...  
I. 02/25 22:23:09.327415 Checkpointing...  
I. 02/25 22:23:09.327483 Starting checkpoint of "iqdemo" (iqdemo.db) at Fri Feb 25 2022 22:23  
I. 02/25 22:23:09.482760 Finished checkpoint of "iqdemo" (iqdemo.db) at Fri Feb 25 2022 22:23  
I. 02/25 22:23:09.482800 Recovery complete  
I. 02/25 22:23:09.706122 Update previous log GUID to: 8d62f71a-9372-11ec-8000-ff2b62b1fccc  
Update current log GUID to :f3142ad8-96d4-11ec-8000-c69e869a5e19  
I. 02/25 22:23:09.807556 Database "iqdemo" (iqdemo.db) started at Fri Feb 25 2022 22:23  
I. 02/25 22:23:09.846863 IQ Server g1\_iqdemo.  
I. 02/25 22:23:09.847295 Starting checkpoint of "iqdemo" (iqdemo.db) at Fri Feb 25 2022 22:23  
I. 02/25 22:23:10.024374 Finished checkpoint of "iqdemo" (iqdemo.db) at Fri Feb 25 2022 22:23  
I. 02/25 22:23:10.032852 Database server started at Fri Feb 25 2022 22:23  
I. 02/25 22:23:10.032967 Trying to start SharedMemory link ...  
I. 02/25 22:23:10.033315 SharedMemory link started successfully  
I. 02/25 22:23:10.033364 Trying to start TCPIP link ...  
I. 02/25 22:23:10.033992 Starting on port 2638  
I. 02/25 22:23:15.034120 TCPIP link started successfully  
I. 02/25 22:23:15.056954 Now accepting requests  
New process id is 19425

Server started successfully
728x90
저작자표시 비영리 변경금지

'IT_Engineer > DBA' 카테고리의 다른 글

DB 별 TPC-H 로드 방법  (1) 2023.10.19
[Tibero] java.sql.SQLException: JDBC-5072, 티베로 버그  (3) 2022.03.01
[Oracle] ORA-01045: user lacks CREATE SESSION privilege; logon denied  (0) 2022.01.20
[DB/ORACLE] 테이블 및 컬럼 조회  (1) 2021.08.10
[DB/Tibero] JDBC-8026:Invalid identifier 에러  (0) 2020.12.22

댓글()

[Tibero] java.sql.SQLException: JDBC-5072, 티베로 버그

IT_Engineer/DBA|2022. 3. 1. 18:33
728x90

DROP TABLE로 테스트 용 테이블들을 정리하는 중

 

한 테이블을 DROP 하려고 하니, 아래와 같은 에러가 발생했다.

 

[18:14:20.215]java.sql.SQLException: JDBC-5072:Failure converting NUMBER to or from a native type.

 

뭐지???? 처음보는 에러에 살짝 당황스러워서 검색해보니, 티베로 버그인 듯 하다.

ROW COUNT가 NULL이 되면서 생긴 에러 같은데,
해당 계정의 테이블에 대해서만 ROW COUNT가 NULL인 OBJECT ID를 조회하니 나는 안 되더라,,
일단 해당 전체 테이블 대상으로 OBJECT ID가 안 나오길래 전체 테이블에서 에러가 되는 요인을 찾기로 했다.

  1. TIBERO 접속
    - 리눅스 : tbsql sys/tibero
    - TB STUDIO / TBADMIN 등 유틸 사용하는 경우 : DBA계정으로 접속

   2. ROW COUNT가 NULL인 OBJECT ID 검색

select a.object_id from dba_objects a, (select obj_id, row_cnt from sys._dd_tbl ) b where a.object_id= b.obj_id and b.row_cnt is null;

   3. 해당되는 OBJECT_ID들 UPDATE

update sys._dd_tbl set row_cnt = 0 where obj_id in ( select a.object_id from dba_objects a, (select obj_id, row_cnt from sys._dd_tbl ) b where a.object_id= b.obj_id and b.row_cnt is null);

   4. 다시 한 번 에러 났던 테이블에 대해서

DROP TABLE {테이블 이름};

 

728x90
저작자표시 비영리 변경금지

'IT_Engineer > DBA' 카테고리의 다른 글

DB 별 TPC-H 로드 방법  (1) 2023.10.19
[Sybase IQ] 데모 설치하기  (0) 2022.03.30
[Oracle] ORA-01045: user lacks CREATE SESSION privilege; logon denied  (0) 2022.01.20
[DB/ORACLE] 테이블 및 컬럼 조회  (1) 2021.08.10
[DB/Tibero] JDBC-8026:Invalid identifier 에러  (0) 2020.12.22

댓글()

[Oracle] ORA-01045: user lacks CREATE SESSION privilege; logon denied

IT_Engineer/DBA|2022. 1. 20. 17:36
728x90

1. 현상

 - g1 이란 user를 생성해서

- 로그인하려고 했는데 아래와 같이,

   " ORA-01045: user G1 lacks CREATE SESSION privilege; logon denied " 에러 발생

 

2. 원인

 - 새로 만든 g1이란 User가 SESSION  생성에 대한 권한이 없어서 발생

 

3. 해결

 - grant 로 session 생성 권한 부여

728x90
저작자표시 비영리 변경금지

'IT_Engineer > DBA' 카테고리의 다른 글

[Sybase IQ] 데모 설치하기  (0) 2022.03.30
[Tibero] java.sql.SQLException: JDBC-5072, 티베로 버그  (3) 2022.03.01
[DB/ORACLE] 테이블 및 컬럼 조회  (1) 2021.08.10
[DB/Tibero] JDBC-8026:Invalid identifier 에러  (0) 2020.12.22
[Tibero] 티베로 설치 및 DB 생성 예시  (0) 2020.10.28

댓글()

[DB/ORACLE] 테이블 및 컬럼 조회

IT_Engineer/DBA|2021. 8. 10. 18:05
728x90

1. 테이블 조회하기


  - 테이블 목록 조회

     SELECT * FROM all_all_tables

     SELECT * FROM dba_tables

     SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE'

 

  - 테이블 목록 조회 (접속한 계정)

     SELECT * FROM tabs

     SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'

     SELECT * FROM USER_TABLES

 

  - 테이블 코멘트 조회

     SELECT * FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = '테이블명'

     SELECT * FROM USER_TAB_COMMENTS

 

2. 컬럼 조회하기

 

  - 컬럼 조회

     SELECT * FROM COLS WHERE TABLE_NAME = '테이블명'

     SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '테이블명'

     SELECT * FROM USER_TAB_COLUMNS

 

  - 컬럼 코멘트 조회

     SELECT * FROM USER_COL_COMMENTS



출처: https://jwklife.tistory.com/45 [인 생]

728x90
저작자표시 비영리 변경금지

'IT_Engineer > DBA' 카테고리의 다른 글

[Sybase IQ] 데모 설치하기  (0) 2022.03.30
[Tibero] java.sql.SQLException: JDBC-5072, 티베로 버그  (3) 2022.03.01
[Oracle] ORA-01045: user lacks CREATE SESSION privilege; logon denied  (0) 2022.01.20
[DB/Tibero] JDBC-8026:Invalid identifier 에러  (0) 2020.12.22
[Tibero] 티베로 설치 및 DB 생성 예시  (0) 2020.10.28

댓글()

[DB/Tibero] JDBC-8026:Invalid identifier 에러

IT_Engineer/DBA|2020. 12. 22. 12:58
728x90

DB 작업을 하다가 y 컬럼만 조회하고 싶어서

 

SELECT y
FROM DATA_TABLE ;

 

라고 했는데 뜨는  " JDBC - 8026 : Invalid indentifier. at line 1, column 9 osfg null: SELECT y  ^ " 에러

 

찾아보니 컬럼 앞 뒤로 " " 표시를 해주었어야 했는데, 혹시나 나같은 경우가 있을까해서 정리한다.

 

" SQL 명령문 안에 적은 컬럼명과 실제 데이터 베이스 내 컬럼명이 다른 경우 발생(부적합한 식별자)하는 에러 "

 

> 해결방법 

 1) 해당 컬럼이 존재하는 지 확인

 2) 컬럼명 오타 확인 ( " " 로 묶인 경우는 대소문자도 구분)

 3) 컬럼명을 " " 혹은 ' ' 으로 감싸보기

   - 작은 따옴표(' ') : 값으로 취급 / 큰 따옴표 (" ") : 따옴표 내 그대로 문자 이름으로 처리

 4) 컬럼명에 첫 글자로 숫자 사용 혹은 특수 문자(기호) 를 사용하고 있는지 확인 → 수정

 5) 컬럼명에 Oracle 예약어를 사용하고 있는지 확인 → 수정

 

-- 수정 후

SELECT "y"
FROM data
;

 

이런 사소한 실수는 적어놓아야 시간을 아끼고, 다시 같은 실수를 안할 수 있다. ㅎㅎ

 

728x90

'IT_Engineer > DBA' 카테고리의 다른 글

[Sybase IQ] 데모 설치하기  (0) 2022.03.30
[Tibero] java.sql.SQLException: JDBC-5072, 티베로 버그  (3) 2022.03.01
[Oracle] ORA-01045: user lacks CREATE SESSION privilege; logon denied  (0) 2022.01.20
[DB/ORACLE] 테이블 및 컬럼 조회  (1) 2021.08.10
[Tibero] 티베로 설치 및 DB 생성 예시  (0) 2020.10.28

댓글()

[Tibero] 티베로 설치 및 DB 생성 예시

IT_Engineer/DBA|2020. 10. 28. 09:00
728x90

! 기초 구축 환경

 - VM 접속 정보 : CentOS 7 64bit VM 사용

 

! 구축 내용

  1. Tibero 데모 라이선스 발급
  2. 준비된 VM 환경에서 CentOS 기반으로 Tibero 설치 및 테스트

1. Tibero Binary 다운로드 및 Technet (테크넷) 데모 라이선스 발급

! TechNet 에서 Tibero Binary 다운로드

  • TechNet 가입 및 로그인 (https://technet.tmaxsoft.com/)
  • TechNet > 다운로드 > 데이터베이스 > Tibero > Tibero6
  • Tibero6 > 다운로드 > Linux > Linux(x86) 64-bit > 463.08MB 크기 파일 다운로드

TechNet 바이너리 다운로드 화면

 

다운로드 버튼 클릭 클릭!!
linux64(tibero6-bin-FS07_CS_19-12-linux-64~.tar.gz) tar파일 다운로드

 

! 데모 라이선스 발급

  • ★ HostName은 커널에서 uname –n 명령어 확인 후 동일하게 작성
  • 제출하기 클릭 후 메일확인하여 첨부파일 xml
  • 파일질라를 통해 디렉토리 license로 보내기

TechNet 홈페이지 화면에서 데모 라이선스 신청 고고!
데모라이선스 발급 신청서 작성 후 제출

2. Tibero6 설치를 위한 환경 설정

! JDK 5.0 이상 설치

  • 설치 가능 확인 및 1.8버전 설치
# 설치 가능 리스트 확인
yum list java*jdk-devel 

# 위 리스트 중 1.8.0 버전 설치
yum install java-1.8.0-openjdk-devel.x86_64 

# jdk 설치 확인 
rpm –qa java*jdk-devel 

# 설치된 버전 확인 ( 제대로 설치 되었다면, javac 1.8.0_101 라고 뜸)
javac –version 

! 커널 파라미터 설정

  • Linux 커널 파라미터 설정

  • kernel parameter 동적 변경 > kernel이 살아있는 동안만 변경 (일시적)

 

 

#커널 파라미너 확인
sysctl –a 
확인 변경
sysctl -a | grep sem sysctl -w kernel.sem="10000 32000 10000 10000"
sysctl -a | grep shmmax sysctl -w kernel.shmmax=4090912768
sysctl -a | grep file-max sysctl -w fs.file-max=65536
sysctl -a | grep shmall sysctl -w kernel.shmall=2097152
sysctl -a | grep shmmni sysctl –w kernel.shmmni=4096 #물리 메모리의 1/2
sysctl-a | grep ip_local_port_range sysctl-w net.ipv4.ip_local_port_range =“1024 65000”

 

  • 이후 nomount 모드로 변경할 때, 위 kernel parameter와 VM 환경이 맞아야 동작
    ( top 명령어로 환경 확인해서 kernel.shmmni 를 물리 메모리의 1/2로 맞추기!)
  • kernel parameter 정적 변경 > 영구적인 변경
#시스템 환경파일을 vi 에디터로 편집
vi /etc/sysctl.conf
#kernel parameter 항목을 sysctl.conf 파일에 추가
#kernel.shmmax 값은 free –kb로 확인한 mem의 total 값의 1/2로 설정
fs.file-max = 65536kernel.sem = 10000 32000 10000 10000kernel.shmmax = 4090912768

#sysctl.conf에 추가한 후 sysctl –p 명령으로 동적 적용 가능

 

3. 호스트명/ 포트/ 계정 설정

! 호스트명과 포트 번호, 시스템 계정 확인

  • 라이선스를 요청할 때
uname –n # 호스트 명 확인 > tibero6 라이선스 발급 시 필요!

 

  • 포트 번호 : Tibero 가 가동할 때 부여될 포트 번호 확인

 

  • 그룹 및 사용자 생성
groupadd dba #dba 이름의 그룹 추가
useradd –md /home/tibero6 –g dba –p tibero –s /bin/bash tibero6
# 기본 디렉토리를 /home/tibero6로 만들고(-md), dba그룹의(-g),
tibero라는 비밀번호로(-p), /bin/bash(-s) 의 tibero6(맨 마지막)라는 사용자 생성

 

  • 사용자 환경변수 설정
    - 사용자 계정 홈 디렉토리에 있는 환경 파일 수정
# tibero6 계정으로 접속
su – tibero6 
 # 디렉토리 내 파일 확인
ls –al


# bash shell일 경우 .bash_profile로 환경파일 수정
# ---- vi.bash_profile 하단부터 #bash_profile 적용 전까지 수정 내용 -----
vi .bash_profile 


### JAVA ENV ###
export JAVA_HOME=/usr/java6
export JDK_HOME=$JAVA_HOME
export PATH=$JAVA_HOME/bin:/usr/local/bin:$PATH

######## TIBERO ENV ########
export TB_HOME=/home/tibero6/tibero6
export TB_SID=t6
export TB_PROF_DIR=$TB_HOME/bin/prof
export PATH=.:$TB_HOME/bin:$TB_HOME/client/bin:~/tbinary/monitor:$PATH
export LD_LIBRARY_PATH=$TB_HOME/lib:$TB_HOME/client/lib:$LD_LIBRARY_PATH
export SHLIB_PATH=$LD_LIBRARY_PATH:$SHLIB_PATH
export LIBPATH=$LD_LIBRARY_PATH:$LIBPATH

######## TIBERO alias ######## (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'
alias tbcliv='vi ${TB_HOME}/client/config/tbnet_alias.tbr'
alias tbdata='cd $TB_HOME/tbdata'
alias tbi='cd ~/tbinary'alias clean='tbdown clean'
alias dba='tbsql sys/tibero'
alias tm='cd ~/tbinary/monitor;monitor;cd -'

# bash_profile 적용
. .bash_profile 

#티베로 계정으로 접속

echo $TB_HOME 

 

! Tibero Binary 업로드 및 설치

  • FileZilla를 통한 이동

 

  • shell에서 tibero6 디렉토리 접속 후 알집 풀기
# home/tibero6 디렉토리에 접속
cd /home/tibero6 
#알집 풀기
tar –xvzf tibero6-bin-FS07_CS_1912-linux64-174424-opt.tar.gz

 

  • License xml파일 업로드
    - hostname에 맞는 license.xml 파일을 발급 후 $TB_HOME/license 디렉토리 생성 후 FileZilla를 통한 이동

 

! 초기 환경파일 생성

  • $TB_SID.tip 파일 생성: Tibero 파라미터 파일
  • tbdsn.tbr 파일 생성 : Tibero Client 접속 설정 파일
  • psm_commands 파일 생성 : psm compile을 위한 command 파일 생성
cd $TB_HOME/config
./gen_tip.sh

 

! Tibero 파라미터 파일($TB_HOME/config/$TB_SID.tip) 수정 _ 생략

  • exit > root 계정에서 free –kb 명령어(물리 메모리 byte 단위로 보는 명령어) 후,
  • 물리 메모리를 확인 후 위에 설정한 config 상태의 2배가 맞는지 확인

  • Mem의 total 확인 : 8181825536 이므로 kernel.shmmax = 4090912768

4. Tibero 데이터베이스 생성

  • Tibero를 nomount 모드로 부팅 및 system 유저로 접속
tbboot nomount # nomount(Tibero의 프로세스만 기동하는 모드) 모드로 부팅
tbsql sys/tibero # sys 계정으로 (비밀번호 tibero)로 tbsql 접속
  • DataBase 생성 스크립트
SQL> CREATE DATABASE “t6"
        USER sys IDENTIFIED BY tibero
        MAXDATAFILES 256 CHARACTER SET MSWIN949 -- UTF8, EUCKR, ASCII
        LOGFILE
            GROUP 0 ('log01.log','log02.log') SIZE 10M,
            GROUP 1 ('log11.log','log12.log') SIZE 10M,
            GROUP 2 ('log21.log','log22.log') SIZE 10M
        MAXLOGFILES 100
        MAXLOGMEMBERS 8
        NOARCHIVELOG
        DATAFILE 'system001.dtf' SIZE 10M
            AUTOEXTEND ON NEXT 8M MAXSIZE 3G
        DEFAULT TEMPORARY TABLESPACE TEMP
            TEMPFILE 'temp001.dtf' SIZE 10M
            AUTOEXTEND ON NEXT 8M MAXSIZE 3G
            EXTENT MANAGEMENT LOCAL AUTOALLOCATE
        UNDO TABLESPACE UNDO
            DATAFILE 'undo001.dtf' SIZE 10M
            AUTOEXTEND ON NEXT 8M MAXSIZE 3G
            EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 

  • Tibero 재가동
tbdown # Tibero instance terminated (NORMAL mode).라는 말이 나와야 함
tbboot # tibero 부팅

 

  • Data Dictionary 및 System 패키지 생성
cd $TB_HOME/scripts
sh system.sh # 모든 사항은 y로 답
# 첫 번째 패스워드 : tibero , 두 번째 패스워드 : syscat
# 패스워드 입력없이 한 번에 실행하는 방법 :
# sh system.sh 실행 전에 system.sh 파일을 다음과 같이 수정


$ sh system.sh << EOF
> tibero

 

728x90

'IT_Engineer > DBA' 카테고리의 다른 글

[Sybase IQ] 데모 설치하기  (0) 2022.03.30
[Tibero] java.sql.SQLException: JDBC-5072, 티베로 버그  (3) 2022.03.01
[Oracle] ORA-01045: user lacks CREATE SESSION privilege; logon denied  (0) 2022.01.20
[DB/ORACLE] 테이블 및 컬럼 조회  (1) 2021.08.10
[DB/Tibero] JDBC-8026:Invalid identifier 에러  (0) 2020.12.22

댓글()
이전 1 다음

티스토리툴바