PowerDesigner

Events – Before Database Generate (More or less)

And we’re back.  Sorry for the delay.  Between day job, the worlds worst sinus infection, and becoming a pee-wee soccer coach (which required actually learning the rules of soccer), it’s been a busy end of summer.

Part n of our m part series on events continues.  Today we’re going to talk about the Before Database Generate event.  I could take the easy road by pointing out that I’ve already covered these in my post on adding seed data to a table but we’ll take it a little further.

First, let’s talk about before database generate events (and their cousin after database generate events) in general.  What can we use them for?  Well, here’s the list I came up with.

• To set the properties of an object that need to be modified at generation time such as parameters that are dependent on the target.  Many of these things can be done in pre-generation transforms, and this is probably the right thing to do in most cases.  Putting them in a a before database generate event has the advantage that the changes remain in the model and don’t just exist in the generated DDL.  So if it’s important to keep a model for posterity, or perhaps audit purposes, using a pre-generation event might be better.
• To integrate data or information from other sources which may also be changing (e.g. seed data)
• To automate versioning activities such as performing a model check-in when DDL is generated
• Setting object properties that are dependent on the current date

Now, just because you can do these things doesn’t necessarily mean you should.  This event is at the MODEL level.  This should give you an idea that Sybase really intended this as something you’d use for versioning, external tool interface, etc. rather than for manipulating individual objects (that’s what transforms are for) prior to generation.  You can still write code to go through your objects and take whatever action you want, but think long and hard about it.

I’ve covered external integration with my seed data demo and Sybase has some good material provided on interacting with the repository, so for our example I’m going to modify objects based on the date.

I’m going to configure my model so that for any date range partitioned table, I only have to specify the starting partition, and it will built out partitions until the current date.  I’ll be honest, this isn’t how I would choose to do this, in part because my sample is too simplistic to really be useful, in part because most databases now have interval partitions where you only have to tell it how long the date range for each partition is and it takes care of the rest, and in part because if this is something I need to automate in a warehousing environment, it’s usually something I prefer to build into the ETL or operations routine anyway.  But, it’s still a fun example, and it gave me an excuse to play with the GetPhysicalOptionValue method.

The idea here is we’ll define a single partition at the beginning of your date range.  If I wanted to make this more flexible, I’d establish what the interval was for each partition (daily, weekly, monthly, quarterly, yearly) as an extended attribute and the routine would take care of the rest using the options on the initial partition as a template.  If you take it this far, let me know.

We’ll start with a custom method on the table object.  Since partitions are fairly database specific, I should probably put this in the XDB file but I’m going to leave it in an XEM simply to make it easier to distribute.  This particular method is based on Oracle 11g.

Here’s the code for my method.  I’ll walk through it below.

sub %method%(obj)
'BuildPartitions method

dim part
 dim part_col_code
 dim part_col
 dim col
 dim part_value
 dim start_dt
 dim part_cnt

dim myregexp
 dim mymatches
 dim mymatch

if not obj.getextendedattributetext("tablepropertiestablepartitioningclausesrangeorcompositepartitioningclausepresence") then
 output "table " & obj.code & " is not range partitioned"
 exit sub
 end if

' now assuming we have range partitions, is it a date? get the name of the column
 part_col_code = obj.getphysicaloptionvalue("<table_partitioning_clauses>/partition by range/<column_list>/<column>:1")

'now find that column object

for each col in obj.columns
 if col.code = part_col_code then
 set part_col = col
 end if
 next

' and see if it is a date.
 if part_col.datatype = "DATE" then

'now we retrieve the partition data for the single partition we've defined
 part_value = obj.getphysicaloptionvalue _
 ("<table_partitioning_clauses>/partition by range/<partition_list>/partition:1/values less than/<value_list>")

'now lets get the current date and build out partitions to support it

set myregexp = new regexp
 myregexp.ignorecase = true
 myregexp.global = true
 myregexp.pattern = "\d+/\d+"

set mymatches = myregexp.execute(part_value)

for each mymatch in mymatches
 start_dt = cdate(mymatch.value)
 next

output "Building partitions from " & start_dt & " to " & date & " on " & obj.Code

else
 output "table " & obj.code & " is not range partitioned by date"
 exit sub
 end if

