DB2 SQL causing overflow error

Hello Gurus!
Environment JDE on DB2 on AS400.  Using ODBC connection to pull data from DB2 to a Windows app.  I am using the following SQL

SELECT CAST(FY as char(4))||'M'||CAST(Period as char(10)) as FYPeriod, CAST(GBCO as char(10)),
CAST(GBMCU As Char(10)), CAST(GBOBJ As Char(4)), CAST(GBSUB As Char(4)), CAST(GBSBL As Char(10)), CAST(Data As NUMERIC(15,0))                        
FROM xxxxxxxx.V_F0902
where CAST(FY as char(4)) = '2014'
AND CAST(Period as char(2)) = '1'
AND CAST(GBCO as char(10)) = '00020'  


When executing I receive the 'Arithmetic operation resulted in overflow.'  I can execute a limited version of the same SQL- shown below - and it executes file.  

SELECT CAST(FY as char(4))||'M'||CAST(Period as char(10)) as FYPeriod, CAST(Data As NUMERIC(15,0))                        
FROM proddta.V_F0902
where CAST(FY as char(4)) = '2014'
AND CAST(Period as char(2)) = '1'
AND CAST(GBCO as char(10)) = '00020'  

Now to make things more interesting.  I have multiple versions of JDE - 2.2, 7.3 and 9.1.  The above SQL works just fine against JDE 9.1 and JDE 2.2 and only fails against the 7.2 instance.  

Thoughts?  Recommendations?  

Thanks in advance!
Cheryl McCormickConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
Since none of us here on EE know the content of your data, it's probably best if you start by removing CAST from your query, one at a time. At some point, the query will run correctly, and you'll know which CAST is causing the error because it'd be the last one you removed before it worked.

HTH,
DaveSlash
0
Dave FordSoftware Developer / Database AdministratorCommented:
Alternately, you could remove all the CAST functions, verify the query works, and add them back, one-by-one.
0
Cheryl McCormickConsultantAuthor Commented:
Thanks for the quick reply.  I tried every combination of removing the CAST functions.  Same error regardless of whether I use CAST or not.  Funny that the SQL works against JDE 9,1 and JDE 2.2 but not against JDE 7.3.  Other recommendations are eagerly awaited!
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Cheryl McCormickConsultantAuthor Commented:
Quick update.... I can't execute ANYTHING unless I have a CAST statement for each column in the SELECT statement,
0
Dave FordSoftware Developer / Database AdministratorCommented:
So, this works?

SELECT FY,
       Period,
       GBCO,
       GBMCU,
       GBOBJ,
       GBSUB,
       GBSBL,
       Data
  FROM xxxxxxxx.V_F0902
 where CAST(FY as char(4)) = '2014'
   AND CAST(Period as char(2)) = '1'
   AND CAST(GBCO as char(10)) = '00020'

Open in new window

0
Kent OlsenData Warehouse Architect / DBACommented:
Hi hypermac,

The good news is that the query works with the only numeric field in the column list.

So looking at the other columns:

CAST(GBCO as char(10)),
CAST(GBMCU As Char(10)),
CAST(GBOBJ As Char(4)),
CAST(GBSUB As Char(4)),
CAST(GBSBL As Char(10)),

I assume that 1 or more of these are numeric data types in the database and that the query is converting the item(s) to character.

I suspect that the conversion to ASCII and then truncation as it's being cast to CHAR is the problem.  A data item in one or more columns is losing significance.  If any of these items are numeric, try this for them:

CAST(mod(GBCO,10000000000) as char(10)),
CAST(mod(GBMCU, 10000000000) As Char(10)),
CAST(mod(GBOBJ, 10000) As Char(4)),
CAST(mod(GBSUB, 10000) As Char(4)),
CAST(mod(GBSBL, 10000000000) As Char(10)),

That won't solve the problem, but it might identify it.  And if any of these columns are non-integer number (float, double, numeric(x,y), etc.) the rules will change a bit.


