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

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

Page 1 of 3

• 1
• 2
• 3
• >

138 Replies

---

---

P: n/a

Brian Tkatch

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

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

Though i agree that DB2 can be tedious and confusing, note that SQL
Server is super easy, and it pretty much breaks the standard to do it.
So, give db2 a shot. :) As long as you come with the attitude that it
can be done, you just don't know how, the users of this group are very
helpful.
[color=blue]
>1. Selecting a value[/color]

SQL Server:
SELECT 'Hello, world!'

This is wholly incorrect. It is not SQL whatsoever. It is a convenience
added by Sybase/SQL Server.

In the DB2 world, you use: VALUES 'Hello World'
[color=blue]
> SELECT 'Hello, world!' FROM SYSIBM.SysDummy1
> 1 Row(s) affected
>
>
>Is there a SysDummy2? 3? Why?[/color]

Compatability with other systems, that do not use a special statement,
and require *all* statments to include a TABLE reference. Oracle
supplies a one-record TABLE called Dual. Other systems use other names.
[color=blue]
>1. Declaring a variable
>
>SQL Server:
> DECLARE @SavedUserID int
> The command(s) completed successfully.[/color]

Drop the useless @ symbol, and end all statement with a semi-colon.
Also, there is no implicit block of code, so you must start your own.

BEGIN
DECLARE SaverUserID INT;
END

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

Again, use VALUES.

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

[color=blue]
>DB2
> CREATE PROCEDURE foo
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
> P1: BEGIN
> --declare the cursor
> DECLARE cursor1 CURSOR WITH RETURN FOR
> SELECT Application_ID() FROM SYSIBM.SYSDUMMY1;
> -- Cursor left open for client application
> OPEN cursor1;
> END P1[/color]

db2 => create function application_id() returns int return 1
DB20000I The SQL command completed successfully.
db2 => CREATE PROCEDURE foo \
db2 (cont.) => DYNAMIC RESULT SETS 1
\
db2 (cont.) => LANGUAGE SQL
\
db2 (cont.) => P1: BEGIN
\
db2 (cont.) => --declare the cursor
\
db2 (cont.) => DECLARE cursor1 CURSOR WITH RETURN FOR
\
db2 (cont.) => SELECT Application_ID() FROM SYSIBM.SYSDUMMY1;
\
db2 (cont.) => -- Cursor left open for client application
\
db2 (cont.) => OPEN cursor1;
\
db2 (cont.) => END P1
DB20000I The SQL command completed successfully.

Works for me. :)
[color=blue]
>And finally, the full trigger i'm trying to create in DB2 that i can't can't
>make work.[/color]

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

B.

Mar 7 '06 #2

---

P: n/a

ML

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

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

--
ML

Mar 7 '06 #3

---

P: n/a

Knut Stolze

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

Ian Boyd wrote:
[color=blue]
> i've been thrown into a pit with DB2 and have to start writing things such
> as tables, indexes, stored procedures, triggers, etc. The online reference
> is only so helpful. The two pdf manuals are only so helpful. Googling is
> only so helpful.
>
> So let's start with some simple SQL constructs, that i know so very well
> in SQL Server, that seem to be like pulling teeth in DB2.
>
> 1. Selecting a value
>
> SQL Server:
> SELECT 'Hello, world!'
> (1 row(s) affected)[/color]

This is not standardized SQL, which always requires a FROM clause in a
SELECT statement. So you can do this:

SELECT 'abc'
FROM sysibm.sysdummy1

have a look here for the table referenced: http://tinyurl.com/ohtzg

or use a table constructor:

VALUES 'abc'
[color=blue]
> 1. Declaring a variable
>
> SQL Server:
> DECLARE @SavedUserID int
> The command(s) completed successfully.[/color]

The '@' isn't SQL either.
[color=blue]
> DB2:
> DECLARE SavedUserID integer;
> Error: SQL0104N An unexpected token "integer" was found following "
> DECLARE
> SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
> NUMBER=1. SQLSTATE=42601
> (State:42601, Native Code: FFFFFF98)[/color]

