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.

ct_library - How do I read new rows in an unindexed table?

24 posts in General Discussion Last posting was on 2011-06-23 21:22:44.0Z
Jon Saxton Posted on 2011-06-08 17:47:47.0Z
Sender: 19ee.4defaf66.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: ct_library - How do I read new rows in an unindexed table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4defb5c3.1acf.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Jun 2011 10:47:47 -0700
X-Trace: forums-1-dub 1307555267 10.22.241.41 (8 Jun 2011 10:47:47 -0700)
X-Original-Trace: 8 Jun 2011 10:47:47 -0700, 10.22.241.41
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30236
Article PK: 72415

I am trying to read new stuff which gets added to the
sysaudits_0X tables in sybsecurity. The audit tables are
not indexed. Sybase just writes new stuff to the end until
the threshold procedure kicks in.

So I have a C++ program using ct_library calls to read audit
records. Let us say I read 200000 records and I reach the
end of the table. 5 seconds later another 30 records have
been written. I want to read those 30 new records without
having to read the first 200000 all over again.

I have read every piece of documentation on cursors that I
can find and none of them addresses this specific issue but
I do get the impression that a sensitive, read-only cursor
(SEMI_SENSITIVE in Sybase parlance) should do the trick but
I am not sure and I haven't found the correct sequence of
calls.

I expect that after doing all the initialisation I should be
able to read to the end of the result set, leave the cursor
open, then in a few seconds try again and get any new rows
which have been added, repeating this process at regular
intervals.

What I find is that the cursor gets into some state which
prevents it from being reused. My program detects the end
of the result set and then hangs around for a bit but when
it tries to do another read, the cursor is basically
scrogged. I have not been able to get it to process the
added rows.

So before I bore you all with the details of the calls and
results the first question is: "Is this an exercise in
futility?" If so then I really don't understand cursors at
all, even though this seems like a really worthwhile
application for one.

Right now I just want to know if I should persevere or
emigrate to somewhere nice and tropical.


Jon Saxton Posted on 2011-06-10 14:45:35.0Z
Sender: 35e1.4df0c4ec.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4df22e0f.5ab5.1681692777@sybase.com>
References: <4defb5c3.1acf.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 10 Jun 2011 07:45:35 -0700
X-Trace: forums-1-dub 1307717135 10.22.241.41 (10 Jun 2011 07:45:35 -0700)
X-Original-Trace: 10 Jun 2011 07:45:35 -0700, 10.22.241.41
Lines: 2
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30242
Article PK: 72422

Perhaps a simpler question is "How do I read the last N rows
in an unindexed table?" given that I know the value of N.


Rob V [ Sybase ] Posted on 2011-06-10 14:57:13.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.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
References: <4defb5c3.1acf.1681692777@sybase.com> <4df22e0f.5ab5.1681692777@sybase.com>
In-Reply-To: <4df22e0f.5ab5.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: <4df230c9$1@forums-1-dub>
Date: 10 Jun 2011 07:57:13 -0700
X-Trace: forums-1-dub 1307717833 10.22.241.152 (10 Jun 2011 07:57:13 -0700)
X-Original-Trace: 10 Jun 2011 07:57:13 -0700, vip152.sybase.com
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30243
Article PK: 72421


On 10-Jun-2011 16:45, Jon Saxton wrote:
> Perhaps a simpler question is "How do I read the last N rows
> in an unindexed table?" given that I know the value of N.

select top N * from table order by some_column desc

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


Jon Saxton Posted on 2011-06-14 12:52:04.0Z
Sender: 2bc9.4df74f8a.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4df75974.2d17.1681692777@sybase.com>
References: <4df230c9$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Jun 2011 05:52:04 -0700
X-Trace: forums-1-dub 1308055924 10.22.241.41 (14 Jun 2011 05:52:04 -0700)
X-Original-Trace: 14 Jun 2011 05:52:04 -0700, 10.22.241.41
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30249
Article PK: 72428


> On 10-Jun-2011 16:45, Jon Saxton wrote:
> > Perhaps a simpler question is "How do I read the last N
> > rows in an unindexed table?" given that I know the value
> of N. select top N * from table order by some_column desc
>
> 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
> ----------------------------------------------------------
> -------
>

Thanks for the suggestion, Rob. Seems simple enough, but I
have a question about it ...

Doesn't the "order by some_column desc" clause imply a sort
of the entire table?

The audit table is already in the correct order in that new
rows are appended to it as auditable events occur. I just
want the last few records, i.e. the ones which have been
appended since last time I looked.

Do I really want to tell Sybase to sort 200000 records just
to give me 5 when I know that the 5 I want are at the end of
the table?

It is this consideration which led me to consider using a
sensitive cursor. However if your solution does not imply a
sort then clearly it would be a lot easier.

As a bit of background, I have experimented using the
eventtime column which contains a monotonically increasing
value. When testing a query of the form "select ... where
eventtime > previous" I found that as the audit table grows,
there is a corresponding linear growth in Sybase's CPU time
until such time as a rollover occurs.


Rob V [ Sybase ] Posted on 2011-06-14 14:42:50.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.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
References: <4df230c9$1@forums-1-dub> <4df75974.2d17.1681692777@sybase.com>
In-Reply-To: <4df75974.2d17.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: <4df7736a@forums-1-dub>
Date: 14 Jun 2011 07:42:50 -0700
X-Trace: forums-1-dub 1308062570 10.22.241.152 (14 Jun 2011 07:42:50 -0700)
X-Original-Trace: 14 Jun 2011 07:42:50 -0700, vip152.sybase.com
Lines: 93
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30250
Article PK: 72429


On 14-Jun-2011 14:52, Jon Saxton wrote:
>> On 10-Jun-2011 16:45, Jon Saxton wrote:
>>> Perhaps a simpler question is "How do I read the last N
>>> rows in an unindexed table?" given that I know the value
>> of N. select top N * from table order by some_column desc
>>
>> 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
>> ----------------------------------------------------------
>> -------
>>
> Thanks for the suggestion, Rob. Seems simple enough, but I
> have a question about it ...
>
> Doesn't the "order by some_column desc" clause imply a sort
> of the entire table?
>
> The audit table is already in the correct order in that new
> rows are appended to it as auditable events occur. I just
> want the last few records, i.e. the ones which have been
> appended since last time I looked.
>
> Do I really want to tell Sybase to sort 200000 records just
> to give me 5 when I know that the 5 I want are at the end of
> the table?
>
> It is this consideration which led me to consider using a
> sensitive cursor. However if your solution does not imply a
> sort then clearly it would be a lot easier.
>
> As a bit of background, I have experimented using the
> eventtime column which contains a monotonically increasing
> value. When testing a query of the form "select ... where
> eventtime> previous" I found that as the audit table grows,
> there is a corresponding linear growth in Sybase's CPU time
> until such time as a rollover occurs.

First, let's observe that without an 'order by', there is no guarantee
on any ordering of the result set rows. The table's lockscheme and the
precise query plan may all make a difference without an 'order by' --
meaning that 'first' or 'last' may not be what you expect.
Now, assuming the rows always come out in the same order anyway, if you
wanted the first N rows, it would simply be a matter of "select top N *
from table". However there is no way to get only the last N rows, unless
(i) you use an order by or (ii) there is an index defining the ordering
you're having in mind *and* the index is created with descending key
ordering. In that case you could force that index to be used, i.e.
"select top N * from table (index your_index)". If the index exists but
has the default ascending key ordering, then you may be able to still
achieve this, but you'd have to force a descending scan though an
abstract query plan, and I don;t have that syntax handy right now.
What I'm trying to say here is that getting only the N last rows is not
as trivial as you may think.
As you indicate, you may be able to cut down on the amount of rows to be
ordered by specifying a relatively recent time in a where clause, i.e.:
"select top N * from table where timekey > 'some recent time' order by
somekey desc'

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


Jon Saxton Posted on 2011-06-14 19:35:11.0Z
Sender: 39fc.4df7b2f3.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4df7b7ef.3a87.1681692777@sybase.com>
References: <4df7736a@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Jun 2011 12:35:11 -0700
X-Trace: forums-1-dub 1308080111 10.22.241.41 (14 Jun 2011 12:35:11 -0700)
X-Original-Trace: 14 Jun 2011 12:35:11 -0700, 10.22.241.41
Lines: 127
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30254
Article PK: 72432


> On 14-Jun-2011 14:52, Jon Saxton wrote:
> >> On 10-Jun-2011 16:45, Jon Saxton wrote:
> >>> Perhaps a simpler question is "How do I read the last
> N >>> rows in an unindexed table?" given that I know the
> value >> of N. select top N * from table order by
> some_column desc >>
> >> 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
> >>
> ----------------------------------------------------------
> >> ------- >>
> > Thanks for the suggestion, Rob. Seems simple enough,
> > but I have a question about it ...
> >
> > Doesn't the "order by some_column desc" clause imply a
> > sort of the entire table?
> >
> > The audit table is already in the correct order in that
> > new rows are appended to it as auditable events occur.
> > I just want the last few records, i.e. the ones which
> > have been appended since last time I looked.
> >
> > Do I really want to tell Sybase to sort 200000 records
> > just to give me 5 when I know that the 5 I want are at
> > the end of the table?
> >
> > It is this consideration which led me to consider using
> > a sensitive cursor. However if your solution does not
> > imply a sort then clearly it would be a lot easier.
> >
> > As a bit of background, I have experimented using the
> > eventtime column which contains a monotonically
> > increasing value. When testing a query of the form
> > "select ... where eventtime> previous" I found that as
> > the audit table grows, there is a corresponding linear
> > growth in Sybase's CPU time until such time as a
> rollover occurs.
>
> First, let's observe that without an 'order by', there is
> no guarantee on any ordering of the result set rows. The
> table's lockscheme and the precise query plan may all
> make a difference without an 'order by' -- meaning that
> 'first' or 'last' may not be what you expect. Now,
> assuming the rows always come out in the same order anyway
> , if you wanted the first N rows, it would simply be a
> matter of "select top N * from table". However there is
> no way to get only the last N rows, unless (i) you use an
> order by or (ii) there is an index defining the ordering
> you're having in mind *and* the index is created with
> descending key ordering. In that case you could force
> that index to be used, i.e. "select top N * from table
> (index your_index)". If the index exists but has the
> default ascending key ordering, then you may be able to
> still achieve this, but you'd have to force a descending
> scan though an abstract query plan, and I don;t have that
> syntax handy right now. What I'm trying to say here is
> that getting only the N last rows is not as trivial as
> you may think. As you indicate, you may be able to cut
> down on the amount of rows to be ordered by specifying a
> relatively recent time in a where clause, i.e.: "select
> top N * from table where timekey > 'some recent time'
> order by somekey desc'
>
> 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
> ----------------------------------------------------------

Thanks for all the feedback, Rob.

I think we're getting to the point of saying that it is not
possible to do what I want.

The sybsecurity audit tables are not indexed. New rows are
simply added to the end of the table as audit events occur.

I don't really care what order I read the rows. I have
other robust code which deals with ordering issues. I can
just throw rows at it and everything gets sorted out. My
issue is that if I know that 12 rows have been added to the
end of the table then I just want to fetch those 12 rows
with the minimum possible impact on Sybase. From
performance testing it does appear that doing a select for
eventtime > something causes Sybase to read the entire table
every time looking for rows which satisfy the condition. It
doesn't know that they are all at the end of the table even
though that is where Sybase put them.

Because these are Sybase-generated tables I do not have the
option of indexing them.

So far I have not get given up entirely on the idea of a
scrollable semi-sensitive cursor but I am certainly not too
optimistic.


"Mark A. Parsons" <iron_horse Posted on 2011-06-14 21:18:57.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
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: ct_library - How do I read new rows in an unindexed table?
References: <4df7736a@forums-1-dub> <4df7b7ef.3a87.1681692777@sybase.com>
In-Reply-To: <4df7b7ef.3a87.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: <4df7d041$1@forums-1-dub>
Date: 14 Jun 2011 14:18:57 -0700
X-Trace: forums-1-dub 1308086337 10.22.241.152 (14 Jun 2011 14:18:57 -0700)
X-Original-Trace: 14 Jun 2011 14:18:57 -0700, vip152.sybase.com
Lines: 139
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30255
Article PK: 72430

I just code in simple T-SQL, ie, no ct-lib. Sooooo, fwiw ...

Well, first a question ... have you tried Bret's suggestion of tacking an adhoc row onto the end of the sysaudits_01
table just before you go looking for new records? The general idea being to create a unique adhoc record,
perform/continue your cursor scan of sysaudits_01, halt when you run into your unique adhoc record, sleep awhile, then
repeat.

Here's a simple T-SQL script that (I believe) does what you've been asking for:

============================
use sybsecurity
go

declare audit_cur cursor
for
select event, -- modify per your requirement
spid,
eventtime,
extrainfo -- this is where my unique string will go
from sysaudits_01
for read only
go

declare @event smallint,
@spid smallint,
@eventtime datetime,
@extrainfo varchar(255),
@halt_flag varchar(50)

-- generate my unique string; will show up in sysaudits_0x.extrainfo

select @halt_flag = 'halt at ' + convert(varchar,getdate(),109)

-- dump the unique string onto sysaudits_0x

exec sp_addauditrecord @halt_flag

-- the audit process may be a little slow to add to sysaudits_0x
-- so we need to wait a sec ... or two ... ymmv

waitfor delay "00:00:01"

open audit_cur

fetch audit_cur into @event,
@spid,
@eventtime,
@extrainfo

while @@sqlstatus = 0
begin
print '@event = %1! : @spid = %2! : @eventtime = %3! : @extrainfo = %4!',
@event, @spid, @eventtime, @extrainfo

if @extrainfo = @halt_flag -- we're at/near the end of the audit table
-- so let's stop and wait for awhile
begin
print 'waiting ...'
waitfor delay "00:00:10" -- modify to your liking

-- generate a new unique string

select @halt_flag = 'halt at ' + convert(varchar,getdate(),109)

-- dump the new unique string onto sysaudits_0x

exec sp_addauditrecord @halt_flag

-- wait for the auditing system to actually add my unique
-- string to sysaudits_0x

waitfor delay "00:00:01"
end
else
begin
print 'here is where you could process your newly acquired audit record'
end

fetch audit_cur into @event,
@spid,
@eventtime,
@extrainfo
end

close audit_cur
go

deallocate cursor audit_cur
go
============================

NOTES:

1 - you'll need to run "sp_audit 'adhoc',...." to allow for adding adhoc rows via sp_addauditrecord

2 - sp_addauditrecord allows you to designate up to 6 different attributes of an audit record so there's plenty of room
for creativity in building a unique adhoc audit record; for this example I went with setting a single attribute, namely
the 'extrainfo' column

3 - you'll want to experiment with the amount of time you wait after running sp_addauditrecord; objective is to make
sure you don't race ahead to read sysaudits_01 before your halt flag has been inserted by the auditing process; if
you're not too concerned with how quickly you read rows you could consider doing the sp_addauditrecord before the 10
second wait (ie, just do the single wait once you're inside the loop)

4 - I'll leave it up to you to add the logic for checking to see if/when you need to switch to a new sysaudits_0x table
(eg, a threshold procedure forces the audit process to switch to a different sysaudits_0x table)

5 - to support the ability to restart your process where you left off you may want to consider keeping track of the last
halt flag you successfully read from sysaudits_0x; you could then add logic to the above code which initially scans the
table until it finds the last successfully read halt flag (objective being to skip over - not process - rows you're
already processed)

6 - I'm assuming this T-SQL cursor code can be emulated in ct-library routine ... ?

On 06/14/2011 15:35, Jon Saxton wrote:
> I think we're getting to the point of saying that it is not
> possible to do what I want.
> The sybsecurity audit tables are not indexed. New rows are
> simply added to the end of the table as audit events occur.
>
> I don't really care what order I read the rows. I have
> other robust code which deals with ordering issues. I can
> just throw rows at it and everything gets sorted out. My
> issue is that if I know that 12 rows have been added to the
> end of the table then I just want to fetch those 12 rows
> with the minimum possible impact on Sybase. From
> performance testing it does appear that doing a select for
> eventtime> something causes Sybase to read the entire table
> every time looking for rows which satisfy the condition. It
> doesn't know that they are all at the end of the table even
> though that is where Sybase put them.
>
> Because these are Sybase-generated tables I do not have the
> option of indexing them.
>
> So far I have not get given up entirely on the idea of a
> scrollable semi-sensitive cursor but I am certainly not too
> optimistic.


Jon Saxton Posted on 2011-06-15 15:35:20.0Z
Sender: 62e7.4df8bb25.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4df8d138.6601.1681692777@sybase.com>
References: <4df7d041$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Jun 2011 08:35:20 -0700
X-Trace: forums-1-dub 1308152120 10.22.241.41 (15 Jun 2011 08:35:20 -0700)
X-Original-Trace: 15 Jun 2011 08:35:20 -0700, 10.22.241.41
Lines: 211
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30263
Article PK: 72441

Mark:

I probably don't need to go to the trouble of adding a
sentinel row. I can always keep track of the number of rows
that I have processed and I can discover the current size of
the audit table so I know exactly how many rows to fetch
without running off the end. I am going to explore that
avenue.

Notwithstanding the above, I find your scripts instructive.
Thanks for that.

I can guarantee that there will be at least two audit tables
and I believe that handling a rollover is pretty trivial;
when I detect a table change I close the cursor and open a
new one. (It is actually not quite so simple because I have
to read the last few rows of the old table ... but that is
the main idea.)

So my code containing the fetch loop is always called with
the current table and the number of rows to be read. The
big yet-to-be-tested question is whether or not the cursor
will see rows added between invocations of the
aforementioned fetch loop. A second question is whether or
not the cursor solution will prove to be any better than the
simple queries suggested by Rob and others.

Of course I am not expecting this to work on any Sybase
earlier than 15.x and it will take me a day or two to write
the code.

Meanwhile I just thought of another possibility. Even if
the new rows are not visible to the cursor (contrary to my
reading of the documentation) then if the result set is not
materialized too early it may be possible to do an explicit
"seek" to the row that I want. Again, my reading of the
documentation suggests that this should work, even if it
means opening the cursor afresh each cycle. (Of course it
would be disastrous if the result set were materialized over
the whole table!)

Any thoughts would be appreciated.

> I just code in simple T-SQL, ie, no ct-lib. Sooooo, fwiw
> ...
>
> Well, first a question ... have you tried Bret's
> suggestion of tacking an adhoc row onto the end of the
> sysaudits_01 table just before you go looking for new
> records? The general idea being to create a unique adhoc
> record, perform/continue your cursor scan of sysaudits_01
> , halt when you run into your unique adhoc record, sleep
> awhile, then repeat.
>
> Here's a simple T-SQL script that (I believe) does what
> you've been asking for:
>
> ============================
> use sybsecurity
> go
>
> declare audit_cur cursor
> for
> select event, -- modify per your requirement
> spid,
> eventtime,
> extrainfo -- this is where my unique
> string will go from sysaudits_01
> for read only
> go
>
> declare @event smallint,
> @spid smallint,
> @eventtime datetime,
> @extrainfo varchar(255),
> @halt_flag varchar(50)
>
> -- generate my unique string; will show up in
> sysaudits_0x.extrainfo
>
> select @halt_flag = 'halt at ' + convert(varchar
> ,getdate(),109)
>
> -- dump the unique string onto sysaudits_0x
>
> exec sp_addauditrecord @halt_flag
>
> -- the audit process may be a little slow to add to
> sysaudits_0x -- so we need to wait a sec ... or two ...
> ymmv
>
> waitfor delay "00:00:01"
>
> open audit_cur
>
> fetch audit_cur into @event,
> @spid,
> @eventtime,
> @extrainfo
>
> while @@sqlstatus = 0
> begin
> print '@event = %1! : @spid = %2! : @eventtime =
> %3! : @extrainfo = %4!',
> @event, @spid, @eventtime, @extrainfo
>
> if @extrainfo = @halt_flag -- we're at/near the
> end of the audit table
> -- so let's stop and
> wait for awhile
> begin
> print 'waiting ...'
> waitfor delay "00:00:10" -- modify
> to your liking
>
> -- generate a new unique string
>
> select @halt_flag = 'halt at ' +
> convert(varchar,getdate(),109)
>
> -- dump the new unique string onto
> sysaudits_0x
>
> exec sp_addauditrecord @halt_flag
>
> -- wait for the auditing system to
> actually add my unique
> -- string to sysaudits_0x
>
> waitfor delay "00:00:01"
> end
> else
> begin
> print 'here is where you could process
> your newly acquired audit record'
> end
>
> fetch audit_cur into @event,
> @spid,
> @eventtime,
> @extrainfo
> end
>
> close audit_cur
> go
>
> deallocate cursor audit_cur
> go
> ============================
>
> NOTES:
>
> 1 - you'll need to run "sp_audit 'adhoc',...." to allow
> for adding adhoc rows via sp_addauditrecord
>
> 2 - sp_addauditrecord allows you to designate up to 6
> different attributes of an audit record so there's plenty
> of room for creativity in building a unique adhoc audit
> record; for this example I went with setting a single
> attribute, namely the 'extrainfo' column
>
> 3 - you'll want to experiment with the amount of time you
> wait after running sp_addauditrecord; objective is to make
> sure you don't race ahead to read sysaudits_01 before your
> halt flag has been inserted by the auditing process; if
> you're not too concerned with how quickly you read rows
> you could consider doing the sp_addauditrecord before the
> 10 second wait (ie, just do the single wait once you're
> inside the loop)
>
> 4 - I'll leave it up to you to add the logic for checking
> to see if/when you need to switch to a new sysaudits_0x
> table (eg, a threshold procedure forces the audit process
> to switch to a different sysaudits_0x table)
>
> 5 - to support the ability to restart your process where
> you left off you may want to consider keeping track of the
> last halt flag you successfully read from sysaudits_0x;
> you could then add logic to the above code which initially
> scans the table until it finds the last successfully read
> halt flag (objective being to skip over - not process -
> rows you're already processed)
>
> 6 - I'm assuming this T-SQL cursor code can be emulated in
> ct-library routine ... ?
>
>
> On 06/14/2011 15:35, Jon Saxton wrote:
> > I think we're getting to the point of saying that it is
> > not possible to do what I want.
> > The sybsecurity audit tables are not indexed. New rows
> > are simply added to the end of the table as audit events
> occur. >
> > I don't really care what order I read the rows. I have
> > other robust code which deals with ordering issues. I
> > can just throw rows at it and everything gets sorted
> > out. My issue is that if I know that 12 rows have been
> > added to the end of the table then I just want to fetch
> > those 12 rows with the minimum possible impact on
> > Sybase. From performance testing it does appear that
> > doing a select for eventtime> something causes Sybase
> > to read the entire table every time looking for rows
> > which satisfy the condition. It doesn't know that they
> > are all at the end of the table even though that is
> where Sybase put them. >
> > Because these are Sybase-generated tables I do not have
> > the option of indexing them.
> >
> > So far I have not get given up entirely on the idea of a
> > scrollable semi-sensitive cursor but I am certainly not
> > too optimistic.


"Mark A. Parsons" <iron_horse Posted on 2011-06-15 20:41:26.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
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: ct_library - How do I read new rows in an unindexed table?
References: <4df7d041$1@forums-1-dub> <4df8d138.6601.1681692777@sybase.com>
In-Reply-To: <4df8d138.6601.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: <4df918f6$1@forums-1-dub>
Date: 15 Jun 2011 13:41:26 -0700
X-Trace: forums-1-dub 1308170486 10.22.241.152 (15 Jun 2011 13:41:26 -0700)
X-Original-Trace: 15 Jun 2011 13:41:26 -0700, vip152.sybase.com
Lines: 273
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30265
Article PK: 72442


On 06/15/2011 11:35, Jon Saxton wrote:
> Mark:
>
> I probably don't need to go to the trouble of adding a
> sentinel row. I can always keep track of the number of rows
> that I have processed and I can discover the current size of
> the audit table so I know exactly how many rows to fetch
> without running off the end. I am going to explore that
> avenue.

How are you determining the number of rows to fetch? systabstats, running a 'select count(*)', something else?

systabstats isn't guaranteed to be 100% accurate so there's a good chance you could find your counts off a bit from time
to time.

Obviously (?) the 'select count(*)' is going to incur a good bit of overhead for constant rescanning of the audit table.

> Notwithstanding the above, I find your scripts instructive.
> Thanks for that.
>
> I can guarantee that there will be at least two audit tables
> and I believe that handling a rollover is pretty trivial;
> when I detect a table change I close the cursor and open a
> new one. (It is actually not quite so simple because I have
> to read the last few rows of the old table ... but that is
> the main idea.)

Yes, keeping track of the 'current audit table' value in syscurconfigs would be trivial. In my example that check could
be done each time before/after the 10 second sleep period. [NOTE: I'm wondering if the cursor lock towards the tail
end of the audit table would cause any problems for the auditing process ... probably depends on what the threshold
attempts to do with the old table after performing the switch.]

Reading the last few rows of the old table shouldn't be too hard ... just read until you run out of rows (ie, hit end of
the table). At that point you're out of the fetch loop and you can close/reopen the cursor on the new table.

> So my code containing the fetch loop is always called with
> the current table and the number of rows to be read. The
> big yet-to-be-tested question is whether or not the cursor
> will see rows added between invocations of the
> aforementioned fetch loop. A second question is whether or
> not the cursor solution will prove to be any better than the
> simple queries suggested by Rob and others.

Why are you passing a 'number of rows to be read' to the loop? This is where the sentinel record comes in handy ... no
need to know how many rows to read ... just read until you hit the sentinel and then sleep for awhile.

If you're pre-calculating how many rows to read then you're quite likely to run into situations where you fall behind
(ie, not reading enough rows) or possibly miss some rows ... *shrug* ... it really depends on the technical details of
how you implement your solution. Again, the sentinel record should alleviate this issue as long as you give enough time
for the sentinel record (added via sp_addauditrecord) time to make its way into the audit table.

As for whether a cursor method is better than some of the other suggestions ... *shrug* ... depends on how you plan to
implement the cursor. My example code opens, and leaves open, a single cursor that performs just one (continuous) table
scan of the audit table so the overhead for performing IOs is minimal ... certainly no need to constantly rescan the
audit table. [NOTE: Obviously if you need to restart the process you would incur a table scan to get back to where you
left off.]

-----

Your comments sound like you're planning on exiting the fetch loop after you've read some number of rows.

What are you doing with your cursor between your fetch loops? Are you closing/re-opening the cursor each time you want
to read from the audit table? And if so, how do you plan on reading the last X records without having to rescan the
audit table each time you open the cursor? (Or am I restating your case from the OP?)

If you are closing/re-opening your cursor ... why? Why not leave it open?

> Of course I am not expecting this to work on any Sybase
> earlier than 15.x and it will take me a day or two to write
> the code.

Why do you think you'll be unable to get this to work in pre-15 dataservers? If you're thinking about (semi)sensitive
cursors and all the schtuff associated with them ... you may be over thinking a bit; the example T-SQL code I provided
should work just fine in a 12.5.x dataserver, too.

Looking back over your previous posts it's not clear (to me) if you've actually tried a cursor method or not ... ? If
you have tried a cursor method, what were the results/issues/problems?

If you're having problems getting a ct-lib solution to work properly, have you considered putting the code (eg,
something similar to my T-SQL sample) into a stored proc and just calling the stored proc from your application?

