By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

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

P: n/a
i've been thrown into a pit with DB2 and have to start writing things such
as tables, indexes, stored procedures, triggers, etc. The online reference
is only so helpful. The two pdf manuals are only so helpful. Googling is
only so helpful.

So let's start with some simple SQL constructs, that i know so very well in
SQL Server, that seem to be like pulling teeth in DB2.

1. Selecting a value

SQL Server:
SELECT 'Hello, world!'
(1 row(s) affected)

DB2:
SELECT 'Hello, world!'
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"LECT 'Hello, world!'". Expected tokens may include: "<table_expr>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

SELECT 'Hello, world!' FROM SYSIBM.SysDummy1
1 Row(s) affected

Is there a SysDummy2? 3? Why?

1. Declaring a variable

SQL Server:
DECLARE @SavedUserID int
The command(s) completed successfully.

DB2:
DECLARE SavedUserID integer;
Error: SQL0104N An unexpected token "integer" was found following " DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=1. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

2. Setting a variable
SQL Server:
DECLARE @ProcessID int
SET @ProcessID = @spid
or
SET @SavedUserID = (SELECT User_ID FROM Connection_Users WHERE spid =
@@spid)
or (depricated)
SELECT @SavedUseID = UserID FROM Connection_Users WHERE spid = @@spid

DB2:
DECLARE ApplicationID varchar(128) --can't declare variables
SET ApplicationID = Application_ID()
or
DECLARE ApplicationID varchar(128) --can't declare variables
SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);

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

DB2
SELECT Application_ID()
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"ect application_ID()". Expected tokens may include: "<table_expr>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
or
SELECT Application_ID() FROM IBM.SysDummy1 --SysDummy2
or
DECLARE ApplicationID varchar(128) --can't declare variables
SET ApplicationID = (SELECT APPLICATION_ID() FROM SYSIBM.SYSDUMMY1);
SELECT ApplicationID

3. Returning rows from a stored procedure
SQL Server
CREATE PROCEDURE foo AS
SELECT @@spid AS ProcessID

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

Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found
following "ROM SYSIBM.SYSDUMMY1". Expected tokens may include: "JOIN
<joined_table>". LINE NUMBER=7. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE
statement is blank or empty. SQLSTATE=42617
(State:42617, Native Code: FFFFFF3A)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found
following "END P1". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

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

CREATE TRIGGER SUPERDUDE.LI_DAILYLOGS
--"LI_DAILYLOGS" = "LogInsert_DailyLogs" 18 character limit in DB2
AFTER INSERT
ON SUPERDUDE.DAILY_LOGS
REFERENCING NEW_TABLE AS INSERTED
FOR EACH STATEMENT
MODE DB2SQL
BEGIN ATOMIC
-- Load the saved UserID
DECLARE SavedUserID integer;

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

INSERT INTO Audit_Log(
ChangeDate,
RowID,
ChangeType,
-- Username, HostName, AppName,
UserID,
TableName,
FieldName,
TagID,
Tag,
OldValue,
NewValue)
SELECT
getdate(),
i.Daily_Log_ID,
'INSERTED',
-- USER_NAME(), HOST_NAME(), APP_NAME(),
SavedUserID,
'Daily_Logs', --TableName
'', --FieldName
NULL, --TagID
i.Name, --Tag
'', --OldValue
'' --NewValue
FROM Inserted i;
END;

Error: SQL0104N An unexpected token "integer" was found following " DECLARE
SavedUserID". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10. SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0104N An unexpected token "=" was found following "SELECT
SavedUserID ". Expected tokens may include: "<space>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Error: SQL0204N "SUPERDUDE.INSERTED" is an undefined name. SQLSTATE=42704
(State:42704, Native Code: FFFFFF34)
Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)
Mar 7 '06
Share this Question
Share on Google+
138 Replies


P: n/a
Ian Boyd wrote:
Nope, in this case I think it's the thing I mentioned in my other
post: comments can only appear as the first non-whitespace
characters in a line.


i can't get that to work either:

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

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


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

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

--

Mar 8 '06 #51

P: n/a
> 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).


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

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

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

DO NOT RESPOND

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

CREATE TABLE "SUPERDUDE"."AUDIT_LOG" (
"AUDITLOGID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +0
INCREMENT BY +1
MINVALUE +0
MAXVALUE +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;

Mar 8 '06 #53

P: n/a
Dave Hughes wrote:
Serge Rielau wrote:
Working as documented:
http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2.
udb.doc/admin/r0000888.htm

DEFAULT ...

[snip]

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

Support or non support more often than not has one reason: resources.
Quite likely when these registers were added DEFAULT was not part of the
requirement. Given that they have been present for a while no one ever
complained (AFAIK) this might well have been the right decision at the time.
Also note that BEFORE triggers support ALL registers and it is highly
unlikely anyone would want to use default values for those on LOAD.
So, mostly we are talking orthogonality here. A desirable property, but
hard to measure in $$.

Cheers
Serge

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

P: n/a
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 8 '06 #55

P: n/a
Ian Boyd wrote:
PS: I find this thread quite interesting actually.

In a morbid train-wreck sorta way?

Not at all. Calibrating your culture shock.
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 9 '06 #56

P: n/a

"Ian Boyd" <ia***********@avatopia.com> schreef in bericht
news:du********@enews3.newsguy.com...
[..]
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.


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

Mar 9 '06 #57

P: n/a

"Jeroen van den Broek" <je****@NOSPAM.demon.nl> schreef in bericht
news:12*************@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...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
Mar 9 '06 #58

P: n/a
Ian Boyd wrote:
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).
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)
);=
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.

--

