Sneller laden van AS400 naar SQL Server: één kolom per rij

Het probleem

Elke nacht laden we nieuwe journaalregels van het grootboek uit een IBM i (DB2 for i) in een staging-tabel in SQL Server. De load loopt via een linked server, FIS_LINKED, met de MSDASQL-provider en de IBM i Access ODBC-driver. Hij is incrementeel: hij haalt alleen regels op die hij nog niet heeft.

Op een gewone dag zijn dat 1.000 tot 2.000 regels, en is de load in een paar seconden klaar. Toen kwam er een zware dag met 361.899 nieuwe regels. De load duurde 11,5 minuut: ongeveer 2 minuten om te tellen wat nieuw was, en 9,5 minuut om het op te halen. Dat is ongeveer 650 rijen per seconde.

Bij een AS400 denk je eerst aan een ontbrekende index of een druk bronsysteem. Aan het bronsysteem konden we niets veranderen. We moesten dus eerst uitzoeken waar de tijd bleef, voordat we iets aanpasten.

Wat we gemeten hebben, stap voor stap

De load leest twee keer per bedrijf en jaar. Eerst een telling, om te zien wat nieuw is. Daarna het ophalen van die regels. We hebben de twee leesacties apart getimed.

Daarna keken we naar de indexen aan de kant van de AS400. IBM i noemt ze logical files. Op deze release waren SYSINDEXES en SYSKEYS voor die bestanden leeg, maar QSYS2.SYSPARTITIONINDEXSTAT geeft hun sleutels wel. De brontabel, JOURNAL_LINES, had 483 miljoen rijen en 14 logical files. Eén daarvan, LEDGER.JOURNAL_IDX, had een sleutelkolom waar we niet op filterden. Met dat filter erbij werd de telling 3 keer zo snel: van 8,8 s naar 3,1 s.

Dat hielp. Maar de telling was maar een klein deel van de run. Het ophalen kostte de echte tijd. Dus haalden we dezelfde 56.809 rijen vier keer op, elke keer met een andere kolomlijst:

Kijk naar de laatste twee rijen. De AS400 vindt de rijen snel, en de datarij er ook nog bij lezen kost minder dan een seconde extra. De tijd groeit met rijen maal kolommen. Het ODBC-pad via MSDASQL verplaatst elke cel apart, met ongeveer 60.000 cellen per seconde. Met 76 kolommen per rij gaat bijna alle tijd van het ophalen op aan het verplaatsen van cellen.

Het idee

Als je per cel betaalt, stuur dan minder cellen. We lieten de AS400 alle 76 kolommen van een rij aan elkaar plakken tot één string met vaste breedte. SQL Server knipt die string daarna op positie weer in kolommen.

Alles hangt af van de vaste breedte. CHAR-kolommen hebben al een vaste lengte, die gaan ongewijzigd mee. Een DECIMAL-kolom gaat als een teken ('+' of '-') plus DIGITS(). DIGITS() geeft de absolute waarde als string, met altijd hetzelfde aantal cijfers en zonder decimale punt.

Een voorbeeld met 3 kolommen: COMPANY CHAR(3), ACCOUNT CHAR(10) en AMOUNT DECIMAL(15,2). De expressie die aan de kant van DB2 inpakt:

1
COMPANY
2
|| ACCOUNT
3
|| CASE WHEN AMOUNT < 0 THEN '-' ELSE '+' END
4
|| DIGITS(AMOUNT)

Een rij met bedrijf 001, rekening R4000 en bedrag -1234,56 komt dan binnen als één string van 29 tekens:

1
001R4000     -000000000123456

Aan de kant van SQL Server knipt SUBSTRING hem weer op:

1
SELECT
2
    SUBSTRING(r, 1, 3)  AS COMPANY,
3
    SUBSTRING(r, 4, 10) AS ACCOUNT,
4
    CAST(  SUBSTRING(r, 14, 1)   -- sign
5
         + SUBSTRING(r, 15, 13)  -- 13 integer digits
6
         + '.'
7
         + SUBSTRING(r, 28, 2)   -- 2 decimals
8
         AS decimal(15, 2)) AS AMOUNT
9
FROM stage.JOURNAL_PACKED;

De decimale punt gaat er als tekst weer in, tussen de cijfers van het gehele deel en de laatste twee cijfers. We hadden de 15 cijfers ook naar een getal kunnen casten en door 100 delen. Dat deden we bewust niet: een deling brengt datatypes en afronding mee, en tekst plus CAST geeft precies de waarde terug die de AS400 verliet.

De stappen die we namen

We wilden geen 76 posities met de hand schrijven. De kolomtypes staan in QSYS2.SYSCOLUMNS. Een klein script leest ze dus uit en maakt twee lijsten: de inpak-expressie voor DB2 en de uitpaklijst voor SQL Server. Elke kolom krijgt zijn startpositie en lengte uit het type.

