SQL0668N Operation not allowed for reason code "1&_施嘉伟forever

http://blog.sina.com.cn/u/1929988847

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

SQL0668N Operation not allowed for reason code "1&

(2014-11-19 18:30:17)

标签：

db2

it

杭州美创科技有限公司

分类： DB2

————————————————————–Step 1——————————————————————–

On product environment

Listing 1. 导出表格数据

$ mkdir -p /db2backup/ctrold/exp

$ cd /db2backup/ctrold/exp

$ db2move ctrold export

————————————————————–Step 2——————————————————————–

On product environment

Listing 2. 导出DDL语句

$ db2look -d ctrold -e -l -a -o db2look.sql

– Generate statistics for all creators

– Creating DDL for table(s)

– Output is sent to file: db2look.sql

————————————————————–Step 3——————————————————————–

On target environment

Listing 3. 传到目标环境

$ mkdir -p /db2backup/ctrold/exp

$ cd /db2backup/ctrold/exp

ftp 10.44.65.74

ftp> cd /db2backup/ctrold/exp

ftp> prompt

Interactive mode Off .

ftp> bin

200 Type set to I.

ftp> mget *

————————————————————–Step 4——————————————————————–

On target environment

Listing 3.修改db2look.sql文件

CONNECT TO IDM;

------------------------------------

-- DDL Statements for BUFFERPOOLS --

------------------------------------

CREATE BUFFERPOOL "BP16K" SIZE 200 PAGESIZE 16384;

CREATE BUFFERPOOL "IDMBPTMP" SIZE 5000 PAGESIZE 16384;

CONNECT RESET;

CONNECT TO IDM;

------------------------------------

-- DDL Statements for TABLESPACES --

------------------------------------

CREATE REGULAR TABLESPACE IDMTBSUSR IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE

USING (FILE '/db2/idm/oausrdata/IDMTBSUSR01.DAT'449000)

EXTENTSIZE 16

PREFETCHSIZE 16

BUFFERPOOL BP16K

OVERHEAD 10.500000

TRANSFERRATE 0.140000

DROPPED TABLE RECOVERY ON;

CREATE LARGE TABLESPACE IDMTBSUSRLARGE IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE

USING (FILE '/db2/idm/oausrdata/IDMTBSUSR02.DAT'256000)

EXTENTSIZE 8

PREFETCHSIZE 8

BUFFERPOOL IBMDEFAULTBP

OVERHEAD 10.500000

TRANSFERRATE 0.140000;

CREATE USER TEMPORARY TABLESPACE IDMTBSTMP IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE

USING (FILE '/db2/idm/oatmpdata/IDMTBSTMP.DAT'64000)

EXTENTSIZE 16

PREFETCHSIZE 16

BUFFERPOOL IDMBPTMP

OVERHEAD 10.500000

TRANSFERRATE 0.140000;

CREATE REGULAR TABLESPACE SYSTOOLSPACE IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 4096 MANAGED BY SYSTEM

USING ('/db2/idm/SYSTOOLSPACE')

EXTENTSIZE 32

PREFETCHSIZE AUTOMATIC

BUFFERPOOL IBMDEFAULTBP

OVERHEAD 12.670000

TRANSFERRATE 0.180000

DROPPED TABLE RECOVERY ON;

在目标库下面建立相应的表空间oausrdata，oatmpdata，SYSTOOLSPACE

导入ddl语句

$ db2 -tvf db2look.sql > /db2backup/ctrold/exp/db2look.out

$ db2 -tvf db2look.sql > /db2backup/ctrold/exp/db2look2.out

导入数据

$ db2move ctrold load

————————————————————–Step 5——————————————————————–

Listing 5. 对比

product

db2 => select count(*),type from syscat.tables where tabschema='db2inst1' GROUP BY TYPE

1 TYPE

———– —-

8 N

27 T

3 V

3 record(s) selected.

target

db2 => select count(*),type from syscat.tables where tabschema='db2inst1' GROUP BY TYPE

1 TYPE

———– —-

27 T

3 V

2 record(s) selected.

TYPE CHAR(1) Type of object.

A = Alias

G = Global temporary table

H = Hierarchy table

L = Detached table

N = Nickname

S = Materialized query table

T = Table (untyped)

U = Typed table

V = View (untyped)

W = Typed view

以上的结果表明table 类型为 N = Nickname没有创建 nickname类似于oracle的dblink

db2 => CREATE WRAPPER "DRDA" LIBRARY 'libdb2drda.a' OPTIONS (DB2_FENCED 'N')

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL20076N The instance for the database is not enabled for the specified

action or operation. Reason code = "1". SQLSTATE=0A502

db2 => ? SQL20076N

解决方法:

db2 update database manager configuration using federated yes

重启实例

db2stop

db2start

创建nickname

CREATE WRAPPER "DRDA" LIBRARY 'libdb2drda.a' OPTIONS (DB2_FENCED 'N')

CREATE SERVER "ROEEESERVER" TYPE DB2/UDB VERSION '8.1' WRAPPER "DRDA" AUTHORIZATION "db2inst1" PASSWORD "db2inst1" OPTIONS(DBNAME 'ROEEEOLD');

