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)

>So let's start with some simple SQL constructs, that i know so very well in[color=blue]
>SQL Server, that seem to be like pulling teeth in DB2.[/color]

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.
[color=blue]
>1. Selecting a value[/color]

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'
[color=blue]
> SELECT 'Hello, world!' FROM SYSIBM.SysDummy1
> 1 Row(s) affected
>
>
>Is there a SysDummy2? 3? Why?[/color]

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.
[color=blue]
>1. Declaring a variable
>
>SQL Server:
> DECLARE @SavedUserID int
> The command(s) completed successfully.[/color]

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

[color=blue]
>3. Returning a value
>SQL Server:
> SELECT @@spid AS ProcessID
> or
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> SELECT @ProcessID[/color]

Again, use VALUES.

[color=blue]
>3. Returning rows from a stored procedure
>SQL Server
> CREATE PROCEDURE foo AS
> SELECT @@spid AS ProcessID[/color]

[color=blue]
>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[/color]

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. :)
[color=blue]
>And finally, the full trigger i'm trying to create in DB2 that i can't can't
>make work.[/color]

I have little experience with TRIGGERs in DB2. I'll leave that to
someone else. :)

B.

What's the fish smell??? Oh, nevermind, just a troll.

--
ML

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

> What's the fish smell??? Oh, nevermind, just a troll.

Hey, i honestly tried to be very very nice. i revised my draft post to
removed little comments.

i don't care if T-SQL prefixes variables with @ and DB2-SQL finishes each
line with ;
i don't care if T-SQL sticks me with all CAPS 18-character table names.

There are things that DO bug me, but i won't bother anyone with my
grumblings - my grumblings are my own (and many other peoples).

1. Selecting a value

SQL Server:[color=blue]
> SELECT 'Hello, world!'
> In the DB2 world, you use:
> VALUES 'Hello World'[/color]

How about aliasing field names?[color=blue]
> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage[/color]

i try:[color=blue]
> VALUES 'Hello, world!' AS MyLovelyWelcomeMes[/color]
Error: SQL0104N An unexpected token "AS" was found following "LUES 'Hello,
world!'". Expected tokens may include: "INTO". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

i assume that i should be using a SELECT, and not VALUES[color=blue]
> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage FROM SYSIBM.SysDummy1[/color]

2. Declaring Variables
[color=blue]
> BEGIN
> DECLARE SaverUserID INT;
> END[/color]

Doesn't work for me:
Error: SQL0104N An unexpected token "DECLARE" was found following "BEGIN
". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
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)

i throught that the field type in DB2-SQL was INTEGER. No?

Really scary when i can't get 3 lines to work. So i try:[color=blue]
> DECLARE SaverUserID INT;[/color]

And now i can't even get one line to work. (Yes, frustrating)
Error: SQL0104N An unexpected token "INT" was found following "DECLARE
SaverUserID". Expected tokens may include: "END-OF-STATEMENT".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)


3. Returning a value
SQL Server:[color=blue][color=green]
>> SELECT @@spid AS ProcessID
>> or
>> DECLARE @ProcessID int
>> SET @ProcessID = @spid
>> SELECT @ProcessID[/color]
>
> Again, use VALUES.[/color]

Again, i assume that VALUES is a bad thing:
[color=blue]
> VALUES Application_ID() AS ProcessID[/color]

Error: SQL0104N An unexpected token "AS" was found following "UES
Application_ID()". Expected tokens may include: "->". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

And that i really should be using SELECT[color=blue]
> SELECT Application_ID() AS ApplicationID FROM SYSIBM.SysDummy1[/color]

which does work, but i'd really like to know how to declare variables, set
variables and return variables. So i try:
[color=blue]
>BEGIN
> DECLARE ApplicationID varchar(128);
>END
>BEGIN
> SET ApplicationID = VALUES Application_ID();
>END
>BEGIN
> SELECT ApplicationID AS ApplicationID FROM SYSIBM.SysDummy1;
>END[/color]

Error: SQL0104N An unexpected token "BEGIN DECLARE ApplicationID varchar"
was found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "END" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<update>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "END BEGIN" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

So i'm pretty much stuck. Three lines, and i can't any one work. Note the
heavy use of BEGIN/END because "there is no implicit block of code, so you
must start your own." Surely that can't be the syntax i have to use. i would
have thought that the following would have been enough, but i can't really
tell since i get the errors:[color=blue]
>BEGIN
> DECLARE ApplicationID varchar(128);
> SET ApplicationID = VALUES Application_ID();
> SELECT ApplicationID AS ApplicationID
> FROM SYSIBM.SysDummy1;
>END[/color]

Error: SQL0104N An unexpected token "BEGIN DECLARE ApplicationID varchar"
was found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "SET ApplicationID =" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0206N "APPLICATIONID" is not valid in the context where it is
used. SQLSTATE=42703
(State:S0022, Native Code: FFFFFF32)
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)

3. Returning rows from a stored procedure[color=blue]
>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
> Works for me. :)[/color]

Any idea why it doesn't work for me? Any idea what the error message is
trying to say:
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)

Complaining about me ending my statement after the SELECT, that that it is
expecting a JOIN. Should be joining to something. Do i need to also join to
a dummy table? e.g.
[color=blue]
>CREATE PROCEDURE foo
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
>P1: BEGIN
> DECLARE cursor1 CURSOR WITH RETURN FOR
> SELECT Application_ID() FROM SYSIBM.SYSDUMMY1
> FULL OUTER JOIN SYSIBM.SYSDUMMY1 ON (1=1);
> OPEN cursor1;
>END P1[/color]

No, that doesn't work:

Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"IBM.SYSDUMMY1 ON (1=1)". Expected tokens may include: "<psm_semicolon>".
LINE NUMBER=7. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"OPEN cursor1". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
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)

[color=blue][color=green]
>>And finally, the full trigger i'm trying to create in DB2 that i can't
>>make work.[/color]
>I have little experience with TRIGGERs in DB2. I'll leave that to someone
>else. :)[/color]

That's fine, pretend it's not a trigger. Pretend it's just a regular query:
[color=blue]
>-- Load the saved UserID
>DECLARE SavedUserID integer;[/color]
i can't get any variant of any variable declaration to work. Any ideas?
[color=blue]
> SELECT SavedUserID = User_ID
> FROM Connection_Users
> WHERE Application_ID = Application_ID();[/color]
Is this valid DB2-SQL syntax to put a value into a variable? i can't really
test it, since i cannot declare variables. Or is it invalid syntax, and i
should be using:
[color=blue]
> SET SavedUserID = SELECT User_ID
> FROM Connection_Users
> WHERE Application_ID = Application_ID();[/color]

