none
Insert to DB2 from SQL hang on RRS feed

  • Question

  • Hi:

         This is my first experience with DB2. I'm running test of a new system based on SQL 2008 R2, and I need insert record on a legacy DB2/400 ver 05.04.0000.

         Using the DB2OLEDB (ver 3.0) provider from HIS2010, the DB2 is linked to SQL.

         SELECT queries run confortable fast it retrives 1000 record in about 6 seconds, UPDATE is very slow it takes 14 seconds to update a single field on a single row like: UPDATE TestDB2.S10994BD.TBL2_400.TICKETS SET FIELD1 = CONSTAT1 WHERE FIELD2 = CONSTAT2 that table have 7 files and about 160,000 records.

        Finally INSERT is weird when it works takes about 5 minutes to insert a single row, but almost is hanged up, I'm doing the test with a singe row insert using constants.

        Booth servers are on the same LAN with no firewall or router between.

        Thanks in advance for the help.

    Regards

    Norzagaray

       

    Friday, November 12, 2010 7:34 PM

Answers

  • Switch from using 4-part named queries to using either openquery or exec calls, that will take care of part of the issue, especially with the Update, as SQL is going to download every record in the table to check the where clause, then send the update.

    The below is something I do often:

    EXEC ('SELECT * FROM CHARLIEE2.TEST') at CHARLIEE2_OSPRY_CLEAN
    go
    DELETE FROM OPENQUERY(CHARLIEE2_OSPRY_CLEAN,'SELECT * FROM CHARLIEE2.TEST')
    GO
    declare @i int
    declare @c char(10)
    set @i = 1
    while @i < 21
    	begin
    	set @c = cast (@i as char(10))
    	EXEC ('insert into CHARLIEE2.TEST values (?)',@c) at CHARLIEE2_OSPRY_CLEAN
    	set @i = @i + 1
    	end
    go
    EXEC ('SELECT * FROM CHARLIEE2.TEST') at CHARLIEE2_OSPRY_CLEAN
    go
    EXEC('UPDATE CHARLIEE2.TEST SET C1 = ''CHARLES'' WHERE C1 = ''1''') at CHARLIEE2_OSPRY_CLEAN
    go
    

    it's still not going to be super fast however on the inserts above, but better than using 4-part named inserts. We have also implemented openrowset with fastload, but this is only enabled when you are using SSIS so that you can move data from SQL to DB2 faster. I've never seen it take 5 minutes to insert a row however, no matter how I've done it, when using a linked server and the systems are 'local'. 5000 miles, maybe yes.

    I would suggest that if you continue to have some issue that you open a case with HIS Support.

    Below is another test file I've used in the past for select, inserts, passing parameters.

    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('98105','Issaquah',105,5,12346)
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('01581','Westboro',101,1,11)
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('01730','Bedford',101,2,12)
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('01833','Georgetown',101,3,13)
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('02116','Boston',101,4,14)
    
    -- Passing @myvar VARCHAR to AREAS table
    --DECLARE @myvar VARCHAR(5);
    --SET @myvar = '98105';
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREAID = ?',@myvar) AT charlotte_charliee2_english;
    --GO
    
    --DECLARE @myvar VARCHAR (5);
    --SET @myvar = '98105';
    --EXEC ('DELETE FROM charliee2.AREAS WHERE AREAID = ?',@myvar) AT charlotte_charliee2_english
    --GO
    
    -- Passing @myvar DECIMAL to AREAS table
    --DECLARE @myvar DECIMAL (9,0);
    --SET @myvar = 12346;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREADEC = ?',@myvar) AT charlotte_charliee2_english;
    --GO
    
    --DECLARE @myvar DECIMAL (9,0);
    --SET @myvar = 12346;
    --EXEC ('DELETE FROM charliee2.AREAS WHERE AREADEC = ?',@myvar) AT charlotte_charliee2_english
    --GO
    
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('98105','Issaquah',105,5,12346)
    --GO
    DECLARE @myareaid char(5);
    DECLARE @myareadesc varchar(20);
    DECLARE @myregionid integer;
    DECLARE @myareanum numeric(9,0);
    DECLARE @myareadec decimal(9,0);
    set @myareaid = '98105';
    set @myareadesc = 'Issaquah';
    set @myregionid = 105;
    set @myareanum = 5;
    set @myareadec = 12346;
    EXEC ('INSERT INTO charliee2.AREAS values(?,?,?,?,?)',@myareaid,@myareadesc,@myregionid,@myareanum,@myareadec) AT charlotte_charliee2_english
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREAID = ?',@myareaid) AT charlotte_charliee2_english;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREADESC = ?',@myareadesc) AT charlotte_charliee2_english;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE REGIONID = ?',@myregionid) AT charlotte_charliee2_english;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREANUM = ?',@myareanum) AT charlotte_charliee2_english;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREADEC = ?',@myareadec) AT charlotte_charliee2_english;
    --EXEC ('DELETE FROM charliee2.AREAS WHERE AREAID = ? and AREADESC = ? and REGIONID = ? and AREANUM = ? and AREADEC = ?',@myareaid,@myareadesc,@myregionid,@myareanum,@myareadec) AT charlotte_charliee2_english
    EXEC ('SELECT * FROM charliee2.AREAS') at charlotte_charliee2_english
    
    

    Charles Ezzell - MSFT
    Friday, November 12, 2010 9:15 PM