Kent
0
Dave FordSoftware Developer / Database AdministratorCommented:
Please post the results of that query and also the data-types (and lengths) for each of the database columns.

-- DaveSlash
0
Cheryl McCormickConsultantAuthor Commented:
Sorry for the delay in my reply...travel nightmares!    I'll test & post results as soon as I get home later today.
0
tliottaCommented:
where CAST(FY as char(4)) = '2014'

Open in new window

Why would you want to code something like that instead of simply coding:
where FY = 2014

Open in new window

Seeing things like that makes for a lot of potential confusion not to mention performance questions. If it's somehow necessary to use CAST there, we possibly need to know the reason. The other WHERE clause items also seem suspicious.

Tom
0
Cheryl McCormickConsultantAuthor Commented:
Thanks to everyone for your suggestions.  As mentioned before, if I don't use the CAST function, I immediately receive the overflow error.  The SQL above executes successfully against the View in JDE 9.1 and JDE 2.2.  The exact same View has been created against the JDE 7.3 tables.    The SQL only fails against the JDE 7,3 View.  All three JDE instances are installed on the same AS400 server.  So.... same View...same backend table (F0902)...same server....same SQL script.  How can I get different results?  Of course, the only thing I have control over is executing the SQL script.  The DBA said he created the same View and sent me his CREATE View scripts.  I'll post the columns data types / field sizes and the results of the query above asap.

Thanks again.
0
Cheryl McCormickConsultantAuthor Commented:
Data types in F0902 table

GBCO          String (5)
GBMCU      String (12)
GBOBJ         String (6)
GBSUB        String (8)
GBSBL         String (8)
0
Kent OlsenData Warehouse Architect / DBACommented:
Hold on.  You're recasting character variables a character variables of a different length?  That's easier to do with string functions.

GBCO || '     ',
GBMCU || '  '
LEFT (GBOBJ, 4),
GBSUB || '    ',
LEFT (GBSBL, 8)

Why do you want to recast the character columns to a different size?  Return them in their defined size and the application should pad/truncate them as needed.


Kent
0
Dave FordSoftware Developer / Database AdministratorCommented:
What about the data-types for FY, Period, and Data? Those are used in your original query.
0
Dave FordSoftware Developer / Database AdministratorCommented:
If you insist on using a function, it's probably safer to use TRIM:

SELECT trim(FY)
         || 'M'
         || trim(Period) as FYPeriod, 
       trim(GBCO) as GBCO,
       trim(GBMCU) as GBMCU,
       trim(GBOBJ) as GBOBJ,
       trim(GBSUB) as GBSUB,
       trim(GBSBL) as GBSBL),
       trim(Data) as Data
  FROM xxxxxxxx.V_F0902
 where trim(FY) = '2014'
   and trim(Period) = '1'
   AND trim(GBCO) = '00020'   

Open in new window

0
Cheryl McCormickConsultantAuthor Commented:
FY, Period & Data are numeric fields in JDE.
0
Dave FordSoftware Developer / Database AdministratorCommented:
In that case, can you please post the output from this query:

SELECT varchar(FY)
         || 'M'
         || varchar(Period) as FYPeriod, 
       trim(GBCO) as GBCO,
       trim(GBMCU) as GBMCU,
       trim(GBOBJ) as GBOBJ,
       trim(GBSUB) as GBSUB,
       trim(GBSBL) as GBSBL),
       varchar(Data) as Data
  FROM xxxxxxxx.V_F0902
 where FY = 2014
   and Period = 1
   AND trim(GBCO) = '00020'   

Open in new window

0
Cheryl McCormickConsultantAuthor Commented:
Yes.... Give me a few.  Thanks!
0
Cheryl McCormickConsultantAuthor Commented:
I just received the 'File Field Descriptions' from the DBA,  They are attached.  Maybe this will help?
0
Cheryl McCormickConsultantAuthor Commented:
SQL updated for accurate field lengths:
SELECT CAST(FY as char(2))||'M'||CAST(Period as char(2)) as FYPeriod, CAST(GBCO as char(5)),
CAST(GBMCU As Char(12)), CAST(GBOBJ As Char(6)), CAST(GBSUB As Char(8)), CAST(GBSBL As Char(8)), CAST(Data As NUMERIC(15,0))                    
FROM xxxxxx73.V_F0902
where CAST(FY as char(2))||'M'||CAST(Period as char(2))  = '2014M1'
AND CAST(GBCO as char(5)) = '051'      

