Problems with stored procedures in ASE 12

I have recently upgraded a test RS6000 server running AIX 4.3.3 maintenance 
level 6 from ASE 11.0.3.3 to ASE 12. A stored procedure to create a table 
which used to run perfectly well in a matter of minutes now hangs 
completely, but the same code when run as a script runs in seconds. The 
clue, I think, is in the showplans for the script and stored procedure. For 
the script you just get:

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is CREATE TABLE.


But for the stored procedure after this line 

    STEP 1
        The type of query is EXECUTE.

you get "The type of query is CREATE TABLE" repeated continuosly, as if the 
procedure was stuck in a loop.

Any ideas? Thanks.

Steve

0
Steve_Mayne
8/8/2001 4:40:39 PM
sybase.ase.performance+tuning 2395 articles. 0 followers. Follow

6 Replies
525 Views

Similar Articles

[PageSpeed] 56

Hello Steve,

Have you dropped and recreated the proc in 12?

Later,

Eric

Steve_Mayne wrote:

> I have recently upgraded a test RS6000 server running AIX 4.3.3 maintenance
> level 6 from ASE 11.0.3.3 to ASE 12. A stored procedure to create a table
> which used to run perfectly well in a matter of minutes now hangs
> completely, but the same code when run as a script runs in seconds. The
> clue, I think, is in the showplans for the script and stored procedure. For
> the script you just get:
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>     STEP 1
>         The type of query is CREATE TABLE.
>
> But for the stored procedure after this line
>
>     STEP 1
>         The type of query is EXECUTE.
>
> you get "The type of query is CREATE TABLE" repeated continuosly, as if the
> procedure was stuck in a loop.
>
> Any ideas? Thanks.
>
> Steve

0
Eric
8/8/2001 7:18:01 PM
Hello Eric,

Thanks for your response.

Yes, I tried dropping and re-creating (I had to re-write it to do so as it 
was originally written for a pre-ASE 11 version) but with the same results. 
Is this recommended anyway?

However, ASE 12 is at the base level and I'm in the process of patching up 
to at least version 12.0.0.1 (SWR 9268) to see if this makes any 
difference. I'll let you know what happens.

Regards,

Steve 

Eric Miner wrote:

Hello Steve, 
Have you dropped and recreated the proc in 12?

Later,

Eric


0
Steve_Mayne
8/10/2001 8:06:44 AM
Steve_Mayne wrote...
> Hello Eric,
> 
> Thanks for your response.
> 
> Yes, I tried dropping and re-creating (I had to re-write it to do so as it 
> was originally written for a pre-ASE 11 version) but with the same results. 
> Is this recommended anyway?
> 
> However, ASE 12 is at the base level and I'm in the process of patching up 
> to at least version 12.0.0.1 (SWR 9268) to see if this makes any 
> difference. I'll let you know what happens.

I would go to 12.0.0.3.
-- 
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Achieve Nerdvana at Sybase TechWave 2001 
http://www.sybase.com/events/techwave2001
August 12th - 16th, San Diego
0
Jim
8/10/2001 2:10:09 PM
Jim,

Thanks for your response. Sorry for the delay in acknowledging it, but I've 
been on holiday.

Why do you recommend 12.0.0.3? Version 12.0.0.1 actually cured the problem 
I was having, but now I've got other problems with loading data using bcp 
(syntax errors in the source field) that don't occur in version 11. 
Changing the format of the data (which comes from a mainframe) will be a 
nightmare so is 12.0.0.3 likely to help? 

Regards,

Steve Mayne


Jim Egan wrote:

I would go to 12.0.0.3.


Steve Mayne wrote:

Hello Eric, 
Thanks for your response.

Yes, I tried dropping and re-creating (I had to re-write it to do so as it 
was originally written for a pre-ASE 11 version) but with the same results. 

Is this recommended anyway?

However, ASE 12 is at the base level and I'm in the process of patching up 
to at least version 12.0.0.1 (SWR 9268) to see if this makes any 
difference. I'll let you know what happens.


0
Steve_Mayne
9/3/2001 2:32:25 PM
Eric,

Version 12.0.0.1 cured the problem I was having, but now I've got other 
problems with loading data using bcp (syntax errors in the source field) 
that don't occur in version 11. 

Sorry for the delay in letting you know but I've been on holiday.

Regards,

Steve



Steve Mayne wrote:

Hello Eric, 
Thanks for your response.

Yes, I tried dropping and re-creating (I had to re-write it to do so as it 
was originally written for a pre-ASE 11 version) but with the same results. 
Is this recommended anyway?

However, ASE 12 is at the base level and I'm in the process of patching up 
to at least version 12.0.0.1 (SWR 9268) to see if this makes any 
difference. I'll let you know what happens.



Eric Miner


Hello Steve, 
Have you dropped and recreated the proc in 12?

