468,457 Members | 1,763 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,457 developers. It's quick & easy.

SQL 103 - "Numeric literal invalid" on SELECT *, but not SELECT a,b,c

Hi,
I'm encountering a strange error after an upgrade has been run on a test site. Attempting to
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM table
or even just entering
Expand|Select|Wrap|Line Numbers
  1. SELECT *
(no table specified) returns the following error:
Expand|Select|Wrap|Line Numbers
  1. SQL0103N The numeric literal "1234_upgrade" is not valid. SQLSTATE=42604
The only place that I could see that coming from is the name of the folder the database was backed up to before the upgrade. I did a search within files of the upgrade scripts, and then of the entire DB2 user's directory (where the scripts, backups and DB2 data files all reside), and the string "1234_upgrade" only appears in the db2rhist files, and .bash_history in the commands used to backup the database.

I can still select individual columns just fine, such as
Expand|Select|Wrap|Line Numbers
  1. SELECT a,b,c FROM table
  2. --or
  3. SELECT DISTINCT a FROM table
Most or all of our queries explicitly specify the columns (as in the example above), so the test site still works. However, I'd like to resolve this issue, as it's quite the anomaly.

I believe that somewhere DB2 is picking up on the "1234", and somehow classifying it as numeric, then encountering the "_upgrade", but still treating it as numeric and throwing the error. I've looked at all the update scripts, and they consist of ALTER, INSERT, UPDATE and IMPORT statements - nothing exotic.

The DB2 manual provides little insight, as the error messages don't give me any hints as to where the problem might be.

"User Response:

Correct the invalid numeric literal. Federated system users, if the error occurred in a pass-through session, determine what data source is causing the error (see the problem determination guide for the failing data sources). Examine the SQL dialect for that data source to determine which literal representation rule has been violated, and adjust the failing statement as needed."
Thank you for your time and assistance,
-T. Dickerson

Following is the (cleansed) bash history of the commands that were used:
Expand|Select|Wrap|Line Numbers
  1. cd /home/db2inst1/1234_upgrade/backups
  2. gunzip CAREBEAR.0.db2inst1.NODE0000.CATN0000.20080722175447.001.gz > \
  3. CAREBEAR.0.db2inst1.NODE0000.CATN0000.20080722175447.001
  4. ls
  5. db2 connect to carebear
  6. db2 "force application (230)" db2 list applications
  7. db2 restore db carebear
  8. cd
  9. db2 connect to carebear
  10. db2 -vf db2_1233_1234_upgrade.sql -t > scriptout.txt
  11. vi scriptout.txt
  12. db2 -vf stares_change.sql -t
  13. db2 "import from stares.csv of del insert into stares" db2 -vf stares_setup.sql -t > output.txt
  14. vi output.txt
  15. db2 -vf dropconst.sql -t
  16. db2 -vf restoreconst.sql -t
  17. db2 "delete from beastly" db2 "import from beastly.ixf of ixf insert into beastly" db2 list active databases
  18. db2 list database directory
  19. exit
Topic also posted here: http://www.ibm.com/developerworks/fo...20379&tstart=0
Aug 6 '08 #1
3 15693
docdiesel
297 Expert 100+
Hi,

as far as I can see you're working from within a shell. Seems to me you tried the SELECT without using double quotes:

Expand|Select|Wrap|Line Numbers
  1. shellprompt# db2 SELECT * FROM my.table
In this case the shell is interpreting the asterisk * and replaces it with the names of the directories and files found in the current directory. Add some double quotes to your statement or escape the asterisk, so that the shell is told to pass the * through to the db2 binary:
Expand|Select|Wrap|Line Numbers
  1. shellprompt# db2 "SELECT  * FROM my.table"
  2. shellprompt# db2  SELECT \* FROM my.table
Regards,

Bernd
Aug 7 '08 #2
Hi,

as far as I can see you're working from within a shell. Seems to me you tried the SELECT without using double quotes:

Expand|Select|Wrap|Line Numbers
  1. shellprompt# db2 SELECT * FROM my.table
In this case the shell is interpreting the asterisk * and replaces it with the names of the directories and files found in the current directory. Add some double quotes to your statement or escape the asterisk, so that the shell is told to pass the * through to the db2 binary:
Expand|Select|Wrap|Line Numbers
  1. shellprompt# db2 "SELECT  * FROM my.table"
  2. shellprompt# db2  SELECT \* FROM my.table
Regards,

Bernd
Thanks for the suggestion, but this also occurs within the DB2 CLP, and with quotes.
Aug 7 '08 #3
docdiesel
297 Expert 100+
Hi,

this behaviour is quiet, hm, interesting. Did you ever check what happens if you rename the folder from 1234_upgrade to just 1234 ? Or is the folder already gone and it's name persists just in the log and history files?

Regards,

Bernd
Aug 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Pascual ataeda | last post: by
6 posts views Thread by ern | last post: by
5 posts views Thread by Grant Edwards | last post: by
1 post views Thread by subhajit12345 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.