db2 unload issue

Ron Thomas

db2 unload issue

Hello.
Here below is th column defintion and when i am unloading to a flat file , i am gettting the error as below

ACQ_MARGIN_PCT DECIMAL LENGTH 5 Not NULL

CAST(COALESCE(DECIMAL(ACQ_MARGIN_PCT,4,2),CHAR(' ',5)) AS CHAR)
AS ACQ_MARGIN_PCT ,


DB2 ERROR: FETCH CURSOR
 SQLCODE = -406, ERROR:  A CALCULATED OR DERIVED NUMERIC VALUE IS NOT
 WITHIN THE RANGE OF ITS OBJECT COLUMN
 SQLSTATE   = 22003 SQLSTATE RETURN CODE
 SQLERRP    = DSNXRDEC SQL PROCEDURE DETECTING ERROR
 SQLERRD    = -2002  0  90  -1  0  0 SQL DIAGNOSTIC INFORMATION
 SQLERRD    = X'FFFFF82E'  X'00000000'  X'0000005A'  X'FFFFFFFF'
 X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION

Thanks

Ron T

Philip Sevetson

db2 unload issue
(in response to Ron Thomas)
Ron,

I’ve reproduced your original problem.

It looks to me like you’re trying to COALESCE a DECIMAL with a CHAR inside the parentheses. I don’t think that works. Are you trying to create leading spaces? Or blank if null? What would your output look like for a column value of 1? What about 12345?

--Phil

From: Ron Thomas [mailto:[login to unmask email]
Sent: Monday, February 11, 2019 1:28 PM
To: [login to unmask email]
Subject: [DB2-L] - db2 unload issue


Hello.
Here below is th column defintion and when i am unloading to a flat file , i am gettting the error as below

ACQ_MARGIN_PCT DECIMAL LENGTH 5 Not NULL

CAST(COALESCE(DECIMAL(ACQ_MARGIN_PCT,4,2),CHAR(' ',5)) AS CHAR)
AS ACQ_MARGIN_PCT ,


DB2 ERROR: FETCH CURSOR
SQLCODE = -406, ERROR: A CALCULATED OR DERIVED NUMERIC VALUE IS NOT
WITHIN THE RANGE OF ITS OBJECT COLUMN
SQLSTATE = 22003 SQLSTATE RETURN CODE
SQLERRP = DSNXRDEC SQL PROCEDURE DETECTING ERROR
SQLERRD = -2002 0 90 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'FFFFF82E' X'00000000' X'0000005A' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

Thanks

Ron T

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Ron Thomas

RE: db2 unload issue
(in response to Philip Sevetson)

Philip , I would need the output to be displayed as 1.00 or as 123.45. If NULL then it should be shown as blanks

Edited By:
Ron Thomas[Organization Members] @ Feb 11, 2019 - 02:19 PM (America/Eastern)

Philip Sevetson

db2 unload issue
(in response to Ron Thomas)
Ron,

You’re displaying a decimal number which has no fractional values (DECIMAL(5)), and using a format with fractional values (DECIMAL(columnval,4,2)) to represent it. DB2 is expecting your numbers to overflow the whole number display range.

Try this instead:

SET CURRENT SQLID = 'DB2FDB';
DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
SELECT VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT,16),'00000.99') AS ACQ_MARGIN_PCT
FROM SESSION.T1;
COMMIT;

Which produces this result:
ACQ_MARGIN_PCT
--------------
12345.00
06789.00
Or, if you’re representing DECIMAL(5) but it’s storing (DECIMAL(5,2), then divide the value by 100 when selecting the column:

DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
SELECT VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'000.99') AS ACQ_MARGIN_PCT
FROM SESSION.T1;
COMMIT;

Which will give a result of:

ACQ_MARGIN_PCT
--------------
123.45
067.89

Let me know how it works!

--Phil


From: Ron Thomas [mailto:[login to unmask email]
Sent: Monday, February 11, 2019 2:19 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 unload issue


Philip , I would need the output to be displayed as 1.00 or as 123.45.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Philip Sevetson

db2 unload issue
(in response to Philip Sevetson)
Two things to add to it. First, I used a slightly wrong format string for the result you want. This:

SET CURRENT SQLID = 'DB2FDB';
DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
SELECT VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'999.99') AS ACQ_MARGIN_PCT
FROM SESSION.T1;
COMMIT;