Your problem here is probably that you did not explicitly specify a
statement terminator. So the end-of-line terminates your SQL statement,
and that leaves 'integer;' as a ...something... where DB2 rightfully
complains about. Try the -t option of the "db2" command line instead (or
search through the menues if you are using the Command Editor).
[color=blue]
> 2. Setting a variable
> SQL Server:
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> or
> SET @SavedUserID = (SELECT User_ID FROM Connection_Users WHERE spid =
> @@spid)
> or (depricated)
> SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@spid
>
> DB2:
> DECLARE ApplicationID varchar(128) --can't declare variables
> SET ApplicationID = Application_ID()
> or
> DECLARE ApplicationID varchar(128) --can't declare variables
> SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);[/color]

First question in a set-oriented language like SQL would be: what do you
want to do with the value that you really need procedural logic here.
[color=blue]
> 3. Returning a value
> SQL Server:
> SELECT @@spid AS ProcessID
> or
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> SELECT @ProcessID[/color]

Use this:

VALUES application_id()

and then fetch from the table created that way.
[color=blue]
> And finally, the full trigger i'm trying to create in DB2 that i can't
> can't make work.
>
> CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
> --"LI_DAILYLOGS" = "LogInsert_DailyLogs" 18 character limit in DB2
> AFTER INSERT
> ON SUPERDUDE.DAILY_LOGS
> REFERENCING NEW_TABLE AS INSERTED
> FOR EACH STATEMENT
> MODE DB2SQL
> BEGIN ATOMIC
> -- Load the saved UserID
> DECLARE SavedUserID integer;
>
> SELECT SavedUserID = User_ID
> FROM Connection_Users
> WHERE Application_ID = Application_ID();[/color]

SET SavedUserID = ( SELECT ... );
[color=blue]
> INSERT INTO Audit_Log(
> ChangeDate,
> RowID,
> ChangeType,
> -- Username, HostName, AppName,
> UserID,
> TableName,
> FieldName,
> TagID,
> Tag,
> OldValue,
> NewValue)
> SELECT
> getdate(),
> i.Daily_Log_ID,
> 'INSERTED',
> -- USER_NAME(), HOST_NAME(), APP_NAME(),
> SavedUserID,
> 'Daily_Logs', --TableName
> '', --FieldName
> NULL, --TagID
> i.Name, --Tag
> '', --OldValue
> '' --NewValue
> FROM Inserted i;
> END;
>
> Error: SQL0104N An unexpected token "integer" was found following "
> DECLARE SavedUserID". Expected tokens may include: "END-OF-STATEMENT".
> LINE NUMBER=10. SQLSTATE=42601
> (State:42601, Native Code: FFFFFF98)[/color]

Now that is really a problem with the statement terminator. DB2 takes the
first ';' as end of the statement so that you will have a syntax error
right away. That's why you see quite ofter the '@' being used as statement
terminator here.

I would write your trigger like this:

CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
--"LI_DAILYLOGS" = "LogInsert_DailyLogs" 18 character limit in DB2
AFTER INSERT
ON SUPERDUDE.DAILY_LOGS
REFERENCING NEW_TABLE AS INSERTED
FOR EACH STATEMENT
MODE DB2SQL

INSERT INTO audit_log(...)
SELECT getdate(),
i.Daily_Log_ID,
'INSERTED',
-- USER_NAME(), HOST_NAME(), APP_NAME(),
( SELECT User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID() )
'Daily_Logs', --TableName
'', --FieldName
NULL, --TagID
i.Name, --Tag
'', --OldValue
'' --NewValue
FROM Inserted i;

No variables needed in the first place and you give the DB2 optimizer a much
better chance to do a good job without the procedural logic.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 7 '06 #4

---

P: n/a

Ian Boyd

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

> 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

---

P: n/a

Ian Boyd

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

1. Selecting a value

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

3. Returning rows from a stored procedure[color=blue]
>DB2
> CREATE PROCEDURE foo
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
> P1: BEGIN
> --declare the cursor
> DECLARE cursor1 CURSOR WITH RETURN FOR
> SELECT Application_ID() FROM SYSIBM.SYSDUMMY1;
> -- Cursor left open for client application
> OPEN cursor1;
> END P1
> Works for me. :)[/color]

Any idea why it doesn't work for me? Any idea what the error message is
trying to say:
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"ROM SYSIBM.SYSDUMMY1". Expected tokens may include: "JOIN <joined_table>".
LINE NUMBER=7. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE
statement is blank or empty. SQLSTATE=42617
(State:42617, Native Code: FFFFFF3A)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END P1". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

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

No, that doesn't work:

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

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

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

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

