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.

Modify the physical order of the columns in a table

16 posts in Product Futures Discussion Last posting was on 2003-09-04 11:42:48.0Z
Leonardo Pasta Posted on 2003-08-29 20:26:49.0Z
From: Leonardo Pasta <lhpasta@performinformatica.com.br>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5b) Gecko/20030813 Thunderbird/0.2a
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Modify the physical order of the columns in a table
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 200.203.179.70
Message-ID: <3f4fb744$1@forums-2-dub>
X-Original-Trace: 29 Aug 2003 13:27:48 -0700, 200.203.179.70
Lines: 12
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Aug 2003 13:25:09 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Aug 2003 13:26:49 -0700
X-Trace: forums-1-dub 1062188809 10.22.108.75 (29 Aug 2003 13:26:49 -0700)
X-Original-Trace: 29 Aug 2003 13:26:49 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1363
Article PK: 95595

Would be nice if we could do it automatically, I can see two reasons:
1 - When I think that a different order would turn the sp_help or
similar easier to understand for our developers (to group related
information together)
2 - some times I need to do it because I had to export/import a table to
another server, and the order of the column has changed. For 1 table
it's easy to workaround (simply create a view), but for a bigger set of
tables this is really boring.

Thanks in advance,
Leonardo Pasta


putnamr Posted on 2003-09-02 20:35:06.0Z
Sender: 5f17.3f54fd4e.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f54fefa.5f62.846930886@sybase.com>
References: <3f4fb744$1@forums-2-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 Sep 2003 13:35:06 -0700
X-Trace: forums-1-dub 1062534906 10.22.241.41 (2 Sep 2003 13:35:06 -0700)
X-Original-Trace: 2 Sep 2003 13:35:06 -0700, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1366
Article PK: 95596

Hello,

I am not sure what you are asking for. Is it the ability to
modify the column orders as they exist in syscolumns? If
so, I believe that you should be able to do this without any
problems by updating syscolumns. The colid and offset are
disparate columns and as such you should be able to modify
the column order yourself by updating syscolumns or writing
a stored procedure, etc, to do this. Is this what you are
looking for? If so, maybe Sybase can provide a procedure
for you to do this easier in the future.

Exactly, what grouping do you want this to be done at? I
assume just the physical that is the column name, datatype,
nullability, defaults, etc and not to the logical purpose of
the column or field of the entity.

Thanks,
Ryan Putnam


Sherlock, Kevin Posted on 2003-09-02 20:53:55.0Z
Message-ID: <3F550415.56A650FA@qwest.com.nospam>
From: "Sherlock, Kevin" <ksherlo@qwest.com.nospam>
Reply-To: ksherlo@qwest.com.nospam
Organization: QWEST DEX
X-Mailer: Mozilla 4.79 (Macintosh; U; PPC)
X-Accept-Language: en,pdf,ko
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 155.70.39.45
X-Original-NNTP-Posting-Host: 155.70.39.45
Date: 2 Sep 2003 13:53:55 -0700
X-Trace: forums-1-dub 1062536035 155.70.39.45 (2 Sep 2003 13:53:55 -0700)
X-Original-Trace: 2 Sep 2003 13:53:55 -0700, 155.70.39.45
Lines: 43
X-Authenticated-User: teamps
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1367
Article PK: 95597

Modifying the syscolumns table as described below is asking for MAJOR
trouble IMHO. Column id's and offsets are used in compiled objects such
as query trees in sysprocedures, etc. No way would I recommend
modifying syscolumns. I must be misunderstanding what Ryan is
suggesting below...

If the original poster wants to be able to bcp out columns in a specfic
order, he/she should use format files for this.

putnamr@river.it.gvsu.edu wrote:
>
> Hello,
>
> I am not sure what you are asking for. Is it the ability to
> modify the column orders as they exist in syscolumns? If
> so, I believe that you should be able to do this without any
> problems by updating syscolumns. The colid and offset are
> disparate columns and as such you should be able to modify
> the column order yourself by updating syscolumns or writing
> a stored procedure, etc, to do this. Is this what you are
> looking for? If so, maybe Sybase can provide a procedure
> for you to do this easier in the future.
>
> Exactly, what grouping do you want this to be done at? I
> assume just the physical that is the column name, datatype,
> nullability, defaults, etc and not to the logical purpose of
> the column or field of the entity.
>
> Thanks,
> Ryan Putnam

