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

**Ian Boyd** · Mar 8 '06, 04:45 PM

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

5. Comments

How to do comments in DB2-SQL?

From:
IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
Chapter 2. Language Elements
Tokens

<quote>
Comments
Static SQL statements may include host language comments or SQL comments.
Either type of comment may be specified wherever a space may be specified,
except
within a delimiter token or
between the keywords EXEC and SQL.
SQL comments are introduced by two consecutive hyphens (--) and ended by the
end of the line.
</quote>

The following DB2-SQL fails:

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-1', --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)
);

But if a take out the comments, it works. i have no keywords EXEC or SQL,
and i am not putting my comments within a delimiter token "," since i
cannot split a comma into two parts.

*so tired*

**Ian Boyd** · Mar 8 '06, 05:15 PM

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

> Welcome to the non-MS/Windows world. Where you are expected to[color=blue]
> understand what your are doing. :)[/color]

They have databases on computers these days. i know what i'm doing, and
computers are powerful enough these days to know as well; or at least
proceed with the only possible course of action.
[color=blue][color=green]
>>My confusion comes from the fact that sometimes i can just run SELECT *
>>FROM
>>..., and other times i have to declare a cursor for a select[/color]
>
> A SELECT statement can be run outside a block of code, a DECLARE
> within.
>
> The difference is, SELECT is a "statement" and DECLARE is a "control
> statement". Both clearly delineated in SQL Reference Volume 2.
>
> Generally, the beginning of the documentate for a particular statement
> says when it can (and sometimes when it cannot) be executed.[/color]

That explains why the reference doesn't include some statements, their not
the right "kind" of statements.
i see the majority of the reference is in a section called "Statements ". i
don't see a corresponding section of "control statements", nor is the
keyword DECLARE in the index. Is there a Reference Volume 3 that documents
the "control statements?" Are there are more kinds of statements?

[Comicbook Guy] Umm, excuse me. Clearly select is a statement, and declare
is a control statement. Thank you.

**Ian Boyd** · Mar 8 '06, 05:25 PM

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

>select 1+2+3, 4+5+6 from sysibm.sysdummy 1
1 2
----------- -----------
6 15
[color=blue]
> SELECT 3.14159, 1+2+3, 4+5+6[/color]

1 2 3
-------- -------- --------
3.14159 6 15
[color=blue]
> You two got yor wires crossed.. Knut is talking about correlation names
> (above the ----- line).
> You are now talking about result types.[/color]

?

i thought we were talking about correlation names and how they are still
necessary even if there is only one column in the results set.

**Ian Boyd** · Mar 8 '06, 05:25 PM

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

> This is the root for SQL Procedure logic:[color=blue]
> http://publib.boulder.ibm.com/infoce...n/r0004239.htm
> This is the root for the simpler command line scripting:
> http://publib.boulder.ibm.com/infoce...n/r0004240.htm[/color]

i get it. Some statements are only valid inside other statements. If of
course be convient if they would just work.
[color=blue]
> This statement can only be embedded in an SQL procedure. It is not an
> executable statement and cannot be dynamically prepared."[/color]

Why? It can't be a technical limitation, and there is little value in making
things more difficult for people to use.
Is it the standard? And if so how many companies in the consortium are
trying to change it? It's fair to say that in the end this stuff is meant to
be used by developers, not just computer scientists.
[color=blue]
> You implicitly raise an interesting point though.
> The SQL Reference is "dictionary " it is as little the right tool to learn
> the basics of SQL as any dictionary.[/color]

Every other computer language has keywords or tokens, and someplace you can
open a reference manual and get an explanation for that token, or a
reference to where it is used.
[color=blue]
> You are used to MS SQL Server "Books Online" which is more of a guide.
> It describes what matters example driven).
> The DB2 SQL Ref is the _exact_ specification of DB2's SQL.[/color]

Which is about a dry a read as ISO/IEC 9075 is, albeit more helpful.
[color=blue]
> There are plans to deliver a SQL Guide in a future release which will be
> more appropriate and have information such as which statement can be used
> where, and include scenario based examples.[/color]

An index would be nice. Seaching a web-site, pdf, google groups, or the
internet for "ibm db2 set" doesn't help so much. But if i could type SET and
be presented with the index entries that someone has already taken the time
to pre-select, would be so so SO SO SO much more useful.

