running in a circle looking for ibm db2 connector
-
thx kaj, we are querying it in sql off a linked server. i'm no expert but i believe we still have/want to go thru a driver (installed on our sql server) to reach that as400. i've heard of drivers installed on the target but we dont want to go there.
i have just learned that the erp is from a different vendor called infor and is their xa product. so i believe the erp is irrelevant to this question. i'm trying to get clarity on the model (or whatever) of the machine and version of db2 presumably installed there. i'm assuming at the moment that terms like os400 and iseries are as400 terms but am not sure.
i looked inside the conn info in the linked server and indeed , amongst other things it has this attribute...DBMS Platform=DB2/AS400
i've also reached out to CData but since we already have a license to run the erp on the as400, am thinking that we should start with ibm connector products. at the moment my head would clear somewhat if i understood how this list of compatible sources can be reconciled to what we already have. i dont know if its the as400 machine, the db2 dbms or something else i need to find out if any of the items in this list is what we have...are we likely db2 for ibm i? or maybe db2 for z/os? or none of these?
-
If you are connected to what was formerly known as an AS/400, when it was sold as an integrated system (for more than 10 years it has been sold as an Operating System only, IBM i, that can be installed on a POWER hardware system, just as AIX and Linux can), you should definitely look for ODBC drivers that support IBM i.
-
the site our network guy was referring to is https://www.ibm.com/support/pages/db2-odbc-cli-driver-download-and-installation-information#%5B%3Ch2%3E%5DLicense%5B%3C%2Fh2%3E%5D , which he got initially from me.
-
Looking at the page you linked to, it looks like the driver is probably optimized for accessing a DB2 database for the Windows/Linux/Unix platforms (https://www.ibm.com/docs/en/db2/10.5?topic=overviews-db2-database-linux-unix-windows).
The driver can be used for other DB2 platforms, like z/OS and IBM i, but may not as be efficient.
I would probably try to download the ODBC driver for Windows that is optimized for IBM i, from the IBM download page I linked to. Not sure if that will work better, but if you don't try, you'll never know, right?
-
This old web page shows how to set up a linked server to an IBM i system (the page makes references to SQL Server 2000 and SQL Server 2003 which doesn't promote confidence, but the page has been updated as late as December 2022, so should still be relevant):
https://www.ibm.com/support/pages/configuring-ole-db-provider-sql-server
-
thank you. it looks like ibm i is listed on that one link's product supported list i showed. i'm not sure but i dont think that is free.
since it looks likely that i'll be paying for whatever replacement driver i find anyway, i'm going to trial CData's driver first. it may take a couple of days to get to it. I have their download link.
I'll report back here how it compares to the nightmare we currently face.
-
Have you tried the "DB2 Connect" product from IBM. It is designed to get the best performance from DB2, unlink MS products where the vendor gains mo benefit from getting good performance from another DBMS.
Be aware that if you join any external table to an internal table, no matter what middle ware you use, the receiving system only has two choices on how to do the join, neither are good for performance.
First, it can use a cursor for all parts of the join. You get the data row by agonising row, but quantity of data moved across a network is minimised.
Second, it can read the entire external table into a temp table on the receiving ststem (limited by any WHERE clauses that relate solely to the external table). Having got the external data (into a heap) it can then work out the best query plan to return the data you need. This can often get your query to finish quicker than option 1, but network traffic will be high.
It does not matter what ODBC or other driver you use, the mechanics are simple - either a cursor or a data heap.
There is another option, but this needs a bit of coding. First, you get all the data you need from the external table and put it into a temp table on the receiving system. Then you add appropriate indexes to make the join you need run fastest. Finally, you join to the temp table of external data. This option is likely to be slower than if all your data was permanently in the receiving system, but also faster than the other two choices.
If you need to do the join only a few times a day, then option three is the one to go with. If you need to join many times a minute, then you need to look at real-time replication from the external system to a dedicated table with appropriate indexes on the receiving system.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply