473,288 Members | 1,745 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

How to do...well...anything...in DB2 SQL

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)
Mar 7 '06 #1
138 58886
>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.
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.


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

B.

Mar 7 '06 #2
ML
What's the fish smell??? Oh, nevermind, just a troll.

--
ML

Mar 7 '06 #3
Ian Boyd wrote:
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)


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
Mar 7 '06 #4
> 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).
Mar 7 '06 #5
1. Selecting a value

SQL Server:
SELECT 'Hello, world!'
In the DB2 world, you use:
VALUES 'Hello World'
How about aliasing field names? SELECT 'Hello, world!' AS MyLovelyWelcomeMessage
i try: VALUES 'Hello, world!' AS MyLovelyWelcomeMes 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 SELECT 'Hello, world!' AS MyLovelyWelcomeMessage FROM SYSIBM.SysDummy1
2. Declaring Variables
BEGIN
DECLARE SaverUserID INT;
END
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: DECLARE SaverUserID INT;
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:
SELECT @@spid AS ProcessID
or
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID


Again, use VALUES.


Again, i assume that VALUES is a bad thing:
VALUES Application_ID() AS ProcessID
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 SELECT Application_ID() AS ApplicationID FROM SYSIBM.SysDummy1
which does work, but i'd really like to know how to declare variables, set
variables and return variables. So i try:
BEGIN
DECLARE ApplicationID varchar(128);
END
BEGIN
SET ApplicationID = VALUES Application_ID();
END
BEGIN
SELECT ApplicationID AS ApplicationID FROM SYSIBM.SysDummy1;
END
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:BEGIN
DECLARE ApplicationID varchar(128);
SET ApplicationID = VALUES Application_ID();
SELECT ApplicationID AS ApplicationID
FROM SYSIBM.SysDummy1;
END
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 procedureDB2
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. :)
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.
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
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)

And finally, the full trigger i'm trying to create in DB2 that i can't
make work.

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


That's fine, pretend it's not a trigger. Pretend it's just a regular query:
-- Load the saved UserID
DECLARE SavedUserID integer; i can't get any variant of any variable declaration to work. Any ideas?
SELECT SavedUserID = User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID(); 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:
SET SavedUserID = SELECT User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID();
or should i be using VALUES along the lines of:
SET SavedUserID = VALUES Application_ID;
i guess, which is preferred? Again, i can't test anything, because i cannot
declare variables.
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;
(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:
INSERT INTO foo (Field1, Field2, ..., FieldN)
SELECT Value1, Value2, ..., ValueN
FROM MyTable
WHERE ...
Finally, is that the valid way to alias tables in DB2-SQL? FROM Inserted i will that work, or do i have to do something like: FROM Inserted AS i or is it some other syntax, maybe more like Java, which IBM seems to live: FROM (i)Inserted

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

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.
Mar 7 '06 #6
Ian Boyd wrote:
1. Selecting a value

SQL Server:
SELECT 'Hello, world!'
In the DB2 world, you use:
VALUES 'Hello World'
How about aliasing field names?


There are no "fields" in SQL - just rows, columns and values.
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. So renamed
columns are in my opinion only worthwhile for sub-queries.
i assume that i should be using a SELECT, and not VALUES
SELECT 'Hello, world!' AS MyLovelyWelcomeMessage FROM SYSIBM.SysDummy1
Yes, to rename a column you have to have a SELECT statement.
2. Declaring Variables
BEGIN
DECLARE SaverUserID INT;
END

BEGIN ATOMIC
DECLARE i INT;
END@
i throught that the field type in DB2-SQL was INTEGER. No?
Data type.
3. Returning a value
SQL Server: SELECT @@spid AS ProcessID
or
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID
Again, use VALUES.


Again, i assume that VALUES is a bad thing:


How so?
BEGIN
SET ApplicationID = VALUES Application_ID();
SET ApplicationID = Application_ID();
END
BEGIN
SELECT ApplicationID AS ApplicationID FROM SYSIBM.SysDummy1;
END


Just nest this into a single statement:

VALUES application_id()
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.
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.
(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)
Have a look at the DB2 special registers in the manual: USER, CURRENT DATE,
CURRENT TIME, CURRENT TIMESTAMP.
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:
INSERT INTO foo (Field1, Field2, ..., FieldN)
SELECT Value1, Value2, ..., ValueN
FROM MyTable
WHERE ...

Works both.
Finally, is that the valid way to alias tables in DB2-SQL?
FROM Inserted i will that work, or do i have to do something like:
FROM Inserted AS i


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

This are sub-queries and DB2 supports them. Have a look at the syntax
diagram for queries.
But originally, and most importantly, what's wrong with:

DECLARE SomeNumber INT;


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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 7 '06 #7
> 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.
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).
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.
DECLARE SavedUserID int 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: DECLARE SavedUserID integer@
SQL0104N An unexpected token "integer" was found following "DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT

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.
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: SELECT @@spid AS ProcessID
or
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID
How do i declare, set and fetch local variables? What would be syntax to do
that?
And finally, the full trigger i'm trying to create in DB2 that i can't
can't make work.

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)
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.


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.
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.


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.
Mar 7 '06 #8
Ian Boyd wrote:
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.


That's the relational model: everything is a table. Period. ;-)
DECLARE SavedUserID int

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:
DECLARE SavedUserID integer@


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


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

BEGIN ATOMIC ... END
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. 3. Returning a value
SQL Server:
SELECT @@spid AS ProcessID
or
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID
How do i declare, set and fetch local variables? What would be syntax to
do that?


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 )
Do you mean in general other people who write triggers?
I was referring to the posts in this newsgroup.
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.


How would you translate this trimmed down version of a trigger from SQL
Server?


What's trimmed down? It does the same thing unless there is more in the SQL
Server trigger going.
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.


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
Mar 7 '06 #9
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
Mar 7 '06 #10
Ian Boyd wrote:
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.

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
Mar 7 '06 #11
> There are no "fields" in SQL - just rows, columns and values.
You channeling Celko? :)
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.
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.
Yes, to rename a column you have to have a SELECT statement.
2. Declaring Variables
BEGIN
DECLARE SaverUserID INT;
END
BEGIN ATOMIC
DECLARE i INT;
END@


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

3. Returning a value SQL Server:
SELECT @@spid AS ProcessID
or
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID

Again, use VALUES.

Again, i assume that VALUES is a bad thing:

How so?


