¿Qué job está integrando en mi ambiente?
Si su ambiente tiene habilitada la integración por botón, entonces puede revisar ejecutando la siguiente consulta a la BD_ENV:
SELECT * FROM sec_features_permissions WHERE ds_job_name<>»
Si su ambiente tiene integración programada, entonces puede revisar en el etc\crontab de su ambiente carte.
¿Cuáles son los nombres correctos de layout y columnas?
########### Columnas correctas para layout Campus
SELECT DISTINCT
l.id_code as layoutId,
plf.id_code,
l.ds_code as bd_tablename,
l.ds_name as csv_filename,
plf.ds_fieldName as bd_fieldname,
plf.ds_fieldName_public as csv_fieldname,
max(plf.is_mandatory) as mandatoryfield,
is_key as keyfield,
plf.ds_fieldType as fieldtypeFROM etl_layout_productversion_fields plf
JOIN etl_productversions pv ON pv.id_code=plf.id_productversion AND pv.is_active=1 AND plf.is_active=1
JOIN etl_products p ON p.id_code=pv.id_product AND p.is_active=1
JOIN etl_layouts l ON l.id_code=plf.id_layoutWHERE l.ds_code=»campus»
GROUP BY ds_fieldName;
¿Mi ambiente usa vistas o layouts?
Revise el parámetro INTEGRATION_METHOD en etl_transformations de la BD_STG
¿Cuál es el estado de la última integración?
Ejecute la siguiente query
############ MASTER LOGS
WITH etl_log_steps_TRANSPOSED AS (
SELECT ID_BATCH,
MAX(if(STEPNAME like «%Registros Leidos%»,lines_written,0)) AS READED,
MAX(if(STEPNAME like «%Registros Insertados%»,lines_written,0)) AS WRITTEN,
MAX(if(STEPNAME like «%Registros Actualizados%»,lines_written,0)) AS UPDATED,
MAX(if(STEPNAME like «%Registros Inactivados%»,lines_written,0)) AS INACTIVATED,
MAX(if((STEPNAME like «%no actualizar!%» OR STEPNAME like «%Registros sin Cambios%»),lines_written,0)) AS NOTHING_TO_UPDATE,
MAX(if(STEPNAME like «%Registros con Error%»,lines_written,0)) AS ERROR,
MAX(if(STEPNAME like «%incremental!%»,lines_written,0)) AS SKIPPED
FROM etl_log_steps GROUP BY ID_BATCH),
etl_channel_execution AS (
SELECT
ID_BATCH,
CHANNEL_ID,
SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_FIELD,»id_execution = «,-1),»\n»,1) AS EXECUTION_ID
FROM etl_log_transformations),
etl_layouts_to_integrate AS (
SELECT DISTINCT «stg» AS ds_type,
1 as is_master,
id_code,
CONCAT(«to_stg_load: [«,ds_name,»]») AS ds_name,
nm_order AS nm_order_layout,
1 AS nm_order_transformation
FROM etl_layouts WHERE is_active=1
UNION
SELECT DISTINCT «upl» AS ds_type,
etl_t.is_master,
etl_t.id_code,
SUBSTRING_INDEX(SUBSTRING_INDEX(etl_t.ds_url,»/»,-1),»\.»,1) AS ds_name,
etl_l.nm_order AS nm_order_layout,
etl_t.nm_order AS nm_order_transformation
FROM etl_layouts etl_l
LEFT JOIN etl_transformations etl_t on etl_t.id_layout=etl_l.id_code
WHERE etl_l.is_active=1 and etl_t.is_active=1 and etl_t.ds_type in («UPL»,»CORR»)
ORDER BY ds_type, nm_order_layout,nm_order_transformation
),
etl_transformations_channels AS (
SELECT DISTINCT
lt.ID_BATCH,
lt.CHANNEL_ID,
SUBSTRING_INDEX(jc.FILENAME,»/»,-2) AS FILEPATH,
IF(lt.TRANSNAME=’to_stg_load’,CONCAT(«to_stg_load: [«,SUBSTRING_INDEX(SUBSTRING_INDEX(lt.LOG_FIELD,»\\»,-1),»\'»,1),»]»),IFNULL(SUBSTRING_INDEX(jc.FILENAME,»/»,-2),lt.TRANSNAME)) as STEP_PATH,
IF(lt.TRANSNAME=’to_stg_load’,CONCAT(«to_stg_load: [«,SUBSTRING_INDEX(SUBSTRING_INDEX(lt.LOG_FIELD,»\\»,-1),»_»,1),»]»),lt.TRANSNAME) as STEP_NAME,
lt.`STATUS`,
lt.ENDDATE AS STARTDATE,
TIMEDIFF(lt.LOGDATE,lt.ENDDATE) AS TIME,
IF(lt.`STATUS`<>»end»,lt.LOG_FIELD,»») AS LOG_ERROR
FROM etl_log_transformations lt
LEFT JOIN etl_log_job_channel jc ON jc.CHANNEL_ID=lt.CHANNEL_ID AND jc.LOGGING_OBJECT_TYPE=’TRANS’)
SELECT DISTINCT
che.EXECUTION_ID,
tch.ID_BATCH,
tch.STEP_PATH,
tch.`STATUS`,
tch.STARTDATE,
tch.TIME,
lst.READED,
lst.WRITTEN,
lst.UPDATED,
lst.INACTIVATED,
lst.ERROR,
lst.SKIPPED,
lst.NOTHING_TO_UPDATE,
tch.LOG_ERROR
FROM etl_transformations_channels tch
INNER JOIN etl_log_steps_TRANSPOSED lst ON lst.ID_BATCH=tch.ID_BATCH
INNER JOIN etl_channel_execution che ON che.ID_BATCH=tch.ID_BATCH
LEFT JOIN etl_layouts_to_integrate lti ON lti.ds_name = tch.STEP_NAME
— WHERE lti.is_master IS NULL OR lti.is_master=1
— WHERE che.EXECUTION_ID in (19)
¿Cuánto están tardando las integraciones?
Ejecute la siguiente query:
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_FIELD,»id_execution = «,-1),»\n»,1) AS EXECUTION_ID,
REPLAYDATE as EXECUTION_DATE,
TIMESTAMPDIFF(MINUTE,REPLAYDATE,LOGDATE) AS RUNTIME
FROM etl_log_job
WHERE JOBNAME like «%job_principal_multiproduct_child%» AND LOG_FIELD LIKE «%id_execution =%»