--
Kevin Sherlock
Staff Info Systems Analyst
Omaha, NE
Dex Media
http://www.qwestdex.com
[TeamSybase]
http://teamsybase.com/kevin.sherlock
Take a look at CodeXchange:
http://www.codexchange.sybase.com
ISUG Enhancement Requests
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement


putnamr Posted on 2003-09-02 21:11:24.0Z
Sender: 6091.3f5506db.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f55077c.60b3.846930886@sybase.com>
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com><3F550415.56A650FA@qwest.com.nospam>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 Sep 2003 14:11:24 -0700
X-Trace: forums-1-dub 1062537084 10.22.241.41 (2 Sep 2003 14:11:24 -0700)
X-Original-Trace: 2 Sep 2003 14:11:24 -0700, 10.22.241.41
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1368
Article PK: 95600

I agree, but if you were to write such a process you would
have to take that into consideration. Such as, dropping and
recreating all compiled objects after running such a process
as part of a bigger process. However, I was just trying to
get a better understanding of what is being sought after.

Thanks,
Ryan

> Modifying the syscolumns table as described below is
> asking for MAJOR trouble IMHO. Column id's and offsets
> are used in compiled objects such as query trees in
> sysprocedures, etc. No way would I recommend modifying
> syscolumns. I must be misunderstanding what Ryan is
> suggesting below...
>
> If the original poster wants to be able to bcp out columns
> in a specfic order, he/she should use format files for
> this.
>
> putnamr@river.it.gvsu.edu wrote:
> >
> > Hello,
> >
> > I am not sure what you are asking for. Is it the
> > ability to modify the column orders as they exist in
> > syscolumns? If so, I believe that you should be able to
> > do this without any problems by updating syscolumns.
> > The colid and offset are disparate columns and as such
> > you should be able to modify the column order yourself
> > by updating syscolumns or writing a stored procedure,
> > etc, to do this. Is this what you are looking for? If
> > so, maybe Sybase can provide a procedure for you to do
> > this easier in the future.
> > Exactly, what grouping do you want this to be done at?
> > I assume just the physical that is the column name,
> > datatype, nullability, defaults, etc and not to the
> > logical purpose of the column or field of the entity.
> >
> > Thanks,
> > Ryan Putnam
>
> --
> Kevin Sherlock
> Staff Info Systems Analyst
> Omaha, NE
> Dex Media
> http://www.qwestdex.com
> [TeamSybase]
> http://teamsybase.com/kevin.sherlock
> Take a look at CodeXchange:
> http://www.codexchange.sybase.com
> ISUG Enhancement Requests
> http://www.isug.com/cgi-bin/ISUG2/submit_enhancement


Rob Verschoor Posted on 2003-09-02 21:13:20.0Z
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com>
Subject: Re: Modify the physical order of the columns in a table
Lines: 55
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: a111218.upc-a.chello.nl
X-Original-NNTP-Posting-Host: a111218.upc-a.chello.nl
Message-ID: <3f5507f0$1@forums-1-dub>
Date: 2 Sep 2003 14:13:20 -0700
X-Trace: forums-1-dub 1062537200 62.163.111.218 (2 Sep 2003 14:13:20 -0700)
X-Original-Trace: 2 Sep 2003 14:13:20 -0700, a111218.upc-a.chello.nl
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1369
Article PK: 95598

Here's a warning: DON'T mess with the colid/offset columns in
syscolumns: it'll get you into trouble.
In any case, there is a misunderstanding here: colid is only partly
related to the actual order in which the columns are stored. What it
really defines is the order the columns were created in, and therefore
the order in which they appear when you do a 'select *'.
The physical storage order of columns in a row is different: all
fixed-length columns go first, followed by the var-length columns. Bit
columns are a special case. Manually changing syscolumns will not have
any effect on the actual storage order, and may make it impossible to
retrieve your existing data correctly.