In the Books Online, i rarely use the "contents" and i rarely use "search".
i use the index almost exclusivly. If i want help on, for example, SET, i
type the word SET and am presented with the documentation on the SET
keyword. Doing a word search for "SET" would be folly.

**Dave Hughes** · Mar 8 '06, 06:55 PM

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

Ian Boyd wrote:
[color=blue]
> The following DB2-SQL fails:
>
> 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-1', --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)
> );
>
> But if a take out the comments, it works. i have no keywords EXEC or
> SQL, and i am not putting my comments within a delimiter token ","
> since i cannot split a comma into two parts.[/color]

Yup, in DB2 SQL comments must appear as the first non-whitespace
characters in a line. Hence:

SELECT
AFIELD, -- This is not a comment
FROM ...

SELECT
-- This is a comment
AFIELD,
FROM ...

[color=blue]
> 5. Comments
>
> How to do comments in DB2-SQL?
>
> From:
> IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
> Chapter 2. Language Elements
> Tokens
>
> <quote>
> Comments
> Static SQL statements may include host language comments or SQL
> comments. Either type of comment may be specified wherever a space
> may be specified, except within a delimiter token or between
> the keywords EXEC and SQL. SQL comments are introduced by two
> consecutive hyphens (--) and ended by the end of the line. </quote>[/color]

Strange that the manual doesn't make any mention of this behaviour. I
could swear it did at some point in the past, but maybe my memory's
faulty. It is an annoying behaviour, especially as it's not exactly
difficult to change a parser to permit -- comments pretty much anywhere
(if anything, it's more difficult to write a parser that only permits
-- comments as the first non-whitespace characters in a line, something
I've found out from experience in writing syntax highlighters for SQL
editors and such like).

HTH,

Dave.

**Dave Hughes** · Mar 8 '06, 07:05 PM

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

Knut Stolze wrote:

[snip][color=blue]
> Note that DB2 names expressions that are returned from a query itself
> (it numbers them) if they don't have an explicit name. That's why
> you see the "1" or "2" in the following output:
>
> $ db2 "select 1+2+3, 4+5+6 from sysibm.sysdummy 1"
>
> 1 2
> ----------- -----------
> 6 15
>
> 1 record(s) selected.
>
>
> Same thing here:
>
> $ db2 "values ( 1+2+3, 4+5+6 )"
>
> 1 2
> ----------- -----------
> 6 15
>
> 1 record(s) selected.
>
> And those "1" or "2" can be used in a Java application when you fetch
> the data from the result set by column name.[/color]

Yes, though my personal opinion is that it's a bad idea to use the
"raw" numeric column names that DB2 generates; they're subject to
change if the query changes, and can't be used in all the same ways as
a properly named column. For example:

Numeric column names

SELECT * FROM (VALUES (1, 2)) AS T; -- Works
SELECT 1, 2 FROM (VALUES (1, 2)) AS T; -- Works
SELECT T.* FROM (VALUES (1, 2)) AS T; -- Works
SELECT T.1, T.2 FROM (VALUES (1, 2)) AS T; -- Doesn't work

Aliased column names

SELECT * FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
SELECT F1, F2 FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
SELECT T.* FROM (VALUES (1, 2)) AS T(F1, F2); -- Works
SELECT T.F1, T.F2 FROM (VALUES (1, 2)) AS T(F1, F2); -- Works

Hence, I'd always recommend one renames generated column names to
something meaningful.

HTH,

Dave.

**Ian Boyd** · Mar 8 '06, 07:05 PM

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

6. Column defaults

Follownig works:
ALTER TABLE SUPERDUDE.AUDIT _LOG
ALTER COLUMN CHANGEDATE
SET WITH DEFAULT CURRENT TIMESTAMP ;

Following fails:
ALTER TABLE SUPERDUDE.AUDIT _LOG
ALTER COLUMN APPNAME
SET WITH DEFAULT CURRENT CLIENT_APPLNAME ;

Both are special registers.

**Serge Rielau** · Mar 8 '06, 07:15 PM

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