Results in error message:  'Arithmetic operation resulted in an overflow'
0
Kent OlsenData Warehouse Architect / DBACommented:
We seem to be going in circles here.

1)  Why are you CASTING strings to different lengths?  That's a very bizarre way to resize a string.
2)  You need to identify which column(s) are an issue.  Strip the query down to the simple one that you found to work.  Then add the other columns back 1 at a time to determine which columns are an issue.


Kent
0
Dave FordSoftware Developer / Database AdministratorCommented:
So, what was the output for the query I posted 2.5 hours ago?

-- DaveSlash
0
Cheryl McCormickConsultantAuthor Commented:
Testing:
SELECT CAST(FY as char(4))||'M'||CAST(Period as char(10)) as FYPeriod, CAST(Data As NUMERIC(15,0)),
CAST(mod(GBCO,10000000000) as char(5))                   
FROM pfsdta73.V_F0902
where CAST(FY as char(4)) = '2015'
AND CAST(Period as char(2)) = '1'
AND CAST(GBCO as char(5)) = '00110'      

Results - I don't know how to interpret this:

2015M1               -1815      1.1E2
2015M1               180677      1.1E2
2015M1               -713668      1.1E2
etc.....
0
Cheryl McCormickConsultantAuthor Commented:
SELECT CAST(FY as char(2))||'M'||CAST(Period as char(2)) as FYPeriod,
TRIM(GBCO) as GBCO,
TRIM(GBMCU) As GBMCU,
TRIM(GBOBJ) As GBOBJ,
TRIM(GBSUB) As GBSUB,
TRIM(GBSBL) As GBSBL,
CAST(Data As NUMERIC(15,0))                    
FROM XXXXXX73.V_F0902
where CAST(FY as char(2))||'M'||CAST(Period as char(2))  = '2014M1'
AND TRIM(GBCO) = '00110'      

Error:   'Arithmetic Operation results in an overflow"
0
Cheryl McCormickConsultantAuthor Commented:
SELECT varchar(FY)
         || 'M'
         || varchar(Period) as FYPeriod,
       trim(GBCO) as GBCO,
       trim(GBMCU) as GBMCU,
       trim(GBOBJ) as GBOBJ,
       trim(GBSUB) as GBSUB,
       trim(GBSBL) as GBSBL,
       varchar(Data) as Data
  FROM xxxxx73.V_F0902
 where FY = 2014
   and Period = 1
   AND trim(GBCO) = '00110'  

Error:  ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0802 - Data conversion or data mapping error.
0
Dave FordSoftware Developer / Database AdministratorCommented:
This is where your debugging skills come in.

I'm pretty confident that this would run. Does it?

SELECT FY,
       Period, 
       GBCO,
       GBMCU,
       GBOBJ,
       GBSUB,
       GBSBL,
       Data
  FROM xxxxx73.V_F0902

Open in new window


Try it out if you don't know.

Then, one step at a time, start adding your logic. Remember ... ONE STEP AT A TIME! As you're building the complexity, at some point (before you finish) you'll discover where the error is occurring.

HTH,
DaveSlash
0
Cheryl McCormickConsultantAuthor Commented:
Thanks, DaveSlash,
I've done the basic debugging steps.  This above SQL immediately returns the overflow error identified above.   This is what everyone is missing.  The original SQL executes successfully against the View on JDE9.1 and JDE2,2.  Works great and returns what is expected.  Same SQL FAILS against the JDE7.3 instance.   The View is the same in JDE91, JDE22 and JDE73.  So what would cause it to fail in the JDE73 instance?
0
Kent OlsenData Warehouse Architect / DBACommented:
Hypermac,

