[DB2-L] Question on Column Definition

Georg Peter

[DB2-L] Question on Column Definition
>>>> The problem is that (I didn't mention) is that this site I is at V7. <<<<<

And I thought my company was the last one that migrated to DB2 z/OS Version 8 - CM and yesterday to NFM ;-)

Sorry, could not resist ;-)

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
Ground Floor Room 018
70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-27271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de < http://www.dzbw.de/ >
----------------------------------------------------------------------



Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Robert Knight
Gesendet: Donnerstag, 10. Februar 2011 17:56
An: [login to unmask email]
Betreff: Re: [DB2-L] Question on Column Definition



The problem is that (I didn't mention) is that this site I is at V7.



Bob Knight



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Roy Boxwell
Sent: Thursday, February 10, 2011 11:57 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Question on Column Definition



What about BIGINT field ?

Roy Boxwell

SOFTWARE ENGINEERING GmbH

-Product Development-

Robert-Stolz-Straße 5

40470 Düsseldorf/Germany

Tel. +49 (0)211 96149-675

Fax +49 (0)211 96149-32

Email: [login to unmask email]

http://www.seg.de



Software Engineering GmbH

Amtsgericht Düsseldorf, HRB 37894

Geschäftsführung: Gerhard Schubert


On 10 Feb 2011, at 17:21, Robert Knight <[login to unmask email]> wrote:



I have data coming in from MY SQL and going to be loaded into DB2 tables.



The question I have is that the column on my sql is defined as big integer which is 19 characters from -9,223,372,036,854,775,808 to -9,223,372,036,854,775,807.



This column is to be used as the primary key on the DB2 tables.



The DB2 definition of large integer has a precision of 31 bits -2147483648 to +2147483647.



Would it be better to define the DB2 table with char(20) to handle this?



Anybody run into this before and what is the best method of handling it?



Thanks



Bob Knight

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 >



________________________________

Introducing IBM® DB2® 10 for z/OS < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

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 >



________________________________

Introducing IBM® DB2® 10 for z/OS < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

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 >



Abonnieren Sie unseren Infobrief und erfahren Sie regelmäßig die neuesten Nachrichten über unsere Lösungen, aktuellen Projekte und Entwicklungen. Melden sie sich an mit diesem Link http://www.datenzentrale.de/Info-Brief
_______________________________________________________________________________

Datenzentrale Baden-Württemberg, Anstalt des öffentlichen Rechts
Krailenshaldenstr. 44, 70469 Stuttgart
Telefon (0711) 8108-20, Telefax (0711) 8108-21350
E-Mail [login to unmask email], Internet www.datenzentrale.de
Vorstand: Karl Tramer (Vors.) und Harald Schätzle, Vorsitzender des Verwaltungsrats: Gunter Czisch
USt-Id-Nr. DE147794223
_______________________________________________________________________________




_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Mike Bell

Re: Question on Column Definition
(in response to Georg Peter)
Ok, now you are between a rock and a hard place.

Yes, you could load it as decimal however the calls from the clients will be
passing big int fields which would make the SQL on v7 is not indexable.

Worse yet, bigint isn't even a supported datatype in DB2 z/os until v9.

Some more bad answers -

1. you could break the field into 2 integer values. Downside is that DB2
does some magic to make negative numbers sort correctly and anything
expecting order by a bigint field isn't going to get it.
2. you could define the field as double precision float - for most
applications the number range difference isn't a problem. Yes, it is smaller
but still bigger than integer.

You could also check whether any of the tables actually have values greater
than the magic 2G -1 value and force everything to integer. I would hate to
have that hanging over the design as a when we get 2G rows, better be
migrated to v9 and convert the whole application again?

Mike

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert Knight
Sent: Thursday, February 10, 2011 10:56 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Question on Column Definition

The problem is that (I didn’t mention) is that this site I is at V7.



Bob Knight



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Roy Boxwell
Sent: Thursday, February 10, 2011 11:57 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Question on Column Definition



What about BIGINT field ?

Roy Boxwell

SOFTWARE ENGINEERING GmbH

-Product Development-

Robert-Stolz-Straße 5

40470 Düsseldorf/Germany

Tel. +49 (0)211 96149-675

Fax +49 (0)211 96149-32

Email: <mailto:[login to unmask email]> [login to unmask email]

http://www.seg.de



Software Engineering GmbH

Amtsgericht Düsseldorf, HRB 37894

Geschäftsführung: Gerhard Schubert


On 10 Feb 2011, at 17:21, Robert Knight <[login to unmask email]>
wrote:



I have data coming in from MY SQL and going to be loaded into DB2
tables.



The question I have is that the column on my sql is defined as big
integer which is 19 characters from -9,223,372,036,854,775,808 to
-9,223,372,036,854,775,807.



This column is to be used as the primary key on the DB2 tables.



The DB2 definition of large integer has a precision of 31 bits
-2147483648 to +2147483647.



Would it be better to define the DB2 table with char(20) to handle
this?



Anybody run into this before and what is the best method of handling
it?



Thanks



Bob Knight

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 >


________________________________

Introducing IBM® DB2® 10 for z/OS
< http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

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 >


________________________________

Introducing IBM® DB2® 10 for z/OS
< http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

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 * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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