New version of utils2_ak

I have submitted a new version of utils2_ak, which is dated November 11, 1999, which is available now on the IIUG Software Repository. The only update from the prior release is a new version of myschema.ec which includes two new features. Myschema now supports the WITH ROWIDS clause (for fragmented tables with added rowid columns) and the WITH CRCOLS feature (for tables participating in EDR conflict resolution). Hopefully now myschema does EVERYTHING dbschema does except -hd and adds many features not found in dbschema. As always let me know if anything you use is missing or broken. The second feature is yet another one to make the lives of us busy DBAs easier. The new -r flag causes the output of only two statements per table: ALTER TABLE MODIFY NEXT SIZE....; and ALTER FRAGMENT ON TABLE .... INIT IN ...; The calculated NEXT SIZE obeys the -a and -n flags. This automates database reorganization and defragmentation (extent compression). Finally a section of redundant code was rewritten and 170 lines of source dropped so it is possible that some new bug slipped past my regression testing and 10 days of personal use. BTW if anyone knows of an easier way to determine if a table has ROWIDS or CRCOLS, besides comparing reported and actual row size, let me know. The method in myschema works but it's a bit messy and I'd like a more elegant way to do this. My news server is acting up again so if anyone has comments please email me along with posting. Art S. Kagel Sent via Deja.com http://www.deja.com/ Before you buy.

kagel@bloomberg.net writes: > I have submitted a new version of utils2_ak, which is dated November 11, > 1999, which is available now on the IIUG Software Repository. The only > update from the prior release is a new version of myschema.ec which > includes two new features. [SNIP] Art, I miss just one tily little feature -dumping tables/procedures to files so they can be CVS'ed... I'm hacking/whacking myschema now, but this is better done by the original sinnner. I did start parsing the output from dbschema and/or myschema with perl, but judged it to be "the wrong way". It's not hard to hack myschema (ok, tables are a little bit hard), but I suppose the diffs will breake rsn. Thomas

Hi Art, >BTW if anyone knows of an easier way to determine if a table has ROWIDS >or CRCOLS, besides comparing reported and actual row size, let me know. >The method in myschema works but it's a bit messy and I'd like a more >elegant way to do this. What about this to find ROWIDs: Lookup the sysfragments-table for rows where indexname = "system-rowid" and tabid = systables.tabid I do this in a little 4gl-function to print a report about space usage of tables in a database. (7.24 on Solaris) Best regards, Paul

In article <81dpmk$ktb$1@news.netway.at>, "Paul Herger" <herger@netway.*please_no_spam*.at> wrote: > Hi Art, > > >BTW if anyone knows of an easier way to determine if a table has ROWIDS > >or CRCOLS, besides comparing reported and actual row size, let me know. > >The method in myschema works but it's a bit messy and I'd like a more > >elegant way to do this. > > What about this to find ROWIDs: Lookup the sysfragments-table for rows where > indexname = "system-rowid" and tabid = systables.tabid OK, that's worth investigating, thanks. I should be able to formulate a subquery that will produce a flag for me. > I do this in a little 4gl-function to print a report about space usage of > tables in a database. (7.24 on Solaris) Art S. Kagel Sent via Deja.com http://www.deja.com/ Before you buy.

