RE: Check on ODBC connection to IBM DB2 table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It is great to hear that you have resolved the first problem.

For the second problem, i have done some simple experiments on my side
(db2con.Open "myDSN", "myUid", "myPwd"). The following is the result:
- ADO/MSDASQL did not try to resolve the ODBC DSN information
- MSDASQL will only call into DB2 driver's SQLDriverConnect with the DSN name
- Therefore, from within ADO/MSDASQL, it shouldn't know the attribute
DBALIAS (which i supposed it is defined inside DSN)
- So, how do you get the string: "Provider=MSDASQL.1;Extended
Properties="DSN=DB2G;UID=xxxxx;PWD=yyyyyyyy;DBALIAS=DB2G;""? Basically, the
extra quotation mark is used to enclose the entire [Extended Properties].

So, I would like to confirm whether your code looks like the following:
db2con.ConnectionString = db2cnstr
db2con.Open odbcdsn, UserID, Passwd
(stripped comment out; there is no ADODB.Connection.Open calls within your
function ChckCnn)

If this is the case, according to
http://msdn.microsoft.com/en-us/library/ms808201.aspx, the [odbcdsn] will
override the [db2cnstr].

So, could you please tell us what the value of [odbcdsn] is? Is it the
constant defined inside ChckCnn, and its value is "SequelinktoDB2G1".

Since the content of DSN is resolved within DB2 driver, it is good to
investigate whether there are any required attributes missed from the DSN
definition. For example, the attribute "SDSN", "HST", "PRT", and "DBALIAS"
may be important.
(this can be verified by connecting to DB2 with a pure ODBC application;
e.g. ODBC Test inside MDAC SDK)

If this does not resolve your problem, you may want to search for some DB2
specific information or contact the support of DB2 driver directly.

Thanks,
WDAC Team, Microsoft.

P.S. We recommend customers to use the forum to ask questions in the future,
where you can obtain a faster response (Forum is at:
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/threads/)


"Amduke" wrote:

First of all, thank you Pak-Ming Cheung.

at firts, there is nog maintenace on the server but only an update of the
DB2 table.
In that case, connnection is not allowed.
At this moment, I have a solution to connect to the DB2 enviroment and check
if the connection is OK or not.

A new problem came up. While connecting to the DB2 enviroment, I would like
to check if the needed table is available.
So I define as recordset and try to make the connection of the table.
Part of the Code:

Set db2con = New ADODB.Connection
db2con.ConnectionString = db2cnstr
'Error code generated when opening the recordset; see below
'"Provider=MSDASQL.1;Extended
Properties="DSN=DB2G;UID=xxxxx;PWD=yyyyyyyy;DBALIAS=DB2G;""
db2con.Open odbcdsn, UserID, Passwd

With db2con
'rs_Chk.ActiveConnection = CurrentProject.Connection
Set rs_Chk = New ADODB.Recordset
rs_Chk.Source = Table
rs_Chk.CursorType = adOpenDynamic
rs_Chk.LockType = adLockOptimistic
rs_Chk.ActiveConnection = db2con
rs_Chk.Open
'Alternative code rs_Chk.open Table, db2con, adOpenStatic, adLockOptimistic

End With

End code.

At this point the error is comming up:
: Msg : "-2147467259:[IBM][CLI Driver][DB2] SQL0104N An unexpected token
"<END-OF-STATEMENT>" was found following "". Expected tokens may include:
"<IDENTIFIER>". SQLSTATE=426"

Practicaly this is the cause of the problem:
: ConnectionString : "Provider=MSDASQL.1;Password=vergeten;User
ID=amyon;Data Source=DB2G;Extended
Properties="DSN=DB2G;UID=xxxx;PWD=yyyyyyyy;DBALIAS=DB2G;"" : String

How can I eliminate the extra quotes in the Connectionstring ??
The string is generate by the system itself.

"Pak-Ming Cheung - MSFT" wrote:

From my understanding, there is some server maintenance regularly. During
that time, the DB2 database is not allowed to connect. And you are looking
for a method to investigate whether the failure of connection is due to the
above problem. Am I right? Correct me if i am wrong.

In this case, there are a number of ideas to solve this problem:
- How about sleeping/retrying upon receiving the error "3709: The connection
cannot be used to perform this operationn. It is either closed or invalid in
this context". This assumes every connection problem is due to the
maintenance. Does this fits your expectation?