Mar 9 '06 #59

P: n/a
Serge Rielau wrote:
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.

--

Mar 9 '06 #60

P: n/a
"Dave Hughes" <da**@waveform.plus.com> wrote in message
news:44***********************@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).
Mar 9 '06 #61

P: n/a
In article <du********@enews3.newsguy.com>, Ian Boyd (ian.msnews010
@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> @

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@

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
Mar 9 '06 #62

P: n/a
>was referring to (an older version of) the SQL Reference for DB2 on zOS ("Mainframe").

Version 8 for LUW.

Or at least that's what they have on the corparate intranet.

B.

Mar 9 '06 #63

P: n/a
I guess you never saw MS Word's message "you must click OK to exit"
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.

Mar 9 '06 #64

P: n/a
Point taken. :)

Obviously the software should do some things, just don;t take my power
to change them away.

What you said.

B.

Mar 9 '06 #65

P: n/a
I quite agree on how careful and respectful both the OP and MOST of the
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:
44**********************@ptn-nntp-reader02.plus.net...
.....snip>
[snip]
> PS: I find this thread quite interesting actually.

In a morbid train-wreck sorta way? .....snip 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.

--


Mar 9 '06 #66

P: n/a
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
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
Mar 9 '06 #67

P: n/a
Ian Boyd wrote:
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?)
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.
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.
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
(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>
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.
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
DB2 Information Integration Development
IBM Germany
Mar 9 '06 #68

P: n/a
Dave Hughes wrote:
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. ;-))

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 9 '06 #69

P: n/a
> If you want similar documentation for Windows you have to go here:
http://www-306.ibm.com/software/data...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.
Mar 9 '06 #70

P: n/a

"Knut Stolze" <st****@de.ibm.com> wrote in message
news:du**********@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.
Mar 9 '06 #71

P: n/a
Couple of minor corrections to the corrections :-)

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.

--

Mar 9 '06 #72

P: n/a
>> - 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 :)
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 :)
Mar 9 '06 #73

P: n/a
> PS: Name any software product (which I know of course) and I can rattle
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.
Mar 9 '06 #74

P: n/a
> This isn't right, it depends. If you put multiple commands in one
file you need to seperate them if you keep them on one line
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.
Mar 9 '06 #75

P: n/a
Ian Boyd wrote:
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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 9 '06 #76

P: n/a
> 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?)
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.
Mar 9 '06 #77

P: n/a
> Then, ping-pong between the explanation and the SQL diagram in order to try to infer what the syntax is.

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.

Mar 9 '06 #78

P: n/a
>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.


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.

Mar 9 '06 #79

P: n/a
In article <du*********@enews3.newsguy.com>, Ian Boyd (ian.msnews010
@avatopia.com) says...
This isn't right, it depends. If you put multiple commands in one
file you need to seperate them if you keep them on one line


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.


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?
Mar 9 '06 #80

P: n/a
> You may not like the IBM diagrams, but they are indeed very clear.

The same diagrams are in the ANSI spec. They are very clear once you already
know the syntax.
Mar 9 '06 #81

P: n/a
> And the query changes per application,
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?
Mar 9 '06 #82

P: n/a
> 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


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

Mar 9 '06 #83

P: n/a
> 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?


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.

Mar 9 '06 #84

P: n/a
> 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.
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!
Mar 9 '06 #85

P: n/a
Ian Boyd wrote:
- 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.

--

Mar 9 '06 #86

P: n/a
Ian Boyd wrote:
And the query changes per application,
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?


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.

--

Mar 9 '06 #87

P: n/a
Dave Hughes wrote:
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?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 9 '06 #88

P: n/a
> 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 *
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 :)
Mar 9 '06 #89

P: n/a
> Argh! I was scratching my head wondering how anyone could be so crazy
Don't put it past me :)
> 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.
Mar 9 '06 #90

P: n/a
7. Casting number to a string

<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?
Mar 9 '06 #91

P: n/a
> 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?


Perhaps IBM should ask Oracle or MS if they can license their engines.
Mar 9 '06 #92

P: n/a

"Ian Boyd" <ia***********@avatopia.com> schreef in bericht
news:du*********@enews4.newsguy.com...
7. Casting number to a string

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


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
Mar 9 '06 #93

P: n/a

"Ian Boyd" <ia***********@avatopia.com> schreef in bericht
news:du*********@enews4.newsguy.com...
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?


Perhaps IBM should ask Oracle or MS if they can license their engines.


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
Mar 9 '06 #94

P: n/a

"Jeroen van den Broek" <je****@NOSPAM.demon.nl> schreef in bericht
news:12*************@corp.supernews.com...

"Ian Boyd" <ia***********@avatopia.com> schreef in bericht
news:du*********@enews4.newsguy.com...
7. Casting number to a string

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


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


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
Mar 9 '06 #95

P: n/a
In SQL Server, just so you know what i'm basing it off of:

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"

Mar 9 '06 #96

P: n/a

"Jeroen van den Broek" <je****@NOSPAM.demon.nl> schreef in bericht
news:12*************@corp.supernews.com...

"Ian Boyd" <ia***********@avatopia.com> schreef in bericht
news:du*********@enews4.newsguy.com...
7. Casting number to a string

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


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


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
Mar 9 '06 #97

P: n/a
On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <da**@waveform.plus.com>
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
Mar 10 '06 #98

P: n/a
Will Honea wrote:
On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <da**@waveform.plus.com>
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?


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.

--

Mar 10 '06 #99

P: n/a
> 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".


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.
Mar 10 '06 #100

138 Replies

This discussion thread is closed

Replies have been disabled for this discussion.