Re: Amount of free log space
Jacob Salomon wrote:
>
> Hi family.
>
> This must be one off the most frequently asked questions on DSA but,
> incredibly, I could not find it in the FAQ, nor in the Informix
> techinfo
> page.
>
> I have a query below to find the total amount of free log space. In
> other words, logs that are backed up, in use (not new) and have no
> transactions active in them. i.e. in OnLine 5 these would have been
> freed up already. The query is interested only in the number of free
> pages in the total of these free logs.
>
> ---------------------------------
> select sum(size)
> from syslogs
> where is_used = 1
> and is_backed_up = 1
> and uniqid
> < (select min(tx_logbeg)
> from systrans
> where tx_logbeg > 0
> )
> ;
> ---------------------------------
> This worked a coupe of times for me but one time it gave me 1 row with
> a
> null value. Why? well, the above has one flaw: If there are no
> transactions in the system, the subquery returns no rows; hence the
> sum
> from the outer query is null.
>
> Surely the query I've seen so many times has accounted for this!
You mean:
SELECT SUM(size)
FROM syslogs
WHERE uniqid < ( SELECT MIN(tx_loguniq)
FROM systrans
WHERE tx_loguniq > 0
)
? Although thaqt doesn't include the current log.
For more detail, try:
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)
Hope that helps,
--
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"!|/ ////////|
+----------------------+-----------------------------------+-----------+