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.

Multiple tempdb features

10 posts in Product Futures Discussion Last posting was on 2003-05-08 16:09:37.0Z
putnamr Posted on 2003-04-14 16:56:31.0Z
From: putnamr@river.it.gvsu.edu
Date: Mon, 14 Apr 2003 12:56:31 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Multiple tempdb features
Message-ID: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums>
Lines: 11
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1119
Article PK: 95359

Hello,

Can Sybase extend the functionality of the multiple tempdbs feature in ASE
by allowing for specific SQL statements, Triggers, Procedures, and Abstract
Plans to be bound to a specific tempdb database? In addition, to allow for
a "in use" option to be specified that allows a different specified tempdb
database to be chosen if that one is in use. If all tempdbs specified are
in use, ASE will choose the database that is least utilized.

Thanks,
Ryan Putnam


Rob Verschoor Posted on 2003-04-14 22:39:06.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums>
Subject: Re: Multiple tempdb features
Date: Tue, 15 Apr 2003 00:39:06 +0200
Lines: 40
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <#GQn7dtADHA.334@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: a66085.upc-a.chello.nl 62.163.66.85
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1121
Article PK: 95361


<putnamr@river.it.gvsu.edu> wrote in message
news:81283738E33852E7005D107C85256D08.005D10A285256D08@webforums...
> Hello,
>
> Can Sybase extend the functionality of the multiple tempdbs feature
in ASE
> by allowing for specific SQL statements, Triggers, Procedures, and
Abstract
> Plans to be bound to a specific tempdb database? In addition, to
allow for
> a "in use" option to be specified that allows a different specified
tempdb
> database to be chosen if that one is in use. If all tempdbs
specified are
> in use, ASE will choose the database that is least utilized.
>
> Thanks,
> Ryan Putnam

Why would you want this? Have you already ran into limitations of the
multiple tempdb feature?

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

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

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

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


Jason L. Froebe Posted on 2003-04-15 00:16:31.0Z
Message-ID: <3E9B4F5F.3090600@froebe.net>
Date: Mon, 14 Apr 2003 19:16:31 -0500
From: "Jason L. Froebe" <jfroebe@froebe.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: Multiple tempdb features
References: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums> <#GQn7dtADHA.334@forums-1-dub>
In-Reply-To: <#GQn7dtADHA.334@forums-1-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 12-247-248-248.client.attbi.com 12.247.248.248
Lines: 50
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1123
Article PK: 95363


Rob Verschoor wrote:
> <putnamr@river.it.gvsu.edu> wrote in message
> news:81283738E33852E7005D107C85256D08.005D10A285256D08@webforums...
>
>>Hello,
>>
>>Can Sybase extend the functionality of the multiple tempdbs feature
>
> in ASE
>
>>by allowing for specific SQL statements, Triggers, Procedures, and
>
> Abstract
>
>>Plans to be bound to a specific tempdb database? In addition, to
>
> allow for
>
>>a "in use" option to be specified that allows a different specified
>
> tempdb
>
>>database to be chosen if that one is in use. If all tempdbs
>
> specified are
>
>>in use, ASE will choose the database that is least utilized.
>>
>>Thanks,
>>Ryan Putnam
>
>
> Why would you want this? Have you already ran into limitations of the
> multiple tempdb feature?
>
> HTH,
>

Hi Rob,

I think Ryan has a valid request... it would be similar to binding an
object to cache, in this case we would be binding it to a particular
tempdb. I can see the advantages of it... high transaction queries
could be bound to a set of TEMPDBs while reporting queries could be
bound to a different set of TEMPDBs - this would possibly further reduce
the contention on system tables in the TEMPDBs.

I personally would like to see the #tables handled the same way was the
worktables - not being entered into the system tables. A memory
structure specific to the spid could be used to keep track of which
object id belongs to the #table name. Since #table tables are supposed
to be accessed only by a single spid, I do not forsee a major problem
doing it like so. Although, I do know that some people actually access
the #table from other spids (the method to determine the physical name
is on http://www.isug.com/Sybase_FAQ if I remember correctly) as an
alternative to the tempdb..tablename.

thanks

Jason


--
Jason L. Froebe [Team Sybase]
http://jfroebe.bookcrossing.com


