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
 

 

Multi Row Insert & Get Diagnostics

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Fri Sep 05, 2014 6:47 pm    Post subject: Multi Row Insert & Get Diagnostics
Reply with quote

Hi,

My requirement is to inert the rows into a DB2 table from a sequential file which has records in millions.

For that I'm using Multi Row Insert feature with NOT ATOMIC CONTINUE ON SQLEXCEPTION option. Now I want to write all the records that failed to insert in an Error file.

For ex: If I'm doing a multi row insert for 10 rows and SQLERRD(3) of SQLCA returns 7, this means 3 records failed to insert.
How can I identify which 3 records are failed to insert and the error(SQLCODE) associated with them?

Thanks.
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 Sep 05, 2014 6:54 pm    Post subject: Reply to: Multi Row Insert & Get Diagnostics
Reply with quote

You may need to use GET DIAGNOSTICS.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Fri Sep 05, 2014 7:11 pm    Post subject:
Reply with quote

Thanks. Could you please provide any link or document where I can find more about GET DIAGNOSTICS feature. What are the variables and the syntax which can help me to identify the information that I need. I tried the manual, but
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 Sep 05, 2014 7:18 pm    Post subject: Reply to: Multi Row Insert & Get Diagnostics
Reply with quote

http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_getdiagnostics.dita
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Fri Sep 05, 2014 8:33 pm    Post subject:
Reply with quote

http://ibmmainframes.com/about47690.html
Should help.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Fri Sep 05, 2014 9:13 pm    Post subject:
Reply with quote

Thanks Gnanas. The link you provided looks useful. I will work on it monday now.

Quote:
If you have a array of 10 and then you are doing this in a sequential mode so certainly the 8th occurance of the same array has a probably duplicate data or a bad data.

@Rohit: I'm doing multi insert as below (WS-NUM-ROWS=10).
First I'm reading the first 10 records from file and populating the WS-CUSTOMER table and then performing an insert. If successful, read the next 10 record else find out which one failed and write them into an error file.

How would I know which one failed and for what reason(SQLCODE).

Code:
01 WS-CUSTOMER.
   05 WS-CUSTOMER-ID   PIC S9(9) COMP        OCCURS 10 TIMES.
   05 WS-CUSTOMER-NAME PIC X(50)             OCCURS 10 TIMES.

... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

INSERT INTO CUSTOMER               
    (CUSTOMER_ID                           
   , CUSTOMER_NAME )                           
VALUES                                     
    (:WS-CUSTOMER-ID                 
   , :WS-CUSTOMER-NAME)                           
     FOR :WS-NUM-ROWS ROWS                     
     NOT ATOMIC CONTINUE ON SQLEXCEPTION


Quote:
So in your process you can check the SQLCODE <> 0 after MASS/MULTI INSERT then write that 8th one to the error file and then perform another para in a loop to simply try inserting rest of the 2 records( 9 and 10) and once it does again loan next 10 records to the array and perform your original MASS/MULTI INSERT.

I didn't understand this part at all. Could you please elaborate further.

Thanks.
Back to top
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 982
Location: Oostende, Belgium

PostPosted: Sat Sep 06, 2014 1:48 pm    Post subject:
Reply with quote

You don't insert millions of rows using a program, with all the overhead of logging and commit logic. There is a perfect alternative, the LOAD utility!
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Sep 06, 2014 6:50 pm    Post subject:
Reply with quote

Hi Robert,

The program involves lots of calculation. It's not a direct insert from the file.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Sep 06, 2014 6:58 pm    Post subject:
Reply with quote

What will be the COBOL host variable for the following two columns:
Code:
DB2_ROW_NUMBER  DECIMAL(31,0)
ROW_COUNT       DECIMAL(31,0)
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Sep 06, 2014 7:28 pm    Post subject:
Reply with quote

I'm using PIC S9(31)V USAGE COMP-3 with ARITH(EXTEND) option.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Sun Sep 07, 2014 4:19 am    Post subject: Reply to: Multi Row Insert & Get Diagnostics
Reply with quote

Well, you consult some DB2 doc. which tells you whether that definition is signed or not.

Yes, if you want 31 digits you'll need ARITH(EXTEND). The trailing V is unnecessary (the compiler effectively ignores it).

Why, though, would a row-id need to be able to go to 9,999,999,999,999,999,999,999,999,999,999?

That's ten (US) billion-billion-biliion-billion-billion-billion (I think, but it hardly matters), give or take one. Is that really reasonable? ARITH(EXTEND) has performance penalties if using numeric FUNCTIONs, as does the use of any packed-decimal with more than (15 I think it is) digits. 15 digits can give you a billion-billion-billion, which is often enough.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sun Sep 07, 2014 4:54 pm    Post subject:
Reply with quote

Thanks Bill. The maximum value that this field can have is 1000. So I have removed the ARITH(EXTEND) option. Now everything is working fine including GET DIAGNOSTICS part.

Thank you all for you help.
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 Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Reading selected volumes of a multi-v... RickBig JCL & VSAM 6 Wed Jul 13, 2016 7:26 pm
No new posts How do I right justify a string in an... rakesh17684 DB2 1 Wed Jun 08, 2016 8:01 pm


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