Re: How does one count free logical logs in 7.23?
SaTriGuy wrote:
>
> >A company I work for uses INFORMIX Vn. 7.23. As one of
> >its future administrators, I have a problem with checking
> >how full the logical logs are, since {onstat -l} now says
> >all logs but the current one are "used" (and 100% "full"),
> >once they have been used at all since the initialisation
> >of the server. Under previous versions {tbstat -l} spoke of
> >"free" and "used" logs, and we could prevent problems by
> >occasionally checking how many logs were still free at any
> >given time.
>
> Yep --- I know it sounds really silly, but it really is a feature.
>
> Here's the scoop.
>
> In the past, Informix distinguished between a backed-up log and a free
> log. We would mark the log as "free" during transaction commits
> and/or
> rollbacks if that log file had been backed up, it contained no other
> active
> transactions, and no prior log files containd any active transactions.
> Basically all we did was to re-initialize the log header information
> in the
> reserved pages so that it was ready for re-use.
>
> However, this presents a problem for replication. Suppose that for
> some
> reason, replication is running behind. Well, we need a way to know
> wheither the replication threads can transfer data from the log files
> to a
> replicate. What happens is that if the log file is marked as in-use,
> then
> replication (both HDR and CDR) can replicate from it. If instead it
> does
> not, then the replication threads can not.
>
> So -- what's a guy supposed to do to know just how close he is to a
> long
> transaction?
>
> (I may need some help with this because I don't have access to an
> instance
> where I at right now.)
>
> Basically, by taking the oldest begin log entry from the
> systransactions
> table within sysmaster database, you can determine which log files are
> similar to the "free" status of old. If the log file has a lower
> unique id
> than the lowest entry from systransactions, then that log file is
> "free".
>
> We've had several sample queries that could be run against sysmaster
> database in the past concerning this problem. Maybe someone would be
> willing to post one of the them again.
You mean:
SELECT uniqid, (used/size*100) used, (size-used) free
FROM syslogs
WHERE uniqid >= (
SELECT MIN(tx_loguniq)
FROM systrans
WHERE tx_loguniq > 0
)
UNION
SELECT uniqid, 0.00, size
FROM syslogs
WHERE uniqid < (
SELECT MIN(tx_loguniq)
FROM systrans
WHERE tx_loguniq > 0
)
Or for the total space:
SELECT (
SELECT SUM(size-used)
FROM syslogs
WHERE uniqid >= (
SELECT MIN(tx_loguniq)
FROM systrans
WHERE tx_loguniq > 0
)
)
+
(
SELECT SUM(size)
FROM syslogs
WHERE uniqid < (
SELECT MIN(tx_loguniq)
FROM systrans
WHERE tx_loguniq > 0
)
) free
FROM systables
WHERE tabid=1
Oooouch! Yes, well, it DOES save a temp table. :-|
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
|Mark D. Stock - Informix SA http://www.informix.com |//////// /|
|mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //|
| +-----------------------------------+//// / ///|
| Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////|
| Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////|
|Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////|
+----------------------+-----------------------------------+-----------+