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 14:52 Ian Boyd wrote: > 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 ; 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_WRKSTNNAME; 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.
From: Serge Rielau on 8 Mar 2006 14:59 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
From: Ian Boyd on 8 Mar 2006 14:56 > 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 - Microsoft ADO using the IBM DB2 OLEDB Provider - 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 ? 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) );? ------------------------------------------------------------------------------ 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) ); DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "," was found following "ES ( 'DL-20060307-3'". Expected tokens may include: ")". SQLSTATE=42601 SQL0104N An unexpected token "," was found following "ES ( 'DL-20060307-3'". Expected tokens may include: ") ". Explanation: A syntax error in the SQL statement was detected at the specified token following the text "<text>". The "<text>" field indicates the 20 characters of the SQL statement that preceded the token that is not valid. As an aid to the programmer, a partial list of valid tokens is provided in the SQLERRM field of the SQLCA as "<token-list>". This list assumes the statement is correct to that point. The statement cannot be processed. User Response: Examine and correct the statement in the area of the specified token. sqlcode : -104 sqlstate : 42601 </quote> > 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". 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'm walking a fine line here: of trying to extract information from the people in the know, without touching a nerve.
From: Dave Hughes on 8 Mar 2006 15:22 Serge Rielau wrote: > Working as documented: > http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2. > udb.doc/admin/r0000888.htm > > DEFAULT ... > [snip] But why can CURRENT CLIENT_APPLNAME and CURRENT CLIENT_WRKSTNNAME not be used as column defaults? I'm guessing there must be some technical reason, e.g. maybe there are some circumstances in which these registers would be unknown or undefined when inserting a row into a table? (though I guess one could argue that these registers ought to evaluate to NULL under such circumstances instead of being impossible to use as a column default) Cheers, Dave.
From: Ian Boyd on 8 Mar 2006 15:25
> 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. The running joke at the office is the DB2 "WARNING: Everything is okay!" dialog box. i hit "OK" to save my changes to, for example, a table. Everything saves okay, but DB2 feels compelled to pop up a big dialog with a lot of text in it. Obviously something bad happened, because if everything saved okay after i told you to save, the edit table screen would go way, and we'd be done. So each and every time we see one of these boxes pop up, we have to read it: DB2 Message Commands attempted <some sql that i didn't ever enter> The command completed successfully. Explanation: No errors were encountered during the execution of this command. User Response: No action required. Warning! Everything is okay. It is just as stupid as installing the game Ghost Recon a few years ago, a dialog box pops up, "You have enough free space on this drive to install the game. Proceed?" As if i needed to know that. <StartOfRanging @skip="yes"> Or another one, i want to shrink a varchar field length: DBAINTRN The table and its related objects will be dropped and re-created. Changing the definition of a column attribute involves a LOAD operation. If the database is recoverable, a load copy will be taken for the table being altered. The load copy will be saved as SUPERDUDE.AUDIT_LOG_table.dat. You can delete the load copy file once a backup has been taken for all the related tablespaces. Would you like to proceed? Would i like to proceed? Well, i don't know. i didn't ask you to drop and re-create the table and all it's dependant objects. i told you to shink a column from 128 to 100 characters. You do whatever it is you have to do to do it. Are you trying to ask me if i'm okay with the various steps you have to do to perform that operation? Why wouldn't i be? My only choices are to either save my changes or not. i said save, so do it. Some people will argue, "But the table will have to be dropped and renamed and all keys will recreated. It could potentially be a long operation and maybe the user didn't realize it would take so long to do - so we need to get their permission before doing it. Or worse yet, what if there's a power failure, and something is left in a broken state. At least the user knows that DB2 was performing this relativly big operation. We have to inform the user before we just go ahead and do this." No you don't. i said save, you save. You do whatever it is you do when you have to save. If it takes a long time, i'm okay with that, because i was changing table structure - i'll expect it to take a long time. And with the nulls. Why can't it implicitly cast a NULL to the type of the column? What alternative is there? You are perfectly okay casting other types around, why not NULL? Null is the lack of data, it is nothing, it is not being. People will argue, "But DB2 is a strongly typed system, and null doesn't have the same type as integer. What if the user didn't mean to put a NULL into that column, and we went ahead and did it anyway, that would be bad. If the user really meant to put null in this field they should indicate that by casting it to the proper target data type." No i don't. i said put null in this column. Your choices are to put it in the column, or not. So why would you not do it? Implicitly cast it and get it done. Just do it. i should have to tell twice, when everything knows that's what i want done. Even the village idiot comes to that conclusion. </StartOfRanging> >>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 guess this is where some help with examples would be extraordinarily useful. >>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". SQL Reference Volume 1 Contents About this book Chapter 1. Concepts Chapter 2. Language elements Chapter 3. Functions Chapter 4. Queries Appendix A. SQL limits ... Appendix Q. Notices Index Contacting IBM SQL Reference Volume 2 Contents About this book Statements Appendix A. DB2 Universal Databse technical information Appendix B. Notices Index Contacting IBM These are two PDF files that are referred to by the online documentation. i reach the online documentation from Control Center by hitting: Help->About On the web-site, i see Reference SQL How to read the syntax diagrams Common syntax elements Language elements Functions Procedures Queries Statements Reserved schema names and reserved words SQL statements allowed in routines Communications areas, descriptor areas, and exception tables Explain tables Explain register values Japanese and traditional-Chinese extended UNIX code (EUC) considerations Backus-Naur form (BNF) specifications for DATALINKs >>, 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. i gotta find this book, web-site, pdf, help file, or eBook you got. |