[IQUG] resize columns of a very big table

Nguyen Anh Vu vuna1981 at gmail.com
Wed Aug 8 01:16:39 MST 2018


Dear Mark,
Thanks for your reply. " In your case, you are shrinking the type from a
numeric 22,2 to 18,2.  Why?" ==> i want to save the space.
As documented here:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/X315932.htm:
if my precision is from 10 to 18, IQ need 8 bytes per cell, if my precision
is 22, IQ need 18 bytes. My table has 236 columns in decimal (22,2) format,
so if i can resize to (18,2) will saving so much space.

I  appriciate all your responds,

Thanks you all so much,

Best regards,

On Tue, Aug 7, 2018 at 10:12 PM Mumy, Mark <mark.mumy at sap.com> wrote:

> IQ does not natively support changing datatypes.  As Ron mentions the
> easiest way to do this is to add a new column with the new definition, then
> run an update that populates the new column with data from the old.  You
> then drop the old column and rename the new one to the proper name.
>
>
>
> The one thing that I like to bring up is what the advantage is, or not.
> In your case, you are shrinking the type from a numeric 22,2 to 18,2.
> Why?  The old type will store any data that you want with the new type.
> Yes, there could be overhead as it is a bigger type.  Then again, depending
> on cardinality, you may not save anything.  If this column is able to use
> an n-bit, then this process won’t reduce the cardinality and thus you won’t
> save space.   If it is a flat FP, then there would certainly be a space
> savings.
>
>
>
> Mark
>
>
>
> *Mark Mumy*
>
> *Strategic Technology Incubation Group*
>
> *SAP Global Center of Excellence *|  *SAP*
>
> M +1 347-820-2136 | E mark.mumy at sap.com
>
> My Blogs: https://blogs.sap.com/author/markmumy/
>
>
>
> https://sap.na.pgiconnect.com/I825063
>
> Conference tel: 18663127353,,8035340905#
>
>
>
> *From: *"iqug-bounces at iqug.org" <iqug-bounces at iqug.org> on behalf of Ron
> Watkins <rwatkins at dssolutions.com>
> *Date: *Monday, August 6, 2018 at 9:16 AM
> *To: *'Nguyen Anh Vu' <vuna1981 at gmail.com>, "iqug at iqug.org" <iqug at iqug.org
> >
> *Subject: *Re: [IQUG] resize columns of a very big table
>
>
>
> You should check the size of the column FP and any advanced indexes on
> that column to see if a “copy” of that column can be made.
>
> It’s possible to do what you want by simply adding a “new” column to the
> end of the table and running one or more update statements to copy the data
> from the old column to the new column.
>
> You would then drop the old column.
>
> I would suggest dropping any advanced indexes which exist on that old
> column first to give you extra space if needed.
>
>
>
>
>
> *From:* iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] *On Behalf
> Of *Nguyen Anh Vu
> *Sent:* Sunday, August 05, 2018 7:38 PM
> *To:* iqug at iqug.org
> *Subject:* [IQUG] resize columns of a very big table
>
>
>
> Dear experts,
>
>
>
> We are using SAP sybase IQ 16 for DW, we have a 10 Terabytes table with
> some columns in decimal (22,2) format, now we need to resize these columns
> to decimal (18,2). We have about 3T free in dbspaces.
>
>
>
> Pls suggest me the best way to do this,
>
>
>
> Thanks and best regards,
>
>
>
> Vu Nguyen,
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20180808/32a3967f/attachment.html>


More information about the IQUG mailing list