Trick from Walter Bright

 Yesterday I learnt a neat syntactical trick from Walter Bright founder of the D language.

do
{
    if (x) break;
    if (y) break;
    if (z) break;
    hotPath();
} while (0);

This is very useful if you like me often have more than one return from functions and subroutine. For many reasons you only want one return point. This little trick may come handy in some situations. 

I recommend the D language page https://dlang.org/ if you are interested in Programming in general. I found the discussions of the D language itself very interesting.    

New year new currency rates

Last year, actually last Friday we had an incident in the Data Warehouse. The currency rates for next month must be loaded otherwise the Data Warehouse stops. It litterary stops, you cannot do any updating at all, so updating the currency rates it a pretty important task. Now I have not been involved in this since 2013 and the task was handed over to a new guy on the job, he is located in India and that is important for this story. I thought of course this will go wrong, but I created the currency procedure so I can fix it this should not be a problem, but of course it was. 

This story begins in 2008, every month since we introduced currency rates in the Data Warehouse I had to manually copy the rates from an excelsheet pubished on a crap web site. Around 2008 I was fed up with this and created a procedure that use CURL to grab the Excel sheet and updated the Data Warehouse. This worked fine until I was on vacation when a summer stand-in at the finance added comments and rearrage the rates on the Excel.They call me from the office when I was on the autobahn in southern Germany, that is when I invented speed debugging. Back at the office I had a meeting with the finance and made them promise never ever change the Excel format of the currency rates without telling in advance. (It turned out noone else progmatically grabbed and interpreted the Excel sheet.) 

Later at 2016 when I was the enterprise lead information architect, I together  with a finance guy created a system grabbing the currency rates from Bloomberg and published them in MS sharepoint, which was and is the company's platform for data interchange. We were ready to go into production when the finance department responsible for currencies was moved over Seas. The new department manager stopped my new 'rate system'. (They created a currency system of their own. Better? My system you can see, the new system you will never see). Anyway the rates were published on Sharepoint and I created a Sharepoint API import system for the Data Warehouse. At that time I had been removed as an IT architect. The new information architects decided it was forbidden to fetch data directly from Sharepoint! Data must only be extracted by APIs developed by a consultant.  I found it a bit strange not to use Sharepoints own API for sharing, but OK I can use the new APIs. But the management of the Data Warehouse did not allow me to use the new API. I find it a bit comic I who was behind the idea and developed the APIs for programatically extract the currency rates was in the end the only one who was not allowed to use the currency rates API when it was finally put into production.

So here we were at the end of 2023 failing to update the currency rates using my old semi automatic system from long time ago. First problem, my old system transfers the rates to the data warehouse with FTP. Now at the end of year someone has firewallblocked the FTP transfer to the Data Warehouse. It took quite some time to realize this. If FTP does not work I had other ways to transfer the data to the data warehouse. Now we run the insert procedure and it failed,  now the Data Warehouse was blocked, if there is a problem with the currencies the Data Warehouse is blocked. W-T-F was the reason for the failure. After a long time I realised there was a problem with the input. At this point a colleague come to the rescue. Before sending the currency rates to the Data Warehouse the excelsheet is converted to a CSV file. My colleague told us you must use Swedish locale when doing the CSV conversion, something I had forgot, the (swedish) colleague sent me a correct CSV file. Now when we tried to insert the currecies we were told the Data Warehose was blocked due to invalid currencies. Of course we have a force option, I use the more colorful 'gogogo', adding gogogo we were able to insert correct currencies.

Now in hindsight, the 'currency procedure' is not that bad, but it is brittle. We have some work to do until next month's end. 

p.s.

The result of  last currency intake, (the last row  is the important one).

STATUS
DISTINCT_VALUES	1: AVG AND EOP HAVE DIFFERENT VALUES FROM LATEST PERIOD
NEW_RATES	WARNING: TOTAL NUMBER OF EXCHANGE RATES IS DIFFERENT FROM PREVIOUS PERIOD
MAX_EOP	1: EOP SINCE LATEST PERIOD HAS NOT INCREASED MORE THAN 20%
MIN_EOP	1: EOP SINCE LATEST PERIOD HAS NOT DECREASED MORE THAN 20%
MAX_AVG	1: AVG SINCE LATEST PERIOD HAS NOT INCREASED MORE THAN 20%
MIN_AVG	1: AVG SINCE LATEST PERIOD HAS NOT DECREASED MORE THAN 20%
MAX_EURX	1: EURX SINCE LATEST PERIOD HAS NOT INCREASED MORE THAN 20%
MIN_EURX	1: EURX SINCE LATEST PERIOD HAS NOT DECREASED MORE THAN 20%
DISTINCT_DATE	1: EOP,AVG,EURX ALL HAVE A NEW DATE DIFFERENT FROM LAST PERIOD
ACAB_DATE	1: ACAB HAVE NEW DATE FROM LATEST PERIOD
ACAB_VALUE	1: ACAB HAS DIFFERENT VALUE FROM LATEST PERIOD
BUD_DATE	1: BUD HAVE NEW DATE FROM LATEST PERIOD
BUD_VALUE	1: BUD HAS DIFFERENT VALUE FROM LATEST PERIOD
CURRENT_PERIOD	1: LATEST PERIOD IN EXCHANGE_RATE IS CORRECT

    