- Another idea is to have a separate database to keep the status of the DB2
database. Before your main DB2 database goes into maintenance mode, update
the status database first. On the client side, if you encounter the above
problem, query the status database to confirm the status of the DB2 database.


Thanks,
Ming.
WDAC, Microsoft.

P.S. We recommend customers to use the forum to ask questions in the future,
where you can obtain a faster response (Forum is at:
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/threads/)



"Amduke" wrote:

Hello,
not sure if I'm on the right place here but here is my question.
I'm trying to make a connection via ODBC to IBM DB2 table.
I want to check if the connection is OK because the programs are running on
a Jobqueue PC over night. Somethimes the DB2 table is not available because
it will be reloaded. So if I can check this status, then I can run a loop to
wait and retry later.
At this moment I get an ErrorMessage: 3709: The connection cannot be used to
perform this operationn. It is either closed or invalid in this context.

Here is some code I use, and my comment on where is does go wrong is in it.

Can someone help me out with this ?
Tnx.

Code:

Private Sub Form_Load()
OK_flag = False
'Set warnings off
DoCmd.SetWarnings False
ChckCnn ("VGEGD_EUY1P_V001")
End Sub

Option Compare Database

Dim SqlChk As String
Dim connected As Boolean

'Define connection in current database
Dim rs_Chk As New ADODB.Recordset
Dim db2con As ADODB.Connection


Public Sub ChckCnn(Table As String)

On Error GoTo err_conn

odbcdsn = "SequelinktoDB2G1"
UserID = MyID
Passwd = MyPswd
Host = "ibm.agfa.be"
serverdsn = "TCPtoMVSDB2G1"
portnumber = 3008

'Define connection string to DB2 with Sequelink
db2cnstr = "DSN=" & odbcdsn & ";SDSN=" & serverdsn & ";HST=" & _
Host & ";PRT=" & CStr(portnumber) & ";UID=" & UserID & _
";PWD=" & Passwd

Set db2con = New ADODB.Connection
db2con.ConnectionString = db2cnstr

Remark: With this controle it goes wrong the first time. The result of the
next step Remark: is "adStateClosed".
GetState (db2con.State)

Remark: After this step the ErrorMessage is shown.
rs_Chk.Open Table, db2con, adOpenStatic, adLockOptimistic

If rs_Chk.RecordCount > 0 Then
connected = True
Else
connected = False
End If


err_conn:
Dim Msg As String
DoCmd.Hourglass False
Msg = Table & ":" & Err.Number & ":" & Err.Description
MsgBox Msg
'Select Case Err
'Case 3146
' '3146:ODBC--call failed.
'Case 3704
' '3704:Operation is not allowed when the object is closed.
'Case 0
' 'nothing
'Case Else
' 'WriteToLog Msg
'End Select
Resume Next

End Sub

Public Function GetState(intState As Integer) As String

Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End Select

End Function
.

Relevant Pages

  • RE: Check on ODBC connection to IBM DB2 table
    ... This assumes every connection problem is due to the ... Another idea is to have a separate database to keep the status of the DB2 ... Public Sub ChckCnn(Table As String) ...
    (microsoft.public.data.odbc)
  • RE: Check on ODBC connection to IBM DB2 table
    ... DB2 table. ... So I define as recordset and try to make the connection of the table. ... Set db2con = New ADODB.Connection ... Another idea is to have a separate database to keep the status of the DB2 ...
    (microsoft.public.data.odbc)
  • Re: Basic question :)
    ... List some examples of database applications and explain the differences ... If someone could just explain the differences between Oracle, DB2, SQL ... However, the DB2 Connection Concentrator ...
    (comp.databases.oracle.server)
  • RE: Check on ODBC connection to IBM DB2 table
    ... I tested both the connection with Sequelink aswell the connection with ... 'Define connection string to DB2 with Sequelink ... 'Define connection string to DB2 with DB2connect ... Set db2con = New ADODB.Connection ...
    (microsoft.public.data.odbc)
  • RE: Check on ODBC connection to IBM DB2 table
    ... it seems that you are going to get the connection ... connection string to open a recordset. ... 'Define connection string to DB2 with DB2connect ... Set db2con = New ADODB.Connection ...
    (microsoft.public.data.odbc)