关于DB2EXPLN和DB2ADVIS的文件格式
当使用这两个命令分析一个包含SQL的文件时，是不是里面的SQL不能有换行符？
一个可以运行的SQL放进去报语法错误，去掉SQL中的所有换行符就没错了，但是如果这样大段的SQL分析起来比较麻烦，不知道大家有什么建议么
一个可以运行的SQL放进去报语法错误，去掉SQL中的所有换行符就没错了，但是如果这样大段的SQL分析起来比较麻烦，不知道大家有什么建议么
8同行回答
[b2cdbins@b2cdw ~]$ cat b.sql select * FROM dw_sor.TD_GDS_INF_TD c;[b2cdbins@b2cdw ~]$ db2expln -d ubtdb -g -stmtfile "/home/b2cdbins/b.sql" -t DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool ******************** DYNAMIC *************************************** ==================== STATEMENT ========================================== ERROR -- From db2exdyn: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "select *". Expected tokens may include: "". SQLSTATE=42601 [b2cdbins@b2cdw ~]$
[b2cdbins@b2cdw ~]$ cat b.sql select * FROM dw_sor.TD_GDS_INF_TD c[b2cdbins@b2cdw ~]$ db2expln -d ubtdb -g -stmtfile "/home/b2cdbins/b.sql" -t DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool ******************** DYNAMIC *************************************** ==================== STATEMENT ========================================== Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = No SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "B2CDBINS" Statement: select * FROM dw_sor.TD_GDS_INF_TD c Section Code Page = 1208 Estimated Cost = 59865.792969 Estimated Cardinality = 1035070.000000 Access Table Name = DW_SOR.TD_GDS_INF_TD ID = 2,11 | #Columns = 18 | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability | May participate in Scan Sharing structures | Scan may start anywhere and wrap, for completion | Fast scan, for purposes of scan sharing management | Scan can be throttled in scan sharing management | Relation Scan | | Prefetch: Eligible | Lock Intents | | Table: Intent Share | | Row : Next Key Share | Sargable Predicate(s) | | Return Data to Application | | | #Columns = 18 Return Data Completion End of section Optimizer Plan: Rows Operator (ID) Cost 1.03507e+06 n/a RETURN ( 1) 59865.8 | 1.03507e+06 n/a TBSCAN ( 2) 59865.8 | 1.03507e+06 n/a Table: DW_SOR TD_GDS_INF_TD收起
浏览2383
你最后少个分号收起
浏览2262
浏览2282
no problem running here:
(taoewang@db2debug) /home/taoewang/temp
$ db2level
DB21085I Instance "taoewang" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23027", and Fix Pack
"1".
Product is installed at "/home/taoewang/sqllib".
(taoewang@db2debug) /home/taoewang/temp
$ db2 connect to sample
Database Connection Information
Database server = DB2/AIX64 9.7.1
SQL authorization ID = TAOEWANG
Local database alias = SAMPLE
(taoewang@db2debug) /home/taoewang/temp
$ cat test.sql
select
*
from employee
;
(taoewang@db2debug) /home/taoewang/temp
$ db2 set current explain mode explain
DB20000I The SQL command completed successfully.
(taoewang@db2debug) /home/taoewang/temp
$ db2 -tvf test.sql
select * from employee
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604收起
(taoewang@db2debug) /home/taoewang/temp
$ db2level
DB21085I Instance "taoewang" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23027", and Fix Pack
"1".
Product is installed at "/home/taoewang/sqllib".
(taoewang@db2debug) /home/taoewang/temp
$ db2 connect to sample
Database Connection Information
Database server = DB2/AIX64 9.7.1
SQL authorization ID = TAOEWANG
Local database alias = SAMPLE
(taoewang@db2debug) /home/taoewang/temp
$ cat test.sql
select
*
from employee
;
(taoewang@db2debug) /home/taoewang/temp
$ db2 set current explain mode explain
DB20000I The SQL command completed successfully.
(taoewang@db2debug) /home/taoewang/temp
$ db2 -tvf test.sql
select * from employee
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604收起
浏览2264
浏览2234
(taoewang@db2debug) /home/taoewang/temp
$ db2expln -d sample -g -f "test.sql" -terminal -z ";"
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"TAOEWANG"
Statement:
select *
from employee
Section Code Page = 1208
Estimated Cost = 11.374599
Estimated Cardinality = 25.000000
Access Table Name = TAOEWANG.EMPLOYEE ID = 2,6
| #Columns = 14
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Return Data to Application
| | | #Columns = 14
Return Data Completion
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
25
n/a
RETURN
( 1)
11.3746
|
25
n/a
TBSCAN
( 2)
11.3746
|
25
n/a
Table:
TAOEWANG
EMPLOYEE
(taoewang@db2debug) /home/taoewang/temp
$ db2level
DB21085I Instance "taoewang" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23027", and Fix Pack
"1".
Product is installed at "/home/taoewang/sqllib".
(taoewang@db2debug) /home/taoewang/temp
$ cat test.sql
select
*
from
employee
;
(taoewang@db2debug) /home/taoewang/temp
$
next time please make sure to paste all information and screenshot for such problem, so that people can try your steps for repro.
In infocenter note the following statement:
-stmtfile query-statement-file
A file that contains one or more query statements to be dynamically prepared and explained. By default, each line of the file is assumed to be a distinct query statement. If statements must span lines, use the -terminator option to specify the character that marks the end of an query statement. 收起
$ db2expln -d sample -g -f "test.sql" -terminal -z ";"
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"TAOEWANG"
Statement:
select *
from employee
Section Code Page = 1208
Estimated Cost = 11.374599
Estimated Cardinality = 25.000000
Access Table Name = TAOEWANG.EMPLOYEE ID = 2,6
| #Columns = 14
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Return Data to Application
| | | #Columns = 14
Return Data Completion
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
25
n/a
RETURN
( 1)
11.3746
|
25
n/a
TBSCAN
( 2)
11.3746
|
25
n/a
Table:
TAOEWANG
EMPLOYEE
(taoewang@db2debug) /home/taoewang/temp
$ db2level
DB21085I Instance "taoewang" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23027", and Fix Pack
"1".
Product is installed at "/home/taoewang/sqllib".
(taoewang@db2debug) /home/taoewang/temp
$ cat test.sql
select
*
from
employee
;
(taoewang@db2debug) /home/taoewang/temp
$
next time please make sure to paste all information and screenshot for such problem, so that people can try your steps for repro.
In infocenter note the following statement:
-stmtfile query-statement-file
A file that contains one or more query statements to be dynamically prepared and explained. By default, each line of the file is assumed to be a distinct query statement. If statements must span lines, use the -terminator option to specify the character that marks the end of an query statement. 收起
浏览2279