OPENQUERY accepteert alleen een letterlijke string, dus de query per bedrijf en jaar moet je met dynamische SQL bouwen. De valkuil zijn de quotes. De binnenste query heeft eigen quotes, en binnen de OPENQUERY-string verdubbel je ze allemaal. We bouwen eerst de binnenste query en verdubbelen de quotes in één stap:

1
DECLARE @inner nvarchar(max) =
2
      N'SELECT ' + @pack + N' AS r FROM JOURNAL_LINES '
3
    + N'WHERE COMPANY = ''' + @company + N''' AND FISCAL_YEAR = ' + CAST(@year AS nvarchar(4));
4

5
DECLARE @sql nvarchar(max) =
6
      N'INSERT INTO stage.JOURNAL_PACKED (r) '
7
    + N'SELECT r FROM OPENQUERY(FIS_LINKED, ''' + REPLACE(@inner, N'''', N'''''') + N''')';
8

9
EXEC sys.sp_executesql @sql;

Eén valkuil kostte ons wat tijd. Ons script sloot elke regel van de uitpaklijst af met commentaar dat het kolomtype noemde. Het script plakte het volgende deel van het statement aan de laatste regel, en zo belandde dat in het commentaar. SQL Server antwoordde dat de select-lijst minder items had dan de insert-lijst. De oplossing was klein: sluit de gegenereerde lijst af met een nieuwe regel.

De telstap kreeg een kleinere aanpassing. Die liep via een view met 4-delige namen. In plaats daarvan stuurden we dezelfde 7 aggregaties met OPENQUERY direct naar DB2. Overdag ging de telling daarmee van 257 s naar 135 s.

Veilig maken

Dit is financiële data. Een snelle load die soms fout is, is erger dan een trage load die altijd klopt. Inpakken op positie heeft één duidelijk risico: als één kolom van lengte verandert, schuiven alle posities erna op. De waarden komen er dan fout uit, en geen enkele foutmelding vertelt je dat.

Daarom kwamen er drie bewakers bij.

Bij de start draait een layoutcontrole. Die vergelijkt type, lengte, schaal, CCSID en nullability van alle 76 kolommen met QSYS2.SYSCOLUMNS. Wijkt er één af van de layout waarvoor de inpaklijst gebouwd is, dan staat het snelle pad voor die run uit.

Een rijbewaker controleert elke ingepakte rij. Die moet precies 500 tekens lang zijn, en hij moet het bedrijf en het jaar bevatten waar we om vroegen. Een rij die niet door de controle komt, zegt dat het inpakken ergens fout ging.

Een terugvaloptie vangt allebei op. Faalt de layoutcontrole of de rijbewaker, dan gaat dat bedrijf en jaar, of de hele run, op de oude, trage manier, en krijgen we een waarschuwingsmail. Pas als de oude manier ook faalt, is het een fout.

Hoe we het getest hebben

Een controle die nooit gefaald heeft, is alleen gedraaid, niet getest. Dus voordat we een controle vertrouwden, lieten we hem expres falen.

Eerst de data zelf. We berekenden per rij een SHA-256-hash over alle 76 kolommen en vergeleken de nieuwe rijen met de rijen die de oude load had opgeslagen. Er waren 0 verschillen. Daarna de rode test: we haalden in de uitpaklijst het teken van één bedrag weg. Dezelfde vergelijking vond 191.977 verschillen.

Daarna de twee procedures naast elkaar. We draaiden de oude en de nieuwe op kopieën van de tabellen, op hetzelfde moment gestart. Ze gaven dezelfde rijen en dezelfde statistieken. Toen veranderden we één teken in één kopie, en de vergelijking vond het: 1 en 1 verschil.

Als laatste de geforceerde fouten. We gaven de procedure een verkeerde layout, een snelle ophaalactie die faalde, een rijbewaker die faalde, en een geval waarin beide manieren faalden. Elk scenario eindigde met de goede data of met de goede fout.

Resultaten

Voor en na, voor dezelfde 361.899 regels, in hetzelfde uur:

De tijden schommelen met de belasting op de AS400. ’s Nachts zitten de getallen aan de lage kant, overdag aan de hoge kant.

Wat we meenemen

• Meet de onderdelen voordat je gaat tunen. Onze eerste winst, een filter op een indexsleutel, was echt, maar zat in het verkeerde deel van de run.
• Met MSDASQL zijn de kosten per cel. Minder, bredere cellen winnen, ook als de bron wat meer werk doet om ze te bouwen.
• Een controle die niet gedaan is, is geen geslaagde controle. Laat elke controle één keer expres falen voordat je erop vertrouwt.
• Houd het oude pad als terugvaloptie. Gaat de layout ooit stuk, dan is de load van die nacht traag, maar kloppen de getallen nog.