AS400,iseries,System i,LANSA,CSS,HTML,Scripts: How to connect Classic ASP with IBM DB2

Author

Message

Anil
Newbie
Newbie

Joined: 10 Apr 2007
Location: Cote D'Ivoire
Posts: 32

Topic: How to connect Classic ASP with IBM DB2
Posted: 23 Feb 2010 at 5:53am

I am getting the following error message while connecting IBM universal DB2 database with classic ASP .

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

Machine Configuration : Windows 7 with Internet information server (IIS) 7 installed.

Language : Classic ASP 3

any help would be aprreciated

IP Logged

DB2GURU
Newbie
Newbie

Joined: 23 Feb 2010
Location: Japan
Posts: 3

Posted: 23 Feb 2010 at 6:04am

I have tried connecting IBM DB2 database with classic ASP using the driver IBM DB2 Driver for ODBC and CLI. I have tried the in windows 7 and IIS 7.

The following connection is used to establish the connection.

Driver={IBM DB2 ODBC DRIVER};Database=myDataBase;Hostname=myServerAddress;Port=1234; Protocol=TCPIP;Uid=myUsername;Pwd=myPassword;

I am getting the following error message while using the above connection string

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

Then I tried creating system ODBC DSN. The system DSN created successfully and test connection also succeeded. I am getting the same error after implementing the connection string with system DSN.

Then I digged in the net and understood that this error is related some security issue.

Then I have tried following trouble shooting method to getting the things to work.

1) Granting Read, Write, List & Execute privileges to IUSR account on the /program files/ibm/sqllib folder and its subfolders.

2) Check the TCPIP config? From a command prompt:

Issue command db2set -all

You should get at least a line returned that reads:

DB2COMM=TCPIP

If you don't get this, run the command:

db2set DB2COMM=TCPIP

3) Also you need to check the database manager config to make sure the service name is listed properly.
db2 get dbm config | find "SVCENAME”
This value should be set to either the service name or port that is listed in C:\WINDOWS\System32\drivers\etc\services
If it’s not set update the dbm cfg using:
update dbm cfg using svcename port#
4) Restart the instance:
db2start
db2stop

4) Check the port number by issuing following command

db2set -all:

[e] DB2PATH=C:\Program Files\IBM\SQLLIB
DB2INSTPROF=C:\Program Files\IBM\SQLLIB
DB2COMM=TCPIP
[g] DB2_EXTSECURITY=YES
[g] DB2SYSTEM=BRAMPWOOD
[g] DB2PATH=C:\Program Files\IBM\SQLLIB
[g] DB2INSTDEF=DB2
[g] DB2ADMINSERVER=DB2DAS00

db2 get dbm config | find "SVCENAME":

TCP/IP Service name (SVCENAME) = db2c_DB2

Last line of C:\WINDOWS\System32\drivers\etc\services:

db2c_DB2 50000/tcp

5) Add IUSR user account to the DB2USERS and DB2ADMNS group and restart IIS.

6) Add IUSR read & execute permission to \SQLLIB.

7) Restart the machine.

After doing the all the above successfully connected with IBM DB2 universal database with classic ASP.

IP Logged

DB2GURU
Newbie
Newbie

Joined: 23 Feb 2010
Location: Japan
Posts: 3

Posted: 23 Feb 2010 at 6:12am

<html>
<body>
<%
Dim connection,recordset,strConnect
'create an instance of the ADO connection and recordset object
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
'define the connectio
strConnect = ("Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE;Hostname=Localhost;Protocol=TCPIP;Uid=userid;Pwd=password")
'strconnect="Provider=IBMDADB2;DSN=sample;UId=userid;Pwd=password"
'strconnect="Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=127.0.0.1;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=userid;Password=password;"
'strconnect="Provider=IBMDADB2;Database=test;HOSTNAME=Baiju-PC;PROTOCOL=TCPIP;PORT=50000;uid=userid;pwd=password"
'strConnect = ("Driver={IBM DB2 ODBC DRIVER - DB2COPY1};dsn=dsnname;userid=userid;password=password")
'open an active connection
On Error Resume Next
Connection.Open strConnect
If Err.Number <> 0 Then
%>
An error occurred in the execution of this ASP page 
Page Error Object 
Error Number <%= Err.Number %> 
Error Description <%= Err.Description %> 
Source <%= Err.Source %> 
LineNumber <%= Err.Line %> 

<% end if
On Error Resume Next
Recordset.open "SELECT * FROM administrator.employee",Connection
If Err.Number <> 0 Then
%>
An error occurred in the execution of this ASP page 
Page Error Object 
Error Number <%= Err.Number %> 
Error Description <%= Err.Description %> 
Source <%= Err.Source %> 

<% end if
Do While Not Recordset.Eof
response.write(Recordset(1))
response.write(" ")
Recordset.MoveNext
loop
Set Recordset = Nothing
Connection.Close
Set Connection =Nothing
%>
</body>
</html>

IP Logged

DB2
AS400,iseries,System i,LANSA,CSS,HTML,Scripts :AS400(Iseries) :DB2

Author	Message
Anil Newbie Joined: 10 Apr 2007 Location: Cote D'Ivoire Posts: 32	Topic: How to connect Classic ASP with IBM DB2 Posted: 23 Feb 2010 at 5:53am
	I am getting the following error message while connecting IBM universal DB2 database with classic ASP . Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Machine Configuration : Windows 7 with Internet information server (IIS) 7 installed. Language : Classic ASP 3 any help would be aprreciated

	IP Logged

DB2GURU Newbie Joined: 23 Feb 2010 Location: Japan Posts: 3	Posted: 23 Feb 2010 at 6:04am
	I have tried connecting IBM DB2 database with classic ASP using the driver IBM DB2 Driver for ODBC and CLI. I have tried the in windows 7 and IIS 7. The following connection is used to establish the connection. Driver={IBM DB2 ODBC DRIVER};Database=myDataBase;Hostname=myServerAddress;Port=1234; Protocol=TCPIP;Uid=myUsername;Pwd=myPassword; I am getting the following error message while using the above connection string Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Then I tried creating system ODBC DSN. The system DSN created successfully and test connection also succeeded. I am getting the same error after implementing the connection string with system DSN. Then I digged in the net and understood that this error is related some security issue. Then I have tried following trouble shooting method to getting the things to work. 1) Granting Read, Write, List & Execute privileges to IUSR account on the /program files/ibm/sqllib folder and its subfolders. 2) Check the TCPIP config? From a command prompt: Issue command db2set -all You should get at least a line returned that reads: DB2COMM=TCPIP If you don't get this, run the command: db2set DB2COMM=TCPIP 3) Also you need to check the database manager config to make sure the service name is listed properly. db2 get dbm config \| find "SVCENAME” This value should be set to either the service name or port that is listed in C:\WINDOWS\System32\drivers\etc\services If it’s not set update the dbm cfg using: update dbm cfg using svcename port# 4) Restart the instance: db2start db2stop 4) Check the port number by issuing following command db2set -all: [e] DB2PATH=C:\Program Files\IBM\SQLLIB DB2INSTPROF=C:\Program Files\IBM\SQLLIB DB2COMM=TCPIP [g] DB2_EXTSECURITY=YES [g] DB2SYSTEM=BRAMPWOOD [g] DB2PATH=C:\Program Files\IBM\SQLLIB [g] DB2INSTDEF=DB2 [g] DB2ADMINSERVER=DB2DAS00 db2 get dbm config \| find "SVCENAME": TCP/IP Service name (SVCENAME) = db2c_DB2 Last line of C:\WINDOWS\System32\drivers\etc\services: db2c_DB2 50000/tcp 5) Add IUSR user account to the DB2USERS and DB2ADMNS group and restart IIS. 6) Add IUSR read & execute permission to \SQLLIB. 7) Restart the machine. After doing the all the above successfully connected with IBM DB2 universal database with classic ASP.
	IP Logged

DB2GURU Newbie Joined: 23 Feb 2010 Location: Japan Posts: 3	Posted: 23 Feb 2010 at 6:12am
	<html> <body> <% Dim connection,recordset,strConnect 'create an instance of the ADO connection and recordset object Set Connection = Server.CreateObject("ADODB.Connection") Set Recordset = Server.CreateObject("ADODB.Recordset") 'define the connectio strConnect = ("Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE;Hostname=Localhost;Protocol=TCPIP;Uid=userid;Pwd=password") 'strconnect="Provider=IBMDADB2;DSN=sample;UId=userid;Pwd=password" 'strconnect="Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=127.0.0.1;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=userid;Password=password;" 'strconnect="Provider=IBMDADB2;Database=test;HOSTNAME=Baiju-PC;PROTOCOL=TCPIP;PORT=50000;uid=userid;pwd=password" 'strConnect = ("Driver={IBM DB2 ODBC DRIVER - DB2COPY1};dsn=dsnname;userid=userid;password=password") 'open an active connection On Error Resume Next Connection.Open strConnect If Err.Number <> 0 Then %> <FONT FACE="ARIAL">An error occurred in the execution of this ASP page<BR> <B>Page Error Object</B><BR> Error Number <%= Err.Number %><BR> Error Description <%= Err.Description %><BR> Source <%= Err.Source %><BR> LineNumber <%= Err.Line %><BR> </FONT> <% end if On Error Resume Next Recordset.open "SELECT * FROM administrator.employee",Connection If Err.Number <> 0 Then %> <FONT FACE="ARIAL">An error occurred in the execution of this ASP page<BR> <B>Page Error Object</B><BR> Error Number <%= Err.Number %><BR> Error Description <%= Err.Description %><BR> Source <%= Err.Source %><BR> </FONT> <% end if Do While Not Recordset.Eof response.write(Recordset(1)) response.write("<br />") Recordset.MoveNext loop Set Recordset = Nothing Connection.Close Set Connection =Nothing %> </body> </html>
	IP Logged