首页 > 代码库 > SAP Sybase IQ 操作基础

SAP Sybase IQ 操作基础

1、启动

source IQ-16_0.sh 

命令行查看安装程序是否成功

start_iq -v2

 

2、数据库、表空间

start_iq -n utility_db 

dbisql -c ‘uid=dba;pwd=sql;eng=utility_db;dbn=utility_db‘ -nogui

 

-- 建立数据库

(DBA)> CREATE DATABASE ‘/sybaseiq160/T2/anos.db‘ IQ PATH ‘/sybaseiq160/T2/iq_system_main.iq‘ IQ SIZE 100 IQ PAGE SIZE 131072 TEMPORARY PATH ‘/sybaseiq160/T2/iq_system_temp.iqtmp‘ TEMPORARY size 200 MESSAGE PATH ‘/sybaseiq160/T2/anos_cfg.iqmsg‘ LOG ON ‘/sybaseiq160/T2/anos_cfg.log‘ PAGE SIZE 4096 COLLATION ‘EUC_CHINA‘ BLANK PADDING ON  CASE IGNORE;

stop_iq -stop all

 

cd T2/  

scp /sybaseiq160/IQ-16_0/scripts/default.cfg ./ 

start_iq @default.cfg anos.db    

dbisql -c ‘uid=dba;pwd=sql;eng=anos;dbn=anos‘ -nogui

-- 建立表空间

(DBA)> CREATE DBSPACE ANOS_DS USING FILE ANOS_MAIN_FILE01 ‘/sybaseiq160/T2/ANOS_MAIN_FILE01.iq‘ size 3000;

-- 设置默认空间(重要)

(DBA)> set option public.DEFAULT_DBSPACE= ‘ANOS_DS‘;

 

3、用户、角色

(DBA)> sp_iqaddlogin ‘anoscfg’,‘anoscfg’;
Execution time: 0.282 seconds

(DBA)> grant connect to anoscfg;
Execution time: 0.046 seconds

 

4、SCC监控

[root@bogon bin]# cd /sybaseiq160/SCC-3_2/bin/

[root@bogon bin]# scc.sh 

 

https://192.168.128.137:8283/scc/

sccadmin/ zxc123(安装时密码)

 

5、 索引

(DBA)>   sp_iqcheckoptions;

(DBA)>   set option public.index_Advisor="On";

(DBA)>   set option public.index_advisor_max_row=10000;

 

(DBA)>  sp_iqindexadvice;

 

(DBA)> CREATE LF index LF_L_RETURNFLAG ON LINEITEM(L_RETURNFLAG);
(DBA)> CREATE DTTM index DTTM_L_SHIPDATE ON LINEITEM(L_SHIPDATE);

6、优化监控

create table mytable (c1 char(4) not null,c2 int not null);
-- 火山模式

set temporary option query_plan = ‘On‘ ;
set temporary option query_detail = ‘On‘ ;
set temporary option query_plan_after_run = ‘On‘ ;
set temporary option query_plan_as_html =‘On‘ ;
set temporary option query_plan_as_html_directory = ‘c:/temp‘;
set temporary option query_timing = ‘On‘ ;

 

7、 ETL

(DBA)>CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));

 

(DBA)>load table nation(N_NATIONKEY  ‘|‘,N_NAME       ‘|‘,N_REGIONKEY  ‘|‘,N_COMMENT   ‘|‘) using file ‘/home/sybaseiq/nation.tbl‘ row delimited by ‘\x0d\x0a‘ quotes off escapes off

 

SAP Sybase IQ 操作基础