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.

TempDB block using #temp tables...

9 posts in General Discussion Last posting was on 2010-07-27 23:58:32.0Z
"Manuel Espinoza" <jmespinoza_no_spam Posted on 2010-07-23 21:41:07.0Z
From: "Manuel Espinoza" <jmespinoza_no_spam@seicom_dot_com_dot_mx>
Newsgroups: sybase.public.ase.general
Subject: TempDB block using #temp tables...
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c4a0c73@forums-1-dub>
Date: 23 Jul 2010 14:41:07 -0700
X-Trace: forums-1-dub 1279921267 10.22.241.152 (23 Jul 2010 14:41:07 -0700)
X-Original-Trace: 23 Jul 2010 14:41:07 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29391
Article PK: 78621

Hi all!


We have many(150+) SPs for reporting that uses temp tables. However, we just
found that when creating the temp tables, there's blocking on the TempDB(is
that called contention?). We found that by creating empty physical tables
instead would increase the speed and avoid this blocking problem. Prior to
that, there were another 14 tempdb added to reduce the chances to block, but
no luck...

But, I wonder if is there any other solution for this? I just don't think
this would be the better solution(create one empty table for each SP!) .
Any config that must be set?


Thanks in advance!


ASE 15.0.1
Win2k3 Server


"Mark A. Parsons" <iron_horse Posted on 2010-07-23 22:49:22.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: TempDB block using #temp tables...
References: <4c4a0c73@forums-1-dub>
In-Reply-To: <4c4a0c73@forums-1-dub>
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: <4c4a1c72$1@forums-1-dub>
Date: 23 Jul 2010 15:49:22 -0700
X-Trace: forums-1-dub 1279925362 10.22.241.152 (23 Jul 2010 15:49:22 -0700)
X-Original-Trace: 23 Jul 2010 15:49:22 -0700, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29393
Article PK: 78629

blocking on tempdb == contention? yes, that would be one form of contention

While it's possible that you could be running into a bug with ASE (especially with 15.0.1!!), we really need more
details ...

Could you verify when you say 'uses temp tables' that you're referring to tables that begin with '#'?

Are any of your tempdb's configured with 'allow ddl in tran' enabled? (sp_helpdb)

How do your sp's create the temp tables ... 'select into'? 'create table' + 'insert/select'?

When you get a blocking situation, on what table(s) does the blocking occur? If you're not sure, post back here with
the output from running the following commands during a period of blocking:

- sp_lock

- select * from master..syslogshold

- select * from master..systransactions

Could you also post the exact ASE version? (select @@version)

Have you tried opening a case with Sybase Tech Support?

---------------------

I highly recommend you consider upgrading to the latest ESD for ASE 15.0.3.

ASE 15.0 and 15.0.1 were so buggy that they shouldn't have been released to the general public.

Early releases of 15.0.2 were a little better, but still quite problem-prone.

Later ESDs of 15.0.2 were pretty decent, and 15.0.3 has been fairly stable (compared to earlier 15.x versions).

Manuel Espinoza wrote:
> Hi all!
>
>
> We have many(150+) SPs for reporting that uses temp tables. However, we just
> found that when creating the temp tables, there's blocking on the TempDB(is
> that called contention?). We found that by creating empty physical tables
> instead would increase the speed and avoid this blocking problem. Prior to
> that, there were another 14 tempdb added to reduce the chances to block, but
> no luck...
>
> But, I wonder if is there any other solution for this? I just don't think
> this would be the better solution(create one empty table for each SP!) .
> Any config that must be set?
>
>
> Thanks in advance!
>
>
> ASE 15.0.1
> Win2k3 Server
>
>


"Mark A. Parsons" <iron_horse Posted on 2010-07-23 22:58:28.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: TempDB block using #temp tables...
References: <4c4a0c73@forums-1-dub> <4c4a1c72$1@forums-1-dub>
In-Reply-To: <4c4a1c72$1@forums-1-dub>
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: <4c4a1e94@forums-1-dub>
Date: 23 Jul 2010 15:58:28 -0700
X-Trace: forums-1-dub 1279925908 10.22.241.152 (23 Jul 2010 15:58:28 -0700)
X-Original-Trace: 23 Jul 2010 15:58:28 -0700, vip152.sybase.com
Lines: 71
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29395
Article PK: 78624

