One of the more frequent questions people ask me is why can’t Toad run scripts or commands against multiple databases at once? Of course as usual the answer is Toad already can – so I’m going to dedicate the next couple weeks’ blogs to this topic.    This week I’ll highlight the first method of running scripts for multiple databases using the “Toad Script Manager”. It has been in Toad for a long time now – and it’s available...

Continuing on with my discussions on autonomic computing in DB2 is the concept of self managing space. Proper space allocations are another critical factor when it comes to optimizing the performance of your database. Inadequate space allocations can result in application failures and potential downtime which will cost your organization money.

...

This article wraps up the series on the types of controls and our discussion of architectures, by taking a look at the adaptive control. We’ll explore how you can support your business deal with the impact of risks, when there is no contingency plan.

When you go through the entire exhaustive process of doing an effective (i.e. accurate) logical model so that you have properly captured all the true business rules and requirements, you sure don’t want your data modeling tool to compromise those findings as you move into the physical model – which is database platform dependent. Yet most data modeling tools do a very poor job of translating certain data modeling constructs...

I thought that’d grab your attention!  

We know there are a lot of PL/SQL developers out there who are looking to make (or have already made) a break into application development using a more popular programming language like Java as well as many Java developers who use Toad to make access to Oracle easier.  

According to a 2006 IOUG survey of Oracle sites, the 3rd most widely used language after SQL and PL/SQL...

Hey folks,   For anyone attending Oracle Open World this year, here is my schedule of presentations:

Session ID: S300184 Session Title: Weird PL/SQL Track: Oracle Develop: Database Room: Golden Gate C3 Date: 2008-09-21 Start Time: 15:45

 

Session ID: S300183 Session Title: Break Your Addiction to SQL! Track: Oracle Develop: Database Room: Salon 02 Date: 2008-09-22 Start Time: 13:00  

...

The editor window is probably where most users spend a majority of their time. In this blog, I’ll try to highlight the 10 features that will save you the most time when it comes to writing your SQL queries, SQL*Plus scripts, anonymous blocks, and PL/SQL stored procedures. Please note that all of these features are available in the standard edition of Toad for Oracle, version 9.6.

 

1.     SQL Recall

  By...

Nobody likes a firefighting effort, but unfortunately it’s a fact of life that we need to deal with as database professionals. In this article I share my 3 favorite tips for getting through without getting burned.

I have been doing Oracle work for just over two decades – and I’ve probably written thousands of SQL scripts. Many of those were DBA oriented in their task nature, but even application development presents times where SQL scripts are used. For example, the need may arise for the operating systems’ or third party schedulers to invoke a shell script to execute a SQL script to execute some PL/SQL code.

 

Historically...

This is a continuation of a skip series that I’m doing on the architecture of different control types. In this article, we discuss Corrective Controls; why we need them, and how to design for them.

Sometimes, even in today’s world of super GUI’s, a SQL script is still the best way to do something. Plus there are always those people who steadfastly prefer command line to GUI’s no matter what. Can Toad accommodate and even facilitate these needs? Of course you know that the answer is a resounding yes.

 

Let’s say that we want to turn on and off the referential integrity within an entire schema – whose list...

Is IT centralization a good thing for your company, or is decentralization the answer? In this article we look at the advantages and disadvantages from a compliance point of view, and my conclusion to this age-old debate.

I have always believed that Toad Data Modeler offers the best, easy to use data modeling tool on the market for the money. Now my faith has been confirmed – others are starting to see just what a gem Toad Data Modeler. Take a look at this review. I agree 100% of course – but everything there is true.

 

http://www.databasejournal.com/features/oracle/article.php/3754671

...

Anyone who develops, supports, or uses PL/SQL at some level also tests the PL/SQL. It may not be a formal testing environment, but there is a time where you will probably ask yourself, “Does this program actually do what it is supposed to?” I think this is something we can all relate to as computer users.  

This type of testing probably involves clicking a few buttons, then poking around the database or file system...

Last week I did an IDUG sponsored webcast on the topic of autonomic computing. I wil be excerpting these features over the next few blogs. If you wish to view a recording of the webcast here is the link:  http://www.quest.com/events/listdetails.aspx?contentid=7611&technology=&prod=&prodfamily=&loc=

 

The...

Toad for Oracle may use, but is not dependent upon, Oracle database options such as RAC, Partitioning and Spatial and Enterprise Management (OEM) Packs such as Tuning and Diagnostics depending on various factors. Such Toad features include (but not limited to) ADDM/AWR Reports and AWR Browser which are part of the optional DB Admin module add-on.

Some features in Toad which appear to do what Oracle does, DO NOT actually use any Oracle features such as Schema Compare & Sync and Quest SQL Optimizer (included with Toad Xpert, Development Suite and DBA Suite).

...

Okay, we’ve already learned the prevention is the best medicine. But what do we do when we cannot put preventive controls in place? This article answers that question and more, with more examples and data architecture considerations.