If you need a different order fo the columns in a 'select *', either
create a view on top of the table or recreate the table.

HTH,

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

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

Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob@sypron.nl
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

<putnamr@river.it.gvsu.edu> wrote in message
news:3f54fefa.5f62.846930886@sybase.com...
> Hello,
>
> I am not sure what you are asking for. Is it the ability to
> modify the column orders as they exist in syscolumns? If
> so, I believe that you should be able to do this without any
> problems by updating syscolumns. The colid and offset are
> disparate columns and as such you should be able to modify
> the column order yourself by updating syscolumns or writing
> a stored procedure, etc, to do this. Is this what you are
> looking for? If so, maybe Sybase can provide a procedure
> for you to do this easier in the future.
>
> Exactly, what grouping do you want this to be done at? I
> assume just the physical that is the column name, datatype,
> nullability, defaults, etc and not to the logical purpose of
> the column or field of the entity.
>
> Thanks,
> Ryan Putnam


Leonardo Pasta Posted on 2003-09-03 11:51:03.0Z
From: Leonardo Pasta <lhpasta@performinformatica.com.br>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5b) Gecko/20030813 Thunderbird/0.2a
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub>
In-Reply-To: <3f5507f0$1@forums-1-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 200.203.179.70
X-Original-NNTP-Posting-Host: 200.203.179.70
Message-ID: <3f55d5a7@forums-1-dub>
Date: 3 Sep 2003 04:51:03 -0700
X-Trace: forums-1-dub 1062589863 200.203.179.70 (3 Sep 2003 04:51:03 -0700)
X-Original-Trace: 3 Sep 2003 04:51:03 -0700, 200.203.179.70
Lines: 70
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1371
Article PK: 95601

First of all, sorry for my poor english, sometimes I find myself in
trouble trying to "translate" my thoughts to english... :-)
I think tha Rob understood what I mean. Specifically my project use
ErWin for mantaining the database schema, and after 6 months away from
tha DBA tasks, I found that most of the tables in a database we manage
were created with a random column order.
The only problem that I face is that, whenever I need to bcp out from
that database to bcp in another the "automatic way" (i.e. bcp -c or bcp
-n) won't work, because the column order of the tables are diferent.
Of course I coud create views with the order I need, but it can't be
defined as "automatic", specially when we are talking about hundreds of
tables.... :-(
What I'm doing right now is recreating those tables in the same order
we have it in our models. But it is not an easy task also, since there
are HUGE tables involved......
Well, that's the whole picture. I can solve my problem by any of the
ways described above (I'm actually doing it), but if I could do an
"Alter Table" and have Sybase do it for me, it would be terrific.
Of course I would have to find another way to justify my job to my
employer. :-)

Rob Verschoor wrote:
>(....)
> If you need a different order fo the columns in a 'select *', either
> create a view on top of the table or recreate the table.
>
> HTH,
>
> Rob V.
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> and Replication Server 12.5
>
> Author of "Tips, Tricks & Recipes for Sybase ASE" and
> "The Complete Sybase ASE Quick Reference Guide"
> Online orders accepted at http://www.sypron.nl/shop
>
> mailto:rob@sypron.nl
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>
> <putnamr@river.it.gvsu.edu> wrote in message
> news:3f54fefa.5f62.846930886@sybase.com...
>
>>Hello,
>>
>>I am not sure what you are asking for. Is it the ability to
>>modify the column orders as they exist in syscolumns? If
>>so, I believe that you should be able to do this without any
>>problems by updating syscolumns. The colid and offset are
>>disparate columns and as such you should be able to modify
>>the column order yourself by updating syscolumns or writing
>>a stored procedure, etc, to do this. Is this what you are
>>looking for? If so, maybe Sybase can provide a procedure
>>for you to do this easier in the future.
>>
>>Exactly, what grouping do you want this to be done at? I
>>assume just the physical that is the column name, datatype,
>>nullability, defaults, etc and not to the logical purpose of
>>the column or field of the entity.
>>
>>Thanks,
>>Ryan Putnam
>
>


putnamr Posted on 2003-09-03 15:09:52.0Z
Sender: 7076.3f560305.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f560440.70a2.846930886@sybase.com>
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub><3f55d5a7@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 Sep 2003 08:09:52 -0700
X-Trace: forums-1-dub 1062601792 10.22.241.41 (3 Sep 2003 08:09:52 -0700)
X-Original-Trace: 3 Sep 2003 08:09:52 -0700, 10.22.241.41
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1372
Article PK: 95602

Hello,

My question is still:
Exactly, what grouping do you want this to be done at? I
assume just the physical that is the column name, datatype,
nullability, defaults, etc and not to the logical purpose of
the column or field of the entity.

Also, with dynamic sql in stored procedures you should be
able to create a new table using select into or something
(applying whatever column ordering you would want) and
recreate any old dependencies (constraints, indexes, default
bindings, rule bindings, etc...) Drop the old table and
rename the new table to old tables name. Note: this will
force all compiled SQL to go through re-resolution as the
object id for the table has changed.

Thanks,
Ryan Putnam

> First of all, sorry for my poor english, sometimes I
> find myself in trouble trying to "translate" my thoughts
> to english... :-)
> I think tha Rob understood what I mean. Specifically my
> project use ErWin for mantaining the database schema, and
> after 6 months away from tha DBA tasks, I found that most
> of the tables in a database we manage were created with a
> random column order.
> The only problem that I face is that, whenever I need
> to bcp out from that database to bcp in another the
> "automatic way" (i.e. bcp -c or bcp -n) won't work,
> because the column order of the tables are diferent.
> Of course I coud create views with the order I need,
> but it can't be defined as "automatic", specially when we
> are talking about hundreds of tables.... :-(
> What I'm doing right now is recreating those tables in
> the same order we have it in our models. But it is not an
> easy task also, since there are HUGE tables
> involved......
> Well, that's the whole picture. I can solve my problem
> by any of the ways described above (I'm actually doing
> it), but if I could do an "Alter Table" and have Sybase
> do it for me, it would be terrific.
> Of course I would have to find another way to justify
> my job to my employer. :-)
>


