Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| Dev |
|
| QA |
|
| Priority | Minor |
| Complexity | Unknown |
| Component | Q Analyzer - Visual Explain Plan |
| Version | Future |
Add support for visual explain plan in Informix.
see discussion on stack overflow about informix explain
http://stackoverflow.com/questions/1156710/informix-query-plan
http://www.dbforums.com/informix/1670698-informix-explain-plan.html
"This is possible, but you have to be on IDS 11.70: see the EXPLAIN_SQL function, which IBM Data Studio and AGS Server Studio use to return an XML query plan into a BLOB before displaying it visually."
We can add simple Explain text support with the following SQL. We still need to find a way to get the correct server path instead of just putting it in the root drive because it might be on Unix, or we might not have permission. We also need to come up with a unique file name so multiple users executing don't overwrite each other. Then we need to delete the file after execution.
set explain on AVOID_EXECUTE
go
SET EXPLAIN FILE TO 'c:\explain.txt'
go
select * from table_test
go
select FILETOCLOB('c:\explain.txt','server') from sysmaster:informix.sysdual
go
set explain off
We can add simple Explain text support with the following SQL. We still need to find a way to get the correct server path instead of just putting it in the root drive because it might be on Unix, or we might not have permission. We also need to come up with a unique file name so multiple users executing don't overwrite each other. Then we need to delete the file after execution.
set explain on AVOID_EXECUTE
go
SET EXPLAIN FILE TO 'c:\explain.txt'
go
select * from table_test
go
select FILETOCLOB('c:\explain.txt','server') from sysmaster:informix.sysdual
go
set explain off
We could use a stored procedure to do all the work and return the record set. Stored procedures in Informix allow for execution of shell level commands, giving us a way to delete the explain plan file after we are done with it. The user account would require create/drop procedure and read/write to the specific file path. This would solve the issues of files constantly been created and over loading disk space on the server.
We could use a stored procedure to do all the work and return the record set. Stored procedures in Informix allow for execution of shell level commands, giving us a way to delete the explain plan file after we are done with it. The user account would require create/drop procedure and read/write to the specific file path. This would solve the issues of files constantly been created and over loading disk space on the server.
=================
GO
SELECT FILETOCLOB('<FILENAME>','server') FROM sysmaster:informix.sysdual
=================
GO
SELECT FILETOCLOB('<FILENAME>','server') FROM sysmaster:informix.sysdual
Issue #9700 |
| Closed |
Completion |
| No due date |
| No fixed build |
| No time estimate |
http://www.dbforums.com/informix/1670698-informix-explain-plan.html
"This is possible, but you have to be on IDS 11.70: see the EXPLAIN_SQL function, which IBM Data Studio and AGS Server Studio use to return an XML query plan into a BLOB before displaying it visually."