none
BulkCopy In DB2 RRS feed

  • Question

  • Hi,

    I have the following code that is working in SQL Server, I would like to do the same but in DB2, what do I need to change in code to work in DB2?

    Sub UploadToSQLServer()      


            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\guyz\Desktop\Borech.xlsm;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
            ExcelConnection.Open()

            Dim expr As String = "SELECT * FROM [Sheet1$]"

            Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
            Dim objDR As OleDbDataReader
            Dim SQLconn As New SqlConnection()

            SQLconn.ConnectionString = ConnString
            SQLconn.Open()


            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
                bulkCopy.DestinationTableName = "Animal2"

                Try
                    objDR = objCmdSelect.ExecuteReader
                    bulkCopy.WriteToServer(objDR)
                    objDR.Close()
                    SQLconn.Close()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
            End Using
        End Sub


    Guy Zommer

    Wednesday, August 14, 2019 5:25 AM

All replies

  • Hello,

    See the following 

    https://www.ibm.com/support/knowledgecenter/SSSNY3_10.1.0/com.ibm.swg.im.dbclient.adonet.ref.doc/doc/DB2BulkCopyClassConstructor4.html

    Here is the code they did in C# converted. You will need to use their managed data provider.

     Public Shared Sub copyIntoSales(ByVal connString As DB2Connection, ByVal source As DataTable)
      Dim salesCopy As New DB2BulkCopy(connString, CType(DB2BulkCopyOptions.TableLock, DB2BulkCopyOptions) Or DB2BulkCopyOptions.Truncate)
      salesCopy.DestinationTableName = "SALES"
    
      Try
    	salesCopy.WriteToServer(source)
    	salesCopy.Close()
      Catch ex As Exception
    	MessageBox.Show(ex.ToString(), "Exception")
      End Try
     End Sub
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, August 14, 2019 10:51 AM
    Moderator
  • Thanks for the prompt response, I saw this solution but please explain what do I need to do in my project in order IDE will know 

    DB2BulkCopy

    Thanks


    Guy Zommer

    Wednesday, August 14, 2019 11:00 AM
  • Thanks for the prompt response, I saw this solution but please explain what do I need to do in my project in order IDE will know 

    DB2BulkCopy

    Thanks


    Guy Zommer

    I gave you this link before as you need the managed data provider. I worked with DB2 for 20 years and from that know the best method to work with DB2 is via the managed data provider. You add a reference to the provider DLL then add an Import statement followed by writing code like the code sample I provided. Lastly, to do the connection see the following link

    https://www.connectionstrings.com/ibm-db2/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, August 14, 2019 11:25 AM
    Moderator
  • Dear Karen,

    Thanks a lot for your help, I tried to the link you gave me but I do not understand from where to download the  provider DLL ? 


    Guy Zommer

    Thursday, August 15, 2019 3:54 AM
  • Dear Karen,

    Thanks a lot for your help, I tried to the link you gave me but I do not understand from where to download the  provider DLL ? 


    Guy Zommer

    Go to the following page, make sure to read the entire page.

    https://www.nicklitten.com/download-and-install-ibm-i-acs-access-client-solutions/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, August 15, 2019 10:23 AM
    Moderator