dm6安装过程

〇〇 · 发表于 2011-4-18 15:56

SELECT top 10
      C_COUNT,
      COUNT(*) AS CUSTDIST
FROM
      (
            SELECT
                     C_CUSTKEY,
                     COUNT(O_ORDERKEY)C_COUNT
            FROM
                     CUSTOMER LEFT OUTER JOIN ORDERS ON
                              C_CUSTKEY = O_CUSTKEY
                              AND O_COMMENT NOT LIKE '%special%requests%'
            GROUP BY
                     C_CUSTKEY
      ) AS C_ORDERS
GROUP BY
      C_COUNT
ORDER BY
      CUSTDIST DESC,
      C_COUNT DESC
;

      C_COUNT       CUSTDIST

1    0    500021

2    10    66157

3    9    65243

4    11    62072

5    8    58350

6    12    55821

7    13    49811

8    7    46847

9    19    46641

10    18    46259
10 rows got
time used: 458937.559(ms).
SQL>SQL>select count(*) from orders;
select count(*) from orders;

1    15000000
1 rows got
time used: 0.869(ms).
SQL>select count(*) from customer;
select count(*) from customer;

1    1500000
1 rows got
time used: 0.435(ms).

〇〇 · 发表于 2011-4-18 16:27

SQL>select top 3 r_name  from region;
select top 3 r_name  from region;

      r_name

1    AFRICA

2    AMERICA

3    ASIA
3 rows got
time used: 1.098(ms).
SQL>select r_name from region limit 3;
select r_name from region limit 3;

      r_name

1    AFRICA

2    AMERICA

3    ASIA
3 rows got
time used: 0.599(ms).
SQL>select r_name from region where rownum<=3;
select r_name from region where rownum<=3;

      r_name

1    AFRICA

2    AMERICA

3    ASIA
3 rows got
time used: 0.810(ms).

〇〇 · 发表于 2011-4-19 16:16

[root@redflag11012501 tpch2]# gzip --stdout -d /user1/app/oradata/tpch2/lineitem.tbl.gz >/user1/daa &
[1] 28085

[root@redflag11012501 tpch2]# date;split -l15000000 -d /user1/daa;date
2011年 04月 19日星期二 16:09:11 CST
[1]+ Done gzip --stdout -d /user1/app/oradata/tpch2/lineitem.tbl.gz > /user1/daa
2011年 04月 19日星期二 16:10:53 CST

[root@redflag11012501 tpch2]# ls -l x*
-rw-r--r-- 1 root root 1951033298 04-19 16:09 x00
-rw-r--r-- 1 root root 1962174224 04-19 16:10 x01
-rw-r--r-- 1 root root 1962182478 04-19 16:10 x02
-rw-r--r-- 1 root root 1960323740 04-19 16:10 x03

〇〇 · 发表于 2011-4-19 16:33

[root@redflag11012501 sql]# cat lineitemx.ctl
table=(lineitem)
col_num=(16)
col_sep=(|)
data_file=(/user1/app/oradata/tpch2/x00)
date_fmt=(YYYY-MM-DD)

[root@redflag11012501 sql]# cd ..
[root@redflag11012501 bin]# ./dmloader userid=sysdba/sysdba control=sql/lineitemx.ctl log=sql/lineitem.log
create log file fail
login database:SYSTEM
start load file "/user1/app/oradata/tpch2/x00" to table "lineitem"
50000 rows batched

15000000 rows insert in all
457367(ms) used

jinguanding · 发表于 2011-4-19 22:18

OO 是啥数据库产品都在研究嘛.....顶下.....尤其支持我的地盘要不要成为此版块的版主啊.....

〇〇 · 发表于 2011-4-20 16:28

select
      l_returnflag,
      l_linestatus,
      sum(l_quantity) as sum_qty,
      sum(l_extendedprice) as sum_base_price,
      sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
      sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
      avg(l_quantity) as avg_qty,
      avg(l_extendedprice) as avg_price,
      avg(l_discount) as avg_disc,
      count(*) as count_order
from
      lineitem
where
      l_shipdate <= date '1998-12-01' - interval '109' day (3)
group by
      l_returnflag,
      l_linestatus
order by
      l_returnflag,
      l_linestatus
limit 100;

      l_returnflag          l_linestatus          sum_qty       sum_base_price       sum_disc_price       sum_chargeavg_qty       avg_price             avg_disc             count_order

1    A    F    377518399.00 566065727797.25 537759104278.0656    559276670892.116819    25.500975    38237.151009.050007 14804077

2    N    F    9851614.00    14767438399.17  14028805792.2114       14590490998.366737    25.522448    38257.810660.049973 385998

3    N    O    733123780.00 1099304906498.07       1044335077295.6306    1086120726300.933136 25.497525    38233.044635       .049999 28752743

4    R    F    377732830.00 566431054976.00 538110922664.7677    559634780885.086257    25.508385    38251.219274.049997 14808183
4 rows got
time used: 409168.735(ms).

〇〇 · 发表于 2011-4-20 16:28

原帖由 jinguanding 于 2011-4-19 22:18 发表
OO 是啥数据库产品都在研究嘛.....顶下.....尤其支持我的地盘要不要成为此版块的版主啊.....

呵呵,帮忙推荐下..

〇〇 · 发表于 2011-4-22 07:01

select
      s_acctbal,
      s_name,
      n_name,
      p_partkey,
      p_mfgr,
      s_address,
      s_phone,
      s_comment
from
      part,
      supplier,
      partsupp,
      nation,
      region
where
      p_partkey = ps_partkey
      and s_suppkey = ps_suppkey
      and p_size = 33
      and p_type like '%STEEL'
      and s_nationkey = n_nationkey
      and n_regionkey = r_regionkey
      and r_name = 'MIDDLE EAST'
      and ps_supplycost = (
            select
                     min(ps_supplycost)
            from
                     partsupp,
                     supplier,
                     nation,
                     region
            where
                     p_partkey = ps_partkey
                     and s_suppkey = ps_suppkey
                     and s_nationkey = n_nationkey
                     and n_regionkey = r_regionkey
                     and r_name = 'MIDDLE EAST'
      )
order by
      s_acctbal desc,
      n_name,
      s_name,
      p_partkey
limit 100;

....

100 rows got
time used: 96486524.043(ms).
SQL>SQL>

〇〇 · 发表于 2011-4-25 13:04

ALTER TABLE ORDERS
ADD PRIMARY KEY (O_ORDERKEY);

-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references SUPPLIER;

ALTER TABLE PARTSUPP
ADD CONSTRAINT PARTSUPP_FK2 FOREIGN KEY (PS_PARTKEY) references PART;

-- For table ORDERS
ALTER TABLE ORDERS
ADD CONSTRAINT ORDERS_FK1 FOREIGN KEY  (O_CUSTKEY) references CUSTOMER;

-- For table LINEITEM
ALTER TABLE LINEITEM
ADD CONSTRAINT LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY)  references ORDERS;

ALTER TABLE LINEITEM
ADD CONSTRAINT LINEITEM_FK2 FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references
      PARTSUPP;

-- For table PART
ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);

time used: 14210.697(ms).
SQL>SQL>SQL>SQL>2 3 -- For table SUPPLIER
ALTER TABLE SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);

time used: 691.154(ms).
SQL>SQL>2 ALTER TABLE SUPPLIER
ADD CONSTRAINT SUPPLIER_FK1 FOREIGN KEY  (S_NATIONKEY) references NATION;

time used: 193.020(ms).
SQL>SQL>SQL>SQL>2 3
-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);

time used: 87118.346(ms).
SQL>SQL>SQL>SQL>2 3 -- For table CUSTOMER
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);

time used: 12403.566(ms).
SQL>SQL>2 ALTER TABLE CUSTOMER
ADD CONSTRAINT CUSTOMER_FK1 FOREIGN KEY  (C_NATIONKEY) references NATION;

time used: 3210.720(ms).
SQL>SQL>SQL>SQL>2 3

-- For table LINEITEM
ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

time used: 543594.582(ms).
SQL>SQL>SQL>SQL>2 3 -- For table ORDERS
ALTER TABLE ORDERS
ADD PRIMARY KEY (O_ORDERKEY);

time used: 120313.765(ms).
SQL>SQL>SQL>SQL>2 3 第3行: 'PARTSUPP_FK1'附近有语法错误
SQL>SQL>SQL>SQL>2 ALTER TABLE PARTSUPP
ADD CONSTRAINT PARTSUPP_FK2 FOREIGN KEY (PS_PARTKEY) references PART;

