R: DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS

Mauro Moschelli

R: DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS
My guess is that the second column listed was created with an older DB2 Version where the three columns of SYSCOLUMNS didn't exist in the catalog (see default value in createdts).

HTH

Mauro Moschelli
Intesa Sanpaolo Group Services

E-mail : [login to unmask email]

IBM Certified Database Administrator - DB2 9 for z/OS
IBM Certified System Administrator - DB2 9 for z/OS



________________________________
Da: IDUG DB2-L [mailto:[login to unmask email] Per conto di Peter, Georg
Inviato: martedì 24 novembre 2009 14.41
A: [login to unmask email]
Oggetto: [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/images/M_images/idug%20na3.jpg ] < 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 >

Prima di stampare, pensa all'ambiente ** Think about the environment before printing

________________________________
Il presente messaggio, inclusi gli eventuali allegati, ha natura aziendale e potrebbe contenere informazioni confidenziali e/o riservate. Chiunque lo ricevesse per errore, è pregato di avvisare tempestivamente il mittente e di cancellarlo.
E' strettamente vietata qualsiasi forma di utilizzo, riproduzione o diffusione non autorizzata del contenuto di questo messaggio o di parte di esso.
Pur essendo state assunte le dovute precauzioni per ridurre al minimo il rischio di trasmissione di virus, si suggerisce di effettuare gli opportuni controlli sui documenti allegati al presente messaggio. Non si assume alcuna responsabilità per eventuali danni o perdite derivanti dalla presenza di virus.

***
This email (including any attachment) is a corporate message and may contain confidential and/or privileged and/or proprietary information. If you have received this email in error, please notify the sender immediately, do not use or share it and destroy this email. Any unauthorised use, copying or disclosure of the material in this email or of parts hereof (including reliance thereon) is strictly forbidden.
We have taken precautions to minimize the risk of transmitting software viruses but nevertheless advise you to carry out your own virus checks on any attachment of this message. We accept no liability for loss or damage caused by software viruses.
For the conduct of investment business in the UK, the Company is authorized by Bank of Italy and regulated by the Financial Services Authority.

_____________________________________________________________________

* 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

Walter Jani&#223;en

AW: DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS
(in response to Mauro Moschelli)
Georg

My first guess is, these columns are fairly new and contain defaults for rows, which existed at the time these columns were appended.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Peter, Georg
Gesendet: Dienstag, 24. November 2009 14:41
An: [login to unmask email]
Betreff: [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/images/M_images/idug%20na3.jpg ] < 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

Michael Turner

Re: DATATYPEID, TYPENAME and CREATEDTS in SYSIBM.SYSCOLUMNS
(in response to Walter Janißen)
NachrichtHi 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]
Tel: +44 (0)1565-873702
Web: 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
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
----------------------------------------------------------------------




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



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________

* 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