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)

Ian Boyd wrote:
[color=blue][color=green]
> > Nope, in this case I think it's the thing I mentioned in my other
> > post: comments can only appear as the first non-whitespace
> > characters in a line.[/color]
>
> i can't get that to work either:
>
> INSERT INTO Daily_Logs (
> Daily_Log_Number, Created_By_User_ID, Property_ID, Shift_ID,
> Bay_Number, Supervisor_User_ID, Location_ID, Occurrence_ID,
> Checklist_ID, Daily_Log_Type_ID, Daily_Log_SubType_ID, Start_Date,
> End_Date, Description) VALUES (
> --DailyLogNumber
> 'DL-20060307-36',
> 0,
> 1,
> 1,
> 'A74',
> 1,
> 2,
> CAST(NULL AS bigint),
> CAST(NULL AS bigint),
> 2,
> 5,
> '2006-03-01 11:11:07.11111',
> '2006-03-01 11:21:18.22222',
> CAST(NULL AS varchar(1))
> );
>
> SQL0104N An unexpected token "(" was found following "DESCRIPTION)
> VALUES". Expected tokens may include:
> "<table_value_constructor_list>". SQLSTATE=42601[/color]

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

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


Dave.

--

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

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

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

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;

Dave Hughes wrote:[color=blue]
> Serge Rielau wrote:
>[color=green]
>> Working as documented:
>> http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2.
>> udb.doc/admin/r0000888.htm
>>
>> DEFAULT ...
>>[/color]
> [snip]
>
> But why can CURRENT CLIENT_APPLNAME and CURRENT CLIENT_WRKSTNNAME not
> be used as column defaults? I'm guessing there must be some technical
> reason, e.g. maybe there are some circumstances in which these
> registers would be unknown or undefined when inserting a row into a
> table? (though I guess one could argue that these registers ought to
> evaluate to NULL under such circumstances instead of being impossible
> to use as a column default)[/color]
Support or non support more often than not has one reason: resources.
Quite likely when these registers were added DEFAULT was not part of the
requirement. Given that they have been present for a while no one ever
complained (AFAIK) this might well have been the right decision at the time.
Also note that BEFORE triggers support ALL registers and it is highly
unlikely anyone would want to use default values for those on LOAD.
So, mostly we are talking orthogonality here. A desirable property, but
hard to measure in $$.

Cheers
Serge

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

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

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

1 record(s) selected.

DB2 V8.2.2 (FP9)

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

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

Ian Boyd wrote:[color=blue][color=green]
>> PS: I find this thread quite interesting actually.[/color]
> In a morbid train-wreck sorta way?[/color]
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

"Ian Boyd" <ian.msnews010@avatopia.com> schreef in bericht
news:duneia0j3g@enews3.newsguy.com...
[..][color=blue]
>[color=green][color=darkred]
>>>That explains why the reference doesn't include some statements, their
>>>not
>>>the right "kind" of statements.[/color]
>>
>> Close. It's because, they are not statements.[/color]
>
> i guess this is where some help with examples would be extraordinarily
> useful.
>[color=green][color=darkred]
>>>i see the majority of the reference is in a section called "Statements".
>>>i
>>>don't see a corresponding section of "control statements"[/color]
>>
>> In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL
>> control statements".[/color]
>
> SQL Reference Volume 1
> Contents
> About this book
> Chapter 1. Concepts
> Chapter 2. Language elements
> Chapter 3. Functions
> Chapter 4. Queries
> Appendix A. SQL limits
> ...
> Appendix Q. Notices
> Index
> Contacting IBM
>
> SQL Reference Volume 2
> Contents
> About this book
> Statements
> Appendix A. DB2 Universal Databse technical information
> Appendix B. Notices
> Index
> Contacting IBM
>
> These are two PDF files that are referred to by the online documentation.
> i reach the online documentation from Control Center by hitting:
> Help->About
>
> On the web-site, i see
> Reference
> SQL
> How to read the syntax diagrams
> Common syntax elements
> Language elements
> Functions
> Procedures
> Queries
> Statements
> Reserved schema names and reserved words
> SQL statements allowed in routines
> Communications areas, descriptor areas, and exception tables
> Explain tables
> Explain register values
> Japanese and traditional-Chinese extended UNIX code (EUC)
> considerations
> Backus-Naur form (BNF) specifications for DATALINKs
>
>[color=green][color=darkred]
>>>, nor is the keyword DECLARE in the index.[/color]
>>
>> It is absolutely in the index. Though, it is not a bookmark.
>>
>> It is in Chapter 2.=>Compound Statement (Procedure) under
>> "SQL-variable-declaration".[/color]
>[color=green]
>> A search of the index (which is a bookmark) found it for me pretty
>> easily.[/color]
>
>
> i gotta find this book, web-site, pdf, help file, or eBook you got.
>[/color]

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

