IT分销/经销数据库command

savepoint和外部脚本调用问题

下午实验savepoint功能,为什么会报错?具体过程如下
db2 => create table savepoint(id int, name char(10))
    DB20000I The SQL command completed successfully.
    db2 => insert into savepoint values(1,'test1')
    DB20000I The SQL command completed successfully.
    db2 => savepoint savepoint1 on rollback retain cursors
    DB20000I The SQL command completed successfully.
    db2 => insert into savepoint values(2,'savepoint1')
    DB20000I The SQL command completed successfully.
    db2 => savepoint savepoint2 on rollback retain cursors
    DB20000I The SQL command completed successfully.
    db2 => select * from savepoint

    ID NAME
    ----------- ----------
    1 test1
    2 savepoint1

    db2 => rollback to savepoint savepoint1
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0880N SAVEPOINT "SAVEPOINT1" does not exist or is invalid in this context.
    SQLSTATE=3B001

还有如何执行外部脚本,在调用外部脚本时报错
$ more  createSQLproc.db2
CREATE PROCEDURE sales_status
(IN quota INTEGER, OUT sql_state CHAR(5))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE SQLSTATE CHAR(5);
  DECLARE rs CURSOR WITH RETURN FOR
  SELECT sales_person, SUM(sales) AS total_sales
    FROM sales
    GROUP BY sales_person
    HAVING SUM(sales) > quota;
  OPEN rs;
  SET sql_state = SQLSTATE;
@
$ db2 connect to sample
   Database Connection Information

Database server        = DB2/LINUX 9.7.5
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE

[db2inst1@dg2 ~]$ db2 -td@ -vf createSQLproc.db2
CREATE PROCEDURE sales_status (IN quota INTEGER, OUT sql_state CHAR(5)) DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN DECLARE SQLSTATE CHAR(5); DECLARE rs CURSOR WITH RETURN FOR SELECT sales_person, SUM(sales) AS total_sales FROM sales GROUP BY sales_person HAVING SUM(sales) > quota; OPEN rs; SET sql_state = SQLSTATE;
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
参与10

9同行回答

czmmiaoczmmiao数据库管理员长天科技
回复 2# wp28556259 有没有什么配置可以让db2不进行默认提交呢?除了像你那样写,当已经进入了db2=>交互模式下,是否有办法不进行默认提交呢?显示全部
回复 2# wp28556259
有没有什么配置可以让db2不进行默认提交呢?除了像你那样写,当已经进入了
db2=>
交互模式下,是否有办法不进行默认提交呢?收起
IT分销/经销 · 2012-01-31
浏览905
一米阳光一米阳光数据库管理员阳光
你的procedure里面有begin没有end,最后加上end就可以了。C:\WORK>db2 -td@ -f 1.txtDB20000I  SQL命令成功完成。C:\WORK>more 1.txtCREATE PROCEDURE sales_status(IN quota INTEGER, OUT sql_state CHAR(5))DYNAMIC RESULT SETS 1LANGUAGE SQLBEGIN &nbs...显示全部
你的procedure里面有begin没有end,最后加上end就可以了。

C:\WORK>db2 -td@ -f 1.txt
DB20000I  SQL命令成功完成。


C:\WORK>more 1.txt
CREATE PROCEDURE sales_status
(IN quota INTEGER, OUT sql_state CHAR(5))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
  DECLARE SQLSTATE CHAR(5);
  DECLARE rs CURSOR WITH RETURN FOR
  SELECT * from test fetch first 1 rows only;
  OPEN rs;
  SET sql_state = SQLSTATE;
End
@收起
IT分销/经销 · 2012-01-30
浏览874

提问者

czmmiao
数据库管理员长天科技

相关问题

相关资料

相关文章

问题状态

  • 发布时间:2012-01-30
  • 关注会员:1 人
  • 问题浏览:6593
  • 最近回答:2012-02-01
  • X社区推广