i guess, which is preferred? Again, i can't test anything, because i cannot
declare variables.
[color=blue]
> INSERT INTO Audit_Log(
> ChangeDate,
> RowID,
> ChangeType,
> -- Username, HostName, AppName,
> UserID,
> TableName,
> FieldName,
> TagID,
> Tag,
> OldValue,
> NewValue)
> SELECT
> getdate(),
> i.Daily_Log_ID,
> 'INSERTED',
> -- USER_NAME(), HOST_NAME(), APP_NAME(),
> SavedUserID,
> 'Daily_Logs', --TableName
> '', --FieldName
> NULL, --TagID
> i.Name, --Tag
> '', --OldValue
> '' --NewValue
> FROM Inserted i;[/color]

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

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

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

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

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


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


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

DECLARE SomeNumber INT;

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

Mar 7 '06 #6

---

P: n/a

Knut Stolze

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

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

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

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

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

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

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

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

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

Just nest this into a single statement:

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

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

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

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

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

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

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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 7 '06 #7

---

P: n/a

Ian Boyd

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

> or use a table constructor:
VALUES 'abc'

Ahhh. "Table constructor." i get it now. It doesn't declare constants, it
creates an intermediate table. That will require some new thinking on what
problems i can solve with it.
[color=blue]
> DB2:
> DECLARE SavedUserID integer;
> Error: SQL0104N An unexpected token "integer" was found following "
> DECLARE
> SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
> NUMBER=1. SQLSTATE=42601
> (State:42601, Native Code: FFFFFF98)[/color]
[color=blue]
>Your problem here is probably that you did not explicitly specify a
>statement terminator. So the end-of-line terminates your SQL statement,
>and that leaves 'integer;' as a ...something... where DB2 rightfully
>complains about. Try the -t option of the "db2" command line instead (or
>search through the menues if you are using the Command Editor).[/color]

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

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

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



[color=blue]
> 2. Setting a variable
> SQL Server:
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> or
> SET @SavedUserID = (SELECT User_ID FROM Connection_Users WHERE spid =
> @@spid)
> or (depricated)
> SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@spid
>
> DB2:
> DECLARE ApplicationID varchar(128) --can't declare variables
> SET ApplicationID = Application_ID()
> or
> DECLARE ApplicationID varchar(128) --can't declare variables
> SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);[/color]
[color=blue]
> First question in a set-oriented language like SQL would be: what do you
> want to do with the value that you really need procedural logic here.[/color]

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

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

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

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

How do i declare, set and fetch local variables? What would be syntax to do
that?
[color=blue]
> And finally, the full trigger i'm trying to create in DB2 that i can't
> can't make work.
>[color=green]
>>
>> Error: SQL0104N An unexpected token "integer" was found following "
>> DECLARE SavedUserID". Expected tokens may include: "END-OF-STATEMENT".
>> LINE NUMBER=10. SQLSTATE=42601
>> (State:42601, Native Code: FFFFFF98)[/color]
>
>Now that is really a problem with the statement terminator. DB2 takes the
>first ';' as end of the statement so that you will have a syntax error
>right away. That's why you see quite ofter the '@' being used as statement
>terminator here.[/color]

Do you mean in general other people who write triggers?
Or is the symbol '@' (commerical at sign) not coming through the
news server correctly - i don't see any '@' as my statement terminator.
[color=blue]
> I would write your trigger like this:
> CREATE TRIGGER ...
> No variables needed in the first place and you give the DB2 optimizer a
> much
> better chance to do a good job without the procedural logic.[/color]

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

CREATE TRIGGER LogUpdate_Quotes ON Quotes
FOR UPDATE AS

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

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

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

...74 fields ommitted...

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

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

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


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

Mar 7 '06 #8

---

P: n/a

Knut Stolze

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

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

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

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

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

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

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

I was referring to the posts in this newsgroup.
[color=blue][color=green]
>> I would write your trigger like this:
>> CREATE TRIGGER ...
>> No variables needed in the first place and you give the DB2 optimizer a
>> much
>> better chance to do a good job without the procedural logic.[/color]
>
> How would you translate this trimmed down version of a trigger from SQL
> Server?[/color]

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

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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 7 '06 #9

---

P: n/a

Serge Rielau

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

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

---

P: n/a

Serge Rielau

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

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

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

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

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

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


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 7 '06 #11

---

P: n/a