Produces leading spaces:
ACQ_MARGIN_PCT
--------------
123.45
67.89

Second, the reason I used the functions as indicated was that the function TO_NUMBER – better for your purposes than CAST – is a synonym for VARCHAR_FORMAT; and VARCHAR_FORMAT wants a column with DECFLOAT datatype for its first argument. The second argument is a format string, as you see.

--Phil

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, February 11, 2019 3:00 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: db2 unload issue

Ron,

You’re displaying a decimal number which has no fractional values (DECIMAL(5)), and using a format with fractional values (DECIMAL(columnval,4,2)) to represent it. DB2 is expecting your numbers to overflow the whole number display range.

Try this instead:

SET CURRENT SQLID = 'DB2FDB';
DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
SELECT VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT,16),'00000.99') AS ACQ_MARGIN_PCT
FROM SESSION.T1;
COMMIT;

Which produces this result:
ACQ_MARGIN_PCT
--------------
12345.00
06789.00

Or, if you’re representing DECIMAL(5) but it’s storing (DECIMAL(5,2), then divide the value by 100 when selecting the column:

DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
SELECT VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'000.99') AS ACQ_MARGIN_PCT
FROM SESSION.T1;
COMMIT;

Which will give a result of:

ACQ_MARGIN_PCT
--------------
123.45
067.89

Let me know how it works!

--Phil


From: Ron Thomas [mailto:[login to unmask email]
Sent: Monday, February 11, 2019 2:19 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 unload issue


Philip , I would need the output to be displayed as 1.00 or as 123.45.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Anguraj Rathinasamy

db2 unload issue
(in response to Philip Sevetson)
Ron,

It appears the issue is with decimal declaration "DECIMAL(ACQ_MARGIN_PCT,4,2)"; query result set is getting high value then declared.
Try with some appropriate higher value or try with some random number.. as "DECIMAL(ACQ_MARGIN_PCT,12,2)"


Regards,
Anguraj

> On Feb 11, 2019, at 3:06 PM, Sevetson, Phil <[login to unmask email]> wrote:
>
> Two things to add to it. First, I used a slightly wrong format string for the result you want. This:
>
> SET CURRENT SQLID = 'DB2FDB';
> DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
> SELECT VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'999.99') AS ACQ_MARGIN_PCT
> FROM SESSION.T1;
> COMMIT;
>
> Produces leading spaces:
> ACQ_MARGIN_PCT
> --------------
> 123.45
> 67.89
>
> Second, the reason I used the functions as indicated was that the function TO_NUMBER – better for your purposes than CAST – is a synonym for VARCHAR_FORMAT; and VARCHAR_FORMAT wants a column with DECFLOAT datatype for its first argument. The second argument is a format string, as you see.
>
> --Phil
>
> From: Sevetson, Phil [mailto:[login to unmask email]
> Sent: Monday, February 11, 2019 3:00 PM
> To: '[login to unmask email]'
> Subject: [DB2-L] - RE: db2 unload issue
>
> Ron,
>
> You’re displaying a decimal number which has no fractional values (DECIMAL(5)), and using a format with fractional values (DECIMAL(columnval,4,2)) to represent it. DB2 is expecting your numbers to overflow the whole number display range.
>
> Try this instead:
>
> SET CURRENT SQLID = 'DB2FDB';
> DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
> SELECT VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT,16),'00000.99') AS ACQ_MARGIN_PCT
> FROM SESSION.T1;
> COMMIT;
>
> Which produces this result:
> ACQ_MARGIN_PCT
> --------------
> 12345.00
> 06789.00
>
> Or, if you’re representing DECIMAL(5) but it’s storing (DECIMAL(5,2), then divide the value by 100 when selecting the column:
>
> DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
> SELECT VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'000.99') AS ACQ_MARGIN_PCT
> FROM SESSION.T1;
> COMMIT;
>
> Which will give a result of:
>
> ACQ_MARGIN_PCT
> --------------
> 123.45
> 067.89
>
> Let me know how it works!
>
> --Phil
>
>
> From: Ron Thomas [mailto:[login to unmask email]
> Sent: Monday, February 11, 2019 2:19 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: db2 unload issue
>
> Philip , I would need the output to be displayed as 1.00 or as 123.45.
>
>
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Ron Thomas

RE: db2 unload issue
(in response to Philip Sevetson)

Thanks Phiip , when i unloaded using VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT,16),'00000.99'), the below i what i am getting