time used: 40301.053(ms).
SQL>SQL>SQL>SQL>2 3 -- For table ORDERS
ALTER TABLE ORDERS
ADD CONSTRAINT ORDERS_FK1 FOREIGN KEY  (O_CUSTKEY) references CUSTOMER;

time used: 106137.191(ms).
SQL>SQL>SQL>SQL>2 3 -- For table LINEITEM
ALTER TABLE LINEITEM
ADD CONSTRAINT LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY)  references ORDERS;

time used: 341882.766(ms).
SQL>SQL>SQL>SQL>2 3 ALTER TABLE LINEITEM
ADD CONSTRAINT LINEITEM_FK2 FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references
      PARTSUPP;

time used: 563711.689(ms).
SQL>SQL>SQL>SQL>SQL>SQL>SQL>SQL>SQL>
SQL>
SQL>
SQL>select
2          l_returnflag,
3          l_linestatus,
4          sum(l_quantity) as sum_qty,
5          sum(l_extendedprice) as sum_base_price,
6          sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
7          sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
      avg(l_quantity) as avg_qty,
      avg(l_extendedprice) as avg_price,
8 9 10       avg(l_discount) as avg_disc,
11       count(*) as count_order
12  from
13       lineitem
14  where
15       l_shipdate <= date '1998-12-01' - interval '109' day (3)
16  group by
17       l_returnflag,
18       l_linestatus
19  order by
20       l_returnflag,
21       l_linestatus
22  limit 100;

select
      l_returnflag,
      l_linestatus,
      sum(l_quantity) as sum_qty,
      sum(l_extendedprice) as sum_base_price,
      sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
      sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
      avg(l_quantity) as avg_qty,
      avg(l_extendedprice) as avg_price,
      avg(l_discount) as avg_disc,
      count(*) as count_order
from
      lineitem
where
      l_shipdate <= date '1998-12-01' - interval '109' day (3)
group by
      l_returnflag,
      l_linestatus
order by
      l_returnflag,
      l_linestatus
limit 100;

      l_returnflag          l_linestatus          sum_qty       sum_base_price       sum_disc_price       sum_chargeavg_qty       avg_price             avg_disc             count_order

1    A    F    377518399.00 566065727797.25 537759104278.0656    559276670892.116819    25.500975    38237.151009.050007 14804077

2    N    F    9851614.00    14767438399.17  14028805792.2114       14590490998.366737    25.522448    38257.810660.049973 385998

3    N    O    733123780.00 1099304906498.07       1044335077295.6306    1086120726300.933136 25.497525    38233.044635       .049999 28752743

4    R    F    377732830.00 566431054976.00 538110922664.7677    559634780885.086257    25.508385    38251.219274.049997 14808183
4 rows got
time used: 399240.725(ms).
SQL>SQL>select
2          s_acctbal,
3          s_name,
4          n_name,
5          p_partkey,
6          p_mfgr,
7          s_address,
8          s_phone,
9          s_comment
10  from
11       part,
12       supplier,
13       partsupp,
      nation,
14  15       region
16  where
17       p_partkey = ps_partkey
18       and s_suppkey = ps_suppkey
19       and p_size = 33
20       and p_type like '%STEEL'
21       and s_nationkey = n_nationkey
22       and n_regionkey = r_regionkey
23       and r_name = 'MIDDLE EAST'
24       and ps_supplycost = (
25                select
26                         min(ps_supplycost)
27                from
28                         partsupp,
29                         supplier,
30                         nation,
31                         region
32                where
33                         p_partkey = ps_partkey
34                         and s_suppkey = ps_suppkey
35                         and s_nationkey = n_nationkey
36                         and n_regionkey = r_regionkey
37                         and r_name = 'MIDDLE EAST'
38       )
39  order by
40       s_acctbal desc,
41       n_name,
42       s_name,
43       p_partkey
44  limit 100;

select
      s_acctbal,
      s_name,
      n_name,
      p_partkey,
      p_mfgr,
      s_address,
      s_phone,
      s_comment
from
      part,
      supplier,
      partsupp,
      nation,
      region
where
      p_partkey = ps_partkey
      and s_suppkey = ps_suppkey
      and p_size = 33
      and p_type like '%STEEL'
      and s_nationkey = n_nationkey
      and n_regionkey = r_regionkey
      and r_name = 'MIDDLE EAST'
      and ps_supplycost = (
            select
                     min(ps_supplycost)
            from
                     partsupp,
                     supplier,
                     nation,
                     region
            where
                     p_partkey = ps_partkey
                     and s_suppkey = ps_suppkey
                     and s_nationkey = n_nationkey
                     and n_regionkey = r_regionkey
                     and r_name = 'MIDDLE EAST'
      )
order by
      s_acctbal desc,
      n_name,
      s_name,
      p_partkey
limit 100;

      s_acctbal             s_name       n_name       p_partkey             p_mfgr       s_address             s_phone          s_comment

1    9999.21 Supplier#000081559             IRAQ                         756551       Manufacturer#1                YplAwJxhoHNeIyqDv3kgzv x2dymRYx    21-345-604-7861 bold packages cajole blithely pinto beans. carefully special

2    9994.67 Supplier#000048146             EGYPT                         1573100       Manufacturer#4                uq4pbng1m ,bHziSHWDqTi0mOSiqCn    14-860-865-5070 iously bold excuses x-ray evenly. even, regular accounts are daringly. even, ironic pack

3    9994.10 Supplier#000047385             SAUDI ARABIA                   722377       Manufacturer#3                pbRKuJaBXBl,5bOv9UhB8SDR cuz 8    30-427-545-5707 nic dependencies nag fluffily across the blithely special foxes. furiously silent deposit

100    9779.57 Supplier#000021383             IRAQ                         921382       Manufacturer#1                rE8nHb9QvK  21-316-417-2980 efully final braids beneath the carefully pending packages haggle blithel
100 rows got
time used: 191207.250(ms).
SQL>SQL>

〇〇 · 发表于 2011-4-25 13:28

SQL>SQL>ALTER TABLE PARTSUPP
2 ADD CONSTRAINT PARTSUPP_FK1 FOREIGN KEY  (PS_SUPPKEY) references SUPPLIER;
ALTER TABLE PARTSUPP
ADD CONSTRAINT PARTSUPP_FK1 FOREIGN KEY  (PS_SUPPKEY) references SUPPLIER;

