How to determine primary key column name?

Hi to all. I've been reading information about systables looking for a query that brings me the column(s) name of the table's primary key, but I've not succeed. How can I determine the column name of the primary key from a table? Thank you.

sysconstraints identifies the PK, actually rather than re-write it, try = reading this: = http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= 0305parker/0305parker.html j. On Apr 15, 2011, at 7:53 AM, HERNANDO DUQUE wrote: > Hi to all.=20 >=20 > I've been reading information about systables looking for a query that = brings=20 > me the column(s) name of the table's primary key, but I've not = succeed.=20 >=20 > How can I determine the column name of the primary key from a table?=20= >=20 > Thank you.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

The link to Jack's article is not quite right. It should be: http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305pa rker/0305parker.html Great article, Jack. You should submit to present at the conference next year! However, that article does not follow primary keys explicitly so here is the method: Link systables.tabid to sysconstraints.tabid where constrtype = 'P', that's the primary key constraint record. In there is the idxname column which you can link to sysindices (or the sysindexes view which is simpler to decode unless the index is a functional index). The part<N> columns in sysindexes list the colno's for the key columns in order (the indexkeys column in sysindices encodes the same information). You can use those colno's along with tabid to look the column names up in syscolumns (if the colno is negative that column was defined as DESCending in the index, just use the absolute value of the part<N>). If you search the forum history and/or the CDI history you should find a large and complex single query posted (probably by Jonathan Leffler) periodically that performs the index key translation part at least. Adding a join to sysconstraints to that shouldn't be hard if you want a single query. Personally I prefer to code the key column name lookup in code being a bit less masochistic than Jonathan. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 15, 2011 at 7:58 AM, Jack Parker <jack.parker4@verizon.net>wrote: > sysconstraints identifies the PK, actually rather than re-write it, try = > reading this: > > = > http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= > 0305parker/0305parker.html > > j. > > On Apr 15, 2011, at 7:53 AM, HERNANDO DUQUE wrote: > > > Hi to all.=20 > >=20 > > I've been reading information about systables looking for a query that = > brings=20 > > me the column(s) name of the table's primary key, but I've not = > succeed.=20 > >=20 > > How can I determine the column name of the primary key from a table?=20= > > >=20 > > Thank you.=20 > >=20 > >=20 > > = > **************************************************************************= > *****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307f3528df01c904a0f46b12

Thanks Art, This Mac mail thingie likes to break up my long lines and use "=3D" as a = line=20 continuation character=20 Aside from the article being fairly long in the tooth, the topic is = probably too=20 advanced to present in KC. While I'm here, developerworks just put up my analysis of Lester's = Fastest=20 DBA benchmark. = http://www.ibm.com/developerworks/data/library/techarticle/dm-1104tuneinfo= rmix1/index.html (Remember, look for the "=3D") Regards, Jack Parker On Apr 15, 2011, at 8:46 AM, Art Kagel wrote: > The link to Jack's article is not quite right. It should be:=20 >=20 >=20 > = http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= 0305parker/0305parker.html=20 >=20 > Great article, Jack. You should submit to present at the conference = next=20 > year!=20 >=20 > However, that article does not follow primary keys explicitly so here = is the=20 > method:=20 >=20 > Link systables.tabid to sysconstraints.tabid where constrtype =3D 'P', = that's=20 > the primary key constraint record. In there is the idxname column = which you=20 > can link to sysindices (or the sysindexes view which is simpler to = decode=20 > unless the index is a functional index). The part<N> columns in = sysindexes=20 > list the colno's for the key columns in order (the indexkeys column in=20= > sysindices encodes the same information). You can use those colno's = along=20 > with tabid to look the column names up in syscolumns (if the colno is=20= > negative that column was defined as DESCending in the index, just use = the=20 > absolute value of the part<N>).=20 >=20 > If you search the forum history and/or the CDI history you should find = a=20 > large and complex single query posted (probably by Jonathan Leffler)=20= > periodically that performs the index key translation part at least. = Adding=20 > a join to sysconstraints to that shouldn't be hard if you want a = single=20 > query. Personally I prefer to code the key column name lookup in code = being=20 > a bit less masochistic than Jonathan.=20 >=20 > Art=20 >=20 > Art S. Kagel=20 > Advanced DataTools (www.advancedatatools.com)=20 > Blog: http://informix-myview.blogspot.com/=20 >=20 > Disclaimer: Please keep in mind that my own opinions are my own = opinions and=20 > do not reflect on my employer, Advanced DataTools, the IIUG, nor any = other=20 > organization with which I am associated either explicitly, implicitly, = or by=20 > inference. Neither do those opinions reflect those of other = individuals=20 > affiliated with any entity with which I am affiliated nor those of the=20= > entities themselves.=20 >=20 > On Fri, Apr 15, 2011 at 7:58 AM, Jack Parker = <jack.parker4@verizon.net>wrote:=20 >=20 >> sysconstraints identifies the PK, actually rather than re-write it, = try =3D=20 >> reading this:=20 >>=20 >> =3D=20 >> = http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= =3D=20 >> 0305parker/0305parker.html=20 >>=20 >> j.=20 >>=20 >> On Apr 15, 2011, at 7:53 AM, HERNANDO DUQUE wrote:=20 >>=20 >>> Hi to all.=3D20=20 >>> =3D20=20 >>> I've been reading information about systables looking for a query = that =3D=20 >> brings=3D20=20 >>> me the column(s) name of the table's primary key, but I've not =3D=20= >> succeed.=3D20=20 >>> =3D20=20 >>> How can I determine the column name of the primary key from a = table?=3D20=3D=20 >>=20 >>> =3D20=20 >>> Thank you.=3D20=20 >>> =3D20=20 >>> =3D20=20 >>> =3D=20 >> = **************************************************************************= =3D=20 >> *****=3D20=20 >>> Forum Note: Use "Reply" to post a response in the discussion = forum.=3D20=3D=20 >>=20 >>> =3D20=20 >>=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >>=20 >=20 > --20cf307f3528df01c904a0f46b12=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

