| View previous topic :: View next topic |
| Author |
Message |
nishantrk
Beginner
Joined: 24 Mar 2006
Posts: 32
Topics: 16
|
 Posted: Thu May 13, 2010 9:06 pm Post subject: DB2 version 8 Multi row insert |
 |
|
Have read that multi row insert is around 15% more effiect than normal inserts ,am trying to implement it ,in one of our programs but am not sure how to use the get diagonostic for error handling...
Below is the code ,Can anyone suggest if there are any special SQL code that needs to be handled.Also how can i handle duplicate records here..i don't want the program to error for duplicate records...
| Code: |
EXEC SQL
INSERT INTO CUST_INCL_ON_RQST
(STMT_RQST_ID
, CUSTOMER_ID
, SMPL_CUST_YN
, RSLT_CD)
VALUES
(:ZNHDCUIR-STMT-RQST-ID
, :WS-CUSTOMER-ID
, :ZNHDCUIR-SMPL-CUST-YN
, :ZNHDCUIR-RSLT-CD)
FOR :NUM-ROWS ROWS
NOT ATOMIC CONTINUE ON SQLEXCEPTION
END-EXEC.
IF SQLCODE = +0
MOVE ROW-COUNT TO WS-CUST-INSRTD-CNT
ELSE
MOVE 1 TO CTR
PERFORM VARYING CTR FROM 1 BY 1 UNTIL CTR>NUM-ROWS
EXEC SQL
GET DIAGNOSTICS CONDITION :CTR
:ERR-SQLCODE = DB2_RETURNED_SQLCODE,
:ERR-SQLSTATE = RETURNED_SQLSTATE,
:ROW-NUM = DB2_ROW_NUMBER
END-EXEC
ADD 1 TO CTR
IF ERR-SQLCODE NOT EQUAL 0 OR -803
INITIALIZE ERROR-DTL
MOVE ERR-SQLCODE TO WS-ERR-CODE
MOVE ERR-SQLSTATE TO WS-ERR-SQLSTATE
Move WS-CUSTOMER-ID (DB2_ROW_NUMBER) TO WS-ERR-CUSTOMER
WRITE ERR-RECORD
PERFORM ABEND-PROGRAM
END-IF
END-PERFORM |
|
|
| Back to top |
|
 |
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Thu May 13, 2010 10:50 pm Post subject: |
|
|
| Quote: | | i don't want the program to error for duplicate records... |
I probably misunderstand, but yes you do want the program to error if the duplicate is detected. . .
_________________
All the best,
di |
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
|
| Back to top |
|
|
nishantrk
Beginner
Joined: 24 Mar 2006
Posts: 32
Topics: 16
|
| Posted: Fri May 14, 2010 6:49 am Post subject: |
|
|
| papadi wrote: | | Quote: | | i don't want the program to error for duplicate records... |
I probably misunderstand, but yes you do want the program to error if the duplicate is detected. . . |
yeah..I don't want the program to abend ,if duplicates are present ,the code should have been as below.
| Code: | IF SQLCODE = +0
MOVE ROW-COUNT TO WS-CUST-INSRTD-CNT
ELSE
MOVE 1 TO CTR
PERFORM VARYING CTR FROM 1 BY 1 UNTIL CTR>NUM-ROWS
EXEC SQL
GET DIAGNOSTICS CONDITION :CTR
:ERR-SQLCODE = DB2_RETURNED_SQLCODE,
:ERR-SQLSTATE = RETURNED_SQLSTATE,
:ROW-NUM = DB2_ROW_NUMBER
END-EXEC
ADD 1 TO CTR
IF ERR-SQLCODE NOT EQUAL 0 AND -803
INITIALIZE ERROR-DTL
MOVE ERR-SQLCODE TO WS-ERR-CODE
MOVE ERR-SQLSTATE TO WS-ERR-SQLSTATE
Move WS-CUSTOMER-ID (DB2_ROW_NUMBER) TO WS-ERR-CUSTOMER
WRITE ERR-RECORD
PERFORM ABEND-PROGRAM
END-IF
END-PERFORM |
|
|
| Back to top |
|
|
Dibakar
Advanced
Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
|
| Posted: Mon May 17, 2010 12:34 pm Post subject: |
|
|
| Code: | PERFORM VARYING CTR FROM 1 BY 1 UNTIL CTR>NUM-ROWS
EXEC SQL
GET DIAGNOSTICS CONDITION :CTR |
CTR is not a row number, but a condition number. See example 6 here
I get confused how the AND is handled with NOT so for non duplicate errors i would code
| Code: | IF (ERR-SQLCODE NOT EQUAL 0) AND
(ERR-SQLCODE NOT EQUAL -803) |
instead of
| Code: | | IF ERR-SQLCODE NOT EQUAL 0 AND -803 |
|
|
| Back to top |
|
|
nishantrk
Beginner
Joined: 24 Mar 2006
Posts: 32
Topics: 16
|
| Posted: Mon May 17, 2010 7:37 pm Post subject: |
|
|
Thanks Dibankar...
yeah i had changed my code ..but still its doesn't seem to work perfectly...
I changed it to..
| Code: | IF SQLCODE = +0
add to inserted-counter
else
IF SQLCODE = -253
INITIALIZE DB2RC
ROW-COUNT
ERRCNT
EXEC SQL
GET DIAGNOSTICS
:ROW-COUNT = ROW_COUNT,
:ERRCNT= NUMBER
END-EXEC
MOVE 1 TO CTR
PERFORM VARYING CTR FROM 1 BY 1
UNTIL CTR > ERRCNT
EXEC SQL
GET DIAGNOSTICS CONDITION :CTR
:ERR-SQLCODE = DB2_RETURNED_SQLCODE,
:ERR-SQLSTATE = RETURNED_SQLSTATE,
:ROW-NUM = DB2_ROW_NUMBER
IF (ERR-SQLCODE EQUAL 0 ) OR
(ERR-SQLCODE EQUAL -803)
CONTINUE
ELSE
WRITE CUSTOMER (ROW-NUM) TO ERR-
FILE
ENDIF
END-EXEC
END-PERFORM
END-IF
|
|
|
| Back to top |
|
|
nishantrk
Beginner
Joined: 24 Mar 2006
Posts: 32
Topics: 16
|
| Posted: Mon May 17, 2010 7:55 pm Post subject: |
|
|
Sorry the earlier got posted half way through...
This code is working fine for me..the only problem am getting is
...the number of conditions returned in the ERRCNT is coming as 2 ,whereas i have only one error in my insert.
I mean it is taking the SQLCODE -253 (which comes if there are more than one row in error) as the first condition and -803 as the 2nd condition.
Is this how its suppose to work or am missing something...
| Code: | IF SQLCODE = +0
add to inserted-counter
else
IF SQLCODE = -253
INITIALIZE DB2RC
ROW-COUNT
ERRCNT
EXEC SQL
GET DIAGNOSTICS
:ROW-COUNT = ROW_COUNT,
:ERRCNT= NUMBER
END-EXEC
MOVE 1 TO CTR
PERFORM VARYING CTR FROM 1 BY 1
UNTIL CTR > ERRCNT
EXEC SQL
GET DIAGNOSTICS CONDITION :CTR
:ERR-SQLCODE = DB2_RETURNED_SQLCODE,
:ERR-SQLSTATE = RETURNED_SQLSTATE,
:ROW-NUM = DB2_ROW_NUMBER
END-EXEC
IF (ERR-SQLCODE EQUAL 0 ) OR
(ERR-SQLCODE EQUAL -803)
CONTINUE
ELSE
WRITE CUSTOMER (ROW-NUM) TO ERR- FILE
ENDIF
END-PERFORM
END-IF |
|
|
| Back to top |
|
|
Dibakar
Advanced
Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
|
| Posted: Tue May 18, 2010 11:11 am Post subject: |
|
|
-253 means some rows were successful and some not. It doesn't say anything about more than one.
ERRCNT equals 2 means that there are two errors, they can be -803 but not -253.
To see all the error codes debug or change -
| Code: | IF (ERR-SQLCODE EQUAL 0 ) OR
(ERR-SQLCODE EQUAL -803)
CONTINUE
ELSE
WRITE CUSTOMER (ROW-NUM) TO ERR- FILE
ENDIF |
to
| Code: |
IF (ERR-SQLCODE NOT EQUAL 0 )
WRITE CUSTOMER (ROW-NUM) ERR-SQLCODE ROW-NUM TO ERR- FILE
ENDIF
|
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
