URGENT!!! Need DB2 ODBC parameters for SQLConfigDataSource() 1

[luvcal]

I have a program that creates ODBC datasources programatically using the Windows API SQLConfigDataSource(). It works fine for other drivers, but not IBM DB2 ODBC DRIVER. I have narrowed it down to a keyword/value pair problem in the following call:

SQLConfigDataSource(GetDesktopWindow(),
ODBC_ADD_SYS_DSN,
DB2_DRIVER,
"DSN=TEST\0"
"DESCRIPTION=test\0"
"UID=myUid\0"
"PWD=mypwd\0"
"DATABASE=myDB\0"
);

Where DB2_DRIVER is "IBM DB2 ODBC DRIVER".

It appears that the DSN,DESCRIPTION, and DATABASE parameters are incorrect...the UID and PWD are being passed just fine...any ideas. I'M DESPARATE!!!

Thanks alot

 

[MarcLodge]

Luvcal,
It's a while since I did much with ODBC, but I don't recognise the \o parameters. Do you know what they are for, and if they are required?
Marc

 

[luvcal]

Evidently, they are used as delimeters between parameters. Seems to work fine for Oracle...but not DB2!! Any ideas??

 

[MarcLodge]

Quite a while back I worked on a project where I connected MS Access to DB2 via ODBC. I specified the connection parms in VBA and they looked like:

ODBC;DSN=DB2T;DBALIAS=DB2T;UID=ABC123;PWD=MYPASSW;

It may be therefore that you need to swap \o with ;

(but then again it might not), give it a try and let me know how you get on. The only other thing that occurred to me was to check with you that you had the right amount of commas etc in your proc as I notice that the last one is after DB2_DRIVER

hth
Marc

 

[luvcal]

Thanks for the info, but that does not work. The funny thing is, it is reading my user id and password parameters just fine...makes me think I am putting in the wrong keywords or something!!!

 

[MarcLodge]

I'm stumped without more info to see what you're actually doing. I'm certain that the DSN parameter is required and the way you have it coded looks correct. I would try playing with quotes and commas and the like without an example to copy. I'll have a search around and see if I can dig up any more info.
Marc

 

[MarcLodge]

There's a lot of info out there! Just to clarify, you're running from Windows (NT?) going to a DB2 system via ODBC. Is it a mainframe DB2 system or PC. Also what error message are you getting
Marc

 

[luvcal]

Yes, I am running from NT(2k actually) and going to a DB2 system via ODBC and it is on a PC. Thanks for all your help.

 

[MarcLodge]

Sorry luvcal, but further questions! What PC db2 are you going to, and what's the message you are receiving. The reason I ask is that the ODBC parameters are particular to a specific specific driver, although many of them are similar. I believe that the standard ODBC parms for the mainframe are different from those on UDB2 for instance.

I think that the problem you are experiencing is a format difference between DB2 and Oracle (for instance). Are you able to drop into the Control Panel, and the ODBC drivers and attempt to access the ODBC driver that you are having problems with, there? This may give you more information, particularly if you are able to turn the ODBC logging on, successfully access DB2, and then interrogate the log to see what parameters it built.

Sorry I can't be of more help, but do get back to me, I'll do the best I can.
Marc

 

[luvcal]

Thanks for being so helpful...my current machine configuration is simply this:

Windows 2000 with DB2 UDB v7.2

The SQLConfigDataSource() is returning: "Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed" when using the "IBM DB2 ODBC DRIVER". The official error code SQLConfigDataSource() is returning is: ODBC_ERROR_REQUEST_FAILED, which, according to Microsoft docs is: "The installer could not perform the operation requested by the fRequest argument. The call to ConfigDSN failed." I can't seem to build a program that calls ConfigDSN explicitly, though...

I have absolutely no problem creating ODBC data sources with the IBM DB2 ODBC DRIVER manually through the control panel, but my tracing does not seem to work...DB2 tracing yields very little good information as well.

Again, thanks for your help.

 

[luvcal]

YES, YES, YES!!! While digging through some DB2 docs, I found that DB2's ODBC driver config parameters ARE different than the rest of the worlds'. Instead of using "DSN=datasourcename", they use "DBALIAS=dbname" and it MUST match an existing database name cataloged in the system. There is no support for the "DATABASE=..." cfg parameter and now UID and PWD don't work, but I don't need to set them anyway!!! What a relief!! Thanks for all your help. I'll mark you as helpful. Funny thing about this problem is that I've been working with DB2 support, and they could not figure it out! Oh well...thanks again!

 

[MarcLodge]

Luvcal,
Thanks for the star. I had a feeling that you were very close, but without actually peering over your shoulder, I was beginning to be at a loss. I'm delighted that you've managed to solve it, and would suggest that you offer yourself to DB2 support on a consultancy basis for a huge fee!

 

[kadaru]

hey Luvcal

can you give me the whole connection parameter string so that i can go about doing it using the SQLCOnfigData source


thanks
kadaru

 

[luvcal]

Sorry it took so long to respond. The connection string I am using in my code for DB2 is as follows:
&quot;DSN=<datasource_name>\0DBALIAS=<database_name>\0\0&quot;

It was tricky to get it working in my C code, but these are the right parameters for DB2. If you need parameters for different db vendors, let me know.