Leonardo Pasta Posted on 2003-09-03 17:49:53.0Z
From: Leonardo Pasta <lhpasta@performinformatica.com.br>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5b) Gecko/20030813 Thunderbird/0.2a
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub><3f55d5a7@forums-1-dub> <3f560440.70a2.846930886@sybase.com>
In-Reply-To: <3f560440.70a2.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 200.203.179.70
X-Original-NNTP-Posting-Host: 200.203.179.70
Message-ID: <3f5629c1@forums-1-dub>
Date: 3 Sep 2003 10:49:53 -0700
X-Trace: forums-1-dub 1062611393 200.203.179.70 (3 Sep 2003 10:49:53 -0700)
X-Original-Trace: 3 Sep 2003 10:49:53 -0700, 200.203.179.70
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1374
Article PK: 95604

When I said about different column orders that would make it easier for
a developer, I am talking about the logical purpose of the column.
I.E.: I prefer an sp_help that lists the column in an logical order like
(for an order entry table):
Order_id, item_id, Client_id, quantity, value, discount, etc.
as oposed to:
discount, client_id, quantity, order_id, item_id,value, etc.

putnamr@river.it.gvsu.edu wrote:
> Hello,
>
> My question is still:
> Exactly, what grouping do you want this to be done at? I
> assume just the physical that is the column name, datatype,
> nullability, defaults, etc and not to the logical purpose of
> the column or field of the entity.
>
> Also, with dynamic sql in stored procedures you should be
> able to create a new table using select into or something
> (applying whatever column ordering you would want) and
> recreate any old dependencies (constraints, indexes, default
> bindings, rule bindings, etc...) Drop the old table and
> rename the new table to old tables name. Note: this will
> force all compiled SQL to go through re-resolution as the
> object id for the table has changed.
>
> Thanks,
> Ryan Putnam