or should i be using VALUES along the lines of:
[color=blue]
> SET SavedUserID = VALUES Application_ID;[/color]

i guess, which is preferred? Again, i can't test anything, because i cannot
declare variables.
[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;[/color]

(NOTE: Anyone who knows T-SQL will recognize getdate(), USER_NAME(),
HOST_NAME(), APP_NAME(). i assume that DB2-SQL has some built-in function to
get the current date/time. i also assume DB2-SQL has no built-in function to
get the current Username, MachineName or AppName)

Aside from the SQL syntax stuck in there (because i can't get enough far
enough to debug it), is that a valid syntax for doing an insert into a table
in DB2 when not using VALUES?

By values i mean:
INSERT INTO foo (Field1, Field2, ..., FieldN)
VALUES (Value1, Value2, ..., ValueN);

Put it another way, is this a valid syntax in DB2-SQL:
[color=blue]
> INSERT INTO foo (Field1, Field2, ..., FieldN)
> SELECT Value1, Value2, ..., ValueN
> FROM MyTable
> WHERE ...[/color]

Finally, is that the valid way to alias tables in DB2-SQL?[color=blue]
> FROM Inserted i[/color]
will that work, or do i have to do something like:[color=blue]
> FROM Inserted AS i[/color]
or is it some other syntax, maybe more like Java, which IBM seems to live:[color=blue]
> FROM (i)Inserted[/color]


Finally, does DB2 support derived tables
[color=blue]
> SELECT myo.*, MyDerivedTable.*
> FROM MyTableOne mto
> INNER JOIN (SELECT * FROM MyTableTwo mtt
> WHERE mtt.Field4 = 'Testing') MyDerivedTable
> mto.SomeJoinField = MyDerivedTable.AnotherJoinField[/color]


But originally, and most importantly, what's wrong with:

DECLARE SomeNumber INT;

i think if i can get that working, i'll knock back a bottle of scotch and
call today very productive. If i can get a declare working, i'll have
written one line of DB2-SQL in 2 weeks of work.

Ian Boyd wrote:
[color=blue]
> 1. Selecting a value
>
> SQL Server:[color=green]
>> SELECT 'Hello, world!'
>> In the DB2 world, you use:
>> VALUES 'Hello World'[/color]
>
> How about aliasing field names?[/color]

There are no "fields" in SQL - just rows, columns and values.
[color=blue][color=green]
>> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage[/color][/color]

The question is what the column name will be good for. If you get just one
row even one value, you usually don't need to name it. And if you have
more, you usually have an application dealing with the data. So renamed
columns are in my opinion only worthwhile for sub-queries.
[color=blue]
> i assume that i should be using a SELECT, and not VALUES[color=green]
>> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage FROM SYSIBM.SysDummy1[/color][/color]

Yes, to rename a column you have to have a SELECT statement.[color=blue]
> 2. Declaring Variables
>[color=green]
>> BEGIN
>> DECLARE SaverUserID INT;
>> END[/color][/color]

BEGIN ATOMIC
DECLARE i INT;
END@
[color=blue]
> i throught that the field type in DB2-SQL was INTEGER. No?[/color]

Data type.
[color=blue]
> 3. Returning a value
> SQL Server:[color=green][color=darkred]
>>> SELECT @@spid AS ProcessID
>>> or
>>> DECLARE @ProcessID int
>>> SET @ProcessID = @spid
>>> SELECT @ProcessID[/color]
>>
>> Again, use VALUES.[/color]
>
> Again, i assume that VALUES is a bad thing:[/color]

How so?
[color=blue][color=green]
>>BEGIN
>> SET ApplicationID = VALUES Application_ID();[/color][/color]

SET ApplicationID = Application_ID();
[color=blue][color=green]
>>END
>>BEGIN
>> SELECT ApplicationID AS ApplicationID FROM SYSIBM.SysDummy1;
>>END[/color][/color]

Just nest this into a single statement:

VALUES application_id()
[color=blue]
> Complaining about me ending my statement after the SELECT, that that it is
> expecting a JOIN. Should be joining to something. Do i need to also join
> to a dummy table? e.g.[/color]

No, the join is just a suggestion telling you that how the statement _could_
continue. The problem is actually that you have a syntactically incorrect
statement because the END keyword is missing.
[color=blue]
> (NOTE: Anyone who knows T-SQL will recognize getdate(), USER_NAME(),
> HOST_NAME(), APP_NAME(). i assume that DB2-SQL has some built-in function
> to get the current date/time. i also assume DB2-SQL has no built-in
> function to get the current Username, MachineName or AppName)[/color]

Have a look at the DB2 special registers in the manual: USER, CURRENT DATE,
CURRENT TIME, CURRENT TIMESTAMP.
[color=blue]
> Aside from the SQL syntax stuck in there (because i can't get enough far
> enough to debug it), is that a valid syntax for doing an insert into a
> table in DB2 when not using VALUES?
>
> By values i mean:
> INSERT INTO foo (Field1, Field2, ..., FieldN)
> VALUES (Value1, Value2, ..., ValueN);
>
> Put it another way, is this a valid syntax in DB2-SQL:
>[color=green]
>> INSERT INTO foo (Field1, Field2, ..., FieldN)
>> SELECT Value1, Value2, ..., ValueN
>> FROM MyTable
>> WHERE ...[/color][/color]

Works both.
[color=blue]
> Finally, is that the valid way to alias tables in DB2-SQL?[color=green]
>> FROM Inserted i[/color]
> will that work, or do i have to do something like:[color=green]
>> FROM Inserted AS i[/color][/color]

Works both. Have a look at the syntax for the sub-select statement.
[color=blue]
> Finally, does DB2 support derived tables
>[color=green]
>> SELECT myo.*, MyDerivedTable.*
>> FROM MyTableOne mto
>> INNER JOIN (SELECT * FROM MyTableTwo mtt
>> WHERE mtt.Field4 = 'Testing') MyDerivedTable
>> mto.SomeJoinField = MyDerivedTable.AnotherJoinField[/color][/color]

This are sub-queries and DB2 supports them. Have a look at the syntax
diagram for queries.
[color=blue]
> But originally, and most importantly, what's wrong with:
>
> DECLARE SomeNumber INT;[/color]

Nothing. You just have to use it in the correct and valid context.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

> or use a table constructor:
VALUES 'abc'

Ahhh. "Table constructor." i get it now. It doesn't declare constants, it
creates an intermediate table. That will require some new thinking on what
problems i can solve with it.
[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]
[color=blue]
>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]

