{{indexmenu_n>1}}
===== SQL Mantenimientos =====
==== CONTROL ERRORES ====
\\
/* Vemos las operaciones que hay en el iqueue por procesar */
select count(id),mid from master_replica_iqueue group by mid;
/* La primera que devuelva la consulta es la que falla */
select * from master_replica_iqueue where mid='317' order by id asc;
/* Ordenando descendentemente vemos los últimos "errores" que pueda tener el dispositivo. */
select * from master_replica_errorlog where mid='317' order by id desc;
==== SELECTIVIDAD ====
/* ********************************* */
/* SELECTIVIDAD */
/* ********************************* */
/* Vemos las operaciones que faltan por analizar la selectividad */
select (select max(id) from master_replica_queue)-lastcid as pendientes,mid, dirip
from master_replica_slave where pendiente=0 order by pendientes desc;
==== ESTADO DISPOSITIVOS ====
/* Estado de operaciones en un MYSQL con borrado de operaciones del SQUEUE */
SELECT sl.MID, cmd.CMDTIME as 'ULTIMA_CONEXION', sl.SINCRONISMO,
(select timestamp from master_replica_queue where mid=sl.mid order by id desc limit 1) as 'ULTIMA_OPER_DESDE_DISPOSITIVO',
x.num as 'OPER_PEND_DESCARGA',sl.Batchsize from master_replica_slave sl
left join master_replica_cmdlog cmd ON sl.MID=cmd.MID
left join (select count(*) as num,mid from master_replica_squeue
group by mid) x ON sl.MID=x.MID where sl.pendiente=0 and sl.MID>1
order by x.num desc;
/* Estado de operaciones en un SQLSERVER si se van borrando las operaciones del SQUEUE cuando los dispositivos replican */
SELECT sl.MID, cmd.CMDTIME as 'ULTIMA_CONEXION', sl.SINCRONISMO,
(select top 1 timestamp from master_replica_queue where mid=sl.mid order by id desc) as 'ULTIMA_OPER_DESDE_DISPOSITIVO',
x.num as 'OPER_PEND_DESCARGA',sl.Batchsize from master_replica_slave sl
left join master_replica_cmdlog cmd ON sl.MID=cmd.MID
left join (select count(*) as num,mid from master_replica_squeue group by mid) x ON sl.MID=x.MID where sl.pendiente=0 and sl.MID>1 order by x.num desc;
/* Estado de operaciones en un SQLSERVER SIN borrado de operaciones del SQUEUE (cuando replican se pone el SENT=1) */
SELECT sl.MID, cmd.CMDTIME as 'ULTIMA_CONEXION', sl.SINCRONISMO,
(select top 1 timestamp from master_replica_queue where mid=sl.mid order by id desc) as 'ULTIMA_OPER_DESDE_DISPOSITIVO',
x.num as 'OPER_PEND_DESCARGA',sl.Batchsize from master_replica_slave sl
left join master_replica_cmdlog cmd ON sl.MID=cmd.MID
left join (select count(*) as num,mid from master_replica_squeue
where sent is null or sent=0 group by mid) x ON sl.MID=x.MID where sl.pendiente=0 and sl.MID>1 order by x.num desc;
==== MANTENIMIENTO TABLAS ====
/* Eliminar operaciones de log */
DELETE FROM adm_xonelive_logs WHERE STR_TO_DATE(TIMESTAMP, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY); -- En ORACLE
DELETE FROM adm_xonelive_errorlog WHERE STR_TO_DATE(TIMESTAMP, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY);
DELETE FROM adm_provisioning_task WHERE STATUS=2 AND STR_TO_DATE(started, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY);
DELETE FROM adm_provisioning_logs WHERE STR_TO_DATE(logcreated, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY);
DELETE FROM interclient_error_log WHERE STR_TO_DATE(errortime, '%d/%m/%Y')<=DATE_SUB(CURDATE(),INTERVAL 15 DAY);
TRUNCATE TABLE master_replica_errorlog;
/* Ajustar al numero de operaciones diarias que tenga el proyecto */
INSERT INTO MASTER_REPLICA_QUEUE_HISTORY (ROWID,OPERID,TIMESTAMP,OPER,MID,CGSSQL,DMID,IDORIGINAL,TBL)
SELECT ROWID,OPERID,TIMESTAMP,OPER,MID,CGSSQL,DMID,ID,TBL FROM MASTER_REPLICA_QUEUE WHERE DMID IS NULL AND
id NOT IN (SELECT QID FROM MASTER_REPLICA_SQUEUE) AND ID<(SELECT MIN(LASTCID) FROM MASTER_REPLICA_SLAVE WHERE CONDITIONAL=1 AND PENDIENTE=0);
DELETE FROM MASTER_REPLICA_QUEUE WHERE DMID IS NULL AND
ID NOT IN (SELECT QID FROM MASTER_REPLICA_SQUEUE) AND ID<(SELECT MIN(LASTCID) FROM MASTER_REPLICA_SLAVE WHERE CONDITIONAL=1 AND PENDIENTE=0);
/* Borramos las operaciones del SQUEUE de los dispositivos que estén desactivados */
DELETE FROM MASTER_REPLICA_SQUEUE WHERE MID IN (SELECT S.MID FROM MASTER_REPLICA_SLAVE S WHERE S.PENDIENTE=1);
/* Borramos del SQUEUE las operaciones que ya se hayan enviado a los dispositivos */
DELETE FROM MASTER_REPLICA_SQUEUE WHERE SENT=1;