database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
ㆍDB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
DB2 Q&A 1492 게시물 읽기
No. 1492
db2에서는 setAutocommit가 안먹나요??
작성자
괴롭다
작성일
2008-01-13 23:20
조회수
20,555

두개의 테이블에 각각 대량의 데이터를 인서트 하던중이었습니다.


두개다 데이터가 잘들어가다가 한군데에서는


SQLState:-803

SQLState:23505

SQLState:Error for batch element #2350: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=4;OPPRA.ID_MATCH_REAL, DRIVER=3.50.152


이런 에러메세지가 막 나기 시작하였고






SQLState:[jcc][t4][102][10040][3.50.152] Non-atomic batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.

Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4228, SQLSTATE=null

SQLState:-4228

SQLState:40001

SQLState:Error for batch element #1: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=2, DRIVER=3.50.152

SQLState:-911

SQLState:null

SQLState:[jcc][103][10843][3.50.152] Non-recoverable chain-breaking exception occurred during batch processing.  The batch is terminated non-atomically. ERRORCODE=-4225, SQLSTATE=null

SQLState:-4225


한테이블에서는이런메시지가 나왔습니다.



텍스트에서 읽어와서 데이터베이스로 넣고 있었고 커넥션을 맺을때 setAutoCommit(true)로 해주었고 그 후 5000건 만다  pStmtBatch.executeBatch() 해주었습니다.  몇만건 이상 들어간 후에 저런 에러 메시지가 나오더군요.


그후에 들어간 데이터들을 지우려고하면


DB21034E  The command was processed as an SQL statement because it was not a

valid Command Line Processor command.  During SQL processing it returned:

SQL0964C  The transaction log for the database is full.  SQLSTATE=57011


로그 풀이라는 메세지가 뜨네요. 

흠... setAutoCommit만으로 commit가 제대로 이루어지지 않는건가요?? 


급한데.. 이것참 큰일이네요.. ㅜㅜ

이 글에 대한 댓글이 총 1건 있습니다.

setautocommit은 먹지만 환경에 맞도록 범위를 조정해주셔야 합니다. 

좀더 작은 단위로 나누어 해보세요.


1. SQLCODE=-803

- pk나 unique index에 중복외는 값이 있는 경우

- 메시지 확인방법

db2 "? sql0803"

SQL0803N One or more values in the INSERT statement, UPDATE

          statement, or foreign key update caused by a DELETE

          statement are not valid because the primary key, unique

          constraint or unique index identified by

          "" constrains table "

" from

          having duplicate rows for those columns. 

Explanation:

The INSERT or UPDATE object table "

" is constrained

by one or more UNIQUE indexes to have unique values in certain

columns or groups of columns.  Alternatively, a DELETE statement

on a parent table caused the update of a foreign key in a

dependent table "

" that is constrained by one or more

UNIQUE indexes.  Unique indexes might support primary keys or

unique constraints defined on a table. The statement cannot be

processed because completing the requested INSERT, UPDATE or

DELETE statement would result in duplicate column values. 




2. SQLCODE=-911


SQL0911N The current transaction has been rolled back because of

          a deadlock or timeout.  Reason code "". 

Explanation:

The current unit of work was involved in an unresolved contention

for use of an object and had to be rolled back. 

 The reason codes are as follows:

 

 2 transaction rolled back due to deadlock. 

 68 transaction rolled back due to lock timeout. 

 72 transaction rolled back due to an error concerning a DB2 Data

Links Manager involved in the transaction. 

Note: The changes associated with the unit of work must be  

       entered again. 

 The application is rolled back to the previous COMMIT. 

User Response:

To help avoid deadlock or lock timeout, issue frequent COMMIT

operations, if possible, for a long-running application, or for

an application likely to encounter a deadlock. 

 Federated system users: the deadlock can occur at the federated

server or at the data source.  There is no mechanism to detect

deadlocks that span data sources and potentially the federated

system.  It is possible to identify the data source failing the

request (refer to the problem determination guide to determine

which data source is failing to process the SQL statement). 

 Deadlocks are often normal or expected while processing certain

combinations of SQL statements.  It is recommended that you

design applications to avoid deadlocks to the extent possible. 

 sqlcode :  -911

 sqlstate :  40001 




3. SQL0964


SQL0964C The transaction log for the database is full. 

Explanation:

All space in the transaction log is being used. 

 If a circular log with secondary log files is being used, an

attempt has been made to allocate and use them.  When the file

system has no more space, secondary logs cannot be used. 

 If an archive log is used, then the file system has not provided

space to contain a new log file. 

 The statement cannot be processed. 

User Response:

Execute a COMMIT or ROLLBACK on receipt of this message (SQLCODE)

or retry the operation. 

 If the database is being updated by concurrent applications,

retry the operation.  Log space may be freed up when another

application finishes a transaction. 

 Issue more frequent commit operations.  If your transactions are

not committed, log space may be freed up when the transactions

are committed.  When designing an application, consider when to

commit the update transactions to prevent a log full condition. 

 If deadlocks are occurring, check for them more frequently. 

This can be done by decreasing the database configuration

parameter DLCHKTIME.  This will cause deadlocks to be detected

and resolved sooner (by ROLLBACK) which will then free log

space. 

 If the condition occurs often, increase the database

configuration parameter to allow a larger log file.  A larger log

file requires more space but reduces the need for applications to

retry the operation. 

 If installing the sample database, drop it and install the

sample database again. 

 sqlcode :  -964

 sqlstate :  57011

--님이 2008-01-14 09:21에 작성한 댓글입니다.
이 댓글은 2008-01-14 09:25에 마지막으로 수정되었습니다. Edit
[Top]
No.
제목
작성자
작성일
조회
1496맨 끝 레코드 가져오려면?? [2]
db초보
2008-01-18
9081
1495안녕하세요 날짜 기간 검색에 대해서 질문입니다. [3]
김연태
2008-01-18
9637
1493안녕하세요~ 에러코드인대 알려주십시요 [1]
이장훈
2008-01-14
9315
1492db2에서는 setAutocommit가 안먹나요?? [1]
괴롭다
2008-01-13
20555
1491with문을 이용한 임시테이블 생성시 [2]
지니
2008-01-11
9645
1490한글깨짐현상.. [1]
db초보
2008-01-11
13390
1489데이터 중복 처리 관련... [1]
DB2사랑
2008-01-10
10111
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2021 DSN, All rights reserved.
작업시간: 0.012초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다