|
View previous topic :: View next topic
|
| Author |
Message |
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
|
|
Hi all,
I am trying to run the below SQL from SPUFI. I am getting SQLCODE of -104 (invalid chars) in the 5th line.
My requirement is that I need to get account level information (DOB, DEPT, SALARY) along with sum of salary on Department.
| Code: |
SELECT ACCOUNT,
DOB_DATE,
DEPARTMENT,
SALARY,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT) AS 'DEPT_SAL'
FROM TSOPPPP.TAB_EMP; |
Correct me if I am missing anything in the SQL.
Thanks in advance. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Additional diagnostic information was shown. Is there some reason you have not posted it here?
Did you read about the -104 sqlcode in the Messages and Codes documentation?
In which db2/sql documentation did you read about OVER? |
|
| Back to top |
|
|
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
|
|
Hi Dick,
The error information has been provided below.
| Quote: |
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: , FROM INTO
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 315 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
X'0000013B' X'000001F6' SQL DIAGNOSTIC INFORMATION |
SQL CODE -104 is related to "ILLEGAL SYMBOL" in the SQL. I have seen the documents to find the issue, but without any luck.
The IBM link for Over():
www.ibm.com/developerworks/data/library/techarticle/dm-0401kuznetsov/index.html
I tried the same query above in DB2 v9 Express in my local Windows Desktop. It worked!!!!. But not in Z/OS. 
Thanks again for your help. |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
DB2 for z/OS do not contain OLAP facilities like UDB for smaller computer systems has.
That's why OVER doesn't work on z/OS DB2. |
|
| Back to top |
|
|
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
|
|
| Kjeld wrote: |
DB2 for z/OS do not contain OLAP facilities like UDB for smaller computer systems has.
That's why OVER doesn't work on z/OS DB2. |
Thanks Kjeld for your response.
Does that mean we can not use aggregate functions with OVER() in z/OS?
I am able to use "ROW_NUMBER() OVER(ORDER BY col_name)" in z/OS for getting the Row Number in a COBOL module involving cursor.
Thanks,
Purusothaman Ramanujam |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Kjeld,
have fun! |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
| Certain OLAP specifications are allowed, however, and ROW_NUMBER is one of them. Do a search in SQL reference manual for your version of DB2 for 'OLAP'. |
|
| Back to top |
|
|
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
|
|
Thanks again Kjeld.
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.
So I assuse DB2 for z/OS supports OVER() with PARTITION BY clause.
Kindly correct me if I am wrong. Thanks again.
Purusothaman Ramanujam |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| That's why OVER doesn't work on z/OS DB2. |
What is not clear about this. . . 
| Quote: |
So I assuse DB2 for z/OS supports OVER() with PARTITION BY clause.
Kindly correct me if I am wrong |
Yes, it looks like you are wrong. |
|
| Back to top |
|
|
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
|
|
Hi Dick,
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.
Thanks a ton.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
As i understand this, db2 v9 for z/os supports partitioning. It does not support OVER.
I may be misunderstanding something. . .
If you cannot find OVER in the z/os documentation (i tried and was not able to find it) it is most likely not available. |
|
| Back to top |
|
|
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I think that aggregate functions like AVG and SUM can not be used with OVER() and Partition clause in Z/os. |
Yes, i believe this is true.
AVG and SUM may be used in z/os (we use these in v8 for z/os so i'm sure they work in v9).
Good luck 
d |
|
| Back to top |
|
|
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
|
|
| Thanks for all the help. Let me try to find a alternative solution. |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
You got my point, good.
The only idea that comes to my mind is to have an ODBC connection to the z/OS tables from a server UDB environment that supports full OLAP functionality. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I'm not saying it will perform well
| Code: |
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; |
|
|
| Back to top |
|
|
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
|
|
In my shop, distributed systems get the DB2 data via a Stored Procedure executed via "DB2 Connect" and that's the only approved connectivity mechanics.
so, ODBC will not be an option available to my Distributed guy.
Anyhow thanks for your idea. |
|
| Back to top |
|
|
purusothaman
New User
Joined: 17 Feb 2007
Posts: 39
Location: Chennai
|
|
|
|
| GuyC wrote: |
I'm not saying it will perform well
| Code: |
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.
Thanks for your help. |
|
| Back to top |
|
|
|
|
