| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-05-09, 10:26
|
|
Registered User
|
|
Join Date: Nov 2009
Location: Dusseldorf, Germany
Posts: 3
|
|
|
Multiple row insert
|
|
Hallo,
I have a question about DB2 9.1.6 for LUW:
For DB2 UDB V8 for z/OS, an application program can issue a multiple-row insert using static SQL like this:
EXEC SQL
INSERT INTO DSN8810.ACT
(ACTNO, ACTKWD, ACTDESC)
VALUES (:HVA1, :HVA2, :HVA3)
FOR :NUM-ROWS ROWS
END-EXEC.
I checked the manuals for DB2 9.1.6 for LUW, but I didn't find anything similar...
Does a feature like this exist for this DB2 version?
If not, will it be available in a later version?
Thanks in advance and kind regards!
Frank
|
|
11-05-09, 10:44
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
No, it does not exist for DB2 for LUW. You will have to as IBM if it will ever be implemented.
Andy
|
|
11-05-09, 10:44
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
|
|
11-05-09, 11:27
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
What you can do in LUW is the standard stuff:
Code:
INSERT INTO ...
VALUES ( 1st-row ),
( 2nd-row ),
...;
Or you use dynamic SQL and compose the SQL statement that way.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
11-05-09, 11:58
|
|
∞∞∞∞∞∞
|
|
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
|
|
There is "insert buf" bind parameter for "buffered insert". I've only used it with import in a multi-partitioned env. Don't know if this is similar to multiple-row insert on z/OS.
|
|
11-05-09, 12:09
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by db2girl
There is "insert buf" bind parameter for "buffered insert". I've only used it with import in a multi-partitioned env. Don't know if this is similar to multiple-row insert on z/OS.
|
He want to insert from the AREA which is not a possible.
He can create a file and use LOAD utility to solve this problem. 
Lenny
|
|
11-05-09, 20:25
|
|
Registered User
|
|
Join Date: Feb 2009
Posts: 114
|
|
Yep, like I have always said, UNIX is only 30 years or so behind z/OS
|
|
11-05-09, 23:51
|
|
Registered User
|
|
Join Date: Nov 2009
Location: Bangalore
Posts: 25
|
|
|
Last edited by sawangupta; 11-05-09 at 23:54.
Reason: More Info
|
11-06-09, 05:17
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
The above was static SQL. No multi-row insert as on z/OS is available in DB2 LUW for that API.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
11-07-09, 12:53
|
|
Registered User
|
|
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 555
|
|
Quote:
Originally Posted by stratmf
Hallo,
I have a question about DB2 9.1.6 for LUW:
For DB2 UDB V8 for z/OS, an application program can issue a multiple-row insert using static SQL like this:
EXEC SQL
INSERT INTO DSN8810.ACT
(ACTNO, ACTKWD, ACTDESC)
VALUES (:HVA1, :HVA2, :HVA3)
FOR :NUM-ROWS ROWS
END-EXEC.
I checked the manuals for DB2 9.1.6 for LUW, but I didn't find anything similar...Frank
|
Yes, static SQL does exist in DB2/LUW, just use sqlj instead of JDBC.
This example,however, looks like cobol with inline SQL. That also works for LUW, even with open-source cobol compilers. Just install open-cobol on your linux-box (together with db2) and copy this source and call it "cblsql2.sqb"
Code:
identification division.
program-id. cblsql2.
environment division.
input-output section.
file-control.
data division.
file section.
*
working-storage section.
77 w-start-ws pic X(08) value 'Start WS'.
77 w-sqlcode pic z(8)9+.
77 w-dis-count pic z(8)9+.
77 displ-salary pic -Z,ZZZ,ZZ9.99.
01 switsjes.
03 sw-800-curs pic 9.
88 sw-800-curs-open value 1.
88 sw-800-curs-fets value 2.
88 sw-800-curs-clos value 3.
/ DB2 thingies
EXEC SQL
include sqlca
END-EXEC.
EXEC SQL begin declare section END-EXEC.
01 w-userid pic x(08).
01 w-password pic x(08).
01 w-count pic S9(04) comp-5.
01 w-empno pic x(06) value space.
01 w-1st-name.
49 w-1st-name-len pic S9(04) comp-5
value zero.
49 w-1st-name-dat pic x(12) value space.
01 w-birthdate pic x(10) value space.
01 w-workdept pic x(03) value space.
01 w-workdept-NULL pic S9(04) comp-5.
01 w-salary pic S9(7)V9(2) comp-3.
01 k-empno-start pic x(06) value low-value.
01 k-empno-stop pic x(06) value high-value.
EXEC SQL end declare section END-EXEC.
/
PROCEDURE DIVISION.
000-000-main section.
************************************
000-010.
perform 020-000-init-connect.
perform 100-000-main-process.
perform 090-000-exit-reset.
000-090.
stop run.
/
020-000-init-connect section.
************************************
020-010.
EXEC SQL
connect to sample
END-EXEC.
if SQLCODE not equal zero
then move SQLCODE to w-sqlcode
display 'CONNECT failed with rc ' w-sqlcode
else display 'CONNECT :)'
end-if.
020-090.
exit.
*
090-000-exit-reset section.
************************************
0090-010.
EXEC SQL
connect reset
END-EXEC.
if SQLCODE not equal zero
then move SQLCODE to w-sqlcode
display 'reset CONNECT failed with rc ' w-sqlcode
else display 'reset CONNECT :)'
end-if.
0090-090.
exit.
100-000-main-process section.
************************************
100-010.
set sw-800-curs-open to true.
perform 800-000-process-emply-cursor.
if SQLCODE equal zero
then set sw-800-curs-fets to true
perform 800-000-process-emply-cursor
perform until SQLCODE not equal zero
perform 110-000-process-emply-row
perform 800-000-process-emply-cursor
end-perform
end-if
if SQLCODE equal +100
then set sw-800-curs-clos to true
perform 800-000-process-emply-cursor
end-if.
100-090.
exit.
110-000-process-emply-row section.
************************************
110-010.
if w-workdept-NULL less
than zero
then move space to w-workdept
end-if
move w-salary to displ-salary
display w-empno space
w-1st-name-dat space
w-workdept space
w-birthdate space
displ-salary.
110-090.
exit.
/ CURSOR
800-000-process-emply-cursor section.
*************************************
* Declare
EXEC SQL declare c8000 cursor for
select empno
, firstnme
, workdept
, birthdate
, coalesce(salary,-1)
from employee
where empno between :k-empno-start
and :k-empno-stop
order by firstnme
END-EXEC.
800-010.
evaluate true
* Open
when sw-800-curs-open
EXEC SQL
open c8000
END-EXEC
* Fetch
when sw-800-curs-fets
initialize w-1st-name
EXEC SQL
fetch c8000
into :w-empno
, :w-1st-name
, :w-workdept
:w-workdept-NULL
, :w-birthdate
, :w-salary
END-EXEC
* Close
when other
EXEC SQL
close c8000
END-EXEC
end-evaluate.
if SQLCODE equal zero or +100
then continue
else move SQLCODE to w-sqlcode
display 'sqlCode : ' w-sqlcode space
display 'sw-800-curs : ' sw-800-curs space
display 'sqlerrm : ' sqlerrm space
end-if.
800-090.
exit.
This cobol program will read & display your employee table. Use this script to (pre)compile, link & bind this:
Code:
#!/bin/sh -x
#
rm ./${1}.cbl
rm ./${1}
db2 connect to sample
db2 prep ${1}.sqb bindfile target ANSI_COBOL
/usr/bin/cobc ${1}.cbl -t ${1}.lst -Wall -L${HOME}/sqllib/lib -ldb2 -v -x -save-temps
db2 bind ${1}.bnd
db2 connect reset
./${1}
and there you have it: cobol with static sql on you PC just like you're used to on the mainframe.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Multiple row insert