' now we know we have a table that is range partitioned by a date column
 ' let's add monthly partitions from start date to current date + 1 (remember
 ' the partition is defined by the upper bound

' set the partition count - we have one defined by default, so we'll start with 2
 part_cnt = 2

do while start_dt <= dateadd("m",1,date)

obj.SetPhysicalOptionValue "<table_partitioning_clauses>/partition by range/<partition_list>/partition:" & _
 part_cnt & "/<partition_name>", "part" & year(start_dt) & Right("0" & month(start_dt), 2)
 obj.SetPhysicalOptionValue "<table_partitioning_clauses>/partition by range/<partition_list>/partition:" & _
 part_cnt & "/values less than/<value_list>", "to_date('" & year(start_dt) & "/" & Right("0" & month(start_dt), 2) & "','YYYY/MM')"

start_dt = dateadd("m",1,start_dt)
 part_cnt = part_cnt + 1
 loop

output part_cnt & " partitions built"

end sub
 

Let’s break it down. Our first task is to determine if the table is range partitioned, and if it’s a date.  To do this, we’ll check and extended  attribute on the table, then we’re going to inspect the physical options on the table to get the partitioning column we’ve defined and determine if it’s a date.

Lines 15-19 check the partitioning and terminate the subroutine if it’s not range partitioned.  We follow that immediately by using the getPhysicalOptionValue method to retrieve the name of the partitioning column.  I don’t find the help file on this method terribly informative.  So I’ll give you my Cliff’s Notes version of common errors.

1. There are two similar methods getPhysicalOptionValue and getPhysicalOptionValues.  The first retrieves the value you’ve set, the second retrieves the list of possible values, which for the partitioning column would retrieve a list of all columns in the table.  A misplaced S will cause a lot of confusion.
2. When you enter the option path, enter it exactly as displayed in the tree.  If it’s surrounded by “<” and “>” include them, if it doesn’t, do not.  You won’t get an error if you enter it wrong, you just won’t get a value.

Lines 26-33 find the column object referred to in the physical options by looping through the columns collection.  If the column is not a date, the subroutine will exit.

Lines 36 and 37 retrieve the “values less than clause”, again using the getPhysicalOption method.

I’m counting on my method having the value expressing in some sort of standard date format.  VBScript is pretty good at automatic date conversions, but use your best judgement here.  Lines 41-50 use regular expressions to extract the date portion from the value.  I’m extracting all numbers and the “/” character.  If you want a good tutorial on regular expressions, you are NOT on the right blog.  I know enough to be dangerous and how to use Google.  Here are two sites I find handy:

Regular Expression Library

Regular Expression Tester

Now that we’ve completed all of that work, we’ll loop through from our starting date to the current date (plus one month since we’re specifying an upper bound) in lines 66 to 75 and add partitions using the setPhysicalOptionValue method.  The same warnings apply as for getPhysicalOptionValue(s).  Notice that we need to change our index to build new partitions and that we start with 2 so we don’t overwrite the starter partition.

So, a lot of work and still no BeforeDatabaseGenerate event (I guess maybe I should’ve titled this one “Getting and Setting Physical Options”).  Let’s add it now.

I’m going to assume you’re familiar with adding classes and events to an XEM file, we’ve covered that before. Remember it’s got to be at the MODEL level.  Here’s the code:

Function %BeforeDatabaseGenerate%(model, fct)

Dim myObj
 Dim mySel

Set mySel = model.CreateSelection
 mySel.AddObjects ActiveModel, cls_table, false, true

For each myObj in mySel.Objects
 With myObj
 Output .Name
 .ExecuteCustomMethod("Events2.BuildPartitions")
 end with
 Next

%BeforeDatabaseGenerate% = True

End Function

Nothing surprising here at this point (I hope).  As usual, we’re using a selection to find all our tables, and then executing our method for each.  You’ll notice that the input parameter for the method isn’t Obj for this event, since it only applies to models, it’s been replaced with the more specific Model on lines 1 and 6.

So there you have it.  A few words of wisdom (or maybe just words) on what you can and should do with BeforeDatabaseGenerate events, and a brief sample.  Remember, this event is probably best reserved for integration with external files or sources  and version control rather than actual model manipulation.  Use transforms for that.  However, in a pinch, here’s another tool for the box.

Best of luck.

Missing Timestamps and AltEnableTSCopy

I’m working on a deploy for an Oracle (10g) database and I’ve been asked to preserve data. Because most of my tables are compressed I’ve got “use alter when possible” disabled (that and the fact that you rarely get alters anyway but that probably deserves its own post someday). So, PowerDesigner does this:

1. Rename the original table to “tmp_Table”
2. Create the new table
3. Insert into table (col1, …, colN) select col1, …, colN from “tmp_Table”

When I test the script, I discover that my audit columns (“Created Datetime”, “Last Update Datetime”) are missing from my insert statements. No errors reported. No warnings.  No question marks for me to find and fix.  They’re just not there.  This seemed a bit unusual.

Turns out there’s an option in the XDB file that allows you to skip timestamp columns when you reload the table.  Probably handy, but not what I want.  More interesting, the default is set to omit them.  Even more interesting, the option is hidden when you look in the XDB file.  I’m assuming the thinking is that timestamp columns will be populated with defaults (or by trigger) when the data is re-inserted but, in this case, I don’t want my table alterations to change the audit trail information in the table and it seems a bold assumption that all of your timestamp columns are audit trail data anyway.

To change the value, you need to go to Edit Database\Script\Objects\Column then right click on table and select “Add Items”.

Scroll through the list of options that we apparently don’t need to know about and  find AltEnableTSCopy.

Add it to the list of options, and then change the default from 0 (skip timestamp columns) to 1 (copy timestamp columns).  You should now see your timestamp columns included in your insert statements.

I think this behavior is really poorly thought out.

1. The default should be to include all columns in the insert statement rather than omitting them
2. If you’re going to have the default be to omit columns from an insert statement, the parameter to configure this should really be visible without having to go in and manually add it.

I did take a few minutes to peak at the rest of the hidden options in the column and table objects in the XDB file and some of them appear quite useful.  I hope to complete a more thorough review in the future.  If I find anything interesting, I hope to post it here.