英文:
How to Rollback DB2 Ingest statement for malformed data
问题
我有一个Bash Shell脚本,运行一个DB2 SQL文件。这个SQL文件的任务是完全替换数据库表的内容为该SQL文件的内容。
然而,如果在摄入的文件中发现错误,我还需要保留数据库表的内容。例如,假设我的表当前如下:
MY_TABLE | C1 | C2 |
---|---|---|
row0 | 15 | 27 |
row1 | 19 | 20 |
假设我有一个输入文件如下:
15,28
34,90
"a string that's obviously not supposed to be here"
54,23
如果我用这个输入文件运行脚本,表应该保持与之前完全相同,不使用文件的内容。
然而,当我运行我的脚本时,我观察到的行为并非如此:相反,MY_TABLE的内容确实被输入文件的所有有效行替换,因此表的新内容变为:
MY_TABLE | C1 | C2 |
---|---|---|
row0 | 15 | 28 |
row1 | 34 | 90 |
row2 | 54 | 23 |
在我的脚本逻辑中,我明确禁用了在脚本的文件摄取部分的自动提交,并且仅在检查SQL执行未引发错误后调用提交;如果引发了错误,我调用回滚。尽管如此,当发生错误时表的内容仍然被替换,就好像根本没有调用回滚命令,而是调用了提交。
我的脚本中有什么问题?
英文:
I have a Bash Shell Script that runs a DB2 sql file. The job of this sql file is to completely replace the contents of a database table with whatever are the contents of this sql file.
However, I also need that database table to have its contents preserved if errors are discovered in the ingested file. For example, supposing my table currently looks like this:
MY_TABLE | C1 | C2 |
---|---|---|
row0 | 15 | 27 |
row1 | 19 | 20 |
And supposing I have an input file that looks like this:
15,28
34,90
"a string that's obviously not supposed to be here"
54,23
If I run the script with this input file, the table should stay exactly the same as it was before, not using the contents of the file at all.
However, when I run my script, this isn't the behavior I observe: instead, the contents of MY_TABLE do get replaced with all of the valid rows of the input file so the new contents of the table become:
MY_TABLE | C1 | C2 |
---|---|---|
row0 | 15 | 28 |
row1 | 34 | 90 |
row2 | 54 | 23 |
In my script logic, I explicitly disable autocommit for the part of the script that ingests the file, and I only call commit after I've checked that the sql execution returned no errors; if it did cause errors, I call rollback instead. Nonetheless, the contents of the table get replaced when errors occur, as though the rollback command wasn't called at all, and a commit was called instead.
Where is the problem in my script?
script.ksh
SQL_FILE=/app/scripts/script.db2
LOG=/app/logs/script.log
# ...
# Boilerplate to setup the connection to the database server
# ...
# +c: autocommit off
# -v: echo commands
# -s: Stop if errors occur
# -p: Show prompt for interactivity (for debugging)
# -td@: use '@' as the statement delimiter in the file
db2 +c -s -v -td@ -p < $SQL_FILE >> $LOG
if [ $? -gt 2 ];
then echo "An Error occurred; rolling back the data" >> $LOG
db2 "ROLLBACK" >> $LOG
exit 1
fi
# No errors, commit the changes
db2 "COMMIT" >> $LOG
script.db2
ingest from file '/app/temp/values.csv'
format delimited by ','
(
$C1 INTEGER EXTERNAL,
$C2 INTEGER EXTERNAL
)
restart new 'SCRIPT_JOB'
replace into DATA.MY_TABLE
(
C1,
C2
)
values
(
$C1,
$C2
)@
答案1
得分: 1
根据 OP 的建议,将其作为答案添加:
根据 ingest 命令 的 db2 文档,似乎 +c: autocommit off
不会生效:
从 INGEST 命令的更新在进行摄入操作结束时提交。INGEST 命令基于 commit_period 和 commit_count 配置参数发出提交。因此,以下内容不会影响 INGEST 命令:通常会影响 CLP 是否自动提交 CREATE TABLE 语句上的 NOT LOGGED INITIALLY 选项的 CLP -c 或 +c 选项。
英文:
Adding as answer per OP's suggestion:
Per the db2 documentation for the ingest command It appears that the +c: autocommit off
will not function:
Updates from the INGEST command are committed at the end of an ingest
operation. The INGEST command issues commits based on the commit_period
and commit_count configuration parameters. As a result of this, the
following do not affect the INGEST command: the CLP -c or +c options, which
normally affect whether the CLP automatically commits the NOT LOGGED
INITIALLY option on the CREATE TABLE statement
答案2
得分: 0
你可能想设置warningcount 1
选项,这将导致命令在第一个错误或警告后终止。默认行为是继续处理并忽略所有错误(warningcount 0
)。
英文:
You probably want to set the warningcount 1
option, which will cause the command to terminate after the first error or warning. The default behaviour is to continue processing while ignoring all errors (warningcount 0
).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论