CREATE USER MAPPING FOR db2inst1 SERVER "ROEEESERVER" OPTIONS (REMOTE_AUTHID 'db2inst1',REMOTE_PASSWORD 'db2inst1');

select 'select count(1) from '||tabschema||'.'||TABNAME from syscat.tables where TABSCHEMA='WAVESET'

select count(1)from WAVESET .ACCOUNT

select count(1)from WAVESET .ACCTATTR

select count(1)from WAVESET .ACCTCHANGE

select count(1)from WAVESET .ATTRIBUTE

select count(1)from WAVESET .ENTATTR

select count(1)from WAVESET .ENTCHANGE

select count(1)from WAVESET .ENTITLE

select count(1)from WAVESET .EXPLAIN_ARGUMENT

select count(1)from WAVESET .EXPLAIN_INSTANCE

select count(1)from WAVESET .EXPLAIN_OBJECT

select count(1)from WAVESET .EXPLAIN_OPERATOR

select count(1)from WAVESET .EXPLAIN_PREDICATE

select count(1)from WAVESET .EXPLAIN_STATEMENT

select count(1)from WAVESET .EXPLAIN_STREAM

select count(1)from WAVESET .LOG

select count(1)from WAVESET .LOGATTR

select count(1)from WAVESET .OBJCHANGE

select count(1)from WAVESET .OBJECT

select count(1)from WAVESET .OPERATETIME

select count(1)from WAVESET .ORG

select count(1)from WAVESET .ORGATTR

select count(1)from WAVESET .ORGCHANGE

select count(1)from WAVESET .ORG_ID_NAME

select count(1)from WAVESET .ORG_SAP

select count(1)from WAVESET .ORG_SAPTOIDM

select count(1)from WAVESET .ORG_SAPTOIDM_TEST

select count(1)from WAVESET .ORG_SAP_IDM

select count(1)from WAVESET .QATTR

select count(1)from WAVESET .QCHANGE

select count(1)from WAVESET .QUEUE

select count(1)from WAVESET .ROLEATTR

select count(1)from WAVESET .ROLECHANGE

select count(1)from WAVESET .ROLEOBJ

select count(1)from WAVESET .SAPORIDMORG

select count(1)from WAVESET .SAPORIDMUSER

select count(1)from WAVESET .SLOGATTR

select count(1)from WAVESET .SYSLOG

select count(1)from WAVESET .TASK

select count(1)from WAVESET .TASKATTR

select count(1)from WAVESET .TASKCHANGE

select count(1)from WAVESET .TEMPSAPUSER

select count(1)from WAVESET .TESTORG

select count(1)from WAVESET .USERATTR

select count(1)from WAVESET .USERCHANGE

select count(1)from WAVESET .USEROBJ

select count(1)from WAVESET .USER_SAP

select count(1)from WAVESET .USER_SAP_BACKUP

select count(1)from WAVESET .USER_SAP_COMPARE

select count(1)from WAVESET .USER_SAP_IDM

select count(1)from WAVESET .USER_SAP_IDM_TEST

select count(1)from WAVESET .USER_TEST

select count(1)from WAVESET .USER_TEST2

select count(1)from WAVESET .ZJYC_ORG

select count(1)from WAVESET .ZJYC_PROPERTY

select count(1)from WAVESET .ZY_PROPERTY

可能会遇到

db2 => select count(1)from WAVESET .EXPLAIN_ARGUMENT

-----------

SQL0668N Operation not allowed for reason code "1" on table

"WAVESET.EXPLAIN_ARGUMENT". SQLSTATE=57016

解决方法：

db2 "select 'db2 set integrity for oatest.'||TABNAME||' immediate checked' from syscat.tables where TABSCHEMA='OATEST' and STATUS='C'"

[db2inst1@devsvr2 oatest0303]$ db2 "select 'db2 set

integrity for oatest.'||TABNAME||' immediate checked'

from syscat.tables where TABSCHEMA='OATEST' and STATUS='C'"

-----------------------------------------------------------------

db2 set integrity for oatest.CAL_AUTH immediate checked

……

db2 set integrity for oatest.UM_USER immediate checked

81 条记录已选择。

执行这 81 条语句，语句有可能报错

主要命令：

db2 set integrity for oatest. CAL_AUTH immediate checked

[db2inst1@devsvr2 oatest0303]$ db2 set integrity for oatest.CAL_AUTH immediate checked

DB20000I SQL 命令成功完成。

[db2inst1@devsvr2 oatest0303]$ db2 set integrity for oatest.UM_USER immediate checked

DB20000I SQL 命令成功完成。

…………

如果报错，再反复执行这些语句直到下面的语句结果返回 0 条记录

[db2inst1@devsvr2 oatest0303]$ db2 "select 'db2 set integrity

for oatest.'||TABNAME||' immediate checked' from syscat.tables

where TABSCHEMA='OATEST' and STATUS='C'"

---------------------------------------------------------------

0 条记录已选择。

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：db2逻辑整库迁移

后一篇：db2 异机备份恢复（不重定向）

新浪BLOG意见反馈留言板　欢迎批评指正