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

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

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

i've tried a couple:

- Microsoft ADO using the IBM DB2 driver for ODBC

- Microsoft ADO using the IBM DB2 OLEDB Provider

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

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

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

<quote>
------------------------------ Commands
Entered ------------------------------
INSERT INTO Daily_Logs (
Daily_Log_Numbe r, 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_SubTy pe_ID, Start_Date, End_Date,
Description)
VALUES (
'DL-20060307-3', --DailyLogNumber
0, --CreatedByUserID
1, --PropertyID
1, --ShiftID
'A74', --BayNumber
1, --SupervisorUserI D
2, --LocationID
CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls)
CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID
5, --DailyLogSubType ID
'2006-03-01 11:11:07.11111' , --StartDate
'2006-03-01 11:21:18.22222' , --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls)
);½
------------------------------------------------------------------------------
INSERT INTO Daily_Logs ( Daily_Log_Numbe r, 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_SubTy pe_ID, Start_Date, End_Date,
Description) VALUES ( 'DL-20060307-3', --DailyLogNumber 0, --CreatedByUserID
1, --PropertyID 1, --ShiftID 'A74', --BayNumber 1, --SupervisorUserI D
2, --LocationID CAST(NULL AS bigint), --Occurrence_ID (must manually cast
nulls) CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
2, --DailyLogTypeID 5, --DailyLogSubType ID '2006-03-01
11:11:07.11111' , --StartDate '2006-03-01 11:21:18.22222' , --EndDate
CAST(NULL AS varchar(1)) --Description (must manually cast nulls) );
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "," was found following "ES (
'DL-20060307-3'".
Expected tokens may include: ")". SQLSTATE=42601

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

Explanation:

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

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

The statement cannot be processed.

User Response:

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

sqlcode : -104

sqlstate : 42601
</quote>

[color=blue]
> PS: I find this thread quite interesting actually.[/color]
In a morbid train-wreck sorta way?

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

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

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

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

[Dave Hughes]

Re: How to do...well...any thing...in DB2 SQL

Serge Rielau wrote:
[color=blue]
> Working as documented:
> http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2.
> udb.doc/admin/r0000888.htm
>
> DEFAULT ...
>[/color]
[snip]

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

Cheers,

Dave.

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

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

The running joke at the office is the DB2 "WARNING: Everything is okay!"
dialog box.

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

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

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

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

Warning! Everything is okay.

It is just as stupid as installing the game Ghost Recon a few years ago, a
dialog box pops up, "You have enough free space on this drive to install the
game. Proceed?" As if i needed to know that.


<StartOfRangi ng @skip="yes">
Or another one, i want to shrink a varchar field length:

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

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

Would you like to proceed?

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

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

No you don't. i said save, you save. You do whatever it is you do when you
have to save. If it takes a long time, i'm okay with that, because i was
changing table structure - i'll expect it to take a long time.


And with the nulls. Why can't it implicitly cast a NULL to the type of the
column? What alternative is there? You are perfectly okay casting other
types around, why not NULL? Null is the lack of data, it is nothing, it is
not being. People will argue,

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

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



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

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

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

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

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

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

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


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

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

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

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

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

--Fails (unexpected token near "CLIENT_WRKSTNN AME")
ALTER TABLE SUPERDUDE.AUDIT _LOG
ALTER COLUMN HOSTNAME
SET WITH DEFAULT CURRENT CLIENT_WRKSTNNA ME ;


This goes to what i was saying before about non-sensical nature of DB2.
"Why can some special registers be used as column defaults and not others?"
"Cause."
"Wouldn't you maybe want to clean that up so it is consistent?"

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

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

i can't get that to work either:

INSERT INTO Daily_Logs (
Daily_Log_Numbe r, 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_SubTy pe_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 "DESCRIPTIO N) VALUES".
Expected tokens may include: "<table_value_c onstructor_list >".
SQLSTATE=42601

[Dave Hughes]

Re: How to do...well...any thing...in DB2 SQL

Ian Boyd wrote:
[color=blue][color=green]
> > Nope, in this case I think it's the thing I mentioned in my other
> > post: comments can only appear as the first non-whitespace
> > characters in a line.[/color]
>
> i can't get that to work either:
>
> INSERT INTO Daily_Logs (
> Daily_Log_Numbe r, 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_SubTy pe_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 "DESCRIPTIO N)
> VALUES". Expected tokens may include:
> "<table_value_c onstructor_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.

--

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

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

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

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

[Ian Boyd]

Re: How to do...well...any thing...in DB2 SQL

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

DO NOT RESPOND

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

CREATE TABLE "SUPERDUDE"."AU DIT_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_WRKSTNNA ME, 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_DA ILYLOGS
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_User s
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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(NULL AS varchar(1)),
CAST(NULL AS varchar(1))
FROM Inserted i;
END

NOTES:
2. i had to specify SESSION_USER, CURRENT CLIENT_WRKSTNNA ME, 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_Numbe r, 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_SubTy pe_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.sysdummy 1;

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.sysdummy 1;

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.sysdummy 1;
END;

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

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

CREATE PROCEDURE doStuff
BEGIN
SELECT * FROM Users;
END;

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

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

[Serge Rielau]

Re: How to do...well...any thing...in DB2 SQL

Dave Hughes wrote:[color=blue]
> Serge Rielau wrote:
>[color=green]
>> Working as documented:
>> http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2.
>> udb.doc/admin/r0000888.htm
>>
>> DEFAULT ...
>>[/color]
> [snip]
>
> But why can CURRENT CLIENT_APPLNAME and CURRENT CLIENT_WRKSTNNA ME 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

[Serge Rielau]

Re: How to do...well...any thing...in DB2 SQL

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

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

[Serge Rielau]

Re: How to do...well...any thing...in DB2 SQL

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

[Jeroen van den Broek]

Re: How to do...well...any thing...in DB2 SQL


"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/db2/zos/v8books.html

If you want similar documentation for DB2 on Linux, Unix and Windows you
have to go here:

http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html

where you will find the SQL Reference (Vol 1 and 2) you already have.
Next to those however, you may want to take a look at the Command Reference.
The Master Index may also be usefull for determining which document you need
for a specific purpose.

HTH.

--
Jeroen

[Jeroen van den Broek]

Re: How to do...well...any thing...in DB2 SQL


"Jeroen van den Broek" <jeroen@NOSPAM. demon.nl> schreef in bericht
news:120usjk5p4 rks89@corp.supe rnews.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

[Dave Hughes]

Re: How to do...well...any thing...in DB2 SQL

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_Numbe r, 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_SubTy pe_ID,
> Start_Date, End_Date, Description)
> VALUES (
> 'DL-20060307-3', --DailyLogNumber
> 0, --CreatedByUserID
> 1, --PropertyID
> 1, --ShiftID
> 'A74', --BayNumber
> 1, --SupervisorUserI D
> 2, --LocationID
> CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls)
> CAST(NULL AS bigint), --ChecklistID (must manually cast nulls)
> 2, --DailyLogTypeID
> 5, --DailyLogSubType ID
> '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.

--

[Dave Hughes]

Re: How to do...well...any thing...in DB2 SQL

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.sysdummy 1;
>
> 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.sysdummy 1;
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.

--