time used: 29554.301(ms).
SQL>` /user1/postgresql/data/q3.txt
2 ;
fail to open include file
SQL>
SQL>` /user1/postgresql/data/q3.txt
2 /
fail to open include file
SQL>` /user1/postgresql/data/q1.txt
2 /
fail to open include file
SQL>exit

[root@redflag11012501 bin]# pwd
/user1/dm/bin
[root@redflag11012501 bin]# cp /user1/postgresql/data/q3.txt ./
[root@redflag11012501 bin]# cp /user1/postgresql/data/q*.txt ./
cp：是否覆盖“./q3.txt”? y
[root@redflag11012501 bin]#
[root@redflag11012501 bin]# ./isql
Login first.
SQL>login
server name:localhost
user name:sysdba
password:
port:
login succeed! dm_login time used:55.614(ms)
SQL>` ./q3.txt
2 /
fail to open include file
SQL>exit
[root@redflag11012501 bin]# ls *txt
q10.txt  q12.txt  q14.txt  q16.txt  q18.txt  q1.txt q21.txt  q2.txt  q4.txt  q6.txt  q8.txt
q11.txt  q13.txt  q15.txt  q17.txt  q19.txt  q20.txt  q22.txt  q3.txt  q5.txt  q7.txt  q9.txt
[root@redflag11012501 bin]# mv *txt *sql
[root@redflag11012501 bin]# ls *sql
a.sql       isql          lineitemx.ctl  orders.log partsupp.log  q13.txt  q17.txt  q20.txt  q3.txt  q7.txt    region.log
customer.ctl  lineitem2.ctl  nation.ctl    part.ctl    q10.txt    q14.txt  q18.txt  q21.txt  q4.txt  q8.txt    supplier.ctl
customer.log  lineitem.ctl nation.log    part.log    q11.txt    q15.txt  q19.txt  q22.txt  q5.txt  q9.txt    supplier.log
dss.ddl    lineitem.log orders.ctl    partsupp.ctl  q12.txt    q16.txt  q1.txt q2.txt q6.txt  region.ctl
[root@redflag11012501 bin]# ls
backup    dmcc       dmloader initdb          libdmcc_client_dll.a libdmodbc.a       libyacc_java.so serverconfig.xml.1
client_ssl  dmccd       dmobsv    isql.properties  libdmcc_client_dll.so  libdmOdbcSetup.so  manager.sh       server_ssl
console.sh  dmcc.ini    dmobsvd isql.sh       libdmcc_dll.a       libdmodbc.so    manual.sh       sql
dbcheck    dmcc.sh    dmserver jexe          libdmcc_dll.so       libdmpc.a       monitor.sh       startWebServer.sh
dbreplay dmdblinkd dmserverd  jgss.conf       libdmcrypto_engine.so  libdmpc.so       obsv.ini       stopWebServer.sh
dbupd    dm.ini    dts.sh    libdmapi.a    libdmdci.so          libdmucvt.so    par_dll.so       SYSWORD.GBK.LIB
dmagent    dm.key    expdb    libdmapi.so    libdmlnk_dll.so       libdmzip.so       proc             SYSWORD.UTF8.LIB
dmagentd dmlistenerd  impdb    libdmback.so    libdmobsv.so          libdts_dll.so    restore          transfer.ini
dmapi.log dmlnksvr    include libdmbcp.so    libdmoci.so          liblic_read.so    serverconfig.xml
[root@redflag11012501 bin]# cd sql
[root@redflag11012501 sql]# ls
a.sql       isql          lineitemx.ctl  orders.log partsupp.log  q13.txt  q17.txt  q20.txt  q3.txt  q7.txt    region.log
customer.ctl  lineitem2.ctl  nation.ctl    part.ctl    q10.txt    q14.txt  q18.txt  q21.txt  q4.txt  q8.txt    supplier.ctl
customer.log  lineitem.ctl nation.log    part.log    q11.txt    q15.txt  q19.txt  q22.txt  q5.txt  q9.txt    supplier.log
dss.ddl    lineitem.log orders.ctl    partsupp.ctl  q12.txt    q16.txt  q1.txt q2.txt q6.txt  region.ctl
[root@redflag11012501 sql]# cd ..
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q3.txt
-bash: ./isql: 没有那个文件或目录
[root@redflag11012501 bin]# mv sql/isql ./
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q3.txt
isql V6.0.2.77-Build(2010.12.23)
select
      l_orderkey,
      sum(l_extendedprice * (1 - l_discount)) as revenue,
      o_orderdate,
      o_shippriority
from
      customer,
      orders,
      lineitem
where
      c_mktsegment = 'HOUSEHOLD'
      and c_custkey = o_custkey
      and l_orderkey = o_orderkey
      and o_orderdate < date '1995-03-29'
      and l_shipdate > date '1995-03-29'
group by
      l_orderkey,
      o_orderdate,
      o_shippriority
order by
      revenue desc,
      o_orderdate
      limit 10;

      l_orderkey             revenue       o_orderdate          o_shippriority

1    21996356       429022.2405    1995-03-26    0

2    31617348       427014.3344    1995-03-27    0

3    47577254       426741.3390    1995-03-18    0

4    21404195       424534.2231    1995-03-25    0

5    35312039       422403.5320    1995-03-05    0

6    34684896       417863.0392    1995-03-11    0

7    34575136       413225.0126    1995-03-26    0

8    47741510       412126.9054    1995-03-09    0

9    46891527       411812.3222    1995-03-25    0

10    11646177       410971.5546    1995-03-16    0
10 rows got
time used: 181370.922(ms).
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q4.txt
isql V6.0.2.77-Build(2010.12.23)
'./sql/q4.txt'文件第 9 行: 'and'附近有语法错误
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q5.txt
isql V6.0.2.77-Build(2010.12.23)
'./sql/q5.txt'文件第 21 行: 'group'附近有语法错误
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q6.txt
isql V6.0.2.77-Build(2010.12.23)
'./sql/q6.txt'文件第 8 行: 'and'附近有语法错误
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q7.txt
isql V6.0.2.77-Build(2010.12.23)

[笔记] dm6安装过程

回复 #14 〇〇的帖子

[笔记] dm6安装过程

回复 #14 〇〇 的帖子

回复 #14 〇〇的帖子