Administration, Database Design, Tips & Tricks

Clearing Database Designer Workspace

0

I recently had an error running Database Designer which left some residual tables and schemas associated with the design. I posted my question on the (unofficial) Vertica forums as to how to remove the design workspace, and was fortunate to have my question answered. However, I was surprised there were few resources around the function suggested in the answer.

The error that I received from Database Designer was:

Unknown error while running Database Designer.
Design process may still be executing in the background.

After attempting to retry the design, the error I received was:

The design cannot be created because another design ‘design’ is in progress. Try again when that session is complete.

After my question was answered, I was expecting to be able to research the answer for my own reference. In this case, there was little documentation around resolving Database Designer errors using the two functions described below. At the time of this post, a Google search for “dbd_drop_all_workspaces” returns 7 results. The only official resource being a Vertica 5.0 Troubleshooting Guide on an IBM FTP. It almost seems as though Vertica expects there to be no need to use these cleanup functions in Vertica versions newer than 5.0. I strongly feel these should be part of the core documentation.

Onto the valuable information from the 5.0 troubleshooting guide:

In rare circumstances, a user design space (workspace) might not be dropped, such as if the terminal window closes mid design. The following issues describe the different error messages you might see and how to resolve them.

Issue 1

A workspace is left behind after the design process stopped running, such as if the terminal window closes. When you next start the Administration Tools and try to recreate the design, the system returns a message that multiple workspaces are not allowed.

Error

Resolution

  1. Drop the Database Designer workspace tables manually using the following statement:
  2. Open the Administration Tools and select Configuration Menu > Run Database Designer again.

Issue 2

The design process is running and a second user tries to create a design on a database where a workspace already exists.

Error 1

Error 2

Resolution

Check the progress of the design by querying the internal table vs_designs using the following command:

Cancel the design using the following command:

Hint: The output from the vs_designs table returns the workspace and design names

Documentation

About the author / 

Norbert Krupa

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: