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.

alter table

6 posts in General Discussion Last posting was on 2011-01-06 00:25:34.0Z
jobless Posted on 2011-01-05 20:35:06.0Z
Sender: 7931.4d23870f.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: alter table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d24d5fa.531e.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Jan 2011 12:35:06 -0800
X-Trace: forums-1-dub 1294259706 10.22.241.41 (5 Jan 2011 12:35:06 -0800)
X-Original-Trace: 5 Jan 2011 12:35:06 -0800, 10.22.241.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29837
Article PK: 79064

download 15.5 for testing.

- Create database DB1 (durability = full)
- Create table TB1
- Backed up database DB1
- inserted 1 row in TB1
- Backed up tran for DB1
- inserted 2nd row in TB1
- Backed up tran for DB1
- Altered table TB1 set dml_logging to minimum
- Backed up tran for DB1
- inserted 3rd row in TB1
- Backuped up tran for DB1

When i restored DB1 and apply all logs, i still get all 3
rows in TB1 ( what did dml_logging = minimal do in this case
), i thought it will not let me dump tran but it did, i
thought if I restore all tran dumps i would not have 3rd row
but the transaction dump did have the 3rd row. If recover
upto last but 1 tran then I see only 2 rows.

ASE_RDDB


"Mark A. Parsons" <iron_horse Posted on 2011-01-05 21:21:56.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: alter table
References: <4d24d5fa.531e.1681692777@sybase.com>
In-Reply-To: <4d24d5fa.531e.1681692777@sybase.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d24e0f4$1@forums-1-dub>
Date: 5 Jan 2011 13:21:56 -0800
X-Trace: forums-1-dub 1294262516 10.22.241.152 (5 Jan 2011 13:21:56 -0800)
X-Original-Trace: 5 Jan 2011 13:21:56 -0800, vip152.sybase.com
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29838
Article PK: 79066

I haven't played with dml_logging (yet), and I can't tell from your post if you've left out some of your details (eg,
dboption settings) ... so fwiw ... from the IMDB Users Guide:

re: database-level logging:

"You must set the databaseÂ’s select into option to on for minimal logging to take effect."

re: table-level logging:

"You can execute minimally logged DML commands on a table only if the database has the select into database option
turned on; otherwise, all DML commands are fully logged."

re: session-level logging:

"Minimal DML logging requires that the select into database option is turned on, which requires a database owner or
sa_role privilege."

All 3 levels of log control state the need to have a db configured with 'select into' enabled. And from the table-level
comment it kinda sounds like the dml_logging setting is silently ignored if the database has not been configured with
'select into' enabled.

Sooooo, did you enable 'select into' for DB1?

jobless wrote:
> download 15.5 for testing.
>
> - Create database DB1 (durability = full)
> - Create table TB1
> - Backed up database DB1
> - inserted 1 row in TB1
> - Backed up tran for DB1
> - inserted 2nd row in TB1
> - Backed up tran for DB1
> - Altered table TB1 set dml_logging to minimum
> - Backed up tran for DB1
> - inserted 3rd row in TB1
> - Backuped up tran for DB1
>
> When i restored DB1 and apply all logs, i still get all 3
> rows in TB1 ( what did dml_logging = minimal do in this case
> ), i thought it will not let me dump tran but it did, i
> thought if I restore all tran dumps i would not have 3rd row
> but the transaction dump did have the 3rd row. If recover
> upto last but 1 tran then I see only 2 rows.
>
> ASE_RDDB


jobless Posted on 2011-01-06 00:09:19.0Z
Sender: 7931.4d23870f.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: alter table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d25082f.5fe5.1681692777@sybase.com>
References: <4d24e0f4$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Jan 2011 16:09:19 -0800
X-Trace: forums-1-dub 1294272559 10.22.241.41 (5 Jan 2011 16:09:19 -0800)
X-Original-Trace: 5 Jan 2011 16:09:19 -0800, 10.22.241.41
Lines: 69
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29840
Article PK: 79070

Thanks for the document name, i did not read it - will have
to read it. I just read the reference manual which indicated
DML will not be logged.

I certainly did not issue sp_dboption for DB1 after I
created it.
After reading your post I was certain that 'select into' was
not enabled.