Ian Boyd wrote:[color=blue]
> 5. Comments
>
> How to do comments in DB2-SQL?
>
> From:
> IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
> Chapter 2. Language Elements
> Tokens
>
> <quote>
> Comments
> Static SQL statements may include host language comments or SQL comments.
> Either type of comment may be specified wherever a space may be specified,
> except
> within a delimiter token or
> between the keywords EXEC and SQL.
> SQL comments are introduced by two consecutive hyphens (--) and ended by the
> end of the line.
> </quote>
>
>
> The following DB2-SQL fails:
>
> 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-1', --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)
> );
>
> But if a take out the comments, it works. i have no keywords EXEC or SQL,
> and i am not putting my comments within a delimiter token "," since i
> cannot split a comma into two parts.
>
> *so tired*
>
>[/color]
Ian, what tool are you using. This works for me using CLP
Please clarify your environment.

The thing about -- is that if your client strips out line feeds then
everything after the first -- will look like a comment.
select * --hello from -- comment t -- more comment
And of course select * is not legal SQL. There is nothing DB2 can do on
-- if the client screws things up... so please clarify your client
interface.

Cheers
Serge

PS: I find this thread quite interesting actually.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**Brian Tkatch** · Mar 8 '06, 07:25 PM

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

>They have databases on computers these days. i know what i'm doing, and[color=blue]
>computers are powerful enough these days to know as well; or at least
>proceed with the only possible course of action.[/color]

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

A search of the index (which is a bookmark) found it for me pretty
easily.

B.

**Dave Hughes** · Mar 8 '06, 07:35 PM

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

Serge Rielau wrote:
[color=blue]
> Ian Boyd wrote:[color=green]
> > 5. Comments
> >
> > How to do comments in DB2-SQL?
> >
> > From:
> > IBM DB2 Universal Database SQL Reference Volume 1 Version 8.2
> > Chapter 2. Language Elements
> > Tokens
> >
> > <quote>
> > Comments
> > Static SQL statements may include host language comments or SQL
> > comments. Either type of comment may be specified wherever a space
> > may be specified, except within a delimiter token or between
> > the keywords EXEC and SQL. SQL comments are introduced by two
> > consecutive hyphens (--) and ended by the end of the line.
> > </quote>
> >
> >
> > The following DB2-SQL fails:
> >
> > 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-1', --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)
> > );
> >
> > But if a take out the comments, it works. i have no keywords EXEC
> > or SQL, and i am not putting my comments within a delimiter token
> > "," since i cannot split a comma into two parts.
> >
> > *so tired*
> >
> >[/color]
> Ian, what tool are you using. This works for me using CLP
> Please clarify your environment.[/color]

This works in CLP? Doesn't for me! From DB2 UDB v8 under Linux:

$ db2 -t
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.0

[snip help stuff]

db2 => SELECT
db2 (cont.) => F1, -- A comment
db2 (cont.) => F2, -- Another comment
db2 (cont.) => F3 -- Yet another comment
db2 (cont.) => FROM
db2 (cont.) => (VALUES (1, 2, 3)) AS T(F1, F2, F3);
SQL0104N An unexpected token "," was found following "SELECT F1".
Expected
tokens may include: "<table_exp r>". SQLSTATE=42601
db2 => SELECT
db2 (cont.) => -- A comment
db2 (cont.) => F1,
db2 (cont.) => -- Another comment
db2 (cont.) => F2,
db2 (cont.) => -- Yet another comment
db2 (cont.) => F3
db2 (cont.) => FROM
db2 (cont.) => (VALUES (1, 2, 3)) AS T(F1, F2, F3);

F1 F2 F3
----------- ----------- -----------
1 2 3

1 record(s) selected.

[color=blue]
> The thing about -- is that if your client strips out line feeds then
> everything after the first -- will look like a comment. select *
> --hello from -- comment t -- more comment And of course select * is
> not legal SQL. There is nothing DB2 can do on -- if the client screws
> things up... so please clarify your client interface.[/color]

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=blue]
> PS: I find this thread quite interesting actually.[/color]

Absolutely. In another post I was rambling on vaguely incoherently
about functional versus procedural styles in DB2 and other DBs ... I'm
beginning to suspect there's a whole different way of thinking required
when switching from certain relational systems to others (a bit like
learning functional programming after doing C/Pascal imperative stuff
for so long ... I remember feeling very fatigued at how difficult
everything seemed, until there came a point where I just "got it" and
it all just seemed to fall into place ... the relief was tangible!)

Cheers,

Dave.

**Ian Boyd** · Mar 8 '06, 07:35 PM

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

More examples.

--Works
ALTER TABLE SUPERDUDE.AUDIT _LOG
ALTER COLUMN USERNAME
SET WITH DEFAULT USER ;

