DB2 connection

I'm using an OLE DB provider connection string similar to this.
cn2.Open("Provider=IBMDADB2;Database=JDEENT;Hostname=;Protocol=TCPIP;Port=8476; Uid=RLEE;Pwd=PEARL")
I'm getting a COM error. I'm assujming one of my parameters may be wrong. Can someone confirm that. How does one interrogate an AS400 box to find the right parameters? I know the IP is right. The user name and password I'm passing has sufficient rights.
I need to find if the PORT and database name are right. How do I do that?
Who is Participating?
MurpheyConnect With a Mentor Application ConsultantCommented:
Hi HenryV,

Data Source =SYSTEM_NAME  ( from your example it wold be
User Id=myUsername   ( from your example it wold be RLEE)
Password=myPassword   ( from your example it wold be PEARL)
Default Collection= is the library on the AS/400 where the tabels are.

If you don't specify the Default Collection, you can qualify the files with the libraryname like:
in that case an sql statement should look like this :
     Select * from Mylibrary/Myfile  or
     Select * from Mylibrary. Myfile  

If you leave the User Id and Password blank, the AS/400 will ask for it ONCE if you make the connection.
if you already connected to the AS/400 he will use that connection and will not ask for a logon.


MurpheyApplication ConsultantCommented:
This is the right format:
Provider=IBMDADB2;Database=myDataBase;Hostname=myServerAddress;Protocol=TCPIP;Port=50000; Uid=myUsername;Pwd=myPassword;

MurpheyApplication ConsultantCommented:
oops... posted to fast
 I think you have to add the " ; " to the end :)
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Hi HenryV1955,

For the second part of your question, to manage your port, there's 2 places...
1-Go TCPADM, option 7, option 3: This one is to manage TCP/IP Connection Status
2-Go TCPADM, option 1, option 4: This one is to manage TCP/IP Port Restrictions

By the way, there might be something else in TCPADM that can give you a COM error too. You should provide us the error message to go deeper. Or you can sneak around in TCPADM, you might find it.

Does this helped ?
HenryV1955Author Commented:
Hi, heres the error message I get
SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "".  Communication function detecting the error: "selectForRecvTimeout".  Protocol specific error code(s): "0", "*", "*".  SQLSTATE=08001

HenryV1955Author Commented:
HI Lewis

I dont know what TCPADM is? Where is it, how do I access it?

Hi HenryV1955,

Sorry for TCPADM... I was assuming too much...
On the command line in the AS/400, if you type GO TCPADM, the system will bring you the TCP maintenances of the AS/400. Then when you'll do the options mentionned, you'll be able to see and manage your setup.

Be back for you error message SQL30081N...
MurpheyApplication ConsultantCommented:
Hi Henry,

Do you know the return code of this message? or the sense codes ?

The most common sense codes associated with this error message, and the suggested solution in each case, are as follows:

SQL30081N with Return Code 1 and sna sense code 0877002C
Wrong network name has been specified.
SQL30081N with Return Code 1 and SNA sense code ffff0003
The wrong MAC address has been specified or the SNA link is not active.
SQL30081N with Return Code 1 and SNA sense code 10030021
There is an LU type mismatch.
SQL30081N with Return Code 1 and SNA sense code 084B6031
The MAXDBAT in DSNZPARM (at a DB2 for OS/390 and z/OS(TM) host) is set to 0.
Other suggestions:

When creating the Local LU profile, define the LU as the default LU. For example, in the SNA Feature list panel in CM/2, either:
Place a checkmark in the checkbox 'Use this local LU as your default local LU alias', or
Set the profile or environment variable APPCLLU on the DB2 Connect Enterprise Edition server system to the Local LU name. You can do this on Windows(R) systems using the Control Panel.
Check that SNA is started on the DB2 Connect server.
If you are using DB2 for OS/390 and z/OS, check that the Distributed Data Facility (DDF) address space is started and that DB2 is running.

for the other return codes, Check the error code in the manual

Good Luck,
HenryV1955Author Commented:
HI Murphy, this is complete error msg I get in VB whatever this means

System.Runtime.InteropServices.COMException was unhandled
  Message=" SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "".  Communication function detecting the error: "selectForRecvTimeout".  Protocol specific error code(s): "0", "*", "*".  SQLSTATE=08001
  Source="IBM OLE DB Provider for DB2"
       at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)
       at Project1.Form1.Command1_Click(Object eventSender, EventArgs eventArgs) in C:\Documents and Settings\hvanderste\My Documents\VB\Project1.NET\Form1.vb:line 30
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Project1.Form1.Main() in C:\Documents and Settings\hvanderste\My Documents\VB\Project1.NET\Form1.Designer.vb:line 1
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
MurpheyApplication ConsultantCommented:
can you double check:

- is the IP address right ?
- is the port number correctly specified ?
- is the TCPIP listener started ?
MurpheyApplication ConsultantCommented:
Hmm in VB? Connecting to DB2 on a AS?400 (iSeries) server?
HenryV1955Author Commented:
HI Murphy
I'm relying on someone else for correct IP, PORT details, which I'm not sure is accurate, thats why I'm interested in knowing how to interrogate the AS400 box
In my research, if you have db2 client installed on your pc, which I do, you can supposedly use a DB2 connection string OLE DB provider to connect to DB2 which is what I posted here.
HenryV1955Author Commented:
Yes a DB2 on an AS400 i series server
MurpheyApplication ConsultantCommented:
Why not using the IBMDA400 Driver?

Connection string:
Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;
Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;Default Collection=MY_LIBRARY;
HenryV1955Author Commented:
Hi Murphy, ok I'll try.
Please clarify, whats Data Source? Is it database name
Whats Default Collection?

MurpheyApplication ConsultantCommented:
Hi HenryV,

Oh yeah forget to tell, the AS/400 doesn't really have a database name (you mentioned that in yhe last question).
A library is a sort of Database, but technically it looks more like a map or directory
a Library contain files (tables) and or other objects like definitions, programs, profiles etc.

Just nice to know (i think) :)

Have fun,

HenryV1955Author Commented:
Hi Murphy
Thank you very much for your help.
I would appreciate some further discussion
HenryV1955Author Commented:
HI Murphy, I used your IBMDA400 driver
Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;
works great

I assume that this driver should work too if I knew all the parameters? True?
Provider=IBMDADB2;Database=myDataBase;Hostname=myServerAddress;Protocol=TCPIP;Port=50000; Uid=myUsername;Pwd=myPassword;
Is Database the library name?
How do I find out what Port to use?

Question, is there a difference in performance from one to the other?


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.