In article <uogcl1t4o.fsf@ose.no>, Thomas Parsli <thomas.parsli@ose.no> wrote: > kagel@bloomberg.net writes: > > > I have submitted a new version of utils2_ak, which is dated November 11, > > 1999, which is available now on the IIUG Software Repository. The only > > update from the prior release is a new version of myschema.ec which > > includes two new features. > [SNIP] > > Art, > > I miss just one tily little feature -dumping tables/procedures to files > so they can be CVS'ed... You mean each table/procedure in it's own file? Might be easier to write a driver application to run myschema once for each object separately, though slower I guess. Give me more detail and I may put it on my list. Your code will help, also, if you carry the mods far. > I'm hacking/whacking myschema now, but this is better done by the > original sinnner. > > I did start parsing the output from dbschema and/or myschema with > perl, but judged it to be "the wrong way". > It's not hard to hack myschema (ok, tables are a little bit hard), > but I suppose the diffs will breake rsn. In hacking the table code just keep in mind whether you are adding pre-table code (like "CREATE TABLE", column code, or post-table code (like "IN <dbspace>") to keep straight where the code goes and whether to use systable.* or prev.* to get fields for the 'current' table. If you are hacking pre-table code in an older version of myschema note that this code was where I reduced 170 lines of redundant code in the latest release so you may want to start over with the latest codebase to save having to insert the code in multiple places. You are of course correct in that this is the most messy part of the code and is the last legacy of the original logic from the Sybase schema.ec code. Art S. Kagel PS - Anyone who has tried to use listdb.ec on Intel or Alpha platforms should contact me for an update to the code which will be included in a future utils2_ak release. This code depended on bit fields which are not portable and failed to determine the dbspace correctly on little-endian machines like Intel and Alpha. The new code uses the same division coding as myschema and works on all platforms. Sent via Deja.com http://www.deja.com/ Before you buy.

kagel@bloomberg.net writes: > In article <uogcl1t4o.fsf@ose.no>, > Thomas Parsli <thomas.parsli@ose.no> wrote: [SNIP] > > I miss just one tily little feature -dumping tables/procedures to > files > > so they can be CVS'ed... > > You mean each table/procedure in it's own file? Might be easier to > write a driver application to run myschema once for each object > separately, though slower I guess. Give me more detail and I may put > it on my list. Your code will help, also, if you carry the mods far. The idea is to put the usefull parts of the databasedefinition in CVS. One for each table, with indexes, triggers and permissions. One for each procedure and probably permissions in the same files. I don't have useful (for anyone else that is) code yet, but I'm basicly just writing to files at the right (?) places... Speed is not a factor here, so maybe I'll wrap up some code to call dbschme/myschema umphteen times... And speaking of that, shouldn't ALTER TABLE be just after CREATE TABLE when I use 'myschema -d database -t table'? Thomas

In article <uk8n814vd.fsf@ose.no>, Thomas Parsli <thomas.parsli@ose.no> wrote: > kagel@bloomberg.net writes: > > > In article <uogcl1t4o.fsf@ose.no>, > > Thomas Parsli <thomas.parsli@ose.no> wrote: > [SNIP] > > > I miss just one tily little feature -dumping tables/procedures to > > files > > > so they can be CVS'ed... > > > > You mean each table/procedure in it's own file? Might be easier to > > write a driver application to run myschema once for each object > > separately, though slower I guess. Give me more detail and I may put > > it on my list. Your code will help, also, if you carry the mods far. > > The idea is to put the usefull parts of the databasedefinition in CVS. > One for each table, with indexes, triggers and permissions. One for > each procedure and probably permissions in the same files. > > I don't have useful (for anyone else that is) code yet, but I'm basicly > just writing to files at the right (?) places... OK, that what I was reading but I was not sure. > Speed is not a factor here, so maybe I'll wrap up some code to call > dbschme/myschema umphteen times... > And speaking of that, shouldn't ALTER TABLE be just after CREATE TABLE > when I use 'myschema -d database -t table'? You mean the alters to add primary and foreign key constraints printing after the table/column level permissions? Yeah I debated that one. I could either move the GRANTS up to print after each the table, which I dearly wanted, or gang them at the end like dbschema does which make cut and paste more difficult. The reason the ALTERs come at the end is that one cannot add any FOREIGN KEYs until all of the tables have been created since there is no guarantee that the dependent table's definition comes after the independent table's definition. Since I had to delay FOREIGN key constraints to the end and the index that the PRIMARY key constraint needs was already generated with the table it just made sense to delay the PRIMARY key constraint until the same point, output all the PRIMARY and UNIQUE constraints then FOREIGN KEY constraints. The code for a database level schema moved the GRANTs up to the table level section from the end so that is how even the table level schema are output. Do you see any problem from doing it this way? I could break the print_constraints() function up into one that prints PRIMARY and UNIQUE constraints immediately after the indexes and then another to write the FOREIGN KEY constraints only at the end. Anyone have an opinion on which way is better or even if it matters? BTW Erik Van Veen has sent me the correct method for detecting ROWIDS and CRCOLS and I have an updated version of myschema using that code. The change is minor so I will not post it immediately. Maybe the changes Thomas is looking for could be included then. Art S. Kagel Sent via Deja.com http://www.deja.com/ Before you buy.

Art S. Kagel <kagel@bloomberg.net> writes: > In article <uk8n814vd.fsf@ose.no>, > Thomas Parsli <thomas.parsli@ose.no> wrote: [SNIPPING] > > And speaking of that, shouldn't ALTER TABLE be just after CREATE TABLE > > when I use 'myschema -d database -t table'? > > You mean the alters to add primary and foreign key constraints printing > after the table/column level permissions? > > Yeah I debated that one. I could either move the GRANTS up to print > after each the table, which I dearly wanted, or gang them at the end > like dbschema does which make cut and paste more difficult. The reason > the ALTERs come at the end is that one cannot add any FOREIGN KEYs > until all of the tables have been created since there is no guarantee > that the dependent table's definition comes after the independent > table's definition. But when you specify _one_ table, you really don't care about this do you? > Since I had to delay FOREIGN key constraints to > the end and the index that the PRIMARY key constraint needs was already > generated with the table it just made sense to delay the PRIMARY key > constraint until the same point, output all the PRIMARY and UNIQUE > constraints then FOREIGN KEY constraints. When I think of it I'm actually arguing for two diffrent behaviours: a) myschema shows one table, and I want all TABLE-statements grouped b) myschema shows several tables, constraints should be last You'd have to count tables matched (wildcards) with the '-t' option... Shall we forget my first mail? Thomas