Also ...

How long does a typical period of blocking last for a given set of blocking locks?

Mark A. Parsons wrote:
> blocking on tempdb == contention? yes, that would be one form of
> contention
>
> While it's possible that you could be running into a bug with ASE
> (especially with 15.0.1!!), we really need more details ...
>
> Could you verify when you say 'uses temp tables' that you're referring
> to tables that begin with '#'?
>
> Are any of your tempdb's configured with 'allow ddl in tran' enabled?
> (sp_helpdb)
>
> How do your sp's create the temp tables ... 'select into'? 'create
> table' + 'insert/select'?
>
> When you get a blocking situation, on what table(s) does the blocking
> occur? If you're not sure, post back here with the output from running
> the following commands during a period of blocking:
>
> - sp_lock
>
> - select * from master..syslogshold
>
> - select * from master..systransactions
>
> Could you also post the exact ASE version? (select @@version)
>
> Have you tried opening a case with Sybase Tech Support?
>
> ---------------------
>
> I highly recommend you consider upgrading to the latest ESD for ASE 15.0.3.
>
> ASE 15.0 and 15.0.1 were so buggy that they shouldn't have been released
> to the general public.
>
> Early releases of 15.0.2 were a little better, but still quite
> problem-prone.
>
> Later ESDs of 15.0.2 were pretty decent, and 15.0.3 has been fairly
> stable (compared to earlier 15.x versions).
>
>
>
> Manuel Espinoza wrote:
>> Hi all!
>>
>>
>> We have many(150+) SPs for reporting that uses temp tables. However,
>> we just found that when creating the temp tables, there's blocking on
>> the TempDB(is that called contention?). We found that by creating
>> empty physical tables instead would increase the speed and avoid this
>> blocking problem. Prior to that, there were another 14 tempdb added to
>> reduce the chances to block, but no luck...
>>
>> But, I wonder if is there any other solution for this? I just don't
>> think this would be the better solution(create one empty table for
>> each SP!) . Any config that must be set?
>>
>>
>> Thanks in advance!
>>
>>
>> ASE 15.0.1
>> Win2k3 Server
>>


Rob V [ Sybase ] Posted on 2010-07-23 23:56:17.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.7) Gecko/20100713 Thunderbird/3.1.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: TempDB block using #temp tables...
References: <4c4a0c73@forums-1-dub> <4c4a1c72$1@forums-1-dub>
In-Reply-To: <4c4a1c72$1@forums-1-dub>
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: <4c4a2c21@forums-1-dub>
Date: 23 Jul 2010 16:56:17 -0700
X-Trace: forums-1-dub 1279929377 10.22.241.152 (23 Jul 2010 16:56:17 -0700)
X-Original-Trace: 23 Jul 2010 16:56:17 -0700, vip152.sybase.com
Lines: 128
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29396
Article PK: 78625

And most importantly, upgrading to 15.0.2 or later would give you the
new row-level locking behaviour on the system tables. The problem should
completely disappear by going to 15.0.2+.

Rob V.

On 24-Jul-10 00:49, Mark A. Parsons wrote:
> blocking on tempdb == contention? yes, that would be one form of
> contention
>
> While it's possible that you could be running into a bug with ASE
> (especially with 15.0.1!!), we really need more details ...
>
> Could you verify when you say 'uses temp tables' that you're referring
> to tables that begin with '#'?
>
> Are any of your tempdb's configured with 'allow ddl in tran' enabled?
> (sp_helpdb)
>
> How do your sp's create the temp tables ... 'select into'? 'create
> table' + 'insert/select'?
>
> When you get a blocking situation, on what table(s) does the blocking
> occur? If you're not sure, post back here with the output from
> running the following commands during a period of blocking:
>
> - sp_lock
>
> - select * from master..syslogshold
>
> - select * from master..systransactions
>
> Could you also post the exact ASE version? (select @@version)
>
> Have you tried opening a case with Sybase Tech Support?
>
> ---------------------
>
> I highly recommend you consider upgrading to the latest ESD for ASE
> 15.0.3.
>
> ASE 15.0 and 15.0.1 were so buggy that they shouldn't have been
> released to the general public.
>
> Early releases of 15.0.2 were a little better, but still quite
> problem-prone.
>
> Later ESDs of 15.0.2 were pretty decent, and 15.0.3 has been fairly
> stable (compared to earlier 15.x versions).
>
>
>
> Manuel Espinoza wrote:
>> Hi all!
>>
>>
>> We have many(150+) SPs for reporting that uses temp tables. However,
>> we just found that when creating the temp tables, there's blocking
>> on the TempDB(is that called contention?). We found that by creating
>> empty physical tables instead would increase the speed and avoid this
>> blocking problem. Prior to that, there were another 14 tempdb added
>> to reduce the chances to block, but no luck...
>>
>> But, I wonder if is there any other solution for this? I just don't
>> think this would be the better solution(create one empty table for
>> each SP!) . Any config that must be set?
>>
>>
>> Thanks in advance!
>>
>>
>> ASE 15.0.1
>> Win2k3 Server
>>