A lot of times people will ask Quest tech support or via our Yahoo discussion groups how to make the editor run SQL commands and do stuff more like SQL*Plus or how to better control the output look and feel (i.e. behavior). Along those lines and another very popular question is where one can set an option in Toad for the editor to do something specific (e.g. turn off feedback, etc) – because they have already done an online...

You learn something new every day, right? Well, I certainly do (more or less). Even about PL/SQL, about which I am sure many people think I already know everything. Far from it.  

In fact, I learned just last week from the PL/SQL Product Manager, Bryn Llewellyn, that his recommendation regarding cursor FOR loops and bulk collect is different from mine – and for a very good reason.  

Several years ago, he informed...

For those of you who may not come from a super-technical background, the term “ER Diagrammer” may sound strange or unfamiliar. Well, for the record, the term “ER Diagrammer” stands for “Entity Relationship Diagrammer” – a fancy name for a tool that displays the relationships between objects (like tables) in the database. You may ask, “Ok, but why is such a technical tool in a product like Toad for Data Analysis that’s supposed...

There are quite a few blogs out there about what happened or what’s new from this week’s ODTUG conference in New Orleans. So rather than restate what many others are reporting, I thought I’d try a slightly different approach – to instead report more on what was noticeably absent or different this year. J  

The Oracle Developers’ Tools User Conference (i.e. ODTUG) is, in my opinion, the premier annual event for Oracle...

In this article, we discuss the importance of understanding your “Really As-Is” process, and the big mistake companies make when building process documents. Then, we explore a data architecture for continuously auditing your process in an automated fashion.

Aufgrund der großen Anzahl Toad Anwender im deutschsprachigen Raum stellt sich die Frage, ob ein deutscher Toad Blog sinnvoll ist. Wir denken hier zum einen an Themen, die uns im Umfeld Internationalisierung interessieren aber auch an technische Detailfragen, die z.B. übersetzt und so einer größeren Benutzergruppe verfügbar gemacht werden könnte. Bitte schreiben Sie uns ihre Meinung.   Translation:   Due to the high number...

As the Admin for Toad World I want to welcome you and thank you for checking out the Toad World blog.  This “experts corner” is a collection of blogs by some of the most respected names in the database community.  Their blogs will cover a wide range of topics ranging from product insights to new technology developments to requests for your input on product developement.  Our intention is to provide these blogs not only as a resource to Toad users, but also for the entire worldwide database community. ...

  

A problem in the area of SQL optimization that has been around for a long time is the inaccuracy of the cost estimation of a SQL statement. A lot of people have the wrong conception that inaccurate cost estimations generated for a SQL statement by the database optimizer are caused by outdated statistics; whereas in fact, outdated or incorrect statistics is only one of many reasons that cause the database to generate...

In today’s article, we discuss the different types of controls, the best type of control, and considerations for how these controls can be worked into the data architecture of your company.

Next week the annual ODTUG conference will be held in New Orleans. I’ll be attending sessions and visiting vendor booths in order to stay current with the database DBA and development markets (more developer oriented at this particular show). I’ll report my findings and things of interest next week. I especially hope to corner some Oracle folks on the 11g R2 beta – which I’ve already heard rumblings for.

 

Of...

I’m working with our product team to develop a novel idea – and need feedback from you, our customer base. So here comes what may seem like a silly question: How would you like to win a free copy of the Toad DBA Suite for Oracle?  

Let me explain. I’m proposing that we sponsor an Oracle database tuning challenge. The rules would be rather simple: Using the Toad DBA Suite for Oracle’s  many screens, wizards, utilities...

In this article, I scratch the surface on the growing concern of data privacy. We’ll discuss how the industry has reacted to the concern, and how you might start to solution for it.

Oracle doesn't make it terribly easy to run operating system commands from within a PL/SQL block. I suppose that's understandable, given that PL/SQL is an embedded database-oriented language. Still, developers do ask me on a regular basis about how they can do this.

As I understand it, there are basically three ways to do this:

Invoke a Java method from within a PL/SQL wrapper   Call a C program...

What do you do when your company consistently has compliance weak points that don’t seem to go away? This article explores this condition, and provides the steps that your company will need to go through – and how you will support it.

Obtaining optimal performance from your DB2 LUW applications requires that you not only ensure your SQL is written efficiently and is properly tuned, but one must be sure that the DB2 instance itself is properly tuned. Too many people make the mistake of focusing all their efforts on tuning SQL and do not take a look at the big picture. Think of your DB2 installation as an ecosystem, where memory, physical design, and SQL transactions all work in conjunction with each other, and all can have a direct impact on performance. No doubt that proper SQL coding is probably the most critical factor when it comes to performance, but it is very important to understand that the best written SQL statement will not perform properly if there are inadequate memory allocations, or a poor physical database design. Monitoring and tuning a DB2 instance requires time and skill. Many LUW installations may not have the expertise to optimally tune their environments. 