Ian Boyd

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

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

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

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

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

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

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

Excellent. Thank you.


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

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

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

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

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

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

Mar 7 '06 #12

---

P: n/a

Ian Boyd

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

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

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

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

UPDATE MyTable
SET AMoneyField = AMoneyField * 1.10

Wouldn't DB2 perfer when doing the trigger:

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

rather than doing

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

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

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

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

....

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

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

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

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

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

Mar 7 '06 #13

---

P: n/a

Serge Rielau

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

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

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

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

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

Mar 7 '06 #14

---

P: n/a

Serge Rielau

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

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

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

Mar 7 '06 #15

---

P: n/a

Ian Boyd

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Mar 7 '06 #16

---

P: n/a

Ian Boyd

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

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

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

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

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

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

Connection.Execute(myQuery);

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

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

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

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

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

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

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



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

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


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

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

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

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

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

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

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

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

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

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

DECLARE myValue int;

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

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



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

Mar 7 '06 #17

---

P: n/a

Dave Hughes

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

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

---

P: n/a

Stefan Momma

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

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

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

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

hope this helps,

-- stefan

Mar 8 '06 #19

---

P: n/a

Serge Rielau

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

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

---

P: n/a

Serge Rielau

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

Stefan Momma wrote:[color=blue]
> Ian Boyd wrote:
>[color=green]
>> i'm trying to get a super-primer on DB2-SQL, so i can get something up
>> and running.[/color]
>
> My favourite reference for DB2 SQL is Graeme Birchall's
> DB2 SQL Cookbook, which is available from this website:
>
> http://mysite.verizon.net/Graeme_Birchall/id1.html
>
> hope this helps,
>
> -- stefan[/color]
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

---

P: n/a

Knut Stolze

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

Ian Boyd wrote:
[color=blue][color=green]
>> There are no "fields" in SQL - just rows, columns and values.[/color]
> You channeling Celko? :)[/color]

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.
[color=blue]
> 3. Returning a value[color=green][color=darkred]
>>> 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:[/color]
>> How so?[/color]
>
> Because rather than using VALUES:[color=green]
>> SET ApplicationID = VALUES Application_ID();[/color]
> i don't use VALUES:[color=green]
>> SET ApplicationID = Application_ID();[/color][/color]

That doesn't make VALUES a bad thing. It is just not necessary here.
[color=blue]
> 5. Setting a field (a.k.a column) to NULL
>
> SQL Server[color=green]
>> UPDATE MyTable SET SomeField = NULL
>> WHERE SomeOtherField IN (SELECT KeyField FROM MyTable2 WHERE Field = 3)[/color][/color]

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

This works too.
[color=blue]
> or
>[color=green]
>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
>> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system[/color]
>
> In my variant of the 3rd case in DB2, it complains that "NULL is not valid
> in the context where it is used."[/color]

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

---

P: n/a

Knut Stolze

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

Ian Boyd wrote:
[color=blue]
> 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.[/color]

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.
[color=blue]
> Like i said before, i'll have to figure out where VALUES would be useful
> to me.
>[color=green]
>> This T-SQL: SELECT @@spid AS ProcessID
>> is NOT column aliasing.[/color]
>
> If i run this query, i get a record set with one row and one column. And
> that column has no name. If i want to give that column a name (or a
> different name) i have to use:
>[color=green]
>> SELECT @@spid AS ProcessID[/color]
>
> So the column gets renamed from "" to "ProcessID."[/color]

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

---

P: n/a

Ian Boyd

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

> $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1"[color=blue]
>
> 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.[/color]

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

---

P: n/a

Ian Boyd

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

"Serge Rielau" <[e-mail]> wrote in message
news:[e-mail]...[color=blue]
> Ian Boyd wrote:[color=green][color=darkred]
>>> There are no "fields" in SQL - just rows, columns and values.[/color]
>> You channeling Celko? :)[/color][/color]
[color=blue]
> *chuckle* I had the same thought.
> Seriously though it doesn't hurt to use the correct language.
> Just like it doesn't hurt to speak proper English outside the pub ;-)[/color]

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>
[color=blue][color=green][color=darkred]
>>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
>>> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system[/color]
>>
>> In my variant of the 3rd case in DB2, it complains that "NULL is not
>> valid
>> in the context where it is used."[/color]
> DB2 uses strong typing. An untyped NULL (or ?) is only allowed in specific
> places where DB2 cann immediatly deduce the datatype.
> That would be UPDATE SET, SET statement and INSERT VALUES.
> In all other cases CAST(NULL AS <type>) will do the job.
> That's the way the standard is defined. No technical reason really.[/color]

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

