0

With Oracle, the syntax is:

explain plan for
select * from users WHERE user_name = :user_name AND user_dob = :user_dob

Is it possible to do the same in DB2? The statement below does not seem to work.

explain plan with snapshot for
select * from users WHERE user_name = :user_name AND user_dob = :user_dob

Thank you.

Andy
  • 2,770
  • 9
  • 35
  • 42

2 Answers2

1

The answer may depend on your DB2 version and platform, which you chose not to share with us for some reason. This works fine on DB2 for LUW (v10.1, but I'm sure it would work with v9.7 and up):

$ db2 "explain plan with snapshot for select * from syscat.schemata where schemaname = :blah"
DB20000I  The SQL command completed successfully.

You may want to try replacing named parameter markers with questions marks.

AngocA
  • 7,655
  • 6
  • 39
  • 55
mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • Changing the statement to "select * from users WHERE user_name = ? AND user_dob = ?" worked. Thanks. – Andy Oct 22 '13 at 09:01
0

Apparently, the answer is in the IBM website, but it is not easy to make sense of.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0000952.htm

FOR explainable-sql-statement

Specifies the SQL statement to be explained. This statement can be any valid CALL, Compound SQL (Dynamic), DELETE, INSERT, MERGE, SELECT, SELECT INTO, UPDATE, VALUES, or VALUES INTO SQL statement. If the EXPLAIN statement is embedded in a program, the explainable-sql-statement can contain references to host variables (these variables must be defined in the program). Similarly, if EXPLAIN is being dynamically prepared, the explainable-sql-statement can contain parameter markers.

But it does not tell you what "parameter markers" are, so you have to go and search for it.

Andy
  • 2,770
  • 9
  • 35
  • 42