putnamr Posted on 2003-04-16 13:17:49.0Z
From: putnamr@river.it.gvsu.edu
Date: Wed, 16 Apr 2003 09:17:49 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Multiple tempdb features
Message-ID: <831B0852B6D70E1B00490B0D85256D0A.000741CA85256D09@webforums>
References: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums> <#GQn7dtADHA.334@forums-1-dub> <3E9B4F5F.3090600@froebe.net>
Lines: 12
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1132
Article PK: 95374

Awesome response Jason. You are correct in your assumption. We have both
longer running lets say (DSS queries) and OLTP activity which occurs at the
same time on the same server by same logins. Our application must be this
way though. That is, the same login. We use role based security here, and
every user has the same login. What differentiates the permissions that
the users get are what roles they activate and what applications that they
have published.

Hope this helps.

Thanks,
Ryan Putnam


A Cornell Posted on 2003-04-15 01:03:19.0Z
From: "A cornell" <agcornell@msn.com>
References: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums> <#GQn7dtADHA.334@forums-1-dub>
Subject: Re: Multiple tempdb features
Date: Mon, 14 Apr 2003 19:03:19 -0600
Lines: 46
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
Message-ID: <u9v4tsuADHA.250@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 0-3pool64-41.nas27.thornton1.co.us.da.qwest.net 67.4.64.41
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1125
Article PK: 95365

Also extend it allow for binding of logins other than sa.
Thanks Allen Cornell

"Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
message news:#GQn7dtADHA.334@forums-1-dub...
> <putnamr@river.it.gvsu.edu> wrote in message
> news:81283738E33852E7005D107C85256D08.005D10A285256D08@webforums...
> > Hello,
> >
> > Can Sybase extend the functionality of the multiple tempdbs feature
> in ASE
> > by allowing for specific SQL statements, Triggers, Procedures, and
> Abstract
> > Plans to be bound to a specific tempdb database? In addition, to
> allow for
> > a "in use" option to be specified that allows a different specified
> tempdb
> > database to be chosen if that one is in use. If all tempdbs
> specified are
> > in use, ASE will choose the database that is least utilized.
> >
> > Thanks,
> > Ryan Putnam
>
> Why would you want this? Have you already ran into limitations of the
> multiple tempdb feature?
>
> HTH,
>
> Rob
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> and Replication Server 12.5
>
> Author of "The Complete Sybase ASE Quick Reference Guide"
> Online orders accepted at http://www.sypron.nl/qr
>
> mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>
>


Aditya Gurajada Posted on 2003-04-24 06:58:35.0Z
Message-ID: <3EA78B1B.BCC473C@Do-Not-sybase-SpAM-.com>
Date: Wed, 23 Apr 2003 23:58:35 -0700
From: Aditya Gurajada <aditya.gurajada@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Multiple tempdb features
References: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums> <#GQn7dtADHA.334@forums-1-dub> <u9v4tsuADHA.250@forums-1-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: vpn-dub-072.sybase.com 10.22.120.72
Lines: 50
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1174
Article PK: 95414


A cornell wrote:

> Also extend it allow for binding of logins other than sa.

This functional extension is being supported in the upcoming release
12.5.1.
See CR 311830.

>
>
> > > Can Sybase extend the functionality of the multiple tempdbs feature
> > in ASE
> > > by allowing for specific SQL statements, Triggers, Procedures, and
> > Abstract
> > > Plans to be bound to a specific tempdb database?

I'm not sure how the interface might be here. Are you expecting that
trigger t1
is bound to (somehow) tempdb1, and t2 is bound to tempdb2? Not sure how
easy that would be to do.

And binding at a SQL level would (perhaps) mean some syntax extension like
... USING TEMPDB <name>.

We thought that such syntax changes to existing applns/queries would be
hard
for users to perform. So the idea of binding an application / login was
adopted.
Is there anything you can do with application level binding to gain what
you want?
(I'm guessing that it would be difficult for all triggers if they come
through isql
as the appln name for isql would be the same.)


> In addition, to
> > allow for
> > > a "in use" option to be specified that allows a different specified
> > tempdb
> > > database to be chosen if that one is in use. If all tempdbs
> > specified are
> > > in use, ASE will choose the database that is least utilized.
> > >

It is not clear what this 'in use' property is. Any task can be bound to a
tempdb
but the task might not be doing anything. So would this constitute that the
tempdb
is 'in use', so that the next task coming along would skip this tempdb?

I feel that this level of selection is probably complicating the whole
tempdb
assignment scheme. We treated multiple tempdb as just a pool of resources
for tasks to share, and you are bound to one for the life of the
task/appln.
It is not clear right away under which scenario your suggestion would be
uesful.

