IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Multi Row Insert & Get Diagnostics


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
mistah kurtz

Active User


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

PostPosted: Fri Sep 05, 2014 6:47 pm
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: 792
Location: Chennai, India

PostPosted: Fri Sep 05, 2014 6:54 pm
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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Fri Sep 05, 2014 7:11 pm
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: 792
Location: Chennai, India

PostPosted: Fri Sep 05, 2014 7:18 pm
Reply with quote

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

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

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

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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Fri Sep 05, 2014 9:13 pm
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

Senior Member


Joined: 07 Feb 2009
Posts: 1315
Location: Vilnius, Lithuania

PostPosted: Sat Sep 06, 2014 1:48 pm
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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Sep 06, 2014 6:50 pm
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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Sep 06, 2014 6:58 pm
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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Sep 06, 2014 7:28 pm
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sun Sep 07, 2014 4:19 am
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: 316
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sun Sep 07, 2014 4:54 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Insert header record with record coun... DFSORT/ICETOOL 14
No new posts Insert system time/date (timestamp) u... DFSORT/ICETOOL 5
No new posts Identify Program Insert DB2 7
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
No new posts Insert trailer for non empty file only DFSORT/ICETOOL 6
Search our Forums:

Back to Top