---

P: n/a

Serge Rielau

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

Ian Boyd wrote:[color=blue][color=green]
>> $ 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.[/color]
>
> 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.
>
>[/color]
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

---

P: n/a

Serge Rielau

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

Ian Boyd wrote:[color=blue]
> "Serge Rielau" <[e-mail]> wrote in message
> news:[e-mail]...[color=green]
>> Ian Boyd wrote:[color=darkred]
>>>> There are no "fields" in SQL - just rows, columns and values.
>>> You channeling Celko? :)[/color][/color]
>[color=green]
>> *chuckle* I had the same thought.
>> Seriously though it doesn't hurt to use the correct language.
>> Just like it doesn't hurt to speak proper English outside the pub ;-)[/color]
>
> Warning. Celko bait ahead:
> <CelkoBait>[/color]
<snip>[color=blue]
> </CelkoBait>[/color]

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 ;-)
[color=blue][color=green][color=darkred]
>>>> 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."[/color]
>> DB2 uses strong typing. An untyped NULL (or ?) is only allowed in specific
>> places where DB2 cann immediatly deduce the datatype.
>> That would be UPDATE SET, SET statement and INSERT VALUES.
>> In all other cases CAST(NULL AS <type>) will do the job.
>> That's the way the standard is defined. No technical reason really.[/color]
>
> 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.[/color]
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

---

P: n/a

Ian Boyd

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

> Procedural statements are not supported as independent statements by DB2.[color=blue]
>
> 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[/color]

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

---

P: n/a

Brian Tkatch

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

>But i convinced myself that such a scheme is just too complicated to be true, and i assumed that i[color=blue]
>had some fundamental misunderstanding about what is going on.[/color]

Welcome to the non-MS/Windows world. Where you are expected to
understand what your are doing. :)
[color=blue]
>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[/color]

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

---

P: n/a

Serge Rielau

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

Ian Boyd wrote:[color=blue][color=green]
>> 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[/color]
>
> 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.[/color]
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.
[color=blue]
> 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?[/color]
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

---

P: n/a

Ian Boyd

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

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

---

P: n/a

Ian Boyd

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

> Welcome to the non-MS/Windows world. Where you are expected to[color=blue]
> understand what your are doing. :)[/color]

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

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

---

P: n/a

Ian Boyd

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

>select 1+2+3, 4+5+6 from sysibm.sysdummy1
1 2
----------- -----------
6 15
[color=blue]
> SELECT 3.14159, 1+2+3, 4+5+6[/color]

1 2 3
-------- -------- --------
3.14159 6 15
[color=blue]
> You two got yor wires crossed.. Knut is talking about correlation names
> (above the ----- line).
> You are now talking about result types.[/color]

?

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

---

P: n/a

Ian Boyd

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

> This is the root for SQL Procedure logic:[color=blue]
> 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[/color]

i get it. Some statements are only valid inside other statements. If of
course be convient if they would just work.
[color=blue]
> This statement can only be embedded in an SQL procedure. It is not an
> executable statement and cannot be dynamically prepared."[/color]

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

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

Which is about a dry a read as ISO/IEC 9075 is, albeit more helpful.
[color=blue]
> 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.[/color]

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

---

P: n/a

Dave Hughes

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

Ian Boyd wrote:
[color=blue]
> 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.[/color]

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

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


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

---

P: n/a

Dave Hughes

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

Knut Stolze wrote:

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

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

---

P: n/a

Ian Boyd

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

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

---

P: n/a

Serge Rielau

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

Ian Boyd wrote:[color=blue]
> 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*
>
>[/color]
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

---

P: n/a

Brian Tkatch

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

>They have databases on computers these days. i know what i'm doing, and[color=blue]
>computers are powerful enough these days to know as well; or at least
>proceed with the only possible course of action.[/color]

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.
[color=blue]
>That explains why the reference doesn't include some statements, their not
>the right "kind" of statements.[/color]

Close. It's because, they are not statements.
[color=blue]
>i see the majority of the reference is in a section called "Statements". i
>don't see a corresponding section of "control statements"[/color]

