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 |
2024-01-01
New year new currency rates
2023-12-31
Yet another Year
(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😊
2023-10-11
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🙂
2023-01-13
Sevetieth birthday
2022-08-19
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 |
2022-07-20
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)