Because rather than using VALUES: SET ApplicationID = VALUES Application_ID(); i don't use VALUES: SET ApplicationID = Application_ID();
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.
Have a look at the DB2 special registers in the manual: USER, CURRENT
DATE,
CURRENT TIME, CURRENT TIMESTAMP.
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 UPDATE MyTable SET SomeField = NULL
WHERE SomeOtherField IN (SELECT KeyField FROM MyTable2 WHERE Field = 3)
or
INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
VALUES ('Ian', 'Boyd', '728 Helena', NULL, '911-426-3184')
or
INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
SELECT fname, lname, addr1, NULL, NULL FROM legacy_system


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

Mar 7 '06 #12
> 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.
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?

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!'


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;@

Mar 7 '06 #13
Ian Boyd wrote:
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.

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?

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...?)
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!'


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:

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
Mar 7 '06 #14
Ian Boyd wrote:
There are no "fields" in SQL - just rows, columns and values.

You channeling Celko? :)

*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 ;-)
INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
SELECT fname, lname, addr1, NULL, NULL FROM legacy_system


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

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
Mar 7 '06 #15
> 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.
i don't think SQL Server has global variables either - at least i've never
seen them.
I think the name SYSIBM.SYDUMMY1 is rooted in the fact that it returns 1
row.
That's an interesting insight!
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.
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.
This T-SQL: SELECT @@spid AS ProcessID
is NOT column aliasing.
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:
SELECT @@spid AS ProcessID
So the column gets renamed from "" to "ProcessID." Whereas the following SET
operation SET ProcessID = spid;
returns no rows.
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).
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.
Again I'm surprised you try this since AFAIK SQL Server supports no such
thing. all T-SQL must be in a procedure.
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
DECLARE @a int
SET @a = 5
WHILE @A < 5 BEGIN
SET @a = @a + 5
EXECUTE SomeStoredProcedure
END
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.
So let's move into a procedure example:
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:
CALL dostuff(5, 7, ?) Wrong number of parameters
DECLARE c integer; call dostuff(5, 7, ?); An unexpected token "integer" was found following "DECLARE c ".
BEGIN
DECLARE c integer; call dostuff(5, 7, c);
END An unexpected token "DECLARE" was found following "begin "
BEGIN ATOMIC
DECLARE c integer; call dostuff(5, 7, c);
END 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.
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.
A welcome addition.
OK I think that covers it. Let us know how it goes.

Time to go home for the day. Day 9, nothing working yet.
Mar 7 '06 #16
>> 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).


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.


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.
Mar 7 '06 #17
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.
Mar 7 '06 #18
Ian Boyd wrote:
i'm trying to get a super-primer on DB2-SQL, so i can get something up and
running.


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
Mar 8 '06 #19
OK.. 9 days of labour.. some children appear to cause more trouble than
others ;-)

Procedural statements are not supported as independent statements by DB2.

That is you can do:
CREATE..., DROP.., GRANT, REVOKE, ALTER
DECLARE cursors, FETCH, CLOSE (and implied SELECT, VALUES cursors from CLP)
UPDATE,DELETE, INSERT, MERGE
CALL
BEGIN ATOMIC .. END

That's it!

DECLARE variable, SET statement, etc are not 'real' SQL statement. They
must be NESTED in a procedure, trigger, function or said BEGIN ATOMIC

So if you want to run a script with logic from the client you have to
use BEGIN ATOMIC .. END.

Now talking of semicolons. The DB2 engine knows semicolon only inside of
procedures. However semicolon is often also used by query tools as 'go'.
So what happens is that the query tools is chopping up the procedure (or
trigger ...) and sends pieces of the statement, which of course cause
-104 syntax error (unexpected end of statement)

Now I'm somewhat unclear on what query tool you are using.
E.g. the IBM provided tools such as the JDBC Type 4 driver (AFAIK)
detect the BEGIN ATOMIC, an CREATE PROCEDURE keywords and suppress the
batching.
Could it be you are using some MS driver which is ignorant to DB2?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '06 #20
Stefan Momma wrote:
Ian Boyd wrote:
i'm trying to get a super-primer on DB2-SQL, so i can get something up
and running.


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

BTW, when looking for DB2 looks (or IBM published books in general) this
is the place to go:
http://www.redbooks.ibm.com/
The books are free for download as PDF.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '06 #21
Ian Boyd wrote:
There are no "fields" in SQL - just rows, columns and values. You channeling Celko? :)


No, not really. I just want to make sure that you know the terminology used
by DB2 (and the SQL standard) so that you will have an easier time when
looking at the manual.
3. Returning a value
SQL Server:
> SELECT @@spid AS ProcessID
> or
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> SELECT @ProcessID

Again, use VALUES.
Again, i assume that VALUES is a bad thing: How so?


Because rather than using VALUES:
SET ApplicationID = VALUES Application_ID();

i don't use VALUES:
SET ApplicationID = Application_ID();


That doesn't make VALUES a bad thing. It is just not necessary here.
5. Setting a field (a.k.a column) to NULL

SQL Server
UPDATE MyTable SET SomeField = NULL
WHERE SomeOtherField IN (SELECT KeyField FROM MyTable2 WHERE Field = 3)

Works.
or
INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
VALUES ('Ian', 'Boyd', '728 Helena', NULL, '911-426-3184')

This works too.
or
INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
SELECT fname, lname, addr1, NULL, NULL FROM legacy_system


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


This does not work because NULL is untyped and DB2 does not know if the data
types produced by the subselect match with the data types in the table. So
try this:

INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
SELECT fname, lname, addr1,
CAST(NULL AS VARCHAR(10)), CAST(NULL AS VARCHAR(10))
FROM legacy_system

Btw, you got the error SQL0206N in this case. You should first look up the
error description to correct your problem:
-----------------------------------------------------------
$ db2 "? sql0206"

SQL0206N "<name>" is not valid in the context where it is
used.

Explanation:

This error can occur in the following cases:

o For an INSERT or UPDATE statement, the specified column is
not a column of the table, or view that was specified as the
object of the insert or update.

o For a SELECT or DELETE statement, the specified column is not
a column of any of the tables or views identified in a FROM
clause in the statement.

o For an ORDER BY clause, the specified column is a correlated
column reference in a subselect, which is not allowed.

o For a CREATE TRIGGER, CREATE METHOD or CREATE FUNCTION
statement:

- The reference "<name>" does not resolve to the name of a
column, local variable or transition variable.

- The condition name "<name>" specified in the SIGNAL statement
has not been declared.

o For a CREATE TRIGGER statement:

- A reference is made to a column of the subject table without
using an OLD or NEW correlation name.

