Our company uses Micros POS which uses Sybase SQL Anywhere. We have a lot of scripts that use dbisql to extract data from the Micros databases. These scripts have been running for years with no dbisql issues. However, today when a script tries to run for one of our locations, dbisql simply freezes. I've tried different command line options but no matter what, I get nothing.

I tried running the dbisql directly on the command line like this:

c:>dbisql -datasource LOC1 c:filesf1.sql

and it just hangs.

We have dbisql version 10.0.0.2465

What could cause it to completely freeze? These scripts have run hundreds of thousands of times without this issue occurring. Thanks!

asked 28 Mar '14, 13:50

raphael75's gravatar image

raphael75
31113
accept rate: 0%


It might not be dbisql itself freezing... it might be one of the SQL statements is blocked by another connection. Open another dbisql session in GUI mode (not executing a script) and run this command:

CALL sa_conn_info();

If the BlockedOn column is not zero, it will hold the connection Number of the connection that is blocking it.

For example (using SQL Anywhere 9), line Number=4 contains BlockedOn=6, which means connection 6 is blocking connection 4.

Number,Name,Userid,DBNumber,LastReqTime,ProcessTime,Port,ReqType,CommLink,NodeAddr,LastIdle,CurrTaskSw,BlockedOn,LockName,UncmtOps
6,'ddd9-4','DBA',0,'2014-03-28 15:17:01.254',,,'CURSOR_OPEN','local','',1966,,0,0,1
5,'ddd9-3','DBA',0,'2014-03-28 15:15:24.486',,,'CURSOR_CLOSE','local','',0,,0,0,0
4,'ddd9-2','DBA',0,'2014-03-28 15:16:52.678',,,'STMT_EXECUTE','local','',5175,,6,35,0
3,'ddd9-1','DBA',0,'2014-03-28 15:15:24.230',,,'CURSOR_CLOSE','local','',0,,0,0,0

To find out more information about the cause of the block, run this:

CALL sa_locks();
For example, connection 6 has an exclusive lock on a row in table t:
connection,user_id,table_name,lock_type,lock_name
4,'DBA','DBA.t','SPT',
6,'DBA','DBA.t','SPT',
6,'DBA','DBA.t','E',35

If you want a database monitor to tell you about this kind of problem, maybe even send you an email, check out Foxhound...

alt text

permanent link

answered 28 Mar '14, 15:16

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

edited 28 Mar '14, 15:43

Comment Text Removed
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×78
×17
×2

question asked: 28 Mar '14, 13:50

question was seen: 1,312 times

last updated: 28 Mar '14, 15:47