IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 Windowed Function SQLCODE -104


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
purusothaman

New User


Joined: 17 Feb 2007
Posts: 39
Location: Chennai

PostPosted: Tue Feb 01, 2011 10:15 am
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Feb 01, 2011 10:40 am
Reply with quote

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
View user's profile Send private message
purusothaman

New User


Joined: 17 Feb 2007
Posts: 39
Location: Chennai

PostPosted: Tue Feb 01, 2011 11:19 am
Reply with quote

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. icon_sad.gif
Thanks again for your help.
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Tue Feb 01, 2011 1:53 pm
Reply with quote

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
View user's profile Send private message
purusothaman

New User


Joined: 17 Feb 2007
Posts: 39
Location: Chennai

PostPosted: Tue Feb 01, 2011 2:04 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Feb 01, 2011 2:16 pm
Reply with quote

Kjeld,

have fun!
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Tue Feb 01, 2011 2:22 pm
Reply with quote

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
View user's profile Send private message
purusothaman

New User


Joined: 17 Feb 2007
Posts: 39
Location: Chennai

PostPosted: Wed Feb 02, 2011 9:31 am
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Feb 02, 2011 9:39 am
Reply with quote

Hello,

Quote:
That's why OVER doesn't work on z/OS DB2.
What is not clear about this. . . icon_confused.gif

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
View user's profile Send private message
purusothaman

New User


Joined: 17 Feb 2007
Posts: 39
Location: Chennai

PostPosted: Wed Feb 02, 2011 10:28 am
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Feb 02, 2011 10:38 am
Reply with quote

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
View user's profile Send private message
purusothaman

New User


Joined: 17 Feb 2007
Posts: 39
Location: Chennai

PostPosted: Wed Feb 02, 2011 10:45 am
Reply with quote

Thanks again for your effort in helping me out..

In the SQL Reference Manual for DB2 9 for Z/Os, an example has been given at the end of the page in the link :

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqk10/2.22.12?ACTION=MATCHES&REQUEST=OLAP&TYPE=FUZZY&SHELF=&DT=20070125023435&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT

I think that aggregate functions like AVG and SUM can not be used with OVER() and Partition clause in Z/os.

NOTE: AVG() was working fine in IBM DB2 9 Express in my Windows XP.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Feb 02, 2011 10:50 am
Reply with quote

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 icon_smile.gif

d
Back to top
View user's profile Send private message
purusothaman

New User


Joined: 17 Feb 2007
Posts: 39
Location: Chennai

PostPosted: Wed Feb 02, 2011 11:00 am
Reply with quote

Thanks for all the help. Let me try to find a alternative solution.
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Wed Feb 02, 2011 12:45 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Feb 02, 2011 1:58 pm
Reply with quote

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
View user's profile Send private message
purusothaman

New User


Joined: 17 Feb 2007
Posts: 39
Location: Chennai

PostPosted: Wed Feb 02, 2011 2:00 pm
Reply with quote

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
View user's profile Send private message
purusothaman

New User


Joined: 17 Feb 2007
Posts: 39
Location: Chennai

PostPosted: Wed Feb 02, 2011 2:12 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Calling an Open C library function in... CICS 1
No new posts DATE2 function SYNCSORT 15
No new posts Help on PL/I jsonPutValue function PL/I & Assembler 8
No new posts how to use Tso outtrap external function All Other Mainframe Topics 8
No new posts INSYNC option with same function as I... JCL & VSAM 0
Search our Forums:

Back to Top