Check on ODBC connection to IBM DB2 table

[Amduke]

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

[Pak-Ming Cheung - MSFT]

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]

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]

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.

[Amduke]

Hello Pak-Ming Cheung,

This is the code I've used:
First attempt.

I tested both the connection with Sequelink aswell the connection with
DB2-Connect.
In both cases the result is the same.

Public Sub ChckCnn(Table As String)

On Error GoTo err_conn
odbcdsn = "SequelinktoDB2G1"

UserID = "xxxxx"
Passwd = "yyyyyyyy"

Host = "ibm.agfa.be"
serverdsn = "TCPtoMVSDB2G1"
portnumber = 3008

'connected = False
'odbcdsn = "DB2G"
'UserID = "xxxxx"
'Passwd = "yyyyyyyy"
'portnumber = 4462

'Define connection string to DB2 with Sequelink

db2cnstr = "DSN=" & odbcdsn & ";SDSN=" & serverdsn & ";HST=" & Host &
";PRT=" & CStr(portnumber)
'db2cnstr = db2cnstr & ";UID=" & UserID & ";PWD=" & Passwd

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

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

'"Provider=MSDASQL.1;Extended
db2con.Open odbcdsn, UserID, Passwd
'showcnnstrng
GetState (db2con.State)

End code:
The result of the GetState function is "adStateOpen"

So I think the connection is made wright.
The other issue is still the recordset .open based on that connection.
If I check after I made the connection, in both ways, but for now with
Sequelink, and I check with the locals view on, this is the value of the
db2con.connectionstring:
"Provider=MSDASQL.1;Password=yyyyyyyy;User ID=xxxxx;Data
Source=SequelinktoDB2G1;Extended
Properties="DSN=SequelinktoDB2G1;UID=xxxxx;PWD=yyyyyyyy;SDSN=TCPtoMVSDB2G1;HST=ibm.agfa.be;PRT=3008""

As you can see, there has been extra quotes placed after the Properties= and
after PRT=3008.
This causes the problem when opening the recordset.

Second attempt.
As mensioned in your reply, I changed the code as described on the MSDN site:

db2con.Open db2cnstr, UserID, Passwd

With db2con

Set rs_Chk = New ADODB.Recordset

SqlChk = "SELECT " & Table & "* FROM " & Table & " Where ROWNUM = 1;"

'rs_Chk.Open Table, db2con, adOpenStatic, adLockOptimistic
rs_Chk.Open SqlChk, db2con, adOpenStatic, adLockOptimistic

Now the error message has changed to:
-2147217887:ODBC driver does not support the requested properties

[Pak-Ming Cheung - MSFT]

OK, it seems that you are going to (your first attempt) get the connection
string property from the opened connection object, and then use the retrieved
connection string to open a recordset. And this causes an error. Am I right?

If I am right, i think that the second attempt is a better way (in term of
simplicity, performance, etc.). Directly using the opened connection object
to open the recordset.

Just a quick idea: Could you double check whether your query is:

SqlChk = "SELECT " & Table & "* FROM " & Table & " Where ROWNUM = 1;"

or
SqlChk = "SELECT * FROM " & Table & " Where ROWNUM = 1;"

But this may not be the root cause of the problem in the second attempt. I
actually believe there may be some driver issue. So, could you try to enable
the ODBC Trace and post the log here. To enable odbc trace: please follow
http://msdn.microsoft.com/en-us/library/ms711020(VS.85).aspx. This can
intercept all ODBC API calls with input/output arguments and error messages.

You can then understand what's happened in ODBC layer.

Thanks,
Ming.