DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS

Georg Peter

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 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: DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS
(in response to Georg Peter)
I'm not sure this answers all your questions, but the SQL Reference
tells you that Datatypeid is zero if the table was created before V6.
Similar info for typename and createdts.



This makes sense since Distinct Types were implemented first in V6.



Dave

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Peter, Georg
Sent: Tuesday, November 24, 2009 8:41 AM
To: [login to unmask email]
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/ >
----------------------------------------------------------------------






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.


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