Ian Boyd wrote:
[color=blue]
> 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)[/color]
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'
[color=blue]
> 1. Declaring a variable
>
> SQL Server:
> DECLARE @SavedUserID int
> The command(s) completed successfully.[/color]
The '@' isn't SQL either.
[color=blue]
> 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)[/color]
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).
[color=blue]
> 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);[/color]
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.
[color=blue]
> 3. Returning a value
> SQL Server:
> SELECT @@spid AS ProcessID
> or
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> SELECT @ProcessID[/color]
Use this:
VALUES application_id()
and then fetch from the table created that way.
[color=blue]
> 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();[/color]
SET SavedUserID = ( SELECT ... );
[color=blue]
> 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)[/color]
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