Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Strange behaviour of function datachange() in ASE 15.0.3 ESD#2

3 posts in General Discussion Last posting was on 2009-11-23 17:14:21.0Z
Langer Posted on 2009-11-13 08:57:41.0Z
From: Langer <jens.heinitz@web.de>
Newsgroups: sybase.public.ase.general
Subject: Strange behaviour of function datachange() in ASE 15.0.3 ESD#2
Date: Fri, 13 Nov 2009 00:57:41 -0800 (PST)
Organization: http://groups.google.com
Lines: 21
Message-ID: <c8570f60-39eb-48b8-b3c1-2087fb05d6d1@m38g2000yqd.googlegroups.com>
NNTP-Posting-Host: 193.111.40.193
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1258102661 7281 127.0.0.1 (13 Nov 2009 08:57:41 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 13 Nov 2009 08:57:41 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: m38g2000yqd.googlegroups.com; posting-host=193.111.40.193; posting-account=ZNZJ7woAAADpUpgv1vv3YvJOw-HcIjAi
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; SV1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!m38g2000yqd.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28636
Article PK: 77878

Good morning group!

I tried to understand the function datachange() which is available in
Sybase 15. Using a small test table and 2 two indexes I tried to
understand the value of datachange("mytable", null,null).
Unfortuneatly, I was not able to understand it completely. From the
description in Rob Verschoor's "Sybase Bible", I could read that the
function returns a percentage of the number of rows changed relative
to the number of all rows. So this leads me to the fact that the
maximum value should be 100.0, but after updating all rows in my
table, I got an exact value of 200.0 from datachange(). Using a
production table which gets a lot of Realtime updates, the function
datachange returns 83763.023575.

Where is my misunderstanding?

Any help is welcome!

Best regards

Jens


Sherlock, Kevin [TeamSybase] Posted on 2009-11-13 19:24:54.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <c8570f60-39eb-48b8-b3c1-2087fb05d6d1@m38g2000yqd.googlegroups.com>
Subject: Re: Strange behaviour of function datachange() in ASE 15.0.3 ESD#2
Lines: 50
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4afdb286$1@forums-1-dub>
Date: 13 Nov 2009 11:24:54 -0800
X-Trace: forums-1-dub 1258140294 10.22.241.152 (13 Nov 2009 11:24:54 -0800)
X-Original-Trace: 13 Nov 2009 11:24:54 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28637
Article PK: 77879

datachange() is a function that is really meant to be used at a
"column/partition" level. If you call the function like:

select datachange(<tablename>,null,null)

that really doesn't have any real use OTHER than to say that SOME columns
have had changes. By the way, that syntax will only consider columns which
have statistics (usually part of an index, do you have two indexes on your
table?, maybe one index with two columns?).

The much more useful syntax (assuming no partitioning) is:

select datachange(<tablename>,null,<columnname>)

Now, you will see the percentage for each column that was changed relative
to the number of rows in the table FOR EACH COLUMN. Remember, this function
is mostly used to indicate when you _might_ need to (or might NOT need to)
update column level statistics. Also, datachange statistics are only
tracked for columns which currently have statistics.

The question for you is: What do you want to possibly accomplish by using
this function? If it's some kind of programatic way of determining when
you need to update statistics for a column, then use the column level syntax
instead of the table level one you posted.

"Langer" <jens.heinitz@web.de> wrote in message
news:c8570f60-39eb-48b8-b3c1-2087fb05d6d1@m38g2000yqd.googlegroups.com...
> Good morning group!
>
> I tried to understand the function datachange() which is available in
> Sybase 15. Using a small test table and 2 two indexes I tried to
> understand the value of datachange("mytable", null,null).
> Unfortuneatly, I was not able to understand it completely. From the
> description in Rob Verschoor's "Sybase Bible", I could read that the
> function returns a percentage of the number of rows changed relative
> to the number of all rows. So this leads me to the fact that the
> maximum value should be 100.0, but after updating all rows in my
> table, I got an exact value of 200.0 from datachange(). Using a
> production table which gets a lot of Realtime updates, the function
> datachange returns 83763.023575.
>
> Where is my misunderstanding?
>
> Any help is welcome!
>
> Best regards
>
> Jens


Rob V [ Sybase ] Posted on 2009-11-23 17:14:21.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <c8570f60-39eb-48b8-b3c1-2087fb05d6d1@m38g2000yqd.googlegroups.com>
Subject: Re: Strange behaviour of function datachange() in ASE 15.0.3 ESD#2
Lines: 51
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b0ac2ed@forums-1-dub>
Date: 23 Nov 2009 09:14:21 -0800
X-Trace: forums-1-dub 1258996461 10.22.241.152 (23 Nov 2009 09:14:21 -0800)
X-Original-Trace: 23 Nov 2009 09:14:21 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28681
Article PK: 77923

Actually, the percentage can be > 100, if the number of rows affected is
greater than the number of rows in the table, i.e. when you start off with
10 rows and you insert another 20 for example. So seeing > 100% is
definitely possible, and this is not a bug.
Which reminds me to add a clarification on this point to my ASE quickref....

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"Langer" <jens.heinitz@web.de> wrote in message
news:c8570f60-39eb-48b8-b3c1-2087fb05d6d1@m38g2000yqd.googlegroups.com...
> Good morning group!
>
> I tried to understand the function datachange() which is available in
> Sybase 15. Using a small test table and 2 two indexes I tried to
> understand the value of datachange("mytable", null,null).
> Unfortuneatly, I was not able to understand it completely. From the
> description in Rob Verschoor's "Sybase Bible", I could read that the
> function returns a percentage of the number of rows changed relative
> to the number of all rows. So this leads me to the fact that the
> maximum value should be 100.0, but after updating all rows in my
> table, I got an exact value of 200.0 from datachange(). Using a
> production table which gets a lot of Realtime updates, the function
> datachange returns 83763.023575.
>
> Where is my misunderstanding?
>
> Any help is welcome!
>
> Best regards
>
> Jens