银行
db2 procedure export sql3001c sqloopen -2029060079 错误
os info :suse linux 11
db info :db2 v9.7.0.4
user:数据库管理员用户
--
create procedure prc_exp_inc_dat_1()
language sql
BEGIN
DECLARE v_str VARCHAR(2000) default '';
set v_str='export to /backup/2012-09-02/tst.t_tst1.del of del select * from tst.t_tst1 where sta_dte=date(''2012-09-02'') fetch first 2 rows only with ur';
call sysproc.admin_cmd(v_str);
END@
--call
call prc_exp_inc_dat_1()@
报错 ：
SQL3001C An I/O error (reason = "sqlofopn -2029060079") occurred while
opening the output file.
直接导出：db2 "export to /backup/2012-09-02/tst.t_tst1.del of del select * from tst.t_tst1 where sta_dte=date('2012-09-02') fetch first 2 rows only with ur"
是不会报错的，敬请专家帮忙看看，谢谢哈。
db info :db2 v9.7.0.4
user:数据库管理员用户
--
create procedure prc_exp_inc_dat_1()
language sql
BEGIN
DECLARE v_str VARCHAR(2000) default '';
set v_str='export to /backup/2012-09-02/tst.t_tst1.del of del select * from tst.t_tst1 where sta_dte=date(''2012-09-02'') fetch first 2 rows only with ur';
call sysproc.admin_cmd(v_str);
END@
--call
call prc_exp_inc_dat_1()@
报错 ：
SQL3001C An I/O error (reason = "sqlofopn -2029060079") occurred while
opening the output file.
直接导出：db2 "export to /backup/2012-09-02/tst.t_tst1.del of del select * from tst.t_tst1 where sta_dte=date('2012-09-02') fetch first 2 rows only with ur"
是不会报错的，敬请专家帮忙看看，谢谢哈。
7同行回答
db2inst3@dmsrvr:/tmp> db2 "call sysproc.admin_cmd('export to /tmp/a.del of del select * from test1')"
Result set 1
--------------
ROWS_EXPORTED MSG_RETRIEVAL MSG_REMOVAL
-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - -
1 record(s) selected.
Return Status = 0
db2inst3@dmsrvr:/tmp> ls -lt |grep a.del
-rw-r--r-- 1 db2fenc1 db2fadm1 2 2012-09-06 21:55 a.del
看到没，权限要是db2fenc1能写的。收起
Result set 1
--------------
ROWS_EXPORTED MSG_RETRIEVAL MSG_REMOVAL
-------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - -
1 record(s) selected.
Return Status = 0
db2inst3@dmsrvr:/tmp> ls -lt |grep a.del
-rw-r--r-- 1 db2fenc1 db2fadm1 2 2012-09-06 21:55 a.del
看到没，权限要是db2fenc1能写的。收起
浏览4528
/backup/ 目录是服务器上的目录.weiruan85 发表于 2012-9-6 21:55
嗯，以上操作我是在 服务器上/db2backup目录下执行的。包括创建存储过程和执行，以及后面的直接导出。下面的语句是否成功 ?
db2 "call sysproc.admin_cmd('export to /backup/2012-09-02/tst.t_tst1.del of del select * from tst.t_tst1 where sta_dte=date('2012-09-02') fetch first 2 rows only with ur')"收起
db2 "call sysproc.admin_cmd('export to /backup/2012-09-02/tst.t_tst1.del of del select * from tst.t_tst1 where sta_dte=date('2012-09-02') fetch first 2 rows only with ur')"收起
浏览3656
首先来看ADMIN_CMD都能做什么？我查询了一下db2的官网，V9.5中能做的事大致如下：
ADD CONTACT
ADD CONTACTGROUP
AUTOCONFIGURE
BACKUP - online only
DESCRIBE
DROP CONTACT
DROP CONTACTGROUP
EXPORT
FORCE APPLICATION
IMPORT
INITIALIZE TAPE
LOAD
PRUNE HISTORY/LOGFILE
QUIESCE DATABASE
QUIESCE TABLESPACES FOR TABLE
REDISTRIBUTE
REORG INDEXES/TABLE
RESET ALERT CONFIGURATION
RESET DATABASE CONFIGURATION
RESET DATABASE MANAGER CONFIGURATION
REWIND TAPE
RUNSTATS
SET TAPE POSITION
UNQUIESCE DATABASE
UPDATE ALERT CONFIGURATION
UPDATE CONTACT
UPDATE CONTACTGROUP
UPDATE DATABASE CONFIGURATION
UPDATE DATABASE MANAGER CONFIGURATION
UPDATE HEALTH NOTIFICATION CONTACT LIST
UPDATE HISTORY收起
ADD CONTACT
ADD CONTACTGROUP
AUTOCONFIGURE
BACKUP - online only
DESCRIBE
DROP CONTACT
DROP CONTACTGROUP
EXPORT
FORCE APPLICATION
IMPORT
INITIALIZE TAPE
LOAD
PRUNE HISTORY/LOGFILE
QUIESCE DATABASE
QUIESCE TABLESPACES FOR TABLE
REDISTRIBUTE
REORG INDEXES/TABLE
RESET ALERT CONFIGURATION
RESET DATABASE CONFIGURATION
RESET DATABASE MANAGER CONFIGURATION
REWIND TAPE
RUNSTATS
SET TAPE POSITION
UNQUIESCE DATABASE
UPDATE ALERT CONFIGURATION
UPDATE CONTACT
UPDATE CONTACTGROUP
UPDATE DATABASE CONFIGURATION
UPDATE DATABASE MANAGER CONFIGURATION
UPDATE HEALTH NOTIFICATION CONTACT LIST
UPDATE HISTORY收起
浏览4230