In the DB2 9.1 for Z/OS SQL Reference manual, under the section "2.22.12 OLAP-specification", I found the below description:

| PARTITION BY (partitioning-expression,...)

| Defines the partition within which the OLAP specification is applied.
| partitioning-expression is an expression that is used in defining the
| partitioning of the result table. Each column name that is referenced
| in a partitioning-expression must unambiguously reference a column of
| the result table of the subselect that contains the OLAP
| specification. A partitioning-expression cannot include a
| scalar-fullselect or any function that is not deterministic or has an
| external action.

Can you please advise what I was missing? I was not able to get the below text from the manual.

| PARTITION BY (partitioning-expression,...)

| Defines the partition within which the OLAP specification is applied.
| partitioning-expression is an expression that is used in defining the
| partitioning of the result table. Each column name that is referenced
| in a partitioning-expression must unambiguously reference a column of
| the result table of the subselect that contains the OLAP
| specification. A partitioning-expression cannot include a
| scalar-fullselect or any function that is not deterministic or has an
| external action.

SELECT a.ACCOUNT,
a.DOB_DATE,
a.DEPARTMENT,
a.SALARY,
b.DEPT_SAL
FROM TSOPPPP.TAB_EMP A
join
(SELECT DEPARTMENT, SUM(SALARY) AS 'DEPT_SAL'
FROM TSOPPPP.TAB_EMP group by DEPARTMENT) as b
on a.DEPARTMENT = b.DEPARTMENT;

I already tried for the solution using Joins. As it was having poor performance, I tried with the OLAP functions. The cursor in the Stored Procedure already involves 4 joins. So I do not want the SQL to timeout from the Distributed system.