Yet another Year

This year was an eventful year for my Data Warehouse. A new guy Hans Mattson stepped in to help me running the Data Warehouse. In no time Hans became proficient in running the Data Warehouse, with his extreme analytical skills he solved some very complex problem we faced. 'You know I'm curious, I want to know how things works' he used to say. Unfortunatly his previous employeer realized how much they missed him and gave him an offer he could not refused. I'm happy for Hans, but I miss the almost daily chats we had about IT in general and Data Warehousing. Hans is more than a good IT guy who know his craft, he is also a very nice guy to work with.
(A new promising guy have just stepped in to replace Hans.)

I have started upgrade the Data Warehouse from PHP 7.4 to PHP 8.1, (the plan is to go to PHP 8.3). This turned out to be tidious and much harder than I had anticipated. PHP is not very PHPish anymore. All the nice features/defaults I used to silently take care of conversions, bad and buggy code now issues tons of warnings, deprications and errors. This upgrade takes a long long time, eventhough the code in general is pretty good and well documented, sometimes it is hard to remember the reason behind code written in PHP 4 twenty years ago. Upgrades to new PHP versions have been simple until now, (PHP 7.4 forced me to rewrite the entire SAP interface, apart from that very little problems). This time I will remove not only errors, but depreciations and warnings as well.
This year we upgraded MySQL to vs 5.7, next year the plan is to upgrade to vs 8.

Other things.
After years of procrastination I have now almost finalized Rey Valeza's D language web tutorial. It is a really great web programming tutorial, if you are an experienced programmer without 'web experience' and like to get your hands dirty this is a tutorial for you. Next I see if I can find a D language tutorial on creating a 'bare metal' program. I want to be a D programmer, I am not today, I cannot even read D code.

At last a happy new year😊

Import SAP data into MySQL with little effort

 This morning I stumbled upon a SAP import script written in my Integration Tag Language. The script is a little gem, it is probably written about 2008.