0
Steve_Mayne
9/3/2001 2:44:57 PM
Steve_Mayne wrote...
> Jim,
> 
> Thanks for your response. Sorry for the delay in acknowledging it, but I've 
> been on holiday.
> 
> Why do you recommend 12.0.0.3? Version 12.0.0.1 actually cured the problem 
> I was having, but now I've got other problems with loading data using bcp 
> (syntax errors in the source field) that don't occur in version 11. 
> Changing the format of the data (which comes from a mainframe) will be a 
> nightmare so is 12.0.0.3 likely to help? 

I didn't care for the stability of 12.0.0.1.  Things smoothed out quite a bit after 
12.0.0.1 and I would be installing the latest release to make sure I picked up all the 
current fixes.  But, if 12.0.0.1 works fine for you then that's what matters.
-- 
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services
0
Jim
9/6/2001 4:31:47 AM
Reply:

Similar Artilces:

ASE 12.0.0.0 / SWR 8775 UPGRADE TO ASE 12.0.0.6/EBF 10628 PERFORMANCE PROBLEM
Hi All, We recently upgraded ASE 12.0.0.0/SWR 8775 to ASE 12.0.0.6/EBF 10628. Some queries stopped returning resultsets. Recompiled the stored procs which fixed the issue. But the queries are running slower now. The overall performance of the app has become sluggish. The CPU is generally running higher as well! Have also set LD_LIBRARY_PATH_64 to include the new Solaris 8 threads library. Anybody any insights into this? How can we get back to the pre-upgrade performance levels short of rolling back the EBF. Regards Perry Perry, I guess 12.0.0.6 requires some Solaris ...

Problems using BCP from within a stored procedure in ASE 12
This is a multi-part message in MIME format. --------------B45F4A4EF0AE7BAC610304FB Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Greetings, We are testing our application on Sybase 12, and we ran into a problem: Our application uses a stored procedure to import and export data out of the database. Within the stored procedure we call XP_CMDSHELL to execute the BCP command line: EXEC PROC_CALL_XP_CMDSHELL @CMD, @OK output CMD contains the command line and is of the format: '...\BIN\BCP.EXE DB..STAGING_TABLE in filename -U user -P pswd -S ...

Performance Degradation after ASE 12.0 upgrade to ASE 12.5
Hello, There was a issue posted on 08/06/2002 by 'Bob' related to performance degradation after upgrade of ASE 11.9.2 to ASE 12.5. I would like to know whether anyone has found any solution/workaround to resolve those issues without making any changes in the application code. We are also facing the same problems here after upgrade of ASE 12.0 to ASE 12.5. The program which takes 10 mins. on ASE 12.0 is taking more than 3 hrs to finish on ASE 12.5 despite of using the same indexes and the program which takes 3 hrs on ASE 12.0 is taking more than 24 hours on ASE 12.5. ...

Dump/Load (from ASE 12.5.4 to ASE 15.x) - VS.
Hi All, We are in the process of planning the upgrade of our ASE 12.5.4 dataservers to ASE 15.x. What is the most recommended way to upgrade ASE dataservers from ASE 12.5.4 to ASE 15.x? Potential options: 1) Install new instances of ASE 15.x and load database dumps from our ASE 12.5.4 dataservers to the newly installed ASE 15.x dataservers. - OR - 2) Use the ASE upgrade utility (sqlupgrade) to upgrade our existing ASE 12.5.4 dataservers to ASE 15.x. What are the Pros vs. Cons for loading a 12.5.4 database into a 15.x ASE dataserver versus performing an ASE dataserv...

