schemaeditor
Gary Sherman
July 8, 2013

 

I’ve been recently working with a couple of customers who were dropping database columns from their schema on Oracle, and the drops were taking a long time to complete.

In one instance, the customer estimated it would take 50 hours to drop a bunch of columns from their contact table.

In another, a customer observed that dropping two columns from table_site_part took over two hours. Dropping 3 columns from table_contact took over 1 hour.

These long execution times can disrupt normal operations – especially those environments with limited maintenance windows.

Dovetail’s schema editing tool (Schema Editor) and Amdocs schema editing tools (ddcomp, SchemaManager) all do the same basic operations when it comes to dropping columns. Basically they all do this: ALTER TABLE table_name DROP COLUMN column_name;

There’s a better way.

Set Unused

On large tables the process of physically


Gary Sherman
October 22, 2012

Using Dovetail SchemaEditor to add a new relation to your schema is super easy.

 

For example, here’s my schema script for adding a new one-to-one relation between the modem table and the user table:

 

What’s nice is that I only have to define the relation once in the schema file, as opposed to other tools (ddcomp, DD Editor, SchemaManager) where I would have to add the relation to the file in two places (as a relation under both the modem table and as a relation under the user table).

 

But, there is a bit of ambiguity here – which side is the primary side (OTOP) and which side is the foreign side (OTOF)?

Answer

 

The foreign side will be on the table/name, and the primary side will be inverseTable/inverseRelationName.

 

So in the example above, the modem2user is the


Kevin Miller
February 6, 2012

Gary posted recently on Automating Deployment For Clarify. A customer recently had an automation need that I answered in a similar way but rather than using NAnt I pulled from our fancy favorite new build automation tool Rake. In this post we’ll take a look at automating common database setup tasks like applying schema scripts and executing arbitrary SQL using Rake. I hope you find it useful.

Where is the Code?

I put together a Dovetail Automation gist with everything you need to solve two problems.

Applying  Dovetail Schema Editor schema scripts. Applying all SQL scripts in a directory.

Check out the readme for setup and execution instructions. Next I’ll talk a little about what each task does.

Applying Schema Scripts (apply_schemascripts)

This task looks for a child directory named schema below where the rakefile.rb


Gary Sherman
January 11, 2012

Last year I posted about automated deployments for Clarify. From that post:

 

Doing automatic deployments of Clarify changes is pretty straightforward. If I was starting off, I would use a contemporary build script tool such as nant or rake, and simply have it call out to your schema editing tool (Dovetail SchemaEditor, or ddcomp) to perform schema changes, use an import tool (such as Dovetail ArchiveManager, or dataex) for importing forms, and then compile your Clearbasic code using CBEX, or just import it as a DAT file using Dovetail ArchiveManager. File copying, registering components, configuring web servers, and setting permissions are all common tasks that are available in these build tools as well.

 

Should you also need to do additional stuff in your database, some code that uses the Dovetail SDK can be useful.

 

You could tie


Gary Sherman
October 7, 2011

Over the years of working with Clarify, I’ve seen a request come up a few times for calculating case aging, including “pauses”.

 

It’s easy to calculate a total case age. For open cases, it’s the difference between now and the time the case was opened. For closed cases, it’s the difference between case open time and case close time.

 

But what about the workflows where a case can be paused? For example, the customer may go on vacation, or any other myriad of reasons where we want to pause the clock on the case.

 

The common approach to this from an application standpoint is to have a new case Status value of Paused (or something similar), so that we simply change the status to Paused to pause the case, and then change it to something else to


Gary Sherman
August 24, 2011

 

Big Thanks! to all those that attended today’s Schema 201 webinar. We had a great turnout!

 

 

Slides

 

The slides from today’s presentation are available on Slideshare.

 

Recording

The webinar recording is available for playback.

 

play_webinar

Links

Schema 101:  http://dovetal.es/schema101webinar

 

SQL View example: http://dovetal.es/sqlView

 

SQL View that includes case data, plus a column that indicates “how many open subcases does this case have”

 

More Info on Dovetail SchemaEditor: http://dovetal.es/dtSchema

 

More Info (and free download) for Dovetail BOLT:  http://dovetal.es/dtBolt

 

Future Webinars

Have an idea for a future webinar? Let’s hear it!

 

You can leave a comment here, or drop me an email at gary@dovetailsoftware.com


Gary Sherman
August 1, 2011

In this complimentary webinar, we’ll build on the previous Schema 101 session, and dive into more advanced schema topics.

We had a great turnout in the first session, and a lot of requests for part 2.

Topicserd[1] SQL Views Many-To-Many Tables Role Objects Pseudo Relations Exclusive Relations Change Fields Closer look at the ADP tables

Those are the topics I have planned to cover. If there’s anything else you’d be interested in, please let me know, and I’ll see what I can do.

When

Wednesday, August 24, 2011 11:00 AM – 12:00 PM (Central Time)

Register

Register at: https://dovetailsoftwareevents.webex.com/dovetailsoftwareevents/onstage/g.php?t=a&d=669502491

register now


Gary Sherman
May 17, 2011

Big Thanks! to all those that attended today’s webinar on Schema 101.

Slides

The slides from today’s presentation are available on Slideshare.

 

Recording

The webinar recording is available for playback.

play_webinar

Links

More Info on Dovetail SchemaEditor: http://dovetal.es/dtSchema

 

More Info (and free download) for Dovetail BOLT:  http://dovetal.es/dtBolt

Schema 201

 

Interested in a more advanced session? I’m considering doing a Schema 201 session, including items such as SQL Views, Database Flags, Change Fields, Pseudo Relations, MTM tables, ADP in more detail, etc.

 

If this is something you’re interested in – please let me know! I’ll probably only do this if there’s enough interest up front.

 

You can leave a comment here, or drop me an email at gary@dovetailsoftware.com


Gary Sherman
May 16, 2011

Just a little reminder that I will be hosting a webinar on Tuesday of this week covering the basics of your Clarify/Dovetail schema.

This will be a good overview for those who are new to a Clarify/Dovetail system, and a good refresher for those who have been around for a while.

Topics erd Schema and database concepts Viewing your schema Editing your schema Schema tools A walkthrough of customizing your schema using SchemaScript Assorted tips and tricks for working with your schema When

Thursday, May 17, 2011 11:00 AM – 12:00 PM (Central Time)

Register

Register at:  https://dovetailsoftwareevents.webex.com/dovetailsoftwareevents/onstage/g.php?t=a&d=669385476

register now


Gary Sherman
May 2, 2011

While doing some prep for my upcoming webinar on Clarify/Dovetail Schema 101, I started doing some closer looking into what SchemaEditor and ddcomp actually do under the hood.

 

We’ve had some good reports from customers on how much faster schema changes get applied when using Dovetail SchemaEditor. I knew it as well, but I didn’t really know why. So I put on my Encyclopedia Brown hat and started my investigation.

The scenario

 

My test case was pretty simple: add a new column to the case table. A pretty common scenario.

Dovetail SchemaEditor

 

I ran SchemaEditor, adding a new x_test_integer column. I examined the logs emitted by SchemaEditor, as well as the SQL Profiler trace.

 

Here are the changes SchemaEditor made to the database:

 

1. Add the new column to the metadata (adp_sch_info table)

INSERT INTO adp_sch_info


International: +1 (512) 610-5400
Toll Free: 1 (800) 684-2055