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

tdickerson

Hi,
I'm encountering a strange error after an upgrade has been run on a test site. Attempting to

Expand|Select|Wrap|Line Numbers

SELECT * FROM table

or even just entering

Expand|Select|Wrap|Line Numbers

SELECT *

(no table specified) returns the following error:

Expand|Select|Wrap|Line Numbers

 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

 SELECT a,b,c FROM table

--or

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.

Quote:

"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

 cd /home/db2inst1/1234_upgrade/backups

gunzip CAREBEAR.0.db2inst1.NODE0000.CATN0000.20080722175447.001.gz > \

CAREBEAR.0.db2inst1.NODE0000.CATN0000.20080722175447.001

ls

db2 connect to carebear

db2 "force application (230)" db2 list applications

db2 restore db carebear

cd

db2 connect to carebear

db2 -vf db2_1233_1234_upgrade.sql -t > scriptout.txt

vi scriptout.txt

db2 -vf stares_change.sql -t

db2 "import from stares.csv of del insert into stares" db2 -vf stares_setup.sql -t > output.txt

vi output.txt

db2 -vf dropconst.sql -t

db2 -vf restoreconst.sql -t

db2 "delete from beastly" db2 "import from beastly.ixf of ixf insert into beastly" db2 list active databases

db2 list database directory

exit

Topic also posted here: http://www.ibm.com/developerworks/fo...20379&tstart=0

docdiesel

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

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

 shellprompt# db2 "SELECT  * FROM my.table"
 shellprompt# db2  SELECT \* FROM my.table
 

Regards,

Bernd

tdickerson

Quote:

Originally Posted by docdiesel

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

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

 shellprompt# db2 "SELECT  * FROM my.table"
 shellprompt# db2  SELECT \* FROM my.table
 

Regards,

Bernd

Thanks for the suggestion, but this also occurs within the DB2 CLP, and with quotes.

docdiesel

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

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

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

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

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

Similar DB2 Database bytes

/bytes/development

/bytes/it

/bytes/about