The script includes all code needed exporting data from a SAP rfc function into Mysql (including defining an import table in Mysql. I have removed parts not necessary for showing the complete export process from beginning to end.

The script starts defining  the workflow, run time parameter and symbolics:

(The run time parameter sap points to a SAP system.)

Next we have two jobs truncate & getSapData:

1 Truncate the result table if it exists (only for full load)

2 GetSapData (the little gem)

This job is packed with relevant and succinct logic for SAP data extraction.

(I added some comments for your convenience.)

That’s it.

The script is a complex parallel process expressed in concise and well defined code.

I have not written the script myself, but I’m proud of the Integrated Lag Language (ITL) which I wrote in 2006 that makes this script possible.

If you have a better way of importing data from SAP, please drop me a note.

If you are the script author you must give me a call🙂

Sevetieth birthday

Somethings you cannot avoid like haveing your sevetieth birthday. I just had mine, I just pretend it never happened.

A snapshot monitor for MySQL

I recently created a snapshop monitor for Mysql.

It gives nice snapshots every 15 minutes of Mysql (server) activities.

It is a useful tool for finding problems especially from night run as you can go back in time. 

The output consists of:

1 Memory stats of the server

2 Number of SQL queries since last snap

3 Processes running when snap is taken

A snapshot of the snapshop monitor output:

Fri Aug 19 03:31:10 CEST 2022              total       used       free     shared    buffers     cached Mem:         64383      63785        597          2          0      45306 -/+ buffers/cache:      18478      45905 Swap:        32767         75      32692 SQL queries since last snap 1011240 Id User Host db Command Time State Info 36790 KalleAnka toossedwvetl3-i:52859 TRANSFORM Query 1461 Sending data create temporary table LKLP_Selection (primary key(VBELN, POSNR)) as (\n selec 36798 KalleAnka toossedwvetl3-i:52868 RAW Query 4193 updating DELETE FROM TRANSFORM.Finance_Pricing WHERE Valid_From_Date >=  36848 KalleAnka toossedwvetl3-i:52930 TRANSFORM Query 56 copy to tmp table ALTER TABLE PTD_DTR_OL ADD INDEX (KUNNR, VKORG) 36967 manager %:53112 TRANSFORM Query 124 Creating sort index INSERT INTO TRANSFORM.Logistics_MaterialMovements_RollingQuantity_Periodic(\nSELECT\nA.W 36977 KalleAnka toossedwvetl3-i:53122 TRANSFORM Query 4175 Sending data INSERT INTO TRANSFORM.Sourcing_PurchaseOrders\nSELECT\nA.AFNAM as  36992 KalleAnka toossedwvetl3-i:53137 TRANSFORM Query 4170 Sending data INSERT INTO PPV_INVOICED_Temp (\n\t SELECT \n\t A.BELNR as  38395 KalleAnka toossedwvetl3-i:55708 TRANSFORM Query 2857 Sending data INSERT INTO TRANSFORM.PKBridgeMaterialCost  38554 KalleAnka toossedwvetl3-i:55976 ACTADW Query 2449 executing LOAD DATA LOCAL INFILE 'AFRU.CSV' replace INTO TABLE ZDTSQRYAFRU FIELDS B 38566 KalleAnka toossedwvetl3-i:55981 A…

 The bash code:

#!/bin/bash mv /home/tooljn/mymonlog/0mymon.txt "/home/tooljn/mymonlog/$(date '+%Y%m%d%H%M')-mymon.txt" find /home/tooljn/mymonlog -type f  -mtime +32 -delete while true do    date >> /home/tooljn/mymonlog/0mymon.txt    free -m >> /home/tooljn/mymonlog/0mymon.txt    mysql -e "show global status like 'Queries'" | grep Queries >> /home/tooljn/mymonlog/mymonQ.txt    topl="$(head -n 1 /home/tooljn/mymonlog/mymonQ.txt | grep -oE '[^[:space:]]+$')"    botl="$(tail -n 1 /home/tooljn/mymonlog/mymonQ.txt | grep -oE '[^[:space:]]+$')"    queries=$((botl-topl))    echo "SQL queries since last snap $queries" >> /home/tooljn/mymonlog/0mymon.txt    (mapfile -n 2 <  /home/tooljn/mymonlog/mymonQ.txt;    if ((${#MAPFILE[@]}>1)); then     echo "This file has more than 1 line."    fi)    mysql -e "show processlist" | sed '/Sleep/d'  | sed '/root/d' |  sed -e "s/[[:space:]]\+/ /g" | cut -c -150  >> /home/tooljn/mymonlog/0mymon.txt    echo "- - - - - - - - - - - - - - - - - - - - - - \n" >> /home/tooljn/mymonlog/0mymon.txt    sleep 15m done

Transpose away about 92 percent

It has been a long time since I wrote here about The Data Warehouse. I have spend time upgrading PHP from 5.6

to 7.4 and Mysql latin1 to utf8. None of this is in production yet. I also spent time on the long overdue Mysql from

5.6 to 5.7, which makes me go bonkers. In the old days when I had full control of the environment I just downloaded

and installed the MySQL version I wanted. Now I try to follow Ubuntu's rules, and I just fail, I can upgrade a test server,

but the prod server refuses to be upgraded. I have no clue what goes wrong. This is really the backside of complex system

procedures that intercept you from compromising the system, in this case the Ubuntu system. I'm not interested in digging into the

finer details of Ubutu's APT system, but it seems I will in the end.  

Anyhow I did some SQL tuning lately, this is boring laborious work, but at the same time satisfyingly rewarding.

Some weeks ago I stumbled upon this code: 

insert into MAEX_US (                SELECT             A.MATNR,             A.ALAND,             A.GEGRU,             A.ALNUM             FROM MASTERDATA.MAEX A             WHERE A.GEGRU='US'                   ); insert into MAEX_EU (                SELECT             A.MATNR,             A.ALAND,             A.GEGRU,             A.ALNUM             FROM MASTERDATA.MAEX A             WHERE A.GEGRU='EU'                   ); insert into MAEX_E1 (                 SELECT             A.MATNR,             A.ALAND,             A.GEGRU,             A.ALNUM             FROM MASTERDATA.MAEX A             WHERE A.GEGRU='E1'                   ); INSERT INTO LEGAL_REGULATION_BOM     SELECT              A.PLANT             ,A.MATNR             ,A.DESCRIPTION             ,A.EXT_MAT_GRP             ,A.MATNR_COST             ,A.MATNR_COST_CURR             ,A.PARENT             ,A.COMPONENT             ,A.BOM_LEVEL             ,A.QTY             ,A.COMPONENT_COST              ,A.COMPONENT_COST_CURR             ,A.UNIT_OF_MEASURE             ,A.COMP_DESCRIPTION             ,A.ORIGIN             ,A.DUTYNO             ,A.BOM_PLANT_IS_PTD_0101_ACQPLANT             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN B.ALNUM                  WHEN A.PLANT='6100' THEN C.ALNUM                 WHEN A.PLANT='6130' THEN D.ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN E.ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN N.ALNUM                 WHEN A.PLANT='3125' THEN Q.ALNUM                 ELSE 'MISSING_LEGI'             END AS 'E1_LEG'             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN F.ALNUM                  WHEN A.PLANT='6100' THEN G.ALNUM                 WHEN A.PLANT='6130' THEN H.ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN I.ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN O.ALNUM                 WHEN A.PLANT='3125' THEN R.ALNUM                 ELSE 'MISSING_LEGI'             END AS 'EU_LEG'             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN J.ALNUM                  WHEN A.PLANT='6100' THEN K.ALNUM                 WHEN A.PLANT='6130' THEN L.ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN M.ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN P.ALNUM                 WHEN A.PLANT='3125' THEN S.ALNUM                 ELSE 'MISSING_LEGI'             END AS 'US_LEG'             FROM LEGAL_REGULATION_TEMP_BOM A                 LEFT JOIN MAEX_E1 B ON A.COMPONENT=B.MATNR AND B.ALAND='BE'                 LEFT JOIN MAEX_E1 C ON A.COMPONENT=C.MATNR AND C.ALAND='HU'                 LEFT JOIN MAEX_E1 D ON A.COMPONENT=D.MATNR AND D.ALAND='IT'                 LEFT JOIN MAEX_E1 E ON A.COMPONENT=E.MATNR AND E.ALAND='SE'                 LEFT JOIN MAEX_E1 N ON A.COMPONENT=N.MATNR AND N.ALAND='FR'                 LEFT JOIN MAEX_E1 Q ON A.COMPONENT=Q.MATNR AND Q.ALAND='US'                 LEFT JOIN MAEX_EU F ON A.COMPONENT=F.MATNR AND F.ALAND='BE'                 LEFT JOIN MAEX_EU G ON A.COMPONENT=G.MATNR AND G.ALAND='HU'                 LEFT JOIN MAEX_EU H ON A.COMPONENT=H.MATNR AND H.ALAND='IT'                 LEFT JOIN MAEX_EU I ON A.COMPONENT=I.MATNR AND I.ALAND='SE'                 LEFT JOIN MAEX_EU O ON A.COMPONENT=O.MATNR AND O.ALAND='FR'                 LEFT JOIN MAEX_EU R ON A.COMPONENT=R.MATNR AND R.ALAND='US'                 LEFT JOIN MAEX_US J ON A.COMPONENT=J.MATNR AND J.ALAND='BE'                 LEFT JOIN MAEX_US K ON A.COMPONENT=K.MATNR AND K.ALAND='HU'                 LEFT JOIN MAEX_US L ON A.COMPONENT=L.MATNR AND L.ALAND='IT'                 LEFT JOIN MAEX_US M ON A.COMPONENT=M.MATNR AND M.ALAND='SE'                 LEFT JOIN MAEX_US P ON A.COMPONENT=P.MATNR AND P.ALAND='FR'                 LEFT JOIN MAEX_US S ON A.COMPONENT=S.MATNR AND S.ALAND='US'

It took me quite a while to figure out the meaning of all these left joins.It is a  nifty way of transpose ALNUM from the MAEX table over MATNR and ALAND  into E1_LEG, EU_LEG and US_LEG like:

This code is clever or rather it was clever when constructed but now the data volumes have grown to large numbers

disclosing the intrinsic weakness of the many joins, today they take a very long time to execute (some 1100 seconds).

I failed to come up with a better way to do the transpose in the last SQL. I then ‘parked’ this problem.

Two days ago while jogging I realized I attacked the problem wrongly, it was not the last query that was the root problem,

but the first three queries splitting the MAEX table. If I do a full transpose over MATNR, I end up with one row per MATNR

which is what I need in the next phase.I remembered I read about a similar problem some years ago where the max

function was combined with group by.

insert into MAEX_PIVOT (         SELECT             A.MATNR            ,max(if (A.GEGRU='E1' and A.ALAND = 'BE', A.ALNUM, NULL)) as E1BE_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'HU', A.ALNUM, NULL)) as E1HU_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'IT', A.ALNUM, NULL)) as E1IT_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'SE', A.ALNUM, NULL)) as E1SE_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'FR', A.ALNUM, NULL)) as E1FR_ALNUM            ,max(if (A.GEGRU='E1' and A.ALAND = 'US', A.ALNUM, NULL)) as E1US_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'BE', A.ALNUM, NULL)) as EUBE_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'HU', A.ALNUM, NULL)) as EUHU_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'IT', A.ALNUM, NULL)) as EUIT_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'SE', A.ALNUM, NULL)) as EUSE_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'FR', A.ALNUM, NULL)) as EUFR_ALNUM            ,max(if (A.GEGRU='EU' and A.ALAND = 'US', A.ALNUM, NULL)) as EUUS_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'BE', A.ALNUM, NULL)) as USBE_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'HU', A.ALNUM, NULL)) as USHU_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'IT', A.ALNUM, NULL)) as USIT_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'SE', A.ALNUM, NULL)) as USSE_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'FR', A.ALNUM, NULL)) as USFR_ALNUM            ,max(if (A.GEGRU='US' and A.ALAND = 'US', A.ALNUM, NULL)) as USUS_ALNUM             FROM MASTERDATA.MAEX A             WHERE A.GEGRU in ('E1','EU','US') and A.ALNUM is not NULL              group by A.MATNR );     220720 075316.546624 8628 Note SQLSTATE=00000, ERRORNO=0     220720 075316.546695 8628 Note SQLINFO Records: 32606