All replies

  • Switch from using 4-part named queries to using either openquery or exec calls, that will take care of part of the issue, especially with the Update, as SQL is going to download every record in the table to check the where clause, then send the update.

    The below is something I do often:

    EXEC ('SELECT * FROM CHARLIEE2.TEST') at CHARLIEE2_OSPRY_CLEAN
    go
    DELETE FROM OPENQUERY(CHARLIEE2_OSPRY_CLEAN,'SELECT * FROM CHARLIEE2.TEST')
    GO
    declare @i int
    declare @c char(10)
    set @i = 1
    while @i < 21
    	begin
    	set @c = cast (@i as char(10))
    	EXEC ('insert into CHARLIEE2.TEST values (?)',@c) at CHARLIEE2_OSPRY_CLEAN
    	set @i = @i + 1
    	end
    go
    EXEC ('SELECT * FROM CHARLIEE2.TEST') at CHARLIEE2_OSPRY_CLEAN
    go
    EXEC('UPDATE CHARLIEE2.TEST SET C1 = ''CHARLES'' WHERE C1 = ''1''') at CHARLIEE2_OSPRY_CLEAN
    go
    

    it's still not going to be super fast however on the inserts above, but better than using 4-part named inserts. We have also implemented openrowset with fastload, but this is only enabled when you are using SSIS so that you can move data from SQL to DB2 faster. I've never seen it take 5 minutes to insert a row however, no matter how I've done it, when using a linked server and the systems are 'local'. 5000 miles, maybe yes.

    I would suggest that if you continue to have some issue that you open a case with HIS Support.

    Below is another test file I've used in the past for select, inserts, passing parameters.

    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('98105','Issaquah',105,5,12346)
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('01581','Westboro',101,1,11)
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('01730','Bedford',101,2,12)
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('01833','Georgetown',101,3,13)
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('02116','Boston',101,4,14)
    
    -- Passing @myvar VARCHAR to AREAS table
    --DECLARE @myvar VARCHAR(5);
    --SET @myvar = '98105';
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREAID = ?',@myvar) AT charlotte_charliee2_english;
    --GO
    
    --DECLARE @myvar VARCHAR (5);
    --SET @myvar = '98105';
    --EXEC ('DELETE FROM charliee2.AREAS WHERE AREAID = ?',@myvar) AT charlotte_charliee2_english
    --GO
    
    -- Passing @myvar DECIMAL to AREAS table
    --DECLARE @myvar DECIMAL (9,0);
    --SET @myvar = 12346;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREADEC = ?',@myvar) AT charlotte_charliee2_english;
    --GO
    
    --DECLARE @myvar DECIMAL (9,0);
    --SET @myvar = 12346;
    --EXEC ('DELETE FROM charliee2.AREAS WHERE AREADEC = ?',@myvar) AT charlotte_charliee2_english
    --GO
    
    --insert into openquery(charlotte_charliee2_english,'Select * from charliee2.areas') values('98105','Issaquah',105,5,12346)
    --GO
    DECLARE @myareaid char(5);
    DECLARE @myareadesc varchar(20);
    DECLARE @myregionid integer;
    DECLARE @myareanum numeric(9,0);
    DECLARE @myareadec decimal(9,0);
    set @myareaid = '98105';
    set @myareadesc = 'Issaquah';
    set @myregionid = 105;
    set @myareanum = 5;
    set @myareadec = 12346;
    EXEC ('INSERT INTO charliee2.AREAS values(?,?,?,?,?)',@myareaid,@myareadesc,@myregionid,@myareanum,@myareadec) AT charlotte_charliee2_english
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREAID = ?',@myareaid) AT charlotte_charliee2_english;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREADESC = ?',@myareadesc) AT charlotte_charliee2_english;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE REGIONID = ?',@myregionid) AT charlotte_charliee2_english;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREANUM = ?',@myareanum) AT charlotte_charliee2_english;
    --EXEC ('SELECT * FROM charliee2.AREAS WHERE AREADEC = ?',@myareadec) AT charlotte_charliee2_english;
    --EXEC ('DELETE FROM charliee2.AREAS WHERE AREAID = ? and AREADESC = ? and REGIONID = ? and AREANUM = ? and AREADEC = ?',@myareaid,@myareadesc,@myregionid,@myareanum,@myareadec) AT charlotte_charliee2_english
    EXEC ('SELECT * FROM charliee2.AREAS') at charlotte_charliee2_english
    
    

    Charles Ezzell - MSFT
    Friday, November 12, 2010 9:15 PM
  • Charles:

          I'm glad of getting answer from you!. That's because before posting my question, I already reviewed several trhead solved by you, and I think you are the right person to help me by your deep knowledge of the DB2OLEDB.

          I had to uninstall the SQL test server due hardware limitations. I'll reinstall on a week and then I'll attemp as your instructions.

          So don't think that I lost my interest or the problem is solved due the week late replay of the results of the tests.

          In the mean while I have to read about SSIS, that is something new for me, and as you say is the best way to move records from SQL to DB2.

          Which readding for learning SSIS do you suggest?

          Thank for your kidness.

    Regards

    Sergio Norzagaray

    Tuesday, November 16, 2010 2:59 AM
  • To be honest, my knowledge of SSIS has been from working with some of our SQL engineers in-house, and I started (years ago) by running the samples that are available in the online documentation. Once I had a simple foundation there, I moved on. It's difficult to recommend any books (I'm not supposed to), but there are plenty out there, and if you go to sqlservercentral.com and ask, I'm sure you will get plenty of advice (or search for 'books on ssis' will get you plenty of hits also).
    Charles Ezzell - MSFT
    Tuesday, November 23, 2010 3:57 PM