IBM i Global

IBM i Global

Connect, learn, share, and engage with IBM Power.

 View Only

  • 1.  Creation DB link on IBM i to access UDB DB2

    Posted Tue August 20, 2024 12:54 PM

    Hello

    Is there any method to create db link from "IBM i" to DB2 UDB?

    There are many information to create db link from DB2 UDB to "IBM i"

    But I cannot find method to create db link from "IBM i" to DB2 UDB.

    In advanced, thank you for your support.



    ------------------------------
    DuckHyun Yu
    ------------------------------


  • 2.  RE: Creation DB link on IBM i to access UDB DB2

    Posted Tue August 20, 2024 04:50 PM
    Edited by Daniel Gross Wed August 21, 2024 12:57 AM

    Hi,

    I haven't done that - but I would try to access the tables on the DB2-LUW server by DRDA protocoll. AFAIK all DB2 incarnations support DRDA. 

    To do that, you would have to create a DDM file on the IBM i side, with the CRTDDMF command with RMTFILE(*NONSTD '....') parameter. You would have to replace the .... with the correct table name.

    Of course you would have to configure the DB2-LUW server as a remote database in WRKRDBDIRE. And you have to configure security for that connection.

    HTH

    Daniel



    ------------------------------
    Daniel Gross
    ------------------------------

    Original Message


  • 3.  RE: Creation DB link on IBM i to access UDB DB2

    Posted Tue August 20, 2024 04:54 PM

    P.S.: of course you can also use the CONNECT statement to connect directly to a remote database or access a remote table by 3-part-naming (database.schema.table) in a SQL statement - as long as the remote database is correctly configured. AFAIK this also uses DRDA protocol. 



    ------------------------------
    Daniel Gross
    ------------------------------

    Original Message


  • 4.  RE: Creation DB link on IBM i to access UDB DB2

    Posted Tue August 20, 2024 09:31 PM

    Dear DuckHyun

    To do data access from one DB2 product on any platform to another, you use DRDA which is IBM's distributed SQL architecture.  (DDM file works only between IBM i to another IBM i.)

    From IBM i to another DB2, you first use WRKRDBDIRE command to add the target DB2 system and IP address. (This serves like a TCP/IP host table for DB2.)   You also need to add IP routing entry to the target system as well if it is in a different IP network.  Then you specify how to connect DRDA (no security, user ID only, or user + pw).   Details can be read in this article https://powerwire.eu/wrkrdbdire-i-want-data-from-that-box/       

    To connect from DB2 in Windows to IBM i, it used to be that using a product named DB2 Connect is the way to go. I'm not sure if this still stands.  Do Google with "ibm drda  <platform name>" and you will find more.

    If you use SQL for data access, you either use SQL CONNECT statement or 3-part name from your SQL statements.  Google some more on these terms for more info. Or just ask more here.



    ------------------------------
    Satid S
    ------------------------------

    Original Message


  • 5.  RE: Creation DB link on IBM i to access UDB DB2

    Posted Wed August 21, 2024 12:55 AM

    Hi Satid,

    what you wrote about DDM is not correct - using the RMTFILE(*NONSTD '....') parameter, you can also access files on other platforms using DRDA. 

    I used that to access data on a IBM z Mainframe without trouble. So I dare to say, that DB2-LUW might also work, if you find the correct form of the table name (which might simply be 'SCHEMA.TABLE' - but I'm not sure, because I never done that). 

    A fried of mine used DDM to access some tables on an Oracle server - but they had to purchase the Oracle DRDA feature to do that. And there is also a DRDA feature for Microsofts SQL Server.

    Alternatively you can also use CREATE ALIAS localschema.alias FOR remotedatabase.remoteschema.remotetable to create an SQL alias to the remote table. This SQL alias is then (AFAIK) implemented as a DDM file of the system level - even for local aliases, DDM files are created.

    The basis of all this is always a correctly configured remote database entry in WRKRDBDIRE - that's the backend of all DRDA operations on IBM i.

    Regards,

    Daniel



    ------------------------------
    Daniel Gross
    ------------------------------

    Original Message


  • 6.  RE: Creation DB link on IBM i to access UDB DB2

    Posted Wed August 21, 2024 04:29 AM
    Edited by Marius le Roux Wed August 21, 2024 04:29 AM

    Agree with Satid, one can access any DB2 LUW instance from DB2 for i / Z/OS without any additional "drivers" just a configuration that needs to align. though the other way around, that is where the Cost comes into play with DB2 Connect , meaning Connecting from DB2 LUW instance to DB2 for i 

    DuckyHyun, 

    Perhaps if you are open to a 3rd party solution that runs on the IBM i that will make your life easier? there are a few solutions such as :
    https://www.dodbu.com/rdbConnect.shtml
    & 
    https://www.scottklement.com/presentations/External%20Databases%20from%20RPG.pdf <-- for a FUN free one running in Java (excuse the RPG parts, that is just to enable us RPG devs to play aswell with that tech, the config still stands though) 



    ------------------------------
    Marius le Roux
    Owner
    MLR Consulting
    ------------------------------

    Original Message


  • 7.  RE: Creation DB link on IBM i to access UDB DB2

    Posted Thu August 22, 2024 02:14 AM
    >  www.scottklement.com/presentations/... <-- for a FUN free one running in Java 

    I can attest Scott Klement's way is really neat, and definitely not just for fun. I was able to get an ODBC connection to a remote Informix database working (so IBM i was the ODBC client here) and have successfully ingested data into IBM i DB2 for i that way.



    Original Message


  • 8.  RE: Creation DB link on IBM i to access UDB DB2

    Posted Wed August 21, 2024 09:16 PM
    Edited by Satid S Thu August 22, 2024 02:33 AM

    Dear Daniel 

    Your are right that IBM i DDM file also works with mainframe. But keep in mind that mainframe LPARs that run LinuxOne OS do not natively work with IBM i DDM file.  And in the past when there were several m/f OSes, not all of them work with IBM i DDM file either. 

    This mention of m/f makes me wonder how many companies that have IBM i also have m/f ?  In my country, there are just three remaining (out of some 500) and they have no need to interoperate their m/f with IBM i at all.  At best, they use just batch stream file transfer.  

    >>>>  So I dare to say, that DB2-LUW might also work, if you find the correct form of the table name (which might simply be 'SCHEMA.TABLE' - but I'm not sure, because I never done that). <<<<

    All DB2 RDBMS products in any platform support DRDA remote SQL and they all work with IBM i DRDA.   

    >>>> A fried of mine used DDM to access some tables on an Oracle server <<<<

    This is not accurate.  Oracle and MS SQL Server have their own additional products (used to be known as DRDA gateway) that enable them to interoperate with IBM DRDA but only through remote SQL, definitely not with IBM i DDM file.   DDM and DRDA share foundational architecture but they are not exactly the same in high-level features.  If a DDM file is actually created from SQL CREATE ALIAS statement, it should be called an alias to avoid confusion with a DDM file that is created by CRTDDMF CL command which should be called a DDM file.   Another choice of remote SQL is to use JDBC (implying Java environment) or ODBC among these platforms. 

    >>>> Alternatively you can also use CREATE ALIAS localschema.alias FOR remotedatabase.remoteschema.remotetable to create an SQL alias to the remote table. This SQL alias is then (AFAIK) implemented as a DDM file of the system level - even for local aliases, DDM files are created. <<<<

    What you described is DRDA remote SQL, not DDM file.  Local DDM file implements SQL Alias but it has nothing to do with remote SQL. IBM i DDM file does not support remote SQL. 



    ------------------------------
    Satid S
    ------------------------------

    Original Message


  • 9.  RE: Creation DB link on IBM i to access UDB DB2

    Posted Thu August 22, 2024 06:31 AM

    Dear Daniel

    >>>>  Local DDM file implements SQL Alias but it has nothing to do with remote SQL. IBM i DDM file does not support remote SQL.  <<<<

    I just found a Technote about an enhancement as of IBM i 7.1 that enabled a DDM file that is created by CRTDDMF command to be used with SQL: SQL against DDM file works at 7.1.0 at https://www.ibm.com/support/pages/sql-against-ddm-file-works-710.    A new parameter value was added to deliver this SQL support to a DDM file that is created by CRTDDMF command.   

    Here's what you need to do to create a DDM file that allows SQL access.
    CRTDDMF FILE(Library_name/ddm_file_name) RMTFILE(remote Library/remoteFile) RMTLOCNAME(*RDB) RDB(remotedatabasename)

    The key to making this work is the Parm RMTLOCNAME. The parm must be *RDB. If you use a 'remote database name' for RMTLOCNAME, you will continue to get SQL7011 when selecting from the DDM file.



    ------------------------------
    Satid S
    ------------------------------

    Original Message


  • 10.  RE: Creation DB link on IBM i to access UDB DB2

    Posted Thu August 22, 2024 06:51 AM

    Hi Satid,

    it was a customer z system (I don't remember what machine or OS exactly - but it was a "Maiframe OS" not Linux-ONE) - and we implemented a real time database connectivity because MQ was too expensive on our side of the equation - and also not "fast" enough (hence the "real time"). 

    We (my company) never ran a Mainframe - and "they" (the costumer) never had an IBM i. 

    After the project, they considered to "downsize" from the Mainframe to IBM I because of hardware, software and maintenance cost - but I don't think that they have done that - anyway I'm not connected to the company or the customer anymore since several years. 

    Regards,

    Daniel



    ------------------------------
    Daniel Gross
    ------------------------------

    Original Message