How to do...well...anything...in DB2 SQL
Ian Boyd
as tables, indexes, stored procedures, triggers, etc. The online reference
is only so helpful. The two pdf manuals are only so helpful. Googling is
only so helpful.
So let's start with some simple SQL constructs, that i know so very well in
SQL Server, that seem to be like pulling teeth in DB2.
1. Selecting a value
SQL Server:
SELECT 'Hello, world!'
(1 row(s) affected)
DB2:
SELECT 'Hello, world!'
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"LECT 'Hello, world!'". Expected tokens may include: "<table_expr>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
SELECT 'Hello, world!' FROM SYSIBM.SysDummy1
1 Row(s) affected
Is there a SysDummy2? 3? Why?
1. Declaring a variable
SQL Server:
DECLARE @SavedUserID int
The command(s) completed successfully.
DB2:
DECLARE SavedUserID integer;
Error: SQL0104N An unexpected token "integer" was found following " DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=1. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
2. Setting a variable
SQL Server:
DECLARE @ProcessID int
SET @ProcessID = @spid
or
SET @SavedUserID = (SELECT User_ID FROM Connection_Users WHERE spid =
@@spid)
or (depricated)
SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@spid
DB2:
DECLARE ApplicationID varchar(128) --can't declare variables
SET ApplicationID = Application_ID()
or
DECLARE ApplicationID varchar(128) --can't declare variables
SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);
3. Returning a value
SQL Server:
SELECT @@spid AS ProcessID
or
DECLARE @ProcessID int
SET @ProcessID = @spid
SELECT @ProcessID
DB2
SELECT Application_ID()
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"ect application_ID()". Expected tokens may include: "<table_expr>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
or
SELECT Application_ID() FROM IBM.SysDummy1 --SysDummy2
or
DECLARE ApplicationID varchar(128) --can't declare variables
SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);
SELECT ApplicationID
3. Returning rows from a stored procedure
SQL Server
CREATE PROCEDURE foo AS
SELECT @@spid AS ProcessID
DB2
CREATE PROCEDURE foo
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
--declare the cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT Application_ID() FROM SYSIBM.SYSDUMMY1;
-- Cursor left open for client application
OPEN cursor1;
END P1
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found
following "ROM SYSIBM.SYSDUMMY1". Expected tokens may include: "JOIN
<joined_table>". LINE NUMBER=7. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE
statement is blank or empty. SQLSTATE=42617
(State:42617, Native Code: FFFFFF3A)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found
following "END P1". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
And finally, the full trigger i'm trying to create in DB2 that i can't can't
make work.
CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
--"LI_DAILYLOGS" = "LogInsert_DailyLogs" 18 character limit in DB2
AFTER INSERT
ON SUPERDUDE.DAILY_LOGS
REFERENCING NEW_TABLE AS INSERTED
FOR EACH STATEMENT
MODE DB2SQL
BEGIN ATOMIC
-- Load the saved UserID
DECLARE SavedUserID integer;
SELECT SavedUserID = User_ID
FROM Connection_Users
WHERE Application_ID = Application_ID();
INSERT INTO Audit_Log(
ChangeDate,
RowID,
ChangeType,
-- Username, HostName, AppName,
UserID,
TableName,
FieldName,
TagID,
Tag,
OldValue,
NewValue)
SELECT
getdate(),
i.Daily_Log_ID,
'INSERTED',
-- USER_NAME(), HOST_NAME(), APP_NAME(),
SavedUserID,
'Daily_Logs', --TableName
'', --FieldName
NULL, --TagID
i.Name, --Tag
'', --OldValue
'' --NewValue
FROM Inserted i;
END;
Error: SQL0104N An unexpected token "integer" was found following " DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "=" was found following "SELECT
SavedUserID ". Expected tokens may include: "<space>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0204N "SUPERDUDE.INSERTED" is an undefined name. SQLSTATE=42704
(State:42704, Native Code: FFFFFF34)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Brian Tkatch
>SQL Server, that seem to be like pulling teeth in DB2.
Though i agree that DB2 can be tedious and confusing, note that SQL
Server is super easy, and it pretty much breaks the standard to do it.
So, give db2 a shot. :) As long as you come with the attitude that it
can be done, you just don't know how, the users of this group are very
helpful.
>1. Selecting a value
SQL Server:
SELECT 'Hello, world!'
This is wholly incorrect. It is not SQL whatsoever. It is a convenience
added by Sybase/SQL Server.
In the DB2 world, you use: VALUES 'Hello World'
> SELECT 'Hello, world!' FROM SYSIBM.SysDummy1
> 1 Row(s) affected
>
>
>Is there a SysDummy2? 3? Why?
Compatability with other systems, that do not use a special statement,
and require *all* statments to include a TABLE reference. Oracle
supplies a one-record TABLE called Dual. Other systems use other names.
>1. Declaring a variable
>
>SQL Server:
> DECLARE @SavedUserID int
> The command(s) completed successfully.
Drop the useless @ symbol, and end all statement with a semi-colon.
Also, there is no implicit block of code, so you must start your own.
BEGIN
DECLARE SaverUserID INT;
END
>3. Returning a value
>SQL Server:
> SELECT @@spid AS ProcessID
> or
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> SELECT @ProcessID
Again, use VALUES.
>3. Returning rows from a stored procedure
>SQL Server
> CREATE PROCEDURE foo AS
> SELECT @@spid AS ProcessID
>DB2
> CREATE PROCEDURE foo
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
> P1: BEGIN
> --declare the cursor
> DECLARE cursor1 CURSOR WITH RETURN FOR
> SELECT Application_ID() FROM SYSIBM.SYSDUMMY1;
> -- Cursor left open for client application
> OPEN cursor1;
> END P1
db2 => create function application_id() returns int return 1
DB20000I The SQL command completed successfully.
db2 => CREATE PROCEDURE foo \
db2 (cont.) => DYNAMIC RESULT SETS 1
\
db2 (cont.) => LANGUAGE SQL
\
db2 (cont.) => P1: BEGIN
\
db2 (cont.) => --declare the cursor
\
db2 (cont.) => DECLARE cursor1 CURSOR WITH RETURN FOR
\
db2 (cont.) => SELECT Application_ID() FROM SYSIBM.SYSDUMMY1;
\
db2 (cont.) => -- Cursor left open for client application
\
db2 (cont.) => OPEN cursor1;
\
db2 (cont.) => END P1
DB20000I The SQL command completed successfully.
Works for me. :)
>And finally, the full trigger i'm trying to create in DB2 that i can't can't
>make work.
I have little experience with TRIGGERs in DB2. I'll leave that to
someone else. :)
B.
ML
--
ML
Knut Stolze
> i've been thrown into a pit with DB2 and have to start writing things such
> as tables, indexes, stored procedures, triggers, etc. The online reference
> is only so helpful. The two pdf manuals are only so helpful. Googling is
> only so helpful.
>
> So let's start with some simple SQL constructs, that i know so very well
> in SQL Server, that seem to be like pulling teeth in DB2.
>
> 1. Selecting a value
>
> SQL Server:
> SELECT 'Hello, world!'
> (1 row(s) affected)
This is not standardized SQL, which always requires a FROM clause in a
SELECT statement. So you can do this:
SELECT 'abc'
FROM sysibm.sysdummy1
have a look here for the table referenced: http://tinyurl.com/ohtzg
or use a table constructor:
VALUES 'abc'
> 1. Declaring a variable
>
> SQL Server:
> DECLARE @SavedUserID int
> The command(s) completed successfully.
The '@' isn't SQL either.
> DB2:
> DECLARE SavedUserID integer;
> Error: SQL0104N An unexpected token "integer" was found following "
> DECLARE
> SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
> NUMBER=1. SQLSTATE=42601
> (State:42601, Native Code: FFFFFF98)
Your problem here is probably that you did not explicitly specify a
statement terminator. So the end-of-line terminates your SQL statement,
and that leaves 'integer;' as a ...something... where DB2 rightfully
complains about. Try the -t option of the "db2" command line instead (or
search through the menues if you are using the Command Editor).
> 2. Setting a variable
> SQL Server:
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> or
> SET @SavedUserID = (SELECT User_ID FROM Connection_Users WHERE spid =
> @@spid)
> or (depricated)
> SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@spid
>
> DB2:
> DECLARE ApplicationID varchar(128) --can't declare variables
> SET ApplicationID = Application_ID()
> or
> DECLARE ApplicationID varchar(128) --can't declare variables
> SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);
First question in a set-oriented language like SQL would be: what do you
want to do with the value that you really need procedural logic here.
> 3. Returning a value
> SQL Server:
> SELECT @@spid AS ProcessID
> or
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> SELECT @ProcessID
Use this:
VALUES application_id()
and then fetch from the table created that way.
> And finally, the full trigger i'm trying to create in DB2 that i can't
> can't make work.
>
> CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
> --"LI_DAILYLOGS" = "LogInsert_DailyLogs" 18 character limit in DB2
> AFTER INSERT
> ON SUPERDUDE.DAILY_LOGS
> REFERENCING NEW_TABLE AS INSERTED
> FOR EACH STATEMENT
> MODE DB2SQL
> BEGIN ATOMIC
> -- Load the saved UserID
> DECLARE SavedUserID integer;
>
> SELECT SavedUserID = User_ID
> FROM Connection_Users
> WHERE Application_ID = Application_ID();
SET SavedUserID = ( SELECT ... );
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
Ian Boyd
SQL Server:
> SELECT 'Hello, world!'
> In the DB2 world, you use:
> VALUES 'Hello World'
How about aliasing field names?
> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage
i try:
> VALUES 'Hello, world!' AS MyLovelyWelcomeMes
Error: SQL0104N An unexpected token "AS" was found following "LUES 'Hello,
world!'". Expected tokens may include: "INTO". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
i assume that i should be using a SELECT, and not VALUES
> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage FROM SYSIBM.SysDummy1
2. Declaring Variables
> BEGIN
> DECLARE SaverUserID INT;
> END
Doesn't work for me:
Error: SQL0104N An unexpected token "DECLARE" was found following "BEGIN
". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
i throught that the field type in DB2-SQL was INTEGER. No?
Really scary when i can't get 3 lines to work. So i try:
> DECLARE SaverUserID INT;
And now i can't even get one line to work. (Yes, frustrating)
Error: SQL0104N An unexpected token "INT" was found following "DECLARE
SaverUserID". Expected tokens may include: "END-OF-STATEMENT".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
3. Returning a value
SQL Server:
>> SELECT @@spid AS ProcessID
>> or
>> DECLARE @ProcessID int
>> SET @ProcessID = @spid
>> SELECT @ProcessID
>
> Again, use VALUES.
Again, i assume that VALUES is a bad thing:
> VALUES Application_ID() AS ProcessID
Error: SQL0104N An unexpected token "AS" was found following "UES
Application_ID()". Expected tokens may include: "->". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
And that i really should be using SELECT
> SELECT Application_ID() AS ApplicationID FROM SYSIBM.SysDummy1
which does work, but i'd really like to know how to declare variables, set
variables and return variables. So i try:
>BEGIN
> DECLARE ApplicationID varchar(128);
>END
>BEGIN
> SET ApplicationID = VALUES Application_ID();
>END
>BEGIN
> SELECT ApplicationID AS ApplicationID FROM SYSIBM.SysDummy1;
>END
Error: SQL0104N An unexpected token "BEGIN DECLARE ApplicationID varchar"
was found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "END" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<update>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "END BEGIN" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
So i'm pretty much stuck. Three lines, and i can't any one work. Note the
heavy use of BEGIN/END because "there is no implicit block of code, so you
must start your own." Surely that can't be the syntax i have to use. i would
have thought that the following would have been enough, but i can't really
tell since i get the errors:
>BEGIN
> DECLARE ApplicationID varchar(128);
> SET ApplicationID = VALUES Application_ID();
> SELECT ApplicationID AS ApplicationID
> FROM SYSIBM.SysDummy1;
>END
Error: SQL0104N An unexpected token "BEGIN DECLARE ApplicationID varchar"
was found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "SET ApplicationID =" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0206N "APPLICATIONID" is not valid in the context where it is
used. SQLSTATE=42703
(State:S0022, Native Code: FFFFFF32)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
3. Returning rows from a stored procedure
>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. :)
Any idea why it doesn't work for me? Any idea what the error message is
trying to say:
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"ROM SYSIBM.SYSDUMMY1". Expected tokens may include: "JOIN <joined_table>".
LINE NUMBER=7. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE
statement is blank or empty. SQLSTATE=42617
(State:42617, Native Code: FFFFFF3A)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END P1". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Complaining about me ending my statement after the SELECT, that that it is
expecting a JOIN. Should be joining to something. Do i need to also join to
a dummy table? e.g.
>CREATE PROCEDURE foo
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
>P1: BEGIN
> DECLARE cursor1 CURSOR WITH RETURN FOR
> SELECT Application_ID() FROM SYSIBM.SYSDUMMY1
> FULL OUTER JOIN SYSIBM.SYSDUMMY1 ON (1=1);
> OPEN cursor1;
>END P1
No, that doesn't work:
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"IBM.SYSDUMMY1 ON (1=1)". Expected tokens may include: "<psm_semicolon>".
LINE NUMBER=7. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"OPEN cursor1". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END P1". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
>>And finally, the full trigger i'm trying to create in DB2 that i can't
>>make work.
>I have little experience with TRIGGERs in DB2. I'll leave that to someone
>else. :)
That's fine, pretend it's not a trigger. Pretend it's just a regular query:
>-- Load the saved UserID
>DECLARE SavedUserID integer;
i can't get any variant of any variable declaration to work. Any ideas?
> SELECT SavedUserID = User_ID
> FROM Connection_Users
> WHERE Application_ID = Application_ID();
Is this valid DB2-SQL syntax to put a value into a variable? i can't really
test it, since i cannot declare variables. Or is it invalid syntax, and i
should be using:
> SET SavedUserID = SELECT User_ID
> FROM Connection_Users
> WHERE Application_ID = Application_ID();
or should i be using VALUES along the lines of:
> SET SavedUserID = VALUES Application_ID;
i guess, which is preferred? Again, i can't test anything, because i cannot
declare variables.
(NOTE: Anyone who knows T-SQL will recognize getdate(), USER_NAME(),
HOST_NAME(), APP_NAME(). i assume that DB2-SQL has some built-in function to
get the current date/time. i also assume DB2-SQL has no built-in function to
get the current Username, MachineName or AppName)
Aside from the SQL syntax stuck in there (because i can't get enough far
enough to debug it), is that a valid syntax for doing an insert into a table
in DB2 when not using VALUES?
By values i mean:
INSERT INTO foo (Field1, Field2, ..., FieldN)
VALUES (Value1, Value2, ..., ValueN);
Put it another way, is this a valid syntax in DB2-SQL:
> INSERT INTO foo (Field1, Field2, ..., FieldN)
> SELECT Value1, Value2, ..., ValueN
> FROM MyTable
> WHERE ...
Finally, is that the valid way to alias tables in DB2-SQL?
> FROM Inserted i
will that work, or do i have to do something like:
> FROM Inserted AS i
or is it some other syntax, maybe more like Java, which IBM seems to live:
> FROM (i)Inserted
Finally, does DB2 support derived tables
> SELECT myo.*, MyDerivedTable.*
> FROM MyTableOne mto
> INNER JOIN (SELECT * FROM MyTableTwo mtt
> WHERE mtt.Field4 = 'Testing') MyDerivedTable
> mto.SomeJoinField = MyDerivedTable.AnotherJoinField
But originally, and most importantly, what's wrong with:
DECLARE SomeNumber INT;
i think if i can get that working, i'll knock back a bottle of scotch and
call today very productive. If i can get a declare working, i'll have
written one line of DB2-SQL in 2 weeks of work.
Ian Boyd
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).
Knut Stolze
> 1. Selecting a value
>
> SQL Server:
>> SELECT 'Hello, world!'
>> In the DB2 world, you use:
>> VALUES 'Hello World'
>
> How about aliasing field names?
There are no "fields" in SQL - just rows, columns and values.
>> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage
The question is what the column name will be good for. If you get just one
row even one value, you usually don't need to name it. And if you have
more, you usually have an application dealing with the data. So renamed
columns are in my opinion only worthwhile for sub-queries.
> i assume that i should be using a SELECT, and not VALUES
>> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage FROM SYSIBM.SysDummy1
Yes, to rename a column you have to have a SELECT statement.
> 2. Declaring Variables
>
>> BEGIN
>> DECLARE SaverUserID INT;
>> END
BEGIN ATOMIC
DECLARE i INT;
END@
> i throught that the field type in DB2-SQL was INTEGER. No?
Data type.
> 3. Returning a value
> SQL Server:
>>> SELECT @@spid AS ProcessID
>>> or
>>> DECLARE @ProcessID int
>>> SET @ProcessID = @spid
>>> SELECT @ProcessID
>>
>> Again, use VALUES.
>
> Again, i assume that VALUES is a bad thing:
How so?
>>BEGIN
>> SET ApplicationID = VALUES Application_ID();
SET ApplicationID = Application_ID();
>>END
>>BEGIN
>> SELECT ApplicationID AS ApplicationID FROM SYSIBM.SysDummy1;
>>END
Just nest this into a single statement:
VALUES application_id()
> Complaining about me ending my statement after the SELECT, that that it is
> expecting a JOIN. Should be joining to something. Do i need to also join
> to a dummy table? e.g.
No, the join is just a suggestion telling you that how the statement _could_
continue. The problem is actually that you have a syntactically incorrect
statement because the END keyword is missing.
> (NOTE: Anyone who knows T-SQL will recognize getdate(), USER_NAME(),
> HOST_NAME(), APP_NAME(). i assume that DB2-SQL has some built-in function
> to get the current date/time. i also assume DB2-SQL has no built-in
> function to get the current Username, MachineName or AppName)
Have a look at the DB2 special registers in the manual: USER, CURRENT DATE,
CURRENT TIME, CURRENT TIMESTAMP.
> Aside from the SQL syntax stuck in there (because i can't get enough far
> enough to debug it), is that a valid syntax for doing an insert into a
> table in DB2 when not using VALUES?
>
> By values i mean:
> INSERT INTO foo (Field1, Field2, ..., FieldN)
> VALUES (Value1, Value2, ..., ValueN);
>
> Put it another way, is this a valid syntax in DB2-SQL:
>
>> INSERT INTO foo (Field1, Field2, ..., FieldN)
>> SELECT Value1, Value2, ..., ValueN
>> FROM MyTable
>> WHERE ...
Works both.
> Finally, is that the valid way to alias tables in DB2-SQL?
>> FROM Inserted i
> will that work, or do i have to do something like:
>> FROM Inserted AS i
Works both. Have a look at the syntax for the sub-select statement.
> Finally, does DB2 support derived tables
>
>> SELECT myo.*, MyDerivedTable.*
>> FROM MyTableOne mto
>> INNER JOIN (SELECT * FROM MyTableTwo mtt
>> WHERE mtt.Field4 = 'Testing') MyDerivedTable
>> mto.SomeJoinField = MyDerivedTable.AnotherJoinField
This are sub-queries and DB2 supports them. Have a look at the syntax
diagram for queries.
> But originally, and most importantly, what's wrong with:
>
> DECLARE SomeNumber INT;
Nothing. You just have to use it in the correct and valid context.
Ian Boyd
VALUES 'abc'
Ahhh. "Table constructor." i get it now. It doesn't declare constants, it
creates an intermediate table. That will require some new thinking on what
problems i can solve with it.
> DB2:
> DECLARE SavedUserID integer;
> Error: SQL0104N An unexpected token "integer" was found following "
> DECLARE
> SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
> NUMBER=1. SQLSTATE=42601
> (State:42601, Native Code: FFFFFF98)
>Your problem here is probably that you did not explicitly specify a
>statement terminator. So the end-of-line terminates your SQL statement,
>and that leaves 'integer;' as a ...something... where DB2 rightfully
>complains about. Try the -t option of the "db2" command line instead (or
>search through the menues if you are using the Command Editor).
i'm using neither. i'm using an ODBC connection, and issuing commands
that way. But now that you mention it, i'll try Command Editor. i notice
that Command Editor shows at the bottom a "Statement termination character"
defaulted to semi-colon. And it still doesn't work. The help also says that
DB2 understands CR as a statement terminator, and that i should not use
a statement termination character inside a CREATE PROCEDURE or CREATE
TRIGGER. So i'll stop using it.
> DECLARE SavedUserID int
SQL0104N An unexpected token "integer" was found following "DECLARE
SavedUserID".
Expected tokens may include: "END-OF-STATEMENT
i try changing the "Statement termination character" to @ and i change my
SQL Statement to:
> DECLARE SavedUserID integer@
SQL0104N An unexpected token "integer" was found following "DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT
> 2. Setting a variable
> SQL Server:
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> or
> SET @SavedUserID = (SELECT User_ID FROM Connection_Users WHERE spid =
> @@spid)
> or (depricated)
> SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@spid
>
> DB2:
> DECLARE ApplicationID varchar(128) --can't declare variables
> SET ApplicationID = Application_ID()
> or
> DECLARE ApplicationID varchar(128) --can't declare variables
> SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);
> First question in a set-oriented language like SQL would be: what do you
> want to do with the value that you really need procedural logic here.
Honestly, so that while i'm writing and testing the stored procedure, i can
check that
i have managed to fetch the propert value. i would fetch the value into
a variable, and then select it, seeing that i got it. (Although, doing a
select into a procedure is a whole thing, not just a simple SELECT -
you have to declare cursors, modify the procedure header to say that i'll
be returning rows, etc)
Also, i want a variable because my audit logging for UPDATE will contain
an equal number of insert statements as there are fields in the table (well,
almost equal). So rather than DB2 having to join for the same value every
time,
i will grab it once and then kept it stored. i am not after what i should
be doing, i'm looking for the DB2-SQL syntax to perform common operations.
i won't detail every form of query i have ever written and why those
queries got the solution they did.
i'm trying to get a super-primer on DB2-SQL, so i can get something up and
running.
3. Returning a value
SQL Server:
> SELECT @@spid AS ProcessID
> or
> DECLARE @ProcessID int
> SET @ProcessID = @spid
> SELECT @ProcessID
How do i declare, set and fetch local variables? What would be syntax to do
that?
> And finally, the full trigger i'm trying to create in DB2 that i can't
> can't make work.
>
>>
>> Error: SQL0104N An unexpected token "integer" was found following "
>> DECLARE SavedUserID". Expected tokens may include: "END-OF-STATEMENT".
>> LINE NUMBER=10. SQLSTATE=42601
>> (State:42601, Native Code: FFFFFF98)
>
>Now that is really a problem with the statement terminator. DB2 takes the
>first ';' as end of the statement so that you will have a syntax error
>right away. That's why you see quite ofter the '@' being used as statement
>terminator here.
Do you mean in general other people who write triggers?
Or is the symbol '@' (commerical at sign) not coming through the
news server correctly - i don't see any '@' as my statement terminator.
> I would write your trigger like this:
> CREATE TRIGGER ...
> No variables needed in the first place and you give the DB2 optimizer a
> much
> better chance to do a good job without the procedural logic.
How would you translate this trimmed down version of a trigger from SQL
Server?
(You don't really have to, i'm just showing what i will be writing after
i can figure out how to declare a variable, and finish tackling the trivial
job of writing an INSERT audit logging trigger)
CREATE TRIGGER LogUpdate_Quotes ON Quotes
FOR UPDATE AS
/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(),
i.QuoteGUID,
'UPDATED',
USER_NAME(),
HOST_NAME(),
APP_NAME(),
@SavedUserGUID,
'Quotes',
'Tax2',
i.ProjectGUID,
i.QuoteNumber,
CAST(d.Tax2 AS varchar(8000)),
CAST(i.Tax2 AS varchar(8000))
FROM Inserted i
INNER JOIN Deleted d
ON i.QuoteGUID = d.QuoteGUID
WHERE (d.Tax2 IS NULL AND i.Tax2 IS NOT NULL)
OR (d.Tax2 IS NOT NULL AND i.Tax2 IS NULL)
OR (d.Tax2 <> i.Tax2)
/* GrandTotal money */
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(),
i.QuoteGUID,
'UPDATED',
USER_NAME(),
HOST_NAME(),
APP_NAME(),
@SavedUserGUID,
'Quotes',
'GrandTotal',
i.ProjectGUID,
i.QuoteNumber,
CAST(d.GrandTotal AS varchar(8000)),
CAST(i.GrandTotal AS varchar(8000))
FROM Inserted i
INNER JOIN Deleted d
ON i.QuoteGUID = d.QuoteGUID
WHERE (d.GrandTotal IS NULL AND i.GrandTotal IS NOT NULL)
OR (d.GrandTotal IS NOT NULL AND i.GrandTotal IS NULL)
OR (d.GrandTotal <> i.GrandTotal)
...74 fields ommitted...
/* TaxScheduleGUID uniqueidentifier */
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(),
i.QuoteGUID,
'UPDATED',
USER_NAME(),
HOST_NAME(),
APP_NAME(),
@SavedUserGUID,
'Quotes',
'TaxScheduleGUID',
i.ProjectGUID,
i.QuoteNumber,
(SELECT Name FROM TaxSchedules WHERE TaxScheduleGUID = d.TaxScheduleGUID),
(SELECT Name FROM TaxSchedules WHERE TaxScheduleGUID = i.TaxScheduleGUID)
FROM Inserted i
INNER JOIN Deleted d
ON i.QuoteGUID = d.QuoteGUID
WHERE (d.TaxScheduleGUID IS NULL AND i.TaxScheduleGUID IS NOT NULL)
OR (d.TaxScheduleGUID IS NOT NULL AND i.TaxScheduleGUID IS NULL)
OR (d.TaxScheduleGUID <> i.TaxScheduleGUID)
i specifially chose one of the widest tables i had, to demonstrate the
volume
of repeative inserts. For some reason everyone in DB2 world prefers for "For
Each Row"
rather than the "For the Statement" style of triggers. Seems pretty
inefficient to run the
same trigger statement for each row affected, when you can run it once for
all of them.
i'm assuming that DB2, like all RDMS's are set-based, and any
row-by-row/cursor operations
are a waste. But it also makes trying to learn DB2-SQL when everyone prefers
the
simpler row-by-row triggers.
My thinking with creating a variable was trying to save DB2 from having to
construct and join to a virtual table over and over. So, i query for
the value once, rather than forcing DB2 to do it over and over.
But even more than that, i want to learn DB2-SQL. And one of the constructs
i am
trying to learn is declaring a variable and using it.
Knut Stolze
>> or use a table constructor:
> VALUES 'abc'
>
> Ahhh. "Table constructor." i get it now. It doesn't declare constants, it
> creates an intermediate table. That will require some new thinking on what
> problems i can solve with it.
That's the relational model: everything is a table. Period. ;-)
>> DECLARE SavedUserID int
> SQL0104N An unexpected token "integer" was found following "DECLARE
> SavedUserID".
> Expected tokens may include: "END-OF-STATEMENT
>
> i try changing the "Statement termination character" to @ and i change my
> SQL Statement to:
>> DECLARE SavedUserID integer@
>
> SQL0104N An unexpected token "integer" was found following "DECLARE
> SavedUserID". Expected tokens may include: "END-OF-STATEMENT
On the command line, you have to embed the DECLARE into a atomic compound
statement.
BEGIN ATOMIC ... END
> Also, i want a variable because my audit logging for UPDATE will contain
> an equal number of insert statements as there are fields in the table
> (well, almost equal). So rather than DB2 having to join for the same value
> every time,
> i will grab it once and then kept it stored. i am not after what i should
> be doing, i'm looking for the DB2-SQL syntax to perform common operations.
> i won't detail every form of query i have ever written and why those
> queries got the solution they did.
> 3. Returning a value
> SQL Server:
>> SELECT @@spid AS ProcessID
>> or
>> DECLARE @ProcessID int
>> SET @ProcessID = @spid
>> SELECT @ProcessID
>
> How do i declare, set and fetch local variables? What would be syntax to
> do that?
It's relational: Build a table, open a cursor and fetch from the table.
Then there is some syntactic sugar to simplify this a bit like:
SET ( var1, var2, var3 ) = ( val1, val2, val3 )
> Do you mean in general other people who write triggers?
I was referring to the posts in this newsgroup.
>> I would write your trigger like this:
>> CREATE TRIGGER ...
>> No variables needed in the first place and you give the DB2 optimizer a
>> much
>> better chance to do a good job without the procedural logic.
>
> How would you translate this trimmed down version of a trigger from SQL
> Server?
What's trimmed down? It does the same thing unless there is more in the SQL
Server trigger going.
> i specifially chose one of the widest tables i had, to demonstrate the
> volume
> of repeative inserts. For some reason everyone in DB2 world prefers for
> "For Each Row"
> rather than the "For the Statement" style of triggers. Seems pretty
> inefficient to run the
> same trigger statement for each row affected, when you can run it once for
> all of them.
The thing is than DB2 compiles the trigger into the INSERT statement itself.
So doing things "for each row" is not slower than your way - I would guess
that it is even faster because no temp tables will be needed.
Serge Rielau
(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/booksearch/isbninquiry.asp?btob=Y&cds2Pid=9030&isbn=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
Serge Rielau
> 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.
This code from your T-SQL trigger in inefficient.
FROM Inserted i
INNER JOIN Deleted d
ON i.QuoteGUID = d.QuoteGUID
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!'
Ian Boyd
> SELECT 'Hello, world!' AS MyLovelyWelcomeMessage
> The question is what the column name will be good for.
> If you get just one row even one value, you usually don't
> need to name it. And if you have more, you usually have
> an application dealing with the data.
If there are multiple fields (a.k.a. columns) in the returned
records (a.k.a. rows), and the client accesses
fields (a.k.a columns) by name, then each
field (a.k.a column) will need a name. Even if there is only one
field (a.k.a column) in the returned records (a.k.a rows), and
the client can only access fields (a.k.a columns) by name, that
field (a.k.a column) will stil need a name.
> Yes, to rename a column you have to have a SELECT statement.
> 2. Declaring Variables
>
>> BEGIN
>> DECLARE SaverUserID INT;
>> END
>
> BEGIN ATOMIC
> DECLARE i INT;
> END@
Ah-hah! "BEGIN ATOMIC"
>> i throught that the field type in DB2-SQL was INTEGER. No?
i see it now in the online help. INT is an alias for Integer field type.
3. Returning a value
>> SQL Server:
>>>> SELECT @@spid AS ProcessID
>>>> or
>>>> DECLARE @ProcessID int
>>>> SET @ProcessID = @spid
>>>> SELECT @ProcessID
>>>
>>> Again, use VALUES.
>> Again, i assume that VALUES is a bad thing:
> How so?
Because rather than using VALUES:
> SET ApplicationID = VALUES Application_ID();
i don't use VALUES:
> SET ApplicationID = Application_ID();
Keep in mind that you're responding to a post i made talking to the other
guy,
before it was explained that VALUES constructs a virtual table.
> Have a look at the DB2 special registers in the manual: USER, CURRENT
> DATE,
> CURRENT TIME, CURRENT TIMESTAMP.
Excellent. Thank you.
So now i'm getting closer to a compiling trigger. Next question
5. Setting a field (a.k.a column) to NULL
SQL Server
> UPDATE MyTable SET SomeField = NULL
> WHERE SomeOtherField IN (SELECT KeyField FROM MyTable2 WHERE Field = 3)
or
> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
> VALUES ('Ian', 'Boyd', '728 Helena', NULL, '911-426-3184')
or
> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system
In my variant of the 3rd case in DB2, it complains that "NULL is not valid
Ian Boyd
> 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.
>
INSERT INTO NewTable
SELECT * FROM OldTable
can be faster if DB2 has to perform logic on every row in the insert, rather
than one set-based operation?
What about my soon-to-be-headache-for-tomorrow an update trigger
UPDATE MyTable
SET AMoneyField = AMoneyField * 1.10
Wouldn't DB2 perfer when doing the trigger:
INSERT INTO AuditLog
SELECT fields
FROM OldTable
INNER JOIN NewTable
ON OldTable.RowID = NewTable.RowID
WHERE OldTable.AMoneyField <> NewTable.AMoneyField
rather than doing
Row#1
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
Row#2
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
Row#3
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
Row#4
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
...
Row#984,648,321
if OldRow.AMoneyField <> NewRow.AMoneyField then
INSERT INTO AuditLog
SELECT fields, OldTableRow.AMoneyField, NewTableRow.AMoneyField
Set based always performs better than row-by-row logic. What am i missing?
> talking of triggers, you may also want to familiarize yourself with
> BEFORE triggers. Very powerful and much more lightweight than patching up
> the rows after the update/insert.
> (they come at the cost of the evaluation of the expression)
>
> CREATE TRIGGER trg1 BEFORE UPDATE ON T
> FOR EACH ROW REFERENCING NEW AS n OLD AS o
> WHEN (n.c1 <= o.c1)
> SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'C1 must increase!'
Can you translate my original insert trigger into a db2 before insert
trigger?
i've seen that example of a before trigger somewhere, but it doesn't really
help me.
What would i want the evaluation expression to be:
CREATE TRIGGER trg1 BEFORE UPDATE ON T
FOR EACH ROW REFERENCING NEW AS n OLD AS o
WHEN (1=1)
BEGIN ATOMIC And_Then_What_Goes_Here;
END;@
Serge Rielau
work in addition to having to match the rows OldTable.RowID =
NewTable.RowID. likely teh join will be a nested loop. meaning you're
scanning one of the temp tables (which have to be created of course)
984,648,321 times. (unless you can use hashjoin which is still far from
for free.
DB2's triggers are inline. There is no invocation cost.
In SQL Server words compare to T-SQL table functions which can be inlined
(Sometimes I hate that I don't own my patents.. Could be rich charging
MS for that stuff)
DB2 will run the following "SQL":
SELECT COUNT(1) -- Ignore the count, artistic freedom...
FROM (INSERT INTO newtable SELECT * FROM OldTable) AS newtablerow,
(INSERT INTO AuditLog
VALUES fields, NULL, NewTableRow.AMoneyField)
(What was oldtable row meant to be...?)
>> talking of triggers, you may also want to familiarize yourself with
>> BEFORE triggers. Very powerful and much more lightweight than patching up
>> the rows after the update/insert.
>> (they come at the cost of the evaluation of the expression)
>>
>> CREATE TRIGGER trg1 BEFORE UPDATE ON T
>> FOR EACH ROW REFERENCING NEW AS n OLD AS o
>> WHEN (n.c1 <= o.c1)
>> SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'C1 must increase!'
>
> Can you translate my original insert trigger into a db2 before insert
> trigger?
> i've seen that example of a before trigger somewhere, but it doesn't really
> help me.
> What would i want the evaluation expression to be:
>
Your trigger can't be a before trigger. it changes the state of the
database. BEFORE triggers are use to:
* Modify the "INSERTED" table BEFORE doing the INSERT
(e.g. to generate complex defaults expressions)
* do error checking not places in a check constraint or RI for some
reason or other.
The WHEN clause is not mandatory, btw.. just omit it if you want the
trigger to fire always.
Cheers
Serge
Serge Rielau
>> There are no "fields" in SQL - just rows, columns and values.
> You channeling Celko? :)
Seriously though it doesn't hurt to use the correct language.
Just like it doesn't hurt to speak proper English outside the pub ;-)
>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
>> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system
>
> In my variant of the 3rd case in DB2, it complains that "NULL is not valid
> in the context where it is used."
DB2 uses strong typing. An untyped NULL (or ?) is only allowed in
specific places where DB2 cann immediatly deduce the datatype.
That would be UPDATE SET, SET statement and INSERT VALUES.
In all other cases CAST(NULL AS <type>) will do the job.
That's the way the standard is defined. No technical reason really.
Ian Boyd
> methods, triggers and "dynamic compound statements".
> DB2 does not support "global variables" (and I thought SQL Server doesn't
> either, so I'm surprised you have an issue.
i don't think SQL Server has global variables either - at least i've never
seen them.
> I think the name SYSIBM.SYDUMMY1 is rooted in the fact that it returns 1
> row.
That's an interesting insight!
> The VALUES clause is actually very powerful when embedded in the from
> clause.
> E.g. instead of doing:
> T-SQL:
> SELECT * FROM
> (SELECT 5
> UNION
> SELECT 6) AS X
> you can do:
> SELECT * FROM (VALUES (5), (6)) AS X.
i've never used any idea similar to
(SELECT 5 UNION SELECT 6) AS X
i've never had a problem where i needed to join to such virtual table. Like
i said before, i'll have to figure out where VALUES would be useful to me.
> This T-SQL: SELECT @@spid AS ProcessID
> is NOT column aliasing.
If i run this query, i get a record set with one row and one column. And
that column has no name. If i want to give that column a name (or a
different name) i have to use:
> SELECT @@spid AS ProcessID
So the column gets renamed from "" to "ProcessID." Whereas the following SET
operation
> SET ProcessID = spid;
returns no rows.
> It is important to note that whenever you write a statement that contains
> ';' due to "substatements" you need to make sure the statement delimiter
> (what you know as "go" I think) is set to a value other than ';' (imagine
> placing 'go' after each line in a T-SQL procedure.. same confusion).
i've never had to use a "go" in T-SQL. All the text i submit is one batch.
There is no ; needed at the end of every statement.
So now having to have not only a statement delimiter, and a batch delimiter
is painful.
> Again I'm surprised you try this since AFAIK SQL Server supports no such
> thing. all T-SQL must be in a procedure.
No. i can send T-SQL to SQL Server and it runs it. i don't seen it to be
inside
a transaction or any such "BEGIN ATOMIC". It runs the SQL it is given. For
example
>DECLARE @a int
>SET @a = 5
>WHILE @A < 5 BEGIN
> SET @a = @a + 5
> EXECUTE SomeStoredProcedure
>END
is perfectly valid. i can send the T-SQL to SQL Sever using it's own query
tool, or using 3rd party tools, or using ADO. i give it those characters, no
semicolons, no go, no begins, end, atomics; and it just runs.
> So let's move into a procedure example:
Here's an interesting question. The following rus fine, exactly as is:
CREATE PROCEDURE dostuff(IN a INTEGER, INOUT b INTEGER, OUT c INTEGER)
BEGIN
DECLARE d INTEGER;
DECLARE cur CURSOR WITH RETURN FOR
SELECT TABNAME FROM SYSCAT.COLUMNS FETCH FIRST 2 ROWS ONLY;
SET d = a + b;
SET c = d * a;
SET b = 7;
OPEN cur;
END
So now:
> CALL dostuff(5, 7, ?)
Wrong number of parameters
> DECLARE c integer; call dostuff(5, 7, ?);
An unexpected token "integer" was found following "DECLARE c ".
>BEGIN
> DECLARE c integer; call dostuff(5, 7, c);
>END
An unexpected token "DECLARE" was found following "begin "
>BEGIN ATOMIC
> DECLARE c integer; call dostuff(5, 7, c);
>END
The parameter mode (IN, OUT, or INOUT) is not valid for a parameter in
procedure "DOSTUFF"
Why is it that the create procedure doesn't need atomic begins. It doesn't
need the keyword atmoc after the begin, and it doesn't need to wrapped
wholly in a begin end such as:
BEGIN ATOMIC
CREATE PROCEDURE dostuff(...)
BEGIN
END
END
But my ad-hoc sql does need atomic begins? By the way, this is far as i can
get. i don't now how to fix the call to the stored procedure.
> PS: One more thing..... the SQL/PSM standard (which is the foundation for
> DB2's SQL procedures) uses exception handlers for error handling.
> Do NOT overload them to emulate old style T-SQL. Use them just like in SQL
> Server 2005 Microsoft encourages you to use exception handlers.
A welcome addition.
> OK I think that covers it. Let us know how it goes.
Time to go home for the day. Day 9, nothing working yet.
Ian Boyd
>> ';' due to "substatements" you need to make sure the statement delimiter
>> (what you know as "go" I think) is set to a value other than ';' (imagine
>> placing 'go' after each line in a T-SQL procedure.. same confusion).
>
> i've never had to use a "go" in T-SQL. All the text i submit is one batch.
> There is no ; needed at the end of every statement.
i think i see the confusion. SQL Server has no "substatements". And
statements don't have to be separated by semi-colons.
If i were to give the OLE DB Provider for Microsoft SQL Server the following
string, as one long string (and this example is taken from the SQL Server
documentation):
string myQuery = "DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
-- Yields an error because @MyMsg not declared in this batch.
PRINT @MyMsg
SELECT @@VERSION;
-- Yields an error: Must be EXEC sp_who if not first statement in
-- batch.
EXECUTE sp_who"
Connection.Execute(myQuery);
This will just run. The entire set of all the statments are sent over to SQL
Server as one "batch", and SQL Server runs them.
Additionally, there is a standard feature built into the query tools, and
that is the use of the keyword "go". It is not a T-SQL keyword, it is a word
only recognized by Microsoft's query tools.
If you entered the following into Microsoft's Query Analyzer;
<quote>
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO -- @MyMsg is not valid after this GO ends the batch.
-- Yields an error because @MyMsg not declared in this batch.
PRINT @MyMsg
GO
SELECT @@VERSION;
-- Yields an error: Must be EXEC sp_who if not first statement in
-- batch.
sp_who
</quote>
The query tool will now send 3 individual batches to SQL Server. Each batch
is separate from the others. You are free to send over all the text in one
batch, or you can have the tool send it over the multiple batches. But as it
indicated in the example query, variables declared in one batch will no
longer exist in the next batch.
So, when i'm trying to do something in DB2-SQL, e.g.:
DECLARE UserID integer
SET UserID =
( SELECT application_ID()
FROM sysibm.sysdummy1
)
update MyTable
SET UserID = UserID
WHERE UserID IS NULL
delete from MyTable
WHERE UserID = 3
select * from MyTable
i expect all that text to be sent to to DB2, i expect DB2 to run the query,
and return me what it is supposed to return me. But DB2 doesn't just read
the SQL it's given. DB2 seems to require semicolons to separate each
statment. e.g.:
DECLARE UserID integer;
SET UserID =
( SELECT application_ID()
FROM sysibm.sysdummy1
);
update MyTable
SET UserID = UserID
WHERE UserID IS NULL;
delete from MyTable
WHERE UserID = 3;
select * from MyTable;
Which is fine. It would be nicer if it didn't need semicolons, but okay,
i'll live with it.
But now, in addition, this apparently isn't enough. Just because i've given
DB2 some statements to run, doesn't mean that it will run them. For some
reason, i have to tell it that the SQL it just received really is all
together in one "batch" (to steal a MSSQL term)
BEGIN ATOMIC
DECLARE UserID integer;
SET UserID =
( SELECT application_ID()
FROM sysibm.sysdummy1
);
update MyTable
SET UserID = UserID
WHERE UserID IS NULL;
delete from MyTable
WHERE UserID = 3;
select * from MyTable;
END
But not only that, i have to actually begin the batch with the keywords
BEGIN ATOMIC and end the batch with END. So as i understand it, just sending
a bunch of SQL to DB2 is not enough for it to decide to run the batch, i
have to explicitly tell it that it is a batch. Fine, okay, messy, but i
think i understand.
But wait, i don't understand. Because i can send the SQL to create a stored
procedure
CREATE PROCEDURE doStuff(...)
BEGIN
...
END
and i don't have to wrap the batch in BEGIN ATOMIC..END e.g.
BEGIN ATOMIC
CREATE PROCEDURE doStuff(...)
BEGIN
...
END
END
So perhaps because it is a CREATE PROCEDURE, or CREATE TRIGGER, or CREATE
TABLE, etc that i can omit the BEGIN ATMIC...END around the statement in
those batches. Perhaps it is because a CREATE PROCEDURE, CREATE TRIGGER,
CREATE TABLE itself is a single statement that it doesn't need to be
wrapped. Maybe batches that only consist of a single statement don't need to
be wrapped. No, that's not true either:
DECLARE myValue int;
fails also. Maybe Create XXXXX statements are just special like that.
But now, to throw another level of confusion into it, inside a CREATE
TRIGGER, you DO have to have BEGIN ATOMIC...END, but not inside a CREATE
PROCEDURE.
So, if you read this, please try not to respond to things in detail.
Hopefully you can see my confusion, and this must be because i have a
different mental picture of how SQL Server is given and runs T-SQL and how
DB2 is given and runs T-SQL. If you can see the error in my understanding,
and point out exactly where my thinking is wrong - that would be great. It
would be nice to have an understanding, rather than hoping understanding
will come after being exposed to dozens of disprate examples.
Dave Hughes
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.
Stefan Momma
> i'm trying to get a super-primer on DB2-SQL, so i can get something up and
> running.
My favourite reference for DB2 SQL is Graeme Birchall's
DB2 SQL Cookbook, which is available from this website:
http://mysite.verizon.net/Graeme_Birchall/id1.html
hope this helps,
-- stefan
Serge Rielau
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
is the place to go:
http://www.redbooks.ibm.com/
The books are free for download as PDF.
Knut Stolze
>> There are no "fields" in SQL - just rows, columns and values.
> You channeling Celko? :)
No, not really. I just want to make sure that you know the terminology used
by DB2 (and the SQL standard) so that you will have an easier time when
looking at the manual.
> 3. Returning a value
>>> SQL Server:
>>>>> SELECT @@spid AS ProcessID
>>>>> or
>>>>> DECLARE @ProcessID int
>>>>> SET @ProcessID = @spid
>>>>> SELECT @ProcessID
>>>>
>>>> Again, use VALUES.
>>> Again, i assume that VALUES is a bad thing:
>> How so?
>
> Because rather than using VALUES:
>> SET ApplicationID = VALUES Application_ID();
> i don't use VALUES:
>> SET ApplicationID = Application_ID();
That doesn't make VALUES a bad thing. It is just not necessary here.
> 5. Setting a field (a.k.a column) to NULL
>
> SQL Server
>> UPDATE MyTable SET SomeField = NULL
>> WHERE SomeOtherField IN (SELECT KeyField FROM MyTable2 WHERE Field = 3)
Works.
> or
>
>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
>> VALUES ('Ian', 'Boyd', '728 Helena', NULL, '911-426-3184')
This works too.
> or
>
>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
>> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system
>
> In my variant of the 3rd case in DB2, it complains that "NULL is not valid
> in the context where it is used."
This does not work because NULL is untyped and DB2 does not know if the data
types produced by the subselect match with the data types in the table. So
try this:
INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
SELECT fname, lname, addr1,
CAST(NULL AS VARCHAR(10)), CAST(NULL AS VARCHAR(10))
FROM legacy_system
Btw, you got the error SQL0206N in this case. You should first look up the
error description to correct your problem:
-----------------------------------------------------------
$ db2 "? sql0206"
SQL0206N "<name>" is not valid in the context where it is
used.
Explanation:
This error can occur in the following cases:
o For an INSERT or UPDATE statement, the specified column is
not a column of the table, or view that was specified as the
object of the insert or update.
o For a SELECT or DELETE statement, the specified column is not
a column of any of the tables or views identified in a FROM
clause in the statement.
o For an ORDER BY clause, the specified column is a correlated
column reference in a subselect, which is not allowed.
o For a CREATE TRIGGER, CREATE METHOD or CREATE FUNCTION
statement:
- The reference "<name>" does not resolve to the name of a
column, local variable or transition variable.
- The condition name "<name>" specified in the SIGNAL statement
has not been declared.
o For a CREATE TRIGGER statement:
- A reference is made to a column of the subject table without
using an OLD or NEW correlation name.
- The left hand side of an assignment in the SET
transition-variable statement in the triggered action
specifies an old transition variable where only a new
transition variable is supported.
o For a CREATE FUNCTION statement with a PREDICATES clause:
- The RETURN statement of the SQL function references a
variable that is not a parameter or other variable that
is in the scope of the RETURN statement.
- The FILTER USING clause references a variable that is not a
parameter name or an expression name in the WHEN
clause.
- The search target in an index exploitation rule does not
match some parameter name of the function that is being
created.
- A search argument in an index exploitation rule does not
match either an expression name in the EXPRESSION AS
clause or a parameter name of the function being
created.
o For a CREATE INDEX EXTENSION statement, the RANGE THROUGH
clause or the FILTER USING clause references a variable that
is not a parameter name that can be used in the clause.
The statement cannot be processed.
User Response:
Verify that the names are specified correctly in the SQL
statement. For a SELECT statement, ensure that all the required
tables are named in the FROM clause. For a subselect in an ORDER
BY clause, ensure that there are no correlated column references.
If a correlation name is used for a table, verify that subsequent
references use the correlation name and not the table name.
For a CREATE TRIGGER statement, ensure that only new transition
variables are specified on the left hand side of assignments in
the SET transition-variable statement and that any reference to
columns of the subject table have a correlation name specified.
sqlcode : -206
sqlstate : 42703
-----------------------------------------------------------
Unfortunately, this particular situation is not explained explicitly. So
you can only derive that NULL is interpreted as column name. (Note that
DB2 allows a column to be named NULL.)
Knut Stolze
> i've never used any idea similar to
> (SELECT 5 UNION SELECT 6) AS X
>
> i've never had a problem where i needed to join to such virtual table.
I would not call this a "virtual" table. It is a table (like views are
tables). The major issue here is where the data of the table comes from.
In this case, the table is constructed on the fly.
> Like i said before, i'll have to figure out where VALUES would be useful
> to me.
>
>> This T-SQL: SELECT @@spid AS ProcessID
>> is NOT column aliasing.
>
> If i run this query, i get a record set with one row and one column. And
> that column has no name. If i want to give that column a name (or a
> different name) i have to use:
>
>> SELECT @@spid AS ProcessID
>
> So the column gets renamed from "" to "ProcessID."
Note that DB2 names expressions that are returned from a query itself (it
numbers them) if they don't have an explicit name. That's why you see the
"1" or "2" in the following output:
$ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1"
1 2
----------- -----------
6 15
1 record(s) selected.
Same thing here:
$ db2 "values ( 1+2+3, 4+5+6 )"
1 2
----------- -----------
6 15
1 record(s) selected.
And those "1" or "2" can be used in a Java application when you fetch the
data from the result set by column name.
Ian Boyd
>
> 1 2
> ----------- -----------
> 6 15
>
> 1 record(s) selected.
>
>
> Same thing here:
>
> $ db2 "values ( 1+2+3, 4+5+6 )"
>
> 1 2
> ----------- -----------
> 6 15
>
> 1 record(s) selected.
>
> And those "1" or "2" can be used in a Java application when you fetch the
> data from the result set by column name.
Unless someone changed the query to
SELECT 3.14159, 1+2+3, 4+5+6
1 2 3
-------- -------- --------
3.14159 6 15
1 record(s) selected.
And now all the application logic has to be rewritten.
Ian Boyd
news:476fhtF...@individual.net...
> Ian Boyd wrote:
>>> There are no "fields" in SQL - just rows, columns and values.
>> You channeling Celko? :)
> *chuckle* I had the same thought.
> Seriously though it doesn't hurt to use the correct language.
> Just like it doesn't hurt to speak proper English outside the pub ;-)
Warning. Celko bait ahead:
<CelkoBait>
Yes. But those are semantics of the most anal kind. i have seen many, many,
many posts of Celko explaining how if you confuse a row/record and
column/field, you are doing yourself a disservice.
We can all agree that someplace on my hard drive is a the value for a
particular column of a particular row. And we can all agree to call that a
field. Simarly, if i select a specific row from a table, that is a record.
Yes, the terms were invented when one table was stored in one file, and the
notion of "the next 6 rows" was perfectly valid. Yes, modern databases store
things in pages all over the place, but there is still some physical order.
The btree has an order. You can't guarantee that order, or ever even see it.
But the rows that you return are records.
Let us all agree that the terms record/field while originally didn't apply
to RDMS's, now do.
</CelkoBait>
>>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
>>> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system
>>
>> In my variant of the 3rd case in DB2, it complains that "NULL is not
>> valid
>> in the context where it is used."
> DB2 uses strong typing. An untyped NULL (or ?) is only allowed in specific
> places where DB2 cann immediatly deduce the datatype.
> That would be UPDATE SET, SET statement and INSERT VALUES.
> In all other cases CAST(NULL AS <type>) will do the job.
> That's the way the standard is defined. No technical reason really.
Excellent. Thank you. i, of course, would prefer it if DB2 would just do it.
But i'm okay with it forcing me to tell it that what it thinks i want to do
is really what i want to do.
Serge Rielau
(above the ----- line).
You are now talking about result types.
Cheers
Serge
Serge Rielau
> "Serge Rielau" <sri...@ca.ibm.com> wrote in message
> news:476fhtF...@individual.net...
>> Ian Boyd wrote:
>>>> There are no "fields" in SQL - just rows, columns and values.
>>> You channeling Celko? :)
>
>> *chuckle* I had the same thought.
>> Seriously though it doesn't hurt to use the correct language.
>> Just like it doesn't hurt to speak proper English outside the pub ;-)
>
> Warning. Celko bait ahead:
> <CelkoBait>
> </CelkoBait>
Different styles. In this group using the f-word causes a raised
eyebrow. Joe i s abit more opinionated.
Let me put in another way:
There is a _statistical_ correlation between using the professional
vocabulary and the skill level.
Fields and Records are EXCEL and ACCESS speak. It _suggests_ a
technologcal "redneck".
In usenet, perception is everything ;-)
>>>> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
>>>> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system
>>> In my variant of the 3rd case in DB2, it complains that "NULL is not
>>> valid
>>> in the context where it is used."
>> DB2 uses strong typing. An untyped NULL (or ?) is only allowed in specific
>> places where DB2 cann immediatly deduce the datatype.
>> That would be UPDATE SET, SET statement and INSERT VALUES.
>> In all other cases CAST(NULL AS <type>) will do the job.
>> That's the way the standard is defined. No technical reason really.
>
> Excellent. Thank you. i, of course, would prefer it if DB2 would just do it.
> But i'm okay with it forcing me to tell it that what it thinks i want to do
> is really what i want to do.
When the SQL standard was created strong typing was desired.
In reality most products have long abandoned strong typing.
I agree that DB2 is doing a futile "last stand" here.. one of these days
that will be relaxed.
Ian Boyd
>
> That is you can do:
> CREATE..., DROP.., GRANT, REVOKE, ALTER
> DECLARE cursors, FETCH, CLOSE (and implied SELECT, VALUES cursors from
> CLP)
> UPDATE,DELETE, INSERT, MERGE
> CALL
> BEGIN ATOMIC .. END
On some level i wondered if that was how DB2 did it. But i convinced myself
that such a scheme is just too complicated to be true, and i assumed that i
had some fundamental misunderstanding about what is going on.
Is there a complete list somwhere of what i can and cannot run?
Specifically, i'm concerned about the "implied" SELECT and VALUES. My
confusion comes from the fact that sometimes i can just run SELECT * FROM
..., and other times i have to declare a cursor for a select, and then leave
the cursor open. i don't see OPEN in your list.
In fact, a quick check of the IBM DB2 Universal Database SQL Reference
Volume 1 and 2 makes no mention of the keyword BEGIN except for "BEGIN
DECLARE SECTION". Neither does the "DB2 Information Center" website. Can you
point me to some references on this?
Brian Tkatch
>had some fundamental misunderstanding about what is going on.
Welcome to the non-MS/Windows world. Where you are expected to
understand what your are doing. :)
>My confusion comes from the fact that sometimes i can just run SELECT * FROM
>..., and other times i have to declare a cursor for a select
A SELECT statement can be run outside a block of code, a DECLARE
within.
The difference is, SELECT is a "statement" and DECLARE is a "control
statement". Both clearly delineated in SQL Reference Volume 2.
Generally, the beginning of the documentate for a particular statement
says when it can (and sometimes when it cannot) be executed.
B.
Serge Rielau
>> Procedural statements are not supported as independent statements by DB2.
>>
>> That is you can do:
>> CREATE..., DROP.., GRANT, REVOKE, ALTER
>> DECLARE cursors, FETCH, CLOSE (and implied SELECT, VALUES cursors from
>> CLP)
>> UPDATE,DELETE, INSERT, MERGE
>> CALL
>> BEGIN ATOMIC .. END
>
> On some level i wondered if that was how DB2 did it. But i convinced myself
> that such a scheme is just too complicated to be true, and i assumed that i
> had some fundamental misunderstanding about what is going on.
>
> Is there a complete list somwhere of what i can and cannot run?
> Specifically, i'm concerned about the "implied" SELECT and VALUES. My
> confusion comes from the fact that sometimes i can just run SELECT * FROM
> ..., and other times i have to declare a cursor for a select, and then leave
> the cursor open. i don't see OPEN in your list.
The interactive tools (like command center and CLP) have short hands for
queries. That is when you type VALUES or SELECT interactively they
will declare a cursor for you, open it, fetch all the rows and close.
Then they pretty print the output.
> In fact, a quick check of the IBM DB2 Universal Database SQL Reference
> Volume 1 and 2 makes no mention of the keyword BEGIN except for "BEGIN
> DECLARE SECTION". Neither does the "DB2 Information Center" website. Can you
> point me to some references on this?
This is the root for SQL Procedure logic:
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0004239.htm
This is the root for the simpler command line scripting:
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/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.
Ian Boyd
How to do comments in DB2-SQL?
From:
IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
Chapter 2. Language Elements
Tokens
<quote>
Comments
Static SQL statements may include host language comments or SQL comments.
Either type of comment may be specified wherever a space may be specified,
except
within a delimiter token or
between the keywords EXEC and SQL.
SQL comments are introduced by two consecutive hyphens (--) and ended by the
end of the line.
</quote>
The following DB2-SQL fails:
INSERT INTO Daily_Logs (
Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID, Bay_Number,
Supervisor_User_ID, Location_ID, Occurrence_ID, Checklist_ID,
Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date,
Description)
VALUES (
'DL-20060307-1', --DailyLogNumber
0, --CreatedByUserID
1, --PropertyID
1, --ShiftID
"A74", --BayNumber
1, --SupervisorUserID
2, --LocationID
CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls)
CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID
5, --DailyLogSubTypeID
'2006-03-01 11:11:07.11111', --StartDate
'2006-03-01 11:21:18.22222', --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls)
);
But if a take out the comments, it works. i have no keywords EXEC or SQL,
and i am not putting my comments within a delimiter token "," since i
cannot split a comma into two parts.
*so tired*
Ian Boyd
> understand what your are doing. :)
They have databases on computers these days. i know what i'm doing, and
computers are powerful enough these days to know as well; or at least
proceed with the only possible course of action.
>>My confusion comes from the fact that sometimes i can just run SELECT *
>>FROM
>>..., and other times i have to declare a cursor for a select
>
> A SELECT statement can be run outside a block of code, a DECLARE
> within.
>
> The difference is, SELECT is a "statement" and DECLARE is a "control
> statement". Both clearly delineated in SQL Reference Volume 2.
>
> Generally, the beginning of the documentate for a particular statement
> says when it can (and sometimes when it cannot) be executed.
That explains why the reference doesn't include some statements, their not
the right "kind" of statements.
i see the majority of the reference is in a section called "Statements". i
don't see a corresponding section of "control statements", nor is the
keyword DECLARE in the index. Is there a Reference Volume 3 that documents
the "control statements?" Are there are more kinds of statements?
[Comicbook Guy] Umm, excuse me. Clearly select is a statement, and declare
is a control statement. Thank you.
Ian Boyd
1 2
----------- -----------
6 15
> SELECT 3.14159, 1+2+3, 4+5+6
1 2 3
-------- -------- --------
3.14159 6 15
> You two got yor wires crossed.. Knut is talking about correlation names
> (above the ----- line).
> You are now talking about result types.
?
i thought we were talking about correlation names and how they are still
necessary even if there is only one column in the results set.
Ian Boyd
> http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0004239.htm
> This is the root for the simpler command line scripting:
> http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0004240.htm
i get it. Some statements are only valid inside other statements. If of
course be convient if they would just work.
> This statement can only be embedded in an SQL procedure. It is not an
> executable statement and cannot be dynamically prepared."
Why? It can't be a technical limitation, and there is little value in making
things more difficult for people to use.
Is it the standard? And if so how many companies in the consortium are
trying to change it? It's fair to say that in the end this stuff is meant to
be used by developers, not just computer scientists.
> You implicitly raise an interesting point though.
> The SQL Reference is "dictionary" it is as little the right tool to learn
> the basics of SQL as any dictionary.
Every other computer language has keywords or tokens, and someplace you can
open a reference manual and get an explanation for that token, or a
reference to where it is used.
> You are used to MS SQL Server "Books Online" which is more of a guide.
> It describes what matters example driven).
> The DB2 SQL Ref is the _exact_ specification of DB2's SQL.
Which is about a dry a read as ISO/IEC 9075 is, albeit more helpful.
> There are plans to deliver a SQL Guide in a future release which will be
> more appropriate and have information such as which statement can be used
> where, and include scenario based examples.
An index would be nice. Seaching a web-site, pdf, google groups, or the
internet for "ibm db2 set" doesn't help so much. But if i could type SET and
be presented with the index entries that someone has already taken the time
to pre-select, would be so so SO SO SO much more useful.
In the Books Online, i rarely use the "contents" and i rarely use "search".
i use the index almost exclusivly. If i want help on, for example, SET, i
type the word SET and am presented with the documentation on the SET
keyword. Doing a word search for "SET" would be folly.
Dave Hughes
Yup, in DB2 SQL comments must appear as the first non-whitespace
characters in a line. Hence:
SELECT
AFIELD, -- This is not a comment
FROM ...
SELECT
-- This is a comment
AFIELD,
FROM ...
> 5. Comments
>
> How to do comments in DB2-SQL?
>
> From:
> IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
> Chapter 2. Language Elements
> Tokens
>
> <quote>
> Comments
> Static SQL statements may include host language comments or SQL
> comments. Either type of comment may be specified wherever a space
> may be specified, except within a delimiter token or between
> the keywords EXEC and SQL. SQL comments are introduced by two
> consecutive hyphens (--) and ended by the end of the line. </quote>
Strange that the manual doesn't make any mention of this behaviour. I
could swear it did at some point in the past, but maybe my memory's
faulty. It is an annoying behaviour, especially as it's not exactly
difficult to change a parser to permit -- comments pretty much anywhere
(if anything, it's more difficult to write a parser that only permits
-- comments as the first non-whitespace characters in a line, something
I've found out from experience in writing syntax highlighters for SQL
editors and such like).
HTH,
Dave.
Dave Hughes
[snip]
> Note that DB2 names expressions that are returned from a query itself
> (it numbers them) if they don't have an explicit name. That's why
> you see the "1" or "2" in the following output:
>
> $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy1"
>
> 1 2
> ----------- -----------
> 6 15
>
> 1 record(s) selected.
>
>
> Same thing here:
>
> $ db2 "values ( 1+2+3, 4+5+6 )"
>
> 1 2
> ----------- -----------
> 6 15
>
> 1 record(s) selected.
>
> And those "1" or "2" can be used in a Java application when you fetch
> the data from the result set by column name.
Yes, though my personal opinion is that it's a bad idea to use the
"raw" numeric column names that DB2 generates; they're subject to
change if the query changes, and can't be used in all the same ways as
a properly named column. For example:
Numeric column names
SELECT * FROM (VALUES (1, 2)) AS T; -- Works
SELECT 1, 2 FROM (VALUES (1, 2)) AS T; -- Works
SELECT T.* FROM (VALUES (1, 2)) AS T; -- Works
SELECT T.1, T.2 FROM (VALUES (1, 2)) AS T; -- Doesn't work
Aliased column names
SELECT * FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
SELECT F1, F2 FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
SELECT T.* FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
SELECT T.F1, T.F2 FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
Hence, I'd always recommend one renames generated column names to
something meaningful.
HTH,
Dave.
Ian Boyd
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.
Serge Rielau
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.
Brian Tkatch
>computers are powerful enough these days to know as well; or at least
>proceed with the only possible course of action.
The day DBs do things for me, is the day i stop doing databases. I
actually despise Windows mostly because of these assumptions. I love
DBs, because they are so simple, and make no assumptions.
>That explains why the reference doesn't include some statements, their not
>the right "kind" of statements.
Close. It's because, they are not statements.
>i see the majority of the reference is in a section called "Statements". i
>don't see a corresponding section of "control statements"
In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL
control statements".
>, nor is the keyword DECLARE in the index.
It is absolutely in the index. Though, it is not a bookmark.
It is in Chapter 2.=>Compound Statement (Procedure) under
"SQL-variable-declaration".
A search of the index (which is a bookmark) found it for me pretty
easily.
B.
Dave Hughes
This works in CLP? Doesn't for me! From DB2 UDB v8 under Linux:
$ db2 -t
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.0
[snip help stuff]
db2 => SELECT
db2 (cont.) => F1, -- A comment
db2 (cont.) => F2, -- Another comment
db2 (cont.) => F3 -- Yet another comment
db2 (cont.) => FROM
db2 (cont.) => (VALUES (1, 2, 3)) AS T(F1, F2, F3);
SQL0104N An unexpected token "," was found following "SELECT F1".
Expected
tokens may include: "<table_expr>". SQLSTATE=42601
db2 => SELECT
db2 (cont.) => -- A comment
db2 (cont.) => F1,
db2 (cont.) => -- Another comment
db2 (cont.) => F2,
db2 (cont.) => -- Yet another comment
db2 (cont.) => F3
db2 (cont.) => FROM
db2 (cont.) => (VALUES (1, 2, 3)) AS T(F1, F2, F3);
F1 F2 F3
----------- ----------- -----------
1 2 3
1 record(s) selected.
> The thing about -- is that if your client strips out line feeds then
> everything after the first -- will look like a comment. select *
> --hello from -- comment t -- more comment And of course select * is
> not legal SQL. There is nothing DB2 can do on -- if the client screws
> things up... so please clarify your client interface.
Nope, in this case I think it's the thing I mentioned in my other post:
comments can only appear as the first non-whitespace characters in a
line.
> PS: I find this thread quite interesting actually.
Absolutely. In another post I was rambling on vaguely incoherently
about functional versus procedural styles in DB2 and other DBs ... I'm
beginning to suspect there's a whole different way of thinking required
when switching from certain relational systems to others (a bit like
learning functional programming after doing C/Pascal imperative stuff
for so long ... I remember feeling very fatigued at how difficult
everything seemed, until there came a point where I just "got it" and
it all just seemed to fall into place ... the relief was tangible!)
Cheers,
Dave.
Ian Boyd
--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 ;
Dave Hughes
[snip]
> The day DBs do things for me, is the day i stop doing databases.
You hand-crank the execution plan for all your queries? Wow ...
hardcore man! (joking :-)
> I actually despise Windows mostly because of these assumptions. I love
> DBs, because they are so simple, and make no assumptions.
I'd take issue with this in one particular area. I've always liked that
one can tweak just about *any* performance parameter in DB2. That said,
I've come to enjoy the ability added in more recent versions to have
the tools figure out an "optimum" configuration, or in the most recent
versions to just set the parameter to AUTOMATIC and have the database
look after itself.
I'd be extremely disappointed if such configuration parameters were
ever removed completely from manual control ... but I do appreciate a
bit of "intelligence" being added to the system, provided it's optional
:-)
Cheers,
Dave.
Serge Rielau
"table not found", so I figured I got past the syntax checks..
apparently a hasty and wrong assumption.
Dave Hughes
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.
Serge Rielau
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/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.
Ian Boyd
> Please clarify your environment.
i've tried a couple:
- Microsoft ADO using the IBM DB2 driver for ODBC
- Microsoft ADO using the IBM DB2 OLEDB Provider
- 3rd party program called "WinSQL" which connects through an ODBC DSN (with
it's built-in statement delimiter changed to ½)
- IBM Command Editor (db2ce.bat) with it's "Statement termination character"
changed to ½
i'll show you the detailed results from IBM Command Editor, as it returms
more error information than the simple exception thrown by ADO from the ODBC
for OLEDB providers.
<quote>
------------------------------ Commands
Entered ------------------------------
INSERT INTO Daily_Logs (
Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID,
Bay_Number,
Supervisor_User_ID, Location_ID, Occurrence_ID, Checklist_ID,
Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date,
Description)
VALUES (
'DL-20060307-3', --DailyLogNumber
0, --CreatedByUserID
1, --PropertyID
1, --ShiftID
'A74', --BayNumber
1, --SupervisorUserID
2, --LocationID
CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls)
CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID
5, --DailyLogSubTypeID
'2006-03-01 11:11:07.11111', --StartDate
'2006-03-01 11:21:18.22222', --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls)
);½
------------------------------------------------------------------------------
INSERT INTO Daily_Logs ( Daily_Log_Number, Created_By_User_ID, Property_ID,
Shift_ID, Bay_Number, Supervisor_User_ID, Location_ID, Occurrence_ID,
Checklist_ID, Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date,
Description) VALUES ( 'DL-20060307-3', --DailyLogNumber 0, --CreatedByUserID
1, --PropertyID 1, --ShiftID 'A74', --BayNumber 1, --SupervisorUserID
2, --LocationID CAST(NULL AS bigint), --Occurrence_ID (must manually cast
nulls) CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID 5, --DailyLogSubTypeID '2006-03-01
11:11:07.11111', --StartDate '2006-03-01 11:21:18.22222', --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls) );
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "," was found following "ES (
'DL-20060307-3'".
Expected tokens may include: ")". SQLSTATE=42601
SQL0104N An unexpected token "," was found following "ES (
'DL-20060307-3'". Expected tokens may include: ")
".
Explanation:
A syntax error in the SQL statement was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.
As an aid to the programmer, a partial list of valid tokens is
provided in the SQLERRM field of the SQLCA as "<token-list>".
This list assumes the statement is correct to that point.
The statement cannot be processed.
User Response:
Examine and correct the statement in the area of the specified
token.
sqlcode : -104
sqlstate : 42601
</quote>
> PS: I find this thread quite interesting actually.
In a morbid train-wreck sorta way?
i'm approaching DB2 from a very high-level (and overview if you will). It's
not like i don't understand relational databases. i think i am very used to
SQL Server, where it is very powerful and yet very friendly. If you accept
that most enterprise class RDBMS are of a similar feature set, the different
between is semantics, and tools, and language.
i'm walking a fine line here. There are things in DB2 that make no sense.
They are, quite plainly, non-sensical. i try to explain what i think the
vision and philosophy that DB2 has for doing things - as though there was
an all-encompassing grand vision for everything. But things are not that
way. Due to historical design decisions, backwards compatiblity, forward
compatiblity, standards compatability, etc things can make little sense to
an outside observer. Which is fine, as long as i can quickly find the list
of all these different design paradigms. But many zealots will take my
confusion and frustration of the scattered design as an insult, and thats a
tough needle to thread, especially in a DB2 newsgroup - where i am stating
up front i come from Microsoft SQL Server, and a lot of people in here have
used the word "Micro$oft".
Also, when dealing with, and writing many user interfaces, i have become
picky about programs or systems that cannot do what a user expects. So some
of IBM's graphical tools, and SQL language itself, can leave much to be
desired from a usability point of view.
i'm walking a fine line here: of trying to extract information from the
people in the know, without touching a nerve.
Dave Hughes
> Working as documented:
> http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.
> udb.doc/admin/r0000888.htm
>
> DEFAULT ...
>
[snip]
But why can CURRENT CLIENT_APPLNAME and CURRENT CLIENT_WRKSTNNAME not
be used as column defaults? I'm guessing there must be some technical
reason, e.g. maybe there are some circumstances in which these
registers would be unknown or undefined when inserting a row into a
table? (though I guess one could argue that these registers ought to
evaluate to NULL under such circumstances instead of being impossible
to use as a column default)
Cheers,
Dave.
Ian Boyd
> actually despise Windows mostly because of these assumptions. I love
> DBs, because they are so simple, and make no assumptions.
The running joke at the office is the DB2 "WARNING: Everything is okay!"
dialog box.
i hit "OK" to save my changes to, for example, a table. Everything saves
okay, but DB2 feels compelled to pop up a big dialog with a lot of text in
it. Obviously something bad happened, because if everything saved okay after
i told you to save, the edit table screen would go way, and we'd be done.
So each and every time we see one of these boxes pop up, we have to read it:
DB2 Message
Commands attempted
<some sql that i didn't ever enter>
The command completed successfully.
Explanation:
No errors were encountered during the execution of this
command.
User Response:
No action required.
Warning! Everything is okay.
It is just as stupid as installing the game Ghost Recon a few years ago, a
dialog box pops up, "You have enough free space on this drive to install the
game. Proceed?" As if i needed to know that.
<StartOfRanging @skip="yes">
Or another one, i want to shrink a varchar field length:
DBAINTRN
The table and its related objects will be dropped and re-created.
Changing the definition of a column attribute involves a
LOAD operation. If the database is recoverable, a load copy will be
taken for the table being altered. The load copy will be saved as
SUPERDUDE.AUDIT_LOG_table.dat. You can delete the load
copy file once a backup has been taken for all the related
tablespaces.
Would you like to proceed?
Would i like to proceed? Well, i don't know. i didn't ask you to drop and
re-create the table and all it's dependant objects. i told you to shink a
column from 128 to 100 characters. You do whatever it is you have to do to
do it. Are you trying to ask me if i'm okay with the various steps you have
to do to perform that operation? Why wouldn't i be? My only choices are to
either save my changes or not. i said save, so do it.
Some people will argue, "But the table will have to be dropped and renamed
and all keys will recreated. It could potentially be a long operation and
maybe the user didn't realize it would take so long to do - so we need to
get their permission before doing it. Or worse yet, what if there's a power
failure, and something is left in a broken state. At least the user knows
that DB2 was performing this relativly big operation. We have to inform the
user before we just go ahead and do this."
No you don't. i said save, you save. You do whatever it is you do when you
have to save. If it takes a long time, i'm okay with that, because i was
changing table structure - i'll expect it to take a long time.
And with the nulls. Why can't it implicitly cast a NULL to the type of the
column? What alternative is there? You are perfectly okay casting other
types around, why not NULL? Null is the lack of data, it is nothing, it is
not being. People will argue,
"But DB2 is a strongly typed system, and null doesn't have the same type as
integer. What if the user didn't mean to put a NULL into that column, and we
went ahead and did it anyway, that would be bad. If the user really meant to
put null in this field they should indicate that by casting it to the proper
target data type."
No i don't. i said put null in this column. Your choices are to put it in
the column, or not. So why would you not do it? Implicitly cast it and get
it done. Just do it. i should have to tell twice, when everything knows
that's what i want done. Even the village idiot comes to that conclusion.
</StartOfRanging>
>>That explains why the reference doesn't include some statements, their not
>>the right "kind" of statements.
>
> Close. It's because, they are not statements.
i guess this is where some help with examples would be extraordinarily
useful.
>>i see the majority of the reference is in a section called "Statements". i
>>don't see a corresponding section of "control statements"
>
> In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL
> control statements".
SQL Reference Volume 1
Contents
About this book
Chapter 1. Concepts
Chapter 2. Language elements
Chapter 3. Functions
Chapter 4. Queries
Appendix A. SQL limits
...
Appendix Q. Notices
Index
Contacting IBM
SQL Reference Volume 2
Contents
About this book
Statements
Appendix A. DB2 Universal Databse technical information
Appendix B. Notices
Index
Contacting IBM
These are two PDF files that are referred to by the online documentation. i
reach the online documentation from Control Center by hitting: Help->About
On the web-site, i see
Reference
SQL
How to read the syntax diagrams
Common syntax elements
Language elements
Functions
Procedures
Queries
Statements
Reserved schema names and reserved words
SQL statements allowed in routines
Communications areas, descriptor areas, and exception tables
Explain tables
Explain register values
Japanese and traditional-Chinese extended UNIX code (EUC)
considerations
Backus-Naur form (BNF) specifications for DATALINKs
>>, nor is the keyword DECLARE in the index.
>
> It is absolutely in the index. Though, it is not a bookmark.
>
> It is in Chapter 2.=>Compound Statement (Procedure) under
> "SQL-variable-declaration".
> A search of the index (which is a bookmark) found it for me pretty
> easily.
i gotta find this book, web-site, pdf, help file, or eBook you got.
Ian Boyd
> http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0000888.htm
>
By working at intended do you mean that only some special registers can be
used as default values on columns and not others? Does that mean that it
can't be done? Can you suggest some workarounds to accomplish the same task?
--Fails (unexpected token near "CLIENT_APPLNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN APPNAME
SET WITH DEFAULT CURRENT CLIENT_APPLNAME ;
--Fails (unexpected token near "CLIENT_WRKSTNNAME")
ALTER TABLE SUPERDUDE.AUDIT_LOG
ALTER COLUMN HOSTNAME
SET WITH DEFAULT CURRENT CLIENT_WRKSTNNAME ;
This goes to what i was saying before about non-sensical nature of DB2.
"Why can some special registers be used as column defaults and not others?"
"Cause."
"Wouldn't you maybe want to clean that up so it is consistent?"
Ian Boyd
> comments can only appear as the first non-whitespace characters in a
> line.
i can't get that to work either:
INSERT INTO Daily_Logs (
Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID, Bay_Number,
Supervisor_User_ID, Location_ID, Occurrence_ID, Checklist_ID,
Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date, End_Date,
Description)
VALUES (
--DailyLogNumber
'DL-20060307-36',
0,
1,
1,
'A74',
1,
2,
CAST(NULL AS bigint),
CAST(NULL AS bigint),
2,
5,
'2006-03-01 11:11:07.11111',
'2006-03-01 11:21:18.22222',
CAST(NULL AS varchar(1))
);
SQL0104N An unexpected token "(" was found following "DESCRIPTION) VALUES".
Expected tokens may include: "<table_value_constructor_list>".
SQLSTATE=42601
Dave Hughes
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.
--
Ian Boyd
> 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).
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.
Ian Boyd
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 +2147483647
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;
Serge Rielau
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
> 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).
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
>> PS: I find this thread quite interesting actually.
> In a morbid train-wreck sorta way?
I sent you an email to ian@..., please let me know if that's the right
email address.
W.r.t. walking a line, it would help to have less repetition.
That can take on the smell of "rubbing it in" which is a property of a
troll.
This group is generally quite friendly compared to others where RTFM is
the standard answer for any beginner question an critique is punishable
by personal attacks.
Cheers
Serge
Jeroen van den Broek
"Ian Boyd" <ian.ms...@avatopia.com> schreef in bericht
news:dunei...@enews3.newsguy.com...
[..]
Maybe Brian was referring to (an older version of) the SQL Reference for DB2
on zOS ("Mainframe").
The current (v8) version for that book contains the following TOC:
SQL Reference
Contents
About this book
Summary of changes to this book
Chapter 1. DB2 concepts
Chapter 2. Language elements
Chapter 3. Functions
Chapter 4. Queries
Chapter 5. Statements
Chapter 6. SQL control statements
Appendix A. Limits in DB2 UDB for z/OS
Appendix B. Reserved schema names and reserved words
Appendix C. Characteristics of SQL statements in DB2 UDB for z/OS
Actions allowed on SQL statements
SQL statements allowed in external functions and stored procedures
SQL statements allowed in SQL procedures
Appendix D
....
Appendix H. Sample user-defined functions
Notices
Glossary
Bibliography
Index
Readers' Comments -- We'd Like to Hear from You
This book, as all other ones for DB2 v8 for z/OS can be found here:
http://www-306.ibm.com/software/data/db2/zos/v8books.html
If you want similar documentation for DB2 on Linux, Unix and Windows you
have to go here:
http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html
where you will find the SQL Reference (Vol 1 and 2) you already have.
Next to those however, you may want to take a look at the Command Reference.
The Master Index may also be usefull for determining which document you need
for a specific purpose.
HTH.
--
Jeroen
Jeroen van den Broek
"Jeroen van den Broek" <jer...@NOSPAM.demon.nl> schreef in bericht
news:120usjk...@corp.supernews.com...
>
[..]
>
> If you want similar documentation for DB2 on Linux, Unix and Windows you
> have to go here:
> http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html
> where you will find the SQL Reference (Vol 1 and 2) you already have.
> Next to those however, you may want to take a look at the Command
> Reference.
> The Master Index may also be usefull for determining which document you
> need for a specific purpose.
>
More specific w.r.t. the Command Reference:
Chapter 4. Using command line SQL statements
--
Jeroen
Dave Hughes
> > Ian, what tool are you using. This works for me using CLP
> > Please clarify your environment.
>
> i've tried a couple:
>
> - Microsoft ADO using the IBM DB2 driver for ODBC
This is usually the best option I've found -- the DB2 ODBC driver is
very feature-complete (not surprising given that the DB2 CLI basically
*is* ODBC)
> - Microsoft ADO using the IBM DB2 OLEDB Provider
Generally, I'd avoid this one. For some reason, the DB2 OLEDB provider
lacks some things. For example, I've found in the past that the
meta-data retrieval calls don't work with the native DB2 OLEDB
provider, while they will if you use the DB2 ODBC driver via the MS
OLEDB ODBC provider. Mind you, that was a while ago -- might be fixed
in more recent versions.
> - 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 =
Hmmm, using = as a statement terminator is probably a bad idea (given
the ambiguity). I'd recommend @ (which seems to be an accepted
standard) or ! as I don't think either appear anywhere in the DB2
grammar (well, that's not strictly true for ! but it's only used for
some backward compatibility operators if I recall correctly).
> );=
Hmmm ... you shouldn't need those CASTs around the NULLs, not in an
INSERT statement anyway. Let me just try it:
db2 => CREATE TABLE TEST (
db2 (cont.) => A INTEGER DEFAULT NULL,
db2 (cont.) => B BIGINT DEFAULT NULL,
db2 (cont.) => C VARCHAR(1) DEFAULT NULL
db2 (cont.) => );
DB20000I The SQL command completed successfully.
db2 => INSERT INTO TEST (A, B, C) VALUES
db2 (cont.) => (1, 2, NULL),
db2 (cont.) => (2, NULL, 'A'),
db2 (cont.) => (NULL, NULL, NULL);
DB20000I The SQL command completed successfully.
db2 => SELECT * FROM TEST;
A B C
----------- -------------------- -
1 2 -
2 - A
- - -
3 record(s) selected.
Yup, works for me without CASTs. That's because the data type can be
inferred from the type of the target column in this case. That said,
DB2 does require a CAST around NULLs in certain places. For example,
consider a SELECT:
db2 => SELECT NULL, B, C FROM TEST;
SQL0206N "NULL" is not valid in the context where it is used.
SQLSTATE=42703
db2 => SELECT CAST(NULL AS INTEGER), B, C FROM TEST;
1 B C
----------- -------------------- -
- 2 -
- - A
- - -
3 record(s) selected.
In this case it's because a NULL on its own has no datatype, and each
column in a query result *must* have a datatype. Ergo, the NULL must be
cast to some datatype in this particular case.
Incidentally, this often causes confusion with the set operators
(UNION, INTERSECT and EXCEPT). For example (again using the TEST table
from above):
db2 => SELECT A, B, C FROM TEST
db2 (cont.) => UNION
db2 (cont.) => SELECT NULL, B, C FROM TEST;
SQL0206N "NULL" is not valid in the context where it is used.
SQLSTATE=42703
db2 => SELECT A, B, C FROM TEST
db2 (cont.) => UNION
db2 (cont.) => SELECT CAST(NULL AS INTEGER), B, C FROM TEST;
1 B C
----------- -------------------- -
2 - A
- - A
1 2 -
- 2 -
- - -
5 record(s) selected.
One could argue that, in this case DB2 ought to be able to figure out
that the NULL in the query on the right-hand side of the UNION should
be implicitly cast to an INTEGER as that is the datatype of the first
column in the query on the left-hand side of the UNION.
However (I suspect) the order of execution doesn't allow for this. In
other words, DB2 first attempts to evaluate the left-hand and
right-hand queries, then attempts to evaluate the UNION operation.
Because the right-hand query can't be evaluated, the statement fails
(before ever getting to the UNION). Think about it like a mathematical
evaluation, and it makes sense:
(expression1) + (expression2)
Despite the + being infix here (like the UNION operator in the queries
above), expression1 and expression2 must obviously be evaluated first
before the addition can be evaluated.
[snip]
> > PS: I find this thread quite interesting actually.
> In a morbid train-wreck sorta way?
>
> i'm approaching DB2 from a very high-level (and overview if you
> will). It's not like i don't understand relational databases. i think
> i am very used to SQL Server, where it is very powerful and yet very
> friendly. If you accept that most enterprise class RDBMS are of a
> similar feature set, the different between is semantics, and tools,
> and language.
>
> i'm walking a fine line here. There are things in DB2 that make no
> sense. They are, quite plainly, non-sensical. i try to explain what i
> think the vision and philosophy that DB2 has for doing things - as
> though there was an all-encompassing grand vision for everything. But
> things are not that way. Due to historical design decisions,
> backwards compatiblity, forward compatiblity, standards
> compatability, etc things can make little sense to an outside
> observer. Which is fine, as long as i can quickly find the list of
> all these different design paradigms. But many zealots will take my
> confusion and frustration of the scattered design as an insult, and
> thats a tough needle to thread, especially in a DB2 newsgroup - where
> i am stating up front i come from Microsoft SQL Server, and a lot of
> people in here have used the word "Micro$oft".
Hmm, I'd say there's very little in DB2 that's non-sensical, and
generally I do find there is a philosophy behind DB2's way of doing
things. But as I mentioned in another post, I'm beginning to understand
just how alien it must seem when "switching philosophies" so to speak.
The VALUES expression, to me, is a good example of "DB2's philosophy".
A number of other databases use
SELECT <value>, <value>, ...
as a way of generating a row on an adhoc basis. However, it's unclear
with this syntax how one could generate an adhoc *table*. As someone
else mentioned, in SQL "everything is a table" (a single row is just a
special case of a table, and a single value is another such special
case). Extending this syntax to generate multiple rows like so:
SELECT (<value>, <value>, ...), (<value>, <value>, ...)
*is* non-sensical as one cannot specify multiple rows in the first part
of a SELECT expression against a table. Therefore using SELECT in this
fashion violates the principal that "everything is a table". Hence, DB2
uses the VALUES expression
VALUES (<value>, <value>, ...), (<value>, <value>, ...)
to generate an adhoc table. This, in turn, fits in neatly with the
INSERT statement as now the general syntax for INSERT can be:
INSERT INTO <table> (<column>, <column>, ...) <data>
Where <data> is some expression that returns a table such as a SELECT
expression, or a VALUES expression. Therefore, one can insert multiple
rows into a table with:
INSERT INTO mytable (cola, colb, colc)
VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
or
INSERT INTO mytable (cola, colb, colc)
SELECT cola, colb, colc FROM myothertable
This is also why I frown upon the syntax MySQL uses for INSERT:
INSERT INTO mytable SET cola=vala, colb=valb, ...
(although admittedly MySQL can also use the standard VALUES syntax). I
suspect they introduced this other syntax to make INSERT look more like
UPDATE but it doesn't "fit" when you start thinking about "everything
is a table".
Speaking of UPDATE, the UPDATE statement has never really "fit" the
"everything is a table" philosophy particularly well. It seems like the
new MERGE statement (introduced in either SQL-99 or SQL-2003, I forget
which) is an attempt to address this.
But enough philosophical ramblings... Suffice it to say that there does
appear (to a long-time user) to be a "grand design" to the way DB2's
SQL grammar is structured.
> 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 suspect you'll be preaching to the choir with regard to the graphical
tools. I can't remember a single occassion of someone *praising* the
graphical tools! No design philosophy here, or at least none I've ever
figured out.
Personally, I stick with the command line (combined with a decent shell
like bash under Linux it's very powerful, though I'll admit that's
little comfort to anyone not wishing to use a command line for whatever
reason).
> i'm walking a fine line here: of trying to extract information from
> the people in the know, without touching a nerve.
You're doing a good job so far I'd say.
--
Dave Hughes
> Dave Hughes wrote:
> > 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).
> 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
db2 => select /* hello */ 1 from sysibm.sysdummy1;
SQL0104N An unexpected token "select /* hello */ 1" was found
following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
SQLSTATE=42601
$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08020"
with level identifier "03010106".
Informational tokens are "DB2 v8.1.0.64", "s040812", "MI00086", and
FixPak "7".
Product is installed at "/opt/IBM/db2/V8.1".
Hmmm, time to upgrade the fixpak I guess! (I don't usually bother with
every fixpak on this box as it's just a test box not accessible outside
the local LAN, so security's not a big concern).
Cheers,
Dave.
--
Mark A
news:440f7a23$0$70294$ed26...@ptn-nntp-reader03.plus.net...
> Hmmm, time to upgrade the fixpak I guess! (I don't usually bother with
> every fixpak on this box as it's just a test box not accessible outside
> the local LAN, so security's not a big concern).
>
>
> Cheers,
>
> Dave.
>
Aside from security fixes, there have been about 1500 other APAR's fixed
since then (assuming that you install FP11).
Gert van der Kooij
@avatopia.com) says...
> 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
Sorry, I don't agree. I do respond because it contains errors.
>
>
> 7. Every DB2-SQL Statement must end with a semi-colon (;)
>
> 8. You cannot give DB2 some arbitrary SQL to run.
This isn't right, it depends. If you put multiple commands in one
file you need to seperate them.
If you put the following commands in one file you can run them
without a semi-colon _as_long_as_you_put_one_statement_on_one_line.
The end-of-line is the default statement delimiter.
empl_test.sql contains:
connect to sample
select * from employee
insert into employee (<column names>) values (< values>)
connect reset
run it from the Command Window with:
db2 -f empl_test.sql
If you want to a statement to span multiple lines you need to
seperate them by a command delimiter. The default delimiter is the
semi-colon.
connect to sample;
select *
from employee
where EMPNO > 10;
connect reset;
use the '-t' option to run it from the Command Window with:
db2 -tf empl_test.sql
If you want to create a trigger or procedure you need to seperate the
statements within them with a semi-colon. Because of that the 'create
function' and 'create trigger' statements needs to be seperated by
another delimiter.
To create your trigger using an input file you can do the following:
trg_define.sql contains:
connect to <yourdb> @
END@
connect reset@
and run it from the command window with the following command
db2 -t@ -f trg_define.sql
The example provided by you contains an error, it's missing the
finishing ')' in the CREATE TABLE statement. The statements below can
be run at once without a problem.
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;
If you put them in a file multiple_statements.sql it can be run at
once with the command:
db2 -tf multiple_statements.sql
Hope this helps.
Regards, Gert
Brian Tkatch
Version 8 for LUW.
Or at least that's what they have on the corparate intranet.
B.
Brian Tkatch
with one button marked "OK".
DB2 is an IBM product, and has a message and an error code for
*everything*. It's part and parcel of IBM to document everything.
(Worked beautifully in OS/2.)
As for NULLs, i have the same gripe.
As for "make it works anyway, cus i cliked "save". I hope you are never
my DBA. :P
B.
Brian Tkatch
Obviously the software should do some things, just don;t take my power
to change them away.
What you said.
B.
Pierre Saint-Jacques
responders have been.
For myself, this has been quite instructive(?) (It's your language anyway).
I've learned a lot and I've rarely met an OP that has been as careful as he
has been with knowing as much as he has!
Thnaks, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Dave Hughes" <da...@waveform.plus.com> a écrit dans le message de news:
440f75d0$0$6964$ed26...@ptn-nntp-reader02.plus.net...
....snip>
> [snip]
>> > PS: I find this thread quite interesting actually.
>> In a morbid train-wreck sorta way?
....snip
Knut Stolze
> Unless someone changed the query to
> SELECT 3.14159, 1+2+3, 4+5+6
I guess you mean VALUES 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.
True. But my answers would be
(a) Why is the additional column not appended? Then you don't break
anything.
(b) I don't think this is a big deal in reality. For several decades now
the the fetching of values from a result set is based on the order of the
columns. So far there hasn't been a great outrage on this. Granted, the
fetching using column names is a nice feature.
(c) You could always wrap the VALUES into a SELECT to give the column names
SELECT *
FROM TABLE ( VALUES ( 3.14159, 1+2+3, 4+5+6 ) ) AS t(a, b, c)
or use sysibm.sysdummy1 (or DUAL in Oracle).
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Knut Stolze
> 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.
I also respond because there are some more things not correct. So I'd
rather correct it before someone else gets the wrong ideas in the future.
(I'm wondering, don't you have a database (not necessarily relational) for
such things?)
This is because the special registers are considered to be not
deterministic. A different user connecting to the system implies different
values for those defaults. So it _is not_ deterministic. So use a trigger
instead.
You could argue that the same holds for CURRENT TIMESTAMP and I would agree.
However, the user cannot influence the current timestamp, so DB2 can safely
determine it when a row is inserted.
(1) I would throw away the procedural logic for the "SavedUserID" and do
this purely in SQL as we discussed before.
(2) You should switch to a FOR EACH ROW trigger as Serge explained.
> 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.
Not true. DB2 does support comments:
$ cat trig.sql
create trigger a_ins after insert on a
referencing new as n
for each row
-- comment 1
insert into b
-- comment 2
values (n.a);
$ db2 -t -f trig.sql
DB20000I The SQL command completed successfully.
$ db2 "select text from syscat.triggers where trigname = 'A_INS'"
----------------------------------------------------------
create trigger a_ins after insert on a
referencing new as n
for each row
-- comment 1
insert into b
-- comment 2
values (n.a)
> 3. DB2 cannot implicitly cast a NULL to any data type.
It does if it can derive the data type, for example from the column name or
by other means like here:
VALUES CASE
WHEN 1 = 0
THEN 123
ELSE NULL
END
The "123" tells DB2 the data type for the CASE expression and DB2 will
implicitly use this type for the (untyped) NULL.
Only if the type cannot be derived, you have to explicitly cast the NULL.
<Celko-mode>NULL is not a value.</celko mode>
Not true. See above.
> 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.
Not true. See above.
> ADDITIONAL NOTES:
>
> 6. Many other SQL constructs are not understood by DB2.
>
> SELECT 'Hello, world!';
This is not a SQL construct. ;-)
> 7. Every DB2-SQL Statement must end with a semi-colon (;)
Not true. You can choose your statement terminator freely, and it can even
be the end of line.
> 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).
Not true. You have to separate the statements with the statement
terminator.
> The major cavaet with this limitation is that something like the following
> is invalid:
>
> CREATE TABLE Users (
> UserID int,
> Username varchar(50);
Closing ')' is missing.
> 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;
$db2 -t -vf a
CREATE TABLE Users ( UserID int, Username varchar(50) )
DB20000I The SQL command completed successfully.
INSERT INTO Users (UserID, Username) VALUES (1, 'Ian')
DB20000I The SQL command completed successfully.
INSERT INTO Users (UserID, Username) VALUES (2, 'Brian')
DB20000I The SQL command completed successfully.
INSERT INTO Users (UserID, Username) VALUES (3, 'Knut')
DB20000I The SQL command completed successfully.
INSERT INTO Users (UserID, Username) VALUES (4, 'Serge')
DB20000I The SQL command completed successfully.
SELECT * FROM Users WHERE Username = 'Knut'
USERID USERNAME
----------- --------------------------------------------------
3 Knut
1 record(s) selected.
DROP TABLE Users
DB20000I The SQL command completed successfully.
--
Knut Stolze
> Hmmm, using = as a statement terminator is probably a bad idea (given
> the ambiguity). I'd recommend @ (which seems to be an accepted
> standard) or ! as I don't think either appear anywhere in the DB2
> grammar (well, that's not strictly true for ! but it's only used for
> some backward compatibility operators if I recall correctly).
The ! can (still) be used to call unregistered stored procedures (the
DB2DARI) style. But that style is deprecated as of V8.
> INSERT INTO mytable (cola, colb, colc)
> SELECT cola, colb, colc FROM myothertable
You can even do this:
INSERT INTO table(a, b. c)
VALUES ( SELECT col1, col2, col3
FROM other_table
WHERE ... )
But beware. There is a fine difference to:
INSERT INTO table(a, b. c)
SELECT col1, col2, col3
FROM other_table
WHERE ...
Namely, if the WHERE clause identifies no rows, the 2nd statement will not
insert any rows - but the 1st statement will (attempt to) insert a row with
all colums set to NULL.
> Speaking of UPDATE, the UPDATE statement has never really "fit" the
> "everything is a table" philosophy particularly well. It seems like the
> new MERGE statement (introduced in either SQL-99 or SQL-2003, I forget
> which) is an attempt to address this.
SQL-2003
> I can't remember a single occassion of someone *praising* the
> graphical tools!
I do remember someone saying that the Control Center was good. ;-))
Ian Boyd
> http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html
> where you will find the SQL Reference (Vol 1 and 2) you already have.
Yes.
> Next to those however, you may want to take a look at the Command
> Reference.
No.
> The Master Index may also be usefull for determining which document you
> need for a specific purpose.
i see my problem now. This "Master Index" contains an index into all other
documents. If you click on an index entry, i will load that PDF and jump to
the page. In my case, it contains index entries that the target PDF itself
doesn't include. My mistake was trying to use the index in "SQL Reference
Volume 2 Version 8.2". That was my mistake, that was folly.
i was looking for "DECLARE", when instead i should be looking for "Compound
SQL (Procedure)". Then you scan that chapters' SQL Diagrams for one that
contains the word DECLARE, which in my case is "SQL-Variable-declaration."
Then scroll down a few pages, looking for "SQL-Variable-declaration". Then,
ping-pong between the explanation and the SQL diagram in order to try to
infer what the syntax is. Then scroll randomly a few pages forward and a few
pages back hoping for an example - and in this case there is one.
Intuitive.
i am SO ready for IBM.
Ian Boyd
"Knut Stolze" <sto...@de.ibm.com> wrote in message
news:duoq7u$gcs$1...@lc03.rz.uni-jena.de...
> Ian Boyd wrote:
>
>> Unless someone changed the query to
>> SELECT 3.14159, 1+2+3, 4+5+6
>
> I guess you mean VALUES 3.14159, 1+2+3, 4+5+6
Yes, i slipped, i was focused on the concept. i should have written:
SELECT 3.14159, 1+2+3, 4+5+6 FROM sysibm.sysdummy1
> True. But my answers would be
> (a) Why is the additional column not appended? Then you don't break
> anything.
Cause i didn't.
> (b) I don't think this is a big deal in reality. Granted, the
> fetching using column names is a nice feature.
From almost day one, i got burned fetching column values by ordinal. It's
just a bad idea. Yes i could do it, and i could be forced to keep the
ordinal location of every field in every table, result set, or query. But it
makes it easier for separate systems to talk to each other if each side
talks to the other through named columns. It makes systems much more
resiliant to changes. It's just good practice.
> For several decades now
> the the fetching of values from a result set is based on the order of the
> columns.
They have databases on computers now. It's not a huge performance penalty to
lookup columns by name. It just isn't. No new systems (i.e. those not
written in the last few decades) lookup fields by ordinal. No web-sites do
it, no desktop applications do it.
>So far there hasn't been a great outrage on this.
The outrage comes when someone tries to maintain legacy systems, for example
removing legacy columns that are no longer used for the business. You delete
the column, and every application that depends on column's by ordinal
location break. So now we're stuck with junk because someone never bothered
to make their system flexible and smart.
Dave Hughes
Knut Stolze wrote:
> Ian Boyd wrote:
>
[snip]
>
> > 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.
>
> Not true. DB2 does support comments:
>
> $ cat trig.sql
> create trigger a_ins after insert on a
> referencing new as n
> for each row
> -- comment 1
> insert into b
> -- comment 2
> values (n.a);
>
> $ db2 -t -f trig.sql
> DB20000I The SQL command completed successfully.
I think Ian might have written this before discovering (in a separate
note somewhere in this thread) that something on the client side is
stripping line breaks causing problems with line comments.
>
[snip]
> > 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).
>
> Not true. You have to separate the statements with the statement
> terminator.
Actually, Ian is correct in a limited sense here. While it is certainly
true that one can write a script containing several statements
(separated by whatever means one wishes) and give this to the CLP for
execution, this still doesn't mean you can execute multiple
statements...
In this particular case, the CLP will break the script up into
individual statements and send each statement separately to the server.
It doesn't send the entire script en-masse to the server.
Likewise, if you are writing an application that connects to DB2 via
(for example) ODBC, you cannot send multiple SQL statements to the
server in a single SQLExecute or SQLExecDirect call. That said, I'm
reasonably sure you're not meant to be able to do such a thing anyway.
Quoting from Microsoft's own documentation of the SQLExecDirect call in
the ODBC API:
The application calls SQLExecDirect to send _an_SQL_statement_ to the
data source
No mention of multiple statements there. If one can do this with MS SQL
Server (?), it's certainly non-standard behaviour, and shouldn't be
relied upon to be implemented by other databases.
Then again, I might be misinterpreting Ian's intent here.
Dave.
--
Ian Boyd
>> character" changed to =
>
> Hmmm, using = as a statement terminator is probably a bad idea (given
> the ambiguity).
Sorry, sorry, sorry. That didn't translate through the news server
properly. The character i changed it to was the
"Vulgar Fraction One Half"
U+00BD
Alt+0189 on the keyboard if you're using Windows
"½" <-- It shows up as 1/2 in Outlook Express's editor :)
> Hmmm ... you shouldn't need those CASTs around the NULLs, not in an
> INSERT statement anyway. Let me just try it:
You do - sometimes.
Try something of the form:
INSERT INTO TestTable (Name, Address, Phone)
SELECT fname, addr1, NULL FROM Customers
From my post at 20060307 4:15pm i say:
<quote>
> 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."
</quote>
And as Serge responds:
<quote>
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.
</quote>
Now in this case, DB2 actually can infer the type - it just isn't looking
far enough ahead. So rather than risk it sometimes working and sometimes
not, i will just adhere to the rule that you should always do it. It's just
safer that way.
> In this case it's because a NULL on its own has no datatype, and each
> column in a query result *must* have a datatype. Ergo, the NULL must be
> cast to some datatype in this particular case.
No reason DB2 can't just pick a type. If the extent of my statement was:
SELECT NULL AS SomeNullValue FROM sysibm.sysdummy1
Then just go ahead and make it an integer. Nobody's gonna care.
> Incidentally, this often causes confusion with the set operators
> (UNION, INTERSECT and EXCEPT). For example (again using the TEST table
> from above):
>
> db2 => SELECT A, B, C FROM TEST
> db2 (cont.) => UNION
> db2 (cont.) => SELECT NULL, B, C FROM TEST;
> SQL0206N "NULL" is not valid in the context where it is used.
> SQLSTATE=42703
> One could argue that, in this case DB2 ought to be able to figure out
> that the NULL in the query on the right-hand side of the UNION should
> be implicitly cast to an INTEGER as that is the datatype of the first
> column in the query on the left-hand side of the UNION.
Yes, one would :)
> However (I suspect) the order of execution doesn't allow for this. In
> other words, DB2 first attempts to evaluate the left-hand and
> right-hand queries, then attempts to evaluate the UNION operation.
> Because the right-hand query can't be evaluated, the statement fails
> (before ever getting to the UNION). Think about it like a mathematical
> evaluation, and it makes sense:
>
> (expression1) + (expression2)
>
> Despite the + being infix here (like the UNION operator in the queries
> above), expression1 and expression2 must obviously be evaluated first
> before the addition can be evaluated.
That sounds like a technical proglem, that need a technical solution.
> Hmm, I'd say there's very little in DB2 that's non-sensical, and
> generally I do find there is a philosophy behind DB2's way of doing
> things. But as I mentioned in another post, I'm beginning to understand
> just how alien it must seem when "switching philosophies" so to speak.
It's not so much switching that is a problem, or maybe it is. Maybe it is
the design standard itself that is weird. But there are things that "work
there", but "don't work there". And often-times the answer as to why it
behaves that way is:
"that's the standard"
"by design"
But as a human using the system there are things that just shouldn't be that
way. If the standard says it, maybe the standard needs to be revisited. If
it's a technical limitation, then it needs to be overcome. If what i want
makes no logical sense, then there will be a logical reason why. But if it
turns out that
"i want to do this, i can't think of any reason why your product can't
shouldn't do it."
"Well, we don't, and we have no plans to do it that way."
"Then can you point to me to a competitors product that will do this?"
On some level, IBM is writing software to be used by developers. It might
not be a bad idea to make their jobs easier, rather than harder.
> The VALUES expression, to me, is a good example of "DB2's philosophy".
> A number of other databases use
>
> SELECT <value>, <value>, ...
>
> as a way of generating a row on an adhoc basis. However, it's unclear
> with this syntax how one could generate an adhoc *table*.
Yes, i agree. VALUES in an expression that has no equivalent in SQL Server.
And IBM has added value to their product with this innovation. And other
RDBMs would do well to steal the idea :)
> As someone
> else mentioned, in SQL "everything is a table" (a single row is just a
> special case of a table, and a single value is another such special
> case).
Not everything is a table. USER is a special register. There are plenty of
"special registers". i guess i would need to ask, since i am speaking
without knowing... Is the following valid:
ALTER TABLE MyTable ALTER COLUMN Tag SET WITH DEFAULT asdfasdf ;
how about
ALTER TABLE SUPERDUDE.AUDIT_LOG ALTER COLUMN TAG SET WITH DEFAULT USER ;
how about
ALTER TABLE SUPERDUDE.AUDIT_LOG ALTER COLUMN TAG SET WITH DEFAULT (select
username from employees fetch first 1 rows only) ;
In the first case i can default the value of a column to a string, and the
string is not a table.
In the second case, i want to default it to a special register, a special
register is not a table. But if i wanted to read the value of the special
register, i have to select it from a dummy table, or turn it into a table
with VALUES.
In the third case, i literally want the value from a table.
But in the first two, alter table does not, and is not, taking a table. But
supposedly everything is a table. So i'll try the 3rd case where i literally
do return a table, and it's invalid. Again, maybe i got the syntax wrong,
and maybe the third case can be done. But the violated concept is that USER
is sometimes a table and sometimes not. And sometimes i have to access it as
a table, and sometimes i don't. So if USER can be read without return it as
a table, then it can be read without needing a table. So then why can't i
read the value of 'asdfasdf' without having to use a table? Obviously it can
be done somewhere.
> Extending this syntax to generate multiple rows like so:
> SELECT (<value>, <value>, ...), (<value>, <value>, ...)
> *is* non-sensical
Yeah, that's silly syntax
> I suspect you'll be preaching to the choir with regard to the graphical
> tools. I can't remember a single occassion of someone *praising* the
> graphical tools! No design philosophy here, or at least none I've ever
> figured out.
Right now, in this office, it's more of "Look at this user interface
design."
And they then point out what the graphical tool is doing, and we all can
silently, immediatly and intuitivly see how bad the design is, and we all
know what it should be doing instead.
> You're doing a good job so far I'd say.
i'm slipping here and there. i apologize to those on those other threads of
this post. e.g.
"ComicBookGuy"
"columns by ordinal"
It's difficult to be frustrated and pleasent at the same time; especially
when i also turn around and vent off to colleagues here so easily :)
Ian Boyd
> down a set of odd limitations.
But an important thing that perhaps IBM itself should be aware of, is that
this was stuff that worked in Microsoft SQL Server.
Yes, there are workaround to it, but i would have that locked into the table
itself, and implementers of my audit log triggers not have to deal with
CURRENT USER/HOSTNAME/APPLNAME.
But take note that a competitors product does this fine. Because a
competitors product has other limitations is not an excuse not to bother
implementing them in yours.
Ian Boyd
Nobody here will write queries on all one line. It's jut a practical thing.
> 'create function' and 'create trigger' statements needs to be
> seperated by another delimiter.
> connect to <yourdb> @
i am already connected using ADO. The "connect to <yourdb> @" is invalid
DB2-SQL.
If i need to separate statements, i'm going to have to do it manually.
Knut Stolze
>> As someone
>> else mentioned, in SQL "everything is a table" (a single row is just a
>> special case of a table, and a single value is another such special
>> case).
>
> Not everything is a table. USER is a special register. There are plenty of
> "special registers".
When you use USER as an expression in a SQL statement, its value is used.
Where the value originates from is not of interest and the "everything is a
table" also applies to other scalar values, for example constants or values
recieved from an application via host-variables.
A single value (also called scalar value) is the same as a table with one
row and one column. If you try to think that way, statements like
SET (a, b, c) = (1, 2, 3)
are much easier to understand, I'd say.
Ian Boyd
> rather correct it before someone else gets the wrong ideas in the future.
> (I'm wondering, don't you have a database (not necessarily relational) for
> such things?)
Google groups! My database search will be "ian boyd db2 create table", and
brought right to valid DB2-SQL example to create a table, etc.
> This is because the special registers are considered to be not
> deterministic. A different user connecting to the system implies
> different
> values for those defaults. So it _is not_ deterministic. So use a
> trigger
> instead.
> You could argue that the same holds for CURRENT TIMESTAMP and I would
> agree.
> However, the user cannot influence the current timestamp, so DB2 can
> safely
> determine it when a row is inserted.
Well...my post isn't wrong. DB2 can't do it. Maybe it has a reason for it,
maybe not. Either way, it can't be done that way.
> (1) I would throw away the procedural logic for the "SavedUserID" and do
> this purely in SQL as we discussed before.
> (2) You should switch to a FOR EACH ROW trigger as Serge explained.
It's also a reference of how to do things. i'll will follow your suggestions
in the final implementation.
> Not true. DB2 does support comments:
Turns out that the Windows ODBC or OLEDB provider strip CRLF's. So this
destroys inline comments. You can argue that the driver is not DB2, but in
the end i cannot use inline comments. If IBM will update the drivers so it
works, we can then use them - but now we cannot.
>> 3. DB2 cannot implicitly cast a NULL to any data type.
> It does if it can derive the data type, for example from the column name
> or
> by other means like here:
In this case DB2 *should* be able to derive the data type, but won't:
INSERT INTO Users (Username, Address)
SELECT uid, NULL FROM Employees
In this case it does
INSERT INTO Users (Username, Address)
VALUES ('Ian', NULL)
So if you ever deal with nulls, it's safer to just cast it. Save time by not
going round and round trying to guess what db2 will and won't do. That's my
point there.
> <Celko-mode>NULL is not a value.</celko mode>
NULL is the absense of database. If i personally don't think DB2 should
enforce a type on nothing. But here we get into deep philosophical ideas
(What does nothing sound like? It can sound like whatever you want. Ooooo)
> Not true. See above.
See above :)
> Not true. See above.
See above :)
> This is not a SQL construct. ;-)
Change your definition :)
>> 7. Every DB2-SQL Statement must end with a semi-colon (;)
> Not true. You can choose your statement terminator freely, and it can
> even
> be the end of line.
Well *i* can't.
>> 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).
>
> Not true. You have to separate the statements with the statement
> terminator.
See above :)
>> The major cavaet with this limitation is that something like the
>> following
>> is invalid:
>>
>> CREATE TABLE Users (
>> UserID int,
>> Username varchar(50);
>
> Closing ')' is missing.
Pwned.
Brian Tkatch
Not infer. the diagram is clear, according to the guidelines presented
in "About this book" subsection "How to read the syntax diagrams".
You may not like the IBM diagrams, but they are indeed very clear.
B.
Brian Tkatch
>just a bad idea. Yes i could do it, and i could be forced to keep the
>ordinal location of every field in every table, result set, or query.
Not in the TABLE, in the query. And the query changes per application,
so no problem. If the query is in a stored PROCEDURE, and outputs a
result set, it is best to use the VALUES inside a SELECT statement as
Knut pointed out.
B.
Gert van der Kooij
@avatopia.com) says...
Which tool are you using to run your SQL?
I guess I'm missing the overall picture, did you post it somewhere in
this thread?
Ian Boyd
The same diagrams are in the ANSI spec. They are very clear once you already
know the syntax.
Ian Boyd
> so no problem.
Not when a query is the query was written in one application and used by
another.
> If the query is in a stored PROCEDURE, and outputs a
> result set, it is best to use the VALUES inside a SELECT statement as
> Knut pointed out.
How do you give names to the fields returned from a SELECT of a VALUES
table?
Ian Boyd
> row and one column. If you try to think that way, statements like
That doesn't solve the inconsistency of sometimes being able to use scalars
and sometimes not, and sometimes being able to use tables and sometimes not.
i'm less pointing out the "what" of db2 and more of the "why." i'm fighting
less with the 'this is the way it is' and more of the 'why is it this way?'
Ian Boyd
> I guess I'm missing the overall picture, did you post it somewhere in
> this thread?
i'm using a variety of tools in a variety of environments. None of them
involve a command line and runnnig saved files containing SQL statements.
Most involve writing a query in an editor of some sort and running it. Or
then taking that query and embedding it into code. And we prefer our queries
to be nicely formatted and (ideally) commented.
It is safe to say that our tools are Windows desktop machines, Windows
applications, Windows services, Websphere Java Servlets, editors that let us
type in and run SQL.
Ian Boyd
> note somewhere in this thread) that something on the client side is
> stripping line breaks causing problems with line comments.
It is important to note that it is either inside IBM's ODBC driver or IBM's
OLEDB provider. When we use the same tools to send queries to Microsoft's
SQL Server using either Microsoft's ODBC driver or Microsoft's OLEDB
provider, carriage returns and are put onto the ethernet intact. So it is
something to do with DB2. Maybe not the server side, but it's still IBM's
solution. And so, with IBM's solution i generally cannot use comments.
It really is irrelavant if DB2 is choking on it because it received mangled
SQL, IBMs drivers did it. They are DB2 drivers. Hence DB2 did it. If the
roles were reversed, it would be an SQL Server problem.
>> > 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).
>>
>> Not true. You have to separate the statements with the statement
>> terminator.
>
> Actually, Ian is correct in a limited sense here. While it is certainly
> true that one can write a script containing several statements
> (separated by whatever means one wishes) and give this to the CLP for
> execution, this still doesn't mean you can execute multiple
> statements...
Yes. The tools can do me a favor and chop up a bunch of SQL into chunks that
DB2 can manage. It would be much nicer if DB2 could manage all the code at
once. And as we've already seen in other database products, there is no
technical limitation to doing so - just a design one.
So change the design. People can still be free to only send one statement to
DB2 at a time. But now DB2 can also be powerful and intelligent enough to
handle more than one statement.
>Quoting from Microsoft's own documentation of the SQLExecDirect call in
>the ODBC API:
> [snip]
> No mention of multiple statements there.
From the Microsoft Books Online:
<quote>
Batches
A batch is a group of one or more Transact-SQL statements sent at one time
from an application to Microsoft® SQL Server™ for execution. SQL Server
compiles the statements of a batch into a single executable unit, called an
execution plan. The statements in the execution plan are then executed one
at a time.
...
Assume there are 10 statements in a batch. If the fifth statement has a
syntax error, none of the statements in the batch are executed. If the batch
is compiled, and the second statement then fails while executing, the
results of the first statement are not affected because it has already
executed.
</quote>
Here is a phrase that i really take issue with:
> If one can do this with MS SQL
> Server, it's certainly non-standard behaviour, and shouldn't be
> relied upon to be implemented by other databases.
SQL Server is easier to *use* because of it. DB2 is free to keep their
product harder to use.
Pretend it didn't work in SQL Server. Pretend that this wasn't the way it
works. Why shouldn't IBM innovate and include such a feature? Why not look
for reasons to DO new features, rather than looking for reasons NOT to do
new features.
An overarching theme i'm trying to express is usability. Make things more
usable, rather than less. Just because that's they way it is doesn't mean
that's the way it has to be. There are things that SQL Server does that are
completely self-consisent with their own little world of rules - but they're
just stupid. Just make it work! We all know what you were trying to do. Why
don't you do it? Is it a technical limitation? Fix it! Is it new idea that
you can't stick into your model of how the product is supposed to work?
Maybe the model is broken, maybe you need to extend it!
And it's many many things. It's not just defaults on columns. It's not just
implicitly casting. It's not just being able to select a scalar. It's a lot
of things.
Another very very very common example that people ask over and over and over
and OVER in the Microsoft newsgroups:
"How come i can't do:
SET MyVariable = CALL MyStoredProcedure
when my procedure only returns one row and one column?"
We all know what the person was trying to do. We all KNOW it. So, now before
going into any arguments about why what he is doing is wrong, you have to
stop and choose: Are we going to try to helpful, or are we going to be a
hinderance?
Do you want to make that syntax work, but you can't for technical reasons?
Fix them!
Are you going to refuse to allow that syntax on moral grounds (i.e. Celko)?
You can do that. But perhaps another database vendor will incorporate that
functionality into their system, and theirs will become easier to use.
i realize DB2 is trying to follow the standards, and standards are a good
thing. But please don't argue with your users telling them they need to do
things the hard way because it's "standard" or because "we've decided how
you should do things." Innovate!
Dave Hughes
> > > - IBM Command Editor (db2ce.bat) with it's "Statement termination
> > > character" changed to =
> >
> > Hmmm, using = as a statement terminator is probably a bad idea
> > (given the ambiguity).
>
> Sorry, sorry, sorry. That didn't translate through the news server
> properly. The character i changed it to was the "Vulgar Fraction
> One Half" U+00BD
> Alt+0189 on the keyboard if you're using Windows
> "=" <-- It shows up as 1/2 in Outlook Express's editor :)
Argh! I was scratching my head wondering how anyone could be so crazy
as to use equals as a statement terminator, but from experience I
should have known better (being in the UK, I'm used to mail and news
servers translating the British pound sign into =3A or just =)!
> > Hmmm ... you shouldn't need those CASTs around the NULLs, not in an
> > INSERT statement anyway. Let me just try it:
>
> You do - sometimes.
>
> Try something of the form:
> INSERT INTO TestTable (Name, Address, Phone)
> SELECT fname, addr1, NULL FROM Customers
Damn, I'd forgotten about that particular construction.
[snip]
> Now in this case, DB2 actually can infer the type - it just isn't
> looking far enough ahead. So rather than risk it sometimes working
> and sometimes not, i will just adhere to the rule that you should
> always do it. It's just safer that way.
Fair point ... probably a better strategy for someone new to DB2 and
just wishing to "get on with it".
> > In this case it's because a NULL on its own has no datatype, and
> > each column in a query result must have a datatype. Ergo, the NULL
> > must be cast to some datatype in this particular case.
>
> No reason DB2 can't just pick a type. If the extent of my statement
> was: SELECT NULL AS SomeNullValue FROM sysibm.sysdummy1
>
> Then just go ahead and make it an integer. Nobody's gonna care.
Pick an arbitrary data type... Certainly a viable option, but I'm
beginning to wonder...
> > Incidentally, this often causes confusion with the set operators
> > (UNION, INTERSECT and EXCEPT). For example (again using the TEST
> > table from above):
> >
> >db2 => SELECT A, B, C FROM TEST
> >db2 (cont.) => UNION
> >db2 (cont.) => SELECT NULL, B, C FROM TEST;
> > SQL0206N "NULL" is not valid in the context where it is used.
> > SQLSTATE=42703
>
> > One could argue that, in this case DB2 ought to be able to figure
> > out that the NULL in the query on the right-hand side of the UNION
> > should be implicitly cast to an INTEGER as that is the datatype of
> > the first column in the query on the left-hand side of the UNION.
>
> Yes, one would :)
Yes, there's that idea again: "infer the type".
<tangent>
The more I look at SQL (ignoring the hybrid stuff like stored
procedures, triggers, and dynamic compound statements), the more I'm
convinced it's just a set-based pure functional programming language in
disguise (the disguise being a truly bizarre syntax that wraps a whole
bunch of operations up in a single expression called SELECT).
The type inferencing mentioned above is exactly what certain pure
functional languages like ML do. Which makes me wonder, in the case of
picking an arbitrary datatype (as mentioned above), whether it wouldn't
be a good idea to steal an idea from functional programming (e.g.
permit a column in an output set with "arbitrary" type). Yeah, I know
... crazy talk ...
</tangent>
Now returning to our scheduled program...
[snip another type inference example]
>
> > Hmm, I'd say there's very little in DB2 that's non-sensical, and
> > generally I do find there is a philosophy behind DB2's way of doing
> > things. But as I mentioned in another post, I'm beginning to
> > understand just how alien it must seem when "switching
> > philosophies" so to speak.
>
> It's not so much switching that is a problem, or maybe it is. Maybe
> it is the design standard itself that is weird. But there are things
> that "work there", but "don't work there". And often-times the answer
> as to why it behaves that way is: "that's the standard" "by
> design"
>
> But as a human using the system there are things that just shouldn't
> be that way. If the standard says it, maybe the standard needs to be
> revisited. If it's a technical limitation, then it needs to be
> overcome. If what i want makes no logical sense, then there will be a
> logical reason why. But if it turns out that "i want to do this, i
> can't think of any reason why your product can't shouldn't do it."
> "Well, we don't, and we have no plans to do it that way." "Then
> can you point to me to a competitors product that will do this?"
>
> On some level, IBM is writing software to be used by developers. It
> might not be a bad idea to make their jobs easier, rather than harder.
Again, fair point.
>
> > The VALUES expression, to me, is a good example of "DB2's
> > philosophy". A number of other databases use
> >
> > SELECT <value>, <value>, ...
> >
> > as a way of generating a row on an adhoc basis. However, it's
> > unclear with this syntax how one could generate an adhoc table.
>
> Yes, i agree. VALUES in an expression that has no equivalent in SQL
> Server. And IBM has added value to their product with this
> innovation. And other RDBMs would do well to steal the idea :)
Actually, I'm not entirely sure VALUES is a DB2 "innovation" ... it
could just be standard SQL that DB2's implemented and which other
databases have ignored. Anyone know for sure?
> > As someone
> > else mentioned, in SQL "everything is a table" (a single row is
> > just a special case of a table, and a single value is another such
> > special case).
>
> Not everything is a table. USER is a special register. There are
> plenty of "special registers". i guess i would need to ask, since i
> am speaking without knowing... Is the following valid:
>
> ALTER TABLE MyTable ALTER COLUMN Tag SET WITH DEFAULT asdfasdf ;
> how about
> ALTER TABLE SUPERDUDE.AUDIT_LOG ALTER COLUMN TAG SET WITH DEFAULT
> USER ; how about
> ALTER TABLE SUPERDUDE.AUDIT_LOG ALTER COLUMN TAG SET WITH DEFAULT
> (select username from employees fetch first 1 rows only) ;
Granted, I should qualify that over-generalizing statement: "In SQL
everything is a table ... except when it's not" :-)
In general, SQL could be said to have three "meta-types": scalars,
tuples, and sets of tuples (well, strictly speaking, bags of tuples as
one can have a table without a unique key, but we'll stick with "set"
as per convention). However, a scalar is still a special case of a
one-element tuple, and a tuple a special case of a one-element set.
Which is not to say that one can use a set of tuples anywhere a scalar
is required, but you can still see how a scalar is a "special case" of
a set of tuples.
Anyway, to the above statements. The first two are fine (with the
exception that the constant asdfasdf ought to be quoted). Column
defaults must be scalar, and must either evaluate to a constant or use
a special register as their only variable component, and both
expressions meet these criteria. (I recall from a prior post that not
all special registers, i.e. CURRENT CLIENT_APPLNAME etc., can currently
be used in this manner, however that turned out to be because of a lack
of implementation rather than any fundamental technical or theoretical
restriction).
The third statement, however, is problematic. It's not going to work
because it doesn't evaluate to a constant or special register. That's
the primary reason it won't work, but there's another aspect to it that
begs comment:
FETCH FIRST n ROWS is an oft abused modifier. As I understand it
(hopefully, someone more knowledgeable will correct me if I'm wrong
about this), FETCH FIRST n ROWS is meant to be used to grab a quick
sample of data from a potentially long running query.
For example, if you've put together a query that might take a *long*
time to run, and you'd just like to check a sample of the output, you
add FETCH FIRST n ROWS. I don't believe it's meant to be used in the
sense of limiting a query to a single row, or the top n results, or
whatever. This is simply because a table (theoretically at least) has
no intrinsic order and therefore without an ORDER BY clause in the
query one cannot guarantee the result is deterministic. Even with an
ORDER BY clause, this still wouldn't be the "right" to perform these
tasks.
The right way of limiting a query result to one row would be to ensure
that the WHERE clause specifies enough conditions to guarantee a unique
match in the table (i.e. by limiting on all columns of a unique key).
Likewise, the right way of grabbing the top n results would be to use
the RANK() or DENSE_RANK() functions. Unfortunately, the documentation
for the OLAP functions (of which RANK and DENSE_RANK are two) seems to
have been moved somewhere obscure in the Info Center (they're now all
buried in the "Reference / SQL / Language elements / Expressions"
subject). Just search for DENSE_RANK and you should find them easily
enough.
[snip]
> It's difficult to be frustrated and pleasent at the same time;
> especially when i also turn around and vent off to colleagues here so
> easily :)
I recommend several therapeutic sessions of fragging friends and
colleagues in Quake III (honestly, why this isn't considered mandatory
for the mental well-being of office workers is beyond me :-)
Dave.
--
Dave Hughes
I'm pretty sure I've provided the syntax for this in a couple of other
comments, but maybe I didn't indicate it explicitly:
SELECT COLA, COLB, COLC
FROM (
VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)
) AS TEMP(COLA, COLB, COLC)
The "AS" bit is mandatory and provides a name for the table produced by
VALUES. The field aliases in brackets after AS TEMP are optional, but
allow you to assign names to the columns in the VALUES table, which can
then be referenced in the enclosing SELECT statement as above. Though I
could just as easily have done:
SELECT *
FROM (
VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)
) AS TEMP(COLA, COLB, COLC)
HTH,
Dave.
--
Knut Stolze
> The third statement, however, is problematic. It's not going to work
> because it doesn't evaluate to a constant or special register. That's
> the primary reason it won't work,
Actually, standardized SQL allows check constraints to contain sub-selects.
None of the products I know of actually implemented this, however. The
issue is probably (a) starting table scans on an insert is usually not such
a good idea, and (b) if it is really necessary, one could use triggers. So
it boils down to question we always have with new functionality: Who's
gonna pay for it and which feature should be moved back instead?
Ian Boyd
> comments, but maybe I didn't indicate it explicitly:
>
> FROM (
> VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)
> ) AS TEMP(COLA, COLB, COLC)
>
> The "AS" bit is mandatory and provides a name for the table produced by
> VALUES. The field aliases in brackets after AS TEMP are optional, but
> allow you to assign names to the columns in the VALUES table, which can
> then be referenced in the enclosing SELECT statement as above. Though I
> could just as easily have done:
Thank you for that. i do remember seeing the post, but i thought that
the COLA, COLB, COLC were placeholders for something...i dunno.
i didn't appreciate that it is the way to give names to the columns
created by VALUES.
VALUES is a cool construct. i don't know if it's in SQL92/99/etc, but
SQL Server should have something like it - even if i can't think of good
uses for it right now :)
Ian Boyd
>> > Hmmm ... you shouldn't need those CASTs around the NULLs, not in an
>> > INSERT statement anyway. Let me just try it:
>> You do - sometimes.
>> INSERT INTO TestTable (Name, Address, Phone)
>> SELECT fname, addr1, NULL FROM Customers
> Damn, I'd forgotten about that particular construction.
Bam! Boo-yeah!
> Fair point ... probably a better strategy for someone new to DB2 and
> just wishing to "get on with it".
That's the sentiment DB2 needs to ponder on for a little while.
> Pick an arbitrary data type... Certainly a viable option, but I'm
> beginning to wonder...
i mean, it has two choices: go ahead and just do what we all know i was
trying to do, or refuse. No harm in db2 being nicer and easier.
> <tangent>
> The more I look at SQL...
> ...makes me wonder...
> ...whether it wouldn't
> be a good idea to steal an idea from functional programming..
> </tangent>
i originally learned SQL as ANSI-SQL from a very good book explaining
SQL. It wasn't expousing ANSI-SQL over other flavors, just teaching SQL.
SQL is, to me, a very intuitive thing, and playing with the examples from
the book originally in SQL Server 6.5 almost 10 years ago, it was very
easy to use. i am all in favor of adding to SQL intuitive extensions.
> Actually, I'm not entirely sure VALUES is a DB2 "innovation" ... it
> could just be standard SQL that DB2's implemented and which other
> databases have ignored. Anyone know for sure?
i've never seen it through SQL Server 2000. But i hear that MSSQL has been
playing ANSI catchup for the last few years - not that not begin
fully ANSI-compliant is deal-breaker - just bragging rights.
> Granted, I should qualify that over-generalizing statement: "In SQL
> everything is a table ... except when it's not" :-)
Goes back to the "you know what i wanted to do, so why are you
fighting me on this" sentiment.
> The third statement, however, is problematic. It's not going to work
> because it doesn't evaluate to a constant or special register. That's
> the primary reason it won't work, but there's another aspect to it that
> begs comment: FETCH FIRST n ROWS
It was a contrived example, but one that shows how things are not always
consistent.
> I recommend several therapeutic sessions of fragging friends and
> colleagues in Quake III (honestly, why this isn't considered mandatory
> for the mental well-being of office workers is beyond me :-)
Right now it's WoW.
Ian Boyd
<quote>
CHAR
The CHAR function returns a fixed-length character string representation of:
An integer number, if the first argument is a SMALLINT, INTEGER, or BIGINT
Note: The CAST expression can also be used to return a string expression.
</quote>
So of couse this works:
SELECT CHAR(SomeBigIntColumnFieldArmadillo) FROM MyTable
i would prefer to use CAST when doing all casts, and the documentation says
i can. But this fails
SELECT CAST(SomeBigIntColumnFieldArmadillo AS varchar(50)) FROM MyTable
Error: SQL0461N
A value with data type "SYSIBM.BIGINT" cannot be CAST to type
"SYSIBM.VARCHAR".
SQLSTATE=42846
(State:42846, Native Code: FFFFFE33)
Any ideas?
Ian Boyd
> it boils down to question we always have with new functionality: Who's
> gonna pay for it and which feature should be moved back instead?
Perhaps IBM should ask Oracle or MS if they can license their engines.
Jeroen van den Broek
"Ian Boyd" <ian.ms...@avatopia.com> schreef in bericht
news:duq3t...@enews4.newsguy.com...
This Cast is not supported.
Have a look at the SQL Reference Vol1. Chapter 2 Language elements.
There is a paragraph called "casting between data types" (in the version I'm
reading now it starts on page 96).
It contains Table 8: "Supported Casts between Built-in Data Types".
--
Jeroen
Jeroen van den Broek
Ehm, what were you saying about 'walking a fine line' again... ;-)
You really should try to start a thread like this on c.d.o.s., and watch
what happens...
--
Jeroen
Jeroen van den Broek
"Jeroen van den Broek" <jer...@NOSPAM.demon.nl> schreef in bericht
news:12119fi...@corp.supernews.com...
And this is what the Message Reference has to say on this particular error
message:
---------------------------------------
SQL0461N A value with data type "<source-data-type>" cannot be CAST to type
"<target-data-type>".
Explanation: The statement contains a CAST with the first operand having a
data type of "<source-data-type>" to be cast to the data type
"<target-data-type>". This cast is not supported.
User Response: Change the data type of either the source or target so that
the cast is supported. For predefined data types these are documented in the
SQL Reference. For a cast involving a user-defined distinct type, the cast
can be between the base data type and the user-defined distinct type or from
a data type that is promotable to the base data type to the user-defined
distinct type.
sqlcode: -461
---------------------------------------
--
Jeroen
sqlstate: 42846
Ian Boyd
CREATE TABLE #t2 (Weight real) --btw prefix # means temporary. i could also
use a table variable
insert into #t2 (Weight) VALUES (1)
insert into #t2 (Weight) VALUES (1.1)
insert into #t2 (Weight) VALUES (1.01)
insert into #t2 (Weight) VALUES (1.001)
insert into #t2 (Weight) VALUES (1.0001)
insert into #t2 (Weight) VALUES (1.00001)
insert into #t2 (Weight) VALUES (1.000001)
insert into #t2 (Weight) VALUES (1.0000001)
insert into #t2 (Weight) VALUES (1.00000001)
insert into #t2 (Weight) VALUES (1.000000001)
insert into #t2 (Weight) VALUES (1.0000000001)
insert into #t2 (Weight) VALUES (1.00000000001)
insert into #t2 (Weight) VALUES (1.000000000001)
insert into #t2 (Weight) VALUES (1.0000000000001)
insert into #t2 (Weight) VALUES (1.00000000000001)
SELECT
Weight,
CAST(Weight AS varchar(255)) AS [Using CAST],
CONVERT(varchar(255), Weight) AS [Using CONVERT with default formatting],
CONVERT(varchar(255), Weight, 1) AS [Using CONVERT with 8 digits
(scientific notation)],
CONVERT(varchar(255), Weight, 2) AS [Using CONVERT with 16 digits
(scientific notation)]
FROM #t2
ORDER BY Weight DESC
1.1 1.1 1.1 1.1000000e+000 1.100000023841858e+000
1.01 1.01 1.01 1.0100000e+000 1.009999990463257e+000
1.001 1.001 1.001 1.0010000e+000 1.001000046730042e+000
1.0001 1.0001 1.0001 1.0001000e+000 1.000100016593933e+000
1.00001 1.00001 1.00001 1.0000100e+000 1.000010013580322e+000
1.000001 1 1 1.0000010e+000 1.000000953674316e+000
1.0000001 1 1 1.0000001e+000 1.000000119209290e+000
1.0 1 1 1.0000000e+000 1.000000000000000e+000
1.0 1 1 1.0000000e+000 1.000000000000000e+000
1.0 1 1 1.0000000e+000 1.000000000000000e+000
1.0 1 1 1.0000000e+000 1.000000000000000e+000
1.0 1 1 1.0000000e+000 1.000000000000000e+000
1.0 1 1 1.0000000e+000 1.000000000000000e+000
1.0 1 1 1.0000000e+000 1.000000000000000e+000
1.0 1 1 1.0000000e+000 1.000000000000000e+000
May not be perfect, but 99.9% of the time it is all that i needed.
Espeically for money amounts, weights, emperical values.
The best i can come up with for DB2 so far is:
select CAST(CHAR(DECIMAL(HoursSpent_TestField, 31, 7)) AS varchar(50))
from daily_logs
1
-------------------------------------
000000000000000000000003.1415930
000000000000000000000001.0000000
000000000000000000000002.0000000
000000000000000000000003.0000000
000000000000000000000003.0000000
Tomorrow i will figure out how to do LTRIM and RTRIM away "0"
Jeroen van den Broek
"Jeroen van den Broek" <jer...@NOSPAM.demon.nl> schreef in bericht
news:12119fi...@corp.supernews.com...
>
As you can see in that table, a Cast between BIGINT and CHAR is supported,
as is a Cast between CHAR and VARCHAR, so you might try:
SELECT CAST(CAST(SomeBigIntColumnFieldArmadillo AS char(50)) AS varchar(50))
FROM MyTable
--
Jeroen
Will Honea
wrote:
> I'm pretty sure I've provided the syntax for this in a couple of other
> comments, but maybe I didn't indicate it explicitly:
>
> SELECT COLA, COLB, COLC
> FROM (
> VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)
> ) AS TEMP(COLA, COLB, COLC)
What is the semantic difference between your syntax and the form
WITH TEMP(COLA,COLB,COLC) AS
( SELECT .... )
They appear equivilant - am I missing something?
--
Will Honea
Dave Hughes
The WITH ... construct is a "common table expression" which was syntax
introduced in the SQL-99 standard. Functionally, both your version and
my version above are identical.
That said, common table expressions are considerably more powerful than
sub-selects and (in my personal opinion) a lot clearer especially if a
query includes many sub-selects. One of the things you can do with
common table expressions (that you can't do with ordinary sub-selects)
is "recursive" queries. I haven't seen them used much in practice, and
you've got to be a bit careful with (it's possible to make an
infinitely recursive query), but there's some interesting examples in
the DB2 Info Center under Reference / SQL / Queries / Select-statement
(see the "Recursion example: bill of materials" section).
One last thing, using a common table expression instead of a sub-select
in my example, one wouldn't even need a SELECT expression around the
VALUES expression:
WITH TEMP(COLA, COLB, COLC) AS (
VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)
)
SELECT ...
Anyway, thanks for bringing that up - I should have mentioned common
table expressions in my reply, but forgot :)
Dave.
--
Ian Boyd
> There is a paragraph called "casting between data types" (in the version
> I'm reading now it starts on page 96).
> It contains Table 8: "Supported Casts between Built-in Data Types".
i have that table printed out next to me :)
i was hoping the sentence under CHAR saying that CAST *can* be used to
convert numbers to strings trumps the table.