Faster AS400 loads into SQL Server: one column per row

The problem

Every night we load new general-ledger journal lines from an IBM i (DB2 for i) into a SQL Server staging table. The load runs through a linked server, FIS_LINKED, with the MSDASQL provider and the IBM i Access ODBC driver. It is incremental: it only fetches lines it does not have yet.

On a normal day that is 1,000 to 2,000 lines, and the load is done in seconds. Then came a heavy day with 361,899 new lines. The load took 11.5 minutes: about 2 minutes to count what was new, and 9.5 minutes to fetch it. That is about 650 rows per second.

With an AS400 you first suspect a missing index or a busy source system. We could not touch the source system, so we had to find out where the time went before we changed anything.

What we measured, step by step

The load does two reads per company and year. First a count, to see what is new. Then the fetch of those lines. We timed the two reads apart.

Next we looked at the indexes on the AS400 side. IBM i calls them logical files. On this release SYSINDEXES and SYSKEYS were empty for them, but QSYS2.SYSPARTITIONINDEXSTAT lists their keys. The source table, JOURNAL_LINES, had 483 million rows and 14 logical files. One of them, LEDGER.JOURNAL_IDX, had a key column we did not filter on. Adding that filter made the count 3 times faster: 8.8 s to 3.1 s.

That helped. But the count was only a small part of the run. The fetch was the real cost. So we fetched the same 56,809 rows four times, each time with a different column list:

Look at the last two rows. The AS400 finds the rows fast, and reading the data row as well adds less than a second. The time grows with rows times columns. The ODBC path through MSDASQL moves every cell on its own, at about 60,000 cells per second. With 76 columns per row, almost all of the fetch time goes into moving cells.

The idea

If the cost is per cell, send fewer cells. We let the AS400 glue all 76 columns of a row into one fixed-width string. SQL Server then cuts that string back into columns by position.

Everything depends on fixed width. CHAR columns already have a fixed length, so they go as they are. A DECIMAL column goes as a sign ('+' or '-') plus DIGITS(). DIGITS() returns the absolute value as a string with always the same number of digits and no decimal point.

An example with 3 columns: COMPANY CHAR(3), ACCOUNT CHAR(10) and AMOUNT DECIMAL(15,2). The pack expression on the DB2 side:

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

A row with company 001, account R4000 and amount -1234.56 then arrives as one string of 29 characters:

1
001R4000     -000000000123456

On the SQL Server side, SUBSTRING cuts it back:

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;

The decimal point goes back in as text, between the integer digits and the last two digits. We could also cast the 15 digits to a number and divide by 100. We did not, on purpose: a division brings in data types and rounding, and text plus CAST gives back the exact value that left the AS400.

The steps we took

We did not want to write 76 positions by hand. The column types are in QSYS2.SYSCOLUMNS, so a small script reads them and generates two lists: the pack expression for DB2 and the unpack list for SQL Server. Each column gets its start position and length from the type.

OPENQUERY only takes a literal string, so the query per company and year has to be built with dynamic SQL. The trap is the quotes. The inner query has quotes of its own, and inside the OPENQUERY literal you double each one. We build the inner query first and double its quotes in one step:

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;

One pitfall cost us some time. Our script ended every line of the unpack list with a comment that named the column type. Our script glued the next part of the statement to the last line, so it landed inside that comment. SQL Server answered that the select list had fewer items than the insert list. The fix was small: end the generated list with a newline.

The count step got a smaller change. It ran through a view with 4-part names. We sent the same 7 aggregates straight to DB2 with OPENQUERY instead. By day that took the count from 257 s to 135 s.

Making it safe

This is financial data. A fast load that is sometimes wrong is worse than a slow load that is always right. Packing by position has one clear risk: if one column changes length, every position after it shifts. The values come out wrong, and no error tells you.

So we added three guards.

A layout check runs at the start. It compares type, length, scale, CCSID and nullability of all 76 columns with QSYS2.SYSCOLUMNS. If one of them differs from what the pack list was built for, the fast path is off for that run.

A row guard checks every packed row. It must be exactly 500 characters long, and it must carry the company and year we asked for. A row that fails says the packing went wrong somewhere.

A fallback catches both. If the layout check or the row guard fails, that company and year, or the whole run, goes the old, slow way, and we get a warning mail. Only if the old way fails too is it an error.

How we tested it

A check that never failed has only been run, not tested. So before we trusted a check, we made it fail on purpose.

First the data itself. We computed a SHA-256 hash per row over all 76 columns and compared the new rows with the rows the old load had stored. There were 0 differences. Then the red test: we dropped the sign of one amount in the unpack list. The same comparison found 191,977 differences.

Then the two procedures side by side. We ran the old and the new one on copies of the tables, started at the same moment. They gave the same rows and the same statistics. Next we changed one character in one copy, and the comparison found it: 1 and 1 difference.

Last, the forced errors. We fed the procedure a wrong layout, a failing fast fetch, a failing row guard, and a case where both ways failed. Each scenario ended with the right data or with the right error.

Results

Before and after, for the same 361,899 lines, in the same hour:

The times swing with the load on the AS400. At night the numbers are at the low end, during the day at the high end.

Takeaways

• Measure the parts before you tune. Our first win, a filter on an index key, was real, but it was in the wrong part of the run.
• With MSDASQL the cost is per cell. Fewer, wider cells win, even when the source does a little more work to build them.
• An unmade check is not a pass. Make every check fail once on purpose before you rely on it.
• Keep the old path as a fallback. If the layout ever breaks, that night’s load is slow, but the numbers are still right.