Configuring DB2 (Legacy) as Linked Server on MS SQL

Like I have discussed before, I have been banging my head against the table (I mean it!) on a project I am working on presently. This project involves writing fancy reports against very nasty databases. One, they are as old as my late grandfather (the oldest among the two) and two, the data is all messed up.I am writing more code to circumvent thrash data than to present the application itself.

I have tried some magic using CFMX query of queries but I guess that provide only dumd ass results. I remembered that while at my former work place, some fancy dude has connect the main Oracle DB to SQL server before as linked server. On I went to the net looking for resources. Now, DB2 is not a terribly popular database (It is mostly used by high-end enterprise organizations) not to talk of  a legacy version.

Anyway, after about a million years of trial by error, and a million pages of internet materials, I was able to link the two together. But my queries still won’t run with the four part notation (linkserver.catlog.schema.object). I tried and tried until I discovered that a bug on SNA server (corrected on service pack 4) doesn’t allow four part notation on DB2. So I finally settled down to the OpenQuery() method.

And to thrill my colleagues, I ran a query inner joining an EXCEL worksheet with a DB2 table….

Select b.*,scab+scan+scas accountNumber,scshn accountName from openquery(EQX,
‘select * from S44K7816.KFILKLV.scpf where scan=”783227”’) a, EXCEL_AUTHORS…sheet2$ b
where a.scan=b.[ID]

I can now go home and sleep in peace.

Author: Adedeji Olowe

Adédèjì is the founder of Lendsqr, the loan infrastructure fintech powering lenders at scale. Before this, he led Trium Limited, the corporate VC of the Coronation Group, which invested in Woven Finance, Sparkle Bank, Clane, and L1ght, amongst others. He has almost two decades of banking experience, including stints as the Divisional Head of Electronic Banking at Fidelity Bank Plc. He drove the turnaround of the bank’s digital business. He was previously responsible for United Bank for Africa Group’s payment card business across 19 countries. Alongside other industry veterans, he founded Open Banking Nigeria, the nonprofit driving the development and adoption of a common API standard for the Nigerian financial industry. Beyond open APIs, Adédèjì works deeply within the fintech ecosystem; he’s the board chairman at Paystack. Adédèjì is a renowned fintech pundit and has been blogging on technology and payments at dejiolowe.com since 2001.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.