Very Computer

We have an interesting problem on our Sybase server running on a Sparc
Server 2000 which is now becoming a "pain in the a##"!

It seems that the syslog table is growing too large because some of our
users are forgetting to log-off the system. (We belive that it is due to
the users not logging off, but something may be going on.)                
We are using PowerBuilder 3
under Windows 3.1 and Novell's LanWorkplace to connect to the Sybase
server. The only solution that anyone has offered is to increase the
partition size of the syslog table. It is currently about 300Mb and I
can't understand or justify another 1Gb hard drive for the sun just to
handle the syslog.

Any ideas?

Thanks in advance!

--Dewayne

********************************************************************************
Dewayne Adams                                     (510) 734-4373      voice
Advanced Technology Guru                          (510) 734-4814      fax

5964 West Las Positas Blvd.
Mail Stop J2-2E
Pleasanton, CA 94588

Leaving users logged in should not be a problem as long as you're
committing / rolling back all your transactions.  Of course you could
initiate a timer event to automatically log off the users after working
hours to see if that takes care of it.  
How long does it take to grow to this size after truncating it?

Mike

--

  ^0 0^  __                          //   : air /  ikitai desu yo  //
  ( v )      Woof.                 // .ri :t. /                  //
    U    (or is it Moo?)         //  ght  : /  BRAIN MODERNITY //

Quote:>> It seems that the syslog table is growing too large because some of our
>> users are forgetting to log-off the system. (We belive that it is due to
>> the users not logging off, but something may be going on.)                
>> We are using PowerBuilder 3
>> under Windows 3.1 and Novell's LanWorkplace to connect to the Sybase
>> server. The only solution that anyone has offered is to increase the
>> partition size of the syslog table. It is currently about 300Mb and I
>> can't understand or justify another 1Gb hard drive for the sun just to
>> handle the syslog.

One thing you need to look for is how PowerBuilder is handling
transactions.  For example, if PB30 is not issuing a Commit; command
after each logical unit of work, then the oldest tran in the log could
be causing the problem.  Remember, when you dump a transaction, it takes
only up to before the oldest outstanding tran in the log and removes all
others.  

It is possible that you have forgotten to issue commits on a screen-by
screen basis.  

Hope this helps...

--
Ken Rimple           |   "Let's do some crimes.  Like
Ardmore, PA          |   "buy sushi, and not pay!"

|>
|> It is possible that you have forgotten to issue commits on a screen-by
|> screen basis.  
|>

Is the accepted practice for Power Builder?  We are having a problem where
transactions are blocking.  I know for a fact that commits are not being done
on a screen by screen basis.

--
Lonnie Barnett
Mead Data Central                                     (513) 865-1038

Dayton, Ohio 45401                         ...!uunet!meaddata!lonnie

Assuming that all of your commits are there, what are you doing for dumping the log? If a lot of work is going on, you may have to dump it more often. A simple script which dumps the log n times a day and timestamps them would certainly work. As others have pointed out, though, if you don't got commits, you're stuck.

Topher
---
Christopher A. White          | "I once could see,
McCaw Cellular Communications |      but now, at last, I'm blind."
Kirkland, Wa                  |         -- Dream Theatre