--
Dear Sybase user,

I am currently unable to respond to the specific question(s) which you have sent me. Due to the ever increasing number of questions I am receiving, I currently simply don't have the time to answer detailed individual questions.
Please post your question to one of the ASE-related newsgroups so that the ASE community can participate/benefit.
These newsgoups are comp.databases.sybase (Usenet), as well as various newsgroups on Sybase's own news server (forums.sybase.com).
Also, please provide sufficient technical details (error messages, actual queries, query plans, etc.) about your problem.
Try the Sybase FAQ at http://www.isug.com/Sybase_FAQ/, which contains answers to many questions about ASE.

Kind regards,

Rob Verschoor

If you don't want to use X Windows, you could try "sybinit4ever", a free tool which uses only an ASCII interface to create a new ASE server. It can be downloaded from http://www.sypron.nl/si4evr.html

See the
ASE reference manual / System Administration Guide / Transact-SQL user's guide
This / These books can be viewed or downloaded as PDF files from the Sybase website http://www.sybase.com/support/manuals/ . For more details how to get there, see http://www.sypron.nl/sybbooks.html .

For more information on the background of this problem, as well as a solution, see http://www.sypron.nl/idgaps.html .

... syntax and description are in the ASE Quick Reference Supplement, which you can download from http://www.sypron.nl/ase_qref.html (for a better version, see my book -- www.sypron.nl/qr).


To use dynamic SQL, you need at least ASE version 12.0, which has the "execute immediate" feature for this. In earlier versions of ASE you can simulate some types of dynamic SQL though; for more information, see http://www.sypron.nl/dynsql.html and http://www.sypron.nl/dynsqlcis.html .

For more information about Sybase Certification Exams, see http://www.sypron.nl/certtips.html .

The ASE Performance and Tuning Guide contains a lot fo info about this topic. You can download this manual from http://sybooks.sybase.com/as.html .

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


John Flynn Posted on 2010-07-26 20:06:00.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
References: <4c4a0c73@forums-1-dub> <4c4a1c72$1@forums-1-dub> <4c4a2c21@forums-1-dub>
Subject: Re: TempDB block using #temp tables...
Lines: 14
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c4deaa8$1@forums-1-dub>
Date: 26 Jul 2010 13:06:00 -0700
X-Trace: forums-1-dub 1280174760 10.22.241.152 (26 Jul 2010 13:06:00 -0700)
X-Original-Trace: 26 Jul 2010 13:06:00 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29402
Article PK: 78633


Rob V [ Sybase ] wrote:
> And most importantly, upgrading to 15.0.2 or later would give you the
> new row-level locking behaviour on the system tables. The problem
> should completely disappear by going to 15.0.2+.

I can attest to that. My app relied very heavily on temporary tables in
stored procedures, and in a high-data-volume environment it suffered
tremendously due to lock contention during creation of the temp tables. But
then a couple years ago I upgraded ASE to whatever version fixed that, and
my performance problem instantly went away.

- John.


