If a column is defined as SMALLINT, and one does a SUM (or other similar
function) on that column, the result is an INT. However, if the column is an
INT, the result of the SUM function remains an INT.

This is causing overflow on our data warehouse queries that are
automatically generated by a point and click tool (such as Cognos), and we
cannot ask end users to customize the SQL to cast the result as BIGINT,
because it is all point and click and they never see the SQL.

Any chance of getting DB2 to automatically make SUM function result into
BIGINT if the table column is INT? How do I request such an enhancement from
IBM?

Having to define the detail columns as BIGINT just because a column may be
SUM'ed will waste a lot space in our warehouse since we have billions of
rows.

One idea is to create a view using "BIGINT(int_column_name) AS
int_column_name".
Then, show the view to users.

Op 11-09-10 09:36, Martin schreef:
According to some googling, i found that DB2 functions can be overloaded

So you should be able (but i dont know how) to create your own SUM()
function that always returns a BIGINT

--
Luuk

Op 11-09-10 12:43, Luuk schreef:
to clarify more,
there is an "How to Create User-Defined Functions" in COGNOS
(i found it at: http://apsdwh.vlaanderen.be/cognos/help/hwtcrtudfs.pdf)

i think this is a problem for COGNOS, or any reporting tool...?



--
Luuk

"Luuk" <Luuk (AT) invalid (DOT) lan> wrote

I don't understand how a custom function is going to be used by a
point-and-click report generator.

"Tonkuma" <tonkuma (AT) fiberbit (DOT) net> wrote

I don't understand how a point-and-click report writer (which only
understands standard SQL functions) is going to generate the above
expression. Maybe I am missing something.

BTW, you don't need to cast a SUM as INT, since that is the default of doing
a SUM, even if the base column is a SMALLINT. The problem is that a SUM of
INT also returns INT (instead of BIGINT).

On Sep 12, 10:04*am, "Martin" <n... (AT) none (DOT) com> wrote:
"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM",
db2admin.SUM was used for SUM( INT(edlevel) ) in the query:
SELECT workdept
, SUM( INT(edlevel) ) AS educated_manpower
, SUM(salary + bonus) AS yearly_payment
FROM employee
GROUP BY
workdept
;

If current_path was SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN",
sysibm.SUM was used for SUM( INT(edlevel) ) in same query.

So, it is possible to let DB2 use db2admin.SUM instead of sysibm.SUM
without modifying queries(wheather the queries were generated by tools
or constructed by users/programmers/administrators), by updating
current_path.

supplied SAMPLE tables.

Op 12-09-10 02:59, Martin schreef:
"Tonkuma" explained that in another thread....
(if you dont see it, look here: http://tinyurl.com/2w54s7x)

Basically you define a new function for the user of this report-regenerator,
give it the name SUM
and make it return a BIGINT (which contains, of course, the SUM)

--
Luuk

"Luuk" <Luuk (AT) invalid (DOT) lan> wrote

I did the following:

1. created the new function as DB2INST1.SUM that converts SUM to BIGINT
2. db2 SET PATH = "DB2INST1","SYSIBM","SYSFUN","SYSPROC","SYSIBMADM"
3. db2 select sum(edlevel) from emp -- this returns an INT
4. db2 select db2inst1.sum(edlevel) from emp -- this returns a BIGINT

I need number 3 above to return a BIGINT. I cannot have the query tool
customize the SQL in any way.

On Sep 13, 2:39*am, "Martin" <n... (AT) none (DOT) com> wrote:
(I don't know DB2 supplied emp table.)

I guessed that data type of edlevel in emp is SMALLINT.
Another my guess was that you created the new function as
DB2INST1.SUM( INT ).
If so, best fit for sum(edlevel) in number 3 above is sysibm.sum.
Because sysibm.sum accept SMALLINT directly, while db2inst1.sum need
to convert SMALLINT to INT.

If all my guess looks appropriate, you can confirm my guess by using
sum( INT(edlevel) ) in number 3.
Or change data type of edlevel in emp to INT.