putnamr Posted on 2003-09-03 19:56:13.0Z
Sender: 78ec.3f56455b.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f56475d.790b.846930886@sybase.com>
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub><3f55d5a7@forums-1-dub> <3f560440.70a2.846930886@sybase.com><3f5629c1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 Sep 2003 12:56:13 -0700
X-Trace: forums-1-dub 1062618973 10.22.241.41 (3 Sep 2003 12:56:13 -0700)
X-Original-Trace: 3 Sep 2003 12:56:13 -0700, 10.22.241.41
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1376
Article PK: 95606

Hello,

I would not suspect something like this to be high on the
radar. Does not really improve functionality that much.

You could implement the functionality yourself by creating a
new table my_col_map(pk id,pk colid, lcolid) and modify
sp_help to use the new table with the mappings you created.
Sounds like more work that it is worth though. You would
have to keep your map table up to date as the database
changes. That is adding and removing colid as necessary.
But, being that you want logical I don't really see any
easier way except for ordering the create statement when you
create the table.
(120, 1, 100)
(120, 2, 200)
(120, 3, 150)

Thanks,
Ryan Putnam

> When I said about different column orders that would make
> it easier for a developer, I am talking about the logical
> purpose of the column. I.E.: I prefer an sp_help that
> lists the column in an logical order like (for an order
> entry table):
> Order_id, item_id, Client_id, quantity, value, discount
> , etc.
> as oposed to:
> discount, client_id, quantity, order_id, item_id,value,
> etc.
>
>
> putnamr@river.it.gvsu.edu wrote:
> > Hello,
> >
> > My question is still:
> > Exactly, what grouping do you want this to be done at?
> > I assume just the physical that is the column name,
> > datatype, nullability, defaults, etc and not to the
> > logical purpose of the column or field of the entity.
> >
> > Also, with dynamic sql in stored procedures you should
> > be able to create a new table using select into or
> > something (applying whatever column ordering you would
> > want) and recreate any old dependencies (constraints,
> > indexes, default bindings, rule bindings, etc...) Drop
> > the old table and rename the new table to old tables
> > name. Note: this will force all compiled SQL to go
> > through re-resolution as the object id for the table has
> > changed.
> > Thanks,
> > Ryan Putnam
>


Leonardo Pasta Posted on 2003-09-03 20:53:29.0Z
From: Leonardo Pasta <lhpasta@performinformatica.com.br>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5b) Gecko/20030813 Thunderbird/0.2a
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub><3f55d5a7@forums-1-dub> <3f560440.70a2.846930886@sybase.com><3f5629c1@forums-1-dub> <3f56475d.790b.846930886@sybase.com>
In-Reply-To: <3f56475d.790b.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 200.203.179.70
Message-ID: <3f565511@forums-2-dub>
X-Original-Trace: 3 Sep 2003 13:54:41 -0700, 200.203.179.70
Lines: 34
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 3 Sep 2003 13:51:16 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 3 Sep 2003 13:53:29 -0700
X-Trace: forums-1-dub 1062622409 10.22.108.75 (3 Sep 2003 13:53:29 -0700)
X-Original-Trace: 3 Sep 2003 13:53:29 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1377
Article PK: 95607

Hi Ryan,
Agreed, maybe my topic could suggest it, but I don't see this feature
as an important one, but the group is called
"product_futures_discussion" and I think that we can list here any
sugestion we might have. Of course it isn't critical, or even an
important one, but it is a feature and would make my job easier
sometimes. Someone could argue that changing the datatype wasn't
important either, after all, is simply a matter of droping and
recreating the tables, but I like it and use it a lot.
Thanks...
Leonardo Pasta

putnamr@river.it.gvsu.edu wrote:
> Hello,
>
> I would not suspect something like this to be high on the
> radar. Does not really improve functionality that much.
>
> You could implement the functionality yourself by creating a
> new table my_col_map(pk id,pk colid, lcolid) and modify
> sp_help to use the new table with the mappings you created.
> Sounds like more work that it is worth though. You would
> have to keep your map table up to date as the database
> changes. That is adding and removing colid as necessary.
> But, being that you want logical I don't really see any
> easier way except for ordering the create statement when you
> create the table.
> (120, 1, 100)
> (120, 2, 200)
> (120, 3, 150)
>
> Thanks,
> Ryan Putnam


