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

Ron Thomas

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

Apologies for the delay in responding Phil . Thanks for all of your help

Philip Sevetson

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

My pleasure, and it was an interesting problem.

I do a lot with formatting output in DSNTIAUL, frequently using it to write DB2 utility statements and other types of code. This is the first time I’ve found a solution to the problem of conventional display representation of numeric output. I expect it to be very useful in my future reporting needs.

--Phil

From: Ron Thomas [mailto:[login to unmask email]
Sent: Wednesday, February 20, 2019 5:30 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 unload issue


Apologies for the delay in responding Phil . Thanks for all of your help

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

Michael Hannan

RE: db2 unload issue
(in response to Ron Thomas)



In Reply to Ron Thomas:

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

Ron,

As an afterthought, I run into this type of problem in my Queries all the time, whether in Spufi, DSNTIAUL, whatever.

Casting using the Decimal Function always has the danger that you did not make it big enough. Best to do all arithmetic in Float, especially if any multiplication and division, but at the final step cast to Decimal to choose the number of digits after the decimal point, and the total digits. DEC(expr, 4, 2) allows 2 digits after the decimal point, and 4 in total, meaning you only get 2 digit in front of the decimal point. Yet original number could have had 5 digits (in front of dec. point right)? Using Dec Float is also possible to prevent rounding in binary format, but has some restrictions for usage.

CHAR function on DEC(4, 2) results in optional sign character (for minus) followed by 2 digits, followed by the decimal point char, followed by 2 more digits, which would be zeros for your original Dec(5) number with no decimals unless you divide by 100. CHAR function allows to select the char you want to be placed as a decimal point.

If you have to work with big numbers, but no digits after the Decimal point, you can use Integer, or even BIGINT for double precision Integers up to about 18 or 19 digits. Finally cast them to Character for display purposes.

If anyone has problems getting their output formatted from DSNTIAUL, e.g. with removal of Null Indicators, removal of varchar lengths, getting into the right Char format, char string justification, even conversion of Catalog Unicode strings to EBCDIC, drop me an email. I have long experience on the topic. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd