| Author |
Message
|
| vickas |
 Posted: Thu Apr 21, 2016 1:25 am Post subject: SQL Server 20119 after SQL Server]SHUTDOWN is in progress. |
 |
|
Centurion
Joined: 18 Aug 2013
Posts: 126
|
HI All ,
I have a flow which talks ( selects & Inserts ) to the remote SQL DB frequently .
All was fine untill the SQL DB went unavailable throwing the following exception
Root SQL exception -1 /opt/ibm/IE02/2.0.1/lib/libodbcinterface.so Child SQL exception 16006 6005 [unixODBC][IBM][ODBC SQL Server Legacy Driver][SQL Server]SHUTDOWN is in progress.
so after this exception & when the flow stills tries to interact it throws the collowing exception
Root SQL exception -1 /opt/ibm/IE02/2.0.1/lib/libodbcinterface.so Child SQL exception 20119 [unixODBC][IBM][ODBC SQL Server Legacy Driver]20119
since the flow runs very frequently , all the time when it tried to interact , got the same [unixODBC][IBM][ODBC SQL Server Legacy Driver]20119 & this prolonged even AFTER BRINGING the remote Microsoft SQL DB UP .
The flow was able to talk to the remote microsoft SQL DB only AFTER the FLOW RESTART.
which was against the ideal case . Ideally when the DB goes down & comes back , the flow must be able to insert rite ?
I wasnt able to replicate this on my local system , soon after i bring my Local SQL DB , the flow is able to insert the records .
what could be the issue ??
here is the odbc.ini file
| Code: |
[SQLDSN]
Driver=/opt/ibm/mqsi/9.0.0.2/ODBC64/V7.0/lib/UKmsss26.so
Description=DataDirect 7.1 SQL Server Wire Protocol
Address=xxx.xxx.xxx.xxx,1433
AnsiNPW=Yes
Database=DBNAME
QuotedId=No
ColumnSizeAsCharacter=1
LoginTimeout=0
UseCursorLib=0
IANAAppCodePage=4
UNICODE=UTF-8
ReportDateTimeType=0 |
|
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Apr 21, 2016 4:29 am Post subject: Re: SQL Server 20119 after SQL Server]SHUTDOWN is in progres |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| vickas wrote: |
| Ideally when the DB goes down & comes back , the flow must be able to insert rite ? |
Rite (sic) but that's ideally. Welcome to the real world, enjoy your stay.
| vickas wrote: |
| what could be the issue ?? |
Speaking entirely from personal experience, the MSoft SQL Server ODBC & JDBC drivers don't do a great job of cleaning up. So when the SQL Server DB goes down, they tend to leave the connections open and hanging. Once the DB comes back up, those hanging connections are associated with the "dead" instance of the DB, not the one that's just started so they don't work.
This means (as you've discovered) that you usually need to restart WMB, IIS and anything else that had an instance open to get it to work.
Standard MSoft really - if it's not working, restart it and try again.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Apr 21, 2016 5:57 am Post subject: Re: SQL Server 20119 after SQL Server]SHUTDOWN is in progres |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Vitor wrote: |
Standard MSoft really - if it's not working, restart it and try again. |
 
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| vickas |
| Posted: Mon Apr 25, 2016 4:25 am Post subject: |
|
|
Centurion
Joined: 18 Aug 2013
Posts: 126
|
hie Vitor , davies thanks for your replies .
| Quote: |
Standard MSoft really - if it's not working, restart it and try again.
|
am still surprised to see this happening in IIB v9.0.0.2 . anyways "restarting the flow is the only solution i found till now " .
besides this , I found another issue with the same flow , throwing the following exception :
Root SQL exception -1 /opt/ibm/IE02/2.0.1/lib/libodbcinterface.so Child SQL exception HY010 [unixODBC][Driver Manager]Function sequence error
here , the flow selects and deletes( if record already exists) and inserts on a single table from within one compute node against the SQL DB .
the " delete & insert " are enclosed in loop ( based on the no of records from select ) & somewhere in the middle of the loop , its always when inserting the records ( i mean after deleting ) we see the above error being encountered .
what is might be the reason for such exception in the interim ??
the records being inserted are not exceeding their max lengths & no datatype mismatches .
this happened only once since 2 months but still not able to figure out the exact reason ..
do connections get locked when select & insert ( within loop) in single node ?
does transaction mode we set on nodes have any effect ?
MQ INput(transaction --> yes ) --> SQLDB (transaction -->Automatic )
what can be the possible solution to get rid of this Function sequence error ??
setting any environment variable can help ?
your replies are highly appreciated . |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Apr 25, 2016 5:32 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| vickas wrote: |
hie Vitor , davies thanks for your replies .
| Quote: |
Standard MSoft really - if it's not working, restart it and try again.
|
am still surprised to see this happening in IIB v9.0.0.2 . |
Why? It's a problem in the MSoft drivers not the broker. What are IBM supposed to do about it???
| vickas wrote: |
| what is might be the reason for such exception in the interim ?? |
Row/table lock within SQL server hiccups?
| vickas wrote: |
| his happened only once since 2 months but still not able to figure out the exact reason .. |
Adds credence to the "hiccup" theory. Random error in db server. As I indicate above, my experience teaches that MSoft products are given to random and inexplicable hiccups like this.
| vickas wrote: |
| do connections get locked when select & insert ( within loop) in single node ? |
In the sense that the operations being performed through the connection will result in some level of db locking; that's true for all dbs.
| vickas wrote: |
does transaction mode we set on nodes have any effect ?
MQ INput(transaction --> yes ) --> SQLDB (transaction -->Automatic ) |
Yes. The transaction mode used by the flow will affect the unit of work used by the db server. Again, this is true for all dbs.
| vickas wrote: |
| what can be the possible solution to get rid of this Function sequence error ?? |
Switch to a better db server (i.e. anything other than MSoft SQL server).
| vickas wrote: |
| setting any environment variable can help ? |
Maybe. That's a question for a SQL Server forum not this one. I doubt anything at the IIB level could influence this.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Apr 25, 2016 6:35 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
| Quote: |
| Maybe. That's a question for a SQL Server forum not this one. I doubt anything at the IIB level could influence this. |
So I disagree with this - the SQLServer ODBC Drivers for all platforms (apart from Windows) are supplied with IIB and the IIB team work closely with the provider of those drivers when there are issues. Therefore, it maybe worth raising a PMR to see if there is actually a problem in the driver itself in this case. Also, to add some weight on the other side of the debate here, there are actually a lot of clients who happily use SQLServer with IIB as this combination is very popular and fully supported so please don't feel that you need to switch your DB!
Kind regards.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| vickas |
| Posted: Mon May 02, 2016 9:15 pm Post subject: |
|
|
Centurion
Joined: 18 Aug 2013
Posts: 126
|
Hie Vitor , thanks for ua replies .
sorry for the late response , back from leave .
| Quote: |
| Why? It's a problem in the MSoft drivers not the broker. What are IBM supposed to do about it??? |
we see this happening only with Microsoft SQL n not with ORacle DB .
we have observed one more intersting thing happening . i.e
we have 2 different interfaces ( both interact very frequently with SQL DB ) deployed on same esb server which talks to two different remote SQL DB's ( something like 1 sql Db server is in Europe n another in USA ) .  .
we see the below particular exception occuring
Root SQL exception -1 /opt/ibm/IE02/2.0.1/lib/libodbcinterface.so Child SQL exception 20119 [unixODBC][IBM][ODBC SQL Server Legacy Driver]20119 after the occurence of main exception untill the flow is restarted .
for Eg : for one interface we first got the below exception n then [unixODBC][IBM][ODBC SQL Server Legacy Driver]20119 exception untill we restarted the flow
Root SQL exception -1 /opt/ibm/IE02/2.0.1/lib/libodbcinterface.so Child SQL exception 16006 6005 [unixODBC][IBM][ODBC SQL Server Legacy Driver][SQL Server]SHUTDOWN is in progress.
n another interface we first got the below exception n then [unixODBC][IBM][ODBC SQL Server Legacy Driver]20119 exception untill we restarted the flow
Child SQL exception 01000 104 [unixODBC][IBM][ODBC SQL Server Legacy Driver][libssclient26]ConnectionWrite (send())
we see the same kind of behaviour ( i mean [ODBC SQL Server Legacy Driver]20119 exception occuring after the main exception).
how do we understand this ?? is it something which can be configured in ODBC parameters ??
I guess the broker is still holding the dead connections & hence throwing [ODBC SQL Server Legacy Driver]20119 exception .
How to get Rid of this exception ? can you suggest me some other ideas ??
do you think updating the driver from UKmsss26.so to UKsqls26.so will help ??
we are currently using v9.0.0.2 , somewhere i read that UKsqls26.so is more compatible with v9.0.0.3
| Quote: |
| Switch to a better db server (i.e. anything other than MSoft SQL server). |
you know how things work in IT . this cannot be moved now . its not in my hands . |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue May 03, 2016 12:55 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Vitor wrote: |
Random error in db server. As I indicate above, my experience teaches that MSoft products are given to random and inexplicable hiccups like this.
|
I've seen this sort of error on a table that is accessed for reads only and all selects are done using PASSTHRU with commands like
| Code: |
select * from <mytable> with (NoLock) where...
|
I'm also seeing SQLSrver 2014 DB timeouts when the CPU is running at 10% and the whole of the DB is in memory.
Go figure...
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
|
|
