SAPDB ODBC problem

[Former Member]

Hi,

I develope a little tool connecting to the SAPDB.

My connection string "Driver={SAP DB (Unicode)}; Database=MYDB;UID=ADMIN;PWD=ADMIN"

works fine.

However I like to make it also to a client/server application.

When I enter a server to the connection string e.g. "Driver={SAP DB (Unicode)}; Server=127.0.0.1; Database=MYDB;UID=ADMIN;PWD=ADMIN"

some connections works fine but than the ODBC connection fails.

I close any connection after using but it seems to be something like a connection limit???

Also is much slower than without server address...

Please can somebody help?

Thanks,

Andre

[nelis · Accepted solution]

Hi Andre,

I don't know too much on the ODBC implementation with SAPDB but..

The "connection limit" is determined by the parameter MAXUSERTASKS for the database, you may want to check this. You failed to mention what DB version you are using ? You may also want to connect to the database using the DBMgui and check graphically the connections being made by your tool.

Regards,

Nelis

[Former Member · Accepted solution]

Hi Nelis,

thank you for your hints.

Well, it was my first contact to DBM2 but I think I understand the need of because of your help.

But Data is never up to 6% and Session is max. up to 8%.

The log data ends like this:

2008-06-24 19:58:18 0x1438 19677 CONNECT Client has released connection, T16

2008-06-24 19:58:18 0x1438 19651 CONNECT Connection released, T16

2008-06-24 19:58:18 0x1438 19677 CONNECT Client has released connection, T17

2008-06-24 19:58:18 0x1438 19651 CONNECT Connection released, T17

-

---

current write position -

---

=========================================== end of write cycle ===================

So I think it doesn't look critical here, does it?

Thanks,

Andre

[nelis · Accepted solution]

Hi,

Yes you right, doesn't look like you have any problems with connections from database side.

Another tool you can use from command line which may help you is x_cons, not sure whether you have it with your particular version though.

x_cons <DB> show active or x_con <DB> show tasks

If there are any issues with the database instance they will be displayed in your knldiag or knldiag.err file.

Nelis

[Former Member · Accepted solution]

Hi Nelis,

thanks again- yes, I found x_cons.

Maybe you can help me ones more becourse of I don't realy understand the meaning of:

ID UKT Win TASK APPL Current Timeout Region Wait

tid type pid state priority cnt try item

T1 6 0xB7C Timer Vsleep 0 0 138(s)

T2 2 0x28C ALogwr. No-Work (235) 0 0 6(s)

T3 1 0xCE0 Tracewr No-Work (049) 0 0 1(s)

T4 3 0xD74 Utility Inactive 0 275 10(s)

T5 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T6 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T7 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T8 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T9 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T10 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T11 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T12 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T13 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T14 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T15 6 0xB7C Datawr. No-Work (053) 0 0 138(s)

T16 7 0xB84 User 3172* Command wait 557 0 106931(s)

T17 7 0xB84 User 3172* Command wait 897 0 106931(s)

T66 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T67 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T68 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T69 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T70 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T71 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T72 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T73 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T74 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T75 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T76 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T77 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T78 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T79 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T80 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T81 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T82 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T83 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T84 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T85 4 0xD7C Server Vsuspend (211) 0 0 17(s)

T86 4 0xD7C Server Vsuspend (211) 0 0 17(s)

This dump is from about some seconds before the crash.

knldiag result is simular to the DBM2 output I posted before:

2008-06-25 20:50:08 0xB84 19633 CONNECT Connect req. (T18, Node:'localhost', PID:3172)

2008-06-25 20:50:08 0xB84 19651 CONNECT Connection released, T18

2008-06-25 20:50:08 0xB84 19633 CONNECT Connect req. (T18, Node:'localhost', PID:3172)

2008-06-25 20:50:08 0xB84 19651 CONNECT Connection released, T18

I don't understand but it do not look like a problem, right?

Thanks,

Andre

[nelis · Accepted solution]

Hi Andre,

That output from x_cons is displaying your User Kernel Threads which amongst other special threads make up the run time environment of your database instance. "x_cons <DB> show rte" will show you it in more detail. That output from knldiag looks like normal behavior to me and is not an issue.

If you would like to read up more on MaxDB then take a look at http://maxdb.sap.com/ - the training material here explains the internals of the DB in detail if you're interested. The [Wiki|https://wiki.sdn.sap.com/wiki/display/MaxDB/Main] is also very informative to all areas of MaxDB.

Regards,

Nelis

[Former Member · Accepted solution]

Ok thanks.

However after taking a couple of time for searching it seems that the problem is not new:

http://bugs.mysql.com/bug.php?id=12483

http://lists.mysql.com/maxdb/26117

And these threats are old but I found no solution for...

So I think this is the end of my race, isn't it?

