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

[Mark A]

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

"Dave Hughes" <dave@waveform. plus.com> wrote in message
news:440f7a23$0 $70294$ed2619ec @ptn-nntp-reader03.plus.n et...[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).

[Gert van der Kooij]

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

In article <dunkro0q4f@ene ws3.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_statem ent_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_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@

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_statem ents.sql it can be run at
once with the command:

db2 -tf multiple_statem ents.sql


Hope this helps.

Regards, Gert

[Brian Tkatch]

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

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

[Brian Tkatch]

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

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.

[Brian Tkatch]

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

Point taken. :)

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

What you said.

B.

[Pierre Saint-Jacques]

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

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.n et...
.....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]

[Knut Stolze]

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

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.sysdummy 1 (or DUAL in Oracle).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

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

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"."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.[/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_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[/color]

(1) I would throw away the procedural logic for the "SavedUserI D" 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_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.[/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_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.[/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

[Knut Stolze]

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

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

[Ian Boyd]

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

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

[Ian Boyd]

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


"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.sysdummy 1
[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.

[Dave Hughes]

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

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_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.[/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_stateme nt_ to the
data source

No mention of multiple statements there. If one can do this with MS SQL
Server (?), it's certainly non-standard behaviour, and shouldn't be
relied upon to be implemented by other databases.

Then again, I might be misinterpreting Ian's intent here.


Dave.

--

[Ian Boyd]

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

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

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.

"ComicBookG uy"
"columns by ordinal"

It's difficult to be frustrated and pleasent at the same time; especially
when i also turn around and vent off to colleagues here so easily :)

[Ian Boyd]

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

> 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
implementing them in yours.

[Ian Boyd]

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

> This isn't right, it depends. If you put multiple commands in one[color=blue]
> file you need to seperate them if you keep them on one line[/color]

Nobody here will write queries on all one line. It's jut a practical thing.
[color=blue]
> 'create function' and 'create trigger' statements needs to be
> seperated by another delimiter.
> connect to <yourdb> @[/color]

i am already connected using ADO. The "connect to <yourdb> @" is invalid
DB2-SQL.
If i need to separate statements, i'm going to have to do it manually.