db2异常
<h1>DB2建表时报错：DB2 SQL Error: SQLCODE=-286, SQLSTATE=42727</h1>
<p>解决方法：
第一步：先连接db2数据库，创建一个32k大小的缓存池。</p>
<pre><code class="language-sql">db2 "CREATE BUFFERPOOL PAS_PB_32K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32K"</code></pre>
<p>第二步：通过以下命令，查看添加的缓存池有没有成功添加</p>
<pre><code class="language-sql">db2 "select * from syscat.bufferpools"</code></pre>
<p>第三步：如果已经成功添加，再创建一个带有32k缓存池的表空间</p>
<pre><code class="language-sql">db2 "CREATE LARGE TABLESPACE TBS_SJY PAGESIZE 32K MANAGED BY DATABASE USING ( FILE 'F:\YCPAS\PAS_DATA\PAS_SPACE_DATA\TBS_SJY1' 30G) EXTENTSIZE 32 OVERHEAD 10.5 PREFETCHSIZE 64 TRANSFERRATE 0.14 BUFFERPOOL PAS_PB_32K DROPPED TABLE RECOVERY ON"</code></pre>
<p>创建表空间名：TBS_SJY
创建的表路径路径：‘F:\YCPAS\PAS_DATA\PAS_SPACE_DATA\TBS_SJY1’
创建的表空间大小: 30G
如需调整，根据各自的需求进行调整即可。</p>
<p>第四步：再执行相应创建表的语句，指定新建的表空间TBS_SJY ，就可以执行成功了。</p>
<pre><code class="language-sql">CREATE TABLE TABLENAME (
列1 VARCHAR(1) ,
...
列n VARCHAR(1)
) in TBS_SJY</code></pre>
<p>指定表空间只需在建表的语句后面添加上‘ in 表空间’ 即可</p>
<p>列如</p>
<pre><code class="language-sql">CREATE BUFFERPOOL DY_YCCX_BUFF32K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32K;
CREATE TABLESPACE DY_YCCX_ESPACE PAGESIZE 32K MANAGED BY database USING (FILE '/db2_tag/dbfile/dyyccx/dyyccx_space' 10g) BUFFERPOOL DY_YCCX_BUFF32K;
create table org_early_warn_info(
id bigint not null generated always as identity (start with 10000,increment by 1) primary key ,
org_code varchar(32) not null ,
org_name varchar(100) not null ,
warn_type integer not null default 0,
check_status integer not null default 0,
check_result varchar(5000),
deal_status integer not null default 1,
warn_time timestamp not null default current_timestamp ,
deal_time timestamp,
verifier_code varchar(60),
verifier_name varchar(30),
create_time timestamp not null default current_timestamp ,
valid smallint not null default 1
) in DY_YCCX_ESPACE;
comment on table org_early_warn_info is '连锁企业进销存数据对比预警';
comment on column org_early_warn_info.org_code is '企业编码';
comment on column org_early_warn_info.org_name is '企业名称';
comment on column org_early_warn_info.warn_type is '预警类型0:未预警/1:预警(订单不一致)/2:预警(品规不一致)/3:预警(两者皆不一致)';
comment on column org_early_warn_info.check_status is '核查状态';
comment on column org_early_warn_info.check_result is '核查内容';
comment on column org_early_warn_info.deal_status is '处理状态';
comment on column org_early_warn_info.warn_time is '预警时间';
comment on column org_early_warn_info.deal_time is '处理时间';
comment on column org_early_warn_info.verifier_code is '核查人编码';
comment on column org_early_warn_info.verifier_name is '核查人姓名';
</code></pre>