[nelis · Accepted solution]

Can you not update the DB to a newer release with updated ODBC drivers ?

Nelis

[Former Member · Accepted solution]

No not because of my customer and all of his software works with the SAP DB 7.4.

The different ist that these programs use the JDBC instead of the ODBC driver.

The ODBC Driver I've already updated to V 7.6 but a DB update is not possible

At this thread Thomas point to a completly new version of the ODBC driver for V 7.7.

Maybe this will fix the problem but the link to the driver at this thread is dead ...

Edited by: Andre Bergmann on Jun 26, 2008 2:19 PM

[TTK · Accepted solution]

I refreshed the link to the preview ODBC 7.7 driver. See the old message cited above.

To the original post:

If the db is local, SERVERNODE can be omitted. Then the communication between ODBC and kernel works with shared memory. If SERVERDB=127.0.0.1, the the IP-loop back is used, which is less efficient.

If there are problems with exhausted socket resources of the OS, too many connect/disconnect cycles are run in a given time intervall.

Using connection pooling of the Windows Drivermanager can help here.

Regards Thomas

[Former Member · Accepted solution]

Thanks for the refreshed URL - however I need a hint how to use it because I don't know how to register the driver so I was not able to try yet.

I did the development with a local copy of the DB without SERVERNODE and it works fine than and you're right really fast.

I played a little bit with the connection polling but it also do not solve the problem.

The only solution I found yet is to set trace mode to on than it also won't crash.

But it takes than more than 2h for getting the datasets what is not very productive

[TTK · Accepted solution]

The new driver should be registered with odbcreg as described. You can use it then under the name with DRIVER=<Name given by odbcreg>.

How large is the difference between using SERVERNODE and leaving it? Usually this should hardly be notable assuming the network configuration is ok.

What are the error messages if the problem occurs?

Can you prepare a driver manager trace of the problem?

Regards Thomas

[Former Member · Accepted solution]

Hi Thomas,

thanks ODBC registration works fine.

Ok, I testet with V 7.6:

26.2076848 - without SERVERNODE

34.6798672 - with SERVERNODE 127.0.0.1

Only for Info V 7.7:

15.6925648 - without SERVERNODE

27.6297296 - with SERVERNODE 127.0.0.1

Error with V 7.6:

ERROR [08001] [SAP AG][SQLOD32 DLL][MaxDB] Unable to connect to data source;-709 CONNECT: (could not connect to socket [10048])

Error with V 7.7:

ERROR [08S01] [SAP AG][SDBODBC DLL][MaxDB] Communication link failure;-10709 Connection failed (RTE:could not connect to socket [10048])

When I do a trace no error occurs...

I think the delay for writing the log to the harddisk may solve the problem.

The complete function (where the error occurs) need about 2 minutes with V 7.6.

When I do a trace it needs about 2h - however, without error ...

[TTK · Accepted solution]

Can you please sketch (or sent the relevant parts of a trace) what ODBC-calls your application does?

The error text suggests, that a connection via socket should be performed. I wonder, whether such an error can occur if SERVERNODE is omitted, i.e. shared memory is used.

Regards Thomas

[Former Member · Accepted solution]

Hi Thomas,

sorry for missunderstanding, when I omitte SERVERNODE anything works fine. Just when SERVERNODE is included (e.g. 127.0.0.1 or 192.168.0.1) the error occurs.

When I do a trace the error don't occurs.

I think the delay when writing the log to disk is the reason for?

Maybe I can write the log direct to the RAM so that there'll be no delay.

I'll try this out.

Thanks,

Andre

[Former Member · Accepted solution]

RAMDisk was the solution ...

SAPDB.vshost 1208-1600 EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)

SQLHDBC 08214CE0

SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>

SQLPOINTER 0x0000000F (BADMEM)

SQLINTEGER -5

SAPDB.vshost 1208-1600 ENTER SQLDriverConnectW

HDBC 08214CE0

HWND 00000000

WCHAR * 0x745D9A38 [ -3] "******\ 0"

SWORD -3

WCHAR * 0x745D9A38

SWORD 2

SWORD * 0x00000000

UWORD 0 <SQL_DRIVER_NOPROMPT>

SAPDB.vshost 1208-1600 EXIT SQLDriverConnectW with return code -1 (SQL_ERROR)

HDBC 08214CE0

HWND 00000000

WCHAR * 0x745D9A38 [ -3] "******\ 0"

SWORD -3

WCHAR * 0x745D9A38

SWORD 2

SWORD * 0x00000000

UWORD 0 <SQL_DRIVER_NOPROMPT>

DIAG [08S01] [SAP AG][SDBODBC DLL][MaxDB] Communication link failure;-10709 Connection failed (RTE:could not connect to socket [10048]) (-10709)