ASA and ASE stored procedures -- problems with..
This is a multi-part message in MIME format. ------=_NextPart_000_0037_01BF587C.BF33DF60 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Greetings. Has anyone encountered differences with ASA and ASE stored procedures = when using temporary tables (select colA into #temp_table from app_table where...). I created several stored procedures while using ASA which = since have been migrated to ASE. I am running into some peculiarities with what worked in ODBC and now = does not work in the Enterprise. This is for both sto...

ASE 12: Performence problem login
Hello forum, The time for a login (via JConnect) to our ASE 12 server varies from 150 ms to 6000 ms. Do we need more CPU or what? /L ...

Performance Problem in ASE 12.5
We upgraded from ASE 11.9.2.5 to ASE 12.5 EBF#1. This is in QA environment. The Server performed well for about 2 weeks and then the CPU % shot up to about 100%. More specifically weblogic was causing table scans in the database. Unfortunately I did not take a snaphot of the sysmon before I recycle the server. Recycling the server seem to have fixed the problem. But I am looking for a more concrete root cause. Is there a known memory leak issue in ASE12.5. ....Ishrat Having any rep agents on the server?? maybe you should upgrade to latest EBF getting all known bugs fixed. Ke...

Differences between ASE 12 and ASE 12.5
Hi! I am in the process to migrate mi ASE from version 11.5.1 to an upper one. I am thinking that the logic step is to go to ASE 12, but I was wondering about the version 12.5. would there be any strong reason why I should choose to upgrade to version 12.5 other than just jumping to a newer version than 12. ? What are the main differences between ASE versions 12 and 12.5? greetings !! =:D We are going from 11.9 to 12.5, skipping 12.0. We have two reasons. First, because we are tired of making regular upgrades and figure we can stay on 12.5 for years whereas...

ASE and ASE
Could it be possible to take scripts from ASA and load them in an ASE Any information is welcome jean-fran�ois ASA supports a fairly large sub-set of Transact-SQL, so if you write your stored procedures and triggers in ASA using T-SQL, you should be able to create scripts that will run against both ASE and ASA. If you're planning to do this though, I would suggest developing your database schema against ASA, since everything you write in T-SQL in ASA will be supported on ASE, but the reverse is not true. Check out the section in the ASA documentation entitled "Tra...

ASE to ASE
Hi, Is anyone out there doing ASE to ASE replication using SQL remote? If you are have created your own version of the SSEXTRACT utility to set up a replicant database in ASE rather than ASA? Or does anyone know if SYBASE has created a version SSEXTRACT for use with ASE to ASE replication? Thanks in advance Doug Trainer Hi Douglas, I thought the SQL Remote Replication support in ASE11.5 was meant for a consolidate-database only (by design)! At least that was what Sybase said when they announced support for SQL Remote technology support in ASE11.5. I will be happy to...

Problems with restoring from ASE 11.9.2 to ASE 12.0
Hello all, We have a production machine which we did a full backup to a tape. It is running ASE 11.9.2 on Solaris 2.6. We are trying to restore the master and production db to a new machine running ASE 12.0 on Solaris 8. The mapping between the logins and the users were mismatched. Has anyone seen this problem before? If so, what is the solution? Thanks in advance -- Tony Keith Tony Keith wrote: > We have a production machine which we did a full backup to a tape. > It is running ASE 11.9.2 on Solaris 2.6. > We are trying to restore the master and productio...

Performance issues after upgrate ASE 11.5 to ASE 12.5
Hi How can we improve performance? We're moving a database that was operating fairly well on a HP-UX 10.20 with Adaptive Server Enterprise v. 11.5 (32 bits) to a HP-UX 11.0 with Adaptive Server Enterprise v. 12.5 (32 bits) Our problem is the slower performance of the database on the new, more powerful and faster machine with newer software (in both operative system and sybase version) We've noted that it starts really fast but slows down gradually. We�ve tried many ways to improve the performance: -Increase the max memory. -Increase the number of engines at startup...

ASE 12.5 vs ASE 11.5 performance issues
Hi All, Again my question is about performance of ASE 12.5 against 11.5.01 We have a huge production client-server application running on W2K server with ASE 11.5.0.1 database (more than 400 tables, 2000 stored procedures). The database was placed on raw disk partitions (no RAID) and split between different physical drives as follows: data, log, tempdb and master database are located on different drives. We have two PIII, 1GHz, 1 GB of memory and give 800MB for ASE. We create separate tempdb cache and bind tempdb to it. Database was bind to default data cache. Then we want to switc...

Cursors in Stored Procedures ASE 12.0
Hello, I have a Stored Procedure that will have 2 cursors. The first cursor is the main and then there will be another cursor within. But I keep getting a message stating that I was trying to use the second cursor but it was not open. If you have another suggestion, please let me know. Here is the Stored Procedure: declare @audit_dt smalldatetime, @calc_dt smalldatetime, @diff_dt int, @corp_ord_nbr char(10), @nbr_rqt_ord int, @conf_lvl int, @prev_dt smalldatetime, @conf_lvl_count int, @conv_audit_dt smalldatetime @amulti_nbr_rqt_ord int, @amulti_ovrd_conf_dt, ...

Web resources about - Problems with stored procedures in ASE 12 - sybase.ase.performance+tuning

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Are patients charged drastically different prices for the same medical procedure? - Videos - CBS News ...
Patients are often charged drastically different prices for the same medical procedures and research may link later bedtimes with gaining weight. ...

Vin Scully To Miss Dodgers Postseason After Medical Procedure
... , who has been calling Dodgers games since the Truman administration, will miss the team’s playoff run as he recovers from a medical procedure. ...

Metal Gear Online PS4 release trolled by ancient PSN update procedures
One of the chief problems I have with the PS4 is the way the storefront (and subsequently, the release of most games) works. Not only has the ...

FDA finds flaws in Theranos' nanotainers and quality control procedures
... to Theranos' blood testing technology comes from the US Food and Drug Administration, which found flaws in the company's quality control procedures. ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Hall Of Fame Broadcaster Vin Scully Has Medical Procedure, Will Miss All Of 2015 Postseason
While the Dodgers will be in the 2015 MLB postseason, their Hall of Fame broadcaster, won't. Here's details on Vin Scully.

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Resources last updated: 11/21/2015 3:33:27 PM