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

Feb 17, 2009 · 2 min read  ·
Share on:

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!