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.

Why the "set compatibility_mode" command always in work?

8 posts in General Discussion Last posting was on 2011-07-14 13:22:53.0Z
Eisen Posted on 2011-07-14 06:45:57.0Z
Sender: 37c1.4e1e84da.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Why the "set compatibility_mode" command always in work?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e1e90a5.3919.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Jul 2011 23:45:57 -0700
X-Trace: forums-1-dub 1310625957 10.22.241.41 (13 Jul 2011 23:45:57 -0700)
X-Original-Trace: 13 Jul 2011 23:45:57 -0700, 10.22.241.41
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30343
Article PK: 72521

Dear sirs,
Currently we have both ASE15.0.2 and ASE15.0.3, since
there's some bug in ASE15.0.3 so some procedures must be run
in compatibility_mode. So I thought if I place a switch
table in all dbs, contains the bug version, when running the
procedure, the SP can choose use compatibility mode or not.
So I made some test as below:

--initial the run environment
set compatibility_mode off
go
--Create the switch table , insert the Server version which
has bug
select @@version Version into AAtblComVersion
go

set showplan off
go
if object_id('p_testwj1')<>null drop procedure p_testwj1
go
create procedure p_testwj1
with recompile
as
begin
if exists(select 1 from AAtblComVersion where
Version=@@version) --compare the current @@version with
value in AAtblComversion
begin
print 'a'
set compatibility_mode on
end
-- else
-- begin
-- print 'b'
-- set compatibility_mode off
-- end
select top 1 name from sysobjects -- the test sql part
end
go
set showplan on
go
--test 1:the @@version is same to value in AAtblComVersion,
the test sql part should run in compatibility mode

exec p_testwj1
go

--test 2:no rows in AAtblComversion, the test sql part
should run in non-compatibility mode

delete AAtblComVersion
go
exec p_testwj1
go

But unfortunately, I find both test1 and test2 were the same
, the test sql part always use compatibility mode...
I'm curious on why the "if exists" clause has no work on
"set compatibility mode"... Would anyone has some idea on
this?
Thanks a lot

Best Regards
Eisen


Eisen Posted on 2011-07-14 09:06:40.0Z
Sender: 37c1.4e1e84da.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set compatibility_mode" command always in work?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e1eb1a0.3d8d.1681692777@sybase.com>
References: <4e1e90a5.3919.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Jul 2011 02:06:40 -0700
X-Trace: forums-1-dub 1310634400 10.22.241.41 (14 Jul 2011 02:06:40 -0700)
X-Original-Trace: 14 Jul 2011 02:06:40 -0700, 10.22.241.41
Lines: 77
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30344
Article PK: 72525

I made another try , and get even strange result --
I test with
set showplan on
go
if (1=2) set compatibility_mode on
select top 1 name from sysobjects
go
The output shows that the "set compatibility_mode on
" also works while the condition not match...

Best Regards
Eisen

> Dear sirs,
> Currently we have both ASE15.0.2 and ASE15.0.3, since
> there's some bug in ASE15.0.3 so some procedures must be
> run in compatibility_mode. So I thought if I place a
> switch table in all dbs, contains the bug version, when
> running the procedure, the SP can choose use compatibility
> mode or not. So I made some test as below:
>
> --initial the run environment
> set compatibility_mode off
> go
> --Create the switch table , insert the Server version
> which has bug
> select @@version Version into AAtblComVersion
> go
>
> set showplan off
> go
> if object_id('p_testwj1')<>null drop procedure p_testwj1
> go
> create procedure p_testwj1
> with recompile
> as
> begin
> if exists(select 1 from AAtblComVersion where
> Version=@@version) --compare the current @@version with
> value in AAtblComversion
> begin
> print 'a'
> set compatibility_mode on
> end
> -- else
> -- begin
> -- print 'b'
> -- set compatibility_mode off
> -- end
> select top 1 name from sysobjects -- the test sql
> part end
> go
> set showplan on
> go
> --test 1:the @@version is same to value in AAtblComVersion
> , the test sql part should run in compatibility mode
>
> exec p_testwj1
> go
>
> --test 2:no rows in AAtblComversion, the test sql part
> should run in non-compatibility mode
>
> delete AAtblComVersion
> go
> exec p_testwj1
> go
>
> But unfortunately, I find both test1 and test2 were the
> same , the test sql part always use compatibility mode...
> I'm curious on why the "if exists" clause has no work on
> "set compatibility mode"... Would anyone has some idea on
> this?
> Thanks a lot
>
> Best Regards
> Eisen


