Sybase IQ 15.2学习笔记--脚本

willa.yuan 发表于 2010-11-04 13:46

start_iq -gu utility_db -gp 8192 -n iqdemo

CREATE DATABASE 'f:\test\test.db'
IQ PATH 'f:\test\iq_system_main.iq'
IQ SIZE 100
IQ PAGE SIZE 131072
TEMPORARY PATH 'f:\test\iq_system_temp.iqtmp'
TEMPORARY size 200
MESSAGE PATH 'f:\test\test.iqmsg'
LOG ON 'f:\test\test.log'
PAGE SIZE 8192
COLLATION 'EUC_CHINA'
BLANK PADDING ON
CASE IGNORE;

create database 'E:\aa\megaphone2.db'
message path 'E:\aa\megaphone2.iqmsg'
temporary path 'E:\aa\megaphone2.iqtmp'
log on    'E:\aa\megaphone2.log'
IQ PATH    'E:\aa\megaphone2.iq'
IQ PAGE SIZE 65536
IQ SIZE 200
collation '936ZHO'
blank padding on;

CREATE DATABASE '/abtest/abdb/abdb.db'
   DBA PASSWORD 'sql'
   IQ PATH '/abtest/abdb/abdb.iq'
   IQ PAGE SIZE 131072                                                    // 128k
   TEMPORARY PATH '/abtest/abdb/iqtmp01.iqtmp'
COLLATION '936ZHO'
   BLANK PADDING ON
CASE IGNORE;

/* NT 操作系统提示符下

copy C:\Documents and Settings\All Users\SybaseIQ\demo\iqdemo.cfg test.cfg
edit test.cfg
-nmyhostname_test
-xtcpip{host=MyIP;port=3333}

-c48m
-gc 20
-gd all
-gl all
-gm 10
-gp 4096

-iqmc 320
-iqtc 240

start_iq @test.cfg test.db

dsedit (建open client连接服务名)
isql -Udba -Psql -Stest -w2000

建odbc (注意：数据库名、iq服务名、open client连接服务名、odbc名)
iqdsn -l -b

连接IQ服务
dbisqlc -c "uid=dba;pwd=sql;eng=myhostname_test"

dbisql -nogui -c "uid=dba;pwd=sql" -host localhost -port 3333
dbisql -nogui -c "uid=dba;pwd=sql" -host Myhostname -port 3333

isql -Udba -Psql -Stest -w2000

************************12.7**********************************
dbisql -c "eng=myserver;uid=DBA;pwd=SQL;dbn=utility_db;links=tcpip{host=IP:2638}" -nogui

dbisql -c "eng=Myhostname_test;uid=dba;pwd=sql;dbn=test;links=tcpip{host=IP:3333}" -nogui
dbisql -c "uid=dba;pwd=sql;eng=Myhostname_test;dbn=test;links=tcpip{host=IP}" -nogui

dbisql -c "uid=dba;pwd=sql" -host Myhostname -port 3333 -n hyuan-mobile_test f:\temp\test.db --from remote machine + start IQ
************************12.7 end**********************************

*/

sp_iqstatus

ALTER DBSPACE IQ_SYSTEM_TEMP
      ADD FILE iqtmp02 'f:\temp\iq_system_temp02.iqtmp' SIZE 100;

sp_iqfile

CREATE DBSPACE mydbspaceUSING
         FILE mydbspace_main_1 'f:\temp\mydbspace_main_1.iq' SIZE 100,
      FILE mydbspace_main_2 'f:\temp\mydbspace_main_2.iq' SIZE 100;

GRANT CREATE ON mydbspace TO PUBLIC;
REVOKE CREATE ON IQ_SYSTEM_MAIN FROM PUBLIC;
SET OPTION PUBLIC.DEFAULT_DBSPACE ='mydbspace';
___________________________________________________-

CREATE DBSPACE "user_main"
USING FILE "user_main_001" '/abtest/abdb/IQ_MAIN_001.iq' ,
   FILE "user_main_002" '/abtest/abdb/IQ_MAIN_002.iq' ,
   FILE "user_main_003" '/abtest/abdb/IQ_MAIN_003.iq'
IQ STORE ;

---为IQ_SYSTEM_TEMP 添加新的文件
ALTER DBSPACE IQ_SYSTEM_TEMP
ADD FILE iqtmp02 '/abtest/abdb/iqtmp02.iqtmp',
         FILE iqtmp03 '/abtest/abdb/iqtmp03.iqtmp' ,
         FILE iqtmp04 '/abtest/abdb/iqtmp04.iqtmp' ;

___________________________________________________-
edit test.cfg
*************************************************************
-nhyuan-mobile_test
-xtcpip{host=10.42.66.221;port=3333}

-c48m
-gc 5000
-gr 5000
-gd all
-gl all
-gm 10
-gp 4096
-tl 0
-iqmt 300
-iqpartition 4
-iqgovern 8
-iqnumbercpus 2
-iqmsgsz 100
-iqmsgnum 2
-iqtss 300

