How do I compare ddlgen output between db's? Order of stored proc's is different

[bslade]

I'm using ddlgen output to compare all objects in two db's. ddlgen
is listing the stored proc's in different orders in one database
versus another. This makes it impossible to use ddlgen output to
check for stored proc differences between databases.

The order doesn't seem to be based on name, id, or create date of the
stored proc. Note that one db is missing triggers (intentionally),
but I don't see how that would mess up the stored proc SQL extract
order.

I'll probably open a case with Sybase on this, but I just wanted to
check if anyone else had hit this problem and found.

Thanks
Ben Slade

[[e-mail] [Team Sybase]]

I'm using ddlgen output to compare all objects in two db's.   ddlgen
is listing the stored proc's in different orders in one database
versus another.   This makes it impossible to use ddlgen output to
check for stored proc differences between databases.
The order doesn't seem to be based on name, id, or create date of the
stored proc.  Note that one db is missing triggers (intentionally),
but I don't see how that would mess up the stored proc SQL extract
order.
I'll probably open a case with Sybase on this, but I just wanted to
check if anyone else had hit this problem and found.
Thanks
Ben Slade

Is this ASE 15.x ?
If so, my guess is that ddlgen doesn't use an order by clause, and the
order of rows in 15.x won't follow the clustered index by default (DOL
locking scheme for the system tables).

I don't know how you could fix it.

As an alternative, I know that dbschema.pl (perl script, requires
sybperl) can be asked to generate things in a specific order to
facilitate schema comparisons.

Michael

[bslade]

Post by ***@peppler.org [Team Sybase]

I'm using ddlgen output to compare all objects in two db's.   ddlgen
is listing the stored proc's in different orders in one database
versus another.   This makes it impossible to use ddlgen output to
check for stored proc differences between databases.
The order doesn't seem to be based on name, id, or create date of the
stored proc.  Note that one db is missing triggers (intentionally),
but I don't see how that would mess up the stored proc SQL extract
order.
I'll probably open a case with Sybase on this, but I just wanted to
check if anyone else had hit this problem and found.
Thanks
Ben Slade

Is this ASE 15.x ?
If so, my guess is that ddlgen doesn't use an order by clause, and the
order of rows in 15.x won't follow the clustered index by default (DOL
locking scheme for the system tables).
I don't know how you could fix it.
As an alternative, I know that dbschema.pl (perl script, requires
sybperl) can be asked to generate things in a specific order to
facilitate schema comparisons.
Michael

A correction to my first posting in this thread. The id's for the
objects do *not* match. Furthermore, it looks like the id differences
account for the ddlgen output order diff's.

Thanks for the dbschema.pl pointer, but it's behind the times in terms
of extracting source SQL for new Sybase features. Note, that I've
created a slightly updated version of dbschema.pl at http://github.com/bslade/dbschema.pl
that works a little better with ASE 15.

I'll open a case with sybase and see if I can get a change request
created to address this. I think it's an important feature for sites
that want to check for database schema diff's.

Ben Slade

[bslade]

I opened a case with Sybase requesting a new feature for sorting
ddlgen output.

I also created an enhancement request in the isug.com web pages (Type:
Enhancement - Adaptive Server Enterprise, Issue Id: 3619). Feel free
to vote for it ;)

Ben Slade

[bslade]

Post by bslade
I opened a case with Sybase requesting a new feature for sorting
ddlgen output.
Enhancement - Adaptive Server Enterprise, Issue Id: 3619).  Feel free
to vote for it ;)
Ben Slade

Woohoo, I got a feature request approved with Sybase. They'll add a
sorting option to
ddlgen, tentatively targeted for ASE 15.5 ESD#2 (release date TBD, but
at least several
months away). If anyone is interested the CR number is 627118

Ben Slade