Rob V [ Sybase ] Posted on 2011-07-14 09:23:47.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@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.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set compatibility_mode" command always in work?
References: <4e1e90a5.3919.1681692777@sybase.com> <4e1eb1a0.3d8d.1681692777@sybase.com>
In-Reply-To: <4e1eb1a0.3d8d.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: <4e1eb5a3$1@forums-1-dub>
Date: 14 Jul 2011 02:23:47 -0700
X-Trace: forums-1-dub 1310635427 10.22.241.152 (14 Jul 2011 02:23:47 -0700)
X-Original-Trace: 14 Jul 2011 02:23:47 -0700, vip152.sybase.com
Lines: 107
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30346
Article PK: 72524

What you describe below (if (1=2) set compatibility_mode on) is due to
a bug that was fixed in 15.0.3 esd1: here, set compatibility_mode is
evaluated on parser level rather than at execution time.
For this and other reasons, do not use 15.0.3, but at least use 15.0.3
esd#1 (or better, 15.0.3 esd#4 whihc is the latest (and last) 15.0.3 esd).

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"

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

On 14-Jul-2011 11:06, Eisen wrote:
> I made another try , and get even strange result --
> I test with
> set showplan on
> go
> if (1=2) set compatibility_mode on
> select top 1 name from sysobjects
> go
> The output shows that the "set compatibility_mode on
> " also works while the condition not match...
>
> Best Regards
> Eisen
>
>> Dear sirs,
>> Currently we have both ASE15.0.2 and ASE15.0.3, since
>> there's some bug in ASE15.0.3 so some procedures must be
>> run in compatibility_mode. So I thought if I place a
>> switch table in all dbs, contains the bug version, when
>> running the procedure, the SP can choose use compatibility
>> mode or not. So I made some test as below:
>>
>> --initial the run environment
>> set compatibility_mode off
>> go
>> --Create the switch table , insert the Server version
>> which has bug
>> select @@version Version into AAtblComVersion
>> go
>>
>> set showplan off
>> go
>> if object_id('p_testwj1')<>null drop procedure p_testwj1
>> go
>> create procedure p_testwj1
>> with recompile
>> as
>> begin
>> if exists(select 1 from AAtblComVersion where
>> Version=@@version) --compare the current @@version with
>> value in AAtblComversion
>> begin
>> print 'a'
>> set compatibility_mode on
>> end
>> -- else
>> -- begin
>> -- print 'b'
>> -- set compatibility_mode off
>> -- end
>> select top 1 name from sysobjects -- the test sql
>> part end
>> go
>> set showplan on
>> go
>> --test 1:the @@version is same to value in AAtblComVersion
>> , the test sql part should run in compatibility mode
>>
>> exec p_testwj1
>> go
>>
>> --test 2:no rows in AAtblComversion, the test sql part
>> should run in non-compatibility mode
>>
>> delete AAtblComVersion
>> go
>> exec p_testwj1
>> go
>>
>> But unfortunately, I find both test1 and test2 were the
>> same , the test sql part always use compatibility mode...
>> I'm curious on why the "if exists" clause has no work on
>> "set compatibility mode"... Would anyone has some idea on
>> this?
>> Thanks a lot
>>
>> Best Regards
>> Eisen


Eisen Posted on 2011-07-14 12:50:24.0Z
Sender: 455f.4e1ee3e6.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set compatibility_mode" command always in work?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e1ee610.45b3.1681692777@sybase.com>
References: <4e1eb5a3$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Jul 2011 05:50:24 -0700
X-Trace: forums-1-dub 1310647824 10.22.241.41 (14 Jul 2011 05:50:24 -0700)
X-Original-Trace: 14 Jul 2011 05:50:24 -0700, 10.22.241.41
Lines: 118
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30348
Article PK: 72527

Rob, thanks a lot for your reply.
But my ASE version is alreay ASE15.0.3 ESD#4...

Best Regards
Eisen