-iqmc 200
-iqtc 300
*************************************************************
set option PUBLIC.Allow_Nulls_By_Default='Off';
set option PUBLIC.Append_Load='On';
set option PUBLIC.Force_No_Scroll_Cursors='On';
set option PUBLIC.Garray_Fill_Factor_Percent=2;
set option PUBLIC.Load_Memory_Mb=50;
set option PUBLIC.Max_IQ_Threads_Per_Connection=100;
set option PUBLIC.Minimize_Storage='On';
set option PUBLIC.Notify_Modulus=200000;
set option PUBLIC.Query_Temp_Space_Limit=0;
set option PUBLIC.Sort_Phase1_Helpers=5;
set option PUBLIC.Max_Hash_Rows=10000000;
set option PUBLIC.Default_Having_Selectivity=1;
set option PUBLIC.Query_Temp_Space_Limit=0;
set option PUBLIC.Query_Plan='OFF';
set option PUBLIC.IQMSG_LENGTH_MB=’2047’;
set option PUBLIC.FP_LOOKUP_SIZE_PPM=2500;
set option PUBLIC.FP_LOOKUP_SIZE=32; --32M
set option PUBLIC.string_rtruncation='OFF';
set option PUBLIC.Join_Expansion_Factor = 10;
set option Join_Expansion_Factor = 10;

set --查看数据库选项
sp_iqcheckoptions

***********************与 ASE 相同之处 *********************************************
select @@version,@@servername,@@spid，db_name()
************************************************************************************

drop table cc
create table cc(c1 int,c2 char(10) iq unique(10)) --255 65536
sp_iqindex cc
set temporary option minimize_storage='Off'
set option public.minimize_storage='On'
sp_iqcheckoptions
select * from SYSDOMAIN

--9 load
drop table bb
create table bb(b1 int,b2 date,b3 char(20))

load table bb
(b1 '|',
b2 '|',
b3 '\x0a')
from 'E:\megaphone\database\bb.txt'
limit 1
skip 2
escapes off
quotes off

commit

truncate table bb

select rowid(bb),* from bb

delete from bb where b1=1

set option public.Append_Load='on'

drop table aa

create table aa(a1 char(10),a2 char(10),a3 char(10),a4 char(10))

load table aa
(a3 '|',
a4 '\x0a')
from 'E:\megaphone\database\aa.txt'
start row id 1
escapes off
quotes off
set
select rowid(aa),* from aa >#e:\aa.txt
select @@identity
commit
update aa set a3=null,a4=null

--15 p&t
sp_iqconnection
select @@spid,@@version,@@servername,db_name()
drop connection 5
sp_iqcontext

call sa_server_option('request_level_logging','SQL')
call sa_server_option('request_level_log_file','e:\sqllog.txt')

call sa_server_option('request_level_logging','none')
call sa_server_option('request_level_log_file','')

sp_iqtransaction
sp_iqcheckdb 'check database'
sp_iqcheckdb 'verify database'
sp_iqcheckdb 'allocation database'
sp_iqcheckdb 'repair database' >#e:\check.txt
sp_iqdbstatistics
sp_iqdbsize
sp_iqstatus
sp_iqdbspaceinfo
sp_iqdbspace
select * from sysiqfile order by segment_type

drop table monitor_table
create table monitor_table(a char(1))

iq utilities private into monitor_table
start monitor '-summary -interval 20'

iq utilities private into monitor_table
stop monitor

--从ASE中取数据到IQ--
create table test(t1 int primary key,t2 char(20))
---*** 以下操作在 ASE 中 ***------
use master
sp_addlogin dba,sql
use pubs2
sp_adduser dba
grant all on test to dba
create table test(t1 int primary key,t2 char(20))
insert into test values(1,"I'm from ASE")
---*** end ***------
insert into test location 'sybase.pubs2' packetsize 512
{select * from test}

select SQLDialect('select top 3 combined_revenue
   from telco_facts
   order by combined_revenue desc')
select TransactSQL('select top 3 combined_revenue
   from telco_facts
   order by combined_revenue desc')
select sqlflagger('SQL:2003/Package',
‘select top 3 combined_revenue
   from telco_facts
   order by combined_revenue desc' )

sp_iqlocks
sp_iqtransaction

select * from sysiqtab
sp_iqtable
select * from sysiqtabcol
sp_iqcolumn bb
select * from sysiqidx
sp_iqindex bb

sp_iqrowdensity 'bb'

wfcjz 发表于 2010-11-04 13:53

嗯，好东西！最近技术贴子越来越多了。好现象！

andkylee 发表于 2010-11-08 14:53

顶，附个下载地址最好了。。否则，光看不能测试，呵呵。
sybase的iq安装介质很难下载的说。

pentaho 发表于 2011-07-20 18:33

下载很容易啊

hefan 发表于 2011-09-26 10:04

IQ我倒有12.6的，不过只有AIX的啊

页: [1]

Chinaunix's Archiver

Sybase IQ 15.2学习笔记--脚本