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.

proxy table

7 posts in General Discussion Last posting was on 2012-04-18 17:57:12.0Z
lovedif Posted on 2012-04-17 10:15:59.0Z
Sender: 2c95.4f8d416b.1804289383@sybase.com
From: lovedif
Newsgroups: sybase.public.ase.general
Subject: proxy table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f8d42df.2cc9.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 Apr 2012 03:15:59 -0700
X-Trace: forums-1-dub 1334657759 172.20.134.41 (17 Apr 2012 03:15:59 -0700)
X-Original-Trace: 17 Apr 2012 03:15:59 -0700, 172.20.134.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31031
Article PK: 73921

I have a table that has a text field, it has not been used
much before. Bur since we are using it more now with large
texts the database fills rapidly. Can I do a copy of this
table and put in a new database an connect them with a proxy
table. I like to keep the primary database small since is't
transaction intensive

BestRgds/J


Rob V Posted on 2012-04-17 20:51:21.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: proxy table
References: <4f8d42df.2cc9.1681692777@sybase.com>
In-Reply-To: <4f8d42df.2cc9.1681692777@sybase.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: <4f8dd7c9$1@forums-1-dub>
Date: 17 Apr 2012 13:51:21 -0700
X-Trace: forums-1-dub 1334695881 10.22.241.152 (17 Apr 2012 13:51:21 -0700)
X-Original-Trace: 17 Apr 2012 13:51:21 -0700, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31034
Article PK: 73924


On 17-Apr-2012 12:15, lovedif wrote:
> I have a table that has a text field, it has not been used
> much before. Bur since we are using it more now with large
> texts the database fills rapidly. Can I do a copy of this
> table and put in a new database an connect them with a proxy
> table. I like to keep the primary database small since is't
> transaction intensive
>
> BestRgds/J

Yes, you can do insert-select or select-into just as you would with any
other datatypes.

--
HTH,

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

Certified Professional DBA for Sybase ASE, IQ, Replication Server

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

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


lovedif Posted on 2012-04-18 08:03:56.0Z
Sender: 3103.4f8d5a32.1804289383@sybase.com
From: lovedif
Newsgroups: sybase.public.ase.general
Subject: Re: proxy table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f8e756c.5828.1681692777@sybase.com>
References: <4f8dd7c9$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Apr 2012 01:03:56 -0700
X-Trace: forums-1-dub 1334736236 172.20.134.41 (18 Apr 2012 01:03:56 -0700)
X-Original-Trace: 18 Apr 2012 01:03:56 -0700, 172.20.134.41
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31037
Article PK: 73926


> On 17-Apr-2012 12:15, lovedif wrote:
> > I have a table that has a text field, it has not been
> > used much before. Bur since we are using it more now
> > with large texts the database fills rapidly. Can I do a
> > copy of this table and put in a new database an connect
> > them with a proxy table. I like to keep the primary
> > database small since is't transaction intensive
> >
> > BestRgds/J
>
> Yes, you can do insert-select or select-into just as you
> would with any other datatypes.
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------

I need to ask again, t1 in primary database has 20 columns
one text, this is used always now and expand fast. The
question is can I do t1 in a new database with same columns
an link them together and fill data in the new database
without changing the insert from the code(like insert in t1
primary db but data end up in t1 i new db)


Rob V Posted on 2012-04-18 09:47:14.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: proxy table
References: <4f8dd7c9$1@forums-1-dub> <4f8e756c.5828.1681692777@sybase.com>
In-Reply-To: <4f8e756c.5828.1681692777@sybase.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: <4f8e8da2@forums-1-dub>
Date: 18 Apr 2012 02:47:14 -0700
X-Trace: forums-1-dub 1334742434 10.22.241.152 (18 Apr 2012 02:47:14 -0700)
X-Original-Trace: 18 Apr 2012 02:47:14 -0700, vip152.sybase.com
Lines: 68
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31038
Article PK: 73927


On 18-Apr-2012 10:03, lovedif wrote:
>> On 17-Apr-2012 12:15, lovedif wrote:
>>> I have a table that has a text field, it has not been
>>> used much before. Bur since we are using it more now
>>> with large texts the database fills rapidly. Can I do a
>>> copy of this table and put in a new database an connect
>>> them with a proxy table. I like to keep the primary
>>> database small since is't transaction intensive
>>>
>>> BestRgds/J
>>
>> Yes, you can do insert-select or select-into just as you
>> would with any other datatypes.
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>
> I need to ask again, t1 in primary database has 20 columns
> one text, this is used always now and expand fast. The
> question is can I do t1 in a new database with same columns
> an link them together and fill data in the new database
> without changing the insert from the code(like insert in t1
> primary db but data end up in t1 i new db)

The issue here is about "link them together". You can normally do that
with a trigger, but that cannot be used in combination with text/image
columns. You can do this with a view though (either create a view
pointing to the table, or with a union view pointing to multiple tables
with the same schema; not e that a union view is not updatable).

--
HTH,

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

Certified Professional DBA for Sybase ASE, IQ, Replication Server

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

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


lovedif Posted on 2012-04-18 10:53:00.0Z
Sender: 5cf4.4f8e993e.1804289383@sybase.com
From: lovedif
Newsgroups: sybase.public.ase.general
Subject: Re: proxy table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f8e9d0c.5d63.1681692777@sybase.com>
References: <4f8e8da2@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Apr 2012 03:53:00 -0700
X-Trace: forums-1-dub 1334746380 172.20.134.41 (18 Apr 2012 03:53:00 -0700)
X-Original-Trace: 18 Apr 2012 03:53:00 -0700, 172.20.134.41
Lines: 78
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31039
Article PK: 73928


> On 18-Apr-2012 10:03, lovedif wrote:
> >> On 17-Apr-2012 12:15, lovedif wrote:
> >>> I have a table that has a text field, it has not been
> >>> used much before. Bur since we are using it more now
> >>> with large texts the database fills rapidly. Can I do
> a >>> copy of this table and put in a new database an
> connect >>> them with a proxy table. I like to keep the
> primary >>> database small since is't transaction
> intensive >>>
> >>> BestRgds/J
> >>
> >> Yes, you can do insert-select or select-into just as
> you >> would with any other datatypes.
> >>
> >> --
> >> HTH,
> >>
> >> Rob V.
> >>
> ----------------------------------------------------------
> >> ------- Rob Verschoor >>
> >> Certified Professional DBA for Sybase ASE, IQ,
> Replication >> Server
> >>
> >> Author of Sybase books (order online at
> >> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> >> ASE" "The Complete Sybase IQ Quick Reference Guide"
> (new!) >> "The Complete Sybase ASE Quick Reference Guide"
> >> "The Complete Sybase Replication Server Quick Reference
> >> Guide"
> >>
> >> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> >> @rob_verschoor Sypron B.V., The Netherlands | Chamber
> of >> Commerce 27138666
> >>
> ----------------------------------------------------------
> >> ------- >
> > I need to ask again, t1 in primary database has 20
> > columns one text, this is used always now and expand
> > fast. The question is can I do t1 in a new database with
> > same columns an link them together and fill data in the
> > new database without changing the insert from the
> > code(like insert in t1 primary db but data end up in t1
> i new db)
>
> The issue here is about "link them together". You can
> normally do that with a trigger, but that cannot be used
> in combination with text/image columns. You can do this
> with a view though (either create a view pointing to the
> table, or with a union view pointing to multiple tables
> with the same schema; not e that a union view is not
> updatable).
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------

thanks for your answer, i need to solve this some other way
then /J


jobless Posted on 2012-04-18 17:57:12.0Z
Sender: 6afd.4f8ef839.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: proxy table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f8f0078.6c47.1681692777@sybase.com>
References: <4f8e8da2@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Apr 2012 10:57:12 -0700
X-Trace: forums-1-dub 1334771832 172.20.134.41 (18 Apr 2012 10:57:12 -0700)
X-Original-Trace: 18 Apr 2012 10:57:12 -0700, 172.20.134.41
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31043
Article PK: 73931


> > I need to ask again, t1 in primary database has 20
> > columns one text, this is used always now and expand
> > fast. The question is can I do t1 in a new database with
> > same columns an link them together and fill data in the
> > new database without changing the insert from the
> > code(like insert in t1 primary db but data end up in t1
> i new db)
>
> The issue here is about "link them together". You can
> normally do that with a trigger, but that cannot be used
> in combination with text/image columns. You can do this
> with a view though (either create a view pointing to the
> table, or with a union view pointing to multiple tables
> with the same schema; not e that a union view is not
> updatable).
>

you could extend the view idea and see if
'instead-of-triggers' on view will work in your case!:

- create new db 'myExpandingDB' on same server
- create t1_touch table in 'myExpandingDB' with primary key
same as t1 table and the text column
- In your current DB, Rename your 20col table from t1 to
t1_donottouch
- In your current DB, create view t1 with select:
select <all column from t1_donottouch
except text column>, <text col from t1_touch table in
myExpandingDB db>
from t1_donottouch a, mydbexpanding..t1_touch
where
PK Col Join
- Create instead of trigger for insert/update/delete on view
t1
e.g.
create trigger tt1i on t1
instead of insert as
insert into t1_donottouch( <all col except text>) select
<all col except text> from inserted
insert into mydbexpanding..t1_touch select <pk col>, <text
col> from inserted
--- end---
code for update and delete
truncate issues!

so you will be adding the text row in another db and leave
the size of present db as it is.

You could also leave this as is.. and see if you can
implement a daily/weekly Archive and Purge processes in
place to keep your prod db size in check.


peta62 Posted on 2012-04-18 11:40:20.0Z
From: peta62 <no@mail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: proxy table
References: <4f8d42df.2cc9.1681692777@sybase.com>
In-Reply-To: <4f8d42df.2cc9.1681692777@sybase.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: <4f8ea824@forums-1-dub>
Date: 18 Apr 2012 04:40:20 -0700
X-Trace: forums-1-dub 1334749220 10.22.241.152 (18 Apr 2012 04:40:20 -0700)
X-Original-Trace: 18 Apr 2012 04:40:20 -0700, vip152.sybase.com
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31040
Article PK: 73929

Hello,
why don't you create segment for it restricting its size without
affecting other data in the database ?
Best regards,
Peter

On 4/17/2012 12:15 PM, lovedif wrote:
> I have a table that has a text field, it has not been used
> much before. Bur since we are using it more now with large
> texts the database fills rapidly. Can I do a copy of this
> table and put in a new database an connect them with a proxy
> table. I like to keep the primary database small since is't
> transaction intensive
>
> BestRgds/J