SQL DataServers & MII Buffering

[former_member202228]

Hello All,

We're running MII 12.1.7 (Build 44), in order to update a series of MS-SQL 2000 located off-site with some data. All remote SQL servers are accessed using the com.inet.tds.TdsDriver (Una2000.jar) which, unfortunately, times out from time to time.

Since all dataservers have buffering features, I've set them up so that the buffers are active, but I'm not very sure when to expect the data buffering to run. I've tested the following situations:

1. SQL Data Server disabled. Running the MII transaction to push the data to the MS-SQL server throws the following error:

[ERROR][QRY]com.sap.xmii.Illuminator.logging.LHException: Server XXXXXXXX is currently disabled.

It's expected that the buffers won't hold anything at this point, because the data server through which the content is obtained is disabled.

2. SQL Data Server Enabled, Wrong credentials. Running the MII transaction to push the data to the MS-SQL server throws the following error: [ERROR][QRY]Pending requests found, current request will be retried at a later date.

It's expected and observed that the buffers will hold all data at this point. The data will be posted to the SQL server as soon as good credentials are set for the SQL user.

3. SQL Data server enabled, good credentials, firewall blocking 1433 port temporarily. Running the MII transaction to push the data to the MS-SQL server throws the following errorlogged in NW : [ERROR][QRY]com.sap.xmii.illuminator.logging.LHException: java.sql.SQLException: [TDS Driver] Operation interrupted.

Since the SQL query temprate returned an error at runtime, I was expecting the buffers to hold the data and retry the query as per the buffering settings. However, I cannot see the data being held in the buffers, the SQL BLS block reporting (i.e. last error message) "Response timed out, request terminated." Subsequently, the application log has 3 related entries :

#2.#2010 11 16 22:35:35:312#0-500#Error#com.sap.xmii.common.LocalServiceConnection###

sap.com/xapps_xmiiear#0021281A555E024C0000000200001572#931347450006797223#

sap.com/xapps_xmiiear#com.sap.xmii.common.LocalServiceConnection#Guest#0##

CA2D6AD1EA4F11DFB94A0021281A555E#ca2d6ad1ea4f11dfb94a0021281a555e#

ca2d6ad1ea4f11dfb94a0021281a555e#0#Thread[Managed_Application_Thread_26,5,Managed_Application_Thread]#

Plain##Response timed out, request terminated#

#2.#2010 11 16 22:38:35:571#0-500#Error#/Applications/XMII/Connector###sap.com/xapps_xmiiear#0021281A555E024D0000000000001572

#931347450006797223#sap.com/xapps_xmiiear#com.sap.xmii.Illuminator.connectors.IDBC.Processor#

Guest#0##CA2D6AD1EA4F11DFB94A0021281A555E#ca2d6ad1ea4f11dfb94a0021281a555e#

ca2d6ad1ea4f11dfb94a0021281a555e#0#Thread[Thread-582640,4,Managed_Application_Thread]#

Plain##Error processing command for template [PROJECT/SQL_TEMPLATE]#

#2.#2010 11 16 22:38:35:598#0-500#Error#/Applications/XMII/Connector###sap.com/xapps_xmiiear#0021281A555E024D0000000200001572#

931347450006797223#sap.com/xapps_xmiiear#com.sap.xmii.Illuminator.connectors.IDBC.Processor#Guest

#0##CA2D6AD1EA4F11DFB94A0021281A555E#ca2d6ad1ea4f11dfb94a0021281a555e#

ca2d6ad1ea4f11dfb94a0021281a555e#0#Thread[Thread-582640,4,Managed_Application_Thread]#

Plain##Error in command: INSERT INTO ....

The same insert command works fine when the connection is not interrupted inbetween, not having any syntax errors as the third message in the logs might suggest.

Is the above the expected buffering behavior? No buffering when the connection drops when executing a SQL command? If yes, how do I avoid it? Should I program my own buffering for the cases when the connection drops in the middle of a SQL transaction (i.e. based on the "Response timed out, request terminated." message received at runtime)?

Thanks,

Paul.

[jcgood25]

SQL 2000 - are you sure it's the driver?

As you have found the buffering is largely communication failure based retries (pseudo - garanteed delivery).

It would appear that a mid-stream drop doesn't fit this classification since the initial Query delivery to the database was already made. Your suggestion for checking the Resultset from the SQLQuery action block and the Success bit will be your best bet for additional error handling.

If you think there is a possible bug or needed expansion of functionality then I would suggest logging a support ticket, referencing this thread and providing your steps to reproduce.

Regards,

Jeremy

[former_member202228]

Many thanks Jeremy for your quick answer!

Pretty sure about the drivers, since I've deployed them. In this case, we're using the Una2000.jar (com.inet.tds.TdsDriver) for the MS-SQL 2000 servers, sqljdbc.jar (com.microsoft.sqlserver.jdbc.SQLServerDriver) for MS-SQL 2005 and ojdbc14.jar (oracle.jdbc.driver.OracleDriver) for Oracle (thin).

I can't classify it as a bug not being sure about the expected behavior. Definitely a "nice to have" for a future expansion in functionality, so that all data servers communication issues (time-out included) are/will be covered by the MII buffering framework.

Short term, for the application at hand, I'm looking to enlarge the time-out interval defined in the MII DataServer settings (currently set up at 15, looking for a value of 120 in the case of those remote servers) which hopefully will cover for the disruption, giving more time to the SQL server to reply.

Since the SQL 2000 servers are rather slow, a series of 5 to 10 combined insert queries (of about 100 rows each) can simply delay the SQL server reply over the time-out value, without having a conectivity issue after all. Funny, it's not the first combined insert giving the headaches, always the issue being with one of the consecutive ones (second combined insert, 3rd, or 4th in the series and so on)

If this does not solve the issue, I'll cache locally & re-run the insert sets giving timeouts.

Again, many thanks,

paul.

[jcgood25]

Have you tried using the SQL 2005 MS driver to connect to the SQL 2000 instance?

I'm not sure if you follow what the timeout setting means, I don't think you want 120 minutes: http://help.sap.com/saphelp_mii121/helpdata/en/45/22b06aaf3d18e9e10000000a11466f/content.htm

[former_member202228]

We'll set up the MS-SQL 2005 driver ASAP and I start to look into a local cacheing mechanism, having a transaction re-run the cached insert statements at given intervals of time.

You're right, 120 minutes is a long time, determining some other u2018No Pool availableu2019 situations.

Thanks,

paul.