i'm using neither. i'm using an ODBC connection, and issuing commands
that way. But now that you mention it, i'll try Command Editor. i notice
that Command Editor shows at the bottom a "Statement termination character"
defaulted to semi-colon. And it still doesn't work. The help also says that
DB2 understands CR as a statement terminator, and that i should not use
a statement termination character inside a CREATE PROCEDURE or CREATE
TRIGGER. So i'll stop using it.
[color=blue]
> DECLARE SavedUserID int[/color]
SQL0104N An unexpected token "integer" was found following "DECLARE
SavedUserID".
Expected tokens may include: "END-OF-STATEMENT

i try changing the "Statement termination character" to @ and i change my
SQL Statement to:[color=blue]
> DECLARE SavedUserID integer@[/color]

SQL0104N An unexpected token "integer" was found following "DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT



[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]
[color=blue]
> 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]

Honestly, so that while i'm writing and testing the stored procedure, i can
check that
i have managed to fetch the propert value. i would fetch the value into
a variable, and then select it, seeing that i got it. (Although, doing a
select into a procedure is a whole thing, not just a simple SELECT -
you have to declare cursors, modify the procedure header to say that i'll
be returning rows, etc)

Also, i want a variable because my audit logging for UPDATE will contain
an equal number of insert statements as there are fields in the table (well,
almost equal). So rather than DB2 having to join for the same value every
time,
i will grab it once and then kept it stored. i am not after what i should
be doing, i'm looking for the DB2-SQL syntax to perform common operations.
i won't detail every form of query i have ever written and why those
queries got the solution they did.

i'm trying to get a super-primer on DB2-SQL, so i can get something up and
running.

3. Returning a value
SQL Server:[color=blue]
> SELECT @@spid AS ProcessID
> or
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> SELECT @ProcessID[/color]

How do i declare, set and fetch local variables? What would be syntax to do
that?
[color=blue]
> And finally, the full trigger i'm trying to create in DB2 that i can't
> can't make work.
>[color=green]
>>
>> 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.[/color]

Do you mean in general other people who write triggers?
Or is the symbol '@' (commerical at sign) not coming through the
news server correctly - i don't see any '@' as my statement terminator.
[color=blue]
> I would write your trigger like this:
> CREATE TRIGGER ...
> 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.[/color]

How would you translate this trimmed down version of a trigger from SQL
Server?
(You don't really have to, i'm just showing what i will be writing after
i can figure out how to declare a variable, and finish tackling the trivial
job of writing an INSERT audit logging trigger)

CREATE TRIGGER LogUpdate_Quotes ON Quotes
FOR UPDATE AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(),
i.QuoteGUID,
'UPDATED',
USER_NAME(),
HOST_NAME(),
APP_NAME(),
@SavedUserGUID,
'Quotes',
'Tax2',
i.ProjectGUID,
i.QuoteNumber,
CAST(d.Tax2 AS varchar(8000)),
CAST(i.Tax2 AS varchar(8000))
FROM Inserted i
INNER JOIN Deleted d
ON i.QuoteGUID = d.QuoteGUID
WHERE (d.Tax2 IS NULL AND i.Tax2 IS NOT NULL)
OR (d.Tax2 IS NOT NULL AND i.Tax2 IS NULL)
OR (d.Tax2 <> i.Tax2)

/* GrandTotal money */
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(),
i.QuoteGUID,
'UPDATED',
USER_NAME(),
HOST_NAME(),
APP_NAME(),
@SavedUserGUID,
'Quotes',
'GrandTotal',
i.ProjectGUID,
i.QuoteNumber,
CAST(d.GrandTotal AS varchar(8000)),
CAST(i.GrandTotal AS varchar(8000))
FROM Inserted i
INNER JOIN Deleted d
ON i.QuoteGUID = d.QuoteGUID
WHERE (d.GrandTotal IS NULL AND i.GrandTotal IS NOT NULL)
OR (d.GrandTotal IS NOT NULL AND i.GrandTotal IS NULL)
OR (d.GrandTotal <> i.GrandTotal)

...74 fields ommitted...

/* TaxScheduleGUID uniqueidentifier */
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(),
i.QuoteGUID,
'UPDATED',
USER_NAME(),
HOST_NAME(),
APP_NAME(),
@SavedUserGUID,
'Quotes',
'TaxScheduleGUID',
i.ProjectGUID,
i.QuoteNumber,
(SELECT Name FROM TaxSchedules WHERE TaxScheduleGUID = d.TaxScheduleGUID),
(SELECT Name FROM TaxSchedules WHERE TaxScheduleGUID = i.TaxScheduleGUID)
FROM Inserted i
INNER JOIN Deleted d
ON i.QuoteGUID = d.QuoteGUID
WHERE (d.TaxScheduleGUID IS NULL AND i.TaxScheduleGUID IS NOT NULL)
OR (d.TaxScheduleGUID IS NOT NULL AND i.TaxScheduleGUID IS NULL)
OR (d.TaxScheduleGUID <> i.TaxScheduleGUID)

i specifially chose one of the widest tables i had, to demonstrate the
volume
of repeative inserts. For some reason everyone in DB2 world prefers for "For
Each Row"
rather than the "For the Statement" style of triggers. Seems pretty
inefficient to run the
same trigger statement for each row affected, when you can run it once for
all of them.
i'm assuming that DB2, like all RDMS's are set-based, and any
row-by-row/cursor operations
are a waste. But it also makes trying to learn DB2-SQL when everyone prefers
the
simpler row-by-row triggers.

My thinking with creating a variable was trying to save DB2 from having to
construct and join to a virtual table over and over. So, i query for
the value once, rather than forcing DB2 to do it over and over.


But even more than that, i want to learn DB2-SQL. And one of the constructs
i am
trying to learn is declaring a variable and using it.

Ian Boyd wrote:
[color=blue][color=green]
>> or use a table constructor:[/color]
> VALUES 'abc'
>
> Ahhh. "Table constructor." i get it now. It doesn't declare constants, it
> creates an intermediate table. That will require some new thinking on what
> problems i can solve with it.[/color]

