IBM DB2: Five Ways to Discover the Hostname of an iSeries Server for Use in a Four-Part Table Name

I admit, I don’t have too much experience with DB2 yet. But it’s on my to-do list and I’m working on it. I recently had to import some data from DB2 hosted on an iSeries server. I had the hardest time figuring out the hostname and LINKEDSERVER..SCHEMA.TABLE didn’t work. Once DTS helped me figure out the hostname, I suddenly saw it everywhere. Here are five ways to discover an iSeries hostname:

  • GET ADMIN CONFIGURATION
    From a command prompt, run db2 GET ADMIN CONFIGURATION | grep DB2SYSTEM
  • Telnet
    Telnet to the system’s IP address. The hostname will appear in the lower right hand corner above IBM’s copyright information.
  • Schema Documentation.
    The accounting department printed out a PDF file for me that outlined the schema of the views that were generated for my account. The hostname was listed immediately to the right of the title “IBM Query for iSeries.”
  • Microsoft’s DTS Wizard
    This step requires a valid DB2 Provider: Use DTS Wizard to connect to the iSeries server. Once a successful connection is made, chose to import entire tables. In the next screen, you will be presented with the transformation screen. This screen contains 3-part table names that include the hostname.
  • Perform a malformed SQL Server-based query on a DB2 linked server.
    This step requires a DB2 linked server, which requires a valid DB2 provider: While playing around with the syntax of OPENQUERY, I executed the following statement SELECT * FROM OPENQUERY(AS400, ‘SELECT * FROM LinkedServer.Schema.Table’) and the following error was returned:

    OLE DB provider “IBMDA400” for linked server “AS400” returned message “SQL0114: Relational database AS400 not the same as current server SERVERNAME.
    Cause . . . . . : Relational database AS400 was specified in a 3 part name in the statement. However, either the name is not the same as the current server SERVERNAME, or the name is not the same as a relational database name specified previously in the statement.

Now my 4-part queries on a DB2 Linked server in SQL Server totally work. In this case, SELECT * from LinkedServer.SERVERNAME.Schema.Table. Ohhh, shiny!

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in DB2
6 comments on “IBM DB2: Five Ways to Discover the Hostname of an iSeries Server for Use in a Four-Part Table Name
  1. Tom Liotta says:

    I'm a DB2 guy on the System i (was AS/400, then iSeries) side, so I never see "four-part table names" except in forums where people post questions about how to specify them to access AS/400s. This post of yours is the first that gives me a clue about what the problem is. (Only a clue; not every detail.)

    From looking at all five methods, in particular at the final SQL0114 message, it seems that what everyone is looking for is _not_ the 'hostname' and _not_ the 'server name'; it's not even the system name. Apparently, everyone is looking for the 'database name'!

    While all four of those may be exactly the same, the hostname can be different from the system name, and both can be different from the database name. It's uncommon to name the database differently than the system name, but it's done often enough.

    If it really is the 'database name' that's needed, then the entry in the system's relational database directory that identifies the *LOCAL location database is the one that everyone is after.

    The WRKRDBDIRE command is used to "Work with Relational DataBase Directory Entries".

    Tom

  2. Chrissy says:

    Super interesting, Tom, thank you for your post! It kinda sounds, then, that there's just one massive database per server? Pardon my ignorance, DB2 is just so different from SQL Server.

    What is the easiest way to find the database name in the event that it's not the same as the system name?

  3. Kafi says:

    hi chrissy,

    tom already answered!

  4. Hi there, just became alert to your blog through Google, and found that it is really informative. I’m gonna watch out for brussels. I’ll be grateful if you continue this in future. Many people will be benefited from your writing. Cheers!

  5. Luis Dibiase says:

    On the top of description about iseries hostname way, I have found out lots of idea that is useful to use this. I am pleased through reading this explanation. I liked it.

1 Pings/Trackbacks for "IBM DB2: Five Ways to Discover the Hostname of an iSeries Server for Use in a Four-Part Table Name"
  1. […] you don’t know the variable for ServerName in the above query, you can refer to this tutorial. Also, I was able to use Microsoft’s DB2 Provider, as described here. The Initial Catalog is the […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*