> Meanwhile I just thought of another possibility. Even if
> the new rows are not visible to the cursor (contrary to my
> reading of the documentation) then if the result set is not
> materialized too early it may be possible to do an explicit
> "seek" to the row that I want. Again, my reading of the
> documentation suggests that this should work, even if it
> means opening the cursor afresh each cycle. (Of course it
> would be disastrous if the result set were materialized over
> the whole table!)

In testing my example the new rows were visible to the cursor. In the example of adding the sentinel record I had to
wait a small bit for the record to show up, but it did show up and it was visible by my cursor.

Again, I think you may be over thinking this if you're looking at materialized views and having to 'seek' to the row you
want. The example I posted just sits at the end of the actual audit table waiting for new rows to be inserted ... using
the sentinel record to keep from reading off the end of the table.

What attempts at coding have you made so far, and what actual issues have you run into?


> Any thoughts would be appreciated.
>
>> I just code in simple T-SQL, ie, no ct-lib. Sooooo, fwiw
>> ...
>>
>> Well, first a question ... have you tried Bret's
>> suggestion of tacking an adhoc row onto the end of the
>> sysaudits_01 table just before you go looking for new
>> records? The general idea being to create a unique adhoc
>> record, perform/continue your cursor scan of sysaudits_01
>> , halt when you run into your unique adhoc record, sleep
>> awhile, then repeat.
>>
>> Here's a simple T-SQL script that (I believe) does what
>> you've been asking for:
>>
>> ============================
>> use sybsecurity
>> go
>>
>> declare audit_cur cursor
>> for
>> select event, -- modify per your requirement
>> spid,
>> eventtime,
>> extrainfo -- this is where my unique
>> string will go from sysaudits_01
>> for read only
>> go
>>
>> declare @event smallint,
>> @spid smallint,
>> @eventtime datetime,
>> @extrainfo varchar(255),
>> @halt_flag varchar(50)
>>
>> -- generate my unique string; will show up in
>> sysaudits_0x.extrainfo
>>
>> select @halt_flag = 'halt at ' + convert(varchar
>> ,getdate(),109)
>>
>> -- dump the unique string onto sysaudits_0x
>>
>> exec sp_addauditrecord @halt_flag
>>
>> -- the audit process may be a little slow to add to
>> sysaudits_0x -- so we need to wait a sec ... or two ...
>> ymmv
>>
>> waitfor delay "00:00:01"
>>
>> open audit_cur
>>
>> fetch audit_cur into @event,
>> @spid,
>> @eventtime,
>> @extrainfo
>>
>> while @@sqlstatus = 0
>> begin
>> print '@event = %1! : @spid = %2! : @eventtime =
>> %3! : @extrainfo = %4!',
>> @event, @spid, @eventtime, @extrainfo
>>
>> if @extrainfo = @halt_flag -- we're at/near the
>> end of the audit table
>> -- so let's stop and
>> wait for awhile
>> begin
>> print 'waiting ...'
>> waitfor delay "00:00:10" -- modify
>> to your liking
>>
>> -- generate a new unique string
>>
>> select @halt_flag = 'halt at ' +
>> convert(varchar,getdate(),109)
>>
>> -- dump the new unique string onto
>> sysaudits_0x
>>
>> exec sp_addauditrecord @halt_flag
>>
>> -- wait for the auditing system to
>> actually add my unique
>> -- string to sysaudits_0x
>>
>> waitfor delay "00:00:01"
>> end
>> else
>> begin
>> print 'here is where you could process
>> your newly acquired audit record'
>> end
>>
>> fetch audit_cur into @event,
>> @spid,
>> @eventtime,
>> @extrainfo
>> end
>>
>> close audit_cur
>> go
>>
>> deallocate cursor audit_cur
>> go
>> ============================
>>
>> NOTES:
>>
>> 1 - you'll need to run "sp_audit 'adhoc',...." to allow
>> for adding adhoc rows via sp_addauditrecord
>>
>> 2 - sp_addauditrecord allows you to designate up to 6
>> different attributes of an audit record so there's plenty
>> of room for creativity in building a unique adhoc audit
>> record; for this example I went with setting a single
>> attribute, namely the 'extrainfo' column
>>
>> 3 - you'll want to experiment with the amount of time you
>> wait after running sp_addauditrecord; objective is to make
>> sure you don't race ahead to read sysaudits_01 before your
>> halt flag has been inserted by the auditing process; if
>> you're not too concerned with how quickly you read rows
>> you could consider doing the sp_addauditrecord before the
>> 10 second wait (ie, just do the single wait once you're
>> inside the loop)
>>
>> 4 - I'll leave it up to you to add the logic for checking
>> to see if/when you need to switch to a new sysaudits_0x
>> table (eg, a threshold procedure forces the audit process
>> to switch to a different sysaudits_0x table)
>>
>> 5 - to support the ability to restart your process where
>> you left off you may want to consider keeping track of the
>> last halt flag you successfully read from sysaudits_0x;
>> you could then add logic to the above code which initially
>> scans the table until it finds the last successfully read
>> halt flag (objective being to skip over - not process -
>> rows you're already processed)
>>
>> 6 - I'm assuming this T-SQL cursor code can be emulated in
>> ct-library routine ... ?
>>
>>
>> On 06/14/2011 15:35, Jon Saxton wrote:
>>> I think we're getting to the point of saying that it is
>>> not possible to do what I want.
>>> The sybsecurity audit tables are not indexed. New rows
>>> are simply added to the end of the table as audit events
>> occur.>
>>> I don't really care what order I read the rows. I have
>>> other robust code which deals with ordering issues. I
>>> can just throw rows at it and everything gets sorted
>>> out. My issue is that if I know that 12 rows have been
>>> added to the end of the table then I just want to fetch
>>> those 12 rows with the minimum possible impact on
>>> Sybase. From performance testing it does appear that
>>> doing a select for eventtime> something causes Sybase
>>> to read the entire table every time looking for rows
>>> which satisfy the condition. It doesn't know that they
>>> are all at the end of the table even though that is
>> where Sybase put them.>
>>> Because these are Sybase-generated tables I do not have
>>> the option of indexing them.
>>>
>>> So far I have not get given up entirely on the idea of a
>>> scrollable semi-sensitive cursor but I am certainly not
>>> too optimistic.


Jon Saxton Posted on 2011-06-16 15:32:14.0Z
Sender: 689a.4df8e434.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dfa21fe.1fce.1681692777@sybase.com>
References: <4df918f6$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Jun 2011 08:32:14 -0700
X-Trace: forums-1-dub 1308238334 10.22.241.41 (16 Jun 2011 08:32:14 -0700)
X-Original-Trace: 16 Jun 2011 08:32:14 -0700, 10.22.241.41
Lines: 437
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30276
Article PK: 72454

Such interesting issues you raise! Certainly exposes gaps
in my understanding of the technology. (That is a good
thing, by the way.)

> On 06/15/2011 11:35, Jon Saxton wrote:
> > Mark:
> >
> > I probably don't need to go to the trouble of adding a
> > sentinel row. I can always keep track of the number of
> > rows that I have processed and I can discover the
> > current size of the audit table so I know exactly how
> > many rows to fetch without running off the end. I am
> > going to explore that avenue.
>
> How are you determining the number of rows to fetch?
> systabstats, running a 'select count(*)', something else?
>
> systabstats isn't guaranteed to be 100% accurate so
> there's a good chance you could find your counts off a bit
> from time to time.
>
> Obviously (?) the 'select count(*)' is going to incur a
> good bit of overhead for constant rescanning of the audit
> table.

That is a bit alarming. I would not have expected that. I
assumed that the overall row count is a first class
attribute of a table and the database would be able to
answer an unqualified "select count(*) from sysaudits_0x"
query without having to go to the trouble of scanning the
table. It is exactly the situation I want to avoid.

An analogy would be the C and C++ treatment of strings. C
strings are just a zero-terminated sequence of characters
and to find the length of a string the sizeof() function has
to step through the characters counting them until it finds
the zero at the end. In C++ the std::string object has the
length as an attribute so calling the size() method on a
string object returns the length immediately without
counting. That is the way I would expect a database to
behave for a simple query "tell me how many rows are in this
table". Now I am inferring from your remarks that I am
mistaken, in which case your sentinel record suggestion
would be much more attractive. I would have to think about
boundary conditions such as the effect of writing to a
sysaudits_0x table at the time of a rollover.

Waiting for the sentinel row to appear in the table would
not be an issue. Suppose for example that I was polling the
table every 5 seconds. I could wait 4 seconds, write the
sentinel, then wait another second before doing the fetch.
Obviously some more rows may be written in that intervening
second but I can pick those up on the next cycle.

I don't know how to use systabstats to get the row count for
the audit table of interest. I know it can be had with
sp_spaceused and I presume it gets its information from the
same source. Is that a cheaper query than select count(*)?

> > Notwithstanding the above, I find your scripts
> > instructive. Thanks for that.
> >
> > I can guarantee that there will be at least two audit
> > tables and I believe that handling a rollover is pretty
> > trivial; when I detect a table change I close the cursor
> > and open a new one. (It is actually not quite so simple
> > because I have to read the last few rows of the old
> > table ... but that is the main idea.)
>
> Yes, keeping track of the 'current audit table' value in
> syscurconfigs would be trivial. In my example that check
> could be done each time before/after the 10 second sleep
> period. [NOTE: I'm wondering if the cursor lock towards
> the tail end of the audit table would cause any problems
> for the auditing process ... probably depends on what the
> threshold attempts to do with the old table after
> performing the switch.]

Ideally the threshold procedure does not truncate the old
table. Of course I cannot control that but it is probably
the case and is certainly true of the sample threshold
procedure that I provide.

> Reading the last few rows of the old table shouldn't be
> too hard ... just read until you run out of rows (ie, hit
> end of the table). At that point you're out of the fetch
> loop and you can close/reopen the cursor on the new table.
>
> > So my code containing the fetch loop is always called
> > with the current table and the number of rows to be
> > read. The big yet-to-be-tested question is whether or
> > not the cursor will see rows added between invocations
> > of the aforementioned fetch loop. A second question is
> > whether or not the cursor solution will prove to be any
> > better than the simple queries suggested by Rob and
> others.
>
> Why are you passing a 'number of rows to be read' to the
> loop? This is where the sentinel record comes in handy
> .. no need to know how many rows to read ... just read
> until you hit the sentinel and then sleep for awhile.
>
> If you're pre-calculating how many rows to read then
> you're quite likely to run into situations where you fall
> behind (ie, not reading enough rows) or possibly miss
> some rows ... *shrug* ... it really depends on the
> technical details of how you implement your solution.
> Again, the sentinel record should alleviate this issue as
> long as you give enough time for the sentinel record
> (added via sp_addauditrecord) time to make its way into
> the audit table.

I have dealt with these matters a few paragraphs above. I
can afford to wait until the next fetch cycle.

> As for whether a cursor method is better than some of the
> other suggestions ... *shrug* ... depends on how you plan
> to implement the cursor. My example code opens, and
> leaves open, a single cursor that performs just one
> (continuous) table scan of the audit table so the
> overhead for performing IOs is minimal ... certainly no
> need to constantly rescan the audit table. [NOTE:
> Obviously if you need to restart the process you would
> incur a table scan to get back to where you left off.]

That is exactly the way that my preliminary ct_lib code
works. It reads all the new rows, pokes them into an
accumulator object which handles out-of-sequence and
incomplete events and then returns, leaving the cursor open.
The mainline program processes the complete events
delivered by the accumulator then goes to sleep for a bit.
On re-entry, the fetch routine resumes from where it left
off.

> -----
>
> Your comments sound like you're planning on exiting the
> fetch loop after you've read some number of rows.
>
> What are you doing with your cursor between your fetch
> loops? Are you closing/re-opening the cursor each time
> you want to read from the audit table? And if so, how do
> you plan on reading the last X records without having to
> rescan the audit table each time you open the cursor?
> (Or am I restating your case from the OP?)
>
> If you are closing/re-opening your cursor ... why? Why
> not leave it open?

I have answered this too. Cursor only gets closed on a
table switch or on some sort of error.

> > Of course I am not expecting this to work on any Sybase
> > earlier than 15.x and it will take me a day or two to
> > write the code.
>
> Why do you think you'll be unable to get this to work in
> pre-15 dataservers? If you're thinking about
> (semi)sensitive cursors and all the schtuff associated
> with them ... you may be over thinking a bit; the example
> T-SQL code I provided should work just fine in a 12.5.x
> dataserver, too.

You could be right. However the code does not have to work
on pre-15.x Sybase. From reading the documentation on
ct_cursor and its attendant functions it seemed to me that
the ideal cursor type was a scrollable semi-sensitive one.
IIRC the result set does not materialize until the
ct_scroll_fetch() is called so if I am starting with 200005
rows in the audit table I can do a fetch at absolute
position 200000 and read the last 5 records without getting
all those early rows written to some work table. (This is
only an issue when the program starts.) Of course I could
be mistaken in my interpretation but scrollabe cursors are
not supported in earlier versions of the library and indeed
to not work against a 12.5 Sybase.

> Looking back over your previous posts it's not clear (to
> me) if you've actually tried a cursor method or not ... ?
> If you have tried a cursor method, what were the
> results/issues/problems?

