Question on Column Definition

Robert Knight

Question on Column Definition

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

_____________________________________________________________________
* 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

Philip Sevetson

Re: Question on Column Definition
(in response to Robert Knight)
Robert,
Why not use DEC(19,0) for the DB2 datatype and bring it in with a LOAD using DECIMAL EXTERNAL? I don't think there's a downside, other than if you need this number as input to computation (BCDs are not particularly efficient in computation, especially compared to integers).
--Phil Sevetson

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


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

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < 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

Byron Pierce

Re: Question on Column Definition
(in response to Philip Sevetson)
I didn't know the former Indiana Hoosier basketball coach was a DB2 guy !
And didn't I just see you announcing games on ESPN !?!!?

OK, bad humor perhaps...

Why not just define the DB2 table/column as BIGINT ? And if it's already
defined as INTEGER, you can alter it to BIGINT.

Byron C. Pierce
Prudential Retirement Technology
Database Services - DBA Operate Team
280 Trumbull Street - H18C
Hartford, CT 06103-3509
Work: 860.534.4222
Fax: 860.534.3135



From:
Robert Knight <[login to unmask email]>
To:
[login to unmask email]
Date:
02/10/2011 11:24 AM
Subject:
[DB2-L] Question on Column Definition
Sent by:
IDUG DB2-L <[login to unmask email]>



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

_____________________________________________________________________
* 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
[attachment "C.htm" deleted by Byron Pierce/PI/Prudential]


_____________________________________________________________________
* 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

Robert Knight

Re: Question on Column Definition
(in response to Roy Boxwell)
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]<mailto:[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]<mailto:[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 >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < 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

Roy Boxwell

Re: Question on Column Definition
(in response to Byron Pierce)
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.

_____________________________________________________________________
* 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

Roy Boxwell

Re: Question on Column Definition
(in response to Roy Boxwell)
Are you sure that is a good idea? I know enough people who just do not understand decimal data types...present company excluded naturally!!

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:37, "Sevetson, Phil" <[login to unmask email]> wrote:

> Robert,
> Why not use DEC(19,0) for the DB2 datatype and bring it in with a LOAD using DECIMAL EXTERNAL? I don’t think there’s a downside, other than if you need this number as input to computation (BCDs are not particularly efficient in computation, especially compared to integers).
> --Phil Sevetson
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert Knight
> Sent: Thursday, February 10, 2011 11:21 AM
> To: [login to unmask email]
> Subject: [DB2-L] Question on Column Definition
>
>
> 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.
>
>
>
>
> 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 * 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