{{indexmenu_n>1}}
===== SQL Mantenimientos =====

==== CONTROL ERRORES ====
\\
<code sql>
/* 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;

</code>

==== SELECTIVIDAD ====
<code sql>
/* ********************************* */
/*           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;

</code>

==== ESTADO DISPOSITIVOS ====
<code sql>

/* 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;
</code>

==== MANTENIMIENTO TABLAS ====
<code sql>
/* 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;
</code>