在DB2 SQL中如何做......好......任何...... [英] How to do...well...anything...in DB2 SQL
问题描述
我已经陷入了陷入DB2的困境，必须开始编写诸如表格，索引，存储过程，触发器等等的
。在线参考
只是非常有帮助。这两本pdf手册非常有用。谷歌搜索是
只是如此有用。
所以让我们从一些简单的SQL结构开始，我非常清楚
SQL Server，看起来就像是在咬DB2。
1.选择一个值
SQL Server：
SELECT''Hello，world！''
（1行受影响）
DB2：
SELECT''Hello，world！''
错误：SQL0104N意外的令牌END-OF-STATEMENT发现以下
LECT'Hello，world！''"。预期的代币可能包括：< table_expr>。
SQLSTATE = 42601
（状态：42601，原生代码：FFFFFF98）
SELECT''Hello，world！''来自SYSIBM.SysDummy1
1行受影响
是否有SysDummy2？ 3？为什么？
1.声明变量
SQL Server：
DECLARE @SavedUserID int
命令已成功完成。
DB2：
DECLARE SavedUserID整数;
错误：SQL0104N意外标记整数发现在 DECLARE
SavedUserID"。预期的代币可以包括：结束陈述。 LINE
NUMBER = 1。 SQLSTATE = 42601
（状态：42601，原生代码：FFFFFF98）
2.设置变量
SQL Server：
DECLARE @ProcessID int
SET @ProcessID = @spid
或
SET @SavedUserID =（SELECT User_ID FROM Connection_Users WHERE spid =
@@ spid）
或（depricated）
SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@ spid
DB2：
DECLARE ApplicationID varchar（128） - 不能声明变量
SET ApplicationID = Application_ID（）
或
DECLARE ApplicationID varchar（128） - 不能声明变量
SET ApplicationID =（SELECT APPLICATION_ID（）FROM SYSIBM.SYSDUMMY1 ）;
3.返回值
SQL Server：
SELECT @@ spid AS ProcessID
或
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID
DB2
SELECT Application_ID （）
错误：SQL0104N意外的令牌END-OF-STATEMENT发现在
" ect application_ID（）"后面。预期的代币可能包括：< table_expr>。
SQLSTATE = 42601
（状态：42601，原生代码：FFFFFF98）
或
SELECT Application_ID（）FROM IBM.SysDummy1 --SysDummy2
或
DECLARE ApplicationID varchar（128） - 不能声明变量
SET ApplicationID =（SELECT APPLICATION_ID（）FROM SYSIBM.SYSDUMMY1）;
SELECT ApplicationID
3.返回行来自存储过程
SQL Server
CREATE PROCEDURE foo AS
SELECT @@ spid AS ProcessID
DB2
CREATE PROCEDURE foo
动态结果集1
语言SQL
P1：BEGIN
- 声明光标
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT Application_ID（）FROM SYSIBM.SYSDUMMY1;
- - 光标保持打开状态供客户端应用
OPEN cursor1;
END P1
错误：SQL0104N意外的令牌&quo吨; END-OF-STATEMENT"在ROM SYSIBM.SYSDUMMY1之后被发现
。预期的代币可能包括：JOIN
< joined_table>。 LINE NUMBER = 7。 SQLSTATE = 42601
（状态：42601，本机代码：FFFFFF98）
错误：SQL0198N PREPARE或EXECUTE IMMEDIATE的语句字符串
语句是空的还是空的。 SQLSTATE = 42617
（状态：42617，本机代码：FFFFFF3A）
错误：SQL0104N意外的令牌END-OF-STATEMENT在END P1之后被发现
。预期的代币可能包括：JOIN< joined_table>。
SQLSTATE = 42601
（状态：42601，原生代码：FFFFFF98）
最后，我试图在DB2中创建的完全触发器，我不能再为
工作。
CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
- " LI_DAILYLOGS" =LogInsert_DailyLogs DB2中的18个字符限制
插入后
ON SUPERDUDE.DAILY_LOGS
参考NEW_TABLE as INSERTED
每个语句
模式DB2SQL
BEGIN ATOMIC
- 加载保存的用户ID
DECLARE SavedUserID整数;
SELECT SavedUserID = User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID（）;
INSERT INTO Audit_Log（
ChangeDate，
RowID，
ChangeType，
- 用户名，主机名，AppName，
UserID，
TableName，
FieldName，
TagID，
标签，
OldValue，
NewValue）
SELECT
getdate（），
i。 Daily_Log_ID，
''INSERTED''，
- USER_NAME（），HOST_NAME（），APP_NAME（），
SavedUserID，
''Daily_Logs''， - TableName
''''， - FieldName
NULL， - TagID
i。姓名， - 标签
''''， - OldValue
'''' - NewValue
来自插入i;
END;
错误：SQL0104N意外的令牌整数发现在 DECLARE
SavedUserID"。预期的代币可以包括：结束陈述。 LINE
NUMBER = 10。 SQLSTATE = 42601
（状态：42601，本机代码：FFFFFF98）
错误：SQL0104N意外的令牌=在SELECT
SavedUserID之后被发现。预期的令牌可以包括：< space>。 SQLSTATE = 42601
（状态：42601，本机代码：FFFFFF98）
错误：SQL0204N" SUPERDUDE.INSERTED"是一个未定义的名称。 SQLSTATE = 42704
（状态：42704，本机代码：FFFFFF34）
错误：SQL0104N意外的令牌END-OF-STATEMENT被发现在
END之后。预期的令牌可以包括：JOIN< joined_table>。 SQLSTATE = 42601
（状态：42601，原生代码：FFFFFF98）
i''ve been thrown into a pit with DB2 and have to start writing things such
as tables, indexes, stored procedures, triggers, etc. The online reference
is only so helpful. The two pdf manuals are only so helpful. Googling is
only so helpful.
So let''s start with some simple SQL constructs, that i know so very well in
SQL Server, that seem to be like pulling teeth in DB2.
1. Selecting a value
SQL Server:
SELECT ''Hello, world!''
(1 row(s) affected)
DB2:
SELECT ''Hello, world!''
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"LECT ''Hello, world!''". Expected tokens may include: "<table_expr>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
SELECT ''Hello, world!'' FROM SYSIBM.SysDummy1
1 Row(s) affected
Is there a SysDummy2? 3? Why?
1. Declaring a variable
SQL Server:
DECLARE @SavedUserID int
The command(s) completed successfully.
DB2:
DECLARE SavedUserID integer;
Error: SQL0104N An unexpected token "integer" was found following " DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=1. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
2. Setting a variable
SQL Server:
DECLARE @ProcessID int
SET @ProcessID = @spid
or
SET @SavedUserID = (SELECT User_ID FROM Connection_Users WHERE spid =
@@spid)
or (depricated)
SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@spid
DB2:
DECLARE ApplicationID varchar(128) --can''t declare variables
SET ApplicationID = Application_ID()
or
DECLARE ApplicationID varchar(128) --can''t declare variables
SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);
3. Returning a value
SQL Server:
SELECT @@spid AS ProcessID
or
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID
DB2
SELECT Application_ID()
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"ect application_ID()". Expected tokens may include: "<table_expr>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
or
SELECT Application_ID() FROM IBM.SysDummy1 --SysDummy2
or
DECLARE ApplicationID varchar(128) --can''t declare variables
SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);
SELECT ApplicationID
3. Returning rows from a stored procedure
SQL Server
CREATE PROCEDURE foo AS
SELECT @@spid AS ProcessID
DB2
CREATE PROCEDURE foo
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
--declare the cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT Application_ID() FROM SYSIBM.SYSDUMMY1;
-- Cursor left open for client application
OPEN cursor1;
END P1
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found
following "ROM SYSIBM.SYSDUMMY1". Expected tokens may include: "JOIN
<joined_table>". LINE NUMBER=7. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE
statement is blank or empty. SQLSTATE=42617
(State:42617, Native Code: FFFFFF3A)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found
following "END P1". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
And finally, the full trigger i''m trying to create in DB2 that i can''t can''t
make work.
CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
--"LI_DAILYLOGS" = "LogInsert_DailyLogs" 18 character limit in DB2
AFTER INSERT
ON SUPERDUDE.DAILY_LOGS
REFERENCING NEW_TABLE AS INSERTED
FOR EACH STATEMENT
MODE DB2SQL
BEGIN ATOMIC
-- Load the saved UserID
DECLARE SavedUserID integer;
SELECT SavedUserID = User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID();
INSERT INTO Audit_Log(
ChangeDate,
RowID,
ChangeType,
-- Username, HostName, AppName,
UserID,
TableName,
FieldName,
TagID,
Tag,
OldValue,
NewValue)
SELECT
getdate(),
i.Daily_Log_ID,
''INSERTED'',
-- USER_NAME(), HOST_NAME(), APP_NAME(),
SavedUserID,
''Daily_Logs'', --TableName
'''', --FieldName
NULL, --TagID
i.Name, --Tag
'''', --OldValue
'''' --NewValue
FROM Inserted i;
END;
Error: SQL0104N An unexpected token "integer" was found following " DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "=" was found following "SELECT
SavedUserID ". Expected tokens may include: "<space>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0204N "SUPERDUDE.INSERTED" is an undefined name. SQLSTATE=42704
(State:42704, Native Code: FFFFFF34)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
推荐答案
>所以让我们开始吧有一些简单的SQL结构，我在
>So let''s start with some simple SQL constructs, that i know so very well in
SQL Server中非常熟悉，这似乎就像在DB2中扯皮一样。
虽然我同意DB2可能很乏味和令人困惑，但请注意SQL
服务器非常简单，它几乎打破了标准。
所以，给db2一个机会。 :)只要你有这样的态度，它可以做到b $ b，你就是不知道怎么样，这个群体的用户非常有帮助。
1.选择一个值
SQL Server：
SELECT''你好，世界！''
这完全不正确。它不是SQL。这是一种方便
由Sybase / SQL Server添加。
在DB2世界中，您使用：VALUES''Hello World''
SELECT''Hello，world！''来自SYSIBM.SysDummy1
1行受影响
是否有SysDummy2？ 3？为什么？
与其他系统的兼容性，不使用特殊声明，
并要求* all * statments包含TABLE引用。 Oracle
提供一个名为Dual的单记录表。其他系统使用其他名称。
1.声明变量
SQL Server：
DECLARE @SavedUserID int
命令已成功完成。
删除无用的@符号，并用分号结束所有语句。
此外，没有隐式代码块，所以你必须启动你自己。
BEGIN
DECLARE SaverUserID INT;
END
3 。返回值
SQL Server：
SELECT @@ spid AS ProcessID
或
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ ProcessID
再次使用VALUES。
3.从存储过程返回行
SQL Server
CREATE PROCEDURE foo AS
SELECT @@ spid AS ProcessID
DB2
CREATE PROCEDURE foo
动态结果集1
LANGUAGE SQL
P1：BEGIN
- 声明光标
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT Application_ID（）FROM SYSIBM.SYSDUMMY1;
- 光标左侧打开以供客户端应用程序
OPEN cursor1;
END P1
db2 => create function application_id（）返回int返回1
DB20000I SQL命令成功完成。
db2 => CREATE PROCEDURE foo \
db2（续）=>动态结果集1
\
db2（续）=> LANGUAGE SQL
\
db2（续）=> P1：BEGIN
\
db2（续）=> - 声明光标
\
db2（续）=> DECLARE cursor1 CURSOR WITH RETURN FOR
\
db2（续）=> SELECT Application_ID（）FROM SYSIBM.SYSDUMMY1;
\
db2（续）=> - 游标保持打开状态以供客户申请
\
db2（续）=> OPEN cursor1;
\
db2（续）=>结束P1
DB20000I SQL命令成功完成。
适用于我。 :)
最后，我试图在DB2中创建的完全触发器，我不能做的工作。
SQL Server, that seem to be like pulling teeth in DB2.
Though i agree that DB2 can be tedious and confusing, note that SQL
Server is super easy, and it pretty much breaks the standard to do it.
So, give db2 a shot. :) As long as you come with the attitude that it
can be done, you just don''t know how, the users of this group are very
helpful.
1. Selecting a value
SQL Server:
SELECT ''Hello, world!''
This is wholly incorrect. It is not SQL whatsoever. It is a convenience
added by Sybase/SQL Server.
In the DB2 world, you use: VALUES ''Hello World''
SELECT ''Hello, world!'' FROM SYSIBM.SysDummy1
1 Row(s) affected
Is there a SysDummy2? 3? Why?
Compatability with other systems, that do not use a special statement,
and require *all* statments to include a TABLE reference. Oracle
supplies a one-record TABLE called Dual. Other systems use other names.
1. Declaring a variable
SQL Server:
DECLARE @SavedUserID int
The command(s) completed successfully.
Drop the useless @ symbol, and end all statement with a semi-colon.
Also, there is no implicit block of code, so you must start your own.
BEGIN
DECLARE SaverUserID INT;
END
3. Returning a value
SQL Server:
SELECT @@spid AS ProcessID
or
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID
Again, use VALUES.
3. Returning rows from a stored procedure
SQL Server
CREATE PROCEDURE foo AS
SELECT @@spid AS ProcessID
DB2
CREATE PROCEDURE foo
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
--declare the cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT Application_ID() FROM SYSIBM.SYSDUMMY1;
-- Cursor left open for client application
OPEN cursor1;
END P1
db2 => create function application_id() returns int return 1
DB20000I The SQL command completed successfully.
db2 => CREATE PROCEDURE foo \
db2 (cont.) => DYNAMIC RESULT SETS 1
\
db2 (cont.) => LANGUAGE SQL
\
db2 (cont.) => P1: BEGIN
\
db2 (cont.) => --declare the cursor
\
db2 (cont.) => DECLARE cursor1 CURSOR WITH RETURN FOR
\
db2 (cont.) => SELECT Application_ID() FROM SYSIBM.SYSDUMMY1;
\
db2 (cont.) => -- Cursor left open for client application
\
db2 (cont.) => OPEN cursor1;
\
db2 (cont.) => END P1
DB20000I The SQL command completed successfully.
Works for me. :)
And finally, the full trigger i''m trying to create in DB2 that i can''t can''t
make work.
我对DB2中的TRIGGER很少有经验。我会把它留给其他人。 :)
B.
I have little experience with TRIGGERs in DB2. I''ll leave that to
someone else. :)
B.
什么是鱼的味道???哦，没关系，只是一个巨魔。
-
ML
What''s the fish smell??? Oh, nevermind, just a troll.
--
ML
Ian Boyd写道：
Ian Boyd wrote:
我已经陷入了与DB2的陷阱，必须开始编写诸如表，索引，存储过程，触发器等内容。在线参考
只是非常有用。这两本pdf手册非常有用。谷歌搜索只是如此有用。
所以让我们从一些简单的SQL结构开始，我在SQL Server中非常了解它，这似乎就像拉动DB2中的牙齿。
1.选择一个值
SQL Server：
SELECT''Hello，world！''
（1行（s）受影响的）
这不是标准化的SQL，它总是需要在
SELECT语句中使用FROM子句。所以你可以这样做：
选择''abc''
来自sysibm.sysdummy1
有一个请看这里引用的表格： http://tinyurl.com/ohtzg
或使用表构造函数：
VALUES''abc''
1.声明变量
SQL Server：
DECLARE @SavedUserID int
命令已成功完成。
'''''也不是SQL。
DB2：
DECLARE SavedUserID整数;
错误：SQL0104N意外的标记整数在
DECLARE
SavedUserID之后被发现。预期的代币可以包括：结束陈述。 LINE
NUMBER = 1。 SQLSTATE = 42601
（状态：42601，原生代码：FFFFFF98）
你的问题可能是你没有明确指定
语句终止。所以行尾会终止你的SQL语句，
并留下''整数;''作为......某事...... DB2正确地说
抱怨关于。尝试使用db2的-t选项。而不是命令行（或者
如果使用命令编辑器则搜索菜单）。
2.设置变量
SQL Server：
DECLARE @ProcessID int
SET @ProcessID = @spid
或
SET @SavedUserID =（SELECT User_ID FROM Connection_Users WHERE spid =
@@ spid）
或（已删除）
SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@ spid
DB2：
DECLARE ApplicationID varchar（128） - 不能声明变量
SET ApplicationID = Application_ID（）
或
DECLARE ApplicationID varchar（128） - 不能声明变量
SET ApplicationID =（SELECT APPLICATION_ID（）FROM SYSIBM.SYSDUMMY1）;
像SQL这样的面向集合的语言中的第一个问题是：你有什么想要使用你真正需要程序逻辑的值来实现
。
3.返回值
SQL Server：
SELECT @@ spid AS ProcessID
或
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID
使用此：
VALUES application_id（）
然后从以这种方式创建的表中获取。
最后，我试图在DB2中创建的完全触发器，我不能
不能开始工作。
创建TRIGGER SUPERDUDE.LI_DAILYLOGS
- " LI_DAILYLOGS" =LogInsert_DailyLogs DB2中的18个字符限制
插入后SUPVERDUDE.DAILY_LOGS
参考NEW_TABLE作为插入
对于每个语句
模式DB2SQL
BEGIN ATOMIC
- 加载保存的UserID
DECLARE SavedUserID整数;
SELECT SavedUserID = User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID（）;
SET SavedUserID =（SELECT ...）;
INSERT INTO Audit_Log（
ChangeDate，
RowID，
ChangeType，
- 用户名，主机名，AppName，
UserID，
TableName，
FieldName，
TagID，
标签，
OldValue，
NewValue）
SELECT
getdate（），
i.Daily_Log_ID，
''INSERTED''，
- USER_NAME（），HOST_NAME（） ，APP_NAME（），
SavedUserID，
''Daily_Logs''， - TableName
''''， - FieldName
NULL， - TagID
我.Name， - 标签
''''， - OldValue
'''' - NewValue
来自插入i;
结束;
错误：SQL0104N意外的标记整数发现在
DECLARE SavedUserID之后。预期的代币可能包括：END-OF-STATEMENT。
LINE NUMBER = 10。 SQLSTATE = 42601
（状态：42601，本机代码：FFFFFF98）
i''ve been thrown into a pit with DB2 and have to start writing things such
as tables, indexes, stored procedures, triggers, etc. The online reference
is only so helpful. The two pdf manuals are only so helpful. Googling is
only so helpful.
So let''s start with some simple SQL constructs, that i know so very well
in SQL Server, that seem to be like pulling teeth in DB2.
1. Selecting a value
SQL Server:
SELECT ''Hello, world!''
(1 row(s) affected)
This is not standardized SQL, which always requires a FROM clause in a
SELECT statement. So you can do this:
SELECT ''abc''
FROM sysibm.sysdummy1
have a look here for the table referenced: http://tinyurl.com/ohtzg
or use a table constructor:
VALUES ''abc''
1. Declaring a variable
SQL Server:
DECLARE @SavedUserID int
The command(s) completed successfully.
The ''@'' isn''t SQL either.
DB2:
DECLARE SavedUserID integer;
Error: SQL0104N An unexpected token "integer" was found following "
DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=1. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Your problem here is probably that you did not explicitly specify a
statement terminator. So the end-of-line terminates your SQL statement,
and that leaves ''integer;'' as a ...something... where DB2 rightfully
complains about. Try the -t option of the "db2" command line instead (or
search through the menues if you are using the Command Editor).
2. Setting a variable
SQL Server:
DECLARE @ProcessID int
SET @ProcessID = @spid
or
SET @SavedUserID = (SELECT User_ID FROM Connection_Users WHERE spid =
@@spid)
or (depricated)
SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@spid
DB2:
DECLARE ApplicationID varchar(128) --can''t declare variables
SET ApplicationID = Application_ID()
or
DECLARE ApplicationID varchar(128) --can''t declare variables
SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);
First question in a set-oriented language like SQL would be: what do you
want to do with the value that you really need procedural logic here.
3. Returning a value
SQL Server:
SELECT @@spid AS ProcessID
or
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID
Use this:
VALUES application_id()
and then fetch from the table created that way.
And finally, the full trigger i''m trying to create in DB2 that i can''t
can''t make work.
CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
--"LI_DAILYLOGS" = "LogInsert_DailyLogs" 18 character limit in DB2
AFTER INSERT
ON SUPERDUDE.DAILY_LOGS
REFERENCING NEW_TABLE AS INSERTED
FOR EACH STATEMENT
MODE DB2SQL
BEGIN ATOMIC
-- Load the saved UserID
DECLARE SavedUserID integer;
SELECT SavedUserID = User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID();
SET SavedUserID = ( SELECT ... );
INSERT INTO Audit_Log(
ChangeDate,
RowID,
ChangeType,
-- Username, HostName, AppName,
UserID,
TableName,
FieldName,
TagID,
Tag,
OldValue,
NewValue)
SELECT
getdate(),
i.Daily_Log_ID,
''INSERTED'',
-- USER_NAME(), HOST_NAME(), APP_NAME(),
SavedUserID,
''Daily_Logs'', --TableName
'''', --FieldName
NULL, --TagID
i.Name, --Tag
'''', --OldValue
'''' --NewValue
FROM Inserted i;
END;
Error: SQL0104N An unexpected token "integer" was found following "
DECLARE SavedUserID". Expected tokens may include: "END-OF-STATEMENT".
LINE NUMBER=10. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
现在这对语句终止符来说确实存在问题。 DB2首先使用
'';''作为语句的结尾，这样你就会立即出现语法错误
。这就是为什么你看到'@''被用作声明
终结者的原因。
我会像这样编写你的触发器：
CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
- " LI_DAILYLOGS" =LogInsert_DailyLogs DB2中的18个字符限制
插入后
ON SUPERDUDE.DAILY_LOGS
参考NEW_TABLE as INSERTED
每个语句
模式DB2SQL
INSERT INTO audit_log（...）
SELECT getdate（），
i.Daily_Log_ID，
''INSERTED''，
- USER_NAME（），HOST_NAME（），APP_NAME（），
（ SELECT User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID（））
''Daily_Logs''， - TableName
''''， - FieldName
NULL， - TagID
i.Name， - 标签
'''' ， - OldValue
'''' - NewValue
FROM插入i;
首先不需要变量如果没有程序逻辑，你可以给DB2优化器一个更好的机会来做好工作。
-
Knut Stolze
DB2信息集成开发
IBM德国
Now that is really a problem with the statement terminator. DB2 takes the
first '';'' as end of the statement so that you will have a syntax error
right away. That''s why you see quite ofter the ''@'' being used as statement
terminator here.
I would write your trigger like this:
CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
--"LI_DAILYLOGS" = "LogInsert_DailyLogs" 18 character limit in DB2
AFTER INSERT
ON SUPERDUDE.DAILY_LOGS
REFERENCING NEW_TABLE AS INSERTED
FOR EACH STATEMENT
MODE DB2SQL
INSERT INTO audit_log(...)
SELECT getdate(),
i.Daily_Log_ID,
''INSERTED'',
-- USER_NAME(), HOST_NAME(), APP_NAME(),
( SELECT User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID() )
''Daily_Logs'', --TableName
'''', --FieldName
NULL, --TagID
i.Name, --Tag
'''', --OldValue
'''' --NewValue
FROM Inserted i;
No variables needed in the first place and you give the DB2 optimizer a much
better chance to do a good job without the procedural logic.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
这篇关于在DB2 SQL中如何做......好......任何......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
