DB2 insert multiple rows

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Folks,

My requirement is to insert a DB2 table 78 times using cics program. The main program calls its subroutine 78 times to insert 78 records into the table. Do we have mass insert to do this operation? Please suggest.

Thanks,
Murali.

Gnanas N · Posted: Fri Mar 26, 2010 11:33 am

Hi Murali,

Please check and see if you can use Multiple-row INSERT . This was introduced in DB2 UDB for z/OS Version 8.

The link.
publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.relg/fmlt.htm

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

I am getting compilation error when i tried for mass insert,

DSNHSMUD LINE 197 COL 22 ATTEMPT TO USE NEW FUNCTION WITH NEWFUN NO.

for the below insert sql

EXEC SQL
INSERT INTO tab1
VALUES ( :WS-C0
,:WS-C1
,:WS-C2
,:WS-C3
,:WS-C4
,:WS-C5
,:WS-C6
,:WS-C7)
FOR :NUM-ROWS ROWS
END-EXEC.
WHERE C0....C7 are host variable arrays.

num-rows is defined as S9(04) COMP.

From the link ,

ashimer · Posted: Mon Mar 29, 2010 2:47 pm

Version of DB2 in your shop ?
Seems it is in New Function Mode - in middle of a migration.

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Ashimer,

You are right. the version we are using is 9 rel 1.0. The possibility of getting this newfun no error is,

DSNH4700I
E csectname LINE nnnn COL cc ATTEMPT TO USE NEW FUNCTION WITH NEWFUN NO

Explanation
When the SQL processing option NEWFUN has a value of NO, you cannot use syntax for functions that this release of DB2® introduces. An attempt was made to use one of these functions.

System action
If the containing SQL statement is a declaration, it is discarded. If the containing SQL statement is executable, it is replaced by host language statements that will, if executed, set an error code in the SQLCA.

Programmer response
Do one of these things:
Restrict your program to functions that earlier releases of DB2 introduced. These functions are allowed regardless of the value of NEWFUN.
Process your program with a value of YES for the NEWFUN option. However, you cannot BIND the DBRM until New Function Mode has been enabled.
Severity
8 (error)

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

How to identify which row causes -803 while inserting 10 rows from host variable array to coulmn of a table?

Thanks,
Murali.

dbzTHEdinosauer · Posted: Tue Mar 30, 2010 9:13 pm

Murali,

go back to the documentation. as of vsn 7 there are additional sql calls that you can make - e.g. GET DIAGNOSTICS
which you tell you which COBOL INTERNAL table item caused the -803.

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Great Dick,

Thanks for recollecting about GET DIAGNOSTICS .

Murali.

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

All,

What about the performance while executing mass insert will it affects the performance?

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

i have one more doubt, while inserting 78 rows if i get -803 in 50th row will remaining 27 columns will be inserted?

ashimer · Posted: Wed Mar 31, 2010 7:05 pm

The multi insert can be coded as ATOMIC - if one insert fails then the entire statement fails, or it can be coded as NOT ATOMIC ON SQLERROR CONTINUE - any one failure of any of the inserts will only impact that one insert of the set.

GET DIAGNOSTICS should be avoided for performance reasons unless needed. In the case of a failed non-atomic multi-row insert you’ll get a SQLCODE of -253 - if 1 or more of the inserts failed. Only then
should you use GET DIAGNOSTICS to determine which one failed.

Multi inserts are always faster than normal inserts - this work like a charm in our applications.

So incase you need to continue with your remaining rows use NOT ATOMIC ON SQLERROR CONTINUE.

Hope this helps.

dbzTHEdinosauer · Posted: Wed Mar 31, 2010 7:18 pm

deleted by poster

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

when i used the below insert statement i got DSNH4700I

EXEC SQL
INSERT INTO tab1
VALUES ( :WS-C0
,:WS-C1
,:WS-C2
,:WS-C3
,:WS-C4
,:WS-C5
,:WS-C6
,:WS-C7)
FOR :NUM-ROWS ROWS
END-EXEC.

Num-Rows defined as S9(04) COMP.

When i hasrd coded the value '9' insted of num-rows in the above piece of code its working fine. Please let me know how to resolve it. I want to insert the rows in dynamically.

Thanks,
Murali

ashimer · Posted: Thu Apr 01, 2010 5:16 pm

Its cos of NEW FUNC mode.

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

All,

Thanks for your replies.

The Error DSNH4700I is caused due to usage of S9(10) COMP(BIG INTEGER) as Host Variable Row count. when i replaced S9(10) comp with S9(04) COMP for NUM-ROWS the error got resolved this is due to

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

In multi row insert i am getting SQLCODE=-253 due to duplicate error. I have to use get diagonistics to obtain the sqlcode=-803? whether the SQLCODE=-803 will be stored in any special register? How to obtain this?