In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL
control statements".
[color=blue]
>, nor is the keyword DECLARE in the index.[/color]

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

---

P: n/a

Dave Hughes

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

Serge Rielau wrote:
[color=blue]
> Ian Boyd wrote:[color=green]
> > 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*
> >
> >[/color]
> Ian, what tool are you using. This works for me using CLP
> Please clarify your environment.[/color]

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.

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

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.
[color=blue]
> PS: I find this thread quite interesting actually.[/color]

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

---

P: n/a

Ian Boyd

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

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

---

P: n/a

Dave Hughes

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

Brian Tkatch wrote:

[snip][color=blue]
> The day DBs do things for me, is the day i stop doing databases.[/color]

You hand-crank the execution plan for all your queries? Wow ...
hardcore man! (joking :-)
[color=blue]
> I actually despise Windows mostly because of these assumptions. I love
> DBs, because they are so simple, and make no assumptions.[/color]

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

---

P: n/a

Serge Rielau

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

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

---

P: n/a

Dave Hughes

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

Ian Boyd wrote:
[color=blue]
> 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 ;[/color]

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

---

P: n/a

Serge Rielau

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

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

---

P: n/a

Ian Boyd

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

> Ian, what tool are you using. This works for me using CLP[color=blue]
> Please clarify your environment.[/color]

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>

[color=blue]
> PS: I find this thread quite interesting actually.[/color]
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

---

P: n/a

Dave Hughes

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