> What you describe below (if (1=2) set compatibility_mode
> on) is due to a bug that was fixed in 15.0.3 esd1: here,
> set compatibility_mode is evaluated on parser level
> rather than at execution time. For this and other reasons,
> do not use 15.0.3, but at least use 15.0.3 esd#1 (or
> better, 15.0.3 esd#4 whihc is the latest (and last) 15.0.3
> esd).
>
> 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"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>
>
>
>
> On 14-Jul-2011 11:06, Eisen wrote:
> > I made another try , and get even strange result --
> > I test with
> > set showplan on
> > go
> > if (1=2) set compatibility_mode on
> > select top 1 name from sysobjects
> > go
> > The output shows that the "set compatibility_mode on
> > " also works while the condition not match...
> >
> > Best Regards
> > Eisen
> >
> >> Dear sirs,
> >> Currently we have both ASE15.0.2 and ASE15.0.3,
> since >> there's some bug in ASE15.0.3 so some procedures
> must be >> run in compatibility_mode. So I thought if I
> place a >> switch table in all dbs, contains the bug
> version, when >> running the procedure, the SP can choose
> use compatibility >> mode or not. So I made some test as
> below: >>
> >> --initial the run environment
> >> set compatibility_mode off
> >> go
> >> --Create the switch table , insert the Server version
> >> which has bug
> >> select @@version Version into AAtblComVersion
> >> go
> >>
> >> set showplan off
> >> go
> >> if object_id('p_testwj1')<>null drop procedure
> p_testwj1 >> go
> >> create procedure p_testwj1
> >> with recompile
> >> as
> >> begin
> >> if exists(select 1 from AAtblComVersion where
> >> Version=@@version) --compare the current @@version with
> >> value in AAtblComversion
> >> begin
> >> print 'a'
> >> set compatibility_mode on
> >> end
> >> -- else
> >> -- begin
> >> -- print 'b'
> >> -- set compatibility_mode off
> >> -- end
> >> select top 1 name from sysobjects -- the test sql
> >> part end
> >> go
> >> set showplan on
> >> go
> >> --test 1:the @@version is same to value in
> AAtblComVersion >> , the test sql part should run in
> compatibility mode >>
> >> exec p_testwj1
> >> go
> >>
> >> --test 2:no rows in AAtblComversion, the test sql part
> >> should run in non-compatibility mode
> >>
> >> delete AAtblComVersion
> >> go
> >> exec p_testwj1
> >> go
> >>
> >> But unfortunately, I find both test1 and test2 were the
> >> same , the test sql part always use compatibility
> mode... >> I'm curious on why the "if exists" clause has
> no work on >> "set compatibility mode"... Would anyone has
> some idea on >> this?
> >> Thanks a lot
> >>
> >> Best Regards
> >> Eisen
>
>


Rob V [ Sybase ] Posted on 2011-07-14 13:22:53.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@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.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set compatibility_mode" command always in work?
References: <4e1eb5a3$1@forums-1-dub> <4e1ee610.45b3.1681692777@sybase.com>
In-Reply-To: <4e1ee610.45b3.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: <4e1eedad$1@forums-1-dub>
Date: 14 Jul 2011 06:22:53 -0700
X-Trace: forums-1-dub 1310649773 10.22.241.152 (14 Jul 2011 06:22:53 -0700)
X-Original-Trace: 14 Jul 2011 06:22:53 -0700, vip152.sybase.com
Lines: 126
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30351
Article PK: 72530

In that case I'm not sure what is happening.
if you really want an answer to that, please post as simple a
reproduction as possible and use @@qpmode to determine if compat mode
was used for a statement.

R.