That's the relational model: everything is a table. Period. ;-)
[color=blue][color=green]
>> DECLARE SavedUserID int[/color]
> SQL0104N An unexpected token "integer" was found following "DECLARE
> SavedUserID".
> Expected tokens may include: "END-OF-STATEMENT
>
> i try changing the "Statement termination character" to @ and i change my
> SQL Statement to:[color=green]
>> DECLARE SavedUserID integer@[/color]
>
> SQL0104N An unexpected token "integer" was found following "DECLARE
> SavedUserID". Expected tokens may include: "END-OF-STATEMENT[/color]

On the command line, you have to embed the DECLARE into a atomic compound
statement.

BEGIN ATOMIC ... END
[color=blue]
> Also, i want a variable because my audit logging for UPDATE will contain
> an equal number of insert statements as there are fields in the table
> (well, almost equal). So rather than DB2 having to join for the same value
> every time,
> i will grab it once and then kept it stored. i am not after what i should
> be doing, i'm looking for the DB2-SQL syntax to perform common operations.
> i won't detail every form of query i have ever written and why those
> queries got the solution they did.[/color]
[color=blue]
> 3. Returning a value
> SQL Server:[color=green]
>> SELECT @@spid AS ProcessID
>> or
>> DECLARE @ProcessID int
>> SET @ProcessID = @spid
>> SELECT @ProcessID[/color]
>
> How do i declare, set and fetch local variables? What would be syntax to
> do that?[/color]

It's relational: Build a table, open a cursor and fetch from the table.
Then there is some syntactic sugar to simplify this a bit like:

SET ( var1, var2, var3 ) = ( val1, val2, val3 )
[color=blue]
> Do you mean in general other people who write triggers?[/color]

I was referring to the posts in this newsgroup.
[color=blue][color=green]
>> I would write your trigger like this:
>> CREATE TRIGGER ...
>> 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.[/color]
>
> How would you translate this trimmed down version of a trigger from SQL
> Server?[/color]

What's trimmed down? It does the same thing unless there is more in the SQL
Server trigger going.
[color=blue]
> i specifially chose one of the widest tables i had, to demonstrate the
> volume
> of repeative inserts. For some reason everyone in DB2 world prefers for
> "For Each Row"
> rather than the "For the Statement" style of triggers. Seems pretty
> inefficient to run the
> same trigger statement for each row affected, when you can run it once for
> all of them.[/color]

The thing is than DB2 compiles the trigger into the INSERT statement itself.
So doing things "for each row" is not slower than your way - I would guess
that it is even faster because no temp tables will be needed.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Ian,