- The left hand side of an assignment in the SET
transition-variable statement in the triggered action
specifies an old transition variable where only a new
transition variable is supported.

o For a CREATE FUNCTION statement with a PREDICATES clause:

- The RETURN statement of the SQL function references a
variable that is not a parameter or other variable that
is in the scope of the RETURN statement.

- The FILTER USING clause references a variable that is not a
parameter name or an expression name in the WHEN
clause.

- The search target in an index exploitation rule does not
match some parameter name of the function that is being
created.

- A search argument in an index exploitation rule does not
match either an expression name in the EXPRESSION AS
clause or a parameter name of the function being
created.

o For a CREATE INDEX EXTENSION statement, the RANGE THROUGH
clause or the FILTER USING clause references a variable that
is not a parameter name that can be used in the clause.

The statement cannot be processed.

User Response:

Verify that the names are specified correctly in the SQL
statement. For a SELECT statement, ensure that all the required
tables are named in the FROM clause. For a subselect in an ORDER
BY clause, ensure that there are no correlated column references.
If a correlation name is used for a table, verify that subsequent
references use the correlation name and not the table name.

For a CREATE TRIGGER statement, ensure that only new transition
variables are specified on the left hand side of assignments in
the SET transition-variable statement and that any reference to
columns of the subject table have a correlation name specified.

sqlcode : -206

sqlstate : 42703
-----------------------------------------------------------

Unfortunately, this particular situation is not explained explicitly. So
you can only derive that NULL is interpreted as column name. (Note that
DB2 allows a column to be named NULL.)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 8 '06 #22
Ian Boyd wrote:
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.
I would not call this a "virtual" table. It is a table (like views are
tables). The major issue here is where the data of the table comes from.
In this case, the table is constructed on the fly.
Like i said before, i'll have to figure out where VALUES would be useful
to me.
This T-SQL: SELECT @@spid AS ProcessID
is NOT column aliasing.


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:
SELECT @@spid AS ProcessID


So the column gets renamed from "" to "ProcessID."


Note that DB2 names expressions that are returned from a query itself (it
numbers them) if they don't have an explicit name. That's why you see the
"1" or "2" in the following output:

$ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1"

1 2
----------- -----------
6 15

1 record(s) selected.
Same thing here:

$ db2 "values ( 1+2+3, 4+5+6 )"

1 2
----------- -----------
6 15

1 record(s) selected.

And those "1" or "2" can be used in a Java application when you fetch the
data from the result set by column name.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 8 '06 #23
> $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1"

1 2
----------- -----------
6 15

1 record(s) selected.
Same thing here:

$ db2 "values ( 1+2+3, 4+5+6 )"

1 2
----------- -----------
6 15

1 record(s) selected.

And those "1" or "2" can be used in a Java application when you fetch the
data from the result set by column name.


Unless someone changed the query to
SELECT 3.14159, 1+2+3, 4+5+6

1 2 3
-------- -------- --------
3.14159 6 15

1 record(s) selected.

And now all the application logic has to be rewritten.
Mar 8 '06 #24
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:47************@individual.net...
Ian Boyd wrote:
There are no "fields" in SQL - just rows, columns and values. You channeling Celko? :) *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 ;-)


Warning. Celko bait ahead:
<CelkoBait>
Yes. But those are semantics of the most anal kind. i have seen many, many,
many posts of Celko explaining how if you confuse a row/record and
column/field, you are doing yourself a disservice.

We can all agree that someplace on my hard drive is a the value for a
particular column of a particular row. And we can all agree to call that a
field. Simarly, if i select a specific row from a table, that is a record.

Yes, the terms were invented when one table was stored in one file, and the
notion of "the next 6 rows" was perfectly valid. Yes, modern databases store
things in pages all over the place, but there is still some physical order.
The btree has an order. You can't guarantee that order, or ever even see it.
But the rows that you return are records.

Let us all agree that the terms record/field while originally didn't apply
to RDMS's, now do.
</CelkoBait>
INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
SELECT fname, lname, addr1, NULL, NULL FROM legacy_system


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

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.


Excellent. Thank you. i, of course, would prefer it if DB2 would just do it.
But i'm okay with it forcing me to tell it that what it thinks i want to do
is really what i want to do.
Mar 8 '06 #25
Ian Boyd wrote:
$ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1"

1 2
----------- -----------
6 15

1 record(s) selected.
Same thing here:

$ db2 "values ( 1+2+3, 4+5+6 )"

1 2
----------- -----------
6 15

1 record(s) selected.

And those "1" or "2" can be used in a Java application when you fetch the
data from the result set by column name.


Unless someone changed the query to
SELECT 3.14159, 1+2+3, 4+5+6

1 2 3
-------- -------- --------
3.14159 6 15

1 record(s) selected.

And now all the application logic has to be rewritten.

You two got yor wires crossed.. Knut is talking about correlation names
(above the ----- line).
You are now talking about result types.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '06 #26
Ian Boyd wrote:
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:47************@individual.net...
Ian Boyd wrote:
There are no "fields" in SQL - just rows, columns and values.
You channeling Celko? :)
*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 ;-)
Warning. Celko bait ahead:
<CelkoBait>

<snip> </CelkoBait>


Different styles. In this group using the f-word causes a raised
eyebrow. Joe i s abit more opinionated.
Let me put in another way:
There is a _statistical_ correlation between using the professional
vocabulary and the skill level.
Fields and Records are EXCEL and ACCESS speak. It _suggests_ a
technologcal "redneck".
In usenet, perception is everything ;-)
INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
SELECT fname, lname, addr1, NULL, NULL FROM legacy_system
In my variant of the 3rd case in DB2, it complains that "NULL is not
valid
in the context where it is used."

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.


Excellent. Thank you. i, of course, would prefer it if DB2 would just do it.
But i'm okay with it forcing me to tell it that what it thinks i want to do
is really what i want to do.

When the SQL standard was created strong typing was desired.
In reality most products have long abandoned strong typing.
I agree that DB2 is doing a futile "last stand" here.. one of these days
that will be relaxed.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '06 #27
> Procedural statements are not supported as independent statements by DB2.

That is you can do:
CREATE..., DROP.., GRANT, REVOKE, ALTER
DECLARE cursors, FETCH, CLOSE (and implied SELECT, VALUES cursors from
CLP)
UPDATE,DELETE, INSERT, MERGE
CALL
BEGIN ATOMIC .. END


