ETL troubleshooting recipes (To make changes permanent in DB don't forget to issue COMMIT at the end.)

Issue: Repatienting (rename externalID well after work on sample has started)

System: GAP

Remedy: Request a refresh for Sample_Attributes datamart by issuing SQL statement like this in GAP_PROD.ANALYTICS:

INSERT INTO sample_attr_special_refreshes

SELECT 'SM-' || a.sample_id, SYSDATE 

FROM sample_attributes a

WHERE a.collaborator_sample_id IN ('old_externalID_1','old_externalID_2')

Issue: When upstream data change, some BSP plating info in BSP_PLATED_SAMPLES might need to be refreshed.

Remedy:  Request a refresh for BspPlatingRequestAgent by issuing SQL like this in GAP_PROD.ANALYTICS:

INSERT INTO bsp_plating_special_refreshes VALUES('6E3K2', SYSDATE);

Note there is no "SM-" prefix,

Issue: When upstream data change, some samples in PDO_STAR5 might need to be refreshed.

Remedy: Follow the instructions  here for Manual refresh of PDO Star5 using a query. The query should have the format below:

SELECT DISTINCT a.pdo_name||','||a.pdo_sample_id FROM ... a WHERE ...

Request a refresh for PdoStar5Agent by issuing SQL like this in SEQPROD.COGNOS:

INSERT INTO pdo_star_special_refreshes VALUES('PDO-1', 'SM-12345', sysdate);

Issue: How to make PDO_STAR5 ETL pick a new product ?

Remedy: Add a new "interesting" product by issuing SQL like this in SEQPROD.COGNOS. Provide part number.

INSERT INTO pdo_star_interesting_products values('P-123456');

Issue/Error: Once a week (1am on Fri) PdoStar5 goes through 1y-refresh. Sometime you might want to trigger it earlier.

Diagnosis: 

Remedy:  Follow the instructions  here for Manual refresh of PDO Star5 using a query. Make sure the query filters last year's worth of PDOs

Connect remotely to "analytics-etl" and run following command. Make sure that regular schedule will not interfere with your run (which takes ~50 minutes)

C:\AnalyticsETL\runEtlScript.bat C:\AnalyticsETL\scripts\PdoStarEtl.scala "1y-refresh=true"

Issue: When upstream data change, some samples in ReadgroupMetadata might need to be refreshed.

Remedy: Request a refresh for RunMercuryAgent by issuing SQL like this in SEQPROD.COGNOS:

BEGIN

 cognos.data_warehouse_api.request_refresh(P_FLOWCELL_BARCODE=>'XYZ', P_REQUESTED_BY=>'JohnDoe', P_COMMENTS=>'Test', P_DEFERRAL_ANALYSIS_DATE=>null);

END;

No COMMIT needed in this particular call - it's implicitly done for you.

Issue: How to prevent an entire FC from getting into ReadgroupMetadata.

Remedy: Follow the instructions  here for blacklist runfolder from RunMercury/RghqsLoader agents

Blacklist it by issuing SQL like this in SEQPROD.COGNOS:

UPDATE slxre_rghqs_targets a SET a.blacklist_timestamp=SYSDATE WHERE a.run_name='run123'

Issue: How to whitelist a FC for processing by Run Mercury and RGHQS agents.

Remedy: Follow the instructions  here for whitelist runfolder from RunMercury/RghqsLoader agents

Issue: Scala ETL ERROR: analytics.etl.DeckEventAgent$ (Most likely 'lcSet' tag is missing)

Diagnosis: Sometimes this Squid web-service returns no LcSet. Comment about this incident is automatically posted in IPI-61224 and ticket reopened.

Remedy: Just follow up with LIMS team. Our DeckEventAgent will catch up as soon as LIMS service is fixed. No other intervention is needed.

Issue: analytics.etl.PkViolationWarning. unique constraint (COGNOS.RGHQS_METADATA_PK) violated

Diagnosis: This happens when 2+ readgroups having same (fc, lane, mol_index) try to get into datamart. Picard calls it "CLASH". RunMercuryAgent would ignore abusing readgroups and move on with the rest of 'good' ones. Email would give you detailed troubleshooting info.

Remedy: There isn't much for us to do. If upstream issues are resolved you can request a ReadgroupMetadata refresh for the whole run/fc (see above). You can search for the RG in GPINFOJIRA by searching the FC ID to see if it has been blacklisted and already handled upstream.

Issue/Error: ETL: DB error, server: (thorium or vorarestore), Error: ORA-12008: error in materialized view refresh path, ORA-25153: Temporary Tablespace is Empty

Diagnosis: This happens when the DB team is doing some kind of backup or transfer.

Remedy: These errors can be ignored. Everything is actually fine.