My expertise is in C++, not databases as such. I had done
some preliminary work, at least to the point of declaring a
cursor, opening it and fetching a few rows. Then I decided
I didn't know enough about what I was doing and went away to
read the documentation and ask questions here about the bits
which I didn't understand. And of course I considered the
possibility that there may be much better ways of doing
things than what I had in mind so I wanted to pose that
question to the people who are dealing with these issues all
the time. And a most instructive discussion it has been, at
least for me.

> If you're having problems getting a ct-lib solution to
> work properly, have you considered putting the code (eg,
> something similar to my T-SQL sample) into a stored proc
> and just calling the stored proc from your application?

The only problems I had were getting the call sequencing
correct and dealing with the return codes. An early
difficulty was that sometimes a library call just returns an
error code saying "it didn't work" with no extra
information. That was perplexing. However given that I
already had the code to declare and open the cursor it was
not too difficult to write a crude fetch loop. It may be
fragile in terms of handling all the possible outcomes but
as a proof of concept it was fine. All I wanted was to see
at least one new row retrieved from the second entry to the
fetch routine and I was happy.

> > Meanwhile I just thought of another possibility. Even
> > if the new rows are not visible to the cursor (contrary
> > to my reading of the documentation) then if the result
> > set is not materialized too early it may be possible to
> > do an explicit "seek" to the row that I want. Again, my
> > reading of the documentation suggests that this should
> > work, even if it means opening the cursor afresh each
> > cycle. (Of course it would be disastrous if the result
> > set were materialized over the whole table!)
>
> In testing my example the new rows were visible to the
> cursor. In the example of adding the sentinel record I
> had to wait a small bit for the record to show up, but it
> did show up and it was visible by my cursor.
>
> Again, I think you may be over thinking this if you're
> looking at materialized views and having to 'seek' to the
> row you want. The example I posted just sits at the end
> of the actual audit table waiting for new rows to be
> inserted ... using the sentinel record to keep from
> reading off the end of the table.
>
> What attempts at coding have you made so far, and what
> actual issues have you run into?

Development state is as briefly mentioned above. I have the
mainline loop in place and working. It calls the cursor
fetch object, processes any complete audit events which are
returned, checks for an audit table switch and then goes to
sleep for a while. The accumulator object is well tested.
The only bit I am working on now is the cursor code which
takes rows from the table and pokes them into the
accumulator.

This whole line of investigation arose from the observation
that reading the audit able with a query of the form "select
[list of columns] from sysaudits_0x where eventtime >
something" had an ever-increasing impact on Sybase CPU
utilization as the audit table grew. It is probably not too
bad if the audit segments are 10 Mb or less but some people
insist on having much larger audit tables and then complain
about performance.

For what it is worth, I have a similar issue with auditing
on Oracle but I can get away with the simplistic approach
because the number of audit records generated in a given
period is much lower and I am allowed to truncate the table
whenever I want, e.g. every 5000 rows.

> > Any thoughts would be appreciated.

You have given me plenty to think about. I am coming around
to the idea of writing a sentinel row, especially if it
turns out that "select count(*)" is expensive.

Thanks so much for all the input.