On some level i wondered if that was how DB2 did it. But i convinced myself
that such a scheme is just too complicated to be true, and i assumed that i
had some fundamental misunderstanding about what is going on.

Is there a complete list somwhere of what i can and cannot run?
Specifically, i'm concerned about the "implied" SELECT and VALUES. My
confusion comes from the fact that sometimes i can just run SELECT * FROM
...., and other times i have to declare a cursor for a select, and then leave
the cursor open. i don't see OPEN in your list.

In fact, a quick check of the IBM DB2 Universal Database SQL Reference
Volume 1 and 2 makes no mention of the keyword BEGIN except for "BEGIN
DECLARE SECTION". Neither does the "DB2 Information Center" website. Can you
point me to some references on this?
Mar 8 '06 #28
>But i convinced myself that such a scheme is just too complicated to be true, and i assumed that i
had some fundamental misunderstanding about what is going on.
Welcome to the non-MS/Windows world. Where you are expected to
understand what your are doing. :)
My confusion comes from the fact that sometimes i can just run SELECT * FROM
..., and other times i have to declare a cursor for a select


A SELECT statement can be run outside a block of code, a DECLARE
within.

The difference is, SELECT is a "statement" and DECLARE is a "control
statement". Both clearly delineated in SQL Reference Volume 2.

Generally, the beginning of the documentate for a particular statement
says when it can (and sometimes when it cannot) be executed.

B.

Mar 8 '06 #29
Ian Boyd wrote:
Procedural statements are not supported as independent statements by DB2.

That is you can do:
CREATE..., DROP.., GRANT, REVOKE, ALTER
DECLARE cursors, FETCH, CLOSE (and implied SELECT, VALUES cursors from
CLP)
UPDATE,DELETE, INSERT, MERGE
CALL
BEGIN ATOMIC .. END
On some level i wondered if that was how DB2 did it. But i convinced myself
that such a scheme is just too complicated to be true, and i assumed that i
had some fundamental misunderstanding about what is going on.

Is there a complete list somwhere of what i can and cannot run?
Specifically, i'm concerned about the "implied" SELECT and VALUES. My
confusion comes from the fact that sometimes i can just run SELECT * FROM
..., and other times i have to declare a cursor for a select, and then leave
the cursor open. i don't see OPEN in your list.

I forgot OPEN :-)
The interactive tools (like command center and CLP) have short hands for
queries. That is when you type VALUES or SELECT interactively they
will declare a cursor for you, open it, fetch all the rows and close.
Then they pretty print the output.
In fact, a quick check of the IBM DB2 Universal Database SQL Reference
Volume 1 and 2 makes no mention of the keyword BEGIN except for "BEGIN
DECLARE SECTION". Neither does the "DB2 Information Center" website. Can you
point me to some references on this?

This is the root for SQL Procedure logic:
http://publib.boulder.ibm.com/infoce...n/r0004239.htm
This is the root for the simpler command line scripting:
http://publib.boulder.ibm.com/infoce...n/r0004240.htm

Here are the sentences for the GOTO statement (random example):
"GOTO statement

The GOTO statement is used to branch to a user-defined label within an
SQL procedure.

Invocation

This statement can only be embedded in an SQL procedure. It is not an
executable statement and cannot be dynamically prepared."

You implicitly raise an interesting point though.
The SQL Reference is "dictionary" it is as little the right tool to
learn the basics of SQL as any dictionary.

You are used to MS SQL Server "Books Online" which is more of a guide.
It describes what matters example driven).
The DB2 SQL Ref is the _exact_ specification of DB2's SQL.
There are plans to deliver a SQL Guide in a future release which will be
more appropriate and have information such as which statement can be
used where, and include scenario based examples.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '06 #30
5. Comments

How to do comments in DB2-SQL?

From:
IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
Chapter 2. Language Elements
Tokens

<quote>
Comments
Static SQL statements may include host language comments or SQL comments.
Either type of comment may be specified wherever a space may be specified,
except
within a delimiter token or
between the keywords EXEC and SQL.
SQL comments are introduced by two consecutive hyphens (--) and ended by the
end of the line.
</quote>
The following DB2-SQL fails:

INSERT INTO Daily_Logs (
Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID, Bay_Number,
Supervisor_User_ID, Location_ID, Occurrence_ID, Checklist_ID,
Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date,
Description)
VALUES (
'DL-20060307-1', --DailyLogNumber
0, --CreatedByUserID
1, --PropertyID
1, --ShiftID
"A74", --BayNumber
1, --SupervisorUserID
2, --LocationID
CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls)
CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID
5, --DailyLogSubTypeID
'2006-03-01 11:11:07.11111', --StartDate
'2006-03-01 11:21:18.22222', --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls)
);

But if a take out the comments, it works. i have no keywords EXEC or SQL,
and i am not putting my comments within a delimiter token "," since i
cannot split a comma into two parts.

*so tired*
Mar 8 '06 #31
> Welcome to the non-MS/Windows world. Where you are expected to
understand what your are doing. :)


They have databases on computers these days. i know what i'm doing, and
computers are powerful enough these days to know as well; or at least
proceed with the only possible course of action.
My confusion comes from the fact that sometimes i can just run SELECT *
FROM
..., and other times i have to declare a cursor for a select


A SELECT statement can be run outside a block of code, a DECLARE
within.

The difference is, SELECT is a "statement" and DECLARE is a "control
statement". Both clearly delineated in SQL Reference Volume 2.

Generally, the beginning of the documentate for a particular statement
says when it can (and sometimes when it cannot) be executed.


That explains why the reference doesn't include some statements, their not
the right "kind" of statements.
i see the majority of the reference is in a section called "Statements". i
don't see a corresponding section of "control statements", nor is the
keyword DECLARE in the index. Is there a Reference Volume 3 that documents
the "control statements?" Are there are more kinds of statements?

[Comicbook Guy] Umm, excuse me. Clearly select is a statement, and declare
is a control statement. Thank you.
Mar 8 '06 #32
>select 1+2+3, 4+5+6 from sysibm.sysdummy1
1 2
----------- -----------
6 15
SELECT 3.14159, 1+2+3, 4+5+6
1 2 3
-------- -------- --------
3.14159 6 15
You two got yor wires crossed.. Knut is talking about correlation names
(above the ----- line).
You are now talking about result types.


?

