Page Comparison

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

...

Note: There is no "SM-" prefix,

COVERED HERE Tiger3 ETL Shell

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

Remedy: Follow the instructions  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 ...

COVERED HERE: Tiger3 ETL Shell

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

Diagnosis: 

Remedy:  Connect to "analytics" server using your favorite SSH tool and run the following line:

/home/unix/analytics/TigerETL3/runEtlAgent.sh Task task=etlObject agentName=analytics.tiger.agents.PdoStar db=analyticsetl 'delta=MillisDelta.loadFromDb(365d,0d)'

COVERED HERE: Procedure to refresh Analytics DWH when Product Names change

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');

Note: Also create new JIRA RPT ticket and assign to raymond to track the following:  Assign PdM-approved minimum requirement(s) for coverage met.  pdo_star5_aux will need product_goal assigned as well as sample_coverage_normalized and sample_coverage_unformatted set.  DCFM_RULES must be updated.  DCFM_Agent may need to be updated.  PDO_SEQ_STAR needs "Sample Coverage" calculated field updated.

STILL RELEVANT???  should this get wrapped and added to Tiger3 ETL Shell page?

...

Note: No COMMIT needed in this particular call - it's implicitly done for you. Just replace the XYZ with a flowcell barcode. Leave all other parameters there and unchanged.

COVERED HERE Tiger3 ETL Shell

Issue: How to prevent an entire FC from getting into ReadgroupMetadata (Analytics Blacklisting).

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

Note: Blacklist_timestamp is not null for blacklisted flowcells.

COVERED HERE Tiger3 ETL Shell

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

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

COVERED HERE Tiger3 ETL Shell

STILL HAPPENS. DIAGNOSIS & REMEDY NEEDS UPDATE.

...

OBSOLETE? IPI-61224 hasn't been active in years.

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 inIPI-61224and ticket reopened.

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

OBSOLETE?

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.

OBSOLETE?

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.

Want to see only the latest run for each ETL agent ? Just pass the "latest_run_only=true" parameter.

http://analytics-etl:8090/etl_runs/All?latest_run_only=trueCOVERED HERE Tiger3 ETL Shell

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

Remedy: Follow the instructions  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 ...

COVERED HERE: Tiger3 ETL Shell

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

Diagnosis: 

Remedy:  Connect to "analytics" server using your favorite SSH tool and run the following line:

/home/unix/analytics/TigerETL3/runEtlAgent.sh Task task=etlObject agentName=analytics.tiger.agents.PdoStar db=analyticsetl 'delta=MillisDelta.loadFromDb(365d,0d)'

COVERED HERE: Procedure to refresh Analytics DWH when Product Names change

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');

Note: Also create new JIRA RPT ticket and assign to raymond to track the following:  Assign PdM-approved minimum requirement(s) for coverage met.  pdo_star5_aux will need product_goal assigned as well as sample_coverage_normalized and sample_coverage_unformatted set.  DCFM_RULES must be updated.  DCFM_Agent may need to be updated.  PDO_SEQ_STAR needs "Sample Coverage" calculated field updated.

COVERED HERE Tiger3 ETL Shell

Issue: How to prevent an entire FC from getting into ReadgroupMetadata (Analytics Blacklisting).

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

Note: Blacklist_timestamp is not null for blacklisted flowcells.

COVERED HERE Tiger3 ETL Shell

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

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

COVERED HERE Tiger3 ETL Shell