How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Brian Tkatch on 8 Mar 2006 14:16 >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. 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. >That explains why the reference doesn't include some statements, their not >the right "kind" of statements. Close. It's because, they are not statements. >i see the majority of the reference is in a section called "Statements". i >don't see a corresponding section of "control statements" In my (offline) copy, Chapter 1 is "Statements" and Chapter 2 is "SQL control statements". >, nor is the keyword DECLARE in the index. It is absolutely in the index. Though, it is not a bookmark. It is in Chapter 2.=>Compound Statement (Procedure) under "SQL-variable-declaration". A search of the index (which is a bookmark) found it for me pretty easily. B.
From: Dave Hughes on 8 Mar 2006 14:18 Serge Rielau wrote: > Ian Boyd wrote: > > 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_Number, Created_By_User_ID, Property_ID, Shift_ID, > > Bay_Number, Supervisor_User_ID, Location_ID, Occurrence_ID, > > Checklist_ID, Daily_Log_Type_ID, Daily_Log_SubType_ID, > > Start_Date, End_Date, Description) VALUES ( > > 'DL-20060307-1', --DailyLogNumber > > 0, --CreatedByUserID > > 1, --PropertyID > > 1, --ShiftID > > "A74", --BayNumber > > 1, --SupervisorUserID > > 2, --LocationID > > CAST(NULL AS bigint), --Occurrence_ID (must manually cast nulls) > > CAST(NULL AS bigint), --ChecklistID (must manually cast nulls) > > 2, --DailyLogTypeID > > 5, --DailyLogSubTypeID > > '2006-03-01 11:11:07.11111', --StartDate > > '2006-03-01 11:21:18.22222', --EndDate > > CAST(NULL AS varchar(1)) --Description (must manually cast nulls) > > ); > > > > 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, what tool are you using. This works for me using CLP > Please clarify your environment. 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_expr>". 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. > 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. 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. > PS: I find this thread quite interesting actually. 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.
From: Ian Boyd on 8 Mar 2006 14:08 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_APPLNAME") ALTER TABLE SUPERDUDE.AUDIT_LOG ALTER COLUMN APPNAME SET WITH DEFAULT CURRENT CLIENT_APPLNAME ; --Fails (unexpected token near "CLIENT_WRKSTNNAME") ALTER TABLE SUPERDUDE.AUDIT_LOG ALTER COLUMN HOSTNAME SET WITH DEFAULT CURRENT CLIENT_WRKSTNNAME ;
From: Dave Hughes on 8 Mar 2006 14:30 Brian Tkatch wrote: [snip] > The day DBs do things for me, is the day i stop doing databases. You hand-crank the execution plan for all your queries? Wow ... hardcore man! (joking :-) > I actually despise Windows mostly because of these assumptions. I love > DBs, because they are so simple, and make no assumptions. 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 "intelligence" being added to the system, provided it's optional :-) Cheers, Dave.
From: Serge Rielau on 8 Mar 2006 14:53
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 |