> >> I just code in simple T-SQL, ie, no ct-lib. Sooooo,
> fwiw >> ...
> >>
> >> Well, first a question ... have you tried Bret's
> >> suggestion of tacking an adhoc row onto the end of the
> >> sysaudits_01 table just before you go looking for new
> >> records? The general idea being to create a unique
> adhoc >> record, perform/continue your cursor scan of
> sysaudits_01 >> , halt when you run into your unique adhoc
> record, sleep >> awhile, then repeat.
> >>
> >> Here's a simple T-SQL script that (I believe) does what
> >> you've been asking for:
> >>
> >> ============================
> >> use sybsecurity
> >> go
> >>
> >> declare audit_cur cursor
> >> for
> >> select event, -- modify per your requirement
> >> spid,
> >> eventtime,
> >> extrainfo -- this is where my unique
> >> string will go from sysaudits_01
> >> for read only
> >> go
> >>
> >> declare @event smallint,
> >> @spid smallint,
> >> @eventtime datetime,
> >> @extrainfo varchar(255),
> >> @halt_flag varchar(50)
> >>
> >> -- generate my unique string; will show up in
> >> sysaudits_0x.extrainfo
> >>
> >> select @halt_flag = 'halt at ' + convert(varchar
> >> ,getdate(),109)
> >>
> >> -- dump the unique string onto sysaudits_0x
> >>
> >> exec sp_addauditrecord @halt_flag
> >>
> >> -- the audit process may be a little slow to add to
> >> sysaudits_0x -- so we need to wait a sec ... or two ...
> >> ymmv
> >>
> >> waitfor delay "00:00:01"
> >>
> >> open audit_cur
> >>
> >> fetch audit_cur into @event,
> >> @spid,
> >> @eventtime,
> >> @extrainfo
> >>
> >> while @@sqlstatus = 0
> >> begin
> >> print '@event = %1! : @spid = %2! :
> @eventtime = >> %3! : @extrainfo = %4!',
> >> @event, @spid, @eventtime, @extrainfo
> >>
> >> if @extrainfo = @halt_flag -- we're at/near
> the >> end of the audit table
> >> -- so let's stop
> and >> wait for awhile
> >> begin
> >> print 'waiting ...'
> >> waitfor delay "00:00:10" --
> modify >> to your liking
> >>
> >> -- generate a new unique string
> >>
> >> select @halt_flag = 'halt at ' +
> >> convert(varchar,getdate(),109)
> >>
> >> -- dump the new unique string onto
> >> sysaudits_0x
> >>
> >> exec sp_addauditrecord @halt_flag
> >>
> >> -- wait for the auditing system to
> >> actually add my unique
> >> -- string to sysaudits_0x
> >>
> >> waitfor delay "00:00:01"
> >> end
> >> else
> >> begin
> >> print 'here is where you could
> process >> your newly acquired audit record'
> >> end
> >>
> >> fetch audit_cur into @event,
> >> @spid,
> >> @eventtime,
> >> @extrainfo
> >> end
> >>
> >> close audit_cur
> >> go
> >>
> >> deallocate cursor audit_cur
> >> go
> >> ============================
> >>
> >> NOTES:
> >>
> >> 1 - you'll need to run "sp_audit 'adhoc',...." to allow
> >> for adding adhoc rows via sp_addauditrecord
> >>
> >> 2 - sp_addauditrecord allows you to designate up to 6
> >> different attributes of an audit record so there's
> plenty >> of room for creativity in building a unique
> adhoc audit >> record; for this example I went with
> setting a single >> attribute, namely the 'extrainfo'
> column >>
> >> 3 - you'll want to experiment with the amount of time
> you >> wait after running sp_addauditrecord; objective is
> to make >> sure you don't race ahead to read sysaudits_01
> before your >> halt flag has been inserted by the auditing
> process; if >> you're not too concerned with how quickly
> you read rows >> you could consider doing the
> sp_addauditrecord before the >> 10 second wait (ie, just
> do the single wait once you're >> inside the loop)
> >>
> >> 4 - I'll leave it up to you to add the logic for
> checking >> to see if/when you need to switch to a new
> sysaudits_0x >> table (eg, a threshold procedure forces
> the audit process >> to switch to a different sysaudits_0x
> table) >>
> >> 5 - to support the ability to restart your process
> where >> you left off you may want to consider keeping
> track of the >> last halt flag you successfully read from
> sysaudits_0x; >> you could then add logic to the above
> code which initially >> scans the table until it finds
> the last successfully read >> halt flag (objective being
> to skip over - not process - >> rows you're already
> processed) >>
> >> 6 - I'm assuming this T-SQL cursor code can be emulated
> in >> ct-library routine ... ?
> >>
> >>
> >> On 06/14/2011 15:35, Jon Saxton wrote:
> >>> I think we're getting to the point of saying that it
> is >>> not possible to do what I want.
> >>> The sybsecurity audit tables are not indexed. New
> rows >>> are simply added to the end of the table as audit
> events >> occur.>
> >>> I don't really care what order I read the rows. I
> have >>> other robust code which deals with ordering
> issues. I >>> can just throw rows at it and everything
> gets sorted >>> out. My issue is that if I know that 12
> rows have been >>> added to the end of the table then I
> just want to fetch >>> those 12 rows with the minimum
> possible impact on >>> Sybase. From performance testing
> it does appear that >>> doing a select for eventtime>
> something causes Sybase >>> to read the entire table every
> time looking for rows >>> which satisfy the condition. It
> doesn't know that they >>> are all at the end of the table
> even though that is >> where Sybase put them.>
> >>> Because these are Sybase-generated tables I do not
> have >>> the option of indexing them.
> >>>
> >>> So far I have not get given up entirely on the idea of
> a >>> scrollable semi-sensitive cursor but I am certainly
> not >>> too optimistic.


"Mark A. Parsons" <iron_horse Posted on 2011-06-16 20:17:14.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
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: ct_library - How do I read new rows in an unindexed table?
References: <4df918f6$1@forums-1-dub> <4dfa21fe.1fce.1681692777@sybase.com>
In-Reply-To: <4dfa21fe.1fce.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: <4dfa64ca$1@forums-1-dub>
Date: 16 Jun 2011 13:17:14 -0700
X-Trace: forums-1-dub 1308255434 10.22.241.152 (16 Jun 2011 13:17:14 -0700)
X-Original-Trace: 16 Jun 2011 13:17:14 -0700, vip152.sybase.com
Lines: 131
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30280
Article PK: 72459


On 06/16/2011 11:32, Jon Saxton wrote:
>> How are you determining the number of rows to fetch?
>> systabstats, running a 'select count(*)', something else?
>>
>> systabstats isn't guaranteed to be 100% accurate so
>> there's a good chance you could find your counts off a bit
>> from time to time.
>>
>> Obviously (?) the 'select count(*)' is going to incur a
>> good bit of overhead for constant rescanning of the audit
>> table.
>
> That is a bit alarming. I would not have expected that. I
> assumed that the overall row count is a first class
> attribute of a table and the database would be able to
> answer an unqualified "select count(*) from sysaudits_0x"
> query without having to go to the trouble of scanning the
> table. It is exactly the situation I want to avoid.

select count(*) is going to scan something ... the table if it has no index ... an index if it's smaller than the table.
In both cases the 'select' will go count the number of rows (table) or number of index entries (index).

> I don't know how to use systabstats to get the row count for
> the audit table of interest. I know it can be had with
> sp_spaceused and I presume it gets its information from the
> same source. Is that a cheaper query than select count(*)?

systabstats has a column called rowcnt (sp?) that contains a fairly accurate measure of the number of rows in a table.
The dataserver will periodically flush in-memory stats to systabstats, so systabstats is not guaranteed to be 100%
accurate at all times.

Selecting from systabstats (id = object_id(<table_name>) and indid in (0,1)) is quite fast and definitely faster than
running a 'select count(*)' on a large table ... but with systabstats you don't get near the accuracy of a 'select
count(*)'.

>> Yes, keeping track of the 'current audit table' value in
>> syscurconfigs would be trivial. In my example that check
>> could be done each time before/after the 10 second sleep
>> period. [NOTE: I'm wondering if the cursor lock towards
>> the tail end of the audit table would cause any problems
>> for the auditing process ... probably depends on what the
>> threshold attempts to do with the old table after
>> performing the switch.]
>
> Ideally the threshold procedure does not truncate the old
> table. Of course I cannot control that but it is probably
> the case and is certainly true of the sample threshold
> procedure that I provide.

Yeah, that'll depend on the person who wrote the threshold procedure. I've seen some that truncate immediately after
the switch (and copy), while others will truncate just prior to switching *to* the table. It usually depends on whether
or not there's a rush/desire to free up space in sybsecurity as soon as possible.

>> Why do you think you'll be unable to get this to work in
>> pre-15 dataservers? If you're thinking about
>> (semi)sensitive cursors and all the schtuff associated
>> with them ... you may be over thinking a bit; the example
>> T-SQL code I provided should work just fine in a 12.5.x
>> dataserver, too.
>
> You could be right. However the code does not have to work
> on pre-15.x Sybase. From reading the documentation on
> ct_cursor and its attendant functions it seemed to me that
> the ideal cursor type was a scrollable semi-sensitive one.
> IIRC the result set does not materialize until the
> ct_scroll_fetch() is called so if I am starting with 200005
> rows in the audit table I can do a fetch at absolute
> position 200000 and read the last 5 records without getting
> all those early rows written to some work table. (This is
> only an issue when the program starts.) Of course I could
> be mistaken in my interpretation but scrollabe cursors are
> not supported in earlier versions of the library and indeed
> to not work against a 12.5 Sybase.

Ahhh, you've been concentrating on the ct-lib cursor docs while I've been sticking solely to T-SQL/dataserver-side
cursors. ct-lib offers more bells and whistles ... and thus more confusion. ;-)

When I hear the term 'scrollable cursor' I think of someone wanting to scroll backwards and forwards. For this
particular scenario of reading sysaudits_0x I can't see any reason to need to scroll backwards ... so 'scrollable
cursor' doesn't do anything for me. All I need is the ability to 'scroll forward' one line at a time ... and that's
been available with T-SQL/dataserver-side cursors since well before ASE 15.

For this particular scenario (continual scanning at/near the end of the sysaudits_0x table) the only thing I'm worried
about is making sure the cursor does *NOT* materialize the query (eg, stuff the records into a worktable and then cursor
off of the worktable). Since sysaudits_0x has no index, and my cursor definition does not include any 'order by' or
'group by' clauses, I'm pretty safe with a straight forward table scan of the sysaudits_0x table.

