| Updating columns w/ same value - will DB2 optimize for me? |
|
 |
Index ‹ database
|
- Previous
- 3
- Make DB2 UDB Date and Time Stamp Consistent With Win2000Hello,
As somebody fairly new to DB2..
I am looking to migrate a V6 (No laughter please, it was a sandbox
system hardly used at all) UDB/Win2000 system used by SAP. I noticed
that the Win2000 server in which it resides is off by
about 20 minutes so I changed the time from the Win2000 Control Panel.
Now, SAP is complaining that DB2's date/time is inconsistent with
that on the OS.
How can I make my DB date/time consistent with that of Win2000?
Thanks,
DF
- 3
- How to query a set of tables with identical structure?I need to do the same SELECT on a number of tables that have identical
structure (same column names and types). Of course I can query the
tables one by one, but I want the query result as a single result set.
How do I do that?
Regards,
Bertwim van Beest
- 3
- 4
- Missing Tablespace link error message misleading
If, by some quirk of fate, you manage to delete the logical link to a
tablespace in pg_tblspc, then you try to access a table in that
tablespace, you get an error message that says:
ERROR: could not open relation 15254222/17230/15254223: No such file or
directory
This is somewhat misleading. The message implies you do not have the
relation, when in fact you do not have the tablespace.
Anyway, I'm not expecting anyone to sweat to fix this, since the obvious
solution is not to delete the tablespace link in the first place!
You can recover the error by replacing the logical link.
Anyway...enjoy the speculation as to how this was even discovered :-)
--
Best Regards, Simon Riggs
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
- 9
- Migrating from SQL ServerI'm migrating a huge application wrote for sql server, this software
offers the option for al user to write their own reports. The report
editor was developed many years ago, and it uses the TransactSQL
native syntax for LEFT OUTER JOINS (*=).
The problem is, that syntax obtains diferents data sets than the ANSI
form (FROM T1 LEFT OUTER JOIN T2).
Sample:
Consider that the t3.c column does not contains the 999 value
When execute this sentence:
Select * from T1, T2 LEFT OUTER JOIN T3 ON T2.C = T3.C where T1.B =
T2.B and t3.c = 999
You will obtain
A B C B C D
C D E
----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- -----------
(0 row(s) affected)
but, if you write it in the native transactsql syntax:
Select * from T1,T2,T3 where T1.B=T2.B and T2.C*=T3.C and t3.c = 999
you will obtain
A B C B C D
C D E
----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- -----------
1 1 1 1 1 1
NULL NULL NULL
2 2 2 2 2 2
NULL NULL NULL
(2 row(s) affected)
So there is an inconsitency in the data set, but i HAVE to preserve
this behavior.
Have anyone any idea on what i have to do on DB2 to obtain the same
result?
Thanks
- 9
- SYSSH200Hi,
I have a statement event monitor on and am looking at the output. I
see a lot of executions of package SYSSH200. I looked in
syscat.statements to see what it executes and didn't find any
matches. How come and where can I find the section/statement
information for the package?
Thanks
Lew
- 12
- DB2 Java IMPORT-Statement / truncateHi Knut,
sorry I thought about doing an IMPORT.
So can I use the IMPORT-Statement in JDBC?
Thank you...
Stefan
Knut Stolze schrieb:
> Stefan wrote:
>
> > Hi all,
> > two questions:
> > - is there a way to call the INSERT-Statement from Java (JDBC).
> > (import from file of del replace into table)
>
> You can run a simple INSERT statement through JDBC without any problem.
>
> > - in db2 there is no truncate-statement, is it?
> > Is in db2 something like that?
>
> Have a look at the ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY WITH EMPTY
> TABLE statement.
>
> --
> Knut Stolze
> DB2 Information Integration Development
> IBM Germany
- 13
- M`I,5-Per secution - why the securi ty services ?-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
-= why the. security services? -=
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
You may ask, why do I think. the "they" referred to are the security
services? Is. there any evidence that there is a single source, as opposed
to a loosely based. "whispering campaign" amongst many people? Even if there
is a single source, is there. any evidence that "they" are professional
"buggers" as opposed to amateurs, or perhaps people. working for a privately
funded. organization?
a) As to the question of a. single source versus something more fragmented;
it is quite obvious. that there is a single source from the way the campaign
has been carried. out. Since things have been repeated verbatim which were
said in my home, there must be one group which does the watching. and
listening. Since on several occasions (mainly during travel). people have
been planted in close proximity and rehearsed in what they were to. say, it
follows that someone must have done the planning for. that, and again a
single source is. indicated.
b) So why. couldn't it be amateurs? Why couldn't it be a private
organisation, for example a private detective agency paid. to manage the
campaign and undertake the technical aspects?. Some detective agencies are
unscrupulous as has been proved on. the occasions in the past when they've
been exposed or caught; they too can have access to the. bugging technology
deployed; and there are reported cases of MI5. paying private eyes to do
their. dirty work (against peace campaigners and similar enemies of the
state) on. the understanding that if they were caught then they could deny
all knowledge. Why couldn't that be the. case?
The main factor pointing. to direct security service involvement (as opposed
to amateurs or. MI5 proxies) is the breadth of their access to the media in
particular, and the fact that the. television companies are so involved in
the. campaign. The BBC would not directly invade someone's home themselves,
since it would not be within their remit. to allocate personnel or financial
resources to do so. An organisation. of their stature would not take part in
a campaign set up by private sources. The only people. they would take
material. from would be the security services, presumably on the assumption
that if the cat. ever flew out of the bag yowling it would be MI5 who would
take. the consequences.
State sponsorship for these acts of psychological. terrorism is also
indicated by duration; support for. over six years for a team of three or
four people would be beyond the means and will of most private. sources.
The viciousness of the slanders and personal denigration also. points to
MI5; they traditionally "protect" the British state from politicians of. the
wrong hue by character assassination, and in this case. are using their
tried and tested methods to murder with words an enemy they have. invented
for. themselves.
And there. are precedents. Diana and Hewitt were alleged to have been filmed
"at it" by an Army intelligence team which had operated. in Northern
Ireland, these allegations. were made by someone called Jones who had been
on the team. His statements were denied. by the defence establishment who
tried to. character-assassinate by describing him as the "Jones twins".
Funny how if you tell the truth, then you. must be ill, isn't it? Thought
only communists behaved like. that?
Hewitt later said that he'd. been spoken to by someone in the army who
revealed the. existence of videotapes of him and Diana, and that the tapes
would be published if any attempt was. made by them to resume their
association.
2384
- 13
- uncsubscribeunsubscribe
--
Best regards,
Alexey mailto:email***@***.com
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to email***@***.com)
- 13
- 13
- multiple instance monitoringHow are multiple instances monitored? For example, there are 30
instances running on a AIX BOX running DB2 V8, is ps -ef |grep
"inst[0-2][0-9]" the best way to see if the instance is running, where
instanes are named inst01 thru inst29.
Or is this set up in tools like Health Monitor?
- 13
- A design questionThis question is not strictly Oracle only but as I'm using Oracle 8i I
couldn't think of a better place to post it!
Anyway my problem/qustion is that I have a record that has in access of 200
fields - all the fields relate entirely to a record. Virtually all fields
are optional e.g NULL.
Just wondering is there is a better way to manage them as the users want an
audit log of changes so before every update the whole record has to written
to the audit table - this can happen if theres only one change. I know I
could work out a way to just write changed fields values but time is so
tight I have to write the entire record for now.
I could move blocks of fields in to other related tables but there will
ALWAYS be a one to one relationship so it seems wrong to me anyway to
separate them.
Any suggestions/ideas?
thanks
harry
- 14
- BerkeleyDB CDBIn article <email***@***.com>,
email***@***.com (Paul Marquess) writes:
>
> Nope. "make test" should run the version in the blib directory and
> ignore whatever is installed. Do you have an environment variable,
> like LD_LIBRARY_PATH that points to the Berkeley DB 3.3 library?
>
The following test seems to confirm that the tests are using at least
the BerkeleyDB.pm that is currently installed?
#!./perl -w
use strict ;
BEGIN {
unless(grep /blib/, @INC) {
chdir 't' if -d 't';
@INC = '../lib' if -d '../lib';
}
}
use BerkeleyDB;
use t::util ;
foreach (keys %INC) {
print STDERR "$_ : $INC{$_}\n";
}
Here is the output:
t/inc...............Exporter.pm : /System/Library/Perl/Exporter.pm
Carp.pm : /System/Library/Perl/Carp.pm
XSLoader.pm : /System/Library/Perl/darwin/XSLoader.pm
strict.pm : /System/Library/Perl/strict.pm
vars.pm : /System/Library/Perl/vars.pm
re.pm : /System/Library/Perl/darwin/re.pm
warnings/register.pm : /System/Library/Perl/warnings/register.pm
warnings.pm : /System/Library/Perl/warnings.pm
t/util.pm : t/util.pm
Symbol.pm : /System/Library/Perl/Symbol.pm
UNIVERSAL.pm : /System/Library/Perl/UNIVERSAL.pm
File/Basename.pm : /System/Library/Perl/File/Basename.pm
BerkeleyDB.pm : /Library/Perl/darwin/BerkeleyDB.pm
Exporter/Heavy.pm : /System/Library/Perl/Exporter/Heavy.pm
File/Path.pm : /System/Library/Perl/File/Path.pm
AutoLoader.pm : /System/Library/Perl/AutoLoader.pm
FAILED before any test output arrived
t/join..............ok
Note that the source for BerkeleyDB.pm is the currently installed one?
/Nathan.
- 14
- Select turnedOff/turnedOn time of interruptor in databaseHi,
I have a db table like this :
DATETIME | ON_OFF |
2005-03-23 14:45:00 0
2005-03-23 14:45:15 0
2005-03-23 14:45:30 1
2005-03-23 14:45:45 1
2005-03-23 14:46:00 1
2005-03-23 14:46:15 1
2005-03-23 14:46:30 0
2005-03-23 14:46:45 0
2005-03-23 14:47:00 0
2005-03-23 14:47:15 1
2005-03-23 14:47:30 1
2005-03-23 14:47:45 0
2005-03-23 14:48:00 0
2005-03-23 14:48:15 0
It represent an interruptor on/off sequence.
Iwant to get the datetime off each state variation and know if it's a
turnedOn or a turnedOff value.
I don't see another solution that loop throw a resultSet of select *
query
and detect programmaticaly the changes.
Does someone know a way of doing this with SQL queries ???
Thanks
- 16
- resource monitoringI am running a few web based applications with postgres on the backend.
We have a few app servers load balanced all connecting to a dedicated
postgres server. As usage on the applications increases I want to
monitor my resources so that I can anticipate when I will hit
bottlenecks on the db server. That way we can do upgrades or
optimizations before our performance get's unacceptable.
We are monitoring cpu usage, memory usage, and network traffic. I would
also like to monitor io utilization but am not quite sure how to do
that. Does anyone here know to effetively monitor io to the raid (I'm
guessing that this could be measured with the raid management software
but was hoping their were some standard system commands.) or other io
that I should be measuring?
What bottlenecks could I hit and what are some good stats to check to
anticipate when I am moving towards one of those bottlenecks.
Thanks,
Rick
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
|
| Author |
Message |
allenj
|
 Posted: 2004-2-6 0:25:46 |
Top |
database, Updating columns w/ same value - will DB2 optimize for me?
DB2 UDB 7.2 WSE
Fixpak 9
Linux Red Hat 7.3
I have some library code (written in Java, if that matters) that
processes maintenance screens that are presented to the end-users
as forms in a browser.
Because the code is generic, working against any table, I am
dynamically generating UPDATE statements that update
every column in a row, regardless of whether the value in
a column has actually changed. ie - I might be updating columns
w/ the same value that they already have.
I have though about optimizing this - checking to see what the value
is before I update, and generating a shorter, smarter UPDATE statement
that references only columns whose values have actually changed.
But do I need to be overly concerned about this? Will the DB2 engine
notice that I am updating columns w/ the same value they already have,
and optimize things for me?
Any thoughts appreciated...
aj
|
| |
|
| |
|
Mark A
|
| Posted: 2004-2-6 0:29:00 |
Top |
database >> Updating columns w/ same value - will DB2 optimize for me?
<email***@***.com> wrote in message
news:email***@***.com...
> DB2 UDB 7.2 WSE
> Fixpak 9
> Linux Red Hat 7.3
>
> I have some library code (written in Java, if that matters) that
> processes maintenance screens that are presented to the end-users
> as forms in a browser.
>
> Because the code is generic, working against any table, I am
> dynamically generating UPDATE statements that update
> every column in a row, regardless of whether the value in
> a column has actually changed. ie - I might be updating columns
> w/ the same value that they already have.
>
> I have though about optimizing this - checking to see what the value
> is before I update, and generating a shorter, smarter UPDATE statement
> that references only columns whose values have actually changed.
>
> But do I need to be overly concerned about this? Will the DB2 engine
> notice that I am updating columns w/ the same value they already have,
> and optimize things for me?
>
> Any thoughts appreciated...
>
> aj
I don't think DB2 will optimize it for you. If you use one update statement
per row, the CPU usage will be a bit more, but physical I/O for the table
should be the same if you update columns that haven't changed. But, there
will be extra I/O (I assume) to update the index columns. Also, DB2 will
need to log the whole row, instead of maybe just logging part of the row.
I think it depends on the volume of the transaction as to whether it is
worth optimizing the SQL dynamically in the program.
|
| |
|
| |
|
Serge Rielau
|
| Posted: 2004-2-6 2:13:00 |
Top |
database >> Updating columns w/ same value - will DB2 optimize for me?
Actually DB2 does detect this at runtime by default.
DB2 logs everything between the first columns changed and the last
column changed.
I can't confirm off hand whether this optimization includes the indexes
or not.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
|
| |
|
| |
|
allenj
|
| Posted: 2004-2-6 2:53:00 |
Top |
database >> Updating columns w/ same value - will DB2 optimize for me?
Hi Serge. Thanks for replying.
So you are saying that optimizing my updates will not get me anything?
DB2 is already doing it?
Does this apply to v7? v8? Both?
thanks
aj
On Thu, 05 Feb 2004 13:13:23 -0500, Serge Rielau
<email***@***.com> wrote:
>Actually DB2 does detect this at runtime by default.
>DB2 logs everything between the first columns changed and the last
>column changed.
>I can't confirm off hand whether this optimization includes the indexes
>or not.
>
>Cheers
>Serge
>--
>Serge Rielau
>DB2 SQL Compiler Development
>IBM Toronto Lab
|
| |
|
| |
|
Serge Rielau
|
| Posted: 2004-2-6 3:50:00 |
Top |
database >> Updating columns w/ same value - will DB2 optimize for me?
For sure for V8. I don't have the impression it's new either.
Again I don't know of hands whether that optimization includes index
updates or only the logs.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
|
| |
|
| |
|
Mark A
|
| Posted: 2004-2-6 4:08:00 |
Top |
database >> Updating columns w/ same value - will DB2 optimize for me?
"Serge Rielau" <email***@***.com> wrote in message
news:bvu6qi$gs6$email***@***.com...
> For sure for V8. I don't have the impression it's new either.
> Again I don't know of hands whether that optimization includes index
> updates or only the logs.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab
So you are saying that DB2 does not log columns which are not actually
changed even if they are in the "set" clause of the update statement? I am
not talking about columns which are in between the first and last column
changed, which get logged anyway.
|
| |
|
| |
|
Serge Rielau
|
| Posted: 2004-2-6 4:28:00 |
Top |
database >> Updating columns w/ same value - will DB2 optimize for me?
Found it.. google my newest favorite toy :-)
http://www.db2.jp/db2manual/en_US/index.htm?openup=core/c0011223.htm
DB2ASSUMEUPDATE Default=OFF
Values: ON, OFF
When enabled, allows DB2 to assume that all fixed length columns
provided in an UPDATE statement are in fact being changed. This
eliminates the need for DB2 to compare the existing column values to the
new values provided to determine if the column is actually changing.
Using this registry variable when columns are provided for update (for
example, in a SET clause) but are not actually being modified can result
in additional logging and index maintenance.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
|
| |
|
| |
|
Mark A
|
| Posted: 2004-2-6 6:42:00 |
Top |
database >> Updating columns w/ same value - will DB2 optimize for me?
"Serge Rielau" <email***@***.com> wrote in message
news:bvu92p$h1r$email***@***.com...
> Found it.. google my newest favorite toy :-)
> http://www.db2.jp/db2manual/en_US/index.htm?openup=core/c0011223.htm
> DB2ASSUMEUPDATE Default=OFF
> Values: ON, OFF
>
> When enabled, allows DB2 to assume that all fixed length columns
> provided in an UPDATE statement are in fact being changed. This
> eliminates the need for DB2 to compare the existing column values to the
> new values provided to determine if the column is actually changing.
> Using this registry variable when columns are provided for update (for
> example, in a SET clause) but are not actually being modified can result
> in additional logging and index maintenance.
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab
What about varchar? Are they always logged if in the set statement?
|
| |
|
| |
|
Serge Rielau
|
| Posted: 2004-2-6 10:00:00 |
Top |
database >> Updating columns w/ same value - will DB2 optimize for me?
The way I disect this text is that by default will always verify for ALL
columns whether they have in fact changed. This is the default and what
OP hopes for.
In V8.1.4 the dial was added to assume a well written app and save that
codepath. Presumably there is only a savings for fixed columns (since
they are at fixed locations in the row (?)).
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
|
| |
|
| |
|
Jonathan Leffler
|
| Posted: 2004-2-6 15:16:00 |
Top |
database >> Updating columns w/ same value - will DB2 optimize for me?
email***@***.com wrote:
> So you are saying that optimizing my updates will not get me anything?
One aspect to consider is how many update statements you are
preparing. If the answer is 1 and you're using placeholders for all
the values, then it is easier to just use that one statement. If,
instead, you write a new statement for each operation, then things are
dramatically slower. If you create one statement for each combination
of N updated columns out of M columns in the table, you end up with a
lot of statements.
And if everything is pre-bound - which DB2 tends to do, but Informix
(my main area of expertise) does not - then the issue may be the size
of your package.
I don't know where the trade-offs occur, nor the relative costings.
However, I'd guess that you're better off using one or a few prepared
statements and living with identity updates on some columns than with
managing hundreds of statements (and C(N,M) gets quite big quite
quickly, if you remember your Pascal's Triangle from maths classes).
--
Jonathan Leffler #include <disclaimer.h>
Email: email***@***.com, email***@***.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
|
| |
|
| |
|
| |
|
Index ‹ database |
- Next
- 1
- APPLICATION DEVELOPER-VISUAL : N. CaliforniaPosition Description : INSITE MES DESIGNER. DEFINE MES REQUIREMENTS
WITH CUSTOMER. ANALYZE FUNCTIONAL REQUIREMENTS AND TRANSLATE THEM
INTO AN INSITE OBJECT MODEL. UTILIZE EXPERIENCE AND EXPERTISE OF
INSITE S BASE PRODUCT CAPABILITIES AND EXTEND ADD-ON OFFERINGS.
MUST HAVE EXPERIENCE DEVELOPING DEPLOYING INTEGRATING CAMSTAR S
INSITE MES PRODUCT.Request Type:REQUEST FOR SERVICE
Required Skills ( years ): WINDOWS 2000(2), WINDOWS NT(2), MQ
SERIES(2), VISUAL C++(3), DB2(2), XML(2), DATA MODELER(3), OO
DESIGN(2), OO PROGRAMMING(2), BUSINESS ANALYST(2), WRITTEN ORAL
COMMUNICATION(2)
Additional T's And C's:EXPERIENCE WITH MANUFACTURING EXECUTION
SYSTEMS-DEVELOPMENT AND OR SUPPORT IN A PREVIOUS ENGAGEMENT FOR A
MANUFACTURING COMPANY. NEED EXPERIENCE WITH CAMSTAR S INSITE MES
PRODUCT.
Work Location:SAN JOSE, CA, CALIFORNIA, USA
Start Date:17-MAY-04
End Date:30-NOV-04
Rate $40/hr
Please send resume to email***@***.com. You will get a reply !
Thom Birdsell
FTS Unlimited, Inc
(816) 623-9370
ITUG Member Since 1995
COMPAQ Solutions Partner Y2000
H.P. Encompass - Y2002 & 2003
- 2
- Why we still see some reports of "could not accessGaetano Mendola wrote:
> Are you going to fix it for the 8.0 and/or back patch it ?
http://archives.postgresql.org/pgsql-committers/2004-10/msg00229.php
http://archives.postgresql.org/pgsql-committers/2004-10/msg00191.php
plus backpatches to older branches (REL7_3_STABLE, REL7_2_STABLE).
Has there been any thought about putting out another 7.4 release with
this fix?
-Neil
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
- 3
- extract year()How to select records from a choosen year?
Something like this
select myfield from mytable where year(myfield)=2006
Regards R. Stormo
- 4
- array_to_column functionKind people,
Here's something I came up with, having accidentally discovered the
ARRAY() constructor (BTW, I think at least some pointer to it should
be in the array section of functions & operators).
CREATE OR REPLACE FUNCTION array_to_column (ANYARRAY)
RETURNS SETOF ANYELEMENT
IMMUTABLE
LANGUAGE plpgsql
AS $$
BEGIN
IF (position('][' IN array_dims($1)) <> 0)
THEN
RAISE EXCEPTION 'Only 1-dimensional arrays are allowed!';
END IF;
FOR i IN array_lower($1, 1) .. array_upper($1, 1)
LOOP
RETURN NEXT $1[i];
END LOOP;
RETURN;
END;
$$;
Thanks to Markus Bertheau aka ska-fan for help with removing an
unneeded regex compare and with spelling. :)
Cheers,
D
--
David Fetter email***@***.com http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
- 5
- [HACKERS] Is "trust" really a good default?This is a multi-part message in MIME format.
>The only part of this discussion that I'd really be prepared
>to buy into
>is the part about *if* you use -W or --pwfile, then set up pg_hba.conf
>with MD5 as the default auth (because that's probably what the user
>wants anyway). But otherwise I think we should leave initdb's behavior
>alone. I do not agree with trying to force people to use passwords.
Ok. Here is a patch that does this. I still think there should be a
warning when trust is set, but I'm clearly not convincing enough about
this.
Might still be worth adding "--ident" as a parameter anyway, but in that
case only to help the distros that need it. Or not, because they already
have a way to deal with it.
//Magnus
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
- 6
- Message : DIA8309C Log file was full.We are on DB2 8.1 FixPack 3 and getting "DIA8309C Log file was full"
during testing. I understand long running updates/inserts without
commit or rollback can cause this issue. But ..
1)Can a long running transaction with only SELECT statements cause the
Log to become full? Do we need to commit SELECT statements? The
transaction has many SELECT statements and can last for days without
doing a commit. All SELECTs have 'for read only' clause except one.
2)Can a transaction that calls Stored Procedures with "CURSOR WITH
RETURN TO CLIENT" cause Log to become full? The procedure returns the
results of SELECT statement done with 'for read only' clause.
Thanks for the help.
-Jane
- 7
- == PostgreSQL Weekly News - Februrary 23rd 2004 ==== PostgreSQL Weekly News - February 23rd 2004 ==
The biggest news of the week was probably the pre-announcement of both
7.3.6 and 7.4.2 releases that should be made available in the next few weeks.
We are likely to see some back-patching this week into the 7.3.x branch, so
keep an eye out for the full changelog that will be included along with each
release announcement.
Meanwhile work continues on 7.5 of course. Following up on some work
from the previous week, we can now make use of statistics on index
expressions. Functionality to log disconnections was added into the
postgresql.conf log options. Some inconsistencies in the error messages
received with incorrect input to the oid type were cleared up. Some
significant improvements to the supporting documentation for the cost based
vacuum delay feature were added; if you're curious about this feature you
might want to check out the developer docs. After quite a bit of discussion
on -hackers, psql's input scanner was rewritten to use a flex-generated
lexer, which is used on both SQL command text and backslash commands. This
should make it easier to track the backend's SQL lexer behavior since nearly
identical flex rules are now used in psql. On the win32 front, a patch
implementing settimer() was implemented which means deadlock detection and
statement_timeout are now functional for win32.
Several enhancements to the contrib module dbmirror were incorporated
including replication of sequence operations via setval/nextval, support for
logging to syslog, support for writing SQL statements to files instead of
directly to a slave database, and the addition of several new options within
the config files.
Finally the web team would like to report that we now have RSS feeds
of project news from GBorg available at http://gborg.postgresql.org/news.rss.
You can view the feed in your favorite RSS tool (evolution has this ability)
and we are hoping to get this feed carried by other Open Source web sites (if
you want to help on the latter please post a note to the -advocacy mailing
list). Many thanks go out to David Costa from www.dotgeek.org for donating
time and code to help get this working, hopefully soon we will have official
RSS feeds of the main PostgreSQL news and events as well.
== PostgreSQL Product News ==
SCO unveils OpenServer Update Pack 2
http://www.vnunet.com/News/1152884
pgin.tcl-2.0.0 released
http://gborg.postgresql.org/project/pgintcl/news/newsfull.php?news_id=154
Make Database Reporting Easier with pgexport 0.9.1
http://gborg.postgresql.org/project/pgexport/news/newsfull.php?news_id=153
OLE DB initial version uploaded
http://gborg.postgresql.org/project/oledb/news/newsfull.php?news_id=152
DBD::Pg 1.3.2 Now in Beta Testing
http://archives.postgresql.org/pgsql-general/2004-02/msg01010.php
== PostgreSQL In the News ==
Comparing PostgreSQL to a "Big Proprietary Database"
http://research.rem.nl/performance.html
== Upcoming Events ==
Chemnitzer Linux-Tag: Chemnitz, Germany: March 6-7
Peter Eisentraut will organize a table and will make a presentation
http://www.tu-chemnitz.de/linux/tag/2004/allgemeines/
== PostgreSQL Weekly News - February 23rd 2004 ==
Don't forget to read Elein Mustain's Weekly Summary of the PostgreSQL
General Mailing List http://www.varlena.com/GeneralBits/
On the Web:
http://www.postgresql.org
http://advocacy.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
- 8
- SQL syntax extentions - to put postgres ahead in the raceThis is a multi-part message in MIME format.
Hi,
I am a developer working with many databases, as a part of my job. I use heavy SQL queries and have become somewhat of an expert in SQL, including tricks and workarounds of the limitation of the language.
I feel like a lot of the workarounds could be avoided with adding a few new operators to the SQL language that should be rather easy to support but would give a vast improvement and probably a leap of performance in many common queries. I write you about this hoping that you would support these operators (even though they are not in the ANSI) and thereby position PostGres as a leader and not just a follower! I personaly have a great interest in seeing open source software making it big time! So please - forward this to one of your more technical developers/executives and hopefully I will get to see it in the next version of PostGres:
1) The operator "of max":
suppose I have a table "grades" of 3 fields: class/student/grade where I store many grades of many students of many classes. I want to get the name of the highest scoring student in each class. Note that there may be many students with the same grade, but for starters let's say there is a primary key of class+grade.
My query would now be:
select student from grades where class+'#'+grade in
(
select class+'#'+max(grade) from grades group by class
) a
This means working the query twice - and relying on louzy conversion of the grade from numerical to textual.
We could also use:
select student from grades where student in
(
select student from grades group by class
having class+'#'+grade = max(class+'#'+grade)
) a
This is even worse!
The optimal would be to introduce a new operator "of max" that would be used as follows:
select student of max(grade) from grades group by class
simillarly one should support "of min" and "of any" (brings a representative of the group)
2) aggregated concatenation:
Traditionally the SQL language has referred from supporting order dependent operators from taking a role in aggregated functions. This means that since the query: "select class, grade from grades" does not ensure the order of the records returned, the operation sum() is supported (i.e. select class, sum(grade) from grades group by class) but other operations that would be order dependent are not supported.
I think this approach should be revised. In many cases one would want to get a list of the student names delimited with a comma. It would be great if one could write:
select class, list(student, ',') from grades group by class
and get
class list
----- ----
class1 john, ruth,...
This is of course an order dependent operation so the syntax can either be:
select class, list(student, ',') from grades group by class order by student, grade
in which case the list would be ordered before the list is created, or if no particular order is requested the concatenation of the names should be in an arbitrary order.
Well - that's all for now :-)
Good luck!
Ram
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-1255">
<META content="MSHTML 6.00.2600.0" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Hi,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I am a developer working with many databases, as a
part of my job. I use heavy SQL queries and have become somewhat of an expert in
SQL, including tricks and workarounds of the limitation of the
language.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I feel like a lot of the workarounds could be
avoided with adding a few new operators to the SQL language that should be
rather easy to support but would give a vast improvement and probably a leap of
performance in many common queries. I write you about this hoping that you would
support these operators (even though they are not in the ANSI) and thereby
position PostGres as a leader and not just a follower! I personaly have a
great interest in seeing open source software making it big time! So please -
forward this to one of your more technical developers/executives and hopefully I
will get to see it in the next version of PostGres:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>1) The operator "of max":<BR>suppose I have a
table "grades" of 3 fields: class/student/grade where I store many grades of
many students of many classes. I want to get the name of the highest scoring
student in each class. Note that there may be many students with the same grade,
but for starters let's say there is a primary key of class+grade.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>My query would now be:<BR>select student from
grades where class+'#'+grade in <BR>(<BR> select
class+'#'+max(grade) from grades group by class<BR>) a</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>This means working the query twice - and relying on
louzy conversion of the grade from numerical to textual.<BR>We could also
use:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>select student from grades where student in
<BR>(<BR> select student from grades group by class <BR> having
class+'#'+grade = max(class+'#'+grade)<BR>) a</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>This is even worse!</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>The optimal would be to introduce a new operator
"of max" that would be used as follows:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>select student of max(grade) from grades group by
class</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>simillarly one should support "of min" and "of any"
(brings a representative of the group)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>2) aggregated concatenation:<BR>Traditionally the
SQL language has referred from supporting order dependent operators from taking
a role in aggregated functions. This means that since the query: "select class,
grade from grades" does not ensure the order of the records returned, the
operation sum() is supported (i.e. select class, sum(grade) from grades group by
class) but other operations that would be order dependent are not
supported.<BR>I think this approach should be revised. In many cases one would
want to get a list of the student names delimited with a comma. It would be
great if one could write:<BR>select class, list(student, ',') from grades group
by class<BR>and get</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>class list<BR>----- ----<BR>class1
john, ruth,...<BR>.<BR>.<BR>.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>This is of course an order dependent operation so
the syntax can either be:<BR>select class, list(student, ',') from grades group
by class order by student, grade<BR>in which case the list would be ordered
before the list is created, or if no particular order is requested the
concatenation of the names should be in an arbitrary order.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Well - that's all for now :-)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Good luck!</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Ram</FONT></DIV></BODY></HTML>
- 9
- insert into.. (select count(*),sum(xx) from <> where <>) giving wrong aggregate valuesArun Srinivasan wrote:
> Hi
> I have a sql that I use within an SP..
> INSERT INTO xx.yyyy( c1,cnt1,sum1 )
> (SELECT COUNT(*), sum(yyy) FROM xx.yyyy_dtl WHERE<> GROUP BY <>)
Your number of columns doesn't match up!
There actually has been an APAR around DB2 accepting mismatched column
lists on INSERT with the obvious interesting side-effects.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
- 10
- How to specify the default namespace for queries?I am using DBXml and I cannot figure out how to specify the default
namespace for queries.
I have created a container with several XML documents like this one:
<foo xmlns="http://www.bar.org"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.bar.org foo.xsd">
<bar>
...
</bar>
<bar>
...
</bar>
</foo>
By using the dbxml utility I can succesfully perform the following
operations:
dbxml> openContainer foo.dbxml
dbxml> cquery "/foo/bar"
0 objects returned for eager expression '/foo/bar'
dbxml> setNamespace "" "http://www.bar.org"
dbxml> cquery "/foo/bar"
59042 objects returned for eager expression '/foo/bar'
So, binding the "" prefix to the "http://www.bar.org" makes
"http://www.bar.org" the default namespace and it correctly returns me
all the <bar> elements in the <foo> one.
I tried to do the same in my Java application:
XmlQueryContext xqc = xmlManager.createQueryContext();
xqc.setNamespace("", "http://www.bar.org");
XmlQueryExpression qe =
xmlManager.prepare("collection(\"foo.dbxml\")/foo/bar", xqc);
XmlResults results = qe.execute(xqc);
But it returns 0 objects in the XmlResults collection.
What's wrong with this?
Why don't I get the 59402 objects dbxml correctly finds?
Thank you for your help.
- 11
- SMALLINT casting difference on Windows vs AIXHi DB2 newsgroup,
I have encountered a difference between our development DB2 on Windows
and the test environment on AIX.
If using the LOCATE scalar function with a SMALLINT as LENGTH parameter
the same statement produces different results on Windows (correct) than
on AIX (wrong). The same statement using an INTEGER instead of SMALLINT
produces the correct result in both environments.
Is there a reason why using a SMALLINT instead of an INTEGER leads to
another result in both environments? I tried to specify the string unit
(CODEUNITS16, CODEUNITS32, or OCTETS) but the results still differ.
As you can see below the statement 4 and 5 shows that the error handling
differs and that the SMALLINT handling or casting may be problem here. I
see no reason why the SMALLINT casting should fail.
Both databases are on current FixPack level (V9 FP5), using the same
codepage (1208) and same codeset (but displays in differing cases: Win:
utf-8, AIX: UTF-8).
Any hints or tips?
Thanks in advance,
Michael
Test statements:
values locate('A', 'A', CAST(1 AS SMALLINT)); --different
values locate('A', 'A', CAST(1 AS INTEGER)); --same
values locate('A', 'A', CAST(CAST(1 AS SMALLINT) AS INTEGER)); --same
values locate('A', 'A', CAST(0 AS SMALLINT)); --nearly the same,
differing error message
values locate('', '', CAST(0 AS SMALLINT)); --different
Result on Windows:
db2 => values locate('A', 'A', CAST(1 AS SMALLINT));
==> 1
db2 => values locate('A', 'A', CAST(CAST(1 AS SMALLINT) AS INTEGER));
==> 1
db2 => values locate('A', 'A', CAST(1 AS INTEGER));
==> 1
db2 => values locate('A', 'A', CAST(0 AS SMALLINT));
==> SQL0171N The data type, length or value of argument "start" of
routine "SYSIBM.LOCATE" is incorrect. SQLSTATE=42815
db2 => values locate('', '', CAST(0 AS SMALLINT));
==> SQL0171N The data type, length or value of argument "start" of
routine "SYSIBM.LOCATE" is incorrect. SQLSTATE=42815
Result on AIX:
db2 => values locate('A', 'A', CAST(1 AS SMALLINT));
==> 0
db2 => values locate('A', 'A', CAST(CAST(1 AS SMALLINT) AS INTEGER));
==> 1
db2 => values locate('A', 'A', CAST(1 AS INTEGER));
==> 1
db2 => values locate('A', 'A', CAST(0 AS SMALLINT));
==> SQL0171N The data type, length or value of argument "start" of
routine "" is incorrect. SQLSTATE=42815
db2 => values locate('', '', CAST(0 AS SMALLINT));
==> 1
- 12
- PostgreSQL 8.0 Beta 4 - Qualified ORDER BY column name not working on UNION queryI am running PostgreSQL 8.0 Beta 4 (Windows installer) on Windows XP Pro
SP2.
The query below has three ORDER BY clauses. When I execute it with the
uncommented clause an error results. Some further commentary is
included in the comments below. I would expect the uncommented clause
to produce the same result as the other two commented out clauses.
-- Qualified ORDER BY column name not working on UNION query (simple
example).
SELECT TURBINE_USER.USER_ID FROM TURBINE_USER WHERE TURBINE_USER.USER_ID = 1
UNION
SELECT TURBINE_USER.USER_ID FROM TURBINE_USER WHERE TURBINE_USER.USER_ID = 2
-- The following works.
--ORDER BY 1 ASC
-- The following does not work when I would perhaps expect it to (it
certainly works when a non-UNION query is used).
ORDER BY TURBINE_USER.USER_ID ASC
-- The following also works as I would expect it to unless more than one
USER_ID column appears in the column list.
--ORDER BY USER_ID ASC
-- For the case that does not work the message is:
-- I haven't tried a case where the sort column (in this case USER_ID)
appears more than once in the column list.
I will not be surprised at all if the above is expected behaviour, in
which case I apologise for wasting your time.
Incidentally, the error message that occurs if I EXPLAIN the above query
is as above but with an additional line that seems out of place to me:
Query inserted one rows with OID 0.
Also, if I EXPLAIN this query with one of the working ORDER BY clauses
it produces a result, but if I select the ANALYSE option (I am using the
pgAdmin III that comes bundled with the Beta 4 Windows installer) I get
something completely nonsensical:
ERROR: syntax error at or near "ROLLBACK" at character 897
Thanks,
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
- 13
- slony-I rpmAnyone know of a src.rpm for slony-I to be had?
Thanks,
Ted
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
- 14
- Missing data for columnThis is a multipart message in MIME format.
Hi,
i got an error below after running copy command . the table structure as
following :
Table "biosadm.custinv_temp
Column | Type |
-------------+---------------+-
yr | integer |
custname | text |
invstatus | text |
custlo | text |
invno | integer |
invdate | date |
amount | numeric(10,2) |
acc | text |
salesperson | text |
ERROR: copy: line 1, Missing data for column "subsidiary"
Does anybody have an idea??? TQ
<br><font size=2 face="Times New Roman">Hi,</font>
<br>
<br><font size=2 face="Times New Roman">i got an error below after running copy command . the table structure as following :</font>
<br>
<br><font size=2 face="Times New Roman"> Table "biosadm.custinv_temp</font>
<br><font size=2 face="Times New Roman"> Column | Type |</font>
<br><font size=2 face="Times New Roman">-------------+---------------+-</font>
<br><font size=2 face="Times New Roman"> yr | integer |</font>
<br><font size=2 face="Times New Roman"> custname | text |</font>
<br><font size=2 face="Times New Roman"> invstatus | text |</font>
<br><font size=2 face="Times New Roman"> custlo | text |</font>
<br><font size=2 face="Times New Roman"> invno | integer |</font>
<br><font size=2 face="Times New Roman"> invdate | date |</font>
<br><font size=2 face="Times New Roman"> amount | numeric(10,2) |</font>
<br><font size=2 face="Times New Roman"> acc | text |</font>
<br><font size=2 face="Times New Roman"> salesperson | text |</font>
<br>
<br><font size=2 face="Times New Roman">ERROR: copy: line 1, Missing data for column "subsidiary"</font>
<br>
<br><font size=2 face="Times New Roman">Does anybody have an idea??? TQ</font>
- 15
- Before update or delete trigger to insert ?Hi,
I need some help...I'm fairly new to triggers and am attempting to
duplicate a trigger in DB2 that already works in SQL Server. The
basics are that I want to insert a row into an audit table using data
currently in a table BEFORE an update is executed. In addition, I need
to make sure the the record being updated is of a certain status so I
need to do a count on third table to ensure this status.
From what I've read, I can't do an insert in a BEFORE UPDATE trigger,
but I don't know where to go from here. I also have to do the same on
a BEFORE DELETE. Any help would be greatly appreciated.
My trigger looks like this:
CREATE TRIGGER AUDIT_UPD_RBRT1
NO CASCADE BEFORE UPDATE ON RB.RT1
REFERENCING OLD AS OAUDIT
FOR EACH ROW MODE DB2SQL
WHEN ( (SELECT COUNT(*) FROM RB.RT_FLNG_ADPTN
WHERE (IDL = OAUDIT.IDL AND
IDS = OAUDIT.IDS AND
STATUS_INDC = 'X')) = 1)
INSERT INTO RB.RB_AUDIT_RT_DATA
VALUES (OAUDIT.IDS,
OAUDIT.IDL,
CURRENT TIMESTAMP,
1,
11,
OAUDIT.EXACT,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
OAUDIT.RATE,
'TESTING');
Thanks,
Cindy
|
|
|
