人大金仓数据库常用SQL、启停服务、参数调整、统计信息

主要可执行命令均位于 $kingbase_home/Server/bin/下，其中以数据库初始化在$kingbase_home/data/下为例，所有操作均需要在非ROOT下执行，一般会创建kingbase用户。

查看服务状态

su kingbase &&  $kingbase_home/Server/bin/sys_ctl status -D 

$kingbase_home/data

启动服务

su kingbase &&  $kingbase_home/Server/bin/sys_ctl start -D  $kingbase_home/data  

停止服务

su kingbase &&  $kingbase_home/Server/bin/sys_ctl stop -D  $kingbase_home/data  

参数调整：NC Cloud适配人大金仓数据库，部分参数需进行调整

1、增加关键字

以数据库初始化在/data/kingbase/data/下为例，修改/data/kingbase/data/kingbase.conf，如果有启动exclude_reserved_words参数，则增加level，每个关键字之间用英文的逗号隔开。如果没有启用的exclude_reserved_words，就在文件最后增加1行。参考效果如下：

2、修改sys_guid函数

金仓 sys_guid 生成的uuid 是字节码。 sys_guid_name 是正常的字符。所以导致部分业务（HR），在根据函数取数时，获取的字节超长。

以数据库初始化在/data/kingbase/data/下为例，修改/data/kingbase/data/kingbase.conf，如果有启动bytea_output参数，设置为escape。如果没有启用的bytea_output  ，就在文件最后增加1行。参考效果如下：  

port = 54321 # (change requires restart)
max_connections = 3000 # (change requires restart)

shared_buffers = 20GB

temp_buffers = 5GB

work_mem = 400MB

maintenance_work_mem = 600MB

max_pending_gtfiles_per_backend = 500

plan_cache_mode = force_generic_plan

log_rotation_size = 1024MB

log_statement=all

autovacuum_vacuum_cost_delay = 20ms

extra_float_digits = 3

default_text_search_config = 'pg_catalog.simple'

max_locks_per_transaction = 64 # min 10
max_pred_locks_per_transaction = 64 # min 10
max_pred_locks_per_relation = -2 # negative values mean
# (max_pred_locks_per_transaction  / -max_pred_locks_per_relation) - 1

max_pred_locks_per_page = 2 # min 0 

shared_preload_libraries = 'liboracle_parser, synonym, plsql, force_view, $libdir/plugin_debugger, ora_commands,kdb_ora_expr, sepapower, dblink, sys_stat_statements'

log_line_prefix='%t [%p]: [%x] user=%u,db=%d,app=%a,client=%h'
log_destination = 'stderr'
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default
log_statement = 'none'
log_duration=on
log_min_duration_statement = 0
lc_messages='C'

收集统计信息： 登录当前用户后执行 analyze VERBOSE;

查看执行计划：explain analyze 要分析的SQL 

查看表结构字段属性：

#登录数据库

cd 人大金仓家目录/Server/bin/

./ ksql -U用户名-p端口 数据库名

#查询表字段属性

\d 表名

dateadd函数

CREATE OR REPLACE FUNCTION dateadd(difftype character varying(100), incrementvalue integer, inputdate timestamp with time zone)
RETURNS timestamp without time zone AS
DECLARE
YEAR_CONST Char(15) := 'year';
MONTH_CONST Char(15) := 'month';
WEEK_CONST Char(15) := 'week';
DAY_CONST Char(15) := 'day';
HOUR_CONST Char(15) := 'hour';
MIN_CONST Char(15) :='minute';
SECOND_CONST Char(15) :='second';
dateTemp timestamp;
intervals interval;
BEGIN
IF lower($1) = lower(YEAR_CONST) THEN
select cast(cast(incrementvalue as character varying) || ' year' as interval) into intervals;
ELSEIF lower($1) = lower(MONTH_CONST) THEN
select cast(cast(incrementvalue as character varying) || ' month' as interval) into intervals;
ELSEIF lower($1) = lower(WEEK_CONST) THEN
select cast(cast(incrementvalue as character varying) || ' week' as interval) into intervals;
ELSEIF lower($1) = lower(DAY_CONST) THEN
select cast(cast(incrementvalue as character varying) || ' day' as interval) into intervals;
ELSEIF lower($1) = lower(HOUR_CONST) THEN
select cast(cast(incrementvalue as character varying) || ' hour' as interval) into intervals;
ELSEIF lower($1) = lower(MIN_CONST) THEN
select cast(cast(incrementvalue as character varying) || ' minute' as interval) into intervals;
ELSEIF lower($1) = lower(SECOND_CONST) THEN
select cast(cast(incrementvalue as character varying) || ' second' as interval) into intervals;
END IF;

dateTemp:= inputdate + intervals;

RETURN dateTemp;
END; 

命令行初始化数据库

initdb -Usystem -W --dbmode=oracle --enable-ci -D /opt/Kingbase/ES/V8/data中的 /opt/Kingbase/ES/V8/data 

(--enable-ci 初始化不敏感的 data，初始化敏感的 data 则删除这个参数

license放到Server/bin下面