RE: Check on ODBC connection to IBM DB2 table

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

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: 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
    ... MSDASQL will only call into DB2 driver's SQLDriverConnect with the DSN name ... 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: 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)
  • Open recordset fails with ODBC connection
    ... I try to activate a recordset based on a DB2 table. ... I setup a connection over ODBC and my check programm tells me that the ... The problem I encounter is that I can't establish to open the recordset. ... Set db2con = New ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)