Case study 20: Small SQL script executes and big don't!
Platform: HP-UNIX 11, Tru64 , Windows
Sybase ASE version: 12.5.x
Background story: We've got several new computers with Sybase installed on them, and people from network administration team connected them to existing network.
My task was to create databases, and run all necessary scripts which creates triggers, views and procedures. During this process I've experienced very stochastic behavior. Small SQL scripts were executing and big were not! I was executing these scripts from AseIsql client tool, and got following error in client :
ct_results(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect ct_callback(): user api layer: external error: The connection has been marked dead.
First thing on my mind was that maybe there is some special character in SQL script, but it wasn't. Then I've decided to look at Sybase ASE server error log and found interesting line:
kernel nrpacket: recv, Connection timed out
This line didn't help me, and except some vague explanations I couldn't find anything useful on Internet.
Task: Find out why after several minutes from starting procedure, view or triggers creating script, dead connection occurs.
Solution: I've reported the problem to the all people I knew, and solution came from the least expected source. One of network administration team member told me to check firewall on my machine, from where I've been executing scripts. When I turned the firewall off, all scripts executed smoothly!
Even now, I am not quite sure, how is it possible that small scripts passed through firewall and big were stopped, and how it was possible to SQL client to connect to Sybase ASE server through firewall, which partially blocks connections!!?
But, I've found the solution and it works fine now...:-)