On 14-Jul-2011 14:50, Eisen wrote:
> Rob, thanks a lot for your reply.
> But my ASE version is alreay ASE15.0.3 ESD#4...
>
> Best Regards
> Eisen
>> What you describe below (if (1=2) set compatibility_mode
>> on) is due to a bug that was fixed in 15.0.3 esd1: here,
>> set compatibility_mode is evaluated on parser level
>> rather than at execution time. For this and other reasons,
>> do not use 15.0.3, but at least use 15.0.3 esd#1 (or
>> better, 15.0.3 esd#4 whihc is the latest (and last) 15.0.3
>> esd).
>>
>> 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"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>
>>
>>
>>
>> On 14-Jul-2011 11:06, Eisen wrote:
>>> I made another try , and get even strange result --
>>> I test with
>>> set showplan on
>>> go
>>> if (1=2) set compatibility_mode on
>>> select top 1 name from sysobjects
>>> go
>>> The output shows that the "set compatibility_mode on
>>> " also works while the condition not match...
>>>
>>> Best Regards
>>> Eisen
>>>
>>>> Dear sirs,
>>>> Currently we have both ASE15.0.2 and ASE15.0.3,
>> since>> there's some bug in ASE15.0.3 so some procedures
>> must be>> run in compatibility_mode. So I thought if I
>> place a>> switch table in all dbs, contains the bug
>> version, when>> running the procedure, the SP can choose
>> use compatibility>> mode or not. So I made some test as
>> below:>>
>>>> --initial the run environment
>>>> set compatibility_mode off
>>>> go
>>>> --Create the switch table , insert the Server version
>>>> which has bug
>>>> select @@version Version into AAtblComVersion
>>>> go
>>>>
>>>> set showplan off
>>>> go
>>>> if object_id('p_testwj1')<>null drop procedure
>> p_testwj1>> go
>>>> create procedure p_testwj1
>>>> with recompile
>>>> as
>>>> begin
>>>> if exists(select 1 from AAtblComVersion where
>>>> Version=@@version) --compare the current @@version with
>>>> value in AAtblComversion
>>>> begin
>>>> print 'a'
>>>> set compatibility_mode on
>>>> end
>>>> -- else
>>>> -- begin
>>>> -- print 'b'
>>>> -- set compatibility_mode off
>>>> -- end
>>>> select top 1 name from sysobjects -- the test sql
>>>> part end
>>>> go
>>>> set showplan on
>>>> go
>>>> --test 1:the @@version is same to value in
>> AAtblComVersion>> , the test sql part should run in
>> compatibility mode>>
>>>> exec p_testwj1
>>>> go
>>>>
>>>> --test 2:no rows in AAtblComversion, the test sql part
>>>> should run in non-compatibility mode
>>>>
>>>> delete AAtblComVersion
>>>> go
>>>> exec p_testwj1
>>>> go
>>>>
>>>> But unfortunately, I find both test1 and test2 were the
>>>> same , the test sql part always use compatibility
>> mode...>> I'm curious on why the "if exists" clause has
>> no work on>> "set compatibility mode"... Would anyone has
>> some idea on>> this?
>>>> Thanks a lot
>>>>
>>>> Best Regards
>>>> Eisen


Rob V [ Sybase ] Posted on 2011-07-14 09:32:40.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@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.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set compatibility_mode" command always in work?
References: <4e1e90a5.3919.1681692777@sybase.com>
In-Reply-To: <4e1e90a5.3919.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: <4e1eb7b8@forums-1-dub>
Date: 14 Jul 2011 02:32:40 -0700
X-Trace: forums-1-dub 1310635960 10.22.241.152 (14 Jul 2011 02:32:40 -0700)
X-Original-Trace: 14 Jul 2011 02:32:40 -0700, vip152.sybase.com
Lines: 116
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30347
Article PK: 72526

You need to code it differently:

if <condition>
begin
set compatibility_mode off
select top 1 name from sysobjects
end
else
begin
set compatibility_mode on
select top 1 name from sysobjects
end

The problem with your code is that the plan gets generated when the
stored procedure is executed (since you used with recompile, that means
upon every execution), but before it starts executing. Your statement
that sets compat mode on or off depends on the execution path, but at
that moment execution is already in progress so the plan has already
been generated. This means that the ASE optimizer is unable to apply
your set statement at that moment. In addition, it could not use it when
it generated the plan because it cannot predict which was the
conditional logic will work out (the actual parameter value is not used
for predicting execution branching at optimization time).
In my example above, it is clear to which statement the set statement
will apply because there is no conditional branching in between.

Also, note that 'with recompile' is not needed when you follow approach
in my example above.

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"

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

On 14-Jul-2011 08:45, Eisen wrote:
> Dear sirs,
> Currently we have both ASE15.0.2 and ASE15.0.3, since
> there's some bug in ASE15.0.3 so some procedures must be run
> in compatibility_mode. So I thought if I place a switch
> table in all dbs, contains the bug version, when running the
> procedure, the SP can choose use compatibility mode or not.
> So I made some test as below:
>
> --initial the run environment
> set compatibility_mode off
> go
> --Create the switch table , insert the Server version which
> has bug
> select @@version Version into AAtblComVersion
> go
>
> set showplan off
> go
> if object_id('p_testwj1')<>null drop procedure p_testwj1
> go
> create procedure p_testwj1
> with recompile
> as
> begin
> if exists(select 1 from AAtblComVersion where
> Version=@@version) --compare the current @@version with
> value in AAtblComversion
> begin
> print 'a'
> set compatibility_mode on
> end
> -- else
> -- begin
> -- print 'b'
> -- set compatibility_mode off
> -- end
> select top 1 name from sysobjects -- the test sql part
> end
> go
> set showplan on
> go
> --test 1:the @@version is same to value in AAtblComVersion,
> the test sql part should run in compatibility mode
>
> exec p_testwj1
> go
>
> --test 2:no rows in AAtblComversion, the test sql part
> should run in non-compatibility mode
>
> delete AAtblComVersion
> go
> exec p_testwj1
> go
>
> But unfortunately, I find both test1 and test2 were the same
> , the test sql part always use compatibility mode...
> I'm curious on why the "if exists" clause has no work on
> "set compatibility mode"... Would anyone has some idea on
> this?
> Thanks a lot
>
> Best Regards
> Eisen


