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.

any space free after set to NULL?

3 posts in General Discussion Last posting was on 2010-03-11 21:41:26.0Z
Ray Posted on 2010-03-11 16:34:13.0Z
From: "Ray" <lcm@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: any space free after set to NULL?
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b991b85@forums-1-dub>
Date: 11 Mar 2010 08:34:13 -0800
X-Trace: forums-1-dub 1268325253 10.22.241.152 (11 Mar 2010 08:34:13 -0800)
X-Original-Trace: 11 Mar 2010 08:34:13 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29041
Article PK: 78279

Hi,

We are using Sybase ASE 12.5.3 for our database server, suppose I have a
table with datatypes 'varchar(100)'
or 'text' fields. Initially, these two fields will have data in it, then the
fields will be set to NULL after a period
of time.

I would like to know if the space allocated in these two fields will be
available to the other data used
after the fields being set to NULL?

or it will not be released until the data row is deleted?

Thanks,
Raymond


Rob V [ Sybase ] Posted on 2010-03-11 21:41:26.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: <4b991b85@forums-1-dub>
Subject: Re: any space free after set to NULL?
Lines: 55
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; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b996386@forums-1-dub>
Date: 11 Mar 2010 13:41:26 -0800
X-Trace: forums-1-dub 1268343686 10.22.241.152 (11 Mar 2010 13:41:26 -0800)
X-Original-Trace: 11 Mar 2010 13:41:26 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29043
Article PK: 78280

For 'text' or 'image' columns, when updating the column to NULL, by defualt
all allocated text/image pages for that row are deallocated except the first
one (even though it's empty). If you want that page to be deallocated as
well, you should run:

sp_chgattribute <your-table-name>, 'dealloc_first_txtpg', 1

After this, that empty first text/image page will also be deallocated.
This setting was added in 12.5.1 for tables with large numbers of rows that
have their text/image wiped out but still keep one page per row allocated,
which could be a lot of space.
The reason for not deallocating this page by default is that if text/image
will be written into the column again, much time is saved by keeping an
already-allocated page around.

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
-----------------------------------------------------------------

"Ray" <lcm@hotmail.com> wrote in message news:4b991b85@forums-1-dub...
> Hi,
>
> We are using Sybase ASE 12.5.3 for our database server, suppose I have a
> table with datatypes 'varchar(100)'
> or 'text' fields. Initially, these two fields will have data in it, then
> the fields will be set to NULL after a period
> of time.
>
> I would like to know if the space allocated in these two fields will be
> available to the other data used
> after the fields being set to NULL?
>
> or it will not be released until the data row is deleted?
>
> Thanks,
> Raymond
>


Sherlock, Kevin [TeamSybase] Posted on 2010-03-11 20:37:48.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4b991b85@forums-1-dub>
Subject: Re: any space free after set to NULL?
Lines: 58
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b99549c$1@forums-1-dub>
Date: 11 Mar 2010 12:37:48 -0800
X-Trace: forums-1-dub 1268339868 10.22.241.152 (11 Mar 2010 12:37:48 -0800)
X-Original-Trace: 11 Mar 2010 12:37:48 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29042
Article PK: 78282

I think it's very hard to answer this question (i've already composed and
erased 3 different answers). It's hard to answer, because for one thing,
there are so many variables involved, and two, I don't really know what you
mean by "available to the other data used".

Your best bet is to understand how storage allocation is done and managed by
ASE.

I will tell you though that varchar(n) and text are two very different
datatypes in ASE and their space usage are managed completely differently.
Text values are stored in a separate linked list page chain from the data
row. When a value for that text column on a row is initiated with data,
that page chain is established and at least one page is allocated (more if
the text value is big enough). When updated to NULL, that page chain will
still exist but will have zero bytes of "content" on the page. In fact, you
can not query for NULL text types like you do any other type:

select * from mytable where mytext_col is null

will fail if "mytext_col" is datatype "text". You have to use the
"datalength()" function to find "NULL" rows:

select * from mytable where datalength(mytext_col) = 0

For varchar(n) datatypes, only the value of the column is stored on the row.
If you have a varchar(100) column with 5 bytes stored in the column, then
only 5 bytes will be stored on the row. Null values don't take space on the
row (with the small exception of a byte to indicate an offset of the column
on the row). So, a NULL varchar value will not occupy space on the row, but
updating another varchar(n) column in the row can now use the same "bytes"
as the previous column's value. But, then again this all depends on your
table's locking scheme, settings for reserved_pg_gap, expected_row_size,
max_rows_per_page, fillfactors, etc.

Like I said, it's probably best to study how space allocation and row
layouts are handled to get a more definitive answer for this.

"Ray" <lcm@hotmail.com> wrote in message news:4b991b85@forums-1-dub...
> Hi,
>
> We are using Sybase ASE 12.5.3 for our database server, suppose I have a
> table with datatypes 'varchar(100)'
> or 'text' fields. Initially, these two fields will have data in it, then
> the fields will be set to NULL after a period
> of time.
>
> I would like to know if the space allocated in these two fields will be
> available to the other data used
> after the fields being set to NULL?
>
> or it will not be released until the data row is deleted?
>
> Thanks,
> Raymond
>