SAPDB.vshost 1208-1600 ENTER SQLGetDiagRecW

SQLSMALLINT 2

SQLHANDLE 08214CE0

SQLSMALLINT 1

SQLWCHAR * 0x00228078 (NYI)

SQLINTEGER * 0x03CBD20C

SQLWCHAR * 0x00227870 (NYI)

SQLSMALLINT 1024

SQLSMALLINT * 0x03CBD208

SAPDB.vshost 1208-1600 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)

SQLSMALLINT 2

SQLHANDLE 08214CE0

SQLSMALLINT 1

SQLWCHAR * 0x00228078 (NYI)

SQLINTEGER * 0x03CBD20C (-10709)

SQLWCHAR * 0x00227870 (NYI)

SQLSMALLINT 1024

SQLSMALLINT * 0x03CBD208 (122)

SAPDB.vshost 1208-1600 ENTER SQLGetDiagRecW

SQLSMALLINT 2

SQLHANDLE 08214CE0

SQLSMALLINT 2

SQLWCHAR * 0x00228078 (NYI)

SQLINTEGER * 0x03CBD20C

SQLWCHAR * 0x00227870 (NYI)

SQLSMALLINT 1024

SQLSMALLINT * 0x03CBD208

SAPDB.vshost 1208-1600 EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)

SQLSMALLINT 2

SQLHANDLE 08214CE0

SQLSMALLINT 2

SQLWCHAR * 0x00228078 (NYI)

SQLINTEGER * 0x03CBD20C

SQLWCHAR * 0x00227870 (NYI)

SQLSMALLINT 1024

SQLSMALLINT * 0x03CBD208

If you need more details please let me know.

I can send you the whole file than 12MB/316KB ZIP.

[TTK · Accepted solution]

It seems, that you application connects/disconnects to the database very, very often. Is this the intended behaviour? At least this leads to an OS shortage of sockets, what seems to cause your problem.

I would like to encourage you to check this. Connect/disconnect cycles are a quite expensive operation and should therefore be avoided. One solution would be a properly installed connection pool of the driver manager (at client side!). Then connections are not closed but reused by the driver manager.

If you can not change this, please have a look at

[http://support.microsoft.com/default.aspx?scid=kb;en-us;196271]

or

[http://technet.microsoft.com/de-de/library/bb726981(en-us).aspx]

where some internals regarding Windows sockets and problems are described.

Regards Thomas

[Former Member · Accepted solution]

Yes you're right, there're a lot of queries. My task is to create a month report for any worker of a company. E.g. - 100 workers, 6 bookings every day (come and go, break etc.), and many other components I've to take care for - and for sure I need the data from the db than.

I already optimized any part of my programm for connection, memory and speed reasons.

But I'm sorry because of English is not my native language I don't understand what you suggest:

One solution would be a properly installed connection pool of the driver manager (at client side!). Then connections are not closed but reused by the driver manager.

Also my dictonary can't help - what is the meaning of a properly installed connection pool?

I'm very sorry about that the translation is now the problem, maybe you can point me to a "How To"

Else I'll have a look to the other threads you point ot.

Thanks,

Andre

[TTK · Accepted solution]

Hallo Andre,

damit wollte ich sagen, wenn der Connection-Pool richtig eingerichtet ist (Control Panel -> Admin Tools -> ODBC Datasources: Tabreiter: Connection Pooling), dann verwendet der Drivermanager Connection-Handles wieder. Dh. wenn ein SQLDisconnect aus der Anwendung kommt, dann wartet der Drivermanager erst die Timeout-Zeit ab. Kommt innerhalb dieser Zeit ein Aufruf eines Connects mit denselben Parameters, wie das noch in der Timeout-Phase befindliche, so wird dieses weiterbenutzt.

Ist es für die Anwendung nicht möglich, alle Queries etc. innerhalb einer Connection abzuwickeln? Das wäre die günstigste Lösung und bräuchte auch keine Unterstützung mittels Connection Pooling.

HTH & viele Grüße Thomas

[Former Member · Accepted solution]

Hallo Thomas,

stimmt, dass ist meine Muttersprache

Und wr ein Treffer, habe alles auf eine Connection umgebaut, es gibt keine Fehler mehr und schneller ist das ganze auch noch mal geworden.

Vielen Dank!

Andre

[TTK]

Hello Andre

Please use a connect string like

DRIVER={<my MaxDB ODBC driver>};SERVERNODE=10.29.14.317;SERVERDB=v78;UID=***;PWD=***;

i.e.

the db host is denoted by SERVERNODE

the db itself by SERVERDB

HTH & Regards Thomas

[Former Member]

Hi Thomas,

thanks a lot for your support.

I changed all connection strings to your suggestion but I get an exception at the same connection like before.

However now the code is much more cleaner

Thanks,

Andre