I don't think that it's too complicated for KC at all. I will look at the new article over the weekend. Thanks for the link. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 15, 2011 at 1:49 PM, Jack Parker <jack.parker4@verizon.net>wrote: > Thanks Art, > > This Mac mail thingie likes to break up my long lines and use "=3D" as a = > line=20 > continuation character=20 > > Aside from the article being fairly long in the tooth, the topic is = > probably too=20 > advanced to present in KC. > > While I'm here, developerworks just put up my analysis of Lester's = > Fastest=20 > DBA benchmark. > = > http://www.ibm.com/developerworks/data/library/techarticle/dm-1104tuneinfo= > rmix1/index.html > > (Remember, look for the "=3D") > > Regards, > Jack Parker > > On Apr 15, 2011, at 8:46 AM, Art Kagel wrote: > > > The link to Jack's article is not quite right. It should be:=20 > >=20 > >=20 > > = > http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= > 0305parker/0305parker.html=20 > >=20 > > Great article, Jack. You should submit to present at the conference = > next=20 > > year!=20 > >=20 > > However, that article does not follow primary keys explicitly so here = > is the=20 > > method:=20 > >=20 > > Link systables.tabid to sysconstraints.tabid where constrtype =3D 'P', = > that's=20 > > the primary key constraint record. In there is the idxname column = > which you=20 > > can link to sysindices (or the sysindexes view which is simpler to = > decode=20 > > unless the index is a functional index). The part<N> columns in = > sysindexes=20 > > list the colno's for the key columns in order (the indexkeys column > in=20= > > > sysindices encodes the same information). You can use those colno's = > along=20 > > with tabid to look the column names up in syscolumns (if the colno is=20= > > > negative that column was defined as DESCending in the index, just use = > the=20 > > absolute value of the part<N>).=20 > >=20 > > If you search the forum history and/or the CDI history you should find = > a=20 > > large and complex single query posted (probably by Jonathan Leffler)=20= > > > periodically that performs the index key translation part at least. = > Adding=20 > > a join to sysconstraints to that shouldn't be hard if you want a = > single=20 > > query. Personally I prefer to code the key column name lookup in code = > being=20 > > a bit less masochistic than Jonathan.=20 > >=20 > > Art=20 > >=20 > > Art S. Kagel=20 > > Advanced DataTools (www.advancedatatools.com)=20 > > Blog: http://informix-myview.blogspot.com/=20 > >=20 > > Disclaimer: Please keep in mind that my own opinions are my own = > opinions and=20 > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any = > other=20 > > organization with which I am associated either explicitly, implicitly, = > or by=20 > > inference. Neither do those opinions reflect those of other = > individuals=20 > > affiliated with any entity with which I am affiliated nor those of > the=20= > > > entities themselves.=20 > >=20 > > On Fri, Apr 15, 2011 at 7:58 AM, Jack Parker = > <jack.parker4@verizon.net>wrote:=20 > >=20 > >> sysconstraints identifies the PK, actually rather than re-write it, = > try =3D=20 > >> reading this:=20 > >>=20 > >> =3D=20 > >> = > http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= > =3D=20 > >> 0305parker/0305parker.html=20 > >>=20 > >> j.=20 > >>=20 > >> On Apr 15, 2011, at 7:53 AM, HERNANDO DUQUE wrote:=20 > >>=20 > >>> Hi to all.=3D20=20 > >>> =3D20=20 > >>> I've been reading information about systables looking for a query = > that =3D=20 > >> brings=3D20=20 > >>> me the column(s) name of the table's primary key, but I've not =3D=20= > > >> succeed.=3D20=20 > >>> =3D20=20 > >>> How can I determine the column name of the primary key from a = > table?=3D20=3D=20 > >>=20 > >>> =3D20=20 > >>> Thank you.=3D20=20 > >>> =3D20=20 > >>> =3D20=20 > >>> =3D=20 > >> = > **************************************************************************= > =3D=20 > >> *****=3D20=20 > >>> Forum Note: Use "Reply" to post a response in the discussion = > forum.=3D20=3D=20 > >>=20 > >>> =3D20=20 > >>=20 > >>=20 > >>=20 > >>=20 > > = > **************************************************************************= > *****=20 > >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >>=20 > >>=20 > >=20 > > --20cf307f3528df01c904a0f46b12=20 > >=20 > >=20 > > = > **************************************************************************= > *****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307f39b4e270a704a0f99886

