How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Dave Hughes on 8 Mar 2006 19:24 Ian Boyd wrote: > > Ian, what tool are you using. This works for me using CLP > > Please clarify your environment. > > i've tried a couple: > > - Microsoft ADO using the IBM DB2 driver for ODBC 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) > - Microsoft ADO using the IBM DB2 OLEDB Provider 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. > - 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 = 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). > 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_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-3', --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) > );= 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] > > PS: I find this thread quite interesting actually. > 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". 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. > 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 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). > i'm walking a fine line here: of trying to extract information from > the people in the know, without touching a nerve. You're doing a good job so far I'd say. --
From: Dave Hughes on 8 Mar 2006 19:43 Serge Rielau wrote: > Dave Hughes wrote: > > 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). > db2 => select /* hello */ 1 from sysibm.sysdummy1; > > 1 > ----------- > 1 > > 1 record(s) selected. > > DB2 V8.2.2 (FP9) > > Since Ian has all those drivers maybe he can try it. > > Cheers > Serge db2 => select /* hello */ 1 from sysibm.sysdummy1; 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. --
From: Mark A on 8 Mar 2006 19:52 "Dave Hughes" <dave(a)waveform.plus.com> wrote in message news:440f7a23$0$70294$ed2619ec(a)ptn-nntp-reader03.plus.net... > 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. > Aside from security fixes, there have been about 1500 other APAR's fixed since then (assuming that you install FP11).
From: Gert van der Kooij on 8 Mar 2006 20:32 In article <dunkro0q4f(a)enews3.newsguy.com>, Ian Boyd (ian.msnews010 @avatopia.com) says... > 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 Sorry, I don't agree. I do respond because it contains errors. > > > 7. Every DB2-SQL Statement must end with a semi-colon (;) > > 8. You cannot give DB2 some arbitrary SQL to run. 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_statement_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_DAILYLOGS 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_Users 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_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, 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_statements.sql it can be run at once with the command: db2 -tf multiple_statements.sql Hope this helps. Regards, Gert
From: Brian Tkatch on 8 Mar 2006 21:36
>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. |