Serge Rielau wrote:
[color=blue]
> Working as documented:
> http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2.
> udb.doc/admin/r0000888.htm
>
> DEFAULT ...
>[/color]
[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

---

P: n/a

Ian Boyd

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

> The day DBs do things for me, is the day i stop doing databases. I[color=blue]
> actually despise Windows mostly because of these assumptions. I love
> DBs, because they are so simple, and make no assumptions.[/color]

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>



[color=blue][color=green]
>>That explains why the reference doesn't include some statements, their not
>>the right "kind" of statements.[/color]
>
> Close. It's because, they are not statements.[/color]

i guess this is where some help with examples would be extraordinarily
useful.
[color=blue][color=green]
>>i see the majority of the reference is in a section called "Statements". i
>>don't see a corresponding section of "control statements"[/color]
>
> In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL
> control statements".[/color]

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

[color=blue][color=green]
>>, nor is the keyword DECLARE in the index.[/color]
>
> It is absolutely in the index. Though, it is not a bookmark.
>
> It is in Chapter 2.=>Compound Statement (Procedure) under
> "SQL-variable-declaration".[/color]
[color=blue]
> A search of the index (which is a bookmark) found it for me pretty
> easily.[/color]


i gotta find this book, web-site, pdf, help file, or eBook you got.

Mar 8 '06 #48

---

P: n/a

Ian Boyd

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

> Working as documented:[color=blue]
> http://publib.boulder.ibm.com/infoce...n/r0000888.htm
>[/color]

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

---

P: n/a

Ian Boyd

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

> Nope, in this case I think it's the thing I mentioned in my other post:[color=blue]
> comments can only appear as the first non-whitespace characters in a
> line.[/color]

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

---

P: n/a

Dave Hughes

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

Ian Boyd wrote:
[color=blue][color=green]
> > 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.[/color]
>
> 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[/color]

In that case it definitely sounds like the problem Serge mentioned:
that the client (or something somewhere) is stripping out line breaks.
I'm not sure how one could confirm this. I guess you could stick a
packet sniffer like Ethereal between the client and server, grab the
query going to the server and check it for line breaks, but that seems
like overkill (and even then it wouldn't tell you *what* was stripping
the line breaks, just that it was happening).

In this case, unfortunately, the only solution is to chop out the
comments altogether (shame DB2 doesn't support C-style /*..*/ comments
in which line break chopping doesn't result in ambiguity).


Dave.

--

Mar 8 '06 #51

---

P: n/a

Ian Boyd

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

> In that case it definitely sounds like the problem Serge mentioned:[color=blue]
> that the client (or something somewhere) is stripping out line breaks.
> I'm not sure how one could confirm this. I guess you could stick a
> packet sniffer like Ethereal between the client and server, grab the
> query going to the server and check it for line breaks, but that seems
> like overkill (and even then it wouldn't tell you *what* was stripping
> the line breaks, just that it was happening).[/color]

Good idea. You are correct sir.
0x0000 00 53 D0 51 00 01 00 4D-20 0A 00 44 21 13 4F 4C .SÐQ...M ..D!.OL
0x0010 47 43 53 55 52 56 20 20-20 20 20 20 20 20 20 20 GCSURV
0x0020 4E 55 4C 4C 49 44 20 20-20 20 20 20 20 20 20 20 NULLID
0x0030 20 20 53 59 53 53 48 32-30 30 20 20 20 20 20 20 SYSSH200
0x0040 20 20 20 20 53 59 53 4C-56 4C 30 31 00 41 00 05 SYSLVL01.A..
0x0050 21 05 F1 02 14 D0 43 00-01 02 0E 24 14 00 00 00 !.ñ..ÐC....$....
0x0060 02 04 49 4E 53 45 52 54-20 49 4E 54 4F 20 44 61 ..INSERT INTO Da
0x0070 69 6C 79 5F 4C 6F 67 73-20 28 20 20 20 20 20 20 ily_Logs (
0x0080 44 61 69 6C 79 5F 4C 6F-67 5F 4E 75 6D 62 65 72 Daily_Log_Number

i'm sure either the ODBC driver, OLEDB provider or DB2 itself must have an
option to preserve linebreaks somewhere, but it's not really feasable to go
looking for it.

Mar 8 '06 #52

---

P: n/a

Ian Boyd

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

The progress so far. Note, this is mainly for me, and my coworkers who want
help understanding the limitations of DB2. This post will be google
archived, and available as a future reference.

DO NOT RESPOND

Here is the syntax i've divined for creating a table in IBM DB2-SQL:

CREATE TABLE "SUPERDUDE"."AUDIT_LOG" (
"AUDITLOGID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +0
INCREMENT BY +1
MINVALUE +0
MAXVALUE [phone]
NO CYCLE
NO CACHE
NO ORDER ) ,
"CHANGEDATE" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
"ROWID" INTEGER NOT NULL ,
"CHANGETYPE" VARCHAR(10) NOT NULL ,
"TABLENAME" VARCHAR(128) NOT NULL ,
"FIELDNAME" VARCHAR(128) NOT NULL ,
"OLDVALUE" LONG VARCHAR ,
"NEWVALUE" LONG VARCHAR ,
"USERNAME" VARCHAR(128) ,
"HOSTNAME" VARCHAR(50) ,
"APPNAME" VARCHAR(255) ,
"USERID" INTEGER ,
"TAGID" INTEGER ,
"TAG" VARCHAR(1000) )
IN "USERSPACE1" ;

Notes:
1. Username, Hostname an Appname field would like to have defaults of
SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME
respectivly, but those special registers are not supposed as column default
values. Whereas CURRENT TIMESTAMP is an example of a special register that
is supported as a column default value.

And my trigger code is:

CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
AFTER INSERT
ON SUPERDUDE.DAILY_LOGS
REFERENCING NEW_TABLE AS INSERTED
FOR EACH STATEMENT
BEGIN ATOMIC
-- Load the saved UserID
DECLARE SavedUserID INTEGER;

SET SavedUserID = (SELECT User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID());

INSERT INTO Audit_Log(
RowID,
ChangeType,
UserID,
TableName,
FieldName,
Username,
Hostname,
Appname,
TagID,
Tag,
OldValue,
NewValue)
SELECT
i.Daily_Log_ID,
'INSERTED',
SavedUserID,
'Daily_Logs',
'',
SESSION_USER,
CURRENT CLIENT_WRKSTNNAME,
CURRENT CLIENT_APPLNAME,
i.Daily_Log_ID,
i.Daily_Log_Number,
CAST(NULL AS varchar(1)),
CAST(NULL AS varchar(1))
FROM Inserted i;
END

NOTES:
2. i had to specify SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT
CLIENT_APPLNAME here because DB2 does not support these specific system
registers as column default values.

2. DB2 does not support comments inside in insert statement (e.g. to
document what each field is). Comments are not supported either on the end
of a line, or on it's own line.

3. DB2 cannot implicitly cast a NULL to any data type. The NULL values
specified for columns OldValue and NewValue, must therefore be explicitly
converted to the target column's data type, or to some data type that can be
implicitly to the target column's data type. In my case here, i use a dummy
cast of CAST to varchar(1).


Here is my sample insert into a table getting logged:

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-412',
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))
);