Quote:>- Switch on the PB option (not sure what it's called) that forces a login and
>immediate logoff from the server before and after each unit of work.

I think it's in the docs about SetTrans() and SetTransObject().

--

Vincent Q. Yin

Quote:> Remember, when you dump a transaction, it takes
>only up to before the oldest outstanding tran in the log and removes all
>others.  

????? I thought Sybase will dump all committed transactions even if there
are outstanding trans before/after them.  In other words, it is possible
that, after dump tran, the log storage will have "holes".  I might be wrong.

Quote:>It is possible that you have forgotten to issue commits on a screen-by
>screen basis.  

As the user log out, those transactions will be rolled back by Sybase (or,
actually, committed by PowerBuilder.  I think PB commits the transaction
upon "disconnect;" statement.) Therefore, I don't quite expect that what you
guessed could be the real problem of filling up log space.
--

Vincent Q. Yin

Commits should be done on a unit-of-work basis.  You should never allow
user interaction within a transaction.  Say you do an update and then
wait for the user to close the window but the user goes to lunch, that
would leave the database locked!   Also, if sql server returns an error
and you display a messagebox, make sure you rollback before displaying
the error.  While the user's going to get the administrator with the
messagebox up on the screen, the database is locked up.
BTW, if you have PowerBuilder's Autocommit variable on, then you don't
need to explicitly issue the rollbacks and commits.
--

  ^0 0^  __                          //   : air /  ikitai desu yo  //
  ( v )      Woof.                 // .ri :t. /                  //
    U    (or is it Moo?)         //  ght  : /  BRAIN MODERNITY //

This is generally not a good idea because logging in to the server is
the most expensive (time-consuming) database operation.  Doing it this
way severely degenerates performance.  
--

  ^0 0^  __                          //   : air /  ikitai desu yo  //
  ( v )      Woof.                 // .ri :t. /                  //
    U    (or is it Moo?)         //  ght  : /  BRAIN MODERNITY //

Yes, PB automatically commits upon disconnect... but, what if the users
are "untrained" and, at the end of the day, simply shut off their
computer without exiting nicely?  Any uncommitted transactions will be
left*.
--

  ^0 0^  __                          //   : air /  ikitai desu yo  //
  ( v )      Woof.                 // .ri :t. /                  //
    U    (or is it Moo?)         //  ght  : /  BRAIN MODERNITY //

The assumprion so far on this thread has been that the user terminates
the application normally.  What I have seen happen is that user will
turn off the PC, sometimes in the middle of of a transaction.  The
server does not recognize that the connection is dead and does not
free up the resources (connection, locks, etc).  The only alternative
I have found at that point is to 'shutdown with no_wait' and restart
the server.  This is very disruptive in a production environment.

I have suggested that the AUTOCOMMIT option of PB be torned on
whenever possible, which reduces the size of a transaction, but there
are times where this is not feasable.

I have seen referenced a Solaris patch that is related to tcp
timeouts.  I have lost my reference to it.  It seems that Solaris 2.2
does not recognize a dead connection.  The patch will allow it to
recognize the dead connection.  The timeout duration can be set low
(15 min) and some of these problems may go away.
--
Steve Baker (314)530-1981
Technology Applications, Inc.

I cannot commrnt directly on PowerBuilder but I have seen a few
problems which are similar:

1. Browse lockups.  Application execs a query and retrieves the first
20 rows to show the user.  The remaining rows in the result are left
pending and therefore locked.  In this case the unit of work (a select)
is left waiting for the user to eventually scroll to the end of the
list to finish the query.  This can cause blocks on tables and cause
the tran log to grow.  As always you should never allow a unit of work
to depend on user interactions.

2. TCP hangs.  The PC is turned off or rebooted with another LAN
protocol while Sybase is performing work for that system.  The default
"keep alive" probes and TCP timeouts are set to several hours.  These
should be reduced in most cases to detect and close dead TCP connections.
When the connection is closed Sybase can close it's connection.  This
generally is a probem with PC's since most Unix workstations are either
left running or are shutdown so that all external connections are closed
normally.  They also restart IP services on startup and will clear any
sockets which are probed with a "keep alive".

--
Ralph Griesenbeck                       Fusion Systems Limited

+44/0 71 377 9009 (voice) / 0679 (fax)         London   E1 6DY
--------------------------------------------------------------

Amen.

Quote:>I have seen referenced a Solaris patch that is related to tcp
>timeouts.  I have lost my reference to it.  It seems that Solaris 2.2
>does not recognize a dead connection.  The patch will allow it to
>recognize the dead connection.  The timeout duration can be set low
>(15 min) and some of these problems may go away.

I think I've got that post stored away (at work), but there's a bit of a
problem with that approach.  It makes the TCP keepalives short for all
TCP sessions.  Does this also hold true for NFS mounts?  I'd hate for my
hard-mounted directories not to be restored if it took me longer than
(say) 15 minutes to re-start the serving system...  Of course, with most
my UNIX systems automounted, it wouldn't really matter.  But, all my
PC-NFS clients would be wondering "what happened to system X"... and I'd
rather not have to deal with those sorts of needless questions.

Quote:>I think I've got that post stored away (at work), but there's a bit of a
>problem with that approach.  It makes the TCP keepalives short for all
>TCP sessions.  Does this also hold true for NFS mounts?  I'd hate for my

NFS uses UDP transmission protocol and likely has little to do with TCP.
NFS requests are handled by a number of "nfsd" processes on the host machine.
Those nfsd's are passive and don't care where the requests are from, I think.

Quote:>hard-mounted directories not to be restored if it took me longer than
>(say) 15 minutes to re-start the serving system...  Of course, with most
>my UNIX systems automounted, it wouldn't really matter.  But, all my
>PC-NFS clients would be wondering "what happened to system X"... and I'd

I have a related question here:

PC-NFS is expensive considered that you need a separate copy for each PC.
If we only want to run PowerBuilder or other Client/Server db related
apps (such as Q+E), we don't want the file services provided by PC-NFS.  We
only want to talk to Sybase on Sun with TCP.  Is there something like
WINSOCK.DLL on PC that could let us get rid of PC-NFS?  We tried it once
and couldn't get WDBPING.EXE to connect.

--

Vincent Q. Yin

Quote:>> >- Switch on the PB option (not sure what it's called) that forces a login and
>> >immediate logoff from the server before and after each unit of work.

Yes, that option is the SetTrans() rather than SetTransObject() function.  The problem
there is that you cannot remain connected throughout your session, and it increases
network traffic to have to log on and off multiple times.  That is really quite slow, and
your option should really be to connect once and issue commits whenever you modify.

--
Ken Rimple           |   "Let's do some crimes.  Like
Ardmore, PA          |   "buy sushi, and not pay!"