iSeries as Linked Server
--------------------------------------------------------------------------------
I have set up the iSeries as linked server (in 2005).
When I execute a view from Sequel Server against a table (selecting just 1 record where the selection matches the keys) the response time is terrible, 25+ seconds. When I execute the same select using the iSeries navigator the response time is about 1 second. Does anyone have some tips on how to improve performance etc etc.
Also I am trying to execute (from sequel server) a stored procedure on the iSeries, I get an error and looks like I have a syntax problem:
EXEC AS400SRV_IBMDA400_OLEDB.vgsysb.qgpl.get_company' 010'
The error:
OLE DB provider "IBMDA400" for linked server "AS400SRV_IBMDA400_OLEDB" returned message "SQL0104: Token { was not valid. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN.
Cause . . . . . : A syntax error was detected at token {. Token { is not a valid token. A partial list of valid tokens is ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token {. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.".
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'getcompany' on remote server 'AS400SRV_IBMDA400_OLEDB'.
The strored procedure is Get_Company where I pass in a parameter of 10 char.
create procedure getcompany
(in company varchar(10))
result set 1
language sql
modifies sql data
begin
declare C1 scroll cursor with return for
select drky, drdl01 from vgiprdcom/f0005
where drsy = '00' and drrt = '01' and drky = company;
open C1;
end;
--------------------------------------------------------------------------------
I have set up the iSeries as linked server (in 2005).
When I execute a view from Sequel Server against a table (selecting just 1 record where the selection matches the keys) the response time is terrible, 25+ seconds. When I execute the same select using the iSeries navigator the response time is about 1 second. Does anyone have some tips on how to improve performance etc etc.
Also I am trying to execute (from sequel server) a stored procedure on the iSeries, I get an error and looks like I have a syntax problem:
EXEC AS400SRV_IBMDA400_OLEDB.vgsysb.qgpl.get_company' 010'
The error:
OLE DB provider "IBMDA400" for linked server "AS400SRV_IBMDA400_OLEDB" returned message "SQL0104: Token { was not valid. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN.
Cause . . . . . : A syntax error was detected at token {. Token { is not a valid token. A partial list of valid tokens is ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token {. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.".
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'getcompany' on remote server 'AS400SRV_IBMDA400_OLEDB'.
The strored procedure is Get_Company where I pass in a parameter of 10 char.
create procedure getcompany
(in company varchar(10))
result set 1
language sql
modifies sql data
begin
declare C1 scroll cursor with return for
select drky, drdl01 from vgiprdcom/f0005
where drsy = '00' and drrt = '01' and drky = company;
open C1;
end;