"Jeroen van den Broek" <jeroen@NOSPAM.demon.nl> schreef in bericht
news:120usjk5p4rks89@corp.supernews.com...[color=blue]
>[/color]
[..][color=blue]
>
> 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.
>[/color]

More specific w.r.t. the Command Reference:

Chapter 4. Using command line SQL statements

--
Jeroen

Ian Boyd wrote:
[color=blue][color=green]
> > Ian, what tool are you using. This works for me using CLP
> > Please clarify your environment.[/color]
>
> i've tried a couple:
>
> - Microsoft ADO using the IBM DB2 driver for ODBC[/color]

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)
[color=blue]
> - Microsoft ADO using the IBM DB2 OLEDB Provider[/color]

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

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

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][color=blue][color=green]
> > PS: I find this thread quite interesting actually.[/color]
> In a morbid train-wreck sorta way?
>
> i'm approaching DB2 from a very high-level (and overview if you
> will). It's not like i don't understand relational databases. i think
> i am very used to SQL Server, where it is very powerful and yet very
> friendly. If you accept that most enterprise class RDBMS are of a
> similar feature set, the different between is semantics, and tools,
> and language.
>
> i'm walking a fine line here. There are things in DB2 that make no
> sense. They are, quite plainly, non-sensical. i try to explain what i
> think the vision and philosophy that DB2 has for doing things - as
> though there was an all-encompassing grand vision for everything. But
> things are not that way. Due to historical design decisions,
> backwards compatiblity, forward compatiblity, standards
> compatability, etc things can make little sense to an outside
> observer. Which is fine, as long as i can quickly find the list of
> all these different design paradigms. But many zealots will take my
> confusion and frustration of the scattered design as an insult, and
> thats a tough needle to thread, especially in a DB2 newsgroup - where
> i am stating up front i come from Microsoft SQL Server, and a lot of
> people in here have used the word "Micro$oft".[/color]

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

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).
[color=blue]
> i'm walking a fine line here: of trying to extract information from
> the people in the know, without touching a nerve.[/color]

You're doing a good job so far I'd say.

--

Serge Rielau wrote:
[color=blue]
> Dave Hughes wrote:[color=green]
> > In this case, unfortunately, the only solution is to chop out the
> > comments altogether (shame DB2 doesn't support C-style /*..*/
> > comments in which line break chopping doesn't result in ambiguity).[/color]
> db2 => select /* hello */ 1 from sysibm.sysdummy1;
>
> 1
> -----------
> 1
>
> 1 record(s) selected.
>
> DB2 V8.2.2 (FP9)
>
> Since Ian has all those drivers maybe he can try it.
>
> Cheers
> Serge[/color]

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.

--

"Dave Hughes" <dave@waveform.plus.com> wrote in message
news:440f7a23$0$70294$ed2619ec@ptn-nntp-reader03.plus.net...[color=blue]
> 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.
>[/color]

Aside from security fixes, there have been about 1500 other APAR's fixed
since then (assuming that you install FP11).

In article <dunkro0q4f@enews3.newsguy.com>, Ian Boyd (ian.msnews010
@avatopia.com) says...[color=blue]
> 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[/color]

Sorry, I don't agree. I do respond because it contains errors.[color=blue]
>
>
> 7. Every DB2-SQL Statement must end with a semi-colon (;)
>
> 8. You cannot give DB2 some arbitrary SQL to run.[/color]

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

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

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.

Point taken. :)

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

What you said.

B.

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" <dave@waveform.plus.com> a écrit dans le message de news:
440f75d0$0$6964$ed2619ec@ptn-nntp-reader02.plus.net...
.....snip>[color=blue]
> [snip][color=green][color=darkred]
>> > PS: I find this thread quite interesting actually.[/color]
>> In a morbid train-wreck sorta way?[/color][/color]
.....snip[color=blue][color=green]
>> i'm walking a fine line here: of trying to extract information from
>> the people in the know, without touching a nerve.[/color]
>
> You're doing a good job so far I'd say.
>
> --
>[/color]

Ian Boyd wrote:
[color=blue]
> Unless someone changed the query to
> SELECT 3.14159, 1+2+3, 4+5+6[/color]

