JOIN Conditions with Unicode and ASCII
I cannot stress the dangers of copying data from Excel or HTML and assuming that it’s ASCII. For example U+0040 is the unicode version of @. We ingested the unicode version and couldn’t see why a JOIN condition on the data table wasn’t working.
I looked at the source JSON ( a FHIR DSTU2 Group ) and loaded in VIM and used the following trick:
We ended up showing that our data table’s contents were different using:
SELECT HEX(RESOURCE_VALUE) FROM FHIR.DIM_GROUP 0A40 vs 40
Removing the ^M character in VIM / VI
When you have an unexpected bad data coalesce it. IF NULL, THEN the alternative in COALESCE
I had dupe data in my OLAP table, where the columnar data can be duplicated based on event id. (I loaded data 2x). I had to differentiate the data and remove the duplicates, so I assigned row_numbers over a partition ordered by.
I hope this helps you.
db2 "update (select OME.*, row_number() over(partition by IDN_EVENT_ID order by IDN_EVENT_ID) as rnk from X.OLAP OME) set APP_NM = rnk"
Then I removed using this.
db2 "DELETE X.OLAP OME WHERE APP_NM = 2"
I recommend the two-phase, as you can in theory run this en batch, or async, and double check, versus hope it works.
I lost my changelog tables and the lock table for Liquibase
I was able to Dump Change Log in Liquibase, and then load the resulting inserts into the changelog.