CODEWITHSUNDEEP
t-sqlssisodbcoledbibm-midrange
TizzyFoe
TizzyFoe

Reputation: 1499

Creating a connection from Microsoft SQL server to an AS/400

I'm trying to connect from Microsoft SQL server to as AS/400 so i can pull data from the AS/400 then flag the data as being pulled.

I've successfully created and OLE DB "IBMDASQL" connection, and am able to pull data some data, but i'm running into an issue when i try to pull data from a very large table

This runs fine, and returns a count of 170 million:

select count(*)
from transactions

This query executed for 15 hours before i gave up on it. (It should return zero since i haven't flagged anything as 'in process' yet)

select count(*) 
from transactions
where processed = 'In process'

I'm a Microsoft guy, but my AS/400 guy says that there is an index on the 'processed' column and that locally, that query run instantaneously.

Any thoughts on what i might be doing wrong? I found a table with only 68 records in it, and was able to run this query in about a second:

select count(*)
from smallTable
where RandomColumn = 'randomValue'

So I know that the AS/400 is at least able to understand that type of query.

Upvotes: 3

Views: 6160

Answers (6)

Greldak
Greldak

Reputation: 11

Could this be a collation problem? - your WHERE clause is testing on a text field and if the collations of the two servers don't match this clause will be applied clientside rather than serverside so you are first of all pulling all 170 million records down to the client and then performing the WHERE clause on it there.

Upvotes: 1

TizzyFoe
TizzyFoe

Reputation: 1499

We found that running the query like this performed liked expected:

SELECT *
FROM OpenQuery( LinkedServer,
    'select count(*) 
    from transactions
    where processed = ''In process''')
GO

Upvotes: 1

Luka Milani
Luka Milani

Reputation: 1541

I would suggest this way to have good performance, i suppose you have at least SQL2005, i havent tested yet but this is a tip

Let the AS400 perform the select in native way by creating stored procedure in the AS400

  1. open a AS400 session
  2. launch STRSQL

  3. create an AS400 stored procedure in this way to get/update the recordset

    CREATE PROCEDURE MYSELECT (IN PARAM CHAR(10))
LANGUAGE SQL 
DYNAMIC RESULT SETS 1 
BEGIN 
DECLARE C1 CURSOR FOR SELECT * FROM MYLIB.MYFILE WHERE MYFIELD=PARAM;
OPEN C1;
RETURN; 
END

  4. create an AS400 stored procedure to update the recordset

    CREATE PROCEDURE MYUPDATE (IN PARAM CHAR(10))
LANGUAGE SQL 
RESULT SETS 0 
BEGIN 
UPDATE MYLIB.MYFILE SET MYFIELD='newvalue' WHERE MYFIELD=PARAM;
END

Call those AS400 SP from SQL SERVER

declare @myParam char(10)
set @myParam = 'In process'
-- get the recordset
EXEC ('CALL NAME_AS400.MYLIB.MYSELECT(?) ', @myParam) AT AS400 -- < AS400 = name of linked server
-- update
EXEC ('CALL NAME_AS400.MYLIB.MYUPDATE(?) ', @myParam) AT AS400

Hope it helps

Upvotes: 2

James Allman
James Allman

Reputation: 41168

I recommend following the suggestions in the IBM Redbook SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries to determine what's really happening.

IBM technical support can also be extremely helpful in diagnosing issues such as these. Don't be afraid to get in touch with them as the software support is generally included as part of the maintenance contract and there is no charge to talk to them.

I've seen OLEDB connections eat up 100% cpu for hours and when the same query is run through VisualExplain (query analyzer) it estimates mere seconds to execute.

Upvotes: 1

Mike Wills
Mike Wills

Reputation: 21265

Based on the past interactions I have had, the query should take about the same amount of time no matter how you access the data. Another thought would be if you could create a view on the table to get the data you need or use a stored procedure.

Upvotes: 0

Ta01
Ta01

Reputation: 31610

I have had to fight this battle many times.

There are two ways of approaching this.

1) Stage your data from the AS400 into SQL server where you can optimize your indexes
2) Ask the AS400 folks to create logical views which speed up data retrieval, your AS400 programmer is correct, index will help but I forget the term they use to define a "view" similar to a sql server view, I beleive its something like "physical" v/s "logical". Logical is what you want.

Thirdly, 170 million is a lot of records, even for a relational database like SQL server, have you considered running an SSIS package nightly that stages your data into your own SQL table to see if it improves performance?

Upvotes: 3

Related Questions