As for your example of needing to skip over 200000 records ... you could either try adding a 'where eventtime >
##/##/####' to your cursor definition (should still perform a non-materialized table scan) or just scan the entire table
(could be a bit time consuming to do a bunch of fetch/discard loops to get to where you want to be.

If you run into problems with the ct-lib cursors you should be able to do all of the cursor work in T-SQL with a stored
proc; with the proc passing back to the client any new/recent audit records.

> This whole line of investigation arose from the observation
> that reading the audit able with a query of the form "select
> [list of columns] from sysaudits_0x where eventtime>
> something" had an ever-increasing impact on Sybase CPU
> utilization as the audit table grew. It is probably not too
> bad if the audit segments are 10 Mb or less but some people
> insist on having much larger audit tables and then complain
> about performance.

Yeah, each time you ran your query against sysaudits_0x you were doing a table scan. By running the query often you
were probably insuring the entire sysaudits_0x table was in cache, hence pure cpu usage each time you ran your query.
And of course as the table grew the query required more cpu to scan more pages from cache.

Another (bad) side effect from that approach would have been that you were using up data cache to hold 'old'
sysaudits_0x records (that you didn't need), while other system/user tables would've been pushed out of data cache.
Probably not noticeable for smallish sysaudits_0x tables ...

> For what it is worth, I have a similar issue with auditing
> on Oracle but I can get away with the simplistic approach
> because the number of audit records generated in a given
> period is much lower and I am allowed to truncate the table
> whenever I want, e.g. every 5000 rows.

Well, technically you could also truncate the tables in Sybase ... though the normal approach is to force a switch to a
new sysaudits_0x table and then truncate the old table. And since each switch generates a new dataserver *cfg file, the
DBAs probably wouldn't be too happy with you generating 100's of new *cfg files every day.

> You have given me plenty to think about. I am coming around
> to the idea of writing a sentinel row, especially if it
> turns out that "select count(*)" is expensive.

Yeah, 'select count(*)' is going to be expensive.

> Thanks so much for all the input.

sure, np


Jon Saxton Posted on 2011-06-23 17:38:56.0Z
Sender: 4e3e.4e035ea1.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e037a30.50bd.1681692777@sybase.com>
References: <4dfa64ca$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Jun 2011 10:38:56 -0700
X-Trace: forums-1-dub 1308850736 10.22.241.41 (23 Jun 2011 10:38:56 -0700)
X-Original-Trace: 23 Jun 2011 10:38:56 -0700, 10.22.241.41
Lines: 148
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30285
Article PK: 72465

Mark:

[Much stuff deleted]

> Selecting from systabstats (id = object_id(<table_name>)
> and indid in (0,1)) is quite fast and definitely faster
> than running a 'select count(*)' on a large table ... but
> with systabstats you don't get near the accuracy of a
> 'select count(*)'.

It is certainly good enough for my purposes. I can see a
delay in the update systabstats, sometimes several seconds,
but I can live with that. Anything to avoid a table scan.

>>> Yes, keeping track of the 'current audit table' value
>>> in syscurconfigs would be trivial. In my example that
>>> check could be done each time before/after the 10
>>> second sleep period. [NOTE: I'm wondering if the
>>> cursor lock towards the tail end of the audit table
>>> would cause any problems for the auditing process ...
>>> probably depends on what the threshold attempts to do
>>> with the old table after performing the switch.]

Prophetic words ... see below.

[More stuff deleted]

>>> Why do you think you'll be unable to get this to work
>>> in pre-15 dataservers? If you're thinking about
>>> (semi)sensitive cursors and all the schtuff associated
>>> with them ... you may be over thinking a bit; the
>>> example T-SQL code I provided should work just fine in
>>> a 12.5.x dataserver, too.
>>
>> You could be right. However the code does not have to
>> work on pre-15.x Sybase. From reading the documentation
>> on ct_cursor and its attendant functions it seemed to me
>> that the ideal cursor type was a scrollable
>> semi-sensitive one. IIRC the result set does not
>> materialize until the ct_scroll_fetch() is called so if
>> I am starting with 200005 rows in the audit table I can
>> do a fetch at absolute position 200000 and read the last
>> 5 records without getting all those early rows written
>> to some work table. (This is only an issue when the
>> program starts.) Of course I could be mistaken in my
>> interpretation but scrollabe cursors are not supported
>> in earlier versions of the library and indeed to not
>> work against a 12.5 Sybase.
>
> Ahhh, you've been concentrating on the ct-lib cursor docs
> while I've been sticking solely to T-SQL/dataserver-side
> cursors. ct-lib offers more bells and whistles ... and
> thus more confusion. ;-)
>
> When I hear the term 'scrollable cursor' I think of
> someone wanting to scroll backwards and forwards. For
> this particular scenario of reading sysaudits_0x I can't
> see any reason to need to scroll backwards ... so
> 'scrollable cursor' doesn't do anything for me. All I
> need is the ability to 'scroll forward' one line at a time
> .. and that's been available with T-SQL/dataserver-side
> cursors since well before ASE 15.
>
> For this particular scenario (continual scanning at/near
> the end of the sysaudits_0x table) the only thing I'm
> worried about is making sure the cursor does *NOT*
> materialize the query (eg, stuff the records into a
> worktable and then cursor off of the worktable). Since
> sysaudits_0x has no index, and my cursor definition does
> not include any 'order by' or 'group by' clauses, I'm
> pretty safe with a straight forward table scan of the
> sysaudits_0x table.
>
> As for your example of needing to skip over 200000 records
> .. you could either try adding a 'where eventtime >
> ##/##/####' to your cursor definition (should still
> perform a non-materialized table scan) or just scan the
> entire table (could be a bit time consuming to do a bunch
> of fetch/discard loops to get to where you want to be.

Correct me if I am wrong but from earlier correspondence if
I qualify the select then given that the audit tables are
not indexed a predicate implies a scan of the entire table
to find matching rows. That is exactly what I am trying to
avoid.

> If you run into problems with the ct-lib cursors you
> should be able to do all of the cursor work in T-SQL with
> a stored proc; with the proc passing back to the client
> any new/recent audit records.

The test script actually terminates after one iteration for
reasons probably related to what follows.

From observation (and later confirmed by reading the online
documentation) it is a really bad idea to keep a cursor open
on the audit tables for any length of time. The audit table
is locked against modification until the cursor is closed.
This applies to a read-only cursor also. No new rows will
be added to the table. Ultimately the database will become
unusable.

This I found surprising and disappointing. Examples in the
documentation strongly suggested that new rows would be
visible to a semi-sensitive cursor.

A scrollable, semi-sensitive cursor seems to be the only one
for which the result set is not materialised until the
fetch. That is the type I am using. Exactly what
'materialised' means is another matter. If I have an
unqualified select and then tell the cursor to fetch record
200001 do the first 200000 rows get loaded into a work table
as well? Or is the work table sparse? The documentation is
not very clear on that.

(I am assuming that when applied to a result set the word
'materialised' means that the pertinent rows are stored in a
work table.)

Certainly doing a sequence of fetches starting at row 200001
appears to work. It is just not clear what happens behind
the scenes. I suppose I'll be able to tell when and if I
get around to running performance tests.

Given that I can't keep a cursor open and assuming that the
result set is materialised to just the row(s) fetched then
there may be an alternative solution. I know exactly where
to start fetching so I could just open a cursor afresh on
each poll of the audit table.

I have no idea what the cost of that would be but I suspect
it would be less than reprocessing thousands of
uninteresting records each time and hopefully less than
doing a full table scan to satisfy a predicate on a select
statement.

Now I just have to figure out how to close a cursor properly
and tell Sybase that I don't need its result set any longer.
I've tried a couple of variations on ct_cancel() but I have
not yet found one that seems to do everything. On the next
iteration I can open the cursor anew but I get a CT_FAIL
condition when I try get a new result set and it leaves the
connection to the database in an unusable state. I am
assuming that the cause is an open work table.

Not quite there yet. And of course if the performance is
poor then I'll have abandon the whole plan and go back to
the simple qualified select.


"Mark A. Parsons" <iron_horse Posted on 2011-06-23 18:22:30.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
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: ct_library - How do I read new rows in an unindexed table?
References: <4dfa64ca$1@forums-1-dub> <4e037a30.50bd.1681692777@sybase.com>
In-Reply-To: <4e037a30.50bd.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: <4e038466$1@forums-1-dub>
Date: 23 Jun 2011 11:22:30 -0700
X-Trace: forums-1-dub 1308853350 10.22.241.152 (23 Jun 2011 11:22:30 -0700)
X-Original-Trace: 23 Jun 2011 11:22:30 -0700, vip152.sybase.com
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30286
Article PK: 72463


> The test script actually terminates after one iteration for
> reasons probably related to what follows.
>
> From observation (and later confirmed by reading the online
> documentation) it is a really bad idea to keep a cursor open
> on the audit tables for any length of time. The audit table
> is locked against modification until the cursor is closed.
> This applies to a read-only cursor also. No new rows will
> be added to the table. Ultimately the database will become
> unusable.

Yeah, I was thinking about the locking being a potential problem, especially since there doesn't appear (yet) to be a
way to build the sysaudits_0x tables to use datarows locking.

And probably because a) I'm running my tests in T-SQL b) on an ASE 15.0.3 dataserver I implicitly get a semi-sensitive
cursor (if not explicitly specific by the 'declare cursor' command) so my tests work just fine (ie, my looping cursor
can see newly added rows).

If I redefine my T-SQL cursor as insensitive ... then I get the same behavior as you ... my monitoring loop exits after
one (attempted) pass. [I'm assuming the same failure would occur in ASE 12.5.4 ... I'm just too lazy to fire up the old
PC in the corner that has my 12.5.4 install ... ]

> This I found surprising and disappointing. Examples in the
> documentation strongly suggested that new rows would be
> visible to a semi-sensitive cursor.

They are visible to a semi-sensitive cursor ... at least a server-side/T-SQL semi-sensitive cursor.

If your semi-sensitive cursor (on the ct-lib side) isn't behaving as you expect, then set aside your ct-lib programming
for a little bit and try the T-SQL method.

You've stated that you're running against ASE 15.x so the code I posted earlier should suffice (ie, the 'declare cursor'
command should give you a semi-sensitive cursor).

See if you can get the T-SQL code working from an isql prompt.

If you can get the T-SQL code working from an isql prompt, try replacing isql with your ct-lib app (ie, ct-lib program
uses the server-side/T-SQL cursor).

If you can get the server-side/T-SQL cursor working from your ct-lib program, then perhaps there's a bug/issue with the
ct-lib version you're using?

If you can get the server-side/T-SQL cursor working from your ct-lib program, consider the problem solved (ie, scrub the
client-side cursor solution in favor of a server-side cursor solution) ... ?


"Mark A. Parsons" <iron_horse Posted on 2011-06-23 21:22:44.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
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: ct_library - How do I read new rows in an unindexed table?
References: <4dfa64ca$1@forums-1-dub> <4e037a30.50bd.1681692777@sybase.com>
In-Reply-To: <4e037a30.50bd.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: <4e03aea4$1@forums-1-dub>
Date: 23 Jun 2011 14:22:44 -0700
X-Trace: forums-1-dub 1308864164 10.22.241.152 (23 Jun 2011 14:22:44 -0700)
X-Original-Trace: 23 Jun 2011 14:22:44 -0700, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30287
Article PK: 72466