I guess you mean VALUES 3.14159, 1+2+3, 4+5+6
[color=blue]
> 1 2 3
> -------- -------- --------
> 3.14159 6 15
>
> 1 record(s) selected.
>
> And now all the application logic has to be rewritten.[/color]

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

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

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

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

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

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)

[color=blue]
> 3. DB2 cannot implicitly cast a NULL to any data type.[/color]

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

Not true. See above.
[color=blue]
> 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.[/color]

Not true. See above.
[color=blue]
> ADDITIONAL NOTES:
>
> 6. Many other SQL constructs are not understood by DB2.
>
> SELECT 'Hello, world!';[/color]

This is not a SQL construct. ;-)
[color=blue]
> 7. Every DB2-SQL Statement must end with a semi-colon (;)[/color]

Not true. You can choose your statement terminator freely, and it can even
be the end of line.
[color=blue]
> 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).[/color]

Not true. You have to separate the statements with the statement
terminator.
[color=blue]
> The major cavaet with this limitation is that something like the following
> is invalid:
>
> CREATE TABLE Users (
> UserID int,
> Username varchar(50);[/color]

Closing ')' is missing.
[color=blue]
> 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;[/color]

$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

Dave Hughes wrote:
[color=blue]
> 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).[/color]

The ! can (still) be used to call unregistered stored procedures (the
DB2DARI) style. But that style is deprecated as of V8.
[color=blue]
> INSERT INTO mytable (cola, colb, colc)
> SELECT cola, colb, colc FROM myothertable[/color]

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

SQL-2003
[color=blue]
> I can't remember a single occassion of someone *praising* the
> graphical tools![/color]

I do remember someone saying that the Control Center was good. ;-))

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

> If you want similar documentation for Windows you have to go here:[color=blue]
> http://www-306.ibm.com/software/data...manualsv8.html[/color]
[color=blue]
> where you will find the SQL Reference (Vol 1 and 2) you already have.[/color]
Yes.
[color=blue]
> Next to those however, you may want to take a look at the Command
> Reference.[/color]
No.
[color=blue]
> The Master Index may also be usefull for determining which document you
> need for a specific purpose.[/color]

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.

"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:duoq7u$gcs$1@lc03.rz.uni-jena.de...[color=blue]
> Ian Boyd wrote:
>[color=green]
>> Unless someone changed the query to
>> SELECT 3.14159, 1+2+3, 4+5+6[/color]
>
> I guess you mean VALUES 3.14159, 1+2+3, 4+5+6[/color]

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
[color=blue]
> True. But my answers would be
> (a) Why is the additional column not appended? Then you don't break
> anything.[/color]

Cause i didn't.
[color=blue]
> (b) I don't think this is a big deal in reality. Granted, the
> fetching using column names is a nice feature.[/color]

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.
[color=blue]
> For several decades now
> the the fetching of values from a result set is based on the order of the
> columns.[/color]

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.
[color=blue]
>So far there hasn't been a great outrage on this.[/color]

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.

Couple of minor corrections to the corrections :-)

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

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.
[color=blue]
>[/color]
[snip][color=blue][color=green]
> > 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).[/color]
>
> Not true. You have to separate the statements with the statement
> terminator.[/color]

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.

--

>> - IBM Command Editor (db2ce.bat) with it's "Statement termination[color=blue][color=green]
>> character" changed to =[/color]
>
> Hmmm, using = as a statement terminator is probably a bad idea (given
> the ambiguity).[/color]

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 :)
[color=blue]
> Hmmm ... you shouldn't need those CASTs around the NULLs, not in an
> INSERT statement anyway. Let me just try it:[/color]

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>[color=blue]
> INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone)
> SELECT fname, lname, addr1, NULL, NULL FROM legacy_system[/color]

In my variant of the 3rd case in DB2, it complains that "NULL is not valid
in the context where it is used."
</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.
[color=blue]
> 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.[/color]

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

Yes, one would :)
[color=blue]
> 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.[/color]

That sounds like a technical proglem, that need a technical solution.
[color=blue]
> 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.[/color]

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

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

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.
[color=blue]
> Extending this syntax to generate multiple rows like so:
> SELECT (<value>, <value>, ...), (<value>, <value>, ...)
> *is* non-sensical[/color]
Yeah, that's silly syntax
[color=blue]
> 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.[/color]
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.

[color=blue]
> You're doing a good job so far I'd say.[/color]
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 :)

> PS: Name any software product (which I know of course) and I can rattle[color=blue]
> down a set of odd limitations.[/color]

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<