如何回滚DB2 Ingest语句以处理格式错误的数据

huangapple go评论69阅读模式
英文:

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).

huangapple
  • 本文由 发表于 2023年2月18日 01:30:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/75487477.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定