Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Windowed Function SQLCODE -104

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 Windowed Function SQLCODE -104
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

Site Director


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

PostPosted: Tue Feb 01, 2011 10:40 am    Post subject:
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    Post subject:
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():

http://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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Feb 02, 2011 9:39 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Feb 02, 2011 10:38 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Feb 02, 2011 10:50 am    Post subject: Reply to: DB2 Windowed Function SQLCODE -104
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    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Feb 02, 2011 1:58 pm    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Strange EXEC function call in z/VM Willy Jensen CLIST & REXX 3 Wed Oct 05, 2016 2:07 pm
No new posts Replace function abdulrafi COBOL Programming 13 Mon Sep 12, 2016 10:12 am
No new posts Which function does the Ampersand (&a... Andi1982 PL/I & Assembler 3 Wed Aug 24, 2016 2:05 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us