[z/OS & Linux] LOAD and DEC problem...

Cuneyt Goksu

[z/OS & Linux] LOAD and DEC problem...
Hi Colleagues,

I'm stuck and need your help.

I have table T1 on z/OS
Have a column C1 defined as DEC(7,2) and has data 60000.00
Another column C2 defined as DEC(7,2) and has data 500.00

Then I DSNTIAUL with SELECT DIGITS(c1) FROM T1
move the data to Linux without any problem.

I see the same data in every editor such as VI clearly even in Hex editor...
6000000 and 0050000

Linux Table columns are defined as is DEC(7,2)...

Load gives the following error for C1
SQL0406N A numeric value in the UPDATE or INSERT statement is not within
the range of its target column. SQLSTATE=22003

and LOADs the C2 as 50000.00

There is something wrong with the scale but I can not see it.

Why the scaling of DB2 for Linux and DB2 for z/OS looks different?

Regards,
Cuneyt

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sorensen Henrik (KAFC 21)

Re: [z/OS & Linux] LOAD and DEC problem...
(in response to Cuneyt Goksu)

Cuneyt Goksu (Usa.net), wrote
>I see the same data in every editor such as VI clearly even in Hex
editor...
>6000000 and 0050000

on z/os, the packed decimal, is one half byte per digit _and_ one half
byte for the sign,
your samples seems to be missing the signbyte.

should probably be
x'6000000C'
x'0050000C'

hope this helps
Henrik

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Trovijo Jr (UOL)

Re: [z/OS & Linux] LOAD and DEC problem...
(in response to Sorensen Henrik (KAFC 21))
Hi Cuneyt,

I've moved a whole application database - 8 db2 databases, 1500 tables, +250gb (ok, very small, I know) - from OS/390 to zLinux smoothly - with a few exceptions - using db2move.
By the way the exceptions were not related to decimal fields, almost every table here has a decimal field, we would notice that.

HTH,
Walter Trovijo Jr.

----- Original Message -----
From: "Cuneyt Goksu (Usa.net)" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, January 25, 2007 3:21 PM
Subject: [DB2-L] [z/OS & Linux] LOAD and DEC problem...


> Hi Colleagues,
>
> I'm stuck and need your help.
>
> I have table T1 on z/OS
> Have a column C1 defined as DEC(7,2) and has data 60000.00
> Another column C2 defined as DEC(7,2) and has data 500.00
>
> Then I DSNTIAUL with SELECT DIGITS(c1) FROM T1
> move the data to Linux without any problem.
>
> I see the same data in every editor such as VI clearly even in Hex editor...
> 6000000 and 0050000
>
> Linux Table columns are defined as is DEC(7,2)...
>
> Load gives the following error for C1
> SQL0406N A numeric value in the UPDATE or INSERT statement is not within
> the range of its target column. SQLSTATE=22003
>
> and LOADs the C2 as 50000.00
>
> There is something wrong with the scale but I can not see it.
>
> Why the scaling of DB2 for Linux and DB2 for z/OS looks different?
>
> Regards,
> Cuneyt
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.432 / Virus Database: 268.17.10/651 - Release Date: 24/1/2007 18:48
>
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rodney Krick

[z/OS & Linux] LOAD and DEC problem...
(in response to Walter Trovijo Jr (UOL))



Cuneyt,

I think it works as designed. How can you linux box know about your decimal
point? If you read the description of the DIGITS() function, you'll see
that it returns a string representation of the number, without decimal
point. Did you try it out using the CHAR() function?
Example:
CREATE TABLE MYTAB(NUM DECIMAL(7, 2)) IN DATABASE S0RCBV;
INSERT INTO MYTAB VALUES (123);
INSERT INTO MYTAB VALUES (123,1);
INSERT INTO MYTAB VALUES (123,12);
SELECT DIGITS(NUM) AS DIGITS, CHAR(NUM) AS CHAR FROM MYTAB;
returns:
---------+---------+---
DIGITS CHAR
---------+---------+---
0012300 00123,00
0012310 00123,10
0012312 00123,12

HTH.

Mit freundlichen Grüßen / Best regards,

Rodney Krick
Senior Consultant
____________________________
aformatik Training & Consulting GmbH & Co. KG
Tilsiter Str. 8, D-71065 Sindelfingen, Germany
Tel.: +49 (0)7031-23 807-0
Fax: +49 (0)7031-23 807-19

mailto:[login to unmask email]
___________________________________________
ECIS, Education Center for IBM Software, Websphere and DB2
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Maja Markotic

Re: [z/OS & Linux] LOAD and DEC problem...
(in response to Rodney Krick)
Cuneyt,

Check this out (SQL Reference, p. 264):

