Skip to main content

This article augments the SQL Option documentation on connecting to mainframe DB2 locations.

Problem:

To use the SQL Option you only need to run the bind one time for each mainframe location.  Rebinds can be done on the mainframe, if needed. You may want to review the mfe\\mfsql\\bin\\ecmbind.txt file to see if you may want to run static binds but you still must run the bind utility from the PC one time to use this as well.

Resolution:

Below are the basic steps for getting SQL Option up and running to a DB2 mainframe location:
  1. First complete Gateway Profile (Options - SQL for DB2 – XDB Link) to point to the DB2 location by entering the following:

    Location Name: This is the DB2 location name used by the DDF facility.

    Connection Type: Use DRDA(TCP/IP)

    Remote HOST: The name of the host IP address either specified in the HOSTS file on this machine, or the named DNS entry.

    Port: The TCPPORT used by the DDF facility.

    The Package Name and Collection ID fields can be left as the default unless your DBA prefers to make them something more specific to your site.
    Note: The easiest way to get all this information at once is to go to the mainframe system log and do a find for DSNL004I (F DSNL004I). 

    In this example, a find for DSNL004I displays the following:

    DSNL004I  @D4 DDF START COMPLETE 730

                LOCATION  UDB_71_OS390

                LU        DDINET1.LUDB27R

                GENERICLU -NONE

                DOMAIN    csimvs.microfocus.com

                TCPPORT   8005

                RESPORT   8006

    So the following is:

    Location name is UDB_71_OS390

    Connection Type is DRDA(TCP/IP) – because there is a tcp port value listed

    Remote Host is CSIMVS (and I can ping csimvs on my machine and see that I receive a response and the IP Address is the mainframe)

    Port is 8005

  2. Next you must Bind XDB Link to DB2 (Options – SQL for DB2 – Bind).  To perform these steps, you must have a valid user ID and password on the XDB Server where XDB Link is installed (if you are using XDB Link in a gateway configuration) and on the host system. Additionally, your user ID must have the following privileges under DB2 on the host.

    BINDADD authority

    BIND authority for XDB Link

    CREATE authority for the XDB Link collection ID (needed only if your DB2 system imposes collection ID security)

    If you do not have these privileges, you (or someone authorized to grant such privileges) must execute the following SQL statements on DB2:

    GRANT BINDADD to UserID

    GRANT BIND ON PACKAGE PackageName TO UserID

    GRANT CREATE ON COLLECTION CollectionID TO UserID

    where:

    UserID is your user ID

    PackageName is the name of the XDB Link package as specified in the Gateway Profile Utility

    CollectionID is the collection ID assigned to XDB Link in the Gateway Profile Utility

  3. Once the Bind is completed you must run the following command to grant public EXECUTE privileges for XDB Link:

    GRANT EXECUTE ON PACKAGE collid.* TO PUBLIC

  4. A few Micro Focus development tools, such as SQL Wizard and XDB Migrate, use special tables in which they maintain control information. These programs also need access to system tables. Before you can successfully use these tools with XDB Link, you must run the SQL script called xdblink.sql found in the mfe\\mfsql\\bin directory of MFE against the DB2 location.

Once this is done, and the users DB2 userid and password has been added to SQL Wizard, the connection to the DB2 location should be up and running.

Old KB# 14653

#MFDS
#EnterpriseDeveloper
Terms & ConditionsAccessibility statement