Please be aware that tg4msql has been desupported and replaced by the follow up product Database Gateway for MS SQL Server since March 2008.
You stated that the gateway hangs with any single query. Just to confirm, are you able to select from the SQL Server and get some data back or does it hang for every statement using the gateway?
Best would be also to post a gateway trace by changing in the gateway init file HS_FDS_TRACE_LEVEL=5.
Once set in the gateway init file just open a new SQL*Plus session and run another statement using the gateway. When it hangs again, che ck out the gateway home directory and you'll find a gateway trace file located in tg4msql/trace. Post it to this thread.
Also please post a telnet output where you connect to the SQL Server host and port using telnet to verify the connect details.
(0) Accessing Database 'OKFOC' with SQL:
(0) SELECT "A1"."vid" AS c000, "A1"."fltyear" AS c001, "A1"."season" AS c002, "A1"."startdate" AS c003, "A1"."enddate" AS c004, "A1"."statue" AS c005 FROM "T_FLT_VERSION" "A1"
Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>
Just from the trace it does not indicate that the gateway session hangs. The trace shows a fetch of the data and a normal disconnect:
(0) hoalgof (8): ; hoalgof Exited with retcode = 0.
(0) hoaexit (6): ; hoaexit Entered.
(0) hoaexit (6): ; hoaexit Exited with retcode = 0.
(0) (0): ; Closing log file at TUE JAN 08 15:10:36 2013.
Do you have more details what is causing the hanging session? Are you executing a certain piece of code in a PL/SQL routine?
The Task Manager just shows that there are more than one tg4msql.exe processes exist, and it can be more than 20. I don't know whether it is just because the process was hung or a executing query that keeps it. This may keep the CPU run in 100%, the solution I can find is just kill the process by hand or restart my application. My superior just order me to get over it by giving no advice, I am just a rookie, and I am just losing my confidence now. Hope you or somebody else can give me some advice.
From the description and the trace file I might think about an issue using the gateway. In general when you open the database link by using the gateway you need also make sure to close the database link.
Closing a database link is done automatically when closing the session that uses the gateway, for example when you close SQL*Plus. BUT there's an issue when you use PL/SQL and the Oracle job system as the job system is always active the database link won't be closed at all and depending on the executions of the job a couple of tg4msql processes will remain in the memory. So you need to make sure that you close the database link when you don't need it anymore.
- Count the amount of tg4msql processes.
- select from the SQL Server using the gateway
- you see an additional gateway process.
- you can now get rid of it when you close SQL*Plus or when you execute for example a "commit"/"rollback" and "alter session close database link <db link name>";
=> So please verify that you're closing your database links.