Run a SQL query on a DB2 file with a dot "." in the name?

db2 ibm-midrange
wkm picture wkm · Jun 25, 2012 · Viewed 10.5k times · Source

I have a DB2 file with a name like "my.test". The DB2 filename contains a dot '.'

If I try and run the following query via strsql:

select * from my.test

I receive the following error:

Token . was not valid. Valid tokens: FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

Is there a way around this? I tried surrounding it in quotes but that doesn't help.

Answer

James Allman picture James Allman · Jun 28, 2012

Double quotes are the correct way to escape the file name. The file name becomes case sensitive within quotes and must be specified in upper case.

SELECT * FROM "MY.TEST"

Note that in the IFS naming convention the "." operator is used to reference members within a file but it doesn't work with SQL.

If you need to access a file named "MY" with a member named "TEST" you would need to create an alias to query against. 

CREATE ALIAS QTEMP/MYTEST FOR MY (TEST)
SELECT * FROM QTEMP/MYTEST

Related questions

Equivalent of LIMIT for DB2

How do you do LIMIT in DB2 for iSeries? I have a table with more than 50,000 records and I want to return records 0 to 10,000, and records 10,000 to 20,000. I know in SQL you write LIMIT 0,10000 at the end of the query …

Read More
How to call a stored procedure in IBM System i Access for Windows GUI Tool

I would like to test a DB2 stored procedure running on an AS400 system. I have the IBM System i Access for Windows installed and can run SQL commands against the DB2 database. My question is: What is the syntax …

Read More
Update 1 field in a table from another field in a different table (OS400, not a 1 to 1 relationship)

Im trying to update a field in a table from another field in a different table. The table being updated will have multiple records that need updating from 1 match in the other table. Example, i have a 1 million row sales …

Read More