"Manuel Espinoza" <jmespinoza_no_spam Posted on 2010-07-24 18:22:36.0Z
From: "Manuel Espinoza" <jmespinoza_no_spam@seicom_dot_com_dot_mx>
Newsgroups: sybase.public.ase.general
References: <4c4a0c73@forums-1-dub> <4c4a1c72$1@forums-1-dub>
Subject: Re: TempDB block using #temp tables...
Lines: 1149
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c4b2f6c@forums-1-dub>
Date: 24 Jul 2010 11:22:36 -0700
X-Trace: forums-1-dub 1279995756 10.22.241.152 (24 Jul 2010 11:22:36 -0700)
X-Original-Trace: 24 Jul 2010 11:22:36 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29398
Article PK: 78627

Mark, thanks a lot for your answer.

At this time, the server with 15.0.1 was taken to the owner, and I'll get
access to it until next Monday... in the meanwhile, I wish you could help me
again with this.

I tried a different way for coding(the sp originally used Create #temp, then
Insert into #temp Select...), by using the "Select into" in order to create
the temp table. This works ok, but the problem comes when inserting data to
the temp table:

----------------------------------------------
Exec rpt_cedula 1, 1, '1/1/2010', '1/5/2010'
-----
Server Message: Number 247, Severity 16
Server 'SEICOM15', Procedure 'rpt_cedula', Line 75:
Arithmetic overflow during implicit conversion of NUMERIC value
'571849.84000000000000000000' to a NUMERIC field .
(1 row affected)
(return status = -6)
-----------------------------------------------

However, if I run the original code, it returns the data, and I just don't
find how it gets such a value.
I have attached the code of both original and modified SPs. Next monday I'll
get you the queries you requested.
This queries were run on

Adaptive Server Enterprise/15.0.2/EBF 14332/P/NT (IX86)/Windows
2000/ase1502/2486/32-bit/OPT/Thu May 24 04:10:36 2007

which BTW, is the Express edition. Haven't tested the blocking attempts.


Thanks again!



"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> escribió en el
mensaje news:4c4a1c72$1@forums-1-dub...

> blocking on tempdb == contention? yes, that would be one form of
> contention
>
> While it's possible that you could be running into a bug with ASE
> (especially with 15.0.1!!), we really need more
> details ...
>
> Could you verify when you say 'uses temp tables' that you're referring to
> tables that begin with '#'?
>
> Are any of your tempdb's configured with 'allow ddl in tran' enabled?
> (sp_helpdb)
>
> How do your sp's create the temp tables ... 'select into'? 'create table'
> + 'insert/select'?
>
> When you get a blocking situation, on what table(s) does the blocking
> occur? If you're not sure, post back here with
> the output from running the following commands during a period of
> blocking:
>
> - sp_lock
>
> - select * from master..syslogshold
>
> - select * from master..systransactions
>
> Could you also post the exact ASE version? (select @@version)
>
> Have you tried opening a case with Sybase Tech Support?
>
> ---------------------
>
> I highly recommend you consider upgrading to the latest ESD for ASE
> 15.0.3.
>
> ASE 15.0 and 15.0.1 were so buggy that they shouldn't have been released
> to the general public.
>
> Early releases of 15.0.2 were a little better, but still quite
> problem-prone.
>
> Later ESDs of 15.0.2 were pretty decent, and 15.0.3 has been fairly stable
> (compared to earlier 15.x versions).
>
>
>
> Manuel Espinoza wrote:
>> Hi all!
>>
>>
>> We have many(150+) SPs for reporting that uses temp tables. However, we
>> just
>> found that when creating the temp tables, there's blocking on the
>> TempDB(is
>> that called contention?). We found that by creating empty physical tables
>> instead would increase the speed and avoid this blocking problem. Prior
>> to
>> that, there were another 14 tempdb added to reduce the chances to block,
>> but
>> no luck...
>>
>> But, I wonder if is there any other solution for this? I just don't think
>> this would be the better solution(create one empty table for each SP!) .
>> Any config that must be set?
>>
>>
>> Thanks in advance!
>>
>>
>> ASE 15.0.1
>> Win2k3 Server
>>
>>


"Mark A. Parsons" <iron_horse Posted on 2010-07-24 19:40:52.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: TempDB block using #temp tables...
References: <4c4a0c73@forums-1-dub> <4c4a1c72$1@forums-1-dub> <4c4b2f6c@forums-1-dub>
In-Reply-To: <4c4b2f6c@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c4b41c4$1@forums-1-dub>
Date: 24 Jul 2010 12:40:52 -0700
X-Trace: forums-1-dub 1280000452 10.22.241.152 (24 Jul 2010 12:40:52 -0700)
X-Original-Trace: 24 Jul 2010 12:40:52 -0700, vip152.sybase.com
Lines: 125
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29399
Article PK: 78630

The original code explicitly states the datatype of each column in the #temporal table.

The modified code leaves it up to the dataserver to decide what datatypes to apply to the 4th thru 14th columns of the
#temporal table. (The datatypes of the first 3 columns are derived from the lineas and/or empresas tables.)

Try running the SELECT/INTO as a stand-alone query and then run sp_help on #temporal to see what datatypes the
dataserver chose to apply to the 4th thru 14th columns.

Manuel Espinoza wrote:
> Mark, thanks a lot for your answer.
>
> At this time, the server with 15.0.1 was taken to the owner, and I'll get
> access to it until next Monday... in the meanwhile, I wish you could help me
> again with this.
>
> I tried a different way for coding(the sp originally used Create #temp, then
> Insert into #temp Select...), by using the "Select into" in order to create
> the temp table. This works ok, but the problem comes when inserting data to
> the temp table:
>
> ----------------------------------------------
> Exec rpt_cedula 1, 1, '1/1/2010', '1/5/2010'
> -----
> Server Message: Number 247, Severity 16
> Server 'SEICOM15', Procedure 'rpt_cedula', Line 75:
> Arithmetic overflow during implicit conversion of NUMERIC value
> '571849.84000000000000000000' to a NUMERIC field .
> (1 row affected)
> (return status = -6)
> -----------------------------------------------
>
> However, if I run the original code, it returns the data, and I just don't
> find how it gets such a value.
> I have attached the code of both original and modified SPs. Next monday I'll
> get you the queries you requested.
> This queries were run on
>
> Adaptive Server Enterprise/15.0.2/EBF 14332/P/NT (IX86)/Windows
> 2000/ase1502/2486/32-bit/OPT/Thu May 24 04:10:36 2007
>
> which BTW, is the Express edition. Haven't tested the blocking attempts.
>
>
> Thanks again!
>
>
>
> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> escribió en el
> mensaje news:4c4a1c72$1@forums-1-dub...
>> blocking on tempdb == contention? yes, that would be one form of
>> contention
>>
>> While it's possible that you could be running into a bug with ASE
>> (especially with 15.0.1!!), we really need more
>> details ...
>>
>> Could you verify when you say 'uses temp tables' that you're referring to
>> tables that begin with '#'?
>>
>> Are any of your tempdb's configured with 'allow ddl in tran' enabled?
>> (sp_helpdb)
>>
>> How do your sp's create the temp tables ... 'select into'? 'create table'
>> + 'insert/select'?
>>
>> When you get a blocking situation, on what table(s) does the blocking
>> occur? If you're not sure, post back here with
>> the output from running the following commands during a period of
>> blocking:
>>
>> - sp_lock
>>
>> - select * from master..syslogshold
>>
>> - select * from master..systransactions
>>
>> Could you also post the exact ASE version? (select @@version)
>>
>> Have you tried opening a case with Sybase Tech Support?
>>
>> ---------------------
>>
>> I highly recommend you consider upgrading to the latest ESD for ASE
>> 15.0.3.
>>
>> ASE 15.0 and 15.0.1 were so buggy that they shouldn't have been released
>> to the general public.
>>
>> Early releases of 15.0.2 were a little better, but still quite
>> problem-prone.
>>
>> Later ESDs of 15.0.2 were pretty decent, and 15.0.3 has been fairly stable
>> (compared to earlier 15.x versions).
>>
>>
>>
>> Manuel Espinoza wrote:
>>> Hi all!
>>>
>>>
>>> We have many(150+) SPs for reporting that uses temp tables. However, we
>>> just
>>> found that when creating the temp tables, there's blocking on the
>>> TempDB(is
>>> that called contention?). We found that by creating empty physical tables
>>> instead would increase the speed and avoid this blocking problem. Prior
>>> to
>>> that, there were another 14 tempdb added to reduce the chances to block,
>>> but
>>> no luck...
>>>
>>> But, I wonder if is there any other solution for this? I just don't think
>>> this would be the better solution(create one empty table for each SP!) .
>>> Any config that must be set?
>>>
>>>
>>> Thanks in advance!
>>>
>>>
>>> ASE 15.0.1
>>> Win2k3 Server
>>>
>>>
>
>


"Manuel Espinoza" <jmespinoza_no_spam Posted on 2010-07-26 20:50:11.0Z
From: "Manuel Espinoza" <jmespinoza_no_spam@seicom_dot_com_dot_mx>
Newsgroups: sybase.public.ase.general
References: <4c4a0c73@forums-1-dub> <4c4a1c72$1@forums-1-dub> <4c4b2f6c@forums-1-dub> <4c4b41c4$1@forums-1-dub>
Subject: Re: TempDB block using #temp tables...
Lines: 161
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c4df503@forums-1-dub>
Date: 26 Jul 2010 13:50:11 -0700
X-Trace: forums-1-dub 1280177411 10.22.241.152 (26 Jul 2010 13:50:11 -0700)
X-Original-Trace: 26 Jul 2010 13:50:11 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29403
Article PK: 78634

Mark, thanks for your support.

It was indeed choosing shorter datatypes, so I just intialized the columns
with some operations instead of zeroes, and voilá! It works great at least
in 15.0.2. I'm starting the test in 15.0.1 in a few minutes(once the server
is up and running)

Now a third question comes here. Our client, owner of the ASE licence, says
that the last year they tried to upgrade to 15.0.2, and the only problem
they found is that the key file downloaded from sybase was not valid for
that version. So they tried with techsupport but as long as their support
contract wasn't renewed, they refuse to help with this case. So, the
question is, do you or someone else have any idea of why is that? Shouldn't
it be as easy as applying a patch? And in any case, shouldn't be provided a
valid key? after all, the client have paid for it already. Maybe you can
provide guidance on steps to be followed to get such valid key file for
another version than the originally bought one?


I'll let you know what the results are in ASE 15.0.1 soon...

Thanks again!





"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> escribió en el
mensaje news:4c4b41c4$1@forums-1-dub...

> The original code explicitly states the datatype of each column in the
> #temporal table.
>
> The modified code leaves it up to the dataserver to decide what datatypes
> to apply to the 4th thru 14th columns of the #temporal table. (The
> datatypes of the first 3 columns are derived from the lineas and/or
> empresas tables.)
>
> Try running the SELECT/INTO as a stand-alone query and then run sp_help on
> #temporal to see what datatypes the dataserver chose to apply to the 4th
> thru 14th columns.
>
> Manuel Espinoza wrote:
>> Mark, thanks a lot for your answer.
>>
>> At this time, the server with 15.0.1 was taken to the owner, and I'll get
>> access to it until next Monday... in the meanwhile, I wish you could help
>> me again with this.
>>
>> I tried a different way for coding(the sp originally used Create #temp,
>> then Insert into #temp Select...), by using the "Select into" in order to
>> create the temp table. This works ok, but the problem comes when
>> inserting data to the temp table:
>>
>> ----------------------------------------------
>> Exec rpt_cedula 1, 1, '1/1/2010', '1/5/2010'
>> -----
>> Server Message: Number 247, Severity 16
>> Server 'SEICOM15', Procedure 'rpt_cedula', Line 75:
>> Arithmetic overflow during implicit conversion of NUMERIC value
>> '571849.84000000000000000000' to a NUMERIC field .
>> (1 row affected)
>> (return status = -6)
>> -----------------------------------------------
>>
>> However, if I run the original code, it returns the data, and I just
>> don't find how it gets such a value.
>> I have attached the code of both original and modified SPs. Next monday
>> I'll get you the queries you requested.
>> This queries were run on
>>
>> Adaptive Server Enterprise/15.0.2/EBF 14332/P/NT (IX86)/Windows
>> 2000/ase1502/2486/32-bit/OPT/Thu May 24 04:10:36 2007
>>
>> which BTW, is the Express edition. Haven't tested the blocking attempts.
>>
>>
>> Thanks again!
>>
>>
>>
>> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> escribió en el
>> mensaje news:4c4a1c72$1@forums-1-dub...
>>> blocking on tempdb == contention? yes, that would be one form of
>>> contention
>>>
>>> While it's possible that you could be running into a bug with ASE
>>> (especially with 15.0.1!!), we really need more
>>> details ...
>>>
>>> Could you verify when you say 'uses temp tables' that you're referring
>>> to tables that begin with '#'?
>>>
>>> Are any of your tempdb's configured with 'allow ddl in tran' enabled?
>>> (sp_helpdb)
>>>
>>> How do your sp's create the temp tables ... 'select into'? 'create
>>> table' + 'insert/select'?
>>>
>>> When you get a blocking situation, on what table(s) does the blocking
>>> occur? If you're not sure, post back here with
>>> the output from running the following commands during a period of
>>> blocking:
>>>
>>> - sp_lock
>>>
>>> - select * from master..syslogshold
>>>
>>> - select * from master..systransactions
>>>
>>> Could you also post the exact ASE version? (select @@version)
>>>
>>> Have you tried opening a case with Sybase Tech Support?
>>>
>>> ---------------------
>>>
>>> I highly recommend you consider upgrading to the latest ESD for ASE
>>> 15.0.3.
>>>
>>> ASE 15.0 and 15.0.1 were so buggy that they shouldn't have been released
>>> to the general public.
>>>
>>> Early releases of 15.0.2 were a little better, but still quite
>>> problem-prone.
>>>
>>> Later ESDs of 15.0.2 were pretty decent, and 15.0.3 has been fairly
>>> stable (compared to earlier 15.x versions).
>>>
>>>
>>>
>>> Manuel Espinoza wrote:
>>>> Hi all!
>>>>
>>>>
>>>> We have many(150+) SPs for reporting that uses temp tables. However, we
>>>> just
>>>> found that when creating the temp tables, there's blocking on the
>>>> TempDB(is
>>>> that called contention?). We found that by creating empty physical
>>>> tables
>>>> instead would increase the speed and avoid this blocking problem. Prior
>>>> to
>>>> that, there were another 14 tempdb added to reduce the chances to
>>>> block, but
>>>> no luck...
>>>>
>>>> But, I wonder if is there any other solution for this? I just don't
>>>> think
>>>> this would be the better solution(create one empty table for each SP!)
>>>> .
>>>> Any config that must be set?
>>>>
>>>>
>>>> Thanks in advance!
>>>>
>>>>
>>>> ASE 15.0.1
>>>> Win2k3 Server
>>>>
>>>>
>>


"Mark A. Parsons" <iron_horse Posted on 2010-07-27 23:58:32.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: TempDB block using #temp tables...
References: <4c4a0c73@forums-1-dub> <4c4a1c72$1@forums-1-dub> <4c4b2f6c@forums-1-dub> <4c4b41c4$1@forums-1-dub> <4c4df503@forums-1-dub>
In-Reply-To: <4c4df503@forums-1-dub>
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: <4c4f72a8$1@forums-1-dub>
Date: 27 Jul 2010 16:58:32 -0700
X-Trace: forums-1-dub 1280275112 10.22.241.152 (27 Jul 2010 16:58:32 -0700)
X-Original-Trace: 27 Jul 2010 16:58:32 -0700, vip152.sybase.com
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29422
Article PK: 78654

It's hard to say what the issue was with the upgrade failure ... expired license? change in hostname or hostid?
something else?

I'm not currently near a 15.x dataserver where I get to play DBA but I seem to recall being able to upgrade from 15.0.1
to 15.0.2 to 15.0.3 without needing a new key file (upgrades were in place ... no change in hostname, hostid, primary nic).

> Now a third question comes here. Our client, owner of the ASE licence, says
> that the last year they tried to upgrade to 15.0.2, and the only problem
> they found is that the key file downloaded from sybase was not valid for
> that version. So they tried with techsupport but as long as their support
> contract wasn't renewed, they refuse to help with this case. So, the
> question is, do you or someone else have any idea of why is that? Shouldn't
> it be as easy as applying a patch? And in any case, shouldn't be provided a
> valid key? after all, the client have paid for it already. Maybe you can
> provide guidance on steps to be followed to get such valid key file for
> another version than the originally bought one?