putnamr Posted on 2003-09-03 21:06:55.0Z
Sender: 6cd0.3f565729.1804289383@sybase.com
From: putnamr@river.it.gvsu.edu
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f565839.6cd6.846930886@sybase.com>
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub><3f55d5a7@forums-1-dub> <3f560440.70a2.846930886@sybase.com><3f5629c1@forums-1-dub> <3f56475d.790b.846930886@sybase.com><3f565511@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 3 Sep 2003 14:08:09 -0700, 10.22.241.42
Lines: 50
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 3 Sep 2003 14:04:45 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 3 Sep 2003 14:06:55 -0700
X-Trace: forums-1-dub 1062623215 10.22.108.75 (3 Sep 2003 14:06:55 -0700)
X-Original-Trace: 3 Sep 2003 14:06:55 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1378
Article PK: 95608

Hello,

I almost agree with what you want to do, but I would like it
to be carried to another level. That is add lcolid to
syscolumns and allow for modification of lcolid through
alter table. When table is initially created lcolid = 10000
* colid so that ordering is the same as creation order.
Later you can change the order using alter table for lcolid.
This should also cause all dependent objects to go through
resolution. That is, not only would you change the sp_help
order, but the "select *" order.

Thanks,
Ryan Putnam

> Hi Ryan,
> Agreed, maybe my topic could suggest it, but I don't
> see this feature as an important one, but the group is
> called "product_futures_discussion" and I think that we
> can list here any sugestion we might have. Of course it
> isn't critical, or even an important one, but it is a
> feature and would make my job easier sometimes. Someone
> could argue that changing the datatype wasn't important
> either, after all, is simply a matter of droping and
> recreating the tables, but I like it and use it a lot.
> Thanks...
> Leonardo Pasta
>
> putnamr@river.it.gvsu.edu wrote:
> > Hello,
> >
> > I would not suspect something like this to be high on
> > the radar. Does not really improve functionality that
> > much.
> > You could implement the functionality yourself by
> > creating a new table my_col_map(pk id,pk colid, lcolid)
> > and modify sp_help to use the new table with the
> > mappings you created. Sounds like more work that it is
> > worth though. You would have to keep your map table up
> > to date as the database changes. That is adding and
> > removing colid as necessary. But, being that you want
> > logical I don't really see any easier way except for
> > ordering the create statement when you create the table.
> > (120, 1, 100)
> > (120, 2, 200)
> > (120, 3, 150)
> >
> > Thanks,
> > Ryan Putnam
>


Leonardo Pasta Posted on 2003-09-04 11:40:50.0Z
From: Leonardo Pasta <lhpasta@performinformatica.com.br>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5b) Gecko/20030813 Thunderbird/0.2a
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub><3f55d5a7@forums-1-dub> <3f560440.70a2.846930886@sybase.com><3f5629c1@forums-1-dub> <3f56475d.790b.846930886@sybase.com><3f565511@forums-2-dub> <3f565839.6cd6.846930886@sybase.com>
In-Reply-To: <3f565839.6cd6.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 200.203.179.70
X-Original-NNTP-Posting-Host: 200.203.179.70
Message-ID: <3f5724c2$1@forums-1-dub>
Date: 4 Sep 2003 04:40:50 -0700
X-Trace: forums-1-dub 1062675650 200.203.179.70 (4 Sep 2003 04:40:50 -0700)
X-Original-Trace: 4 Sep 2003 04:40:50 -0700, 200.203.179.70
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1380
Article PK: 95610

This would be fine to me! :-)
Thanks,
Leonardo Pasta

putnamr@river.it.gvsu.edu wrote:
> Hello,
>
> I almost agree with what you want to do, but I would like it
> to be carried to another level. That is add lcolid to
> syscolumns and allow for modification of lcolid through
> alter table. When table is initially created lcolid = 10000
> * colid so that ordering is the same as creation order.
> Later you can change the order using alter table for lcolid.
> This should also cause all dependent objects to go through
> resolution. That is, not only would you change the sp_help
> order, but the "select *" order.
>
> Thanks,
> Ryan Putnam