--Works
ALTER TABLE SUPERDUDE.AUDIT _LOG
ALTER COLUMN CHANGEDATE
SET WITH DEFAULT CURRENT TIMESTAMP ;

--Fails (unexpected token near "CLIENT_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 ;

**Dave Hughes** · Mar 8 '06, 07:45 PM

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

Brian Tkatch wrote:

[snip][color=blue]
> The day DBs do things for me, is the day i stop doing databases.[/color]

You hand-crank the execution plan for all your queries? Wow ...
hardcore man! (joking :-)
[color=blue]
> I actually despise Windows mostly because of these assumptions. I love
> DBs, because they are so simple, and make no assumptions.[/color]

I'd take issue with this in one particular area. I've always liked that
one can tweak just about *any* performance parameter in DB2. That said,
I've come to enjoy the ability added in more recent versions to have
the tools figure out an "optimum" configuration, or in the most recent
versions to just set the parameter to AUTOMATIC and have the database
look after itself.

I'd be extremely disappointed if such configuration parameters were
ever removed completely from manual control ... but I do appreciate a
bit of "intelligen ce" being added to the system, provided it's optional
:-)

Cheers,

Dave.

**Serge Rielau** · Mar 8 '06, 08:05 PM

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

I stand corrected. I ran the insert statement and it came back with
"table not found", so I figured I got past the syntax checks..
apparently a hasty and wrong assumption.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**Dave Hughes** · Mar 8 '06, 08:05 PM

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

Ian Boyd wrote:
[color=blue]
> More examples.
>
> --Works
> ALTER TABLE SUPERDUDE.AUDIT _LOG
> ALTER COLUMN USERNAME
> SET WITH DEFAULT USER ;
>
> --Works
> ALTER TABLE SUPERDUDE.AUDIT _LOG
> ALTER COLUMN CHANGEDATE
> SET WITH DEFAULT CURRENT TIMESTAMP ;
>
> --Fails (unexpected token near "CLIENT_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 ;[/color]

Bizarre. I've just tried the same with some test tables in DB2 UDB 8
under Linux and got the same thing. I can't see anything in the
reference explicitly forbidding such a thing, but maybe there's some
other reason? (none that I can think of at the moment).

Still, it seems to work within a BEGIN ATOMIC block:

CREATE TABLE AUDIT_LOG (
USERNAME VARCHAR(128) NOT NULL WITH DEFAULT CURRENT USER,
CHANGEDATE TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
APPNAME VARCHAR(255) NOT NULL,
HOSTNAME VARCHAR(255) NOT NULL
)!

BEGIN ATOMIC
DECLARE MY_APPNAME VARCHAR(255);
DECLARE MY_HOSTNAME VARCHAR(255);
SET MY_APPNAME = CURRENT CLIENT_APPLNAME ;
SET MY_HOSTNAME = CURRENT CLIENT_WRKSTNNA ME;
INSERT INTO AUDIT_LOG (APPNAME, HOSTNAME)
VALUES (MY_APPNAME, MY_HOSTNAME);
END!

A word of caution: I've used CURRENT USER in the statements above. This
is *not* the same as USER (and then there's SESSION_USER and
SYSTEM_USER as well). See the reference manual for the differences
between them.

HTH,

Dave.

**Serge Rielau** · Mar 8 '06, 08:15 PM

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

Working as documented:

http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0000888.htm

DEFAULT ...

datetime-special-register
Specifies the value of the datetime special register (CURRENT DATE,
CURRENT TIME, or CURRENT TIMESTAMP) at the time of INSERT, UPDATE, or
LOAD as the default for the column. The data type of the column must be
the data type that corresponds to the special register specified (for
example, data type must be DATE when CURRENT DATE is specified).

user-special-register
Specifies the value of the user special register (CURRENT USER,
SESSION_USER, SYSTEM_USER) at the time of INSERT, UPDATE, or LOAD as the
default for the column. The data type of the column must be a character
string with a length not less than the length attribute of a user
special register. Note that USER can be specified in place of
SESSION_USER and CURRENT_USER can be specified in place of CURRENT USER.

CURRENT SCHEMA
Specifies the value of the CURRENT SCHEMA special register at the
time of INSERT, UPDATE, or LOAD as the default for the column. If
CURRENT SCHEMA is specified, the data type of the column must be a
character string with a length greater than or equal to the length
attribute of the CURRENT SCHEMA special register.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab