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

SQL code -104


IBM Mainframe Forums -> DB2
Post new topic   This topic is locked: you cannot edit posts or make replies.
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
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
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: 792
Location: Chennai, India

PostPosted: Wed Jul 04, 2012 11:49 am
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
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

Superior Member


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

PostPosted: Wed Jul 04, 2012 1:01 pm
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
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: 1353
Location: Israel

PostPosted: Wed Jul 04, 2012 8:03 pm
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
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
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
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
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
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
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

Moderator Emeritus


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

PostPosted: Thu Jul 05, 2012 8:34 am
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
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

Moderator Emeritus


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

PostPosted: Thu Jul 05, 2012 9:23 am
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
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

Superior Member


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

PostPosted: Thu Jul 05, 2012 1:21 pm
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: 2455
Location: Hampshire, UK

PostPosted: Thu Jul 05, 2012 1:39 pm
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
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
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 05, 2012 2:12 pm
Reply with quote

Quote:
Please stop repeating everyone's posts.


if he does not do that,
then his posts only consist of db2 must be wrong.

To take a file of account numbers (or anything),
edit the file,
shift all account numbers so that they start on a certain column,
MOVE double quotes, commas OVER all the numbers,
zero fill (pad to the left) certain columns
would take about 10 seconds,
whereas the TS has a Tool that does not work.

as i said before, this boring thread is about someone,
who has made a fundamental mistake,
is incapable of taking the time to find the 'error',
and keeps insisting that db2 is at fault.

last real restriction on db2 query size was db2 vn7.

the problem is not the phantom restrictions imposed by db2,
it is the personality of the TS.
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:30 pm
Reply with quote

I dont blame db2. You completely misunderstood me here. I doubted that db2 might have a constraint in the size of the query. I never knew that the size problem is sorted after version 7. Thanks for calling me incapabale, I very well know about my capabilities in fixing the error. I come here only if the issue is hard to sort out. It doesnot mean that i failed to fix the error. I found an alternate way
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 05, 2012 2:37 pm
Reply with quote

Quote:
It doesnot mean that i failed to fix the error. I found an alternate way


so much BS.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


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

PostPosted: Thu Jul 05, 2012 2:51 pm
Reply with quote

We're not heading anywhere, I'm locking this topic.
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. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts run rexx code with jcl CLIST & REXX 15
No new posts Compile rexx code with jcl CLIST & REXX 6
No new posts REXX code to expand copybook in a cob... CLIST & REXX 2
No new posts VSAM return code 23 - for a Random read COBOL Programming 4
No new posts Monitoring production job progress. N... JCL & VSAM 4
Search our Forums:

Back to Top