NOTES:
4. You cannot have comments inside the select; either on the end of each
line, or on its own line.
5. DB2 cannot cast NULL to any data type. You must manually cast any NULLs
to a data type that DB2 can implicitly cast to the data type of the target
column. In this case, i have to manually cast a NULL to an integer field,
and a varchar field.

ADDITIONAL NOTES:

6. Many other SQL constructs are not understood by DB2.

SELECT 'Hello, world!';

is invalid because every select in DB2 must be from a table. DB2 provides a
dummy table for this purpose

SELECT 'Hello, world!' FROM sysibm.sysdummy1;

This system table contains only 1 row and only 1 column.

7. Every DB2-SQL Statement must end with a semi-colon (;)

8. You cannot give DB2 some arbitrary SQL to run. You are only allowed to
give DB2 one "statement" at a time. If you try to give it more than one
statement, it will choke. Examples of statements include CREATE TABLE,
CREATE TRIGGER, CREATE PROCEDURE, DECLARE CURSOR, CALL (which executes a
stored procedure).

The major cavaet with this limitation is that something like the following
is invalid:

CREATE TABLE Users (
UserID int,
Username varchar(50);
INSERT INTO Users (UserID, Username) VALUES (1, 'Ian');
INSERT INTO Users (UserID, Username) VALUES (2, 'Brian');
INSERT INTO Users (UserID, Username) VALUES (3, 'Knut');
INSERT INTO Users (UserID, Username) VALUES (4, 'Serge');
SELECT * FROM Users
WHERE Username = 'Knut';
DROP TABLE Users;

This is because you tried to execute more than one statment at a time. You
need to break it up and run only one statment at a time.

9. Quite a few SQL constructs are not defined by DB2 as "statments", so you
cannot run them. e.g.

DECLARE MyUsername varchar(50);

is invalid because DECLARE is not a statement. So you would also be unable
to accomplish the following:

DECLARE MyUsername varchar(50);
SET MyUsername = 'Hello, world!';
SELECT MyUsername AS MyUsername FROM sysibm.sysdummy1;

because DECLARE and SET are not statements. The workaround for this in DB2
is to use another generic statement wrapper:

BEGIN ATOMIC
DECLARE MyUsername varchar(50);
SET MyUsername = 'Hello, world!';
SELECT MyUsername AS MyUsername FROM sysibm.sysdummy1;
END;

Like CREATE TABLE, CREATE PROCEDURE, etc, 'BEGIN ATOMIC' is a valid
"statement", and can be used to enclose non-statements.

10. Stored procedures cannot issue select statements. For example, the
following is invalid:

CREATE PROCEDURE doStuff
BEGIN
SELECT * FROM Users;
END;

Instead, the stored procedure must declare a cursor, open it, and leave the
cursor declared and open when leaving the stored procedure:

CREATE PROCEDURE doStuff
BEGIN
DECLARE abc CURSOR WITH RETURN FOR
SELECT * FROM Users
OPEN abc
END;

Mar 8 '06 #53

---

P: n/a

Serge Rielau

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

Dave Hughes wrote:[color=blue]
> Serge Rielau wrote:
>[color=green]
>> Working as documented:
>> http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2.
>> udb.doc/admin/r0000888.htm
>>
>> DEFAULT ...
>>[/color]
> [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)[/color]
Support or non support more often than not has one reason: resources.
Quite likely when these registers were added DEFAULT was not part of the
requirement. Given that they have been present for a while no one ever
complained (AFAIK) this might well have been the right decision at the time.
Also note that BEFORE triggers support ALL registers and it is highly
unlikely anyone would want to use default values for those on LOAD.
So, mostly we are talking orthogonality here. A desirable property, but
hard to measure in $$.

Cheers
Serge

PS: Name any software product (which I know of course) and I can rattle
down a set of odd limitations.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 8 '06 #54

---

P: n/a

Serge Rielau

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

Dave Hughes wrote:[color=blue]
> In this case, unfortunately, the only solution is to chop out the
> comments altogether (shame DB2 doesn't support C-style /*..*/ comments
> in which line break chopping doesn't result in ambiguity).[/color]
db2 => select /* hello */ 1 from sysibm.sysdummy1;

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

1 record(s) selected.

DB2 V8.2.2 (FP9)

Since Ian has all those drivers maybe he can try it.

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