Hi All,
There is requirement to change and test DB connectivity. webMethods adapter needs to be changed from sybase to MS SQL db.
what are the parameters and webMethods artifacts required to be consider for changing connectivity from sybase to MS sql?
wM version used is 10.3
Hi Istkhar,
Please make sure you first connected to a certified version, as listed in http://techcommunity.softwareag.com/ecosystem/documentation/webmethods/adapters_estandards/Adapters/webMethods_Adapters_System_Requirements.pdf page 22.
If you’re switching from one DB flavor to another DB flavor, it might not be simple as changing the parameter will do.
First from the database objects perspective, you’ll need to ensure you’ve done the conversion or creation of the db objects like tables, views, stored procedures and functions to the new one.
Then from JDBC Adapter perspective, you’ll need to get the jConnect for JDBC driver for the respective version of Sybase and place it under WmJDBCAdapter\lib\jar\static.
jCONNECT 5.5 type 4 (for Sybase)
For NO_TRANSACTION and
LOCAL_TRANSACTION:
com.sybase.jdbc2.jdbc.SybDataSource
For XA_TRANSACTION:
com.sybase.jdbc2.jdbc.SybXADataSource
jCONNECT 6.05 type 4 (for Sybase)
For NO_TRANSACTION and
LOCAL_TRANSACTION:
com.sybase.jdbc3.jdbc.SybDataSource
For XA_TRANSACTION:
com.sybase.jdbc3.jdbc.SybXADataSource
Also there are some knowledgebase articles in Empower, that you can refer upon if you’ve requirements that you need secure connection instead of normal connection one for the JDBC connection to Sybase.
Hi @Mike Ng
Thanks for sharing your valuable inputs and useful document.
For JDBC driver yes, admin is aligned to do the changes.
jConnect i am not aware? Initial part of work is to collect, summarise the impact analysis wrt to applications using sybase DB
DB to be changed is from sybase to MS SQL.
I am able to find the dependant services using sybase DB connection and tracing each dependant service using that sybase db connection.
We might need to change the table of old sybase DB to be ensure that table names and schema are same names with column names and primary foreign keys?
Hi Istkhar,
My apologies, I’ve given the instruction the other way around, I thought you’re moving from SQL Server to Sybase.
In that case, you’ll have to find the relevant MS SQL Server JDBC driver for the SQL Server you’re using.
Place at the same folder under WmJDBCAdapter, then use the following class name for datasource name.
Microsoft SQL Server DataSource for JDBC driver
For NO_TRANSACTION and
LOCAL_TRANSACTION:
com.microsoft.sqlserver.jdbc.SQLServerDataSource
For XA_TRANSACTION:
com.microsoft.sqlserver.jdbc.SQLServerXADataSource
You might want to use check if Sybase ASE to SQL Server Migration - SQLines Tools able to help you to migrate the database objects over without needing to rewrite them in SQL Server side. But that’s beyond what webMethods can assist you, and most of the time the responsibility of the implementor/consultant to sort out.
Hi @Mike Ng,
Thanks a bunch for your prompt and valuable technical inputs really appreciate these helpful pointers.
No worries, I will probably ask the DB team for the appropriate driver given in the link which is required for MSSQL to put in JDBC adapter path
details will be: IS/default/lib/jars ?
Also will be using your above provided class name for datasoure name field in IS adapter connection.
One more thing want to confirm with regards to this post i have referred in this thread : Migration from DB2 to MS SQL - webMethods - Software AG Tech Community & Forums
I have seen the comments, “as long as they are defined as “.” there should be no issues.” Since many of adapter service are defined like this mentioned in comment thread.
And apart from manual way to go inside each n number of dependant adapter service and check if it is pointing to the correct table names ?
and what exactly means by testing it ? Do i need to run the adapter service with test data
The library files should be place under \IntegrationServer\instances\default\package\WmJDBCAdapter\lib\jars\static.
Switching over DB flavor in JDBC Adapter is not easy as straight forward, changing the datasource driver name and etc. Most likely all the adapter services that you’ve developed might need to redevelop, as the connection is still pointing to the old connection. There are a lot metadata information like fieldtype changed from DB to DB in the connection that is encoded in base64, so I would said you need to test out. So far most of the time on my past experience, migrating from one DB to another DB will most likely ended up in redo the thing again, as there will be a lot of breaking parts that you’ll need to invest time to find out and troubleshoot the issue.
Good luck anyway.
Hi,
in general I agree with Mike, but the designated path for the drivers should be IntegrationServer/instances/default/packages/WmJDBCAdapter/code/jars as long as the drivers do not require a native library under IntegrationServer/instances/default/lib.
Place bothe the drivers (Sybase and MS SQL) there and restart IS.
After that the connections should be working correctly.
See IS Built-In-Services References for the WmART package to find services to ease exchanging the connections.
When it is relating to the IS internal JDBC functions the bundled DataDirect Driver should have all the required drivers included.
See IS Administrators Guide for details.
Regards,
Holger