(I'm consciously not quoting anything.. fresh start)

DB2 supports variables in the context of stored procedures, functions,
methods, triggers and "dynamic compound statements".
DB2 does not support "global variables" (and I thought SQL Server
doesn't either, so I'm surprised you have an issue.

DB2 supports the SQL/PSM standard for procedural logic.
To learn about this language I strongly recommend:
"DB2 SQL PL" by Paul Yip et al:
http://btobsearch.barnesandnoble.com...sbn=0131477005

I think the name SYSIBM.SYDUMMY1 is rooted in the fact that it returns 1
row.

The VALUES clause is actually very powerful when embedded in the from
clause.
E.g. instead of doing:
T-SQL:
SELECT * FROM
(SELECT 5
UNION
SELECT 6) AS X
you can do:
SELECT * FROM (VALUES (5), (6)) AS X.

This T-SQL: SELECT @@spid AS ProcessID
is NOT column aliasing.
This is a SET statement.
You can either use:
SET ProcessID = spid;
or
VALUES spid INTO processID;

Note the INTO clause. The same principle works for this T-SQL consruct:
SELECT SavedUserID = User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID();

In the SQL Standard (and thus DB2):
SELECT User_ID INTO SavedUserID
FROM Connection_Users
WHERE Application_ID = Application_ID();

There in one difference though: while SQL Server will quietly reyurn any
value if your where clause matches more than one row, DB2 will riot if
more than one row is return (rightly so).

It is important to note that whenever you write a statement that
contains ';' due to "substatements" you need to make sure the statement
delimiter (what you know as "go" I think) is set to a value other than
';' (imagine placing 'go' after each line in a T-SQL procedure.. same
confusion).
With the CLP you can set the delimiter with db2 -td<character>.
E.g. db2 -td@
You can also change the delimiter on the fly in CLP using
--#SET TERMINATOR <character>
GUIs (such as control center) typically have a preference that can be set.
DB2 supports limited(!) scripting capabilities using dynamic compound
(in Oracle this would be called an "anonymous block").
Again I'm surprised you try this since AFAIK SQL Server supports no such
thing. all T-SQL must be in a procedure.

BEGIN ATOMIC -- Note the ATOMIC keyword!
DECLARE a INTEGER DEFAULT 5;
WHILE A < 5 DO
SET a = a + 5;
CALL proc(a);
END WHILE;
END

So let's move into a procedure example:
db2 -t

--#SET TERMINATOR @
DROP PROCEDURE dostuff
@
CREATE PROCEDURE dostuff(IN a INTEGER, INOUT b INTEGER, OUT c INTEGER)
BEGIN
DECLARE d INTEGER;
DECLARE cur CURSOR WITH RETURN FOR
SELECT TABNAME FROM SYSCAT.COLUMNS FETCH FIRST 2 ROWS ONLY;
SET d = a + b;
SET c = d * a;
SET b = 7;
OPEN cur;
END
@
CALL dostuff(5, 7, ?)@
---
db2 => CALL dostuff(5, 7, ?)@

Value of output parameters
--------------------------
Parameter Name : B
Parameter Value : 7

Parameter Name : C
Parameter Value : 60


Result set 1
--------------

TABNAME
----------------------------
COLDIST
COLDIST

2 record(s) selected.

Return Status = 0


OK I think that covers it. Let us know how it goes.

Cheers
Serge

PS: One more thing..... the SQL/PSM standard (which is the foundation
for DB2's SQL procedures) uses exception handlers for error handling.
Do NOT overload them to emulate old style T-SQL. Use them just like in
SQL Server 2005 Microsoft encourages you to use exception handlers.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Ian Boyd wrote:[color=blue]
> i specifially chose one of the widest tables i had, to demonstrate the
> volume
> of repeative inserts. For some reason everyone in DB2 world prefers for "For
> Each Row"
> rather than the "For the Statement" style of triggers. Seems pretty
> inefficient to run the
> same trigger statement for each row affected, when you can run it once for
> all of them.[/color]
Au contraire!
This code from your T-SQL trigger in inefficient.
FROM Inserted i
INNER JOIN Deleted d
ON i.QuoteGUID = d.QuoteGUID
In a FOR EACH ROW trigger the new and old transition variables are
already matched.

Also a FOR EACH ROW trigger does not necessarily have to produce any
inserted and deleted temporary tables. Instead it can pipeline.
A straight forward audit trigger has a cost which is virtually identical
to the cost of the individual inserts.

It is ironic that in most OLTP systems the number of rows changes with
one statement is 1 anyway, so a statement trigger would execute only for
one row.

talking of triggers, you may also want to familiarize yourself with
BEFORE triggers. Very powerful and much more lightweight than patching
up the rows after the update/insert.
(they come at the cost of the evaluation of the expression)

CREATE TRIGGER trg1 BEFORE UPDATE ON T
FOR EACH ROW REFERENCING NEW AS n OLD AS o
WHEN (n.c1 <= o.c1)
SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'C1 must increase!'


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

> There are no "fields" in SQL - just rows, columns and values.
You channeling Celko? :)
[color=blue]
> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage
> The question is what the column name will be good for.
> If you get just one row even one value, you usually don't
> need to name it. And if you have more, you usually have
> an application dealing with the data.[/color]

If there are multiple fields (a.k.a. columns) in the returned
records (a.k.a. rows), and the client accesses
fields (a.k.a columns) by name, then each
field (a.k.a column) will need a name. Even if there is only one
field (a.k.a column) in the returned records (a.k.a rows), and
the client can only access fields (a.k.a columns) by name, that
field (a.k.a column) will stil need a name.
[color=blue]
> Yes, to rename a column you have to have a SELECT statement.
> 2. Declaring Variables
>[color=green]
>> BEGIN
>> DECLARE SaverUserID INT;
>> END[/color]
>
> BEGIN ATOMIC
> DECLARE i INT;
> END@[/color]

Ah-hah! "BEGIN ATOMIC"
[color=blue][color=green]
>> i throught that the field type in DB2-SQL was INTEGER. No?[/color][/color]
i see it now in the online help. INT is an alias for Integer field type.

3. Returning a value[color=blue][color=green]
>> SQL Server:[color=darkred]
>>>> SELECT @@spid AS ProcessID
>>>> or
>>>> DECLARE @ProcessID int
>>>> SET @ProcessID = @spid
>>>> SELECT @ProcessID
>>>
>>> Again, use VALUES.[/color]
>> Again, i assume that VALUES is a bad thing:[/color]
> How so?[/color]

Because rather than using VALUES:[color=blue]
> SET ApplicationID = VALUES Application_ID();[/color]
i don't use VALUES:[color=blue]
> SET ApplicationID = Application_ID();[/color]

Keep in mind that you're responding to a post i made talking to the other
guy,
before it was explained that VALUES constructs a virtual table.
[color=blue]
> Have a look at the DB2 special registers in the manual: USER, CURRENT
> DATE,
> CURRENT TIME, CURRENT TIMESTAMP.[/color]

Excellent. Thank you.


So now i'm getting closer to a compiling trigger. Next question

5. Setting a field (a.k.a column) to NULL

SQL Server[color=blue]
> UPDATE MyTable SET SomeField = NULL
> WHERE SomeOtherField IN (SELECT KeyField FROM MyTable2 WHERE Field = 3)[/color]

or
[color=blue]
> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
> VALUES ('Ian', 'Boyd', '728 Helena', NULL, '911-426-3184')[/color]

or
[color=blue]
> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system[/color]

In my variant of the 3rd case in DB2, it complains that "NULL is not valid
in the context where it is used."

> In a FOR EACH ROW trigger the new and old transition variables are already[color=blue]
> matched.
>
> Also a FOR EACH ROW trigger does not necessarily have to produce any
> inserted and deleted temporary tables. Instead it can pipeline.
> A straight forward audit trigger has a cost which is virtually identical
> to the cost of the individual inserts.
>
> It is ironic that in most OLTP systems the number of rows changes with one
> statement is 1 anyway, so a statement trigger would execute only for one
> row.
>[/color]
So a statement like
INSERT INTO NewTable
SELECT * FROM OldTable

can be faster if DB2 has to perform logic on every row in the insert, rather
than one set-based operation?

What about my soon-to-be-headache-for-tomorrow an update trigger

UPDATE MyTable
SET AMoneyField = AMoneyField * 1.10

Wouldn't DB2 perfer when doing the trigger:

INSERT INTO AuditLog
SELECT fields
FROM OldTable
INNER JOIN NewTable
ON OldTable.RowID = NewTable.RowID
WHERE OldTable.AMoneyField <> NewTable.AMoneyField

rather than doing

Row#1
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField

Row#2
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField

Row#3
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField

Row#4
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField

....

Row#984,648,321
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField

Set based always performs better than row-by-row logic. What am i missing?

[color=blue]
> talking of triggers, you may also want to familiarize yourself with
> BEFORE triggers. Very powerful and much more lightweight than patching up
> the rows after the update/insert.
> (they come at the cost of the evaluation of the expression)
>
> CREATE TRIGGER trg1 BEFORE UPDATE ON T
> FOR EACH ROW REFERENCING NEW AS n OLD AS o
> WHEN (n.c1 <= o.c1)
> SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'C1 must increase!'[/color]

Can you translate my original insert trigger into a db2 before insert
trigger?
i've seen that example of a before trigger somewhere, but it doesn't really
help me.
What would i want the evaluation expression to be:

CREATE TRIGGER trg1 BEFORE UPDATE ON T
FOR EACH ROW REFERENCING NEW AS n OLD AS o
WHEN (1=1)
BEGIN ATOMIC And_Then_What_Goes_Here;
END;@

Ian Boyd wrote:[color=blue][color=green]
>> In a FOR EACH ROW trigger the new and old transition variables are already
>> matched.
>>
>> Also a FOR EACH ROW trigger does not necessarily have to produce any
>> inserted and deleted temporary tables. Instead it can pipeline.
>> A straight forward audit trigger has a cost which is virtually identical
>> to the cost of the individual inserts.
>>
>> It is ironic that in most OLTP systems the number of rows changes with one
>> statement is 1 anyway, so a statement trigger would execute only for one
>> row.
>>[/color]
> So a statement like
> INSERT INTO NewTable
> SELECT * FROM OldTable
>
> can be faster if DB2 has to perform logic on every row in the insert, rather
> than one set-based operation?
>
> What about my soon-to-be-headache-for-tomorrow an update trigger
>
> UPDATE MyTable
> SET AMoneyField = AMoneyField * 1.10
>
> Wouldn't DB2 perfer when doing the trigger:
>
> INSERT INTO AuditLog
> SELECT fields
> FROM OldTable
> INNER JOIN NewTable
> ON OldTable.RowID = NewTable.RowID
> WHERE OldTable.AMoneyField <> NewTable.AMoneyField
>
> rather than doing
>
> Row#1
> if OldRow.AMoneyField <> NewRow.AMoneyField then
> INSERT INTO AuditLog
> SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
>
> Row#2
> if OldRow.AMoneyField <> NewRow.AMoneyField then
> INSERT INTO AuditLog
> SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
>
> Row#3
> if OldRow.AMoneyField <> NewRow.AMoneyField then
> INSERT INTO AuditLog
> SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
>
> Row#4
> if OldRow.AMoneyField <> NewRow.AMoneyField then
> INSERT INTO AuditLog
> SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
>
> ...
>
> Row#984,648,321
> if OldRow.AMoneyField <> NewRow.AMoneyField then
> INSERT INTO AuditLog
> SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
>
> Set based always performs better than row-by-row logic. What am i missing?[/color]
You are missing the fact that thie join of yours is doing all teh same
work in addition to having to match the rows OldTable.RowID =
NewTable.RowID. likely teh join will be a nested loop. meaning you're
scanning one of the temp tables (which have to be created of course)
984,648,321 times. (unless you can use hashjoin which is still far from
for free.
DB2's triggers are inline. There is no invocation cost.
In SQL Server words compare to T-SQL table functions which can be inlined
(Sometimes I hate that I don't own my patents.. Could be rich charging
MS for that stuff)
DB2 will run the following "SQL":
SELECT COUNT(1) -- Ignore the count, artistic freedom...
FROM (INSERT INTO newtable SELECT * FROM OldTable) AS newtablerow,
(INSERT INTO AuditLog
VALUES fields, NULL, NewTableRow.AMoneyField)

(What was oldtable row meant to be...?)
[color=blue][color=green]
>> talking of triggers, you may also want to familiarize yourself with
>> BEFORE triggers. Very powerful and much more lightweight than patching up
>> the rows after the update/insert.
>> (they come at the cost of the evaluation of the expression)
>>
>> CREATE TRIGGER trg1 BEFORE UPDATE ON T
>> FOR EACH ROW REFERENCING NEW AS n OLD AS o
>> WHEN (n.c1 <= o.c1)
>> SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'C1 must increase!'[/color]
>
> Can you translate my original insert trigger into a db2 before insert
> trigger?
> i've seen that example of a before trigger somewhere, but it doesn't really
> help me.
> What would i want the evaluation expression to be:
>[/color]
Your trigger can't be a before trigger. it changes the state of the
database. BEFORE triggers are use to:
* Modify the "INSERTED" table BEFORE doing the INSERT
(e.g. to generate complex defaults expressions)
* do error checking not places in a check constraint or RI for some
reason or other.

The WHEN clause is not mandatory, btw.. just omit it if you want the
trigger to fire always.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Ian Boyd wrote:[color=blue][color=green]
>> There are no "fields" in SQL - just rows, columns and values.[/color]
> You channeling Celko? :)[/color]
*chuckle* I had the same thought.
Seriously though it doesn't hurt to use the correct language.
Just like it doesn't hurt to speak proper English outside the pub ;-)
[color=blue][color=green]
>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
>> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system[/color]
>
> In my variant of the 3rd case in DB2, it complains that "NULL is not valid
> in the context where it is used."[/color]
DB2 uses strong typing. An untyped NULL (or ?) is only allowed in
specific places where DB2 cann immediatly deduce the datatype.
That would be UPDATE SET, SET statement and INSERT VALUES.
In all other cases CAST(NULL AS <type>) will do the job.
That's the way the standard is defined. No technical reason really.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

> DB2 supports variables in the context of stored procedures, functions,[color=blue]
> methods, triggers and "dynamic compound statements".
> DB2 does not support "global variables" (and I thought SQL Server doesn't
> either, so I'm surprised you have an issue.[/color]

i don't think SQL Server has global variables either - at least i've never
seen them.
[color=blue]
> I think the name SYSIBM.SYDUMMY1 is rooted in the fact that it returns 1
> row.[/color]

That's an interesting insight!
[color=blue]
> The VALUES clause is actually very powerful when embedded in the from
> clause.
> E.g. instead of doing:
> T-SQL:
> SELECT * FROM
> (SELECT 5
> UNION
> SELECT 6) AS X
> you can do:
> SELECT * FROM (VALUES (5), (6)) AS X.[/color]

i've never used any idea similar to
(SELECT 5 UNION SELECT 6) AS X

i've never had a problem where i needed to join to such virtual table. Like
i said before, i'll have to figure out where VALUES would be useful to me.
[color=blue]
> This T-SQL: SELECT @@spid AS ProcessID
> is NOT column aliasing.[/color]

If i run this query, i get a record set with one row and one column. And
that column has no name. If i want to give that column a name (or a
different name) i have to use:
[color=blue]
> SELECT @@spid AS ProcessID[/color]

So the column gets renamed from "" to "ProcessID." Whereas the following SET
operation[color=blue]
> SET ProcessID = spid;[/color]

returns no rows.
[color=blue]
> It is important to note that whenever you write a statement that contains
> ';' due to "substatements" you need to make sure the statement delimiter
> (what you know as "go" I think) is set to a value other than ';' (imagine
> placing 'go' after each line in a T-SQL procedure.. same confusion).[/color]

i've never had to use a "go" in T-SQL. All the text i submit is one batch.
There is no ; needed at the end of every statement.

So now having to have not only a statement delimiter, and a batch delimiter
is painful.
[color=blue]
> Again I'm surprised you try this since AFAIK SQL Server supports no such
> thing. all T-SQL must be in a procedure.[/color]

No. i can send T-SQL to SQL Server and it runs it. i don't seen it to be
inside
a transaction or any such "BEGIN ATOMIC". It runs the SQL it is given. For
example
[color=blue]
>DECLARE @a int
>SET @a = 5
>WHILE @A < 5 BEGIN
> SET @a = @a + 5
> EXECUTE SomeStoredProcedure
>END[/color]

is perfectly valid. i can send the T-SQL to SQL Sever using it's own query
tool, or using 3rd party tools, or using ADO. i give it those characters, no
semicolons, no go, no begins, end, atomics; and it just runs.
[color=blue]
> So let's move into a procedure example:[/color]

Here's an interesting question. The following rus fine, exactly as is:

CREATE PROCEDURE dostuff(IN a INTEGER, INOUT b INTEGER, OUT c INTEGER)
BEGIN
DECLARE d INTEGER;
DECLARE cur CURSOR WITH RETURN FOR
SELECT TABNAME FROM SYSCAT.COLUMNS FETCH FIRST 2 ROWS ONLY;
SET d = a + b;
SET c = d * a;
SET b = 7;
OPEN cur;
END

So now:
[color=blue]
> CALL dostuff(5, 7, ?)[/color]
Wrong number of parameters
[color=blue]
> DECLARE c integer; call dostuff(5, 7, ?);[/color]
An unexpected token "integer" was found following "DECLARE c ".
[color=blue]
>BEGIN
> DECLARE c integer; call dostuff(5, 7, c);
>END[/color]
An unexpected token "DECLARE" was found following "begin "
[color=blue]
>BEGIN ATOMIC
> DECLARE c integer; call dostuff(5, 7, c);
>END[/color]
The parameter mode (IN, OUT, or INOUT) is not valid for a parameter in
procedure "DOSTUFF"

Why is it that the create procedure doesn't need atomic begins. It doesn't
need the keyword atmoc after the begin, and it doesn't need to wrapped
wholly in a begin end such as:

BEGIN ATOMIC
CREATE PROCEDURE dostuff(...)
BEGIN
END
END

But my ad-hoc sql does need atomic begins? By the way, this is far as i can
get. i don't now how to fix the call to the stored procedure.
[color=blue]
> PS: One more thing..... the SQL/PSM standard (which is the foundation for
> DB2's SQL procedures) uses exception handlers for error handling.
> Do NOT overload them to emulate old style T-SQL. Use them just like in SQL
> Server 2005 Microsoft encourages you to use exception handlers.[/color]

A welcome addition.
[color=blue]
> OK I think that covers it. Let us know how it goes.[/color]
Time to go home for the day. Day 9, nothing working yet.

>> It is important to note that whenever you write a statement that contains[color=blue][color=green]
>> ';' due to "substatements" you need to make sure the statement delimiter
>> (what you know as "go" I think) is set to a value other than ';' (imagine
>> placing 'go' after each line in a T-SQL procedure.. same confusion).[/color]
>
> i've never had to use a "go" in T-SQL. All the text i submit is one batch.
> There is no ; needed at the end of every statement.[/color]

i think i see the confusion. SQL Server has no "substatements". And
statements don't have to be separated by semi-colons.
If i were to give the OLE DB Provider for Microsoft SQL Server the following
string, as one long string (and this example is taken from the SQL Server
documentation):

string myQuery = "DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'

-- Yields an error because @MyMsg not declared in this batch.
PRINT @MyMsg

SELECT @@VERSION;
-- Yields an error: Must be EXEC sp_who if not first statement in
-- batch.
EXECUTE sp_who"

Connection.Execute(myQuery);

This will just run. The entire set of all the statments are sent over to SQL
Server as one "batch", and SQL Server runs them.

Additionally, there is a standard feature built into the query tools, and
that is the use of the keyword "go". It is not a T-SQL keyword, it is a word
only recognized by Microsoft's query tools.

If you entered the following into Microsoft's Query Analyzer;

<quote>
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO -- @MyMsg is not valid after this GO ends the batch.

-- Yields an error because @MyMsg not declared in this batch.
PRINT @MyMsg
GO

SELECT @@VERSION;
-- Yields an error: Must be EXEC sp_who if not first statement in
-- batch.
sp_who
</quote>

The query tool will now send 3 individual batches to SQL Server. Each batch
is separate from the others. You are free to send over all the text in one
batch, or you can have the tool send it over the multiple batches. But as it
indicated in the example query, variables declared in one batch will no
longer exist in the next batch.



So, when i'm trying to do something in DB2-SQL, e.g.:

DECLARE UserID integer
SET UserID =
( SELECT application_ID()
FROM sysibm.sysdummy1
)
update MyTable
SET UserID = UserID
WHERE UserID IS NULL
delete from MyTable
WHERE UserID = 3
select * from MyTable


i expect all that text to be sent to to DB2, i expect DB2 to run the query,
and return me what it is supposed to return me. But DB2 doesn't just read
the SQL it's given. DB2 seems to require semicolons to separate each
statment. e.g.:

DECLARE UserID integer;
SET UserID =
( SELECT application_ID()
FROM sysibm.sysdummy1
);
update MyTable
SET UserID = UserID
WHERE UserID IS NULL;
delete from MyTable
WHERE UserID = 3;
select * from MyTable;

Which is fine. It would be nicer if it didn't need semicolons, but okay,
i'll live with it.
But now, in addition, this apparently isn't enough. Just because i've given
DB2 some statements to run, doesn't mean that it will run them. For some
reason, i have to tell it that the SQL it just received really is all
together in one "batch" (to steal a MSSQL term)

BEGIN ATOMIC
DECLARE UserID integer;
SET UserID =
( SELECT application_ID()
FROM sysibm.sysdummy1
);
update MyTable
SET UserID = UserID
WHERE UserID IS NULL;
delete from MyTable
WHERE UserID = 3;
select * from MyTable;
END

But not only that, i have to actually begin the batch with the keywords
BEGIN ATOMIC and end the batch with END. So as i understand it, just sending
a bunch of SQL to DB2 is not enough for it to decide to run the batch, i
have to explicitly tell it that it is a batch. Fine, okay, messy, but i
think i understand.

But wait, i don't understand. Because i can send the SQL to create a stored
procedure

CREATE PROCEDURE doStuff(...)
BEGIN
...
END

and i don't have to wrap the batch in BEGIN ATOMIC..END e.g.

BEGIN ATOMIC
CREATE PROCEDURE doStuff(...)
BEGIN
...
END
END

So perhaps because it is a CREATE PROCEDURE, or CREATE TRIGGER, or CREATE
TABLE, etc that i can omit the BEGIN ATMIC...END around the statement in
those batches. Perhaps it is because a CREATE PROCEDURE, CREATE TRIGGER,
CREATE TABLE itself is a single statement that it doesn't need to be
wrapped. Maybe batches that only consist of a single statement don't need to
be wrapped. No, that's not true either:

DECLARE myValue int;

fails also. Maybe Create XXXXX statements are just special like that.

But now, to throw another level of confusion into it, inside a CREATE
TRIGGER, you DO have to have BEGIN ATOMIC...END, but not inside a CREATE
PROCEDURE.



So, if you read this, please try not to respond to things in detail.
Hopefully you can see my confusion, and this must be because i have a
different mental picture of how SQL Server is given and runs T-SQL and how
DB2 is given and runs T-SQL. If you can see the error in my understanding,
and point out exactly where my thinking is wrong - that would be great. It
would be nice to have an understanding, rather than hoping understanding
will come after being exposed to dozens of disprate examples.

Hi Ian,

I must admit I'm not at all familiar with T-SQL, but from your posts I
get the impression that it allows procedural type stuff (like declaring
variables) *outside* a procedure or trigger.

Up until fairly recently, this was impossible in DB2 (if you wanted
procedural logic, you either used a stored procedure inside the
database, or an external application to manipulate the data). However,
more recent versions (I think it first appeared in version 7 or
thereabouts) have introduced a limited version of this capability with
the BEGIN ATOMIC statement. Here's an example from a command line
session under Linux:

$ db2 -td!
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.0
...
[boring help snipped]
...
db2 => BEGIN ATOMIC
db2 (cont.) => DECLARE SAVEDUSERID INTEGER;
db2 (cont.) => END!
DB20000I The SQL command completed successfully.

This construct is like declaring a stored procedure in that each
statement within the block must be terminated with semi-colon, while
the block as a whole counts as a single SQL statement and must be
terminated with some alternate character (hence why I used the -td!
switch in the example above to set the statement terminator to bang).

Where it differs from a stored procedure is that the ATOMIC keyword
after BEGIN is mandatory. ATOMIC indicates that the entire block of
instructions will be executed in a single transaction (hence "atomic").
Therefore, you can't use COMMIT / ROLLBACK within the block (only
outside it).

However, I suspect mere syntactic differences are not the major problem
here. You're thinking of SQL in a procedural manner (which I guess is
perfectly fine for SQL Server but will complicate things for you
horribly in DB2). You need to think of SQL as a "functional" language,
not an "imperative" (procedural) language.

Therefore, instead of writing something like this:

BEGIN ATOMIC
DECLARE var1 INTEGER;
DECLARE var2 INTEGER;
SET var1 = (SELECT afield FROM table1);
SET var2 = (SELECT anotherfield FROM table2 WHERE yetanotherfield =
var1);
INSERT INTO table3 VALUES (var2);
END!

It'd be considered a lot more "normal" (at least, under DB2) to write
something like this:

INSERT INTO table3
SELECT anotherfield
FROM table2
WHERE yetanotherfield = (SELECT afield FROM table1);

If you're familiar with functional programming (Lisp, Haskell, ML,
etc.), note the similarities:

* No variable declarations
* Expressions wrapped within each other (SELECT in a SELECT in an
INSERT) instead of separate statements executed in an explicit order
* Execution order determined "naturally" (i.e. evaluation of the
outer
most expression implicitly evaluates inner expressions)

If you want to become comfortable with DB2's implementation of SQL, you
need to start thinking in this "functional" manner. That's not to say
it's all like this; as you've already discovered, there are stored
procedures, triggers and such like which are fairly procedural in their
nature.

You might be able to get away with the BEGIN ATOMIC statement mentioned
above for a lot of things, but I'd encourage you to avoid it wherever
possible. As Knut mentioned in his post the DB2 optimizer will work a
lot better without procedural logic (again, this ties into the
functional programming analogy).

Don't give up on the VALUES expression either. The VALUES expression
allows you to generate a constant set (scalar or vector) within SQL.
For example:

db2 => VALUES 1;

1
-----------
1

1 record(s) selected.

db2 => VALUES 1, 2;

1
-----------
1
2

2 record(s) selected.

db2 => VALUES ('A', 1), ('B', 2), ('C', 3);

1 2
- -----------
A 1
B 2
C 3

3 record(s) selected.

To answer your question about changing the names of the fields
generated by the VALUES expression:

SELECT *
FROM (
VALUES ('A', 1), ('B', 2), ('C', 2)
) AS TEMP(LETTER, NUMBER);

LETTER NUMBER
------ -----------
A 1
B 2
C 2

3 record(s) selected.

VALUES itself has no way of controlling the names of the fields of the
set it creates, but the fields can be aliased by the enclosing
expression (in this case a SELECT expression).

The above example could also be written using "common table
expressions" (something introduced in ANSI SQL-99, and implemented in
DB2 v6 (?) if I recall correctly):

WITH TEMP(LETTER, NUMBER) AS (
VALUES ('A', 1), ('B', 2), ('C', 2)
)
SELECT * FROM TEMP;

LETTER NUMBER
------ -----------
A 1
B 2
C 2

3 record(s) selected.

Common table expressions can make a query involving a lot of
sub-SELECTs a hell of a lot more readable by defining all the
sub-SELECTs before the main body of the query.

Ahh, I've just read that common table expressions have been added to
the latest version of SQL Server (2005?), so maybe you're familiar with
them already?

Incidentally, the VALUES expression as detailed above, and common table
expressions are not available on DB2 for z/OS, just the Linux / Unix /
Windows version. Weird.


Anyway, hopefully the above will be enough to get one or two (perhaps
even three!) lines of SQL working in DB2 :-)

HTH,

Dave.

Ian Boyd wrote:
[color=blue]
> i'm trying to get a super-primer on DB2-SQL, so i can get something up and
> running.[/color]

My favourite reference for DB2 SQL is Graeme Birchall's
DB2 SQL Cookbook, which is available from this website:

http://mysite.verizon.net/Graeme_Birchall/id1.html

hope this helps,

-- stefan