Nobody that's responded to your question has missed that.  We're all aware that you get different results when you run the query in a different environment.

What we need from you is to identify where the error occurs.  We have no access to your environment and can't debug your query.  But we can guide you through the steps and the first thing that needs to be done is identify which column (or columns) is generating an error.

Take the stripped down query that runs everywhere and add one of the columns from the failing query.  Test it.  Then take the stripped down query and add a different column.  Test it.  Repeat for every one of the columns that were stripped out.  This will tell you (and us) where the error is.


Kent
0
Cheryl McCormickConsultantAuthor Commented:
Sorry about being cranky.  I've been trying to get this resolved for over a week.  From an earlier part of the thread, the only part of the SQL that executes successfully is the SELECT that contains the FYPeriod and the Data.  All other columns cause the overflow issue.  with the CAST function or without it... same results.  I've added every other column with and without the CAST and receive the same error message.  At least it's consistent....
0
tliottaCommented:
We are aware of what succeeds and what fails. But you do give ambiguous or conflicting statements in some places:
The exact same View has been created against the JDE 7.3 tables.    The SQL only fails against the JDE 7,3 View.  All three JDE instances are installed on the same AS400 server.  So.... same View...same backend table (F0902)...same server....same SQL script.
You say "... same View...same backend table (F0902)..." immediately after saying you created another view over a different table. In fact, you say "the JDE 7.3 tables" as if they are separate/different from tables in other JDE instances.

Now, I realize that you possbly mean that the source for the view is the same in all three cases. But that wouldn't make it the "same view". And the tables might have the same source specifications, but that wouldn't make them all the "same backend table". A table created under one version of DB2 with whatever fixes might have been installed might not be the "same" as a table created earlier or later.

But then...

By saying that you are running three versions of JDE, it's not clear what is meant. Does it mean three different schemas (with potentially different ownerships/authorities as well as possibly different related items like indexes, etc.)? Or does it mean three different clients running the SQL? What are the differences relevant to the queries?

(It seems that you might have a single table and single view while routing the same query string through three different clients.)

In fact, just how are you running the queries? You say you're pulling data "from DB2 to a Windows app". What "Windows app"? Is that app some JDE function? What "DB2"? I.e., what is its VRM? It's apparently DB2 for i, but what version?

Also:
where CAST(FY as char(2))||'M'||CAST(Period as char(2))  = '2014M1'

Open in new window

Why do you keep CASTing FY to CHAR(2) when it's apparently a 4-digit year? And why CAST Period to CHAR(2) when you seem to want a CHAR(1) result?

I'm wondering if this is a DB2 issue to begin with. It seems possible that it's a JDE client issue.

Also:
Error:  ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0802 - Data conversion or data mapping error.
But also:
Error:   'Arithmetic Operation results in an overflow"
The first error is definitely a DB2 client error message. But the second error message is very ambiguous. It doesn't seem to come from DB2 server nor client. It seems possible that it comes from JDE.

Hard to be sure, though. Where do you actually see the error message? In a message-box on a PC? In a log file? Somewhere else? When do you see it? Do you type the SELECT statement into an entry field and see the error when you press <Enter>? Is the query embedded in some source and executed somehow by JDE?

Lots of ambiguity that needs to be cleared before the topic area can even be certain.