-- Aditya.


Jason L. Froebe [Team Sybase] Posted on 2003-04-24 16:07:51.0Z
Message-ID: <3EA80BD7.1060701@froebe.net>
Date: Thu, 24 Apr 2003 11:07:51 -0500
From: "Jason L. Froebe [Team Sybase]" <jfroebe@froebe.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Aditya Gurajada <aditya.gurajada@sybase.com>
Subject: Re: Multiple tempdb features
References: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums> <#GQn7dtADHA.334@forums-1-dub> <u9v4tsuADHA.250@forums-1-dub> <3EA78B1B.BCC473C@Do-Not-sybase-SpAM-.com>
In-Reply-To: <3EA78B1B.BCC473C@Do-Not-sybase-SpAM-.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 12-247-248-248.client.attbi.com 12.247.248.248
Lines: 56
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1175
Article PK: 95413


Aditya Gurajada wrote:
>
> A cornell wrote:
>
>
>>Also extend it allow for binding of logins other than sa.
>
>
> This functional extension is being supported in the upcoming release
> 12.5.1.
> See CR 311830.
>
>
>>
>>>>Can Sybase extend the functionality of the multiple tempdbs feature
>>>
>>>in ASE
>>>
>>>>by allowing for specific SQL statements, Triggers, Procedures, and
>>>
>>>Abstract
>>>
>>>>Plans to be bound to a specific tempdb database?
>
>
> I'm not sure how the interface might be here. Are you expecting that
> trigger t1
> is bound to (somehow) tempdb1, and t2 is bound to tempdb2? Not sure how
> easy that would be to do.
>
> And binding at a SQL level would (perhaps) mean some syntax extension like
> ... USING TEMPDB <name>.
>
> We thought that such syntax changes to existing applns/queries would be
> hard
> for users to perform. So the idea of binding an application / login was
> adopted.
> Is there anything you can do with application level binding to gain what
> you want?
> (I'm guessing that it would be difficult for all triggers if they come
> through isql
> as the appln name for isql would be the same.)
>
>
>
>>In addition, to
>>
>>>allow for
>>>
>>>>a "in use" option to be specified that allows a different specified
>>>
>>>tempdb
>>>
>>>>database to be chosen if that one is in use. If all tempdbs
>>>
>>>specified are
>>>
>>>>in use, ASE will choose the database that is least utilized.
>>>>
>
>
> It is not clear what this 'in use' property is. Any task can be bound to a
> tempdb
> but the task might not be doing anything. So would this constitute that the
> tempdb
> is 'in use', so that the next task coming along would skip this tempdb?
>
> I feel that this level of selection is probably complicating the whole
> tempdb
> assignment scheme. We treated multiple tempdb as just a pool of resources
> for tasks to share, and you are bound to one for the life of the
> task/appln.
> It is not clear right away under which scenario your suggestion would be
> uesful.
>
> -- Aditya.
>

Thanks Aditya!

Jason

--
Jason L. Froebe [Team Sybase]
http://jfroebe.bookcrossing.com


putnamr Posted on 2003-05-07 15:00:27.0Z
From: putnamr@river.it.gvsu.edu
Date: Wed, 7 May 2003 11:00:27 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Multiple tempdb features
Message-ID: <1EE94C0CF9550B5E0052706685256D1F.0054A42C85256D1E@webforums>
References: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums> <#GQn7dtADHA.334@forums-1-dub> <u9v4tsuADHA.250@forums-1-dub> <3EA78B1B.BCC473C@Do-Not-sybase-SpAM-.com> <3EA80BD7.1060701@froebe.net>
Lines: 20
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1207
Article PK: 95444

Hello,

We use an OLTP application that uses huge amounts of data. We need this
data to make decisions "real time". Some queries are longer running. I
only want these queries to use a different tempdb database. The logins are
the same for everyone. We use ROLES to give permissions to applications
based on application deployment technology. That being, if there login has
access to an application icon they have access to the database via a role.
So, I really need the ability to bind specific SQL, be it a stored
procedure or dynamic to a specific tempdb database.

I want a tempdb database that is in ramdisk and another on disk. I want
longer running queries to use the tempdb on disk because of the inherant
"longer running = more space needed, typically". I want the faster queries
to use the smaller tempdb that is on the raw ramdisk. So, I really need
this ability. The ability to choose the least utilized tempdb is rather
useless in this scenario.

Thanks,
Ryan


