Geek asks and answers

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

I am trying to connect from a Microsoft SQL server to AS / 400 so that I can pull data from AS / 400 and then mark the data as popped.

I have successfully created an OLE DB connection "IBMDASQL" and can retrieve data with some data, but I have a problem when I try to retrieve data from a very large table.

This is normal, and returns an account of 170 million:

select count(*)
from transactions

This request is completed 15 hours before I give up. (It should return zero, since I have not marked anything as "in process" yet)

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

I am a Microsoft guy, but my AS / 400 guy says that there is an index in the “processed” column and locally this query is executed instantly.

, ? 68 :

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

, , AS/400, , .

+3
6

, , , :

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

.

.

1) AS400 SQL-,
2) AS400 , , AS400 , , , "", sql, - "" v/s "". - , .

-, 170 - , , SQL-, , SSIS, SQL, , ?

+3

, , , SQL2005, ,

AS400 -, AS400

  • AS400.
  • STRSQL

  • AS400 , /

    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

  • AS400

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

AS400 SP 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

,

+2

IBM Redbook " SQL" IBM DB2 Universal Database iSeries, , .

IBM . , , , .

, OLEDB 100% , VisualExplain ( ), .

+1

? - WHERE , , , , 170 , WHERE.

+1

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

0
source

More articles:


All Articles