i thought we were talking about correlation names and how they are still
necessary even if there is only one column in the results set.
Mar 8 '06 #33
> This is the root for SQL Procedure logic:
http://publib.boulder.ibm.com/infoce...n/r0004239.htm
This is the root for the simpler command line scripting:
http://publib.boulder.ibm.com/infoce...n/r0004240.htm
i get it. Some statements are only valid inside other statements. If of
course be convient if they would just work.
This statement can only be embedded in an SQL procedure. It is not an
executable statement and cannot be dynamically prepared."
Why? It can't be a technical limitation, and there is little value in making
things more difficult for people to use.
Is it the standard? And if so how many companies in the consortium are
trying to change it? It's fair to say that in the end this stuff is meant to
be used by developers, not just computer scientists.
You implicitly raise an interesting point though.
The SQL Reference is "dictionary" it is as little the right tool to learn
the basics of SQL as any dictionary.
Every other computer language has keywords or tokens, and someplace you can
open a reference manual and get an explanation for that token, or a
reference to where it is used.
You are used to MS SQL Server "Books Online" which is more of a guide.
It describes what matters example driven).
The DB2 SQL Ref is the _exact_ specification of DB2's SQL.
Which is about a dry a read as ISO/IEC 9075 is, albeit more helpful.
There are plans to deliver a SQL Guide in a future release which will be
more appropriate and have information such as which statement can be used
where, and include scenario based examples.


An index would be nice. Seaching a web-site, pdf, google groups, or the
internet for "ibm db2 set" doesn't help so much. But if i could type SET and
be presented with the index entries that someone has already taken the time
to pre-select, would be so so SO SO SO much more useful.

In the Books Online, i rarely use the "contents" and i rarely use "search".
i use the index almost exclusivly. If i want help on, for example, SET, i
type the word SET and am presented with the documentation on the SET
keyword. Doing a word search for "SET" would be folly.
Mar 8 '06 #34
Ian Boyd wrote:
The following DB2-SQL fails:

INSERT INTO Daily_Logs (
Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID,
Bay_Number, Supervisor_User_ID, Location_ID, Occurrence_ID,
Checklist_ID, Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date,
End_Date, Description) VALUES (
'DL-20060307-1', --DailyLogNumber
0, --CreatedByUserID
1, --PropertyID
1, --ShiftID
"A74", --BayNumber
1, --SupervisorUserID
2, --LocationID
CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls)
CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID
5, --DailyLogSubTypeID
'2006-03-01 11:11:07.11111', --StartDate
'2006-03-01 11:21:18.22222', --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls)
);

But if a take out the comments, it works. i have no keywords EXEC or
SQL, and i am not putting my comments within a delimiter token ","
since i cannot split a comma into two parts.
Yup, in DB2 SQL comments must appear as the first non-whitespace
characters in a line. Hence:
SELECT
AFIELD, -- This is not a comment
FROM ...
SELECT
-- This is a comment
AFIELD,
FROM ...

5. Comments

How to do comments in DB2-SQL?

From:
IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
Chapter 2. Language Elements
Tokens

<quote>
Comments
Static SQL statements may include host language comments or SQL
comments. Either type of comment may be specified wherever a space
may be specified, except within a delimiter token or between
the keywords EXEC and SQL. SQL comments are introduced by two
consecutive hyphens (--) and ended by the end of the line. </quote>

Strange that the manual doesn't make any mention of this behaviour. I
could swear it did at some point in the past, but maybe my memory's
faulty. It is an annoying behaviour, especially as it's not exactly
difficult to change a parser to permit -- comments pretty much anywhere
(if anything, it's more difficult to write a parser that only permits
-- comments as the first non-whitespace characters in a line, something
I've found out from experience in writing syntax highlighters for SQL
editors and such like).

HTH,

Dave.
Mar 8 '06 #35
Knut Stolze wrote:

[snip]
Note that DB2 names expressions that are returned from a query itself
(it numbers them) if they don't have an explicit name. That's why
you see the "1" or "2" in the following output:

$ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1"

1 2
----------- -----------
6 15

1 record(s) selected.
Same thing here:

$ db2 "values ( 1+2+3, 4+5+6 )"

1 2
----------- -----------
6 15

1 record(s) selected.

And those "1" or "2" can be used in a Java application when you fetch
the data from the result set by column name.


Yes, though my personal opinion is that it's a bad idea to use the
"raw" numeric column names that DB2 generates; they're subject to
change if the query changes, and can't be used in all the same ways as
a properly named column. For example:

Numeric column names

SELECT * FROM (VALUES (1, 2)) AS T; -- Works
SELECT 1, 2 FROM (VALUES (1, 2)) AS T; -- Works
SELECT T.* FROM (VALUES (1, 2)) AS T; -- Works
SELECT T.1, T.2 FROM (VALUES (1, 2)) AS T; -- Doesn't work

Aliased column names

SELECT * FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
SELECT F1, F2 FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
SELECT T.* FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
SELECT T.F1, T.F2 FROM (VALUES (1, 2)) AS T(F1, F2); -- Works

Hence, I'd always recommend one renames generated column names to
something meaningful.

HTH,

Dave.
Mar 8 '06 #36
6. Column defaults

Follownig works:
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN CHANGEDATE
SET WITH DEFAULT CURRENT TIMESTAMP ;

Following fails:
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN APPNAME
SET WITH DEFAULT CURRENT CLIENT_APPLNAME ;

Both are special registers.
Mar 8 '06 #37
Ian Boyd wrote:
5. Comments

How to do comments in DB2-SQL?

From:
IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
Chapter 2. Language Elements
Tokens

<quote>
Comments
Static SQL statements may include host language comments or SQL comments.
Either type of comment may be specified wherever a space may be specified,
except
within a delimiter token or
between the keywords EXEC and SQL.
SQL comments are introduced by two consecutive hyphens (--) and ended by the
end of the line.
</quote>
The following DB2-SQL fails:

INSERT INTO Daily_Logs (
Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID, Bay_Number,
Supervisor_User_ID, Location_ID, Occurrence_ID, Checklist_ID,
Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date,
Description)
VALUES (
'DL-20060307-1', --DailyLogNumber
0, --CreatedByUserID
1, --PropertyID
1, --ShiftID
"A74", --BayNumber
1, --SupervisorUserID
2, --LocationID
CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls)
CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID
5, --DailyLogSubTypeID
'2006-03-01 11:11:07.11111', --StartDate
'2006-03-01 11:21:18.22222', --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls)
);

But if a take out the comments, it works. i have no keywords EXEC or SQL,
and i am not putting my comments within a delimiter token "," since i
cannot split a comma into two parts.

*so tired*

Ian, what tool are you using. This works for me using CLP
Please clarify your environment.

The thing about -- is that if your client strips out line feeds then
everything after the first -- will look like a comment.
select * --hello from -- comment t -- more comment
And of course select * is not legal SQL. There is nothing DB2 can do on
-- if the client screws things up... so please clarify your client
interface.

Cheers
Serge

PS: I find this thread quite interesting actually.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '06 #38
>They have databases on computers these days. i know what i'm doing, and
computers are powerful enough these days to know as well; or at least
proceed with the only possible course of action.
The day DBs do things for me, is the day i stop doing databases. I
actually despise Windows mostly because of these assumptions. I love
DBs, because they are so simple, and make no assumptions.
That explains why the reference doesn't include some statements, their not
the right "kind" of statements.
Close. It's because, they are not statements.
i see the majority of the reference is in a section called "Statements". i
don't see a corresponding section of "control statements"
In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL
control statements".
, nor is the keyword DECLARE in the index.


It is absolutely in the index. Though, it is not a bookmark.

It is in Chapter 2.=>Compound Statement (Procedure) under
"SQL-variable-declaration".

A search of the index (which is a bookmark) found it for me pretty
easily.

B.

Mar 8 '06 #39
Serge Rielau wrote:
Ian Boyd wrote:
5. Comments

How to do comments in DB2-SQL?

From:
IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
Chapter 2. Language Elements
Tokens

<quote>
Comments
Static SQL statements may include host language comments or SQL
comments. Either type of comment may be specified wherever a space
may be specified, except within a delimiter token or between
the keywords EXEC and SQL. SQL comments are introduced by two
consecutive hyphens (--) and ended by the end of the line.
</quote>
The following DB2-SQL fails:

INSERT INTO Daily_Logs (
Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID,
Bay_Number, Supervisor_User_ID, Location_ID, Occurrence_ID,
Checklist_ID, Daily_Log_Type_ID, Daily_Log_SubType_ID,
Start_Date, End_Date, Description) VALUES (
'DL-20060307-1', --DailyLogNumber
0, --CreatedByUserID
1, --PropertyID
1, --ShiftID
"A74", --BayNumber
1, --SupervisorUserID
2, --LocationID
CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls)
CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID
5, --DailyLogSubTypeID
'2006-03-01 11:11:07.11111', --StartDate
'2006-03-01 11:21:18.22222', --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls)
);

But if a take out the comments, it works. i have no keywords EXEC
or SQL, and i am not putting my comments within a delimiter token
"," since i cannot split a comma into two parts.

*so tired*

Ian, what tool are you using. This works for me using CLP
Please clarify your environment.


This works in CLP? Doesn't for me! From DB2 UDB v8 under Linux:

$ db2 -t
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.0

[snip help stuff]

db2 => SELECT
db2 (cont.) => F1, -- A comment
db2 (cont.) => F2, -- Another comment
db2 (cont.) => F3 -- Yet another comment
db2 (cont.) => FROM
db2 (cont.) => (VALUES (1, 2, 3)) AS T(F1, F2, F3);
SQL0104N An unexpected token "," was found following "SELECT F1".
Expected
tokens may include: "<table_expr>". SQLSTATE=42601
db2 => SELECT
db2 (cont.) => -- A comment
db2 (cont.) => F1,
db2 (cont.) => -- Another comment
db2 (cont.) => F2,
db2 (cont.) => -- Yet another comment
db2 (cont.) => F3
db2 (cont.) => FROM
db2 (cont.) => (VALUES (1, 2, 3)) AS T(F1, F2, F3);

F1 F2 F3
----------- ----------- -----------
1 2 3

1 record(s) selected.

The thing about -- is that if your client strips out line feeds then
everything after the first -- will look like a comment. select *
--hello from -- comment t -- more comment And of course select * is
not legal SQL. There is nothing DB2 can do on -- if the client screws
things up... so please clarify your client interface.
Nope, in this case I think it's the thing I mentioned in my other post:
comments can only appear as the first non-whitespace characters in a
line.
PS: I find this thread quite interesting actually.


Absolutely. In another post I was rambling on vaguely incoherently
about functional versus procedural styles in DB2 and other DBs ... I'm
beginning to suspect there's a whole different way of thinking required
when switching from certain relational systems to others (a bit like
learning functional programming after doing C/Pascal imperative stuff
for so long ... I remember feeling very fatigued at how difficult
everything seemed, until there came a point where I just "got it" and
it all just seemed to fall into place ... the relief was tangible!)
Cheers,

Dave.
Mar 8 '06 #40
More examples.

--Works
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN USERNAME
SET WITH DEFAULT USER ;

--Works
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN CHANGEDATE
SET WITH DEFAULT CURRENT TIMESTAMP ;

