How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Knut Stolze on 9 Mar 2006 04:26 Dave Hughes wrote: > 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). The ! can (still) be used to call unregistered stored procedures (the DB2DARI) style. But that style is deprecated as of V8. > INSERT INTO mytable (cola, colb, colc) > SELECT cola, colb, colc FROM myothertable 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. > 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. SQL-2003 > I can't remember a single occassion of someone *praising* the > graphical tools! I do remember someone saying that the Control Center was good. ;-)) -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Ian Boyd on 9 Mar 2006 09:28 > If you want similar documentation for Windows you have to go here: > http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html > where you will find the SQL Reference (Vol 1 and 2) you already have. Yes. > Next to those however, you may want to take a look at the Command > Reference. No. > The Master Index may also be usefull for determining which document you > need for a specific purpose. 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.
From: Ian Boyd on 9 Mar 2006 09:44 "Knut Stolze" <stolze(a)de.ibm.com> wrote in message news:duoq7u$gcs$1(a)lc03.rz.uni-jena.de... > Ian Boyd wrote: > >> Unless someone changed the query to >> SELECT 3.14159, 1+2+3, 4+5+6 > > I guess you mean VALUES 3.14159, 1+2+3, 4+5+6 Yes, i slipped, i was focused on the concept. i should have written: SELECT 3.14159, 1+2+3, 4+5+6 FROM sysibm.sysdummy1 > True. But my answers would be > (a) Why is the additional column not appended? Then you don't break > anything. Cause i didn't. > (b) I don't think this is a big deal in reality. Granted, the > fetching using column names is a nice feature. 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. > For several decades now > the the fetching of values from a result set is based on the order of the > columns. 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. >So far there hasn't been a great outrage on this. 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.
From: Dave Hughes on 9 Mar 2006 10:18 Couple of minor corrections to the corrections :-) Knut Stolze wrote: > Ian Boyd wrote: > [snip] > > > NOTES: > > 2. i had to specify SESSION_USER, CURRENT CLIENT_WRKSTNNAME, 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. > > 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. 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. > [snip] > > 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). > > Not true. You have to separate the statements with the statement > terminator. 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_statement_ 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. --
From: Ian Boyd on 9 Mar 2006 10:28
>> - 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). 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 :) > Hmmm ... you shouldn't need those CASTs around the NULLs, not in an > INSERT statement anyway. Let me just try it: 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> > INSERT INTO MyTable (Firstname, Lastname, Address, Child, Phone) > SELECT fname, lname, addr1, NULL, NULL FROM legacy_system 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. > 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. No reason DB2 can't just pick a type. If the extent of my statement was: SELECT NULL AS SomeNullValue FROM sysibm.sysdummy1 Then just go ahead and make it an integer. Nobody's gonna care. > 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 > 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. Yes, one would :) > 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. That sounds like a technical proglem, that need a technical solution. > 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. 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. > 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*. 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 :) > 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). 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. > Extending this syntax to generate multiple rows like so: > SELECT (<value>, <value>, ...), (<value>, <value>, ...) > *is* non-sensical Yeah, that's silly syntax > 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. 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. > You're doing a good job so far I'd say. i'm slipping here and there. i apologize to those on those other threads of this post. e.g. "ComicBookGuy" "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 :) |