Jason L. Froebe [Team Sybase] Posted on 2003-05-08 04:08:40.0Z
Date: Wed, 07 May 2003 23:08:40 -0500
From: "Jason L. Froebe [Team Sybase]" <jfroebe@froebe.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4b) Gecko/20030428 Thunderbird/0.1a
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: Multiple tempdb features
References: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums> <#GQn7dtADHA.334@forums-1-dub> <u9v4tsuADHA.250@forums-1-dub> <3EA78B1B.BCC473C@Do-Not-sybase-SpAM-.com> <3EA80BD7.1060701@froebe.net> <1EE94C0CF9550B5E0052706685256D1F.0054A42C85256D1E@webforums>
In-Reply-To: <1EE94C0CF9550B5E0052706685256D1F.0054A42C85256D1E@webforums>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <elDJyfRFDHA.221@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 12-247-248-248.client.attbi.com 12.247.248.248
Lines: 36
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1213
Article PK: 95452


putnamr@river.it.gvsu.edu wrote:

> Hello,
>
> We use an OLTP application that uses huge amounts of data. We need this
> data to make decisions "real time". Some queries are longer running. I
> only want these queries to use a different tempdb database. The logins are
> the same for everyone. We use ROLES to give permissions to applications
> based on application deployment technology. That being, if there login has
> access to an application icon they have access to the database via a role.
> So, I really need the ability to bind specific SQL, be it a stored
> procedure or dynamic to a specific tempdb database.
>
> I want a tempdb database that is in ramdisk and another on disk. I want
> longer running queries to use the tempdb on disk because of the inherant
> "longer running = more space needed, typically". I want the faster queries
> to use the smaller tempdb that is on the raw ramdisk. So, I really need
> this ability. The ability to choose the least utilized tempdb is rather
> useless in this scenario.
>
> Thanks,
> Ryan

Hi Ryan,

I like your thinking! :-)

If you haven't already done so, you may want to check out the following
which some of the Sybase Engineering people are reading..

http://www.isug.com/sybase-product-future

Jason

--
Jason L. Froebe [Team Sybase]
http://jfroebe.bookcrossing.com


Eric Miner Posted on 2003-05-08 16:09:37.0Z
Message-ID: <3EBA8141.C3245C17@sybase.com>
Date: Thu, 08 May 2003 09:09:37 -0700
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
To: "Jason L. Froebe [Team Sybase]" <jfroebe@froebe.net>
CC: eminer@sybase.com
Subject: Re: Multiple tempdb features
References: <81283738E33852E7005D107C85256D08.005D10A285256D08@webforums> <#GQn7dtADHA.334@forums-1-dub> <u9v4tsuADHA.250@forums-1-dub> <3EA78B1B.BCC473C@Do-Not-sybase-SpAM-.com> <3EA80BD7.1060701@froebe.net> <1EE94C0CF9550B5E0052706685256D1F.0054A42C85256D1E@webforums> <elDJyfRFDHA.221@forums-1-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: vpn-dub-172.sybase.com 10.22.120.172
Lines: 39
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1215
Article PK: 95457

We read this one too.

Eric

"Jason L. Froebe [Team Sybase]" wrote:

> putnamr@river.it.gvsu.edu wrote:
>
> > Hello,
> >
> > We use an OLTP application that uses huge amounts of data. We need this
> > data to make decisions "real time". Some queries are longer running. I
> > only want these queries to use a different tempdb database. The logins are
> > the same for everyone. We use ROLES to give permissions to applications
> > based on application deployment technology. That being, if there login has
> > access to an application icon they have access to the database via a role.
> > So, I really need the ability to bind specific SQL, be it a stored
> > procedure or dynamic to a specific tempdb database.
> >
> > I want a tempdb database that is in ramdisk and another on disk. I want
> > longer running queries to use the tempdb on disk because of the inherant
> > "longer running = more space needed, typically". I want the faster queries
> > to use the smaller tempdb that is on the raw ramdisk. So, I really need
> > this ability. The ability to choose the least utilized tempdb is rather
> > useless in this scenario.
> >
> > Thanks,
> > Ryan
>
> Hi Ryan,
>
> I like your thinking! :-)
>
> If you haven't already done so, you may want to check out the following
> which some of the Sybase Engineering people are reading..
>
> http://www.isug.com/sybase-product-future
>
> Jason
>
> --
> Jason L. Froebe [Team Sybase]
> http://jfroebe.bookcrossing.com