--Fails (unexpected token near "CLIENT_APPLNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN APPNAME
SET WITH DEFAULT CURRENT CLIENT_APPLNAME ;

--Fails (unexpected token near "CLIENT_WRKSTNNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN HOSTNAME
SET WITH DEFAULT CURRENT CLIENT_WRKSTNNAME ;
Mar 8 '06 #41
Brian Tkatch wrote:

[snip]
The day DBs do things for me, is the day i stop doing databases.
You hand-crank the execution plan for all your queries? Wow ...
hardcore man! (joking :-)
I actually despise Windows mostly because of these assumptions. I love
DBs, because they are so simple, and make no assumptions.


I'd take issue with this in one particular area. I've always liked that
one can tweak just about *any* performance parameter in DB2. That said,
I've come to enjoy the ability added in more recent versions to have
the tools figure out an "optimum" configuration, or in the most recent
versions to just set the parameter to AUTOMATIC and have the database
look after itself.

I'd be extremely disappointed if such configuration parameters were
ever removed completely from manual control ... but I do appreciate a
bit of "intelligence" being added to the system, provided it's optional
:-)

Cheers,

Dave.
Mar 8 '06 #42
I stand corrected. I ran the insert statement and it came back with
"table not found", so I figured I got past the syntax checks..
apparently a hasty and wrong assumption.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '06 #43
Ian Boyd wrote:
More examples.

--Works
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN USERNAME
SET WITH DEFAULT USER ;

--Works
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN CHANGEDATE
SET WITH DEFAULT CURRENT TIMESTAMP ;

--Fails (unexpected token near "CLIENT_APPLNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN APPNAME
SET WITH DEFAULT CURRENT CLIENT_APPLNAME ;

--Fails (unexpected token near "CLIENT_WRKSTNNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN HOSTNAME
SET WITH DEFAULT CURRENT CLIENT_WRKSTNNAME ;


Bizarre. I've just tried the same with some test tables in DB2 UDB 8
under Linux and got the same thing. I can't see anything in the
reference explicitly forbidding such a thing, but maybe there's some
other reason? (none that I can think of at the moment).

Still, it seems to work within a BEGIN ATOMIC block:

CREATE TABLE AUDIT_LOG (
USERNAME VARCHAR(128) NOT NULL WITH DEFAULT CURRENT USER,
CHANGEDATE TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
APPNAME VARCHAR(255) NOT NULL,
HOSTNAME VARCHAR(255) NOT NULL
)!

BEGIN ATOMIC
DECLARE MY_APPNAME VARCHAR(255);
DECLARE MY_HOSTNAME VARCHAR(255);
SET MY_APPNAME = CURRENT CLIENT_APPLNAME;
SET MY_HOSTNAME = CURRENT CLIENT_WRKSTNNAME;
INSERT INTO AUDIT_LOG (APPNAME, HOSTNAME)
VALUES (MY_APPNAME, MY_HOSTNAME);
END!

A word of caution: I've used CURRENT USER in the statements above. This
is *not* the same as USER (and then there's SESSION_USER and
SYSTEM_USER as well). See the reference manual for the differences
between them.

HTH,

Dave.
Mar 8 '06 #44
Working as documented:
http://publib.boulder.ibm.com/infoce...n/r0000888.htm

DEFAULT ...

datetime-special-register
Specifies the value of the datetime special register (CURRENT DATE,
CURRENT TIME, or CURRENT TIMESTAMP) at the time of INSERT, UPDATE, or
LOAD as the default for the column. The data type of the column must be
the data type that corresponds to the special register specified (for
example, data type must be DATE when CURRENT DATE is specified).

user-special-register
Specifies the value of the user special register (CURRENT USER,
SESSION_USER, SYSTEM_USER) at the time of INSERT, UPDATE, or LOAD as the
default for the column. The data type of the column must be a character
string with a length not less than the length attribute of a user
special register. Note that USER can be specified in place of
SESSION_USER and CURRENT_USER can be specified in place of CURRENT USER.

CURRENT SCHEMA
Specifies the value of the CURRENT SCHEMA special register at the
time of INSERT, UPDATE, or LOAD as the default for the column. If
CURRENT SCHEMA is specified, the data type of the column must be a
character string with a length greater than or equal to the length
attribute of the CURRENT SCHEMA special register.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '06 #45
> Ian, what tool are you using. This works for me using CLP
Please clarify your environment.
i've tried a couple:

- Microsoft ADO using the IBM DB2 driver for ODBC

- Microsoft ADO using the IBM DB2 OLEDB Provider

- 3rd party program called "WinSQL" which connects through an ODBC DSN (with
it's built-in statement delimiter changed to ½)

- IBM Command Editor (db2ce.bat) with it's "Statement termination character"
changed to ½

i'll show you the detailed results from IBM Command Editor, as it returms
more error information than the simple exception thrown by ADO from the ODBC
for OLEDB providers.

<quote>
------------------------------ Commands
Entered ------------------------------
INSERT INTO Daily_Logs (
Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID,
Bay_Number,
Supervisor_User_ID, Location_ID, Occurrence_ID, Checklist_ID,
Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date,
Description)
VALUES (
'DL-20060307-3', --DailyLogNumber
0, --CreatedByUserID
1, --PropertyID
1, --ShiftID
'A74', --BayNumber
1, --SupervisorUserID
2, --LocationID
CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls)
CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID
5, --DailyLogSubTypeID
'2006-03-01 11:11:07.11111', --StartDate
'2006-03-01 11:21:18.22222', --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls)
);½
------------------------------------------------------------------------------
INSERT INTO Daily_Logs ( Daily_Log_Number, Created_By_User_ID, Property_ID,
Shift_ID, Bay_Number, Supervisor_User_ID, Location_ID, Occurrence_ID,
Checklist_ID, Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date,
Description) VALUES ( 'DL-20060307-3', --DailyLogNumber 0, --CreatedByUserID
1, --PropertyID 1, --ShiftID 'A74', --BayNumber 1, --SupervisorUserID
2, --LocationID CAST(NULL AS bigint), --Occurrence_ID (must manually cast
nulls) CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID 5, --DailyLogSubTypeID '2006-03-01
11:11:07.11111', --StartDate '2006-03-01 11:21:18.22222', --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls) );
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "," was found following "ES (
'DL-20060307-3'".
Expected tokens may include: ")". SQLSTATE=42601

SQL0104N An unexpected token "," was found following "ES (
'DL-20060307-3'". Expected tokens may include: ")
".

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.

As an aid to the programmer, a partial list of valid tokens is
provided in the SQLERRM field of the SQLCA as "<token-list>".
This list assumes the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601
</quote>

PS: I find this thread quite interesting actually.

In a morbid train-wreck sorta way?

i'm approaching DB2 from a very high-level (and overview if you will). It's
not like i don't understand relational databases. i think i am very used to
SQL Server, where it is very powerful and yet very friendly. If you accept
that most enterprise class RDBMS are of a similar feature set, the different
between is semantics, and tools, and language.

i'm walking a fine line here. There are things in DB2 that make no sense.
They are, quite plainly, non-sensical. i try to explain what i think the
vision and philosophy that DB2 has for doing things - as though there was
an all-encompassing grand vision for everything. But things are not that
way. Due to historical design decisions, backwards compatiblity, forward
compatiblity, standards compatability, etc things can make little sense to
an outside observer. Which is fine, as long as i can quickly find the list
of all these different design paradigms. But many zealots will take my
confusion and frustration of the scattered design as an insult, and thats a
tough needle to thread, especially in a DB2 newsgroup - where i am stating
up front i come from Microsoft SQL Server, and a lot of people in here have
used the word "Micro$oft".

Also, when dealing with, and writing many user interfaces, i have become
picky about programs or systems that cannot do what a user expects. So some
of IBM's graphical tools, and SQL language itself, can leave much to be
desired from a usability point of view.

i'm walking a fine line here: of trying to extract information from the
people in the know, without touching a nerve.

Mar 8 '06 #46
Serge Rielau wrote:
Working as documented:
http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2.
udb.doc/admin/r0000888.htm

DEFAULT ...

[snip]

But why can CURRENT CLIENT_APPLNAME and CURRENT CLIENT_WRKSTNNAME not
be used as column defaults? I'm guessing there must be some technical
reason, e.g. maybe there are some circumstances in which these
registers would be unknown or undefined when inserting a row into a
table? (though I guess one could argue that these registers ought to
evaluate to NULL under such circumstances instead of being impossible
to use as a column default)

Cheers,

Dave.

Mar 8 '06 #47
> The day DBs do things for me, is the day i stop doing databases. I
actually despise Windows mostly because of these assumptions. I love
DBs, because they are so simple, and make no assumptions.
The running joke at the office is the DB2 "WARNING: Everything is okay!"
dialog box.

i hit "OK" to save my changes to, for example, a table. Everything saves
okay, but DB2 feels compelled to pop up a big dialog with a lot of text in
it. Obviously something bad happened, because if everything saved okay after
i told you to save, the edit table screen would go way, and we'd be done.

So each and every time we see one of these boxes pop up, we have to read it:

DB2 Message
Commands attempted
<some sql that i didn't ever enter>
The command completed successfully.

Explanation:
No errors were encountered during the execution of this
command.
User Response:
No action required.

Warning! Everything is okay.

It is just as stupid as installing the game Ghost Recon a few years ago, a
dialog box pops up, "You have enough free space on this drive to install the
game. Proceed?" As if i needed to know that.
<StartOfRanging @skip="yes">
Or another one, i want to shrink a varchar field length:

DBAINTRN
The table and its related objects will be dropped and re-created.

Changing the definition of a column attribute involves a
LOAD operation. If the database is recoverable, a load copy will be
taken for the table being altered. The load copy will be saved as
SUPERDUDE.AUDIT_LOG_table.dat. You can delete the load
copy file once a backup has been taken for all the related
tablespaces.

Would you like to proceed?

Would i like to proceed? Well, i don't know. i didn't ask you to drop and
re-create the table and all it's dependant objects. i told you to shink a
column from 128 to 100 characters. You do whatever it is you have to do to
do it. Are you trying to ask me if i'm okay with the various steps you have
to do to perform that operation? Why wouldn't i be? My only choices are to
either save my changes or not. i said save, so do it.

Some people will argue, "But the table will have to be dropped and renamed
and all keys will recreated. It could potentially be a long operation and
maybe the user didn't realize it would take so long to do - so we need to
get their permission before doing it. Or worse yet, what if there's a power
failure, and something is left in a broken state. At least the user knows
that DB2 was performing this relativly big operation. We have to inform the
user before we just go ahead and do this."

No you don't. i said save, you save. You do whatever it is you do when you
have to save. If it takes a long time, i'm okay with that, because i was
changing table structure - i'll expect it to take a long time.
And with the nulls. Why can't it implicitly cast a NULL to the type of the
column? What alternative is there? You are perfectly okay casting other
types around, why not NULL? Null is the lack of data, it is nothing, it is
not being. People will argue,

"But DB2 is a strongly typed system, and null doesn't have the same type as
integer. What if the user didn't mean to put a NULL into that column, and we
went ahead and did it anyway, that would be bad. If the user really meant to
put null in this field they should indicate that by casting it to the proper
target data type."

No i don't. i said put null in this column. Your choices are to put it in
the column, or not. So why would you not do it? Implicitly cast it and get
it done. Just do it. i should have to tell twice, when everything knows
that's what i want done. Even the village idiot comes to that conclusion.
</StartOfRanging>

That explains why the reference doesn't include some statements, their not
the right "kind" of statements.


Close. It's because, they are not statements.


i guess this is where some help with examples would be extraordinarily
useful.
i see the majority of the reference is in a section called "Statements". i
don't see a corresponding section of "control statements"


In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL
control statements".


SQL Reference Volume 1
Contents
About this book
Chapter 1. Concepts
Chapter 2. Language elements
Chapter 3. Functions
Chapter 4. Queries
Appendix A. SQL limits
...
Appendix Q. Notices
Index
Contacting IBM

SQL Reference Volume 2
Contents
About this book
Statements
Appendix A. DB2 Universal Databse technical information
Appendix B. Notices
Index
Contacting IBM

These are two PDF files that are referred to by the online documentation. i
reach the online documentation from Control Center by hitting: Help->About

On the web-site, i see
Reference
SQL
How to read the syntax diagrams
Common syntax elements
Language elements
Functions
Procedures
Queries
Statements
Reserved schema names and reserved words
SQL statements allowed in routines
Communications areas, descriptor areas, and exception tables
Explain tables
Explain register values
Japanese and traditional-Chinese extended UNIX code (EUC)
considerations
Backus-Naur form (BNF) specifications for DATALINKs

, nor is the keyword DECLARE in the index.


It is absolutely in the index. Though, it is not a bookmark.

It is in Chapter 2.=>Compound Statement (Procedure) under
"SQL-variable-declaration".

A search of the index (which is a bookmark) found it for me pretty
easily.

i gotta find this book, web-site, pdf, help file, or eBook you got.
Mar 8 '06 #48
> Working as documented:
http://publib.boulder.ibm.com/infoce...n/r0000888.htm


By working at intended do you mean that only some special registers can be
used as default values on columns and not others? Does that mean that it
can't be done? Can you suggest some workarounds to accomplish the same task?

--Fails (unexpected token near "CLIENT_APPLNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN APPNAME
SET WITH DEFAULT CURRENT CLIENT_APPLNAME ;

--Fails (unexpected token near "CLIENT_WRKSTNNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN HOSTNAME
SET WITH DEFAULT CURRENT CLIENT_WRKSTNNAME ;
This goes to what i was saying before about non-sensical nature of DB2.
"Why can some special registers be used as column defaults and not others?"
"Cause."
"Wouldn't you maybe want to clean that up so it is consistent?"
Mar 8 '06 #49
> Nope, in this case I think it's the thing I mentioned in my other post:
comments can only appear as the first non-whitespace characters in a
line.


i can't get that to work either:

INSERT INTO Daily_Logs (
Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID, Bay_Number,
Supervisor_User_ID, Location_ID, Occurrence_ID, Checklist_ID,
Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date,
Description)
VALUES (
--DailyLogNumber
'DL-20060307-36',
0,
1,
1,
'A74',
1,
2,
CAST(NULL AS bigint),
CAST(NULL AS bigint),
2,
5,
'2006-03-01 11:11:07.11111',
'2006-03-01 11:21:18.22222',
CAST(NULL AS varchar(1))
);

SQL0104N An unexpected token "(" was found following "DESCRIPTION) VALUES".
Expected tokens may include: "<table_value_constructor_list>".
SQLSTATE=42601


Mar 8 '06 #50

This thread has been closed and replies have been disabled. Please start a new discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.