But now i checked it is enabled for DB1. Actually i was
doing some tempdb test earlier and had enabled 'select into'
for the model database. When i created DB1 'select into' was
inherited and enabled for DB1.

> I haven't played with dml_logging (yet), and I can't tell
> from your post if you've left out some of your details (eg
> , dboption settings) ... so fwiw ... from the IMDB Users
> Guide:
>
> re: database-level logging:
>
> "You must set the database’s select into option to on
for
> minimal logging to take effect."
>
> re: table-level logging:
>
> "You can execute minimally logged DML commands on a table
> only if the database has the select into database option
> turned on; otherwise, all DML commands are fully logged."
>
> re: session-level logging:
>
> "Minimal DML logging requires that the select into
> database option is turned on, which requires a database
> owner or sa_role privilege."
>
> All 3 levels of log control state the need to have a db
> configured with 'select into' enabled. And from the
> table-level comment it kinda sounds like the dml_logging
> setting is silently ignored if the database has not been
> configured with 'select into' enabled.
>
> Sooooo, did you enable 'select into' for DB1?
>
> jobless wrote:
> > download 15.5 for testing.
> >
> > - Create database DB1 (durability = full)
> > - Create table TB1
> > - Backed up database DB1
> > - inserted 1 row in TB1
> > - Backed up tran for DB1
> > - inserted 2nd row in TB1
> > - Backed up tran for DB1
> > - Altered table TB1 set dml_logging to minimum
> > - Backed up tran for DB1
> > - inserted 3rd row in TB1
> > - Backuped up tran for DB1
> >
> > When i restored DB1 and apply all logs, i still get all
> > 3 rows in TB1 ( what did dml_logging = minimal do in
> > this case ), i thought it will not let me dump tran but
> > it did, i thought if I restore all tran dumps i would
> > not have 3rd row but the transaction dump did have the
> > 3rd row. If recover upto last but 1 tran then I see only
> > 2 rows.
> > ASE_RDDB


Rob V [ Sybase ] Posted on 2011-01-05 22:14:30.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.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: alter table
References: <4d24d5fa.531e.1681692777@sybase.com>
In-Reply-To: <4d24d5fa.531e.1681692777@sybase.com>
Content-Type: multipart/alternative; boundary="------------020404020607050608060609"
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d24ed46@forums-1-dub>
Date: 5 Jan 2011 14:14:30 -0800
X-Trace: forums-1-dub 1294265670 10.22.241.152 (5 Jan 2011 14:14:30 -0800)
X-Original-Trace: 5 Jan 2011 14:14:30 -0800, vip152.sybase.com
Lines: 128
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29839
Article PK: 79068

On 05-Jan-2011 21:35, jobless wrote:

download 15.5 for testing. - Create database DB1 (durability = full) - Create table TB1 - Backed up database DB1 - inserted 1 row in TB1 - Backed up tran for DB1 - inserted 2nd row in TB1 - Backed up tran for DB1 - Altered table TB1 set dml_logging to minimum - Backed up tran for DB1 - inserted 3rd row in TB1 - Backuped up tran for DB1 When i restored DB1 and apply all logs, i still get all 3 rows in TB1 ( what did dml_logging = minimal do in this case ), i thought it will not let me dump tran but it did, i thought if I restore all tran dumps i would not have 3rd row but the transaction dump did have the 3rd row. If recover upto last but 1 tran then I see only 2 rows. ASE_RDDB

On line #2 you indicated you created DB1 with durability = full.
Minimally logged DML only applies to IMDB and RDDB database (i.e. having durability=no_recovery or =at_shutdown), not to full-durability databases.
Any attempt to use ML-DML in any database other than IMDB or RDDB will be silently ignored.
You can verify this by running 'set show_exec_info on': this will print the actual DML mode for every statement executed. I predict you'll see that full logging mode is used in your DB1.
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 -----------------------------------------------------------------


jobless Posted on 2011-01-06 00:11:17.0Z
Sender: 7931.4d23870f.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: alter table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d2508a5.5ff8.1681692777@sybase.com>
References: <4d24ed46@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Jan 2011 16:11:17 -0800
X-Trace: forums-1-dub 1294272677 10.22.241.41 (5 Jan 2011 16:11:17 -0800)
X-Original-Trace: 5 Jan 2011 16:11:17 -0800, 10.22.241.41
Lines: 60
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29841
Article PK: 79071

