|
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
|
|
|
|
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 |
|
 |
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| You may need to use GET DIAGNOSTICS. |
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
| 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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania
|
|
|
|
| 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 |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Hi Robert,
The program involves lots of calculation. It's not a direct insert from the file. |
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
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 |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
| I'm using PIC S9(31)V USAGE COMP-3 with ARITH(EXTEND) option. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
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 |
|
|
|
|