.. 00046.30...........................
004FFFFF4FF000000000000000000000000000
09000046B300000000000000000000000000000

Let me know how to get the right format .

 

Thanks

Ron T

Philip Sevetson

db2 unload issue
(in response to Ron Thomas)
Ron,
For a complete fix of this several things needed to be done. (You really need to look into the Scalar Functions topic of the DB2 SQL Reference manual for full information on this.)
For the immediate problem, it was necessary to make several changes, thus:
DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 1;
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES -12345;

/* produce the correct result */
SELECT CHAR(VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'990.00'),7) AS ACQ_MARGIN_PCT
FROM SESSION.T1;

/* display the result as HEX to confirm that there are no additional bytes in the result, for development only */
SELECT HEX(CHAR(VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'990.00'),7)) AS ACQ_MARGIN_PCT
FROM SESSION.T1;
COMMIT;

Which led to this result:
1> DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL)
2> go
1> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345
2> go
1> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789
2> go
1> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 1
2> go
1> INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES -12345
2> go
1> SELECT CHAR(VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'990.00'),7) AS ACQ_MARGIN_PCT
2> FROM SESSION.T1
3> go
ACQ_MARGIN_PCT
--------------
123.45
67.89
0.01
-123.45
1> SELECT HEX(CHAR(VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'990.00'),7)) AS ACQ_MARGIN_PCT
2> FROM SESSION.T1
3> go
ACQ_MARGIN_PCT
--------------
40F1F2F34BF4F5
4040F6F74BF8F9
404040F04BF0F1
60F1F2F34BF4F5

Several things were needed to fully resolve this. VARCHAR_FORMAT(DECFLOAT(args),’format’) is needed to present a decimal with a visible decimal point, and the format string is key. The VARCHAR_FORMAT entry in the manual gives the needed values. Then, in order to reduce the result to a non-variable string (and eliminate the length byte leading the field, and the hex zeroes trailing the actual output), the CHAR() function must be applied to the result. In this case, a DECIMAL(5) field needs the five bytes for the value, a byte for the visible decimal point, and a byte for the hypothetical minus sign (DECIMAL fields can be negative in value). So CHAR(variable result string,7) encapsulates the computed string.
--Phil Sevetson
*****************************************************
From: Ron Thomas [mailto:[login to unmask email]
Sent: Monday, February 11, 2019 11:21 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 unload issue

Thanks Phiip , when i unloaded using VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT,16),'00000.99'), the below i what i am getting

.. 00046.30...........................
004FFFFF4FF000000000000000000000000000
09000046B300000000000000000000000000000

Let me know how to get the right format .
*****************************************************
From Phil, 2/11 at 3:06PM EST:
Two things to add to it. First, I used a slightly wrong format string for the result you want. This:

SET CURRENT SQLID = 'DB2FDB';
DECLARE GLOBAL TEMPORARY TABLE T1 (ACQ_MARGIN_PCT DECIMAL(5) NOT NULL);
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 12345;
INSERT INTO SESSION.T1 (ACQ_MARGIN_PCT) VALUES 6789;
SELECT VARCHAR_FORMAT(DECFLOAT(ACQ_MARGIN_PCT/100,16),'999.99') AS ACQ_MARGIN_PCT
FROM SESSION.T1;
COMMIT;

Produces leading spaces:
ACQ_MARGIN_PCT
--------------
123.45
67.89

Second, the reason I used the functions as indicated was that the function TO_NUMBER – better for your purposes than CAST – is a synonym for VARCHAR_FORMAT; and VARCHAR_FORMAT wants a column with DECFLOAT datatype for its first argument. The second argument is a format string, as you see.

--Phil

*****************************************************
From Ron, 2/7 at 10:18AM EST:

Roy - I changed to this CAST(COALESCE(DIGITS(ACQ_SERVICE_CODE),CHAR(' ', 5)) AS CHAR) and it worked . Thanks for the help

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**