Thanks Rob, that confirms why I see 3 rows after restore.

> On 05-Jan-2011 21:35, jobless wrote:
> > download 15.5 for testing.
> >
> > - Create database DB1 (durability = full)
> > - Create table TB1
> > - Backed up database DB1
> > - inserted 1 row in TB1
> > - Backed up tran for DB1
> > - inserted 2nd row in TB1
> > - Backed up tran for DB1
> > - Altered table TB1 set dml_logging to minimum
> > - Backed up tran for DB1
> > - inserted 3rd row in TB1
> > - Backuped up tran for DB1
> >
> > When i restored DB1 and apply all logs, i still get all
> > 3 rows in TB1 ( what did dml_logging = minimal do in
> > this case ), i thought it will not let me dump tran but
> > it did, i thought if I restore all tran dumps i would
> > not have 3rd row but the transaction dump did have the
> > 3rd row. If recover upto last but 1 tran then I see only
> 2 rows. >
> > ASE_RDDB
>
> On line #2 you indicated you created DB1 with durability =
> full. Minimally logged DML only applies to IMDB and RDDB
> database (i.e. having durability=no_recovery or
> =at_shutdown), not to full-durability databases. Any
> attempt to use ML-DML in any database other than IMDB or
> RDDB will be silently ignored.
> You can verify this by running 'set show_exec_info on':
> this will print the actual DML mode for every statement
> executed. I predict you'll see that full logging mode is
> used in your DB1.
>
> 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
> ----------------------------------------------------------
> -------
>
>


Rob V [ Sybase ] Posted on 2011-01-06 00:25:34.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.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: alter table
References: <4d24ed46@forums-1-dub> <4d2508a5.5ff8.1681692777@sybase.com>
In-Reply-To: <4d2508a5.5ff8.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: <4d250bfe@forums-1-dub>
Date: 5 Jan 2011 16:25:34 -0800
X-Trace: forums-1-dub 1294273534 10.22.241.152 (5 Jan 2011 16:25:34 -0800)
X-Original-Trace: 5 Jan 2011 16:25:34 -0800, vip152.sybase.com
Lines: 117
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29842
Article PK: 79069

The thing with ML-DML is that you cannot force it: by setting the
dml_logging options you allow it to be used, but ASE may decide to do
full logging anyway in a number of cases.

R.

On 06-Jan-2011 01:11, jobless wrote:
> Thanks Rob, that confirms why I see 3 rows after restore.
>
>> On 05-Jan-2011 21:35, jobless wrote:
>>> download 15.5 for testing.
>>>
>>> - Create database DB1 (durability = full)
>>> - Create table TB1
>>> - Backed up database DB1
>>> - inserted 1 row in TB1
>>> - Backed up tran for DB1
>>> - inserted 2nd row in TB1
>>> - Backed up tran for DB1
>>> - Altered table TB1 set dml_logging to minimum
>>> - Backed up tran for DB1
>>> - inserted 3rd row in TB1
>>> - Backuped up tran for DB1
>>>
>>> When i restored DB1 and apply all logs, i still get all
>>> 3 rows in TB1 ( what did dml_logging = minimal do in
>>> this case ), i thought it will not let me dump tran but
>>> it did, i thought if I restore all tran dumps i would
>>> not have 3rd row but the transaction dump did have the
>>> 3rd row. If recover upto last but 1 tran then I see only
>> 2 rows.>
>>> ASE_RDDB
>> On line #2 you indicated you created DB1 with durability =
>> full. Minimally logged DML only applies to IMDB and RDDB
>> database (i.e. having durability=no_recovery or
>> =at_shutdown), not to full-durability databases. Any
>> attempt to use ML-DML in any database other than IMDB or
>> RDDB will be silently ignored.
>> You can verify this by running 'set show_exec_info on':
>> this will print the actual DML mode for every statement
>> executed. I predict you'll see that full logging mode is
>> used in your DB1.
>>
>> 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
>> ----------------------------------------------------------
>> -------
>>
>>

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

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