View previous topic :: View next topic
|
Author |
Message |
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 |
|
|
Stefan
Active User
Joined: 12 Jan 2006 Posts: 110 Location: Germany
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
I tried with over 2000 values in IN predicate and the query went fine.
You need to carefully formulate the query. |
|
Back to top |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
@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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
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 ! |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 ! |
|
|
Back to top |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
preethan Warnings : 1 New User
Joined: 15 Dec 2008 Posts: 33 Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
It doesnot mean that i failed to fix the error. I found an alternate way
|
so much BS. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
We're not heading anywhere, I'm locking this topic. |
|
Back to top |
|
|
|