I use both Toad for Oracle and Toad for Data Analysts.
In Toad for Oracle I use Shift+F9 to execute the code where my cursor is located (without having to highlight the entire SQL statement). However, using Toad for Data Analysts with a DB2 connection and with multiple SQL statements in the Editor, when I place cursor inside a statement and hit Shift+F9 it tries to execute globally and gives error ([DB2]SQL0199N "The use of the reserved word “SELECT” following “” is not valid…).
It seems that the behavior of shortcuts F5, F9 and Shift+F9 (and their corresponding menu bar buttons) is identical. The statement executes fine if I highlight it. and use any one of the shortcuts or buttons.
I have tried selecting the “Toad for Oracle keyboard scheme” (Tools/Options/Environment/Keyboard) without success. I tried creating a custom keyboard scheme using the Toad for Oracle keyboard scheme as a point of departure and then creating a new shortcut using Shift+F9 to “Execute the current SQL statement” but that didn’t succeed either. I tried the same process starting with the default keyboard scheme …no luck.
How do I get the Shift+F9 to execute the snippet at my cursor in Toad for Data Analysts without having to highlight the entire statement?
When I connect to Db2 and use F9 it executes the SQL at the cursor.
What is very key here is the defined statement delimitor as we must parse the script to figure out what is the current statement. Are you using a semi-colin for this? (
Or some other symbol?
In D2 you can set the symbol used to end the statement. To do this go to Options | Database | Db2 | General. If you have set this value and your script has this symbol at the end of each statement, and you still have this issue when using F9 please generate a support bundle and post. I will have someone with Db2 knowledge look at it.
Debbie
P.S. If you are trying to get a shortcut to execute the rest of the script from current cursor, that maybe a different issue as it appearst that there is no default shortcut. Please clarify the above point and let me know.
P.S. I was able to assign Shift F9 to execute script form cursor.
Thanks very much. The keyboard shortcut is now working.
I confirmed that the DB2 delimitor was set to a semicolon; it was.
As you suspected, I was not using a delimitor to signal the end of my SQL
statements (since I don’t need a delimitor in Toad for Oracle for the
parser to execute the snippet where the cursor is resting). However, after
I added the delimitor to the relevant statement, it still did not execute
with the shortcut until I added the delimitor to every statement in the
editor (not just the one I was trying to execute using the shortcut).
Your comments were very helpful in sorting this out. Thanks again.
{TDA} on behalf of {Debbie Peabody}
05/30/2011 12:56 PM
To
Michael Brady/CA/KAIPERM@KAIPERM
cc
Subject
Execute Shortcuts F5, F9 and Shift+F9
Forum: Toad for Data Analysts
P.S. I was able to assign Shift F9 to execute script form cursor.
Hello,
Earlier I used Toad for Oracle and currently i use it for db2.
As i remember, in Oracle there was no need for sql statement delimiter. An SQL statement with cursor positioned on it can be executed without having to highlight the statement, both by F9 and Ctrl+Enter keys.
I wonder why in case of db2 a delimiter for all the sql statement on the editor is required.
Thanks
In Toad for Oracle I believe they recognize an empty line as a statement delimiter. Toad for Data Analysts did this for awhile but we removed it as there were complications.
The client application needs to be able to parse a script to send each statement individually to the server. Then the db server needs to be able to parse the SQL in order to figure out the execution plan.
Each db parser is different in its requirements. DB2 is a little difficult when it comes to executing procedures. Here semicolins are okay within the procedure but not as the ending procedure delimiter. Often people use @ as the procedure delimiter. Beacuse of this DB difference you can set you DB2 delimiter.
Debbie
Thanks a ton for quick reply.
Or some other symbol?