This request replacing the three original requests splitting the MAEX table gave MATNR with 18 ‘transpose’ columns

on each row:

Then it was just a matter of remove joins and map the ‘transpose’ columns to the right position:

INSERT INTO LEGAL_REGULATION_BOM     SELECT              A.PLANT             ,A.MATNR             ,A.DESCRIPTION             ,A.EXT_MAT_GRP             ,A.MATNR_COST             ,A.MATNR_COST_CURR             ,A.PARENT             ,A.COMPONENT             ,A.BOM_LEVEL             ,A.QTY             ,A.COMPONENT_COST              ,A.COMPONENT_COST_CURR             ,A.UNIT_OF_MEASURE             ,A.COMP_DESCRIPTION             ,A.ORIGIN             ,A.DUTYNO             ,A.BOM_PLANT_IS_PTD_0101_ACQPLANT             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN B.E1BE_ALNUM                 WHEN A.PLANT='6100' THEN B.E1HU_ALNUM                 WHEN A.PLANT='6130' THEN B.E1IT_ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN B.E1SE_ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN B.E1FR_ALNUM                 WHEN A.PLANT='3125' THEN B.E1US_ALNUM                 ELSE 'MISSING_LEGI'             END AS 'E1_LEG'             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN B.EUBE_ALNUM                  WHEN A.PLANT='6100' THEN B.EUHU_ALNUM                 WHEN A.PLANT='6130' THEN B.EUIT_ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN B.EUSE_ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN B.EUFR_ALNUM                 WHEN A.PLANT='3125' THEN B.EUUS_ALNUM                 ELSE 'MISSING_LEGI'             END AS 'EU_LEG'             ,CASE                 WHEN A.PLANT IN ('0101','0104') THEN B.USBE_ALNUM                 WHEN A.PLANT='6100' THEN B.USHU_ALNUM                 WHEN A.PLANT='6130' THEN B.USIT_ALNUM                 WHEN A.PLANT IN ('1100','1110') THEN B.USSE_ALNUM                 WHEN A.PLANT IN ('3110','3115') THEN B.USFR_ALNUM                 WHEN A.PLANT='3125' THEN B.USUS_ALNUM                 ELSE 'MISSING_LEGI'             END AS 'US_LEG'             FROM LEGAL_REGULATION_TEMP_BOM A             LEFT JOIN MAEX_PIVOT B ON B.MATNR = A.COMPONENT       ;     220720 081115.168632 19727 Note SQLSTATE=00000, ERRORNO=0     220720 081115.168706 19727 Note SQLINFO Records: 2583726

This reduced the time with 1012 seconds (from 1099 seconds to 87)