On 06/23/2011 13:38, Jon Saxton wrote:
>> As for your example of needing to skip over 200000 records
>> .. you could either try adding a 'where eventtime>
>> ##/##/####' to your cursor definition (should still
>> perform a non-materialized table scan) or just scan the
>> entire table (could be a bit time consuming to do a bunch
>> of fetch/discard loops to get to where you want to be.
>
> Correct me if I am wrong but from earlier correspondence if
> I qualify the select then given that the audit tables are
> not indexed a predicate implies a scan of the entire table
> to find matching rows. That is exactly what I am trying to
> avoid.

Regardless of whether or not you supply a predicate you're going to do a table scan of the sysaudits_0x table when you
open the cursor.

Your key concerns are a) to limit the number of times you open the cursor (ie, limit number of times you table scan
sysaudits_0x), and b) in the case of leaving the cursor at the end of the table (waiting for new rows) you need to make
sure the cursor does not materialize the data set (ie, load the data into a worktable and then cursor off the worktable).

The reason I mentioned the predicate ('where eventtime > ##/##/####') was to reduce the volume of fetches you have to do
in the case of (re)opening the cursor (ie, re-scanning the sysaudits_0x table).

For example, assume there are 200,005 rows in the sysaudits_0x table, you need to (re)open your cursor, you want to
obtain the last 5 rows, and you know the last eventtime you processed (ie, eventtime for row # 200,000):

- if you provide no predicate to filter the rows processed by the cursor, then you have to go through 200,000
fetch-n-discard loops until you get to the 5 rows you want [ok, so with newer cursor features you can do a single fetch
of 200,000 rows in one pass ... but probably still a bit of extra overhead for the cursor to fetch-n-discard 200,000 rows]

- if you provide the predicate to filter the rows processed by the cursor, then the cursor will still scan the entire
sysaudits_0x table but it will skip over the first 200,000 rows thus insuring you only have to go through 5
fetch-n-process loops


J Posted on 2011-06-14 16:56:30.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
Reply-To: J@bogusemailAddress.com
Message-ID: <4df79196.695679406@forums.sybase.com>
References: <4df230c9$1@forums-1-dub> <4df75974.2d17.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 14 Jun 2011 09:56:30 -0700
X-Trace: forums-1-dub 1308070590 10.22.241.152 (14 Jun 2011 09:56:30 -0700)
X-Original-Trace: 14 Jun 2011 09:56:30 -0700, vip152.sybase.com
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30252
Article PK: 72433

On 14 Jun 2011 05:52:04 -0700, Jon Saxton wrote:


Jon,

How close to "real-time" are you trying to read the audit rows? I
would think that cursors are not going to help as the resultset should
be fixed at some point and not pickup the rows added after this point.
I think the difference between sensitive and semi-sensitive are the
resultset being fixed at open time vs. as you access the data but when
you hit the last row this should be repeatable with the same cursor (I
believe).

I would think that the best approach would be to copy the rows from
audit to your own table which could be indexed perhaps switching audit
tables before your copy.

Jay

>> On 10-Jun-2011 16:45, Jon Saxton wrote:
>> > Perhaps a simpler question is "How do I read the last N
>> > rows in an unindexed table?" given that I know the value
>> of N. select top N * from table order by some_column desc
>>
>> 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
>> ----------------------------------------------------------
>> -------
>>
>Thanks for the suggestion, Rob. Seems simple enough, but I
>have a question about it ...
>
>Doesn't the "order by some_column desc" clause imply a sort
>of the entire table?
>
>The audit table is already in the correct order in that new
>rows are appended to it as auditable events occur. I just
>want the last few records, i.e. the ones which have been
>appended since last time I looked.
>
>Do I really want to tell Sybase to sort 200000 records just
>to give me 5 when I know that the 5 I want are at the end of
>the table?
>
>It is this consideration which led me to consider using a
>sensitive cursor. However if your solution does not imply a
>sort then clearly it would be a lot easier.
>
>As a bit of background, I have experimented using the
>eventtime column which contains a monotonically increasing
>value. When testing a query of the form "select ... where
>eventtime > previous" I found that as the audit table grows,
>there is a corresponding linear growth in Sybase's CPU time
>until such time as a rollover occurs.


J Posted on 2011-06-15 21:29:56.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
Reply-To: J@bogusemailAddress.com
Message-ID: <4df923d4.798653500@forums.sybase.com>
References: <4df79196.695679406@forums.sybase.com> <4df7b072.39ae.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 15 Jun 2011 14:29:56 -0700
X-Trace: forums-1-dub 1308173396 10.22.241.152 (15 Jun 2011 14:29:56 -0700)
X-Original-Trace: 15 Jun 2011 14:29:56 -0700, vip152.sybase.com
Lines: 108
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30266
Article PK: 72443

On 14 Jun 2011 12:03:14 -0700, Jon Saxton wrote:

Using a semi-sensivite cursor all rows are copied to a work table so
after you visit the last row in the base table the base table is never
revisited. This could only would with a sensitive cursor but that is
not supported.

Jay

>Jay:
>
>I want to get new audit rows every few seconds where "few"
>is not well defined but is somewhere between 2 and 15. Say
>5 for current purposes.
>
>The 15.x documentation does not deal specifically with this
>situation but I have the impression that a scrollable
>semi-sensitive cursor should see new rows added to the
>result set. According to the manual, the result set is not
>materialized until rows are fetched, then once each row is
>read it becomes fixed but if my understanding is correct,
>changes to un-fetched rows should be visible. I *hope* that
>includes the addition of new rows to the end.
>
>Of course I can find out exactly how many rows have been
>added between the fetch cycles so I can probably avoid
>bumping into end-of-data conditions. I could leave the
>cursor pointing just beyond the last row and avoid trying to
>do a fetch at that point.
>
>(I am rambling a bit and thinking as I type.)
>
>
>> On 14 Jun 2011 05:52:04 -0700, Jon Saxton wrote:
>>
>>
>> Jon,
>>
>> How close to "real-time" are you trying to read the audit
>> rows? I would think that cursors are not going to help as
>> the resultset should be fixed at some point and not pickup
>> the rows added after this point. I think the difference
>> between sensitive and semi-sensitive are the resultset
>> being fixed at open time vs. as you access the data but
>> when you hit the last row this should be repeatable with
>> the same cursor (I believe).
>>
>> I would think that the best approach would be to copy the
>> rows from audit to your own table which could be indexed
>> perhaps switching audit tables before your copy.
>>
>> Jay
>>
>> >> On 10-Jun-2011 16:45, Jon Saxton wrote:
>> >> > Perhaps a simpler question is "How do I read the last
>> N >> > rows in an unindexed table?" given that I know the
>> value >> of N. select top N * from table order by
>> some_column desc >>
>> >> 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
>> >>
>> ----------------------------------------------------------
>> >> ------- >>
>> >Thanks for the suggestion, Rob. Seems simple enough, but
>> I >have a question about it ...
>> >
>> >Doesn't the "order by some_column desc" clause imply a
>> sort >of the entire table?
>> >
>> >The audit table is already in the correct order in that
>> new >rows are appended to it as auditable events occur. I
>> just >want the last few records, i.e. the ones which have
>> been >appended since last time I looked.
>> >
>> >Do I really want to tell Sybase to sort 200000 records
>> just >to give me 5 when I know that the 5 I want are at
>> the end of >the table?
>> >
>> >It is this consideration which led me to consider using a
>> >sensitive cursor. However if your solution does not
>> imply a >sort then clearly it would be a lot easier.
>> >
>> >As a bit of background, I have experimented using the
>> >eventtime column which contains a monotonically
>> increasing >value. When testing a query of the form
>> "select ... where >eventtime > previous" I found that as
>> the audit table grows, >there is a corresponding linear
>> growth in Sybase's CPU time >until such time as a rollover
>> occurs.
>>


J Posted on 2011-06-15 22:55:05.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
Reply-To: J@bogusemailAddress.com
Message-ID: <4df937ec.803798468@forums.sybase.com>
References: <4df79196.695679406@forums.sybase.com> <4df7b072.39ae.1681692777@sybase.com> <4df923d4.798653500@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 15 Jun 2011 15:55:05 -0700
X-Trace: forums-1-dub 1308178505 10.22.241.152 (15 Jun 2011 15:55:05 -0700)
X-Original-Trace: 15 Jun 2011 15:55:05 -0700, vip152.sybase.com
Lines: 119
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30267
Article PK: 72445

On 15 Jun 2011 14:29:56 -0700, jtotally_bogus@sbcglobal.net (J) wrote:

Humm,

Your test results don't concur with my research. Unfortunately, I
don't have the cycles to do a lot more on this right now. I will try
to get to it a bit later.

Jay

>On 14 Jun 2011 12:03:14 -0700, Jon Saxton wrote:
>
>Using a semi-sensivite cursor all rows are copied to a work table so
>after you visit the last row in the base table the base table is never
>revisited. This could only would with a sensitive cursor but that is
>not supported.
>
>Jay
>
>>Jay:
>>
>>I want to get new audit rows every few seconds where "few"
>>is not well defined but is somewhere between 2 and 15. Say
>>5 for current purposes.
>>
>>The 15.x documentation does not deal specifically with this
>>situation but I have the impression that a scrollable
>>semi-sensitive cursor should see new rows added to the
>>result set. According to the manual, the result set is not
>>materialized until rows are fetched, then once each row is
>>read it becomes fixed but if my understanding is correct,
>>changes to un-fetched rows should be visible. I *hope* that
>>includes the addition of new rows to the end.
>>
>>Of course I can find out exactly how many rows have been
>>added between the fetch cycles so I can probably avoid
>>bumping into end-of-data conditions. I could leave the
>>cursor pointing just beyond the last row and avoid trying to
>>do a fetch at that point.
>>
>>(I am rambling a bit and thinking as I type.)
>>
>>
>>> On 14 Jun 2011 05:52:04 -0700, Jon Saxton wrote:
>>>
>>>
>>> Jon,
>>>
>>> How close to "real-time" are you trying to read the audit
>>> rows? I would think that cursors are not going to help as
>>> the resultset should be fixed at some point and not pickup
>>> the rows added after this point. I think the difference
>>> between sensitive and semi-sensitive are the resultset
>>> being fixed at open time vs. as you access the data but
>>> when you hit the last row this should be repeatable with
>>> the same cursor (I believe).
>>>
>>> I would think that the best approach would be to copy the
>>> rows from audit to your own table which could be indexed
>>> perhaps switching audit tables before your copy.
>>>
>>> Jay
>>>
>>> >> On 10-Jun-2011 16:45, Jon Saxton wrote:
>>> >> > Perhaps a simpler question is "How do I read the last
>>> N >> > rows in an unindexed table?" given that I know the
>>> value >> of N. select top N * from table order by
>>> some_column desc >>
>>> >> 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
>>> >>
>>> ----------------------------------------------------------
>>> >> ------- >>
>>> >Thanks for the suggestion, Rob. Seems simple enough, but
>>> I >have a question about it ...
>>> >
>>> >Doesn't the "order by some_column desc" clause imply a
>>> sort >of the entire table?
>>> >
>>> >The audit table is already in the correct order in that
>>> new >rows are appended to it as auditable events occur. I
>>> just >want the last few records, i.e. the ones which have
>>> been >appended since last time I looked.
>>> >
>>> >Do I really want to tell Sybase to sort 200000 records
>>> just >to give me 5 when I know that the 5 I want are at
>>> the end of >the table?
>>> >
>>> >It is this consideration which led me to consider using a
>>> >sensitive cursor. However if your solution does not
>>> imply a >sort then clearly it would be a lot easier.
>>> >
>>> >As a bit of background, I have experimented using the
>>> >eventtime column which contains a monotonically
>>> increasing >value. When testing a query of the form
>>> "select ... where >eventtime > previous" I found that as
>>> the audit table grows, >there is a corresponding linear
>>> growth in Sybase's CPU time >until such time as a rollover
>>> occurs.
>>>
>


Bret Halford Posted on 2011-06-10 15:40:20.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
References: <4defb5c3.1acf.1681692777@sybase.com>
In-Reply-To: <4defb5c3.1acf.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: <4df23ae4$1@forums-1-dub>
Date: 10 Jun 2011 08:40:20 -0700
X-Trace: forums-1-dub 1307720420 10.22.241.152 (10 Jun 2011 08:40:20 -0700)
X-Original-Trace: 10 Jun 2011 08:40:20 -0700, vip152.sybase.com
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30244
Article PK: 72423


On 6/8/2011 11:47 AM, Jon Saxton wrote:
> I am trying to read new stuff which gets added to the
> sysaudits_0X tables in sybsecurity. The audit tables are
> not indexed. Sybase just writes new stuff to the end until
> the threshold procedure kicks in.
>
> So I have a C++ program using ct_library calls to read audit
> records. Let us say I read 200000 records and I reach the
> end of the table. 5 seconds later another 30 records have
> been written. I want to read those 30 new records without
> having to read the first 200000 all over again.
>
> I have read every piece of documentation on cursors that I
> can find and none of them addresses this specific issue but
> I do get the impression that a sensitive, read-only cursor
> (SEMI_SENSITIVE in Sybase parlance) should do the trick but
> I am not sure and I haven't found the correct sequence of
> calls.
>
> I expect that after doing all the initialisation I should be
> able to read to the end of the result set, leave the cursor
> open, then in a few seconds try again and get any new rows
> which have been added, repeating this process at regular
> intervals.
>
> What I find is that the cursor gets into some state which
> prevents it from being reused. My program detects the end
> of the result set and then hangs around for a bit but when
> it tries to do another read, the cursor is basically
> scrogged. I have not been able to get it to process the
> added rows.
>
> So before I bore you all with the details of the calls and
> results the first question is: "Is this an exercise in
> futility?" If so then I really don't understand cursors at
> all, even though this seems like a really worthwhile
> application for one.
>
> Right now I just want to know if I should persevere or
> emigrate to somewhere nice and tropical.

I don't know offhand quite what the problem is, but it sounds like
the problem happens when your cursor is on the last row
and you try to fetch again. So you might be able to avoid the problem
by causing your unit of work to insert an audit record and then fetch
only until it reaches that record. Treat that as the end of the
data rather than proceeding until the cursor actually runs out of rows.

-bret


Jason L. Froebe [TeamSybase] Posted on 2011-06-16 16:00:56.0Z
From: "Jason L. Froebe [TeamSybase]" <jason.froebe@gmail.com>
Organization: TeamSybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
References: <4defb5c3.1acf.1681692777@sybase.com>
In-Reply-To: <4defb5c3.1acf.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dfa28b8$1@forums-1-dub>
Date: 16 Jun 2011 09:00:56 -0700
X-Trace: forums-1-dub 1308240056 10.22.241.152 (16 Jun 2011 09:00:56 -0700)
X-Original-Trace: 16 Jun 2011 09:00:56 -0700, vip152.sybase.com
Lines: 46
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30277
Article PK: 72456


On 6/8/2011 12:47 PM, Jon Saxton wrote:
> I am trying to read new stuff which gets added to the
> sysaudits_0X tables in sybsecurity. The audit tables are
> not indexed. Sybase just writes new stuff to the end until
> the threshold procedure kicks in.
>
> So I have a C++ program using ct_library calls to read audit
> records. Let us say I read 200000 records and I reach the
> end of the table. 5 seconds later another 30 records have
> been written. I want to read those 30 new records without
> having to read the first 200000 all over again.
>
> I have read every piece of documentation on cursors that I
> can find and none of them addresses this specific issue but
> I do get the impression that a sensitive, read-only cursor
> (SEMI_SENSITIVE in Sybase parlance) should do the trick but
> I am not sure and I haven't found the correct sequence of
> calls.
>
> I expect that after doing all the initialisation I should be
> able to read to the end of the result set, leave the cursor
> open, then in a few seconds try again and get any new rows
> which have been added, repeating this process at regular
> intervals.
>
> What I find is that the cursor gets into some state which
> prevents it from being reused. My program detects the end
> of the result set and then hangs around for a bit but when
> it tries to do another read, the cursor is basically
> scrogged. I have not been able to get it to process the
> added rows.
>
> So before I bore you all with the details of the calls and
> results the first question is: "Is this an exercise in
> futility?" If so then I really don't understand cursors at
> all, even though this seems like a really worthwhile
> application for one.
>
> Right now I just want to know if I should persevere or
> emigrate to somewhere nice and tropical.

Just out of curiosity, is there a particular reason why you can't have
an index on the table?

jason


Jon Saxton Posted on 2011-06-16 16:52:06.0Z
Sender: 689a.4df8e434.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dfa34b6.222b.1681692777@sybase.com>
References: <4dfa28b8$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Jun 2011 09:52:06 -0700
X-Trace: forums-1-dub 1308243126 10.22.241.41 (16 Jun 2011 09:52:06 -0700)
X-Original-Trace: 16 Jun 2011 09:52:06 -0700, 10.22.241.41
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30278
Article PK: 72457


> On 6/8/2011 12:47 PM, Jon Saxton wrote:
> > I am trying to read new stuff which gets added to the
> > sysaudits_0X tables in sybsecurity. The audit tables
> > are not indexed. Sybase just writes new stuff to the
> > end until the threshold procedure kicks in.
> >
> > So I have a C++ program using ct_library calls to read
> > audit records. Let us say I read 200000 records and I
> > reach the end of the table. 5 seconds later another 30
> > records have been written. I want to read those 30 new
> > records without having to read the first 200000 all over
> > again.
> > I have read every piece of documentation on cursors that
> > I can find and none of them addresses this specific
> > issue but I do get the impression that a sensitive,
> > read-only cursor (SEMI_SENSITIVE in Sybase parlance)
> > should do the trick but I am not sure and I haven't
> > found the correct sequence of calls.
> >
> > I expect that after doing all the initialisation I
> > should be able to read to the end of the result set,
> > leave the cursor open, then in a few seconds try again
> > and get any new rows which have been added, repeating
> > this process at regular intervals.
> >
> > What I find is that the cursor gets into some state
> > which prevents it from being reused. My program detects
> > the end of the result set and then hangs around for a
> > bit but when it tries to do another read, the cursor is
> > basically scrogged. I have not been able to get it to
> > process the added rows.
> >
> > So before I bore you all with the details of the calls
> > and results the first question is: "Is this an exercise
> > in futility?" If so then I really don't understand
> > cursors at all, even though this seems like a really
> > worthwhile application for one.
> >
> > Right now I just want to know if I should persevere or
> > emigrate to somewhere nice and tropical.
>
>
> Just out of curiosity, is there a particular reason why
> you can't have an index on the table?
>
> jason

Yes. It is a Sybase system-generated table. I have no
control over its format. This is the tail end of the output
from "sp_help sysaudits_01":

extrainfo varchar 255 NULL NULL 1 NULL
NULL
NULL NULL 0
nodeid tinyint 1 NULL NULL 1 NULL
NULL
NULL NULL 0
Object does not have any indexes.
No defined keys for this object.


Jason L. Froebe [TeamSybase] Posted on 2011-06-16 17:02:58.0Z
From: "Jason L. Froebe [TeamSybase]" <jason.froebe@gmail.com>
Organization: TeamSybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
References: <4dfa28b8$1@forums-1-dub> <4dfa34b6.222b.1681692777@sybase.com>
In-Reply-To: <4dfa34b6.222b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dfa3742$1@forums-1-dub>
Date: 16 Jun 2011 10:02:58 -0700
X-Trace: forums-1-dub 1308243778 10.22.241.152 (16 Jun 2011 10:02:58 -0700)
X-Original-Trace: 16 Jun 2011 10:02:58 -0700, vip152.sybase.com
Lines: 22
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30279
Article PK: 72458


On 6/16/2011 11:52 AM, Jon Saxton wrote:
>> Just out of curiosity, is there a particular reason why
>> you can't have an index on the table?
>>
>> jason
>
> Yes. It is a Sybase system-generated table. I have no
> control over its format. This is the tail end of the output
> from "sp_help sysaudits_01":
>
> extrainfo varchar 255 NULL NULL 1 NULL
> NULL
> NULL NULL 0
> nodeid tinyint 1 NULL NULL 1 NULL
> NULL
> NULL NULL 0
> Object does not have any indexes.
> No defined keys for this object.

Good point. I missed that part.

jason


Matthew Posted on 2011-06-17 08:39:37.0Z
From: Matthew <matt.malthusian@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ct_library - How do I read new rows in an unindexed table?
References: <4defb5c3.1acf.1681692777@sybase.com>
In-Reply-To: <4defb5c3.1acf.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: <4dfb12c9@forums-1-dub>
Date: 17 Jun 2011 01:39:37 -0700
X-Trace: forums-1-dub 1308299977 10.22.241.152 (17 Jun 2011 01:39:37 -0700)
X-Original-Trace: 17 Jun 2011 01:39:37 -0700, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30281
Article PK: 72460


On 2011-06-08 19:47, Jon Saxton wrote:
> Right now I just want to know if I should persevere or
> emigrate to somewhere nice and tropical.

Let's revisit the drawing board for a few minutes. I guess you (or your
DBA) have sort of an sybsecurity_arch database where the audit trail
ends up once the threshold has fired your specific threshold procedure,
copied the rows from the (up til now) active audit table into the
archive database (where you btw can create all the indexes you want, add
extra columns etc), switched to a new audit table and truncated the old one.

Now, how about instead of waiting around for the threshold to fire your
specific threshold procedure, you fire the threshold procedure by means
of cron or job scheduler at a regular pace that falls within our
business requirements and do all your work in the archive database?

The sybsecurity database is good, cause it cannot be tampered with
having only sa_role. On the other hand you cannot create any indexes
here. The archive version of the sybsecurity database is a normal user
database so it cannot be protected from the sa_role (unless using
encrypted columns - which seems like an overkill to me). The upside is
you can modify the archive table to have the indexes you want or any
extra columns you define.

On my wish list would be a way to create a system database wich has the
same hardcoded protection as sybsecurity but you could (as sso_role)
create tables and indexes here. This way you could have a small and neat
sybsecurity database to keep the auditing going and be flexible with the
ever-growing audit trail in the archive database(s).


/Matthew