Database Connection Freezing.

[former_member966661]

I'm a programmer for a bowling company that uses vendor software with a sybase database(version 10.0.1.4181) on local servers at these centers, around 300. We need to query that data. I have a server with Sybase Central installed and I can connect to all of these database except for one. Well I take that back, it does connect. Will paste in test connection details below. Everything has been working properly for 2 years including the one in question until about 3 days ago. . Network has told me the server is the same as the others and firewall is turned off and nothing has changed, but something has. We have other servers other then the one I'm on that can query data from the database in question plus the local system working fine, so the database is working. I suspect something is going on when sybase attempts to talk back to this particular server in question. I'm not clear what happens after a connection is made but assume handshaking is going on or an attempt to. I guess my question is , are there logs or something I can look at to determine the issue. The issue being I connect with Sybase Central and then it just locks up. Every other db connects no problem. I have to kill it with task manager. We have rebooted my server plus the server with the DB in question. Any advice on how to resolve the issue would be greatly appreciated. Again we are connecting. Let me know what other info I can add or if this is not clear. Thanks

Connection succeeded.

Tue Jan 21 2020 11:50:40

11:50:40 Attempting to connect using:

UID=reportuser;PWD=********;DBN=PixelSQLbase;ServerName=PixelSQLbase;CON=SQL_DBC_5b277b10;LOG=C:\Users\jkerns\AppData\Local\Temp\3\testcon6984900702672889870.tmp;ASTART=NO;Host=BAMF239:2638

11:50:40 Attempting to connect to a running server...

11:50:40 Trying to start TCPIP link ...

11:50:40 TCP using Winsock version 2.2

11:50:40 My IP address is 192.168.0.138

11:50:40 My IP address is ::1

11:50:40 My IP address is 127.0.0.1

11:50:40 TCPIP link started successfully

11:50:40 Attempting TCPIP connection (address 172.28.53.70:2638 found in sasrv.ini cache)

11:50:40 Looking for server with name pixelsqlbase

11:50:40 HOST option specified, ignoring cached address

11:50:40 Trying to find server at address 172.22.39.70:2638

11:50:43 Found server, verifying server name

11:50:46 Found database server pixelsqlbase on TCPIP link

11:50:46 Connected using client address 192.168.0.138:49435

11:50:46 Connected to server over TCPIP

11:50:46 Writing server address 172.22.39.70:2638 to sasrv.ini cache

11:50:55 Connected to SQL Anywhere Server version 10.0.1.4181

11:50:55 Application information:

11:50:55 IP=192.168.0.138;HOST=PROD-WEBPOS;OS='Windows 2012 Build 9200 ';EXE='C:\Program Files\SQL Anywhere 12\Bin64\scjview.exe';PID=0x1cd4;THREAD=0x1c24;VERSION=12.0.1.4134;API=iAnywhereJDBC;TIMEZONEADJUSTMENT=-300

11:50:55 Connected to the server, attempting to connect to a running database...

11:51:22 [ 1397] Connected to database successfully

[chris_keating]

Sybase Central has made a successful connection to the database based in the LOG= output. All of the work for that connection had been completed at that stage. Once Sybase Central connects, it will then running queries. I wonder if it is blocked on one of those queries. Do you have dbisql? If so, can you use it to connect and run the following query

select 
    Number as ConnIdBlocked,   -- the blocked connection
    BlockedOn ConnIdBlocking,  -- the connection id causing the blocking
    connection_property('AppInfo') as AppInfo
from sa_conn_info() 
where BlockedOn <> 0 

If that query returns results and the EXE= is for scjview.exe, you can run the following to get more information about the source of the blocking.

select
    conn_id as ConnIdBlocking,
    conn_name as ConnName, 
    connection_property('AppInfo') as AppInfo,
    user_id as UID, 
    string(creator,'.',table_name) as TableName,
    table_type as TableType,
    index_id as IndexId,
    lock_class as LockClass, 
    lock_duration as LockDuration,
    row_identifier as RowId
from 
    sa_locks() as Locks,
    sa_conn_info() as ConnInfo
where 
    conn_id in (select BlockedOn from sa_conn_info()) and
    conn_id = Number;

This query will tell you which connection is causing the blocking. Use the ConnIdBlocking from the first query to identify what connection to focus if there are more than one connections that are blocking.

[former_member966661]

Well the copy file time was fairly normal. The software using the database at the server locations were working properly. I was notified yesterday that a another vendor system using a mssql dbs was not replicating starting the same day we started having issues at the same location. So network took a closer look and appears that having significant packet loss on the wan that guy is on. So I kinda felt it was a network issue but I need a smoking gun because of politics etc... Hey thanks for your time to respond and suggestions.

[former_member966661]

well take that back. after 20 minutes the sql statements screen came up . ran the query you suggested and it took about 5 minutes and came back 0. I think we have a network bottleneck going on. then i ran simple query and that took 10 minutes. So i leave Sybase Central Connect dialog up for a long time and it would come back with DB not found. So this does help. Indicates a network issue. Thanks for the assistance and apologize for any confusion.

[chris_keating]

Did the copying of the database appear to take a long time relative to the file size? I would argue that network performance problems will not be specific to an application. Is there any evidence of network performance problems other than attempting to connect?

Are the users for this database complaining about performance? I wonder if the problem is that the database server is dealing with a poorly performing query and as a result is not responsive. Is the CPU usage for the engine high? You may want to look at the documentation Monitoring and Improving Performance.

[former_member966661]

To be clear the Connect To SQL Anywhere Database dialogue freezes up. Don't want to imply the db locks. I hit connect and get an hourglass and can't close it without killing the task.

[former_member966661]

Chris , thanks for the quick response.

Well getting the same issue using dbisql. When I connect to the database it locks. So I can't run any queries. Network copied the db file to a test server and our dba started it up and I connected to that with no issue using Sybase Central . I believe the server has issues but I can't remote into it, so trying to determine if I can find some other logging that would give me some ammo. You said once Sybase Central connects it runs queries? Am I reading that right. What queries is it running on a connection?