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.

Alter very large table

3 posts in General Discussion Last posting was on 2009-10-23 21:05:17.0Z
lane Posted on 2009-10-23 19:59:52.0Z
From: lane <lane.kendall@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Alter very large table
Date: Fri, 23 Oct 2009 12:59:52 -0700 (PDT)
Organization: http://groups.google.com
Lines: 14
Message-ID: <19000faa-ca13-4021-a3dc-c65269d94853@e34g2000vbm.googlegroups.com>
NNTP-Posting-Host: 171.159.194.10
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1256327992 32392 127.0.0.1 (23 Oct 2009 19:59:52 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 23 Oct 2009 19:59:52 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: e34g2000vbm.googlegroups.com; posting-host=171.159.194.10; posting-account=v9uBoQoAAAAiKEwGhx2yHv_trzdaTTA7
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 1.0.3705; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; MS-RTC S; MS-RTC LM 8; SRC 2.7.1 E1),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!transit4.readnews.com!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!e34g2000vbm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28541
Article PK: 77787

Hello
I need to alter a column in a very large table, over 53 million
records.

I created a stored procedure with a single line of code

alter table line_item modify company char(5)

The sp runs for about an hour as if it is working but no changes are
made to the table.

Thanks
Lane Kendall


Rob V [ Sybase ] Posted on 2009-10-23 20:40:17.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: <19000faa-ca13-4021-a3dc-c65269d94853@e34g2000vbm.googlegroups.com>
Subject: Re: Alter very large table
Lines: 50
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: <4ae214b1$1@forums-1-dub>
Date: 23 Oct 2009 13:40:17 -0700
X-Trace: forums-1-dub 1256330417 10.22.241.152 (23 Oct 2009 13:40:17 -0700)
X-Original-Trace: 23 Oct 2009 13:40:17 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28542
Article PK: 77783

Such operations can indeed take a long time.
I'm not sure what your question is, but if you're looking for confirmation
that something is indeed happening under the covers, then you can look at
sysprocesses.physical_io for that session: you should see it count up.
Alternatively, with the MDA tables enabled, check monSysStatement for that
session, and look for changes in LogicalReads and PhysicalReads. If you see
these columns change, then this prooves the process is proceeding. If you
don't see this at all, then check if the session is being blocked by another
session.
Your next question may be how you can predict how long the operation will
take. The answer to that is simple: you can't.

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

"lane" <lane.kendall@gmail.com> wrote in message
news:19000faa-ca13-4021-a3dc-c65269d94853@e34g2000vbm.googlegroups.com...
> Hello
> I need to alter a column in a very large table, over 53 million
> records.
>
> I created a stored procedure with a single line of code
>
> alter table line_item modify company char(5)
>
> The sp runs for about an hour as if it is working but no changes are
> made to the table.
>
> Thanks
> Lane Kendall
>


Bret Halford [Sybase] Posted on 2009-10-23 21:05:17.0Z
From: "Bret Halford [Sybase]" <bret@sybase.com>
Organization: Sybase, Inc.
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Alter very large table
References: <19000faa-ca13-4021-a3dc-c65269d94853@e34g2000vbm.googlegroups.com>
In-Reply-To: <19000faa-ca13-4021-a3dc-c65269d94853@e34g2000vbm.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae21a8d$1@forums-1-dub>
Date: 23 Oct 2009 14:05:17 -0700
X-Trace: forums-1-dub 1256331917 10.22.241.152 (23 Oct 2009 14:05:17 -0700)
X-Original-Trace: 23 Oct 2009 14:05:17 -0700, vip152.sybase.com
Lines: 34
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28543
Article PK: 77784


lane wrote:
> Hello
> I need to alter a column in a very large table, over 53 million
> records.
>
> I created a stored procedure with a single line of code
>
> alter table line_item modify company char(5)
>
> The sp runs for about an hour as if it is working but no changes are
> made to the table.
>
> Thanks
> Lane Kendall
>

Hi Lane,

Are you saying that the procedure completes after running
for an hour, but doesn't change the table?

What is the @@version of the server?
What is the definition of the table now (and in particular,
what is the original datatype of the column you are modifying?)

Can you demonstrate the issue on an empty table with the
same layout, or if not on a table with just a few rows
in it?

Are you sure the procedure didn't hit an 1105 error
and rollback?

Cheers,
-bret