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

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

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

假设我有一个输入文件如下:

  1. 15,28
  2. 34,90
  3. "a string that's obviously not supposed to be here"
  4. 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:

  1. 15,28
  2. 34,90
  3. "a string that's obviously not supposed to be here"
  4. 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

  1. SQL_FILE=/app/scripts/script.db2
  2. LOG=/app/logs/script.log
  3. # ...
  4. # Boilerplate to setup the connection to the database server
  5. # ...
  6. # +c: autocommit off
  7. # -v: echo commands
  8. # -s: Stop if errors occur
  9. # -p: Show prompt for interactivity (for debugging)
  10. # -td@: use '@' as the statement delimiter in the file
  11. db2 +c -s -v -td@ -p < $SQL_FILE >> $LOG
  12. if [ $? -gt 2 ];
  13. then echo "An Error occurred; rolling back the data" >> $LOG
  14. db2 "ROLLBACK" >> $LOG
  15. exit 1
  16. fi
  17. # No errors, commit the changes
  18. db2 "COMMIT" >> $LOG

script.db2

  1. ingest from file '/app/temp/values.csv'
  2. format delimited by ','
  3. (
  4. $C1 INTEGER EXTERNAL,
  5. $C2 INTEGER EXTERNAL
  6. )
  7. restart new 'SCRIPT_JOB'
  8. replace into DATA.MY_TABLE
  9. (
  10. C1,
  11. C2
  12. )
  13. values
  14. (
  15. $C1,
  16. $C2
  17. )@

答案1

得分: 1

根据 OP 的建议,将其作为答案添加:

根据 ingest 命令 的 db2 文档,似乎 +c: autocommit off 不会生效:

  1. 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:

  1. Updates from the INGEST command are committed at the end of an ingest
  2. operation. The INGEST command issues commits based on the commit_period
  3. and commit_count configuration parameters. As a result of this, the
  4. following do not affect the INGEST command: the CLP -c or +c options, which
  5. normally affect whether the CLP automatically commits the NOT LOGGED
  6. 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:

确定