locked
SQL Server 2012 :- Linked server not fetching IDMS COM 3 datatype RRS feed

  • Question

  • we are able to create SQL linked server with mainframe IDMS successfully with the help of DSN(ODBC connection) which is using CA-IDMS driver to connect with IDMS server. We can do query from SQL server or excel.

    However; we are facing a issues while accessing few tables. Tables with COM3 fields are not being accesses though linked server and same tables can be access through excel("From Microsoft Query" excel functionality).

    Getting below error while querying on COM-3 datatype table
    [CA][CA IDMS ODBC Driver]Restricted data type attribute violation

    Friday, October 10, 2014 12:38 PM

Answers

  • 1) Please suggest is there any other freeware tool or some driver which we can use to create DSN with IDMS.

    2)The problem is client provided us read-only credential on the IDMS database,so we cannot change anything there.

    Is there any function or other way in which we can convert datatype while migrating data into sql server?

    Hi Eashan,

    According to my knowledge, Microsoft ODBC drivers cannot be used to create linked server for IDMS database. And since there’s little information about accessing data from IDMS database in the SQL Server forum, I suggest you to post the question in the IDMS forum. It is appropriate and more experts will assist you.


    Thanks,
    Lydia Zhang


    Wednesday, October 15, 2014 9:28 AM

All replies

  • Hi Eashan,

    Based  on your description, firstly, it is not recommended to use third-party ODBC drivers when creating SQL Server linked server.

    Secondly, the COM-3 data type is not supported in SQL Server. You can covert the COM-3 data type to SQL Server supported data type in the IDMS table and create an view in the IDMS database, then link to the view.

    Thirdly, I would like to recommend you post the question in the IDMS forum to get more information.


    Thanks,
    Lydia Zhang


    Monday, October 13, 2014 9:46 AM
  • Thanks for the information Lydia

    Please find my comments.

    1) Please suggest is there any other freeware tool or some driver which we can use to create DSN with IDMS.

    2)The problem is client provided us read-only credential on the IDMS database,so we cannot change anything there.

    Is there any function or other way in which we can convert datatype while migrating data into sql server?

    Monday, October 13, 2014 10:44 AM
  • 1) Please suggest is there any other freeware tool or some driver which we can use to create DSN with IDMS.

    2)The problem is client provided us read-only credential on the IDMS database,so we cannot change anything there.

    Is there any function or other way in which we can convert datatype while migrating data into sql server?

    Hi Eashan,

    According to my knowledge, Microsoft ODBC drivers cannot be used to create linked server for IDMS database. And since there’s little information about accessing data from IDMS database in the SQL Server forum, I suggest you to post the question in the IDMS forum. It is appropriate and more experts will assist you.


    Thanks,
    Lydia Zhang


    Wednesday, October 15, 2014 9:28 AM
  • Eashan and Lydia,

    There is a way to get data from IDMS to SQL Server warehouse.  Modern Systems has a product called Mainframe DataShare that replicates data from IDMS to SQL Server, in relational format, without adding footprint on the mainframe.

    There's a video overview, whitepapers, etc- the product's been in use for 10 years and is very mature.  

    I hope you find this helpful!

    Rick O.


    Rick Oppedisano VP Product and Marketing Modern Systems

    Friday, January 30, 2015 12:54 PM