Eisen Posted on 2011-07-14 13:03:49.0Z
Sender: 455f.4e1ee3e6.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set compatibility_mode" command always in work?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e1ee935.4618.1681692777@sybase.com>
References: <4e1eb7b8@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Jul 2011 06:03:49 -0700
X-Trace: forums-1-dub 1310648629 10.22.241.41 (14 Jul 2011 06:03:49 -0700)
X-Original-Trace: 14 Jul 2011 06:03:49 -0700, 10.22.241.41
Lines: 143
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30349
Article PK: 72529

Rob,Thanks a lot.
Now I'm clear to this question. Thanks again.
and another questions please -- When I used ASE12.xx, I can
write procedure like
create procedure sp1
as
begin
...... -- part 1
set showplan on
...... -- part 2
set showplan off
..... --part 3
end

to check the plan of part 2 and ignore part 1 and part3. But
currently, I found the "set showplan on" in SP doesn't work.
Is it changed in ASE15?

Best Regards
Eisen

> You need to code it differently:
>
> if <condition>
> begin
> set compatibility_mode off
> select top 1 name from sysobjects
> end
> else
> begin
> set compatibility_mode on
> select top 1 name from sysobjects
> end
>
> The problem with your code is that the plan gets generated
> when the stored procedure is executed (since you used
> with recompile, that means upon every execution), but
> before it starts executing. Your statement that sets
> compat mode on or off depends on the execution path, but
> at that moment execution is already in progress so the
> plan has already been generated. This means that the ASE
> optimizer is unable to apply your set statement at that
> moment. In addition, it could not use it when it
> generated the plan because it cannot predict which was the
> conditional logic will work out (the actual parameter
> value is not used for predicting execution branching at
> optimization time). In my example above, it is clear to
> which statement the set statement will apply because
> there is no conditional branching in between.
>
> Also, note that 'with recompile' is not needed when you
> follow approach in my example above.
>
> 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"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>
>
>
>
> On 14-Jul-2011 08:45, Eisen wrote:
> > Dear sirs,
> > Currently we have both ASE15.0.2 and ASE15.0.3,
> > since there's some bug in ASE15.0.3 so some procedures
> > must be run in compatibility_mode. So I thought if I
> > place a switch table in all dbs, contains the bug
> > version, when running the procedure, the SP can choose
> > use compatibility mode or not. So I made some test as
> below: >
> > --initial the run environment
> > set compatibility_mode off
> > go
> > --Create the switch table , insert the Server version
> > which has bug
> > select @@version Version into AAtblComVersion
> > go
> >
> > set showplan off
> > go
> > if object_id('p_testwj1')<>null drop procedure p_testwj1
> > go
> > create procedure p_testwj1
> > with recompile
> > as
> > begin
> > if exists(select 1 from AAtblComVersion where
> > Version=@@version) --compare the current @@version with
> > value in AAtblComversion
> > begin
> > print 'a'
> > set compatibility_mode on
> > end
> > -- else
> > -- begin
> > -- print 'b'
> > -- set compatibility_mode off
> > -- end
> > select top 1 name from sysobjects -- the test sql
> > part end
> > go
> > set showplan on
> > go
> > --test 1:the @@version is same to value in
> > AAtblComVersion, the test sql part should run in
> compatibility mode >
> > exec p_testwj1
> > go
> >
> > --test 2:no rows in AAtblComversion, the test sql part
> > should run in non-compatibility mode
> >
> > delete AAtblComVersion
> > go
> > exec p_testwj1
> > go
> >
> > But unfortunately, I find both test1 and test2 were the
> > same , the test sql part always use compatibility
> > mode... I'm curious on why the "if exists" clause has no
> > work on "set compatibility mode"... Would anyone has
> > some idea on this?
> > Thanks a lot
> >
> > Best Regards
> > Eisen
>


