如何从db2错误日志grep以下sql部分

Question

我有一个脚本，它从db2diag.log中提取错误消息。我必须从下面的文件中提取导致死锁的SQL查询。

文件内容：log.txt

db2inst1 , WSCOMUSR , MESSAGE : ADM5501I DB2 is performing lock escalation. The affected application 
      is named "db2jcc_application", and is associated with the workload 
      name "SYSDEFAULTUSERWORKLOAD" and application ID 
      "173.10.105.33.59586.13011817552" at member "0". The total number of 
      locks currently held is "1249935", and the target number of locks to 
      hold is "624967". The current statement being executed is "delete 
      from DMEXPLOG where CREATED < ? ". Reason code "1" 


db2inst1 , WSCOMUSR , MESSAGE : ADM5501I DB2 is performing lock escalation. The affected application 
      is named "db2jcc_application", and is associated with the workload 
      name "SYSDEFAULTUSERWORKLOAD" and application ID 
      "173.10.105.33.59586.13011817552" at member "0". The total number of 
      locks currently held is "1249935", and the target number of locks to 
      hold is "624967". The current statement being executed is "select 
      * from DMEXPLOG where CREATED < ?". Reason code "1" 

需要的输出：所有的SQL查询

1. delete 
      from DMEXPLOG where CREATED < ? 
2. select 
      * from DMEXPLOG where CREATED < ? 

这样。我想从文件中获取所有的sql部分。任何grep或Awk/sed解决方案获得所需的输出？

平台：UNIX（AIX）

Answer 1

awk '{gsub(/^.*The current statement being executed is \"|\". Reason code.*$/,""); print NR". "$0}' log.txt 
1. delete from DMEXPLOG where CREATED < ? 
2. 
3. select * from DMEXPLOG where CREATED < ? 

匹配的字符串可以更短无疑和2是空的，因为你给出的数据有实际数据之间的空行。实际数据中是否有空行？

Answer 2

也许，这可以帮助您

[email protected]:/tmp$ sed -n '/select/,/^$/p;/delete/,/^$/p;/insert/,/^$/p;/update/,/^$/p' log.txt | sed -n '/^[0-9]/!H;//x;$x;s/\n\([^A]\)/ \1/gp' | awk -F'"' '{printf("%d.\t %s\n", NR, $4)}' 
1. delete    from DMEXPLOG where CREATED < ? 
2. select   * from DMEXPLOG where CREATED < ? 

Answer 3

您当前的例子可以用

sed -n '/statement being executed/ s/.*"//p; /Reason code/ s/".*//p' log