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
 

 

SQL code -104
Goto page 1, 2  Next
 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Wed Jul 04, 2012 11:19 am    Post subject: SQL code -104
Reply with quote

Hi all,

I am trying to retrieve some 1300 rows from a table. The problem here is all the 1300 has a unique primary key. Say all 1300 has a unique emp no. I have to write down a sql query having all the 1300 emp no s in the query.

say for example
select * from emptab where empno in (1,2,3,4,5....,1300)

I am getting the following error

SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>".
SOME SYMBOLS THAT MIGHT BE LEGAL ARE: + - NEXTVAL PREVVAL ROW
( USER CURRENT CURRENT_SCHE

When i remove some 1000 rows from the sql statement i am able to retrieve the data. so there is no problem in the syntax. I guess the error is since the SQL query has exceeded its character limit. (The query is very big and it has around 1300 rows). I just wanted to confirm is this due to this error ?

I am thinking about splitting the query into two and do a UNION, but i want to know is there any way to acheive this without UNION?
Back to top
View user's profile Send private message

Stefan

Active User


Joined: 12 Jan 2006
Posts: 110
Location: Germany

PostPosted: Wed Jul 04, 2012 11:44 am    Post subject:
Reply with quote

If it's a continuously ascending sequence of values in column empno, then why not writing
Code:
select * from emptab where empno between 0 and 1300
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Wed Jul 04, 2012 11:49 am    Post subject:
Reply with quote

I tried with over 2000 values in IN predicate and the query went fine.

You need to carefully formulate the query.
Back to top
View user's profile Send private message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Wed Jul 04, 2012 12:15 pm    Post subject:
Reply with quote

@stefan it is not continuos i cant use that way.

@Gnana Sekaran Actuallymy qery will be like

select * from sometable where acct_id in

(123567890321,
012943432322,
439473748933,
....
435342643763
)


The acct id is of 13 characters each. That is the prb here
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Jul 04, 2012 1:01 pm    Post subject:
Reply with quote

preethan wrote:
The acct id is of 13 characters each. That is the prb here
How can this be a problem?

And when you say
Quote:
When i remove some 1000 rows from the sql statement i am able to retrieve the data.
does that mean, starting from 1001 you start getting problems. I'd guess you copy all 1,300 entries manually, yes - there is a chance of manual error, No - what is the source from where you populate all these 1,300 entries?

In short, "ILLEGAL SYMBOL" is provided by the user to DB2, so user has to investigate in.
Back to top
View user's profile Send private message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Wed Jul 04, 2012 1:12 pm    Post subject:
Reply with quote

Anuj Dhawan wrote:
preethan wrote:
The acct id is of 13 characters each. That is the prb here
How can this be a problem?


I guess this might cause a space problem in the query.

Quote:


And when you say
Quote:
When i remove some 1000 rows from the sql statement i am able to retrieve the data.
does that mean, starting from 1001 you start getting problems. I'd guess you copy all 1,300 entries manually, yes - there is a chance of manual error, No - what is the source from where you populate all these 1,300 entries?

In short, "ILLEGAL SYMBOL" is provided by the user to DB2, so user has to investigate in.


I have created a small tool that creates the query. I ve double checked the syntax, its correct.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Wed Jul 04, 2012 8:03 pm    Post subject:
Reply with quote

Where do these 1300 account numbers come from ?
- Are they constant values?
- Do you get them from a file
- Do they come from another table ?

To run your query, do you use ?
- a cobol program
- a rexx program
- spufi
- qmf
- DSNTIAUL
- something else

Come on, give us something useful ! icon_evil.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Jul 04, 2012 9:11 pm    Post subject:
Reply with quote

when db2 says there is a syntax error,
there is a syntax error............................

Quote:
I ve double checked the syntax

the syntax is correct, only if db2 says it is.

as far as size is concerned,
13k for the IN-List is still only 1/3 of what was available in db2 vsn7.
db2 vsn9.1 the size of sql is in the megabytes.

since an IN-List is sorted, if you pre-sort your acct no, it will save some query time.

in processing an IN-List, db2 does a join.
why not unload all the acct no from lowest-range to highest-range of you IN-List?
then let DFSORT/SYNCSORT do the joinkeys.
that would probably be faster than a 1000 item IN-List process.
Back to top
View user's profile Send private message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Wed Jul 04, 2012 9:51 pm    Post subject:
Reply with quote

To answer your question , it comes from a file. I extracted the 1300 accounts from it based on my requirement.

Marso wrote:
Where do these 1300 account numbers come from ?
- Are they constant values?
- Do you get them from a file
- Do they come from another table ?


I tried spufi and DSNTIAUL. I got the same problem in both. Honestly I don't think alll these datas are required. Db2 works in the same way in all the ways u mentioned rite? . I got the same sqlcode everywhere

Quote:

To run your query, do you use ?
- a cobol program
- a rexx program
- spufi
- qmf
- DSNTIAUL
- something else

Come on, give us something useful ! icon_evil.gif
Back to top
View user's profile Send private message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Wed Jul 04, 2012 9:58 pm    Post subject:
Reply with quote

I dont know how to explain my situation to you all.

My Query is in the following structure

Select * from sometable
where acctid in (
'xxxxxxxxxxxxx',
'yyyyyyyyyyyyy',
'zzzzzzzzzzzzzzz',
..........,
....
....
--(1300 accts, so 1300 lines comes here) !!
...
....
'aaaaaaaaaaaaa')

Do you see something wrong in the syntax here? If at all if there is a syntax problem there i would have got a different sqlcode. It is a simple query but the content is very big.

dbzTHEdinosauer wrote:
when db2 says there is a syntax error,
there is a syntax error............................

Quote:
I ve double checked the syntax

the syntax is correct, only if db2 says it is.

as far as size is concerned,
13k for the IN-List is still only 1/3 of what was available in db2 vsn7.
db2 vsn9.1 the size of sql is in the megabytes.

since an IN-List is sorted, if you pre-sort your acct no, it will save some query time.

in processing an IN-List, db2 does a join.
why not unload all the acct no from lowest-range to highest-range of you IN-List?
then let DFSORT/SYNCSORT do the joinkeys.
that would probably be faster than a 1000 item IN-List process.
Back to top
View user's profile Send private message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Wed Jul 04, 2012 10:00 pm    Post subject:
Reply with quote

I have loaded all the accounts into a table I have created and i have acheived what i wanted to do with the help of joins.

But still I would like to know if there is a way to solve this problem. Thanks all for your help !! Keep posted!
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Jul 04, 2012 10:45 pm    Post subject:
Reply with quote

your inability to solve the problem,
was due to your inability to find the syntax error.

we are glad you were able to solve your problem,
this thread was becoming veryyyyyyyyyyy boring.
Back to top
View user's profile Send private message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Thu Jul 05, 2012 7:53 am    Post subject:
Reply with quote

Be in my shoes and talk about my ability. Its about limitations of db2.

dbzTHEdinosauer wrote:
your inability to solve the problem,
was due to your inability to find the syntax error.

we are glad you were able to solve your problem,
this thread was becoming veryyyyyyyyyyy boring.
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: Thu Jul 05, 2012 8:34 am    Post subject:
Reply with quote

Hello,

Quote:
Be in my shoes and talk about my ability. Its about limitations of db2.
Unfortunately it does NOT appear to be due to any db2 limitation. . . If there is some limit being reached, it was probably put there when your system was installed/upgraded.

As was previously mentioned:
Quote:
I tried with over 2000 values in IN predicate and the query went fine.

You need to carefully formulate the query.

Even if you are not ready to believe there is a syntax problem, it appears that there IS one or more problem(s).

How did you place the 1300 values into the query? Might there be something non-displayable in the query? Might there be sequence numbers that are invalid to sql?

PS. Everyone here has been in your shoes. The lucky ones of us have been there many times as we have had the opportunity to work on many new "things" - each one a learning experience.
Back to top
View user's profile Send private message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Thu Jul 05, 2012 9:00 am    Post subject:
Reply with quote

Since everyone is really doubtful about my query, i am pasting the rexx code which is used to generate it.
Code:

K=1
DROP QUERYACCT.
/*QUERYACCT will have the genrated query */
QUERYACCT.1 = 'SELECT * FROM 'QUERYNODE'.'TABLNAME' WHERE'
IF TABLNAME <> 'table1' THEN
   QUERYACCT.2 = 'CUST_ACCT_ID IN ('
ELSE
   QUERYACCT.2 = 'OLD_CUST_ACCT_ID IN ('
DO I=3 TO ACCOUNTS.0 + 2
/*Accounts is read from an input file. All customer accounts are stored in the variable ACCOUNTS. */
   IF I = ACCOUNTS.0 + 2 THEN
      QUERYACCT.I = "'"|| ACCOUNTS.K ||"')"
   ELSE
      QUERYACCT.I = "'"|| ACCOUNTS.K ||"',"
   K = K + 1
END
QUERYACCT.I = "FOR FETCH ONLY WITH UR"
SYSINMEM = TABLNAME
FILE = QUERYACCT
CALL WRITECNTL


The rexx code will generate a query i mentioned in one of my previous posts. The query generated has always been syntatically correct. It worked if the accounts were less than 250. Since the number is 1300, the query failed, that is the reason i kept stressing that the query looks correct. Hope you all had a clear idea on what issue i am facing.

dick scherrer wrote:
Hello,

Quote:
Be in my shoes and talk about my ability. Its about limitations of db2.
Unfortunately it does NOT appear to be due to any db2 limitation. . . If there is some limit being reached, it was probably put there when your system was installed/upgraded.

As was previously mentioned:
Quote:
I tried with over 2000 values in IN predicate and the query went fine.

You need to carefully formulate the query.

Even if you are not ready to believe there is a syntax problem, it appears that there IS one or more problem(s).

How did you place the 1300 values into the query? Might there be something non-displayable in the query? Might there be sequence numbers that are invalid to sql?

PS. Everyone here has been in your shoes. The lucky ones of us have been there many times as we have had the opportunity to work on many new "things" - each one a learning experience.
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: Thu Jul 05, 2012 9:23 am    Post subject:
Reply with quote

Hello,

I suspect the "generator" query has worked.

Now there is something in the query that is presented to db2 that has something wrong. Suggest you spend time looking at the actual query that is being run rather than the code that generates it. . .

What has changed on the system since the last time this has worked?
Back to top
View user's profile Send private message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Thu Jul 05, 2012 10:01 am    Post subject:
Reply with quote

Hello dick scherrer,

Even now it is working if i cut off some large number of accounts from the query. The query generated has 1300 accounts. When I deleted 900 accounts from the query, it actually worked. Nothing changed since when it worked.

dick scherrer wrote:
Hello,

I suspect the "generator" query has worked.

Now there is something in the query that is presented to db2 that has something wrong. Suggest you spend time looking at the actual query that is being run rather than the code that generates it. . .

What has changed on the system since the last time this has worked?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jul 05, 2012 1:21 pm    Post subject:
Reply with quote

With what you've posted so far it's tough via a Forum to help you. The only option I can think of is - try different iterations.

First you said, it worked with first 1000 records, lately you said it worked after deleting 900 records. That itself is not consistent.

Suggest you try differnt iterations, like - if it works for first 1000 records, good - go for 1100 in next iteration, if it works, try with next 100 records and so on until you hit a particular group of records where the error starts showing and then narrow it down to the record in error.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Thu Jul 05, 2012 1:39 pm    Post subject:
Reply with quote

Please stop repeating everyone's posts. It gets in the way (and takes up someone else's storage, bandwidth and time.

Having gotten that out of the way...
What is in the variable QUERYACCT that is being moved to variable FILE? Note that QUERYACCT is NOT the stem QUERYACCT., what is the length of the generated query? If you are trying to write this to a file what is the LRECL and RECFM of the file? What is the maximum length of an SQL statement in SPUFI?
Back to top
View user's profile Send private message
preethan
Warnings : 1

New User


Joined: 15 Dec 2008
Posts: 33
Location: Chennai

PostPosted: Thu Jul 05, 2012 2:11 pm    Post subject:
Reply with quote

I wish I dint say it anywhere by mistake, it never worked with 1000 records. It worked when i had around 200 - 250 accts.

Anuj Dhawan wrote:
First you said, it worked with first 1000 records, lately you said it worked after deleting 900 records. That itself is not consistent.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 pm
This topic is locked: you cannot edit posts or make replies. Need to code a Rexx PGM sundaram.naveen CLIST & REXX 18 Thu Oct 06, 2016 6:45 pm
No new posts Need Help in understanding what is th... mainakdalal PL/I & Assembler 9 Mon Oct 03, 2016 8:03 pm


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