oracle通过透明网关模式访问db2,将db2表置为read only access

问题现象:oracle12c通过透明网关方式连接db2 9.7,一旦从oracle端使用plsqldev发起查询,db2的表就变成read only access了,不知道是设置问题还是数据库版本差异特性导致的,求解。显示全部

问题现象:oracle12c通过透明网关方式连接db2 9.7,一旦从oracle端使用plsqldev发起查询,db2的表就变成read only access了,不知道是设置问题还是数据库版本差异特性导致的,求解。

收起
参与7

返回冯帅的回答

冯帅冯帅  数据库管理员 , 贝壳金服

这个可能跟你的db2设置有关系,可以看下你db2表的定义 默认读写控制选项 ALLOW READ ACCESS、
ALLOW NO ACCESS

If a load operation is interrupted or fails, it remains at the same access level that was specified when the load operation was issued. That is, if a load operation in ALLOW NO ACCESS mode fails, the table data is inaccessible until a load terminate or a load restart is issued. If a load operation in ALLOW READ ACCESS mode aborts, the preexisting table data is still accessible for read access.
If the ALLOW READ ACCESS option was specified for an interrupted or failed load operation, it can also be specified for the load restart or load terminate operation. However, if the interrupted or failed load operation specified the ALLOW NO ACCESS option, the ALLOW READ ACCESS option cannot be specified for the load restart or load terminate operation.

更多
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.admin.dm.doc/doc/c0007909.html

补充下

ALLOW NO ACCESS
Load will lock the target table for exclusive access during the load. The table state will be set to Load In Progress during the load. ALLOW NO ACCESS is the default behavior. It is the only valid option for LOAD REPLACE.
When there are constraints on the table, the table state will be set to Set Integrity Pending as well as Load In Progress. The SET INTEGRITY statement must be used to take the table out of Set Integrity Pending state.
ALLOW READ ACCESS
Load will lock the target table in a share mode. The table state will be set to both Load In Progress and Read Access. Readers can access the non-delta portion of the data while the table is being load. In other words, data that existed before the start of the load will be accessible by readers to the table, data that is being loaded is not available until the load is complete. LOAD TERMINATE or LOAD RESTART of an ALLOW READ ACCESS load can use this option; LOAD TERMINATE or LOAD RESTART of an ALLOW NO ACCESS load cannot use this option. Furthermore, this option is not valid if the indexes on the target table are marked as requiring a rebuild.
When there are constraints on the table, the table state will be set to Set Integrity Pending as well as Load In Progress, and Read Access. At the end of the load, the table state Load In Progress will be removed but the table states Set Integrity Pending and Read Access will remain. The SET INTEGRITY statement must be used to take the table out of Set Integrity Pending. While the table is in Set Integrity Pending and Read Access states, the non-delta portion of the data is still accessible to readers, the new (delta) portion of the data will remain inaccessible until the SET INTEGRITY statement has completed. A user can perform multiple loads on the same table without issuing a SET INTEGRITY statement. Only the original (checked) data will remain visible, however, until the SET INTEGRITY statement is issued.

融资租赁 · 2017-07-19
浏览1986
  • 这里只进行了查询操作,还没有进行load操作 附表结构: CREATE TABLE "DB2ADMIN"."CIGINFO" ("CIG_INFO_ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, MINVALUE 1, MAXVALUE 9223372036854775807, NO CYCLE, NO ORDER), "CIG_BRAND" CHARACTER(6) DEFAULT '', "PLANOWNER_CODE" CHARACTER(8) DEFAULT '', "REJECT_TIME" TIMESTAMP, "PLAN_YEAR" INTEGER, "PLAN_QUARTER" INTEGER, "PLAN_MONTH" INTEGER, "STATE" VARCHAR(2) DEFAULT '0', "PRINTER_CODE" CHARACTER(12) DEFAULT '', "REPORT_STATUS" CHARACTER(1) DEFAULT '2', "IFLEGAL" CHARACTER(1), "BAL_UUID" VARCHAR(64), "REAL_DATE" TIMESTAMP, "AP_UUID" VARCHAR(64), "SCAN_STATE" VARCHAR(2) ) DATA CAPTURE NONE IN "CIGSPACE" INDEX IN "IDXCSPACE" ORGANIZE BY DIMENSIONS ("FACT_CODE", "CIG_BRAND", "STATE" ); ALTER TABLE "DB2ADMIN"."CIGINFO" LOCKSIZE ROW APPEND OFF NOT VOLATILE LOG INDEX BUILD NULL; GRANT SELECT ON TABLE "DB2ADMIN"."CIGINFO" TO USER "PCGL"; ALTER TABLE "DB2ADMIN"."CIGINFO" ADD CONSTRAINT "P_KEY_1" PRIMARY KEY ("CIG_INFO_ID" ); 在oracle是PCGL的用户进行查询的。
    2017-07-19

回答者

冯帅
数据库管理员贝壳金服

冯帅 最近回答过的问题

回答状态

  • 发布时间:2017-07-19
  • 关注会员:2 人
  • 回答浏览:1986
  • X社区推广