下午实验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
收起