We (our dev team) have just moved from IBM Data Studio to Toad for DB2 and we are very pleased with the Toad for DB2 (comercial licence) except one very very very annoying thing: the SQL auto formater.
How can I disable it?
I’ve tried various options but none seems to disable it. It is messing our source code for views / stored procedures. I’ve tried to build some custom configuration for the auto formater but could not reproduce 100% the format that we use (some is contextual).
After losing way to much time with it I want it disabled as we should start using it on our development efforts.
Without knowing the exact ways your source is being displayed\modified, I am not sure the formater is the culprit. Perhaps you can try this: navitate to Tools | Options | Editor | General and specify a zero for the 'Use Large Script Mode for files larger than...' value. The attached image shows how to do that and includes a snippet of the help topic for that control so you can see what editor features will be disabled.
Hope this helps,
Jeff
I’ve tested your suggestion but the behaviour of the formatting was the same (except now I was not having syntax highlighting and other goodies anymore).
I’ve made the following test (see attached screenshots, 1/step):
create a view with the format I want and run the create view ddl.
on this view, in Toad, view | Alter --> the source of the view is messed up.
view | Send to Editor as Script --> mesed up source
Viewer Views | Script --> messed up source
Recreate view in IBM Data Studio
Check the view source in Data Studio (Alter) (OK!)
Check the view in Toad | Alter (OK!)
Check view in Toad | Send to Editor (OK!)
Re-run script in Toad (no screenshot)
The script is messed up (check point 2,3,4)
So it seems that in the moment Toad send the SQL Script to the database serve is messing up the formatting. This is not a DB setting (I think) as the Data Studio is not doing it like this.
I really appreciate some suggestions,
Thanks ToD2.docx (174 KB)
When Toad reads database object definitions from the catalog it displays SQL based object definitions (i.e. views) as their definitions are stored in the catalog. When Toad executes CREATE statements, it trims out spaces (and any indentation within SQL based objects - i.e. views) to eliminate whitespace so DB2 statements do not exceed the DB2 maximum length (~2M). That trimming has been a requirement since the beginning of Toad DB2 as we have many customers with large Queries\Views\Etc that need to be able to execute SQL and create SQL objects that would have otherwise exceeded the ~2M limit without with whitespace trimming. I believe what you are seeing is that data studio does not trim out whitespace when creating SQL based objects. As you have shown, Toad will show those values formatted, (because the definitions for those objects are stored in the DB2 catalog as formatted). When a view is then altered or recreated in Toad, the whitespace is trimmed and those trimmed lines are stored in the catalog.
In Toad, you can use the Format SQL button on the SQL Editor toolbar (or in the View Object Editor) to display the SQL in a formatted manner. (In the SQL Editor in Toad you can customizer how the SQL is formatted by using the drop down button on the Format SQL button, or by changing format options at Tools | Options | Editor | Formatter.)
Hope this helps,
Jeff
Thanks for the answers. Is there a solution for this? As this can be a deal killer for us (even if we bought 5 licences already) and we must then return in using the Data Studio.
As I recommended this product (based on Toad Modeler which we use for data modelling) will be quite embarrassing for me.
I think Microsoft has also an DB2 .Net provider but iirc is delivered just with SQL EE.
I would really need a quick solution for this as we are already started the project implementation and all our views/SPs are pretty much messe up atm.
I created PMR 81641,756,000 with IBM and have sent them a simple test application how to reproduce it.
Also looking here I can say that this is well known for IBM issue http://bytes.com/topic/db2/answers/185392-bug
P.S.
Now I’m waiting an answer from IBM about it.
The APAR to address this issue is IC78613. You won’t be able to find it on ibm.com at this exact moment though as it is just going through some internal review of the text. You should be able to find it if you search on ibm.com in the near future though. If you want to know when the APAR is fixed, please search for the APAR on ibm.com and look for when it moves from OPEN to CLOSED state.
What is really very strange for me is, presuming I’m not the only customer for Toad for DB2 and this is the IBM .Net OleDB provider problem, how the other guys using Toad for DB2 can use it anyways? This problem should affect everyone.
Have nobody seen until now that all the views/stored procedures code formatting is messed up ? Until then I think my team will need to switch back to Data Studio.
I really hope you/IBM find a quick solution for this.
