Repost - Load records via stored procedure or ??

[login to unmask email]

Repost - Load records via stored procedure or ??
This is a repost from 1/3/03 because I didn't receive any responses. There
has to be someone out there who has implemented a similar situation that
can share information isn't there????

I am looking for alternatives for a web application issuing 100,000+
inserts at a time into DB2 tables on OS/390. I have suggested that a
stored procedure bound to OS/390 write to a file instead of issuing the
inserts and load that file into DB2 using the load utility. I have
suggested possibly using stored procedure DSNUTILS to invoke the load
utility but not sure yet. Are there any shops out there that have done
this type of implementation and if so how? Did you see a big performance
gain? We are on DB2V7.1 and use WLM enabled environment for the stored
procedures.
Thanks,
Michelle



Christopher Matt

Re: Repost - Load records via stored procedure or ??
(in response to mmetcalf@NOTES.STATE.NE.US)
Michelle,
You might want to try the "DB2 family cross loader function", new in
vers7. The EXEC SQL utility control statement declares cursors or executes
dynamic SQL statements as part of the DB2 family
cross loader function . Heres an example

Example 1: Load data using a declared cursor. Load MYEMP table with the
results of the SELECT statement declared for cursor C1.

EXEC SQL
DECLARE C1 CURSOR FOR SELECT * FROM DSN8710.EMP
ENDEXEC

LOAD DATA
INCURSOR(C1)
REPLACE
INTO TABLE MYEMP
STATISTICS


Here's some additional discussion on usage :

| You can directly load the output of a SQL SELECT statement into a table
on
| DB2 for OS/390. You can declare a cursor or execute one of the dynamic
SQL
| statements listed in Table 19 in topic 2.10.1.1.1 using the EXEC SQL
| statement. Use the result table from the cursor declared in the EXEC SQL
# statement as input to the LOAD utility statement defined with the
INCURSOR
# option.

# You can load the output from any SELECT statement directly into a table
on
# DB2 for OS/390 and z/OS. Use the EXEC SQL utility control statement to
# execute dynamic SQL statements. Because the SELECT statement can access
# any DRDA server, the data source can be any member of the DB2 family,
# DataJoiner, or any other vendor that supports DRDA server capabilities.
# This extension to the LOAD utility provides synergy between the power of
# the LOAD utility and the connectivity function and reliability of DRDA.
----------------------------------------------------------------------------

Christopher Matt

IBM Global Services , Southbury, Ct
[login to unmask email]



|---------+---------------------------->
| | [login to unmask email]|
| | ATE.NE.US |
| | Sent by: DB2 Data|
| | Base Discussion |
| | List |
| | <[login to unmask email]|
| | LASSOC.COM> |
| | |
| | |
| | 01/14/2003 10:45 |
| | AM |
| | Please respond to|
| | DB2 Data Base |
| | Discussion List |
| | |
|---------+---------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
| |
| To: [login to unmask email] |
| cc: |
| Subject: Repost - Load records via stored procedure or ?? |
| |
>------------------------------------------------------------------------------------------------------------------------------|




This is a repost from 1/3/03 because I didn't receive any responses. There
has to be someone out there who has implemented a similar situation that
can share information isn't there????

I am looking for alternatives for a web application issuing 100,000+
inserts at a time into DB2 tables on OS/390. I have suggested that a
stored procedure bound to OS/390 write to a file instead of issuing the
inserts and load that file into DB2 using the load utility. I have
suggested possibly using stored procedure DSNUTILS to invoke the load
utility but not sure yet. Are there any shops out there that have done
this type of implementation and if so how? Did you see a big performance
gain? We are on DB2V7.1 and use WLM enabled environment for the stored
procedures.
Thanks,
Michelle



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Edward Long

Re: Repost - Load records via stored procedure or ??
(in response to Christopher Matt)
Hi Michelle.
Given that the 7.1 load utility has all those smart
locking features, I kind of like your DSNUTIL Stored
Procedure approach.

Essentially, the Stored Procedure invokes the load
utility (Log Yes of course). You could use the cursor
option if the data is already in a table somewhere.

For any of these options you have to figure out the
restart scenarios. What ya gonna do(I watch COPS)
when whatever you do crashes.

Keep us posted.
--- [login to unmask email] wrote:
> This is a repost from 1/3/03 because I didn't
> receive any responses. There
> has to be someone out there who has implemented a
> similar situation that
> can share information isn't there????
>
> I am looking for alternatives for a web application
> issuing 100,000+
> inserts at a time into DB2 tables on OS/390. I have
> suggested that a
> stored procedure bound to OS/390 write to a file
> instead of issuing the
> inserts and load that file into DB2 using the load
> utility. I have
> suggested possibly using stored procedure DSNUTILS
> to invoke the load
> utility but not sure yet. Are there any shops out
> there that have done
> this type of implementation and if so how? Did you
> see a big performance
> gain? We are on DB2V7.1 and use WLM enabled
> environment for the stored
> procedures.
> Thanks,
> Michelle
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://listserv.ylassoc.com. The owners of the list
> can be reached at
[login to unmask email]


=====
Edward Long



Dave Nance

Re: Repost - Load records via stored procedure or ??
(in response to Edward Long)
Michelle,
I don't see how having the stored procedure write to a flat file 100,000 times, then doing a load is going to save you anything. To be honest, it seems more like it would cost you a bit more CPU time. Are you having locking/contention issues? Or some other problem?

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 1/14/03 10:45:45 AM >>>
This is a repost from 1/3/03 because I didn't receive any responses. There
has to be someone out there who has implemented a similar situation that
can share information isn't there????

I am looking for alternatives for a web application issuing 100,000+
inserts at a time into DB2 tables on OS/390. I have suggested that a
stored procedure bound to OS/390 write to a file instead of issuing the
inserts and load that file into DB2 using the load utility. I have
suggested possibly using stored procedure DSNUTILS to invoke the load
utility but not sure yet. Are there any shops out there that have done
this type of implementation and if so how? Did you see a big performance
gain? We are on DB2V7.1 and use WLM enabled environment for the stored
procedures.
Thanks,
Michelle




This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.



[login to unmask email]

Re: Repost - Load records via stored procedure or ??
(in response to Dave Nance)
David,

I am under the assumption that the LOAD utility outperforms a program
issuing inserts by 50 to 75%. Each insert into the DB2 table invokes DB2
while the load utility should invoke DB2 one time. This would also reduce
the number of calls to the stored procedure that issues the insert. We have
also thought about using either a declared temporary table or a global
temporary table in the program to temporarily hold the data to be loaded or
inserted but haven't tested this out yet and don't know if it will buy us
anything either. We had noticed that there is quite a bit of IO wait time
and IO wait counts so we thought we could improve that. The stored
procedure that issues the insert only has that insert statement in the
program, nothing else but it is inserting into a 28 million row, 28
partitioned table so this may be all the better it gets!

If I'm wrong in assuming this let me know!

Thanks for responding!
Michelle
=====================

Date: Tue, 14 Jan 2003 12:11:30 -0500
From: David Nance <[login to unmask email]>
Subject: Re: Repost - Load records via stored procedure or ??

Michelle,=20
I don't see how having the stored procedure write to a flat file =
100,000 times, then doing a load is going to save you anything. To be =
honest, it seems more like it would cost you a bit more CPU time. Are you =
having locking/contention issues? Or some other problem?

Dave Nance
First Health Services, Corp.
(804)527-6841