"The result of the [DIGITS] function is a fixed-length character string
representing the absolute value of the argument without regard to its scale
."

So:

SELECT DIGITS(DECIMAL(60000.00,7,2))
FROM SYSIBM.SYSDUMMY1
WITH UR;

gives the result 6000000 which doesn't fit into DEC(7,2) column.

Maja





"Cuneyt Goksu
(Usa.net)"
<[login to unmask email] To
NET> [login to unmask email]
Sent by: DB2 Data cc
Base Discussion
List Subject
<[login to unmask email] [DB2-L] [z/OS & Linux] LOAD and DEC
ORG> problem...


25.01.2007 19:21


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






Hi Colleagues,

I'm stuck and need your help.

I have table T1 on z/OS
Have a column C1 defined as DEC(7,2) and has data 60000.00
Another column C2 defined as DEC(7,2) and has data 500.00

Then I DSNTIAUL with SELECT DIGITS(c1) FROM T1
move the data to Linux without any problem.

I see the same data in every editor such as VI clearly even in Hex
editor...
6000000 and 0050000

Linux Table columns are defined as is DEC(7,2)...

Load gives the following error for C1
SQL0406N A numeric value in the UPDATE or INSERT statement is not within
the range of its target column. SQLSTATE=22003

and LOADs the C2 as 50000.00

There is something wrong with the scale but I can not see it.

Why the scaling of DB2 for Linux and DB2 for z/OS looks different?

Regards,
Cuneyt

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

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm



Disclaimer:
Ova elektronicka poruka je povjerljiva i moze sadrzavati povlastene informacije. Ako niste naznaceni primatelj, niste ovlasteni citati, printati, pohraniti, obra?ivati ili priopcavati ovu poruku. Ako ste ovu poruku primili greskom, molimo Vas da o tome odmah obavijestite posiljatelja i izbrisete ovu poruku, njene privitke i kopije. Zagrebacka banka d.d. ne preuzima nikakvu odgovornost s obzirom na bilo koju mogucu netocnost bilo kojeg podatka koji je sadrzan u ovoj poruci ako takav podatak nije povezan s registriranim predmetom poslovanja Zagrebacke banke d.d. Stajalista izrazena u ovoj poruci ne odrazavaju nuzno sluzbena stajalista Zagrebacke banke d.d. Hvala!
This e-mail is confidential and may also contain privileged information. If you are not the intended recipient, you are not authorised to read, print, save, process or disclose this message. If you have received this message by mistake, please inform the sender immediately and delete this e-mail, its attachments and any copies. Zagrebacka banka d.d. does not take any responsibility with regards to any possible inaccuracy of any data contained in this e-mail if such data do not relate to the registered operations of Zagrebacka banka d.d. The opinions expressed in this e-mail do not necessarily reflect the official positions of Zagrebacka banka d.d.. Thank you!

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Cuneyt Goksu

Re: [z/OS & Linux] LOAD and DEC problem...
(in response to Maja Markotic)
Hi,

I was thinking that DB2 for Linux LOAD will distinguish the data & datatype
itself similar to DECIMAL EXTERNAL in DB2 for z/OS LOAD.

CHAR worked!

Thank you very much to all esteemed member of DB2-L

Regards,
Cuneyt
-----Original Message-----
From: Rodney Krick [mailto:[login to unmask email]
Sent: Friday, January 26, 2007 8:15 AM
Subject: [z/OS & Linux] LOAD and DEC problem...


Cuneyt,

I think it works as designed. How can you linux box know about your
decimal point? If you read the description of the DIGITS() function, you'll
see that it returns a string representation of the number, without decimal
point. Did you try it out using the CHAR() function?
Example:
CREATE TABLE MYTAB(NUM DECIMAL(7, 2)) IN DATABASE S0RCBV;
INSERT INTO MYTAB VALUES (123);
INSERT INTO MYTAB VALUES (123,1);
INSERT INTO MYTAB VALUES (123,12);
SELECT DIGITS(NUM) AS DIGITS, CHAR(NUM) AS CHAR FROM MYTAB;
returns:
---------+---------+---
DIGITS CHAR
---------+---------+---
0012300 00123,00
0012310 00123,10
0012312 00123,12

HTH.

Mit freundlichen Grüßen / Best regards,

Rodney Krick
Senior Consultant
____________________________
aformatik Training & Consulting GmbH & Co. KG
Tilsiter Str. 8, D-71065 Sindelfingen, Germany
Tel.: +49 (0)7031-23 807-0
Fax: +49 (0)7031-23 807-19

mailto:[login to unmask email]
___________________________________________
ECIS, Education Center for IBM Software, Websphere and
DB2-------------------------------------------------------------------------
-------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm