Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Mass Insert in Db2?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Fri Mar 26, 2010 10:37 am    Post subject: Mass Insert in Db2?
Reply with quote

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.
Back to top
View user's profile Send private message

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Fri Mar 26, 2010 11:33 am    Post subject:
Reply with quote

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.
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.relg/fmlt.htm
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Mon Mar 29, 2010 1:34 pm    Post subject:
Reply with quote

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 ,


Quote:

The NUM-ROWS host variable specifies the number of rows that are to be inserted, which must be less than or equal to the dimension of each host variable array.


Please let me know what is the dimension to be defined for num-rows? how to resolve this issue.

Thanks,
Murali.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Mar 29, 2010 2:47 pm    Post subject:
Reply with quote

Version of DB2 in your shop ?
Seems it is in New Function Mode - in middle of a migration.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Mon Mar 29, 2010 2:53 pm    Post subject:
Reply with quote

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)
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Tue Mar 30, 2010 5:43 pm    Post subject:
Reply with quote

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

Thanks,
Murali.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Mar 30, 2010 9:13 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Wed Mar 31, 2010 11:52 am    Post subject:
Reply with quote

Great Dick,

Thanks for recollecting about GET DIAGNOSTICS .

Murali.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Wed Mar 31, 2010 5:55 pm    Post subject:
Reply with quote

All,

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

Quote:
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?


Instead of doing 78 individual inserts we can one mass insert but will it affects the performance of the database?

Please clarify.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Wed Mar 31, 2010 6:29 pm    Post subject:
Reply with quote

i have one more doubt, while inserting 78 rows if i get -803 in 50th row will remaining 27 columns will be inserted?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Mar 31, 2010 7:05 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Mar 31, 2010 7:18 pm    Post subject:
Reply with quote

deleted by poster
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Thu Apr 01, 2010 12:11 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Apr 01, 2010 5:16 pm    Post subject:
Reply with quote

Its cos of NEW FUNC mode.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Fri Apr 16, 2010 12:51 pm    Post subject:
Reply with quote

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

Quote:

Multi-Row INSERT: a single INSERT statement
can add multiple rows of data from an array.
 For static and dynamic SQL (examples upcoming)
 FOR n ROWS
 Maximum n is 32,767


the maximum number of count value is 32767.

Regards,
Murali.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Thu May 20, 2010 7:04 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm
No new posts How to insert a lengthy string havin... vidyaa DB2 7 Thu Aug 25, 2016 5:20 pm
No new posts How do I right justify a string in an... rakesh17684 DB2 1 Wed Jun 08, 2016 8:01 pm
No new posts insert the same records but chancing ... danylele74 DB2 6 Mon May 02, 2016 7:22 pm
No new posts Can a DB2 insert trigger execution of... TS70363 DB2 6 Tue Nov 24, 2015 10:44 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us