8-{ -- Guess I'm just tired. Will we see you in KC? Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 15, 2011 at 3:03 PM, Jack Parker <jack.parker4@verizon.net>wrote: > Art, > > I was being facetious. > > j. > > On Apr 15, 2011, at 2:55 PM, Art Kagel wrote: > > > I don't think that it's too complicated for KC at all. > > > > I will look at the new article over the weekend. Thanks for the link. > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, implicitly, > or by inference. Neither do those opinions reflect those of other > individuals affiliated with any entity with which I am affiliated nor those > of the entities themselves. > > > > > > > > On Fri, Apr 15, 2011 at 1:49 PM, Jack Parker <jack.parker4@verizon.net> > wrote: > > Thanks Art, > > > > This Mac mail thingie likes to break up my long lines and use "=3D" as a > = > > line=20 > > continuation character=20 > > > > Aside from the article being fairly long in the tooth, the topic is = > > probably too=20 > > advanced to present in KC. > > > > While I'm here, developerworks just put up my analysis of Lester's = > > Fastest=20 > > DBA benchmark. > > = > > > http://www.ibm.com/developerworks/data/library/techarticle/dm-1104tuneinfo= > > rmix1/index.html > > > > (Remember, look for the "=3D") > > > > Regards, > > Jack Parker > > > > On Apr 15, 2011, at 8:46 AM, Art Kagel wrote: > > > > > The link to Jack's article is not quite right. It should be:=20 > > >=20 > > >=20 > > > = > > > http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= > > 0305parker/0305parker.html=20 > > >=20 > > > Great article, Jack. You should submit to present at the conference = > > next=20 > > > year!=20 > > >=20 > > > However, that article does not follow primary keys explicitly so here = > > is the=20 > > > method:=20 > > >=20 > > > Link systables.tabid to sysconstraints.tabid where constrtype =3D 'P', > = > > that's=20 > > > the primary key constraint record. In there is the idxname column = > > which you=20 > > > can link to sysindices (or the sysindexes view which is simpler to = > > decode=20 > > > unless the index is a functional index). The part<N> columns in = > > sysindexes=20 > > > list the colno's for the key columns in order (the indexkeys column > in=20= > > > > > sysindices encodes the same information). You can use those colno's = > > along=20 > > > with tabid to look the column names up in syscolumns (if the colno > is=20= > > > > > negative that column was defined as DESCending in the index, just use = > > the=20 > > > absolute value of the part<N>).=20 > > >=20 > > > If you search the forum history and/or the CDI history you should find > = > > a=20 > > > large and complex single query posted (probably by Jonathan > Leffler)=20= > > > > > periodically that performs the index key translation part at least. = > > Adding=20 > > > a join to sysconstraints to that shouldn't be hard if you want a = > > single=20 > > > query. Personally I prefer to code the key column name lookup in code = > > being=20 > > > a bit less masochistic than Jonathan.=20 > > >=20 > > > Art=20 > > >=20 > > > Art S. Kagel=20 > > > Advanced DataTools (www.advancedatatools.com)=20 > > > Blog: http://informix-myview.blogspot.com/=20 > > >=20 > > > Disclaimer: Please keep in mind that my own opinions are my own = > > opinions and=20 > > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any = > > other=20 > > > organization with which I am associated either explicitly, implicitly, > = > > or by=20 > > > inference. Neither do those opinions reflect those of other = > > individuals=20 > > > affiliated with any entity with which I am affiliated nor those of > the=20= > > > > > entities themselves.=20 > > >=20 > > > On Fri, Apr 15, 2011 at 7:58 AM, Jack Parker = > > <jack.parker4@verizon.net>wrote:=20 > > >=20 > > >> sysconstraints identifies the PK, actually rather than re-write it, = > > try =3D=20 > > >> reading this:=20 > > >>=20 > > >> =3D=20 > > >> = > > > http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= > > =3D=20 > > >> 0305parker/0305parker.html=20 > > >>=20 > > >> j.=20 > > >>=20 > > >> On Apr 15, 2011, at 7:53 AM, HERNANDO DUQUE wrote:=20 > > >>=20 > > >>> Hi to all.=3D20=20 > > >>> =3D20=20 > > >>> I've been reading information about systables looking for a query = > > that =3D=20 > > >> brings=3D20=20 > > >>> me the column(s) name of the table's primary key, but I've not > =3D=20= > > > > >> succeed.=3D20=20 > > >>> =3D20=20 > > >>> How can I determine the column name of the primary key from a = > > table?=3D20=3D=20 > > >>=20 > > >>> =3D20=20 > > >>> Thank you.=3D20=20 > > >>> =3D20=20 > > >>> =3D20=20 > > >>> =3D=20 > > >> = > > > **************************************************************************= > > =3D=20 > > >> *****=3D20=20 > > >>> Forum Note: Use "Reply" to post a response in the discussion = > > forum.=3D20=3D=20 > > >>=20 > > >>> =3D20=20 > > >>=20 > > >>=20 > > >>=20 > > >>=20 > > > = > > > **************************************************************************= > > *****=20 > > >> Forum Note: Use "Reply" to post a response in the discussion > forum.=20= > > > > >>=20 > > >>=20 > > >=20 > > > --20cf307f3528df01c904a0f46b12=20 > > >=20 > > >=20 > > > = > > > **************************************************************************= > > *****=20 > > > Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > > > >=20 > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --20cf3071ced8340cba04a0f9dedd

Art, I was being facetious. j. On Apr 15, 2011, at 2:55 PM, Art Kagel wrote: > I don't think that it's too complicated for KC at all.=20 >=20 > I will look at the new article over the weekend. Thanks for the link. > Art >=20 > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ >=20 > Disclaimer: Please keep in mind that my own opinions are my own = opinions and do not reflect on my employer, Advanced DataTools, the = IIUG, nor any other organization with which I am associated either = explicitly, implicitly, or by inference. Neither do those opinions = reflect those of other individuals affiliated with any entity with which = I am affiliated nor those of the entities themselves. >=20 >=20 >=20 > On Fri, Apr 15, 2011 at 1:49 PM, Jack Parker = <jack.parker4@verizon.net> wrote: > Thanks Art, >=20 > This Mac mail thingie likes to break up my long lines and use "=3D3D" = as a =3D > line=3D20 > continuation character=3D20 >=20 > Aside from the article being fairly long in the tooth, the topic is =3D > probably too=3D20 > advanced to present in KC. >=20 > While I'm here, developerworks just put up my analysis of Lester's =3D > Fastest=3D20 > DBA benchmark. > =3D > = http://www.ibm.com/developerworks/data/library/techarticle/dm-1104tuneinfo= =3D > rmix1/index.html >=20 > (Remember, look for the "=3D3D") >=20 > Regards, > Jack Parker >=20 > On Apr 15, 2011, at 8:46 AM, Art Kagel wrote: >=20 > > The link to Jack's article is not quite right. It should be:=3D20 > >=3D20 > >=3D20 > > =3D > = http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= =3D > 0305parker/0305parker.html=3D20 > >=3D20 > > Great article, Jack. You should submit to present at the conference = =3D > next=3D20 > > year!=3D20 > >=3D20 > > However, that article does not follow primary keys explicitly so = here =3D > is the=3D20 > > method:=3D20 > >=3D20 > > Link systables.tabid to sysconstraints.tabid where constrtype =3D3D = 'P', =3D > that's=3D20 > > the primary key constraint record. In there is the idxname column =3D > which you=3D20 > > can link to sysindices (or the sysindexes view which is simpler to =3D= > decode=3D20 > > unless the index is a functional index). The part<N> columns in =3D > sysindexes=3D20 > > list the colno's for the key columns in order (the indexkeys column = in=3D20=3D >=20 > > sysindices encodes the same information). You can use those colno's = =3D > along=3D20 > > with tabid to look the column names up in syscolumns (if the colno = is=3D20=3D >=20 > > negative that column was defined as DESCending in the index, just = use =3D > the=3D20 > > absolute value of the part<N>).=3D20 > >=3D20 > > If you search the forum history and/or the CDI history you should = find =3D > a=3D20 > > large and complex single query posted (probably by Jonathan = Leffler)=3D20=3D >=20 > > periodically that performs the index key translation part at least. = =3D > Adding=3D20 > > a join to sysconstraints to that shouldn't be hard if you want a =3D > single=3D20 > > query. Personally I prefer to code the key column name lookup in = code =3D > being=3D20 > > a bit less masochistic than Jonathan.=3D20 > >=3D20 > > Art=3D20 > >=3D20 > > Art S. Kagel=3D20 > > Advanced DataTools (www.advancedatatools.com)=3D20 > > Blog: http://informix-myview.blogspot.com/=3D20 > >=3D20 > > Disclaimer: Please keep in mind that my own opinions are my own =3D > opinions and=3D20 > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any = =3D > other=3D20 > > organization with which I am associated either explicitly, = implicitly, =3D > or by=3D20 > > inference. Neither do those opinions reflect those of other =3D > individuals=3D20 > > affiliated with any entity with which I am affiliated nor those of = the=3D20=3D >=20 > > entities themselves.=3D20 > >=3D20 > > On Fri, Apr 15, 2011 at 7:58 AM, Jack Parker =3D > <jack.parker4@verizon.net>wrote:=3D20 > >=3D20 > >> sysconstraints identifies the PK, actually rather than re-write it, = =3D > try =3D3D=3D20 > >> reading this:=3D20 > >>=3D20 > >> =3D3D=3D20 > >> =3D > = http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= =3D > =3D3D=3D20 > >> 0305parker/0305parker.html=3D20 > >>=3D20 > >> j.=3D20 > >>=3D20 > >> On Apr 15, 2011, at 7:53 AM, HERNANDO DUQUE wrote:=3D20 > >>=3D20 > >>> Hi to all.=3D3D20=3D20 > >>> =3D3D20=3D20 > >>> I've been reading information about systables looking for a query = =3D > that =3D3D=3D20 > >> brings=3D3D20=3D20 > >>> me the column(s) name of the table's primary key, but I've not = =3D3D=3D20=3D >=20 > >> succeed.=3D3D20=3D20 > >>> =3D3D20=3D20 > >>> How can I determine the column name of the primary key from a =3D > table?=3D3D20=3D3D=3D20 > >>=3D20 > >>> =3D3D20=3D20 > >>> Thank you.=3D3D20=3D20 > >>> =3D3D20=3D20 > >>> =3D3D20=3D20 > >>> =3D3D=3D20 > >> =3D > = **************************************************************************= =3D > =3D3D=3D20 > >> *****=3D3D20=3D20 > >>> Forum Note: Use "Reply" to post a response in the discussion =3D > forum.=3D3D20=3D3D=3D20 > >>=3D20 > >>> =3D3D20=3D20 > >>=3D20 > >>=3D20 > >>=3D20 > >>=3D20 > > =3D > = **************************************************************************= =3D > *****=3D20 > >> Forum Note: Use "Reply" to post a response in the discussion = forum.=3D20=3D >=20 > >>=3D20 > >>=3D20 > >=3D20 > > --20cf307f3528df01c904a0f46b12=3D20 > >=3D20 > >=3D20 > > =3D > = **************************************************************************= =3D > *****=3D20 > > Forum Note: Use "Reply" to post a response in the discussion = forum.=3D20=3D >=20 > >=3D20 >=20 >=20 > = **************************************************************************= ***** > Forum Note: Use "Reply" to post a response in the discussion forum. >=20 >=20

Not for all. Anyway, see you next year. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 15, 2011 at 3:20 PM, Jack Parker <jack.parker4@verizon.net>wrote: > > Not this time, I'll try to make it next year. I left it way too late in > the cycle this time to do anything. > > I meant that the folks who show up in KC have typically been using IDS for > decades, system catalogues is old hat for them. > > j. > > On Apr 15, 2011, at 3:15 PM, Art Kagel wrote: > > > 8-{ -- Guess I'm just tired. Will we see you in KC? > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, implicitly, > or by inference. Neither do those opinions reflect those of other > individuals affiliated with any entity with which I am affiliated nor those > of the entities themselves. > > > > > > > > On Fri, Apr 15, 2011 at 3:03 PM, Jack Parker <jack.parker4@verizon.net> > wrote: > > Art, > > > > I was being facetious. > > > > j. > > > > On Apr 15, 2011, at 2:55 PM, Art Kagel wrote: > > > > > I don't think that it's too complicated for KC at all. > > > > > > I will look at the new article over the weekend. Thanks for the link. > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on my employer, Advanced DataTools, the IIUG, > nor any other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > > > > > > > > > > > On Fri, Apr 15, 2011 at 1:49 PM, Jack Parker <jack.parker4@verizon.net> > wrote: > > > Thanks Art, > > > > > > This Mac mail thingie likes to break up my long lines and use "=3D" as > a = > > > line=20 > > > continuation character=20 > > > > > > Aside from the article being fairly long in the tooth, the topic is = > > > probably too=20 > > > advanced to present in KC. > > > > > > While I'm here, developerworks just put up my analysis of Lester's = > > > Fastest=20 > > > DBA benchmark. > > > = > > > > http://www.ibm.com/developerworks/data/library/techarticle/dm-1104tuneinfo= > > > rmix1/index.html > > > > > > (Remember, look for the "=3D") > > > > > > Regards, > > > Jack Parker > > > > > > On Apr 15, 2011, at 8:46 AM, Art Kagel wrote: > > > > > > > The link to Jack's article is not quite right. It should be:=20 > > > >=20 > > > >=20 > > > > = > > > > http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= > > > 0305parker/0305parker.html=20 > > > >=20 > > > > Great article, Jack. You should submit to present at the conference = > > > next=20 > > > > year!=20 > > > >=20 > > > > However, that article does not follow primary keys explicitly so here > = > > > is the=20 > > > > method:=20 > > > >=20 > > > > Link systables.tabid to sysconstraints.tabid where constrtype =3D > 'P', = > > > that's=20 > > > > the primary key constraint record. In there is the idxname column = > > > which you=20 > > > > can link to sysindices (or the sysindexes view which is simpler to = > > > decode=20 > > > > unless the index is a functional index). The part<N> columns in = > > > sysindexes=20 > > > > list the colno's for the key columns in order (the indexkeys column > in=20= > > > > > > > sysindices encodes the same information). You can use those colno's = > > > along=20 > > > > with tabid to look the column names up in syscolumns (if the colno > is=20= > > > > > > > negative that column was defined as DESCending in the index, just use > = > > > the=20 > > > > absolute value of the part<N>).=20 > > > >=20 > > > > If you search the forum history and/or the CDI history you should > find = > > > a=20 > > > > large and complex single query posted (probably by Jonathan > Leffler)=20= > > > > > > > periodically that performs the index key translation part at least. = > > > Adding=20 > > > > a join to sysconstraints to that shouldn't be hard if you want a = > > > single=20 > > > > query. Personally I prefer to code the key column name lookup in code > = > > > being=20 > > > > a bit less masochistic than Jonathan.=20 > > > >=20 > > > > Art=20 > > > >=20 > > > > Art S. Kagel=20 > > > > Advanced DataTools (www.advancedatatools.com)=20 > > > > Blog: http://informix-myview.blogspot.com/=20 > > > >=20 > > > > Disclaimer: Please keep in mind that my own opinions are my own = > > > opinions and=20 > > > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any > = > > > other=20 > > > > organization with which I am associated either explicitly, > implicitly, = > > > or by=20 > > > > inference. Neither do those opinions reflect those of other = > > > individuals=20 > > > > affiliated with any entity with which I am affiliated nor those of > the=20= > > > > > > > entities themselves.=20 > > > >=20 > > > > On Fri, Apr 15, 2011 at 7:58 AM, Jack Parker = > > > <jack.parker4@verizon.net>wrote:=20 > > > >=20 > > > >> sysconstraints identifies the PK, actually rather than re-write it, > = > > > try =3D=20 > > > >> reading this:=20 > > > >>=20 > > > >> =3D=20 > > > >> = > > > > http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= > > > =3D=20 > > > >> 0305parker/0305parker.html=20 > > > >>=20 > > > >> j.=20 > > > >>=20 > > > >> On Apr 15, 2011, at 7:53 AM, HERNANDO DUQUE wrote:=20 > > > >>=20 > > > >>> Hi to all.=3D20=20 > > > >>> =3D20=20 > > > >>> I've been reading information about systables looking for a query = > > > that =3D=20 > > > >> brings=3D20=20 > > > >>> me the column(s) name of the table's primary key, but I've not > =3D=20= > > > > > > >> succeed.=3D20=20 > > > >>> =3D20=20 > > > >>> How can I determine the column name of the primary key from a = > > > table?=3D20=3D=20 > > > >>=20 > > > >>> =3D20=20 > > > >>> Thank you.=3D20=20 > > > >>> =3D20=20 > > > >>> =3D20=20 > > > >>> =3D=20 > > > >> = > > > > **************************************************************************=@@

Not this time, I'll try to make it next year. I left it way too late in = the cycle this time to do anything. I meant that the folks who show up in KC have typically been using IDS = for decades, system catalogues is old hat for them. j. On Apr 15, 2011, at 3:15 PM, Art Kagel wrote: > 8-{ -- Guess I'm just tired. Will we see you in KC? >=20 > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ >=20 > Disclaimer: Please keep in mind that my own opinions are my own = opinions and do not reflect on my employer, Advanced DataTools, the = IIUG, nor any other organization with which I am associated either = explicitly, implicitly, or by inference. Neither do those opinions = reflect those of other individuals affiliated with any entity with which = I am affiliated nor those of the entities themselves. >=20 >=20 >=20 > On Fri, Apr 15, 2011 at 3:03 PM, Jack Parker = <jack.parker4@verizon.net> wrote: > Art, >=20 > I was being facetious. >=20 > j. >=20 > On Apr 15, 2011, at 2:55 PM, Art Kagel wrote: >=20 > > I don't think that it's too complicated for KC at all. > > > > I will look at the new article over the weekend. Thanks for the = link. > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own = opinions and do not reflect on my employer, Advanced DataTools, the = IIUG, nor any other organization with which I am associated either = explicitly, implicitly, or by inference. Neither do those opinions = reflect those of other individuals affiliated with any entity with which = I am affiliated nor those of the entities themselves. > > > > > > > > On Fri, Apr 15, 2011 at 1:49 PM, Jack Parker = <jack.parker4@verizon.net> wrote: > > Thanks Art, > > > > This Mac mail thingie likes to break up my long lines and use "=3D3D" = as a =3D > > line=3D20 > > continuation character=3D20 > > > > Aside from the article being fairly long in the tooth, the topic is = =3D > > probably too=3D20 > > advanced to present in KC. > > > > While I'm here, developerworks just put up my analysis of Lester's =3D= > > Fastest=3D20 > > DBA benchmark. > > =3D > > = http://www.ibm.com/developerworks/data/library/techarticle/dm-1104tuneinfo= =3D > > rmix1/index.html > > > > (Remember, look for the "=3D3D") > > > > Regards, > > Jack Parker > > > > On Apr 15, 2011, at 8:46 AM, Art Kagel wrote: > > > > > The link to Jack's article is not quite right. It should be:=3D20 > > >=3D20 > > >=3D20 > > > =3D > > = http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= =3D > > 0305parker/0305parker.html=3D20 > > >=3D20 > > > Great article, Jack. You should submit to present at the = conference =3D > > next=3D20 > > > year!=3D20 > > >=3D20 > > > However, that article does not follow primary keys explicitly so = here =3D > > is the=3D20 > > > method:=3D20 > > >=3D20 > > > Link systables.tabid to sysconstraints.tabid where constrtype =3D3D = 'P', =3D > > that's=3D20 > > > the primary key constraint record. In there is the idxname column = =3D > > which you=3D20 > > > can link to sysindices (or the sysindexes view which is simpler to = =3D > > decode=3D20 > > > unless the index is a functional index). The part<N> columns in =3D > > sysindexes=3D20 > > > list the colno's for the key columns in order (the indexkeys = column in=3D20=3D > > > > > sysindices encodes the same information). You can use those = colno's =3D > > along=3D20 > > > with tabid to look the column names up in syscolumns (if the colno = is=3D20=3D > > > > > negative that column was defined as DESCending in the index, just = use =3D > > the=3D20 > > > absolute value of the part<N>).=3D20 > > >=3D20 > > > If you search the forum history and/or the CDI history you should = find =3D > > a=3D20 > > > large and complex single query posted (probably by Jonathan = Leffler)=3D20=3D > > > > > periodically that performs the index key translation part at = least. =3D > > Adding=3D20 > > > a join to sysconstraints to that shouldn't be hard if you want a =3D= > > single=3D20 > > > query. Personally I prefer to code the key column name lookup in = code =3D > > being=3D20 > > > a bit less masochistic than Jonathan.=3D20 > > >=3D20 > > > Art=3D20 > > >=3D20 > > > Art S. Kagel=3D20 > > > Advanced DataTools (www.advancedatatools.com)=3D20 > > > Blog: http://informix-myview.blogspot.com/=3D20 > > >=3D20 > > > Disclaimer: Please keep in mind that my own opinions are my own =3D > > opinions and=3D20 > > > do not reflect on my employer, Advanced DataTools, the IIUG, nor = any =3D > > other=3D20 > > > organization with which I am associated either explicitly, = implicitly, =3D > > or by=3D20 > > > inference. Neither do those opinions reflect those of other =3D > > individuals=3D20 > > > affiliated with any entity with which I am affiliated nor those of = the=3D20=3D > > > > > entities themselves.=3D20 > > >=3D20 > > > On Fri, Apr 15, 2011 at 7:58 AM, Jack Parker =3D > > <jack.parker4@verizon.net>wrote:=3D20 > > >=3D20 > > >> sysconstraints identifies the PK, actually rather than re-write = it, =3D > > try =3D3D=3D20 > > >> reading this:=3D20 > > >>=3D20 > > >> =3D3D=3D20 > > >> =3D > > = http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/= =3D > > =3D3D=3D20 > > >> 0305parker/0305parker.html=3D20 > > >>=3D20 > > >> j.=3D20 > > >>=3D20 > > >> On Apr 15, 2011, at 7:53 AM, HERNANDO DUQUE wrote:=3D20 > > >>=3D20 > > >>> Hi to all.=3D3D20=3D20 > > >>> =3D3D20=3D20 > > >>> I've been reading information about systables looking for a = query =3D > > that =3D3D=3D20 > > >> brings=3D3D20=3D20 > > >>> me the column(s) name of the table's primary key, but I've not = =3D3D=3D20=3D > > > > >> succeed.=3D3D20=3D20 > > >>> =3D3D20=3D20 > > >>> How can I determine the column name of the primary key from a =3D > > table?=3D3D20=3D3D=3D20 > > >>=3D20 > > >>> =3D3D20=3D20 > > >>> Thank you.=3D3D20=3D20 > > >>> =3D3D20=3D20 > > >>> =3D3D20=3D20 > > >>> =3D3D=3D20 > > >> =3D > > = **************************************************************************= =3D > > =3D3D=3D20 > > >> *****=3D3D20=3D20 > > >>> Forum Note: Use "Reply" to post a response in the discussion =3D > > forum.=3D3D20=3D3D=3D20 > > >>=3D20 > > >>> =3D3D20=3D20 > > >>=3D20 > > >>=3D20 > > >>=3D20 > > >>=3D20 > > > =3D > > = **************************************************************************= =3D > > *****=3D20 > > >> Forum Note: Use "Reply" to post a response in the discussion = forum.=3D20=3D > > > > >>=3D20 > > >>=3D20 > > >=3D20 > > > --20cf307f3528df01c904a0f46b12=3D20 > > >=3D20 > > >=3D

Thank you very much for your help, your article is very good. I decided to write this post just for those newbies like me, that are reading this forum and wanted to find the step by step for obtaining primary key columns. 1.- Get the tabid for your table: SELECT tabid FROM systables WHERE tabname = 'my_table'; 2.- Get the idxname for the index that holds the primary key: SELECT * FROM sysconstraints WHERE tabid = my_tabid AND constrtype = 'P'; 3.- Get the colum(s) id that hold the primary key (look into fields <part1> through <part16>): SELECT * FROM sysindexes WHERE tabid = my_tabid AND idxname = 'my_idxname'; 4.- Get the colum(s) name: SELECT colname FROM syscolumns WHERE tabid = my_tabid AND (colno = 1 OR colno= 2 ... OR colno = 16); You will get up to 16 rows with the columns names, depending on how many fields your primary key is defined. Hernando.

There is actually a cleaner way to get the index info from the new = sysindexes table, remind me to figure it out/look it up again. cheers j. Glad the article was useful. On Apr 20, 2011, at 8:10 AM, HERNANDO DUQUE wrote: > Thank you very much for your help, your article is very good.=20 >=20 > I decided to write this post just for those newbies like me, that are = reading=20 > this forum and wanted to find the step by step for obtaining primary = key=20 > columns.=20 >=20 > 1.- Get the tabid for your table:=20 > SELECT tabid FROM systables WHERE tabname =3D 'my_table';=20>=20 > 2.- Get the idxname for the index that holds the primary key:=20 > SELECT * FROM sysconstraints WHERE tabid =3D my_tabid AND constrtype =3D='P';=20 >=20 > 3.- Get the colum(s) id that hold the primary key (look into fields = <part1>=20 > through <part16>):=20 > SELECT * FROM sysindexes WHERE tabid =3D my_tabid AND idxname =3D ='my_idxname';=20 >=20 > 4.- Get the colum(s) name:=20 > SELECT colname FROM syscolumns WHERE tabid =3D my_tabid AND (colno =3D =1 OR colno=20 > =3D 2 ... OR colno =3D 16);=20 >=20 > You will get up to 16 rows with the columns names, depending on how = many=20 > fields your primary key is defined.=20 >=20 > Hernando.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

On Wed, Apr 20, 2011 at 05:10, HERNANDO DUQUE <hduquec@hotmail.com> wrote: > Thank you very much for your help, your article is very good. > > I decided to write this post just for those newbies like me, that are > reading > this forum and wanted to find the step by step for obtaining primary key > columns. > > 1.- Get the tabid for your table: > SELECT tabid FROM systables WHERE tabname = 'my_table';> Determining tabid is a very important step - and this mechanism usually works, most (but not all) of the time. 2.- Get the idxname for the index that holds the primary key: > SELECT * FROM sysconstraints WHERE tabid = my_tabid AND constrtype = 'P';> > 3.- Get the colum(s) id that hold the primary key (look into fields <part1> > through <part16>): > SELECT * FROM sysindexes WHERE tabid = my_tabid AND idxname = 'my_idxname';> > 4.- Get the colum(s) name: > SELECT colname FROM syscolumns WHERE tabid = my_tabid AND (colno = 1 OR> colno > = 2 ... OR colno = 16); > > You will get up to 16 rows with the columns names, depending on how many > fields your primary key is defined. > Way back - 13 years ago - I wrote a long email about how to determine the tabid for a given table in a MODE ANSI database with DELIMIDENT set and so on. I found it in my archives, and I'll repeat it here. (Oh, and SQLCMD has had INFO operations for a long time now, too. The code to determine the 'object ID' for a named object (table, procedure, trigger) is available in the SQLCMD source in sqlownobj.ec. --------------------------------------------------------------------------- From: Leffler, Jonathan on Tue, 21 Apr 1998 05:44 Subject: RE: Silly question -- with an answer (long) To: 'informix-list@iiug.org'; 'Jonathan Leffler' I said: "I hope you can read the attachment. If not, I'll resubmit this message." I couldn't read it, so I'm resubmitting it. Sorry for the previous almost illegible posting. If MS doesn't screw this up to badly, it should at least be vaguely legible, unlike my previous attempt which relied upon MS Word to export the document in text-only-with-line-breaks format which doesn't work, and doesn't export text-only since it screws around with all the quote characters. Hate MS! --------------------------------------------------------------------------- Silly question time: How do you determine the value of TabID for a given table in an Informix database? The basic answer is as simple as the question: SELECT TabID FROM SysTables WHERE TabName = 'tablename'; So, what's Leffler thinking about when he asks such a simple question with such an obvious answer? And how on earth does he manage to write such a long message when he's already given the answer? There are several complicating factors: * Consider a MODE ANSI database. * Consider DELIMIDENT (no, on second thoughts, try not to consider DELIMIDENT). * Consider owner names with quotes * Consider owner names without quotes. * Consider remote databases. * Consider whether the components of the name were typed in all lower case, all upper case, or in some mixture of cases. I'm trying to add the INFO statement to my general purpose SQL command interpreter, SQLCMD, and that forces me into wondering exactly how to handle all the following table names, in both MODE ANSI and non-ANSI databases (when the program does not know whether it is currently connected to a MODE ANSI or a non-ANSI database): * INFO INDEXES FOR tablename * INFO INDEXES FOR TableName * INFO INDEXES FOR TABLENAME * INFO INDEXES FOR owner.tablename * INFO INDEXES FOR OWNER.TABLENAME * INFO INDEXES FOR 'owner'.tablename * INFO INDEXES FOR "owner".tablename * INFO INDEXES FOR database:tablename * INFO INDEXES FOR database@server:tablename * INFO INDEXES FOR database{x}@{y}server{z}:{p}'owner'{q}.{r}TABLENAME The context means that this is an ESQL/C program, and the table name as a whole has been read into a character string. The various components of the table name are also available in separate string variables. If there are quotes around the owner name, they have been preserved. The comments in the last example have been duly ignored, as have any spaces, tabs, and newlines anywhere in the commands. The remote database part is much the easiest to handle; you have to prefix the SysTables part of the SELECT statement with whatever database name (and optional server name) is provided in the table name that you are processing. You cannot have a server name specified without a database name too. The only place where the notation '@server' is allowed is in a CONNECT statement, and it does not specify a table name. Both MODE ANSI and non-ANSI database allow you to specify the owner of tables. MODE ANSI databases require you to do so when you don't own the table. Consequently, it is best to write either 'informix'.SysTables or "informix".SysTables in place of SysTables in the original solution. If you consider DELIMIDENT, it is best to use the double-quote notation. The user ID should be a delimited identifier rather than a string (and if you don't understand that, just remember to use double quotes around the owner name for absolute safety). Note that the capitalization of SysTables doesn't matter; it's a Lefflerian idiosyncrasy to capitalize the system catalogue names that way. Handling the various ways of capitalizing the tablename is easy enough. The name should be case-converted to all lower case, unless DELIMIDENT is in effect and the table name is enclosed in double quotes, whereupon it must be left exactly as it is. Ignoring the issues of table ownership, we're left with code that looks somewhat like: if (database and server specified) then prefix = "database@server:"; else if (database specified) then prefix = "database:"; else prefix = ""; systab = prefix || """informix"".SysTables"; tablename = LOWER(tablename); query = "SELECT TabID FROM " || systab || " WHERE TabName = '" || tablename || "'"; This will yield the correct answer in a non-ANSI database if the table exists and the owner was not specified. In a MODE ANSI database, it is only correct if the user owns the table, so for a MODE ANSI database, the query should be: query = "SELECT TabID FROM " || systab || " WHERE TabName = '" || tablename || "' AND Owner = USER; Table ownership is the big complicating factor. When the owner name is specified in (single or double) quotes, the query is simple. To avoid problems in the presence of DELIMIDENT, the code strips the quotes from the quoted owner name and embeds the result in single quotes in the query: Owner = stripquotes(owner); query = "SELECT TabID FROM " || systab || " WHERE TabName = '" || tablename || "'" || " AND Owner = '" || owner || "'"; Even that wasn't too c