Clean-up queries
‘Oude’ data in de Wise database wordt opgeschoond (verwijderd). Er zijn verschillende redenen waarom deze opschoning nodig is:
- Wettelijke AVG policies
- Vervuiling en data-omvang kunnen belemmerend werken t.a.v. de transactiesnelheden
- Data-consistentie
- Migratie naar GBS; in ieders belang willen we zo ‘schoon’ als mogelijk migreren naar het GBS
Voor de opschoning hebben we per domein zinnige termijnen ingesteld die bepalen welke data verwijderd kan worden. Op deze pagina vind je een overzicht van welke data verwijderd wordt en onder welke voorwaarden. Onderaan deze pagina vind je ook de e-mail die is verstuurd met meer achtergrondinformatie over dit opschoonproces (zie Achtergrondinformatie).
De betreffende data valt niet onder de standaard retentie cleanup. De lijst zal in de loop der jaren uitgebreid worden om meer tabellen te schonen.
Hoe werkt het opruimen?
Het verwijderen van de gegevens gebeurt via een extern verwijdercommando. De termijn waarbinnen records mogen worden verwijderd, wordt vastgelegd in de tabel cleanup_retention hieronder.
Deze tabel bevat een 'realm'-kolom en een 'months'-kolom. De months-waarde bepaalt na hoeveel maanden de gegevens mogen worden verwijderd.
De tabel wordt gevuld door het cleanup-script en maakt het mogelijk om de retentieperiode per markt aan te passen, mocht dit nodig zijn.
Retentie opvragen
Om de queries leesbaar te houden wordt de retentieperiode voor een specifieke realm opgezocht met de macro retention(<realm>).
Het resultaat van de macro bepaalt de datum op basis van de cleanup_retention tabel.
Bijvoorbeeld:
retention(orders) wordt vertaald naar:
( NOW() - INTERVAL COALESCE((SELECT months FROM cleanup_retention WHERE realm='orders' LIMIT 1), 99999) MONTH )
Tabel cleanup_retention
| realm | years | months |
|---|---|---|
| action_log | 3 | 36 |
| basket | 2 | 24 |
| bcbirt | 2 | 24 |
| calendar | 4 | 48 |
| campaign | 5 | 60 |
| cleanup | 2 | 24 |
| finance | 8 | 96 |
| fobid | 13 | |
| holds | 5 | 60 |
| inbox | 4 | 48 |
| items | 2 | 24 |
| newsletter | 5 | 60 |
| orders | 8 | 96 |
| poorten_sip2 | 2 | 24 |
| poorten_staff | 1 | 12 |
| sets | 5 | 60 |
| short | 0.25 | 3 |
| tickets | 5 | 60 |
Samenvatting per groep
| Group | Global description |
|---|---|
| action_log | verwijder alle action_log regels ouder dan de retentie termijn (action_log) |
| adres | verwijder alle adressen die niet (meer) gebruikt worden |
| basket | verwijder alle winkelmandje regels ouder dan de retentie termijn (basket) |
| bcbirt | verwijder alle BIRT rapporten (bcbirt) ouder dan de retentie termijn (bcbirt) |
| calendar | verwijder alle kalender en opendgn regels ouder dan de retentie termijn (calendar) |
| campaign | verwijder alle marketing campagnes die langer zijn verlopen dan de retentie termijn (campaign) |
| cleanup | verwijder alle cleanup regels die langer zijn verlopen dan de retentie termijn (cleanup) |
| finance | verwijder alle incasso machtigingen die langer zijn ingetrokken dan de retentie termijn (finance) |
| verwijder alle betaalbestanden die langer geleden zijn uitbetaald dan de retentie termijn (finance) | |
| verwijder alle deurwaarder procedures die langer zijn gesloten dan de retentie termijn (finance) | |
| verwijder alle gesloten stripposten die ouder zijn dan de retentie termijn (finance) | |
| verwijder alle ingetelde boekingen die ouder zijn dan de retentie termijn (finance) | |
| fobid | verwijder alle fobid_logging ouder dan de retentie termijn (fobid) |
| general | verwijder KB koppelingen (NL) |
| verwijder alle kastlijst regels ouder dan de retentie termijn (shelflist) | |
| verwijder alle factuurnummers en sessie locks die ouder zijn dan de retentie termijn (short) | |
| holds | verwijder alle reservering_archief regels met status Z ouder dan de retentie termijn (holds) |
| inbox | verwijder alle inbox_berichten ouder dan de retentie termijn (inbox) |
| items | verwijder alle exemplaar logging en uitleentellers van verwijderde exemplaren en exemplaren met status A, V en @ ouder dan de retentie termijn (items) |
| verwijder alle exemplaar logging van circulatie acties (transport, reservering, GGC sync, uitgesorteerd, afwijkend binnen, uitlening spec. lener) ouder dan de retentie termijn (items) | |
| verwijder niet meer bestaande tijdelijke exemplaren | |
| newsletter | verwijder alle nieuwsbrieven die eerder zijn verzonden dan de retentie termijn (newsletter) |
| orders | verwijder alle order_regels ouder dan de retentie termijn (orders) en alle facturen zonder order_regels |
| poorten | verwijder alle poorten met last_access ouder dan de retentie termijn (poorten_sip2 / poorten_staff) plus alle gerelateerde configuratie |
| rcb | verwijder alle rcb data van verwijderde rcb analyses |
| sets | verwijder alle sets die langer zijn verlopen dan de retentie termijn (sets) |
Queries
| Table name | Group | Order | Number of months | Query | Month added |
|---|---|---|---|---|---|
| action_log | action_log | 1 | 36 | DELETE FROM action_log WHERE updated < retention(action_log); | 2025-03 |
| adres_link | adres | 1 | follows adres | DELETE FROM adres_link WHERE NOT EXISTS (SELECT * FROM adres l WHERE l.adres_id=adres_link.adres_id); | 2025-03 |
| adres_link | adres | 2 | follows actor | DELETE FROM adres_link WHERE NOT EXISTS (SELECT * FROM actor l WHERE l.actor_id=adres_link.actor_id); | 2025-03 |
| adres | adres | 3 | follows adres_link and tw_locatie | DELETE FROM adres WHERE NOT EXISTS (SELECT * FROM adres_link l WHERE l.adres_id=adres.adres_id) and not exists (SELECT * FROM tw_locatie l WHERE l.adres_id=adres.adres_id); | 2025-03 |
| self_pay_hoofd | basket | 1 | 24 | DELETE FROM self_pay_hoofd WHERE NOT ISNULL(eind_datum) AND eind_datum < retention(basket); | 2025-03 |
| self_pay_regel | basket | 2 | follows self_pay_hoofd | DELETE FROM self_pay_regel WHERE hoofd_recno NOT IN (SELECT recno FROM self_pay_hoofd); | 2025-03 |
| bcbirt | bcbirt | 1 | 24 | DELETE FROM bcbirt WHERE updated < retention(bcbirt); | 2025-03 |
| kalender | calendar | 1 | 48 | DELETE FROM kalender WHERE LEFT(periode,4) <= YEAR(retention(calendar)); | 2025-03 |
| opendgn | calendar | 2 | 48 | DELETE FROM opendgn WHERE dag < retention(calendar); | 2025-03 |
| campagne | campaign | 1 | 60 | DELETE FROM campagne WHERE NOT ISNULL(eind_datum) AND eind_datum < retention(campaign); | 2025-03 |
| actor_campagne | campaign | 2 | follows campagne | DELETE FROM actor_campagne WHERE c_recno NOT IN (SELECT recno FROM campagne); | 2025-03 |
| wise_cleanup_done | cleanup | 1 | 24 | DELETE FROM wise_cleanup_done WHERE ent_type = 'E' AND updated < retention(cleanup); | 2025-03 |
| wise_cleanup_done | cleanup | 2 | 24 | DELETE FROM wise_cleanup_done WHERE ent_type = 'T' AND updated < retention(cleanup); | 2025-03 |
| actor_incasso | finance | 1 | 96 | DELETE FROM actor_incasso WHERE NOT ISNULL(datum_intrekking_machtiging) AND datum_intrekking_machtiging < retention(finance); | 2025-03 |
| betaalbestand | finance | 2 | 96 | DELETE FROM betaalbestand WHERE NOT ISNULL(betaaldatum) AND betaaldatum < retention(finance); | 2025-03 |
| strip_posten_archief | finance | 3 | 96 | DELETE FROM strip_posten_archief WHERE adm_edatum < retention(finance); | 2025-03 |
| tot_boekingen | finance | 4 | 96 | DELETE FROM tot_boekingen WHERE periode < CONCAT(DATE_FORMAT(retention(finance), "%y" ), '01'); | 2025-03 |
| tot_boekingen2 | finance | 5 | 96 | DELETE FROM tot_boekingen2 WHERE periode < CONCAT(DATE_FORMAT(retention(finance), "%y" ), '01'); | 2025-03 |
| actor_htel | finance | 6 | 96 | DELETE FROM actor_htel WHERE LEFT(periode,4) <= YEAR(retention(finance)); | 2025-03 |
| deurwaarder_proc | finance | 7 | 96 | DELETE FROM deurwaarder_proc WHERE status IN (7,8,9) AND (deurwaarder_datum < retention(finance) OR (aanmaak_datum < retention(finance) AND ISNULL(deurwaarder_datum))) | 2025-03 |
| log_fobid | fobid | 1 | 13 | DELETE FROM log_fobid WHERE rundatetime < retention(fobid) ; | 2025-03 |
| wise2av | general | 1 | follows actor | DELETE FROM wise2av WHERE actor_id NOT IN (SELECT actor_id FROM actor); | 2025-03 |
| wise2bnl | general | 2 | N/A | TRUNCATE wise2bnl; | 2025-03 |
| kastlijst | general | 3 | 3 | DELETE FROM kastlijst WHERE updated < retention(short); | 2025-03 |
| faknrs | general | 4 | 3 | DELETE FROM faknrs WHERE LEFT(periode,4) < YEAR(retention(short)); | 2025-03 |
| session_lock | general | 5 | 3 | DELETE FROM session_lock WHERE ses_recno=0 AND updated < retention(short); | 2025-03 |
| reservering_archief | holds | 1 | 48 | DELETE FROM reservering_archief WHERE status = 'Z' AND DATE(eind_dat) < retention(holds); | 2025-03 |
| reservering_div_archief | holds | 2 | follows reservering_archief | DELETE FROM reservering_div_archief f WHERE NOT EXISTS (SELECT * FROM reservering_archief r WHERE r.res_id = f.res_id); | 2025-03 |
| local_inbox | inbox | 1 | 48 | DELETE FROM local_inbox WHERE created < retention(inbox); | 2025-03 |
| exemlog2 | items | 1 | 24 | DELETE l.* FROM exemlog2 l, exemplaren e WHERE l.aktie_dat < retention(items) AND e.exem_id = l.exem_id AND e.status IN ("A","V","@"); | 2025-03 |
| exemlog2 | items | 2 | 24 | DELETE FROM exemlog2 l WHERE l.aktie_dat < retention(items) AND NOT EXISTS ( SELECT * FROM exemplaren WHERE l.exem_id = e.exem_id ); | 2025-03 |
| exemlog2 | items | 3 | 24 | DELETE FROM exemlog2 WHERE aktie_dat < retention(items) AND soort IN ('TRP','BIA','PLK','GGC','SRT','SPE','REL'); | 2025-03 |
| exem_tellers | items | 4 | 24 | DELETE t.* FROM exem_tellers t, exemplaren e WHERE jaar < DATE_FORMAT(retention(items), "%Y") AND e.exem_id = t.exem_id AND e.status IN ("A","V","@"); | 2025-03 |
| exem_tellers | items | 5 | follows exemplaren | DELETE FROM exem_tellers t WHERE NOT EXISTS ( SELECT * FROM exemplaren WHERE t.exem_id = e.exem_id ); | 2025-03 |
| tijdexem | items | 6 | follows exemplaren | DELETE FROM tijdexem t WHERE NOT EXISTS( SELECT * FROM exemplaren e WHERE t.exem_id = e.exem_id ); | 2025-03 |
| nieuwsbrief | newsletter | 1 | 60 | DELETE FROM nieuwsbrief WHERE NOT ISNULL(verzonden) AND verzonden < retention(newsletter); | 2025-03 |
| nieuwsbrief_actor | newsletter | 2 | follows nieuwsbrief | DELETE FROM nieuwsbrief_actor WHERE nieuwsbrief_id NOT IN (SELECT id FROM nieuwsbrief); | 2025-03 |
| nieuwsbrief_item_actor | newsletter | 3 | follows nieuwsbrief_item | DELETE FROM nieuwsbrief_item_actor WHERE nieuwsbrief_item_id NOT IN (SELECT id FROM nieuwsbrief_item); | 2025-03 |
| order_regels | orders | 1 | 96 | DELETE FROM order_regels WHERE budgetjaar < year( retention(orders) ) AND ex_status <> "B"; | 2025-03 |
| order_factuur | orders | 2 | follows order_regels | DELETE FROM order_factuur f WHERE NOT EXISTS( SELECT * FROM order_regels r WHERE r.ordfact_id = f.ordfact_id ); | 2025-03 |
| order_line_history | orders | 3 | follows order_regels | DELETE FROM order_line_history f WHERE NOT EXISTS ( SELECT * FROM order_regels r WHERE r.ordfact_id = f.ordfact_id ); | 2025-03 |
| bestel_aanbod | orders | 4 | follows bestel_header | DELETE FROM bestel_aanbod v WHERE NOT EXISTS( SELECT * FROM bestel_header a WHERE a.header_id = v.header_id ); | 2025-03 |
| bestel_verzoek | orders | 5 | follows bestel_aanbod | DELETE FROM bestel_verzoek v WHERE NOT EXISTS(SELECT * FROM bestel_aanbod a WHERE a.aanbod_id = v.aanbod_id); | 2025-03 |
| bestel_advies | orders | 6 | follows bestel_aanbod | DELETE FROM bestel_advies v WHERE NOT EXISTS(SELECT * FROM bestel_aanbod a WHERE a.aanbod_id = v.aanbod_id); | 2025-03 |
| bestel_vergelijk | orders | 7 | follows bestel_aanbod | DELETE FROM bestel_vergelijk v WHERE NOT EXISTS(SELECT * FROM bestel_aanbod a WHERE a.aanbod_id = v.aanbod_id); | 2025-03 |
| poorten | poorten | 1 | 24 | DELETE FROM poorten WHERE soort='S' AND last_access < retention(poorten_sip2); | 2025-03 |
| poorten | poorten | 2 | 12 | DELETE FROM poorten WHERE soort='B' AND last_access < retention(poorten_staff); | 2025-03 |
| poorten_config | poorten | 3 | follows poorten | DELETE FROM poorten_config c WHERE id<>0 AND NOT EXISTS (SELECT * FROM poorten p WHERE c.id=p.id); | 2025-03 |
| poorten_printers | poorten | 4 | follows poorten | DELETE FROM poorten_printers c WHERE id<>0 AND NOT EXISTS (SELECT * FROM poorten p WHERE c.id=p.id); | 2025-03 |
| poorten_sounds | poorten | 5 | follows poorten | DELETE FROM poorten_sounds c WHERE id<>0 AND NOT EXISTS (SELECT * FROM poorten p WHERE c.id=p.id); | 2025-03 |
| rcbdata | rcb | 1 | follows rcbanalyse | DELETE FROM rcbdata d WHERE NOT EXISTS (SELECT * FROM rcbanalyse a WHERE a.analyse = d.analyse ); | 2025-03 |
| set_header | sets | 1 | 60 | DELETE FROM set_header WHERE NOT ISNULL(expires) AND expires <> '0001-01-01' AND expires < retention(sets); | 2025-03 |
| set_regels | sets | 2 | follows set_header | DELETE FROM set_regels WHERE set_id NOT IN (SELECT set_id FROM set_header); | 2025-03 |
| tw_uitvoering | tickets | 1 | 60 | DELETE FROM tw_uitvoering WHERE NOT ISNULL(speeldatum) AND speeldatum < retention(tickets); | 2025-03 |
| tw_uitvoering_stoel | tickets | 2 | follows tw_uitvoering | DELETE FROM tw_uitvoering_stoel WHERE uitvoering_id NOT IN (SELECT id FROM tw_uitvoering); | 2025-03 |
| tw_transactie | tickets | 3 | follows tw_uitvoering | DELETE FROM tw_transactie WHERE uitvoering_id NOT IN (SELECT id FROM tw_uitvoering); | 2025-03 |
| tw_transactie_regel | tickets | 4 | follows tw_transactie | DELETE FROM tw_transactie_regel WHERE transactie_id NOT IN (SELECT id FROM tw_transactie); | 2025-03 |
| rol_theater | tickets | 5 | follows tw_transactie | DELETE FROM rol_theater WHERE actor_id NOT IN (SELECT actor_id FROM tw_transactie); | 2025-03 |
| rol_thea | tickets | 6 | follows tw_transactie | DELETE FROM rol WHERE rol='THEA' AND actor_id NOT IN (SELECT actor_id FROM tw_transactie); | 2025-03 |
| tw_activiteit | tickets | 7 | follows tw_uitvoering | DELETE FROM tw_activiteit WHERE id NOT IN (SELECT activiteit_id FROM tw_uitvoering); | 2025-03 |
| tw_activiteit_planning | tickets | 8 | follows tw_activiteit | DELETE FROM tw_activiteit_planning WHERE activiteit_id NOT IN (SELECT id FROM tw_activiteit); | 2025-03 |
| tw_activiteit_tekst | tickets | 9 | follows tw_activiteit | DELETE FROM tw_activiteit_tekst WHERE activiteit_id NOT IN (SELECT id FROM tw_activiteit); | 2025-03 |
| tw_activiteit_url | tickets | 10 | follows tw_activiteit | DELETE FROM tw_activiteit_url WHERE activiteit_id NOT IN (SELECT id FROM tw_activiteit); | 2025-03 |
| tw_prijs | tickets | 11 | follows tw_activiteit | DELETE FROM tw_prijs WHERE activiteit_id NOT IN (SELECT id FROM tw_activiteit); | 2025-03 |
| tw_kaart_check | tickets | 12 | follows tw_activiteit | DELETE FROM tw_kaart_check WHERE uitvoering_uuid NOT IN (SELECT uuid FROM tw_uitvoering); | 2025-03 |
| tw_seizoen | tickets | 13 | 60 | DELETE FROM tw_seizoen WHERE datum_tot < retention(tickets); | 2025-03 |
| actor_zoek | tickets | 14 | N/A | UPDATE actor_zoek SET rol_thea = FALSE, updated=updated WHERE rol_thea = TRUE AND actor_id NOT IN (SELECT actor_id FROM rol WHERE rol='THEA'); | 2025-03 |
Achtergrondinformatie
Onderstaande e-mail met informatie is verstuurd naar systeembeheerders op 28-03-2025:
Beste,
Zoals tijdens het WGO aangekondigd gaan we ‘oude’ data in de Wise database opschonen (verwijderen). Er zijn verschillende redenen waarom deze opschoning nodig is:
- Wettelijke AVG policies.
- Vervuiling en data-omvang kunnen belemmerend werken t.a.v. de transactiesnelheden.
- Data-consistentie.
- Migratie naar GBS; in ieders belang willen we zo ‘schoon’ als mogelijk migreren naar het GBS.
Voor de opschoning hebben we per domein zinnige termijnen ingesteld die bepalen welke data verwijderd kan worden. Deze termijnen zijn terug te vinden op https://help-nl.wise.oclc.org/Manager_en_systeembeheer/Clean-up_queries/Clean-up_queries
Tabel cleanup_retention toont de termijnen die gehanteerd worden per domein, en tabel Samenvatting per groep geeft een samenvatting van wat er dan verwijderd gaat worden. Voor degene die preciezer wil weten wat er wordt uitgevoerd is er de tabel queries waar alle queries met hun termijnen in beschreven staan.
We gaan deze opschoonacties eerst op de testservers uitvoeren. Dat zullen we doen op 21 april 2025. Van deze schoning zullen we ook een overzicht geven van de hoeveelheid opgeruimde records per tabel. Daarna zullen we een week later de productie databases opschonen. Dat zal over alle Wise systemen als automatisch proces lopen, zonder verdere tellingen etc. Dit zal daarna ook iedere maand uitgevoerd worden om de database schoon te houden.
We verwachten voorlopig ook maandelijks nieuwe queries toe te voegen, omdat we goed gaan kijken waar nog geen standaard opruimroutines actief zijn. Wanneer er nieuwe queries opgenomen worden, zal dit opgenomen worden op https://help-nl.wise.oclc.org/Manager_en_systeembeheer/Clean-up_queries/Clean-up_queries In de kolom “Month added” zijn de nieuwe queries dan herkenbaar. We zullen dan ook een melding sturen naar hostedwise-l@oclc.org ter aankondiging.
De lijst met queries is door een zeer ervaren team van Wise specialisten samengesteld. Mochten er toch bedenkingen zijn en/of heb je vragen over een bepaalde query of retentie termijn, meld dat dan voor 20 april 2025 via devopsemea@oclc.org.
Met vriendelijke groet,
OCLC