Rob V [ Sybase ] Posted on 2011-07-14 13:05:25.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@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.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Why the "set compatibility_mode" command always in work?
References: <4e1eb7b8@forums-1-dub> <4e1ee935.4618.1681692777@sybase.com>
In-Reply-To: <4e1ee935.4618.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: <4e1ee995$1@forums-1-dub>
Date: 14 Jul 2011 06:05:25 -0700
X-Trace: forums-1-dub 1310648725 10.22.241.152 (14 Jul 2011 06:05:25 -0700)
X-Original-Trace: 14 Jul 2011 06:05:25 -0700, vip152.sybase.com
Lines: 148
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30350
Article PK: 72528

This has not changed in ASE 15.
R.

On 14-Jul-2011 15:03, Eisen wrote:
> Rob,Thanks a lot.
> Now I'm clear to this question. Thanks again.
> and another questions please -- When I used ASE12.xx, I can
> write procedure like
> create procedure sp1
> as
> begin
> ...... -- part 1
> set showplan on
> ...... -- part 2
> set showplan off
> ..... --part 3
> end
>
> to check the plan of part 2 and ignore part 1 and part3. But
> currently, I found the "set showplan on" in SP doesn't work.
> Is it changed in ASE15?
>
> Best Regards
> Eisen
>> You need to code it differently:
>>
>> if<condition>
>> begin
>> set compatibility_mode off
>> select top 1 name from sysobjects
>> end
>> else
>> begin
>> set compatibility_mode on
>> select top 1 name from sysobjects
>> end
>>
>> The problem with your code is that the plan gets generated
>> when the stored procedure is executed (since you used
>> with recompile, that means upon every execution), but
>> before it starts executing. Your statement that sets
>> compat mode on or off depends on the execution path, but
>> at that moment execution is already in progress so the
>> plan has already been generated. This means that the ASE
>> optimizer is unable to apply your set statement at that
>> moment. In addition, it could not use it when it
>> generated the plan because it cannot predict which was the
>> conditional logic will work out (the actual parameter
>> value is not used for predicting execution branching at
>> optimization time). In my example above, it is clear to
>> which statement the set statement will apply because
>> there is no conditional branching in between.
>>
>> Also, note that 'with recompile' is not needed when you
>> follow approach in my example above.
>>
>> 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"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>
>>
>>
>>
>> On 14-Jul-2011 08:45, Eisen wrote:
>>> Dear sirs,
>>> Currently we have both ASE15.0.2 and ASE15.0.3,
>>> since there's some bug in ASE15.0.3 so some procedures
>>> must be run in compatibility_mode. So I thought if I
>>> place a switch table in all dbs, contains the bug
>>> version, when running the procedure, the SP can choose
>>> use compatibility mode or not. So I made some test as
>> below:>
>>> --initial the run environment
>>> set compatibility_mode off
>>> go
>>> --Create the switch table , insert the Server version
>>> which has bug
>>> select @@version Version into AAtblComVersion
>>> go
>>>
>>> set showplan off
>>> go
>>> if object_id('p_testwj1')<>null drop procedure p_testwj1
>>> go
>>> create procedure p_testwj1
>>> with recompile
>>> as
>>> begin
>>> if exists(select 1 from AAtblComVersion where
>>> Version=@@version) --compare the current @@version with
>>> value in AAtblComversion
>>> begin
>>> print 'a'
>>> set compatibility_mode on
>>> end
>>> -- else
>>> -- begin
>>> -- print 'b'
>>> -- set compatibility_mode off
>>> -- end
>>> select top 1 name from sysobjects -- the test sql
>>> part end
>>> go
>>> set showplan on
>>> go
>>> --test 1:the @@version is same to value in
>>> AAtblComVersion, the test sql part should run in
>> compatibility mode>
>>> exec p_testwj1
>>> go
>>>
>>> --test 2:no rows in AAtblComversion, the test sql part
>>> should run in non-compatibility mode
>>>
>>> delete AAtblComVersion
>>> go
>>> exec p_testwj1
>>> go
>>>
>>> But unfortunately, I find both test1 and test2 were the
>>> same , the test sql part always use compatibility
>>> mode... I'm curious on why the "if exists" clause has no
>>> work on "set compatibility mode"... Would anyone has
>>> some idea on this?
>>> Thanks a lot
>>>
>>> Best Regards
>>> Eisen