October 4, 2005 at 1:29 pm
We have iSeries client 5.2 installed and are using a linked server to access our iSeries tables. It takes 9 minutes to move 69,000 rows across to SQL Server. Our SQL statement is
insert into tbl_sql
select * from linked_server.catalog.library.table_name
Is there any performance enhancements that can be changed on the data source (i.e. odbc)?
We have tables are large as 500,000 rows to move across on a nightly basis.
October 4, 2005 at 6:07 pm
You are moving the entire table. You can try the pass-through query syntax using openquery which will offload the query processing on the iSeries side - however, it will still not be optimal as you are talking about reading half a million records without any filter criteria and then moving them over.
A faster option might be to extract that data out into text file and do a BULK INSERT into the SQL Server table. Use DTS to generate the text file and then the Bulk INsert task within DTS to do the bulk insert into SQL Server and see if that helps.
October 5, 2005 at 6:03 am
we move millions of rows via ODBC with an DTS-package like this:
DECLARE @sqlstr varchar(4000)
SET @bibl_iSeries = 'TRANSFER'
SET @sqlstr = 'SELECT * INTO SQLTAB FROM ' + @bibl_iSeries + '.LOCAL.' + @bibl_iSeries +'.AS400TAB'
EXEC(@sqlstr)
October 5, 2005 at 6:27 am
Ralf, do I understand this correctly? You execute a string that reads:
SELECT * INTO SQLTAB FROM TRANSFER.LOCAL.TRANSFER.AS400TAB
I've never called a DTS package from T-SQL like that. Can you elloborate on the syntax. It's obviously not Server.Database.owner.table...
RH
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply