DB2 Select Statement – How to Select Every Week’s Log Record

db2selecttimetimestamp

Here is a simplified version of the table in question:

+------------------------------------------------------+
| DatabaseName | LogRecordNumber | LogTimestamp        |
+------------------------------------------------------+
|DB1           | 47378910        | 2014-11-12 10:40:00 |
|DB1           | 48268418        | 2014-11-12 10:41:00 |
|DB2           | 223781480       | 2014-11-12 10:40:00 |
|DB2           | 228976522       | 2014-11-12 10:41:00 |
+------------------------------------------------------+

We have a table that contains the log record number for each database captured every minute. This data spans back a full year, or shorter if the database is newer than that.

We have to watch the log record number to ensure it will not go over the maximum number of bits that it can have. Currently I'm tracking this information with a simple query and adding it into a spreadsheet.

Sample Query:

SELECT Databasename, MAX(LogRecordNumber), LogTimestamp
FROM MyDB.MyTable
WHERE LogTimestamp BETWEEN '2014-11-12-04...' AND '2014-11-12-08...'
GROUP BY DatabaseName, LogTimestamp

I would like to instead develop a report that a technician could run to determine through stat regression when the log records would be out of bits.

So I now need a select statement that would retrieve the LogRecordNumber for each interval of time where we have a log record. Getting the log number once a week would be appropriate and allow me to make a reasonable regression guess at when we will be out of bits.

Again these data can be as far as a year back or as soon as a few months ago, so hard coding something like DATE(CURRENT_TIMESTAMP) – X WEEKS is not possible.

Is there any way to do what I'm suggesting with a query, or do I need to rethink this?

If it matters, the database in question is DB2.

Best Answer

It is possible do this in SQL. There are really 2 problems here:

Find 1 record per week for each database
Calculate the average rate of consumption

For the first problem, you have a couple of ways to do this:

select databasename, logrecordnumber, logtimestamp
  from MyTable
 where logtimestamp > current timestamp - 42 days
   and dayofweek(logtimestamp) = 0
   and time(logtimestamp) between '11:59:30' and '12:00:30'

This looks at only records in the last 6 weeks, and then finds records that occurred on Sunday (dayofweek() = 0) at 12:00. (Using the between predicate should handle minor differences in clocks on your database servers and/or instances when your script doesn't execute exactly on the minute.

It may also be possible to write this using OLAP windowing functions, but I'm not sure it would be any more efficient.

For the second problem you may want to look at using the LAG() function so you can calculate the delta in the log record:

select 
    databasename
   ,logrecordnumber
   ,logtimestamp
   ,logrecordnumber - lag(logrecordnumber,1) over (partition by database order by logtimestamp) as logrecordnumber_growth
from 
    MyTable
where 
    logtimestamp > current timestamp - 42 days
    and dayofweek(logtimestamp) = 0
    and time(logtimestamp) between '11:59:30' and '12:00:30'

This example adds a calculation showing how much the logrecordnumber has grown since the week before.

Doing the rest of the calculation (extrapolating to when LSNs might run out) is left as an exercise for the OP.

Related Solutions

DB2: The transaction log for the database is full, But there is no ‘First active log file’

You really should be reading manuals, not "pruning logs". Your database is set up for circular logging, which means there is nothing to "prune". If transactions occasionally require extra log space, increase the value of LOGSECOND. Too many primary logs will slow down the database startup, as they are preallocated. Secondary logs are allocated as needed, they will remain in the active log directory until the database is restarted.

"Pruning logs" will not affect the amount of active log space (with one exception that I will mention later): it is dictated purely by the sum of LOGPRIMARY and LOGSECOND, multiplied by LOGFILSIZ. If you were using the archive log mode, you would be able to remove older archived log files to free up some disk space, but the amount of active log space would not change. Even then, you would delete logs created prior to the previous backup, not prior to the first active log file.

Only if your archived logs and active logs share the same file system (which is not a good idea in itself), the uncontrolled accumulation of archived logs may prevent the active log space to grow, when necessary, to its configured maximum size.

Mysql – Select latest record for each ID

;with [Users](Id,Name,Other_Columns)
AS(
    SELECT 1,'John','Blah' UNION ALL
    SELECT 2,'Ricky','Blah' UNION ALL
    SELECT 3,'Stella','Blah' UNION ALL
    SELECT 4, 'Bob','Blah')
,
Saldo (Id,[User_id],Saldo)
AS
    (SELECT 1,    3 ,       0.00 UNION ALL
    SELECT 2,    1,        9.00 UNION ALL
    SELECT 3,    2,         0.15 UNION ALL
    SELECT 4,    4,         3.50 )

,Payments(Id,[User_id],Amount,Paid_date)
AS
    (SELECT 1,2,10.00,'2014-09-01 08:10' UNION ALL
    SELECT 2,2,25.00,'2014-09-01 09:00' UNION ALL
    SELECT 3,4,100.00,'2014-05-10 12:47' UNION ALL
    SELECT 4,1,20.50,'2014-02-23 15:30')


SELECT
    ID
    ,Name
    ,Saldo
    ,[Last Payment]
FROM
(
    SELECT
        U.ID,U.Name,S.Saldo 
        ,LEFT(COALESCE(P.Paid_date,'0000-00-00'),10)  + ' ' + CAST( COALESCE(Amount,0) AS VARCHAR(50))  [Last Payment]
        ,ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY COALESCE(P.Paid_date,'0000-00-00') DESC) AS RN
        FROM
            [Users] AS U
            INNER JOIN Saldo AS S
            ON U.ID= S.[User_id]
            left JOIN Payments AS P
            ON P.[User_id] = U.ID
)X
WHERE X.RN =1

output:

ID  Name    Saldo   Last Payment
1   John    9.00    2014-02-23 20.50
2   Ricky   0.15    2014-09-01 25.00
3   Stella  0.00    0000-00-00 0.00
4   Bob 3.50    2014-05-10 100.00

Best Answer

Related Solutions

DB2: The transaction log for the database is full, But there is no ‘First active log file’

Mysql – Select latest record for each ID

Related Question