"Ilya Zvyagin" <ziv{ Posted on 2003-09-03 15:24:24.0Z
Reply-To: "Ilya Zvyagin" <ziv{@}fct{.}ru>
From: "Ilya Zvyagin" <ziv{@}fct{.}ru>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub> <3f55d5a7@forums-1-dub>
Subject: Re: Modify the physical order of the columns in a table
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Comment-To: Leonardo Pasta
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Organization: FCT Ltd
Message-ID: <1062602536.856681@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
NNTP-Posting-Host: gatekeeper.fct.ru
X-Original-NNTP-Posting-Host: gatekeeper.fct.ru
Date: 3 Sep 2003 08:24:24 -0700
X-Trace: forums-1-dub 1062602664 212.113.103.2 (3 Sep 2003 08:24:24 -0700)
X-Original-Trace: 3 Sep 2003 08:24:24 -0700, gatekeeper.fct.ru
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1373
Article PK: 95603

Hello, Leonardo!
You wrote on 3 Sep 2003 04:51:03 -0700:

LP> from that database to bcp in another the "automatic way" (i.e. bcp
LP> -c or bcp -n) won't work, because the column order of the tables
LP> are diferent.

BCP will work if you write a format file.

LP> Of course I coud create views with the order I need, but it can't
LP> be defined as "automatic", specially when we are talking about
LP> hundreds of tables.... :-(

If you have a ErWIN model, you can create BCP format files from it
along with the BCP calling OS script. That what I whould do.

------------------------------------
Ilya Zvyagin
E-mail: ziv[AT]fct[dot]ru
ICQ# 29427861


Leonardo Pasta Posted on 2003-09-03 19:18:24.0Z
From: Leonardo Pasta <lhpasta@performinformatica.com.br>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5b) Gecko/20030813 Thunderbird/0.2a
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub> <3f55d5a7@forums-1-dub> <1062602536.856681@gatekeeper.fct.ru>
In-Reply-To: <1062602536.856681@gatekeeper.fct.ru>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 200.203.179.70
Message-ID: <3f563ecc@forums-2-dub>
X-Original-Trace: 3 Sep 2003 12:19:40 -0700, 200.203.179.70
Lines: 9
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 3 Sep 2003 12:16:15 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 3 Sep 2003 12:18:24 -0700
X-Trace: forums-1-dub 1062616704 10.22.108.75 (3 Sep 2003 12:18:24 -0700)
X-Original-Trace: 3 Sep 2003 12:18:24 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1375
Article PK: 95605


Ilya Zvyagin wrote:
> If you have a ErWIN model, you can create BCP format files from it
> along with the BCP calling OS script. That what I whould do.

Could you send me information about how can I do that? I think it
would be perfect. By the way, we are using ErWin version 3.5.2

Thanks in advance,
Leonardo Pasta


"Ilya Zvyagin" <ziv{ Posted on 2003-09-04 08:45:11.0Z
Reply-To: "Ilya Zvyagin" <ziv{@}fct{.}ru>
From: "Ilya Zvyagin" <ziv{@}fct{.}ru>
Newsgroups: sybase.public.ase.product_futures_discussion
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub> <3f55d5a7@forums-1-dub> <1062602536.856681@gatekeeper.fct.ru> <3f563ecc@forums-2-dub>
Subject: Re: Modify the physical order of the columns in a table
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
X-Comment-To: Leonardo Pasta
Organization: FCT Ltd
Message-ID: <1062664973.624988@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
X-Original-NNTP-Posting-Host: gatekeeper.fct.ru
X-Original-Trace: 4 Sep 2003 01:46:27 -0700, gatekeeper.fct.ru
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Sep 2003 01:42:57 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Sep 2003 01:45:11 -0700
X-Trace: forums-1-dub 1062665111 10.22.108.75 (4 Sep 2003 01:45:11 -0700)
X-Original-Trace: 4 Sep 2003 01:45:11 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1379
Article PK: 95609

Hello, Leonardo!

You wrote on 3 Sep 2003 12:18:24 -0700:

>> If you have a ErWIN model, you can create BCP format files from it
>> along with the BCP calling OS script. That what I whould do.
LP> Could you send me information about how can I do that? I think it
LP> would be perfect. By the way, we are using ErWin version 3.5.2

The aproximate variant of ErWIN script is

%ForEachEntity(){%File(d:\bcp_out.cmd){
bcp %DiagramProp(DBNAME).%TableOwner.%TableName out
%TableOwner.%TableName.dat -c -S%DiagramProp(SRVNAME) -U%DiagramProp(LOGIN)
-P%%BCP_OUT_P%%
}}
%ForEachEntity(){%File(d:\bcp_in.cmd){
bcp %DiagramProp(DBNAME).%TableOwner.%TableName in
%TableOwner.%TableName.dat -c -S%DiagramProp(SRVNAME) -U%DiagramProp(LOGIN)
-P%%BCP_IN_P%%
}}
%Decl(NATT) %Decl(COLNO)
%ForEachEntity(){%=(NATT,0) %ForEachAtt(){%=(NATT,%+(%:NATT,1))} %=(COLNO,0)
%File(d:\%TableOwner.%TableName.fmt){10.0
%:NATT
%ForEachAtt() { %=(COLNO,%+(%:COLNO,1))
%:COLNO SYBCHAR 0 255
%if(%==(%:COLNO,%:NATT)){"\n"}%else{"\t"} %:COLNO %AttFieldName
}}}

This is only as a first sketch.
The script should be modified to the productional variant.

------------------------------------
Ilya Zvyagin
E-mail: ziv[AT]fct[dot]ru
ICQ# 29427861


Leonardo Pasta Posted on 2003-09-04 11:42:48.0Z
From: Leonardo Pasta <lhpasta@performinformatica.com.br>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5b) Gecko/20030813 Thunderbird/0.2a
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Modify the physical order of the columns in a table
References: <3f4fb744$1@forums-2-dub> <3f54fefa.5f62.846930886@sybase.com> <3f5507f0$1@forums-1-dub> <3f55d5a7@forums-1-dub> <1062602536.856681@gatekeeper.fct.ru> <3f563ecc@forums-2-dub> <1062664973.624988@gatekeeper.fct.ru>
In-Reply-To: <1062664973.624988@gatekeeper.fct.ru>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 200.203.179.70
X-Original-NNTP-Posting-Host: 200.203.179.70
Message-ID: <3f572538@forums-1-dub>
Date: 4 Sep 2003 04:42:48 -0700
X-Trace: forums-1-dub 1062675768 200.203.179.70 (4 Sep 2003 04:42:48 -0700)
X-Original-Trace: 4 Sep 2003 04:42:48 -0700, 200.203.179.70
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1381
Article PK: 95613

Thanks Ilya, I will work on it.
Leonardo Pasta

Ilya Zvyagin wrote:

> The aproximate variant of ErWIN script is
>
> %ForEachEntity(){%File(d:\bcp_out.cmd){
> bcp %DiagramProp(DBNAME).%TableOwner.%TableName out
> %TableOwner.%TableName.dat -c -S%DiagramProp(SRVNAME) -U%DiagramProp(LOGIN)
> -P%%BCP_OUT_P%%
> }}
> %ForEachEntity(){%File(d:\bcp_in.cmd){
> bcp %DiagramProp(DBNAME).%TableOwner.%TableName in
> %TableOwner.%TableName.dat -c -S%DiagramProp(SRVNAME) -U%DiagramProp(LOGIN)
> -P%%BCP_IN_P%%
> }}
> %Decl(NATT) %Decl(COLNO)
> %ForEachEntity(){%=(NATT,0) %ForEachAtt(){%=(NATT,%+(%:NATT,1))} %=(COLNO,0)
> %File(d:\%TableOwner.%TableName.fmt){10.0
> %:NATT
> %ForEachAtt() { %=(COLNO,%+(%:COLNO,1))
> %:COLNO SYBCHAR 0 255
> %if(%==(%:COLNO,%:NATT)){"\n"}%else{"\t"} %:COLNO %AttFieldName
> }}}
>
> This is only as a first sketch.
> The script should be modified to the productional variant.
>
> ------------------------------------
> Ilya Zvyagin
> E-mail: ziv[AT]fct[dot]ru
> ICQ# 29427861
>
>