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

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

This is a multi-part message in MIME format.
--------------020404020607050608060609
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------020404020607050608060609
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    <tt>On 05-Jan-2011 21:35, jobless wrote:</tt>
    <blockquote cite="mid:4d24d5fa.531e.1681692777@sybase.com"
      type="cite">
      <pre wrap=""><tt>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
</tt></pre>
    </blockquote>
    <tt><br>
      On line #2 you indicated you created DB1 with durability = full.<br>
      Minimally logged DML only applies to IMDB and RDDB database (i.e.
      having durability=no_recovery or =at_shutdown), not to
      full-durability databases.<br>
      Any attempt to use ML-DML in any database other than IMDB or RDDB
      will be silently ignored. <br>
      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. <br>
    </tt>
    <pre class="moz-signature" cols="72"><tt>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 <a class="moz-txt-link-abbreviated" href="http://www.sypron.nl/shop">www.sypron.nl/shop</a>):
"Tips, Tricks &amp; Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

<a class="moz-txt-link-abbreviated" href="mailto:rob@NO.SPAM.sypron.nl">rob@NO.SPAM.sypron.nl</a> | <a class="moz-txt-link-abbreviated" href="http://www.sypron.nl">www.sypron.nl</a>  | Twitter: @rob_verschoor
Sypron B.V., The Netherlands  |  Chamber of Commerce 27138666
-----------------------------------------------------------------
</tt></pre>
  </body>
</html>

--------------020404020607050608060609--

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=92s 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 =3d 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 =3d 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

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

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