SYBASE IQ 知识整理
目录
SYBASE IQ 知识整理 --------------------------------------------------------------------------------------- 0 1 1.1 1.2 2 2.1
概述--------------------------------------------------------------------------------------------------- 4 内容简介 .......................................................................................................................... 4 Iq数据库的物理限制 ...................................................................................................... 5 SybaseIQ的安装 ---------------------------------------------------------------------------------- 5 Sybase IQ单服务器系统的安装 .................................................................................... 5 2.1.1 操作系统级别的配置 -------------------------------------------------------------------- 5 2.1.2 搭建IQ数据库的用户环境 ------------------------------------------------------------ 6 2.1.3 安装IQ数据库软件 --------------------------------------------------------------------- 7 2.1.4 创建IQ数据库 --------------------------------------------------------------------------- 7 Sybase IQ Multiplex多机系统安装 ............................................................................... 8 2.2.1 基础 ----------------------------------------------------------------------------------------- 8 2.2.2 安装和配置方法 -------------------------------------------------------------------------- 8 2.2.3 MultiPlex的注意事项 ------------------------------------------------------------------- 9 2.2.4 Multiplex重建查询服务器步骤 ------------------------------------------------------ 9 注册插件 .......................................................................................................................... 9 Utility数据库 ................................................................................................................. 10 优化和配置数据库参数 ------------------------------------------------------------------------- 10 配置数据库优化参数 .................................................................................................... 10 多个iq数据库的设置 ................................................................................................... 10 配置内存和高速缓存 .................................................................................................... 11 3.3.1 配置高速缓存分区 ---------------------------------------------------------------------- 11 3.3.2 示例 ---------------------------------------------------------------------------------------- 11 设置并发查询 ................................................................................................................ 12 Sybase IQ 数据加载 ----------------------------------------------------------------------------- 12 数据加载语法 ................................................................................................................ 12 从定长的文本文件装载数据是装载数据的最快方式 ................................................. 13 使用LOAD TABLE装载文本文件注意事项 ............................................................. 13 使用LOAD TABLE如何装载bcp导出的数据文件 ................................................. 13 数据装载时候遇到不能装载也不报错的问题 ............................................................. 14 LOAD TABLE 如何进行错误处理 ............................................................................. 14 Loadtable 其他一些问题处理 ..................................................................................... 15 4.7.1 数据库升级后load错误 --------------------------------------------------------------- 15 4.7.2 LOAD TABLE对缺省值的支持 ----------------------------------------------------- 16 4.7.3 Sybase iq 加载时跳过错误 ----------------------------------------------------------- 16
2.2
2.3 2.4 3 3.1 3.2 3.3
3.4 4 4.1 4.2 4.3 4.4 4.5 4.6 4.7
5 6 6.1 6.2 6.3 7 7.1
7.2 7.3
7.4 7.5 7.6 7.7 8 8.1 8.2 8.3
8.4
8.5 8.6 8.7
数据导出 ------------------------------------------------------------------------------------------- 16 索引-------------------------------------------------------------------------------------------------- 17 IQ的索引特性： ........................................................................................................... 17 索引的适用情况 ............................................................................................................ 18 并行执行创建索引 ........................................................................................................ 19 Sybase IQ 的主要语法简介 -------------------------------------------------------------------- 19 变量 ................................................................................................................................ 20 7.1.1 局部变量 ---------------------------------------------------------------------------------- 20 7.1.2 全局变量 ---------------------------------------------------------------------------------- 20 7.1.3 连接级变量 ------------------------------------------------------------------------------- 20 注释 ................................................................................................................................ 21 表达式 ............................................................................................................................ 21 7.3.1 IF 表达式 --------------------------------------------------------------------------------- 21 7.3.2 CASE 表达式的语法 ------------------------------------------------------------------ 21 7.3.3 游标FETCH 语句 ---------------------------------------------------------------------- 22 7.3.4 循环语句FOR --------------------------------------------------------------------------- 23 7.3.5 LOOP 语句 ------------------------------------------------------------------------------- 24 日期和字符串比较 ........................................................................................................ 25 ALTER TABLE 语句 ................................................................................................... 25 BACKUP 语句 .............................................................................................................. 26 7.6.1 语法 ---------------------------------------------------------------------------------------- 26 创建iq空间设备（dbspace）语法.............................................................................. 26 系统管理 ------------------------------------------------------------------------------------------- 27 使用存储过程获取信息................................................................................................. 27 查看数据库文件 ............................................................................................................ 27 Multiplex多机系统管理 ............................................................................................... 28 8.3.1 主数据库空间的计数在写入和查询服务器上不同 ----------------------------- 28 8.3.2 Multiplex 同步“Not enough space”错误 ---------------------------------------- 28 8.3.3 备份和恢复 Multiplex ----------------------------------------------------------------- 28 8.3.4 更换写入服务器 ------------------------------------------------------------------------- 30 系统表 ............................................................................................................................ 32 8.4.1 DUMMY 系统表 ------------------------------------------------------------------------ 32 8.4.2 IQ_MPX_INFO 系统表 --------------------------------------------------------------- 32 8.4.3 Sysobjects系统表 ----------------------------------------------------------------------- 32 8.4.4 SYSTABLE 系统表 -------------------------------------------------------------------- 33 8.4.5 SYSCOLUMN 系统表----------------------------------------------------------------- 33 8.4.6 SYSFKCOL 系统表 -------------------------------------------------------------------- 33 8.4.7 SYSFOREIGNKEY 系统表 ---------------------------------------------------------- 33 8.4.8 SYSINDEX 系统表 -------------------------------------------------------------------- 33 恢复数据库 .................................................................................................................... 33 系统字符集问题 ............................................................................................................ 34 数据库权限 .................................................................................................................... 34 8.7.1 添加新用户 ------------------------------------------------------------------------------- 34 8.7.2 授予对表、视图、存储过程的权限 ------------------------------------------------ 35
8.8
8.9 8.10
9 9.1 9.2 9.3 9.4 10 10.1 10.2 10.3 11 11.1 11.2 11.3 11.4 11.5 11.6 11.7 11.8 12 12.1 12.2 13 13.1 13.2 13.3 13.4 14 15 16 16.1 17
8.7.3 撤消用户权限 ---------------------------------------------------------------------------- 35 8.7.4 删除 Sybase IQ 用户帐户 ------------------------------------------------------------ 36 8.7.5 数据库连接问题 ------------------------------------------------------------------------- 36 8.7.6 管理组 ------------------------------------------------------------------------------------- 36 8.7.7 资源管理 ---------------------------------------------------------------------------------- 37 系统日志清理 ................................................................................................................ 37 8.8.1 单机系统日志清理 ---------------------------------------------------------------------- 37 8.8.2 SybaseIQ multiplex 日志清理 -------------------------------------------------------- 38 IQ数据仓库清理消息文件*.iqmsg .............................................................................. 38 数据库锁 ........................................................................................................................ 39 8.10.1 锁定用户 ---------------------------------------------------------------------------------- 39 8.10.2 解锁用户 ---------------------------------------------------------------------------------- 39 8.10.3 锁表查看 ---------------------------------------------------------------------------------- 39 存储过程 ------------------------------------------------------------------------------------------- 40 存储过程的语法规则 .................................................................................................... 40 输出存储过程中print消息； ...................................................................................... 40 系统存储过程 ................................................................................................................ 43 Multiplex 系统过程 ...................................................................................................... 43 常用的函数 ---------------------------------------------------------------------------------------- 43 日期和时间函数 ............................................................................................................ 43 CONVERT数据类型转换函数 .................................................................................... 43 字符串函数 .................................................................................................................... 44 故障排除（添加中） ---------------------------------------------------------------------------- 45 insert thrashing detected 或者Hash find thrashingdetected ................................... 45 ASA Error -1009134: Insufficient buffers for sort. .................................................... 45 Multiplex出现other version 数值过大...................................................................... 46 删除数据库中损坏的表................................................................................................. 46 使用强制模式恢复数据库............................................................................................. 47 删除损坏的数据库空间................................................................................................. 48 删除重复记录 ................................................................................................................ 49 一个实现IQ结果集分页SQL查询的调优 ................................................................ 49 系统诊断工具SQL Trace ----------------------------------------------------------------------- 51 SYBASE IQ 15之前的SQL Trace .............................................................................. 51 SYBASE IQ 15的SQL Trace功能增强 ..................................................................... 52 IQ 15 集群搭建 ----------------------------------------------------------------------------------- 53 环境要求 ........................................................................................................................ 53 创建Simplex IQ Server ................................................................................................ 54 创建IQ Multiplex ......................................................................................................... 55 设置IQ Multiplex ......................................................................................................... 56 获取iq数据库文件和日志路径 --------------------------------------------------------------- 57 IQ存储过程返回集的问题 --------------------------------------------------------------------- 57 Utility数据库-------------------------------------------------------------------------------------- 58 命令行下面启动和执行................................................................................................. 58 IQ的官方资源 ------------------------------------------------------------------------------------ 58
18 附录-------------------------------------------------------------------------------------------------- 60 18.1 Iq数据库创建脚本 ........................................................................................................ 60
18.1.1 以普通方式创建数据库（非裸设备） --------------------------------------------- 60 18.1.2 以裸设备方式创建数据库示例 ------------------------------------------------------ 60 18.2 开机自动启动数据库示例............................................................................................. 61 18.3 数据库配置文件params.cfg ......................................................................................... 61 18.4 IQ15以上版本安装 ....................................................................................................... 61 18.5 Iq命令行 ........................................................................................................................ 62
18.5.1 dbsqlc命令实例： ---------------------------------------------------------------------- 62 18.5.2 isql命令实例： -------------------------------------------------------------------------- 62
19 硬件方面问题 ------------------------------------------------------------------------------------- 63 19.1 对每块磁盘进行独立的磁盘扫描................................................................................. 63 19.2 为linux设定swap分区 ............................................................................................... 64 19.3 Aix 磁盘操作 ................................................................................................................ 64 19.4 配置NFS ........................................................................................................................ 66 19.5 如何格式化2T以上的分区 .......................................................................................... 66 19.6 时间设置 ........................................................................................................................ 66 19.7 配置图形安装界面 ........................................................................................................ 66 19.8 为Red Hat Linux5添加裸设备 ................................................................................... 67 19.9 为linux4添加裸设备 .................................................................................................... 67 19.10 Liunx清理swap和缓存 ............................................................................................... 68
19.10.1 清理swap --------------------------------------------------------------------------------- 68 19.10.2 清理缓存 ---------------------------------------------------------------------------------- 68 19.11 磁盘柜多路径驱动 ........................................................................................................ 68 20 优化指导 ------------------------------------------------------------------------------------------- 72 作者 赵亮 版本 1.0 2.0 日期 2009-11-25 2013-3-23 备注
1 概述
1.1 内容简介
本手册主要针对sybase iq文档内容进行提炼和总结，主要包括： Sybase IQ 单机系统的安装方法 Sybase IQ 的性能配置 Sybase IQ 多机系统搭建 Sybase IQ 的主要语法简介 Sybase IQ 的系统管理 Sybase IQ 的存储过程 Sybase IQ 的数据加载和导出 Sybase IQ 的索引