Tom
0
Cheryl McCormickConsultantAuthor Commented:
Hi Tom,  The company is currently using three different versions of JDE to run the business - JDE9.1, JDE7.3 and JDE2.2.  All versions are running on the same AS400 server. There are three different JDE clients running.   I need the same data from each instance of JDE.  The F0902 table is a base table on each instance of JDE.  I have created the same View  in each instance of JDE that is written against the F0902 table.  Yes, there are three Views - one on JDE 9.1, one on JDE 7.3 and one on JDE 2.2.  The columns used in the View have the same data type and field length in each JDE instance (9.1, 7.3 and 2.2).  The FY column has a type of Numeric and length of 2.
I am using a Windows application to run a SELECT statement against each View.  We use the same ODBC connection against each of the JDE9.1, JDE 2.2 and JDE7.3.    If I do not use the CAST function in the SELECT SQL I always receive the 'Arithmetic Operation resulted in an overflow' error message.  Using the CAST function I can successfully execute the SELECT SQL against the JDE9.1 and JDE2.2 Views.  It fails with the overflow error against the JDE 2.2 instance.   However, if I change the SELECT SQL to the following --  CAST(FY as char(4))||'M'||CAST(Period as char(10)) as FYPeriod, CAST(Data As NUMERIC(15,0)), --  and removing all of the other fields in the SELECT, data is returned without errors.    I hope the clarifies... at least a little more....
0
tliottaCommented:
I apologize for being thick skulled, but there many possible configurations. It's only slowly becoming clear exactly what we're trying to solve. Maybe a little more brain-storming will draw something out.
Yes, there are three Views - one on JDE 9.1, one on JDE 7.3 and one on JDE 2.2.
And:
All versions are running on the same AS400 server. There are three different JDE clients running.   I need the same data from each instance of JDE.  The F0902 table is a base table on each instance of JDE.
I don't quite get this part, but I can accept it. Normally a given server would have a single JDE instance. Three instances pointing at a single schema for their tables is questionable at best.

That is, if there is a single physical table on your iSeries server. Or... is there? You say:
The F0902 table is a base table on each instance of JDE.
That implies three separate physical tables. It implies that the three JDE versions do not have views pointing at the same table, but there are three separate views pointing at three separate tables in three separate schemas.

The three JDE versions have slightly different installation requirements. Only JDE 9.1 is still supported, AFAIK. It's possible that JDE 7.3 requires a DB2 or system PTF that doesn't (yet?) exist for the DB2 or system version that it's installed on. Or it might need a JDE fix from Oracle, and I don't know if there's any way to get one.

It's possible that the F0902 physical file object needs to be recreated. We don't know how it was created. It's possible that the V_F0902 view is somehow wrong for the JDE 7.3 table. We haven't seen how the view is defined, so we can't make many useful guesses.

We can, however, be somewhat certain that the SQL from the original question that you were trying to run is "wrong" as far as the F0902 table goes and it should never return valid results. But if good results do come back from two of the JDE versions, it must be because the view is reformatting some of the columns. We need to see the view definition.

The GBFY column in F0902 is NUMERIC(2 0). There is no way it will CAST() to become '2014'. A 2-digit numeric won't CAST() to a useful 4-digit string. Therefore the view is changing things, probably prepending the century. Similarly, there is no "Period" column in F0902, but rather the GBFQ column is the 'Fiscal Quarter'; and it's a UDC (4), i.e., a 'User-Defined Code'. (Assuming that 'Period' is related somehow to GBFQ, which is currently marked as "(Obsolete)" in some JDE references.) Impossible for us to know what you're doing with it, but again the view is needed in order to guess. (We might even need to see what you have in the UDC tables in all three JDE instances. That's especially true if any calculations get involved that reference a UDC item.)

And we still don't know where/when/how you see the 'Arithmetic overflow' error. It still doesn't seem to be a DB2 error message, but it might be related to something that can be changed via DB2.

We need to see the view definition, and we need a way to know what program code is throwing the error.
0
Cheryl McCormickConsultantAuthor Commented:
Turns out the entire issue was related to different coded characters sets on the different versions on JDE.  I was able to handle that in the SQL.  Thanks to everyone for their assistance!
0
tliottaCommented:
Can you post the solution? A CCSID difference would rarely have anything to do with "Arithmetic overflow". Seeing how it affected you would be potentially useful to other members.

Tom
0
Cheryl McCormickConsultantAuthor Commented:
The solution was to add 'CCSID 37' as part of the CAST statement as shown below:

CAST(GBOBJ As Char(6) CCSID 37) as GBOBJ,

That fixed it!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.