From: Charlie on
Hi All.
I just trying to understand why a stored procedure call takes more
time to response than an ODBC request. My company use JDEdwards with
DB2 UDB database server. JDEdwards use ODBC technology to retrieve
information from our DB2 server and I created an external application
that consume the same data but via Stored Procedures calls. So I
noticed that my application consume more time to retrieve the same
data comparing with a JDEdwards Application. Both applications JDE
and my are fat clients, so from network perspective there is not
difference. I even create a Stored Procedure using the same SQL
Script that is executed from JDE through ODBC and it keep consuming
more time to response. I ran the SP directly from SQL Tool Script
(iSeries Navigator) and I have the same result. Below is an example of
my SP Script, JDEdwards use its own iSeries database user credentials
to connect, and my app use a different user, but I don't see that
database user profile could be an issue.

Thanks for your help!

CREATE PROCEDURE JDEINT.COM_GETSALESORDERHEADER(
IN COMPANY VARCHAR(5) ,
IN ORDERTYPE VARCHAR(3) ,
IN ORDERNUMBER DOUBLE PRECISION ,
IN INSTALLATIONID VARCHAR(3) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC JDEINT.COM_GETSALESORDERHEADER
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN

DECLARE TEMP_CURSOR1 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT
A . SHKCOO AS COMPANY ,
A . SHDOCO AS ORDERNUMBER ,
A . SHDCTO AS ORDERTYPE ,
SHMCU AS BRANCHPLANT ,
SHOKCO AS ORIGINALORDERCO ,
SHOORN AS ORIGINALORDERNO ,
SHOCTO AS ORIGINALORDERTYPE ,
SHAN8 AS CUSTOMERNUMBER ,
SHSHAN AS SHIPTOID
FROM CRPDTA . F4201 A
WHERE
A . SHDOCO = ORDERNUMBER
AND A . SHDCTO = ORDERTYPE
AND A . SHKCOO = COMPANY
UNION
SELECT
A . SHKCOO AS COMPANY ,
A . SHDOCO AS ORDERNUMBER ,
A . SHDCTO AS ORDERTYPE ,
SHMCU AS BRANCHPLANT ,
SHOKCO AS ORIGINALORDERCO ,
SHOORN AS ORIGINALORDERNO ,
SHOCTO AS ORIGINALORDERTYPE ,
SHAN8 AS CUSTOMERNUMBER ,
SHSHAN AS SHIPTOID
FROM CRPDTA . F42019 A
WHERE
A . SHDOCO = ORDERNUMBER
AND A . SHDCTO = ORDERTYPE
AND A . SHKCOO = COMPANY
FOR FETCH ONLY ;


OPEN TEMP_CURSOR1 ;

END ;
From: Kent Milligan on
Each SQL interface has a different set of attributes and settings that can cause
the query optimizer to implement a different access plan. In your case, I'd
guess that the optimization goal setting of the SP is the cause. You could try
adding this setting to the cursor declaration: OPTIMIZE FOR 10 ROWS


To learn more about the settings that impact the optimizer, I'd strongly
encourage you to attend the DB2 for i SQL Performance Workshop.
http://ibm.com/systems/i/software/db2/db2educ_m.html


Charlie wrote:
> Hi All.
> I just trying to understand why a stored procedure call takes more
> time to response than an ODBC request. My company use JDEdwards with
> DB2 UDB database server. JDEdwards use ODBC technology to retrieve
> information from our DB2 server and I created an external application
> that consume the same data but via Stored Procedures calls. So I
> noticed that my application consume more time to retrieve the same
> data comparing with a JDEdwards Application. Both applications JDE
> and my are fat clients, so from network perspective there is not
> difference. I even create a Stored Procedure using the same SQL
> Script that is executed from JDE through ODBC and it keep consuming
> more time to response. I ran the SP directly from SQL Tool Script
> (iSeries Navigator) and I have the same result. Below is an example of
> my SP Script, JDEdwards use its own iSeries database user credentials
> to connect, and my app use a different user, but I don't see that
> database user profile could be an issue.
>
> Thanks for your help!
>
> CREATE PROCEDURE JDEINT.COM_GETSALESORDERHEADER(
> IN COMPANY VARCHAR(5) ,
> IN ORDERTYPE VARCHAR(3) ,
> IN ORDERNUMBER DOUBLE PRECISION ,
> IN INSTALLATIONID VARCHAR(3) )
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
> SPECIFIC JDEINT.COM_GETSALESORDERHEADER
> NOT DETERMINISTIC
> MODIFIES SQL DATA
> CALLED ON NULL INPUT
> SET OPTION ALWBLK = *ALLREAD ,
> ALWCPYDTA = *OPTIMIZE ,
> COMMIT = *NONE ,
> DECRESULT = (31, 31, 00) ,
> DFTRDBCOL = *NONE ,
> DYNDFTCOL = *NO ,
> DYNUSRPRF = *USER ,
> SRTSEQ = *HEX
> BEGIN
>
> DECLARE TEMP_CURSOR1 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
> SELECT
> A . SHKCOO AS COMPANY ,
> A . SHDOCO AS ORDERNUMBER ,
> A . SHDCTO AS ORDERTYPE ,
> SHMCU AS BRANCHPLANT ,
> SHOKCO AS ORIGINALORDERCO ,
> SHOORN AS ORIGINALORDERNO ,
> SHOCTO AS ORIGINALORDERTYPE ,
> SHAN8 AS CUSTOMERNUMBER ,
> SHSHAN AS SHIPTOID
> FROM CRPDTA . F4201 A
> WHERE
> A . SHDOCO = ORDERNUMBER
> AND A . SHDCTO = ORDERTYPE
> AND A . SHKCOO = COMPANY
> UNION
> SELECT
> A . SHKCOO AS COMPANY ,
> A . SHDOCO AS ORDERNUMBER ,
> A . SHDCTO AS ORDERTYPE ,
> SHMCU AS BRANCHPLANT ,
> SHOKCO AS ORIGINALORDERCO ,
> SHOORN AS ORIGINALORDERNO ,
> SHOCTO AS ORIGINALORDERTYPE ,
> SHAN8 AS CUSTOMERNUMBER ,
> SHSHAN AS SHIPTOID
> FROM CRPDTA . F42019 A
> WHERE
> A . SHDOCO = ORDERNUMBER
> AND A . SHDCTO = ORDERTYPE
> AND A . SHKCOO = COMPANY
> FOR FETCH ONLY ;
>
>
> OPEN TEMP_CURSOR1 ;
>
> END ;


--
Kent Milligan
ISV Enablement - System i
kmill(a)us.eye-bee-m.com (spam trick) GO HAWKEYES!!
>>> ibm.com/iseries/db2
(opinions stated are not necessarily those of my employer)
From: Serge Rielau on
in addition there is indeed an extra cost.
First you execute the CALL statement which then executes the stored
procedure which needs some basic initialization which then opens the cursor.

In trivial procedures this can be a "feel-able" overhead.
For TPC-C we do not use any stored procedures.
Instead we use inline functions for encapsulation since they are macro
expanded.

Cheers
Serge


--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

From: Mark A on
"Serge Rielau" <srielau(a)ca.ibm.com> wrote in message
news:7skho3FgomU1(a)mid.individual.net...
> in addition there is indeed an extra cost.
> First you execute the CALL statement which then executes the stored
> procedure which needs some basic initialization which then opens the
> cursor.
>
> In trivial procedures this can be a "feel-able" overhead.
> For TPC-C we do not use any stored procedures.
> Instead we use inline functions for encapsulation since they are macro
> expanded.
>
> Cheers
> Serge
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab

Would IBM be willing to publish specific examples of "inline functions for
encapsulation" as used in TPC-C?


From: Hardy on

"Mark A" <noone(a)nowhere.com> д����Ϣ
news:hk4dd8$dt7$1(a)news.eternal-september.org...
> "Serge Rielau" <srielau(a)ca.ibm.com> wrote in message
> news:7skho3FgomU1(a)mid.individual.net...
>> in addition there is indeed an extra cost.
>> First you execute the CALL statement which then executes the stored
>> procedure which needs some basic initialization which then opens the
>> cursor.
>>
>> In trivial procedures this can be a "feel-able" overhead.
>> For TPC-C we do not use any stored procedures.
>> Instead we use inline functions for encapsulation since they are macro
>> expanded.
>>
>> Cheers
>> Serge
>> --
>> Serge Rielau
>> SQL Architect DB2 for LUW
>> IBM Toronto Lab
>
> Would IBM be willing to publish specific examples of "inline functions for
> encapsulation" as used in TPC-C?
>

try this article by Serge,

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0411rielau/?S_TACT=105AGX52&S_CMP=cn-a-db2