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.

Making Database Effectively Read-only

5 posts in General Discussion Last posting was on 2009-11-05 18:20:19.0Z
Scott Kellish Posted on 2009-11-05 03:19:51.0Z
From: "Scott Kellish" <skellish@softsystemsolutions.com>
Newsgroups: sybase.public.ase.general
Subject: Making Database Effectively Read-only
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
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: <4af24457$1@forums-1-dub>
Date: 4 Nov 2009 19:19:51 -0800
X-Trace: forums-1-dub 1257391191 10.22.241.152 (4 Nov 2009 19:19:51 -0800)
X-Original-Trace: 4 Nov 2009 19:19:51 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28608
Article PK: 77851

Hi,
I have a Sybase 12.5 production database that we're upgrading functionality.
We'll be doing a dump/load of the production db to a new server and then
doing an in-place upgrade the the schema etc. We will dropping a number of
tables because the functionality is not needed going forward. We plan to
keep the original database on-line to users can access the historical data
(that is being dropped).

Can someone out-line the options for making the database read-only? There
are approx 400 tables and close to approx 2000 stored procedures.

I understand that setting the "read-only" option will not work as it would
prevent the stored procedures from working. Is this true? Does the read-only
option apply to the server as a whole of individual databases?

Thanks in advance
Scott Kellish


james Posted on 2009-11-05 07:05:01.0Z
From: james <hobbylu@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Making Database Effectively Read-only
Date: Wed, 4 Nov 2009 23:05:01 -0800 (PST)
Organization: http://groups.google.com
Lines: 9
Message-ID: <e942209f-c9fc-42d6-a873-476012854d74@h14g2000pri.googlegroups.com>
References: <4af24457$1@forums-1-dub>
NNTP-Posting-Host: 222.44.33.189
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1257404701 10053 127.0.0.1 (5 Nov 2009 07:05:01 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 5 Nov 2009 07:05:01 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: h14g2000pri.googlegroups.com; posting-host=222.44.33.189; posting-account=lLJJXAoAAAB5c3kS_iuETkFHUOu286BD
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; SE 1.X; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; .NET CLR 1.1.4322; CIBA; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; SE 1.X),gzip(gfe),gzip(gfe)
X-Original-Bytes: 1379
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!h14g2000pri.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28611
Article PK: 77852

you can use dboption 'read only'
use master
go
sp_dboption 'dbname','read only',true
go
use dbname
go
checkpoint
go


Scott Kellish Posted on 2009-11-05 17:41:03.0Z
From: "Scott Kellish" <skellish@softsystemsolutions.com>
Newsgroups: sybase.public.ase.general
References: <4af24457$1@forums-1-dub> <e942209f-c9fc-42d6-a873-476012854d74@h14g2000pri.googlegroups.com>
Subject: Re: Making Database Effectively Read-only
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af30e2f$1@forums-1-dub>
Date: 5 Nov 2009 09:41:03 -0800
X-Trace: forums-1-dub 1257442863 10.22.241.152 (5 Nov 2009 09:41:03 -0800)
X-Original-Trace: 5 Nov 2009 09:41:03 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28620
Article PK: 77860

Thanks,
So since this applied db by db, I would leave tempdb in R/W mode so stored
procedures against a R/O database that use temp tables, would continue to
work?

"james" <hobbylu@gmail.com> wrote in message
news:e942209f-c9fc-42d6-a873-476012854d74@h14g2000pri.googlegroups.com...
> you can use dboption 'read only'
> use master
> go
> sp_dboption 'dbname','read only',true
> go
> use dbname
> go
> checkpoint
> go


Carl Kayser Posted on 2009-11-05 18:20:19.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4af24457$1@forums-1-dub> <e942209f-c9fc-42d6-a873-476012854d74@h14g2000pri.googlegroups.com> <4af30e2f$1@forums-1-dub>
Subject: Re: Making Database Effectively Read-only
Lines: 29
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af31763$1@forums-1-dub>
Date: 5 Nov 2009 10:20:19 -0800
X-Trace: forums-1-dub 1257445219 10.22.241.152 (5 Nov 2009 10:20:19 -0800)
X-Original-Trace: 5 Nov 2009 10:20:19 -0800, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28621
Article PK: 77863


"Scott Kellish" <skellish@softsystemsolutions.com> wrote in message
news:4af30e2f$1@forums-1-dub...
> Thanks,
> So since this applied db by db, I would leave tempdb in R/W mode so stored
> procedures against a R/O database that use temp tables, would continue to
> work?
>

Correct. And if you have multiple temp dbs ... the same applies.


>
>
> "james" <hobbylu@gmail.com> wrote in message
> news:e942209f-c9fc-42d6-a873-476012854d74@h14g2000pri.googlegroups.com...
>> you can use dboption 'read only'
>> use master
>> go
>> sp_dboption 'dbname','read only',true
>> go
>> use dbname
>> go
>> checkpoint
>> go
>
>


Carl Kayser Posted on 2009-11-05 11:16:19.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4af24457$1@forums-1-dub>
Subject: Re: Making Database Effectively Read-only
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af2b403$1@forums-1-dub>
Date: 5 Nov 2009 03:16:19 -0800
X-Trace: forums-1-dub 1257419779 10.22.241.152 (5 Nov 2009 03:16:19 -0800)
X-Original-Trace: 5 Nov 2009 03:16:19 -0800, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28613
Article PK: 77854


"Scott Kellish" <skellish@softsystemsolutions.com> wrote in message
news:4af24457$1@forums-1-dub...
> Hi,
> I have a Sybase 12.5 production database that we're upgrading
> functionality. We'll be doing a dump/load of the production db to a new
> server and then doing an in-place upgrade the the schema etc. We will
> dropping a number of tables because the functionality is not needed going
> forward. We plan to keep the original database on-line to users can access
> the historical data (that is being dropped).
>
> Can someone out-line the options for making the database read-only? There
> are approx 400 tables and close to approx 2000 stored procedures.
>

Answered sperately by James.

> I understand that setting the "read-only" option will not work as it would
> prevent the stored procedures from working. Is this true? Does the
> read-only option apply to the server as a whole of individual databases?
>

If the stored procedures do update/delete/insert/truncate then they
obviously won't work. A more subtle problem is if they (or the client) do a
"begin tran" or have "set autocommit on" (with a select). ASE (foolishly)
considers it as a write and won't allow it for a read-only database.

> Thanks in advance
> Scott Kellish
>