Post reply

DB2 Mainframe access

  • February 27, 2006 at 6:57 am

    #167243

    Good morning,

    I work in a large shop where we have multiple DBMSs.  I have been tasked to federate (link to?) SQL Server 2005 with DB2 mainframe data.  Could someone tell me the type of response they've gotten while joining tables from SQL Server to the mainframe?

    My nightly process will entail a large export of tables from the mainframe to SQL.  I was thinking about using SSIS to draw the data directly from the mainframe  using OLEDB driver.  Another way would be to export the mainframe data to a flat file, copying the data to the SQL Server, and finally importing it into SQL.

    Not all the data from the mainframe will be exported so the need to link some tables from SQL will be there...

    Appreciate any and all feedback you may have.

    Thanks,

    Ivan

     

     

     

     

  • February 28, 2006 at 9:13 am

    #623724

    Our databases haven't been upgraded to SQL 2005 yet, but we have linked servers to a DB2 and an AS400 in SQL 2000.  We currently import data directly from them into SQL tables then the data is cleansed and inserted into the production tables.  I'm sure that SSIS could do this all in one package but as far as creating links to the mainframes, we've had no problems.

  • February 28, 2006 at 10:20 am

    #623748

    Linda, great to hear you're already bringing data from DB2.

    Could you give me an example of your 'linkedserver' parameters and what they may mean?  This is where I am having my most difficult time. 

    Also, you mention that you 'cleanse' the data before you insert into the production tables.  Is this by design or because of the translation between EBCIDIC to ASCII?  Or is it because of data type differences?

    Lastly,  do you have any concerns with response time?

    Appreciate all your help,

    Ivan

     

     

     

     

  • February 28, 2006 at 10:54 am

    #623756

    Sure.  We use an IBM DB2 ODBC driver version 7.01.00.28 and in the DTS package the data source connects using "Other(ODBC data sources)".  We push data across several states every 20 minutes and the export is very fast.  The tricky part was matching up date / time data types.  We had to use a "Data Driven Query Task" and I had to have help from the DB2 side in setting up the translations.  That's it in a nutshell.

  • February 28, 2006 at 11:23 am

    #623760

    Great info Linda.  Thanks a bunch!! 

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply