With the post festive system haze still on \u2013 I spent a full day trying to work out another encoding problem that came up in our exception table.<\/p>\n\n\n\n
Let me take you on a journey.<\/p>\n\n\n\n
It starts with MS-SQL.<\/h2>\n\n\n\n
We have Qlik Replicate replicating data from a DB2\/zOS system into a MS-SQL system. I was investigating why there were inserts missing from the target table and exploring the dbo.attrep_apply_exceptions table I notice a bunch of INSERTS from another table in the exception table:<\/p>\n\n\n
\n\/*\nCREATE TABLE dbo.destination\n(\n\tadd_dte numeric(5, 0) NOT NULL,\n\tchg_dte numeric(5, 0) NOT NULL,\n\tchg_tme varchar(6) NOT NULL,\n\targmt varbinary(10) NOT NULL,\n\tCONSTRAINT pk_destination PRIMARY KEY CLUSTERED\n\t(\n\t\targmt\n\t) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY]\n) ON [PRIMARY]\n*\/\n\nINSERT INTO [dbo].[destination] ( [add_dte],[chg_dte],[chg_tme],[argmt]) VALUES (46021,46021,'151721', 0x35333538C3A302070000);\n<\/pre><\/div>\n\n\nI thought it would be a good place to start by substituting the failed insert into a temp table and using the temp table to investigate downstream data flows.<\/p>\n\n\n\n
So:<\/p>\n\n\n
\nDROP TABLE IF EXISTS ##temp_destination;\n\nSELECT *\nINTO ##temp_destination\nFROM [dbo].[destination]\nWHERE 1 = 0;\n\nINSERT INTO ##temp_destination ( [add_dte],[chg_dte],[chg_tme],[argmt]) VALUES (46021,46021,'151721', 0x35333538C3A302070000);\n<\/pre><\/div>\n\n\nBut as I was substituting the temp table into objects downstream – I was just getting logical errors. Comparing the temp table against other data that already existed dbo.destination; it looked not right<\/em>.<\/p>\n\n\n\nWorking Upstream<\/h2>\n\n\n\n
I thought it would be a good place to start by substituting the failed insert into a temp table and using the temp table to investigate downstream data flows.<\/p>\n\n\n\n
Since we just merged a system into our DB2\/zOS system; my initial thought it was a corrupted record coming from a merge process. I contacted the upstream system owners and asked for help.<\/p>\n\n\n\n
They came back and said the record was fine and the issue must be in the QR system converting the data wrong. This did not make sense – if there was a issue in the QR system; it would have been picked up in testing. Also, there would be lots of exceptions in dbo.attrep_apply_exceptions. I had confidence that QR was handling it right.<\/p>\n\n\n\n
Not trusting what the DB2\/zOS team had told me; I created a temp Qlik task to dump out that table into a test database.<\/p>\n\n\n\n
Using the other values in the record; I could look the record up in the table and compare the two.<\/p>\n\n\n\n
For the field argmt it came back as:<\/p>\n\n\n\n
0xF5F3F5F846022F00000F<\/pre>\n\n\n\nThis looked nothing like what was in the exception table.<\/p>\n\n\n\n
EBCDICy epiphany <\/h2>\n\n\n\n
Of course – it took a while; but it finally struck what was going on.<\/p>\n\n\n\n
The field argmt had sections encoded in EBCDIC. This was getting brought across as bytes to MS-SQL. There was a downstream trigger that was failing in the MS-SQL database. But when it was getting written out into the exception table; it was getting written as ASCII (or possibly Windows-Latin) encoding.<\/p>\n\n\n\n![\"\"](\"https:\/\/craftcookcode.com\/wp-content\/uploads\/2026\/01\/code_ebcdic_conv.png\")
<\/figure>\n\n\n\n(Only showing start of bytes as this is what we use downstream)<\/p>\n\n\n\n
With me just copying the insert statement and using it as is; I was treating ASCII bytes as EBCDIC; causing all sorts of logical errors downstream.<\/p>\n\n\n\n
Where to from here?<\/h2>\n\n\n\n
I’m going to raise this abnormality with Qlik. I am not sure if it is a “bug” or a “feature”; but good to get clarification on that point.<\/p>\n\n\n\n
I also will need to educate our users on how to interpret the values in dbo.attrep_apply_exceptions so not to stumble into mistakes that I did.<\/p>\n\n\n\n
As for fixing the data itself – since we have the full dump of data from the table and the data is slow moving; I can substitute the values from the data dump into the problem INSERT statement.<\/p>\n\n\n\n
This should allow it to run just fine.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
EBCDIC – my perpetual nightmare Before I have written about EBCDIC; and the tribulations I have come across With the post festive system haze still on...<\/p>\n","protected":false},"author":1,"featured_media":1715,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[16,17],"tags":[102,36],"class_list":["post-1710","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-qlik-replicate","category-sql","tag-ebcidic","tag-qlikreplicate"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1710","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1710"}],"version-history":[{"count":4,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1710\/revisions"}],"predecessor-version":[{"id":1716,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1710\/revisions\/1716"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1715"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1710"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1710"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1710"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}