[DB2-L] DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS

Georg Peter

[DB2-L] DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS
Thanks a lot to Dave, Walter, Mauro and Mike.

Now it is clear why we see this differences.......

Sometimes it should be good to have the manuals nearby... ;-))

With kind regards - mit freundlichen Gruessen,
G e o r g H . P e t e r
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Development and Product Support (E3)
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de < http://www.dzbw.de/ >
----------------------------------------------------------------------
P Think before you print.

-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Mike Turner
Gesendet: Dienstag, 24. November 2009 15:24
An: [login to unmask email]
Betreff: Re: [DB2-L] DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS


Hi Georg

I suspect the table that contains column DEW61A_FNAME was created before Version 6. I found the following in the description of the SYSCOLUMNS column TYPENAME:

'TYPENAME is set only for columns created in Version 6 or later. The value for columns created earlier is not filled in'.

Regards
Mike Turner
Email: [login to unmask email] <mailto:[login to unmask email]>
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk < http://www.michael-turner.ltd.uk >
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.

----- Original Message -----
From: Peter, Georg <mailto:[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Tuesday, November 24, 2009 1:40 PM
Subject: [DB2-L] DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS


Environment is DB2 for z/OS Version 8 CM,

-------------------------------------------------



Collegues,



given are two tables. In both tables is one column with the format VARCHAR FOR BIT DATA.

While comparing this two columns via SYSIBM.SYSCOLUMNS we see a difference in three cases:

The SQL was

select

substr(name,1, 12) as column, datatypeid, typename, createdts

from sysibm.syscolumns

where tbname in ( 'DEW61A' , 'DEW56A')

and tbcreator = 'LEWIS'

and (name = 'DEW61A_FNAME'

or name = 'DEW56A_FNAME')

order by tbname

, name asc

with ur

;

---------+---------+---------+---------+---------+---------+---------+---

COLUMN DATATYPEID TYPENAME CREATEDTS

---------+---------+---------+---------+---------+---------+---------+---

DEW56A_FNAME 448 VARCHAR 2004-09-10-11.13.39.470102

DEW61A_FNAME 0 0001-01-01-00.00.00.000000

DSNE610I NUMBER OF ROWS DISPLAYED IS 2

Our assumption was (RUNSTATS was done) that in both cases the values in DATATYPEID, TYPENAME and CREATEDTS should be the same - but this is not true.

AND NOW THE BIG QUESTION IS WHY ?


Any info that anybody can provide on the above would be greatly appreciated.

With kind regards - mit freundlichen Gruessen,
G e o r g H . P e t e r
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Development and Product Support (E3)
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de < http://www.dzbw.de/ >
----------------------------------------------------------------------



________________________________


IDUG - The Worldwide DB2 User Community! < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Seibert

Re: AW: [DB2-L] DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS
(in response to Georg Peter)
The info center is your friend.



http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg27011656



Dave

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Peter, Georg
Sent: Tuesday, November 24, 2009 10:29 AM
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS



Thanks a lot to Dave, Walter, Mauro and Mike.

Now it is clear why we see this differences.......

Sometimes it should be good to have the manuals nearby... ;-))


With kind regards - mit freundlichen Gruessen,
G e o r g H . P e t e r
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Development and Product Support (E3)
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de < http://www.dzbw.de/ >
----------------------------------------------------------------------
P Think before you print.


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Mike Turner
Gesendet: Dienstag, 24. November 2009 15:24
An: [login to unmask email]
Betreff: Re: [DB2-L] DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS

Hi Georg



I suspect the table that contains column DEW61A_FNAME was created before Version 6. I found the following in the description of the SYSCOLUMNS column TYPENAME:



'TYPENAME is set only for columns created in Version 6 or later. The value for columns created earlier is not filled in'.



Regards
Mike Turner
Email: [login to unmask email] <mailto:[login to unmask email]>
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk < http://www.michael-turner.ltd.uk >
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.

----- Original Message -----

From: Peter, Georg <mailto:[login to unmask email]>

Newsgroups: bit.listserv.db2-l

To: [login to unmask email]

Sent: Tuesday, November 24, 2009 1:40 PM

Subject: [DB2-L] DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS



Environment is DB2 for z/OS Version 8 CM,

-------------------------------------------------



Collegues,



given are two tables. In both tables is one column with the format VARCHAR FOR BIT DATA.

While comparing this two columns via SYSIBM.SYSCOLUMNS we see a difference in three cases:

The SQL was

select

substr(name,1, 12) as column, datatypeid, typename, createdts

from sysibm.syscolumns

where tbname in ( 'DEW61A' , 'DEW56A')

and tbcreator = 'LEWIS'

and (name = 'DEW61A_FNAME'

or name = 'DEW56A_FNAME')

order by tbname

, name asc

with ur

;

---------+---------+---------+---------+---------+---------+---------+---

COLUMN DATATYPEID TYPENAME CREATEDTS

---------+---------+---------+---------+---------+---------+---------+---

DEW56A_FNAME 448 VARCHAR 2004-09-10-11.13.39.470102

DEW61A_FNAME 0 0001-01-01-00.00.00.000000

DSNE610I NUMBER OF ROWS DISPLAYED IS 2

Our assumption was (RUNSTATS was done) that in both cases the values in DATATYPEID, TYPENAME and CREATEDTS should be the same - but this is not true.

AND NOW THE BIG QUESTION IS WHY ?


Any info that anybody can provide on the above would be greatly appreciated.

With kind regards - mit freundlichen Gruessen,
G e o r g H . P e t e r
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Development and Product Support (E3)
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de < http://www.dzbw.de/ >
----------------------------------------------------------------------





________________________________

< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



________________________________

< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L