How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Ian Boyd on 8 Mar 2006 11:57 > This is the root for SQL Procedure logic: > http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0004239.htm > This is the root for the simpler command line scripting: > http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0004240.htm i get it. Some statements are only valid inside other statements. If of course be convient if they would just work. > This statement can only be embedded in an SQL procedure. It is not an > executable statement and cannot be dynamically prepared." 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. > 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. 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. > 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. Which is about a dry a read as ISO/IEC 9075 is, albeit more helpful. > 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. 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.
From: Dave Hughes on 8 Mar 2006 13:39 Ian Boyd wrote: > 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. 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 ... > 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> 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.
From: Dave Hughes on 8 Mar 2006 13:48 Knut Stolze wrote: [snip] > 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.sysdummy1" > > 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. 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.
From: Ian Boyd on 8 Mar 2006 13:38 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.
From: Serge Rielau on 8 Mar 2006 14:03
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. 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 |