Oy-Vey - Finding User Defined Data Types
A DBA found that onload was failing after a developer changed table schemas, apparently because some columns now use user-defined (extended) data types, and he needed a way to locate them across 1,000+ tables without reading each schema. Art Kagel supplied a catalog query joining systables, syscolumns and sysxtdtypes (matching extended_id, with tabid >= 100 and extended_id != 0) to list the type name, table and column. The poster confirmed it found all affected tables and columns; the rest of the thread is banter.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good morning (well, I wish is was a better morning).
Ok, well, I have a programmer that changed table schema last week and now my
ONLOAD utility is complaining about user-defined data-types that may not
allow the load to work correctly. And guess what? It doesn't load. So, for
the sysmasters out there, is there an query I can use to find all tables
with user-defined data-types? Or is there a parameter or something I can
grep for with dbschema utility? I have over 1,000+ tables, and drilling
through table schema is only a notch or two higher on my enjoyment list than
getting my hair cut with a weed-whacker.
Thanks so much for any advice. Oh, and I hope your Monday is better than
mine.
Jonathan Smaby
Pomona College
ITS Department
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
select x.name, t.tabname, c.colname
from systables t, syscolumns c, sysxtdtypes x
where t.tabid = c.tabid
and c.extended_id = x.extended_id
and t.tabid >= 100
and c.extended_id != 0;
Art
On Mon, Aug 18, 2008 at 1:02 PM, Jonathan Smaby
<Jonathan.Smaby@pomona.edu>wrote:
> Good morning (well, I wish is was a better morning).
>
> Ok, well, I have a programmer that changed table schema last week and now
> my
> ONLOAD utility is complaining about user-defined data-types that may not
> allow the load to work correctly. And guess what? It doesn't load. So, for
> the sysmasters out there, is there an query I can use to find all tables
> with user-defined data-types? Or is there a parameter or something I can
> grep for with dbschema utility? I have over 1,000+ tables, and drilling
> through table schema is only a notch or two higher on my enjoyment list
> than
> getting my hair cut with a weed-whacker.
>
> Thanks so much for any advice. Oh, and I hope your Monday is better than
> mine.
>
> Jonathan Smaby
> Pomona College
> ITS Department
>
> -------------------------------------------------------------
> This message has been scanned by Postini anti-virus software.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
Fantastic! Thank-you Art, that query found all of the tables and the
user-def data types. You made my week.
Sincerely,
Jonathan Smaby
Pomona College
ITS Department
> From: Art Kagel <art.kagel@gmail.com>
> Reply-To: <ids@iiug.org>
> Date: Mon, 18 Aug 2008 13:35:10 -0400 (EDT)
> To: <ids@iiug.org>
> Subject: Re: Oy-Vey - Finding User Defined Data Types [13148]
>
> select x.name, t.tabname, c.colname
> from systables t, syscolumns c, sysxtdtypes x
> where t.tabid = c.tabid>
> and c.extended_id = x.extended_id
>
> and t.tabid >= 100
>
> and c.extended_id != 0;
>
> Art
>
> On Mon, Aug 18, 2008 at 1:02 PM, Jonathan Smaby
> <Jonathan.Smaby@pomona.edu>wrote:
>
>> Good morning (well, I wish is was a better morning).
>>
>> Ok, well, I have a programmer that changed table schema last week and now
>> my
>> ONLOAD utility is complaining about user-defined data-types that may not
>> allow the load to work correctly. And guess what? It doesn't load. So, for
>> the sysmasters out there, is there an query I can use to find all tables
>> with user-defined data-types? Or is there a parameter or something I can
>> grep for with dbschema utility? I have over 1,000+ tables, and drilling
>> through table schema is only a notch or two higher on my enjoyment list
>> than
>> getting my hair cut with a weed-whacker.
>>
>> Thanks so much for any advice. Oh, and I hope your Monday is better than
>> mine.
>>
>> Jonathan Smaby
>> Pomona College
>> ITS Department
>>
>> -------------------------------------------------------------
>> This message has been scanned by Postini anti-virus software.
>>
>>
>>
>>
>
******************************************************************************
> *
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do those
> opinions reflect those of other individuals affiliated with any entity with
> which I am affiliated nor those of the entities themselves.
>
>
>
******************************************************************************
> *
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
Jonathan Smaby wrote: > Fantastic! Thank-you Art, that query found all of the tables and the > user-def data types. You made my week. > Just as long as he didn't make your whole week. ;o) -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com
Hey OTC, I have an article request something to the gist of: "How to abuse rogue-grammers using Informix and get away with it" That would make my (whole/hole) week too! ;o) Jonathan Smaby Pomona College ITS Department -- Laws are like sausages, it is better not to see them being made. ~Otto von Bismarck -----Original Message----- > From: Obnoxio The Clown <obnoxio@serendipita.com> > Subject: Re: Oy-Vey - Finding User Defined Data Types [13150] > > Just as long as he didn't make your whole week. ;o) > > -- > Cheers, > Obnoxio the Clown ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.
Jonathan Smaby said: > Hey OTC, I have an article request something to the gist of: "How to abuse > rogue-grammers using Informix and get away with it" > > That would make my (whole/hole) week too! ;o) Like this: http://www.gcatwork.com/phpBB2/viewtopic.php?t=6534 ? -- Bye now, Obnoxio http://obotheclown.blogspot.com/