这个TEST像表空间,但不知道怎么用脚本创建 ,刚入门好多都不明白,请教..谢谢各位
导入脚本的命令 db2 -tvf exoa.sql
以下为脚本部分内容
--------------------------------------------------------------------------------
CONNECT TO WJ5HY;
--------------------------------------------------------------------------------
CONNECT TO WJ5HY;
------------------------------------
-- DDL Statements for BUFFERPOOLS --
------------------------------------
CREATE BUFFERPOOL "TEST" SIZE 1000 PAGESIZE 32768 NOT EXTENDED STORAGE;
--(这一句好像有错误,但不知道该怎么改)
CONNECT RESET;
CONNECT TO WJ5HY;
------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------
CREATE LARGE TABLESPACE SYSTOOLSPACE IN DATABASE PARTITION GROUP IBMCATGROUP
PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
EXTENTSIZE 4
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE LARGE TABLESPACE TEST IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
EXTENTSIZE 16
PREFETCHSIZE 16
BUFFERPOOL TEST
OVERHEAD 10.500000
TRANSFERRATE 0.140000
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE TEMPORARY TABLESPACE TEMP1 IN DATABASE PARTITION GROUP IBMTEMPGROUP
PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 16
PREFETCHSIZE 16
BUFFERPOOL TEST
OVERHEAD 10.500000
TRANSFERRATE 0.140000
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY OFF;
CREATE USER TEMPORARY TABLESPACE SYSTOOLSTMPSPACE IN DATABASE PARTITION GROUP IBMCATGROUP
PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE
AUTORESIZE NO
EXTENTSIZE 4
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 7.500000
TRANSFERRATE 0.060000
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY OFF;
-- Mimic tablespace
ALTER TABLESPACE SYSCATSPACE
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
FILE SYSTEM CACHING
TRANSFERRATE 0.060000;
ALTER TABLESPACE TEMPSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
FILE SYSTEM CACHING
TRANSFERRATE 0.060000;
ALTER TABLESPACE USERSPACE1
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
FILE SYSTEM CACHING
TRANSFERRATE 0.060000;
ALTER TABLESPACE TEST
PREFETCHSIZE AUTOMATIC
OVERHEAD 7.500000
FILE SYSTEM CACHING
TRANSFERRATE 0.060000;
------------------------------------------------
-- DDL Statements for table "DB2INST1"."ACTIVITYINS"
------------------------------------------------
CREATE TABLE "DB2INST1"."T_YUANGONGZHUANZHENG" (
"F_TIANBIAOTIME" VARCHAR(400) ,
"F_TXBUZHU" VARCHAR(400) ,
"F_JIAOTONGBUZHU" VARCHAR(400) ,
"F_JIANKONGZONGCAI" VARCHAR(400) ,
"F_ZHUANZHENGTIME" VARCHAR(400) ,
"F_TONGXUNBUZHU2" VARCHAR(400) ,
"F_RESULT" VARCHAR(400) ,
"F_GONGZI2" VARCHAR(400) ,
"F_GANGWEI" VARCHAR(400) ,
"PROCESSID" VARCHAR(400) ,
"F_TONGXUNBUZHU" VARCHAR(400) ,
"F_ZONGCAI" VARCHAR(400) ,
"F_GONGZI" VARCHAR(400) ,
"F_ZHONGXINZONGJIAN" VARCHAR(400) ,
"ID" VARCHAR(400) ,
"F_XINGMING" VARCHAR(400) ,
"SHUOMING" VARCHAR(400) ,
"F_HR" VARCHAR(400) ,
"F_RUZHITIME" VARCHAR(400) ,
"F_PART" VARCHAR(400) ,
"F_JIAOTONGBUZHU2" VARCHAR(400) ,
"F_NIANXIAN2" VARCHAR(400) ,
"F_COMBINESIGN" VARCHAR(400) )
IN "TEST" ;
CREATE TABLE "DB2INST1"."T_YUANGONGZHUANZHENG" ( "F_TIANBIAOTIME" VARCHAR(400) , "F_TXBUZHU" VARCHAR(400) , "F_JIAOTONGBUZHU" VARCHAR(400) , "F_JIANKONGZONGCAI" VARCHAR(400) , "F_ZHUANZHENGTIME" VARCHAR(400) , "F_TONGXUNBUZHU2" VARCHAR(400) , "F_RESULT" VARCHAR(400) , "F_GONGZI2" VARCHAR(400) , "F_GANGWEI" VARCHAR(400) , "PROCESSID" VARCHAR(400) , "F_TONGXUNBUZHU" VARCHAR(400) , "F_ZONGCAI" VARCHAR(400) , "F_GONGZI" VARCHAR(400) , "F_ZHONGXINZONGJIAN" VARCHAR(400) , "ID" VARCHAR(400) , "F_XINGMING" VARCHAR(400) , "SHUOMING" VARCHAR(400) , "F_HR" VARCHAR(400) , "F_RUZHITIME" VARCHAR(400) , "F_PART" VARCHAR(400) , "F_JIAOTONGBUZHU2" VARCHAR(400) , "F_NIANXIAN2" VARCHAR(400) , "F_COMBINESIGN" VARCHAR(400) ) IN "TEST"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:SQL0204N "TEST" is an undefined name. SQLSTATE=42704
4 个解决方案
#1
用这个创建一个表空间
CREATE REGULAR TABLESPACE TEST PAGESIZE 4K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 10.5 PREFETCHSIZE 32 TRANSFERRATE 0.14 BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY ON;
CREATE REGULAR TABLESPACE TEST PAGESIZE 4K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 10.5 PREFETCHSIZE 32 TRANSFERRATE 0.14 BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY ON;
#2
to
liyan93834183
建好表空间后.执行接着有异常如下.这是怎么回事呢?
CREATE TABLE "DB2INST1"."T_PARENTROLE" ( "NAME" VARCHAR(400) , "ID" VARCHAR(400) , "PARENTID" VARCHAR(400) , "TABLENAME" VARCHAR(400) , "POSITION" VARCHAR(400) , "TEMPLATENAME" VARCHAR(400) , "SELFID" VARCHAR(400) , "F_ICON" VARCHAR(400) , "RESOURCETYPE" VARCHAR(400) , "F_DESCRIPTION" VARCHAR(400) ) IN "TEST"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0670N The row length of the table exceeded a limit of "4005" bytes.
(Table space "TEST".) SQLSTATE=54010
liyan93834183
建好表空间后.执行接着有异常如下.这是怎么回事呢?
CREATE TABLE "DB2INST1"."T_PARENTROLE" ( "NAME" VARCHAR(400) , "ID" VARCHAR(400) , "PARENTID" VARCHAR(400) , "TABLENAME" VARCHAR(400) , "POSITION" VARCHAR(400) , "TEMPLATENAME" VARCHAR(400) , "SELFID" VARCHAR(400) , "F_ICON" VARCHAR(400) , "RESOURCETYPE" VARCHAR(400) , "F_DESCRIPTION" VARCHAR(400) ) IN "TEST"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0670N The row length of the table exceeded a limit of "4005" bytes.
(Table space "TEST".) SQLSTATE=54010
#3
那就把4K改为8K吧,可能你的表有超过4000的字段,有Xml或blob字段
#4
问题找到了.原来是数据文件损坏了..不过还是谢谢你.以后还有问题要请教.
#1
用这个创建一个表空间
CREATE REGULAR TABLESPACE TEST PAGESIZE 4K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 10.5 PREFETCHSIZE 32 TRANSFERRATE 0.14 BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY ON;
CREATE REGULAR TABLESPACE TEST PAGESIZE 4K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 OVERHEAD 10.5 PREFETCHSIZE 32 TRANSFERRATE 0.14 BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY ON;
#2
to
liyan93834183
建好表空间后.执行接着有异常如下.这是怎么回事呢?
CREATE TABLE "DB2INST1"."T_PARENTROLE" ( "NAME" VARCHAR(400) , "ID" VARCHAR(400) , "PARENTID" VARCHAR(400) , "TABLENAME" VARCHAR(400) , "POSITION" VARCHAR(400) , "TEMPLATENAME" VARCHAR(400) , "SELFID" VARCHAR(400) , "F_ICON" VARCHAR(400) , "RESOURCETYPE" VARCHAR(400) , "F_DESCRIPTION" VARCHAR(400) ) IN "TEST"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0670N The row length of the table exceeded a limit of "4005" bytes.
(Table space "TEST".) SQLSTATE=54010
liyan93834183
建好表空间后.执行接着有异常如下.这是怎么回事呢?
CREATE TABLE "DB2INST1"."T_PARENTROLE" ( "NAME" VARCHAR(400) , "ID" VARCHAR(400) , "PARENTID" VARCHAR(400) , "TABLENAME" VARCHAR(400) , "POSITION" VARCHAR(400) , "TEMPLATENAME" VARCHAR(400) , "SELFID" VARCHAR(400) , "F_ICON" VARCHAR(400) , "RESOURCETYPE" VARCHAR(400) , "F_DESCRIPTION" VARCHAR(400) ) IN "TEST"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0670N The row length of the table exceeded a limit of "4005" bytes.
(Table space "TEST".) SQLSTATE=54010
#3
那就把4K改为8K吧,可能你的表有超过4000的字段,有Xml或blob字段
#4
问题找到了.原来是数据文件损坏了..不过还是谢谢你.以后还有问题要请教.