...

There have been several papers and blogs from Quest on the issues of benchmarking successfully, however the condition persists where people experience trying issues with Benchmark Factory and obtaining satisfactory benchmark results. I still maintain that it’s primarily attributable to a lack of proper and complete precursory work. But people generally seem to need more than a notice to prepare more, so I’ve constructed the...

Written by Rene Woody  



After a SQL statement has been optimized in the Batch Optimizer in Quest SQL Optimizer for Oracle, you can view the text of the SQL alternatives and the execution plans in the Tuning Lab module. If the original SQL statement and alternatives have been executed, you can view the run time statistics as well.

 

 

1.      In the SQL in Selected Job window in the Batch...

The Code Insight feature rewrite for version 9.6 is perhaps the most dramatic change as it will affect every Toad user who writes SQL or PL/SQL.  

In a nutshell, for the 9.6 release, users can now start at the schema level, and use the ‘Dot Lookup’ feature to see all tables, views, synonyms, snapshots, PL/SQL objects etc. You can then drill down to the column level where possible.  

Here’s what it looks like...

For years now, agile development has been an extremely efficient way to get results while keeping the customer happy. However there are perils, especially for the database people. This article gives you a primer for what to expect, and how to succeed.

It’s that time of year once again. The Oracle Developer Tool Users Group (ODTUG) is quickly approaching (June 16th-19th in New Orleans – see below). For Quest, this is a really great show. It’s very well attended by and representative of our Toad customer base. It’s not uncommon for a vast majority of this show’s attendees to be Toad users.

 

This presents a great opportunity for both our customers and Quest....

We’ve done a lot in Toad© for Data Analysis to make it user friendly and intuitive. Naturally, what’s user friendly to one person may be a bit confusing to someone else. This post will show you how Toad for Data Analysis can be changed to something more comfortable or familiar.

If dead people are accessing your database, data breach is right around the corner. In this week’s blog, we discuss database access management concerns, and how to architect a compliance system to handle them.

Sometimes people will compare Toad against low cost competitors or freeware (remember, Toad offers a freeware version as well). The idea being that one can save money by going with a lesser product. That seems fiscally responsible – until you do the math that is. So, let’s work a simple example to demonstrate just how valuable Toad can be to...

Written by Rene Woody



This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings for determining the criteria for selecting the best alternative, for terminating longer running SQL, and eliminating the effect of caching on the comparison run times. 

 

Selecting the Best Alternative

 

The best alternative...

Here we take a look at end users sending data back to the database. Is it a good idea? What are some of the reasons why your end users would want to do this? And, four key tips to consider when designing in this functionality.

There are so many useful little make or break features inside Toad, it’s very often difficult to pick one to highlight. A few weeks ago I started to divulge some things coming in Toad 9.7 (and already in the beta). One item I mentioned was the enhancements to the Database Browser, where most DBA screens and/or utilities...

For the last couple of weeks we’ve been talking about controls. Here we round out our series by talking about Segregation of Duties ( SOD ) as a way to keep things in control. Here are some key tips for designing systems that demonstrate control with SOD.

Often when writing a query you need to get data from a range of dates; when you start thinking about automating the SQL query it’s even more important. In this post I’ll show you a quick trick to let Toad for Data Analysis figure it out for you!  

For this example I’ll be working with Oracle though it’s no different for other platforms. In my Oracle database, if I wanted to get a list of the orders that were placed...

It’s not uncommon to discover databases that have simply evolved over time rather than having been built according to some overall design or “master plan”. And there seem to be no shortage of answers as to why this occurs. Sometimes DBA’s are just far too busy with a myriad of other tasks. Sometimes developers are under the gun for deliverables so quickly that there’s just not time. Or my favorite, this project started as just a couple new tables and then ballooned into a monster. Whatever the reason, the situation exists far too often in the real world of information systems.

...

As many of you are aware Quest provides a tool known as Benchmark Factory, BMF for short. BMF is a load generation tool that also allows you to install small databases for testing. The tools out of the box can provide TPC-B, TPC-D, TPC-H, AS3AP and many other standard benchmarks as well as allowing goal and scalability tests from your own code, either manually entered or scanned from text or, in the case of Oracle, from active...

Written by Rene Woody  

This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings that determine which SQL statements will be automatically executed by the Batch Optimizer process and the method used for their execution.

 

Executing Options

 

When the optimization process is finished, the execution of the...

Despite the best intentions of the DBA or the person who designed the data model, the collection of primary keys, unique indexes, constraints, and triggers are not a foolproof method of keeping duplicate records creeping into your tables and views. Any business rule in the database can be disabled. An even more likely scenario is the business rule changing after the fact.   So what’s a person to do when they need to clean up their records?

...

As a follow on to last week’s blog about controlling with reconciliation, in this blog we look at another common control – approvals. Here are some key tips for designing systems that demonstrate control with approvals.