Using SQL to insert records into a table
I have a table with a period in the table name, i.e. AB.CDE that I need to insert some records into. When I query the table (Select * from "AB.CDE") it works fine. When I try to insert records (insert into "AB.CDE"), however, I get a file not found message. Does anybody know why this is happening?
Software/Hardware used:
iSeries, interactive SQL
Software/Hardware used:
iSeries, interactive SQL
ASKED:
November 11, 2010 6:05 PM
UPDATED:
November 19, 2010 5:19 PM
Answer Wiki:
Here is what I did and it worked
crtpf qtemp/ab.cde rcdlen(20)
INSERT INTO QTEMP/"AB.CDE" VALUES('testdata')
*
If you are still having a problem, we will need to see your code.
Here is my code:
INSERT INTO XLIB/"MD.ALTAD" (AMD#,ASEQ#,ASTR1,ACITY,ASTATE,AZIP)
(SELECT MDMD#,99,MDSTR,MDCITY,MDST,MDZIP FROM MDMSTR)
When I run it, I get message "MD.ALTAD" IN XLIB TYPE *FILE NOT FOUND
To see all answers submitted to the Answer Wiki: View Answer History.
Compile with commit(*None) or
In RPG,
C/EXEC SQL SET OPTION commit=*none, closqlcsr=*endmod
C/END-EXEC
The idea that you described will work fine as long as (1) the INSERT statement is correct and (2) the environment allows the action.
But you only showed a fragment of your INSERT, so we can’t tell if it was written correctly. We don’t know anything about the file AB.CDE, so we don’t know if it even allows updates. We don’t know if you are authorized to update AB.CDE. We don’t know if AB.CDE is journaled nor what your session attributes were. We might need to know the OS version/release as well as the DB2 group PTF level.
We don’t know what error message you received. Most especially, we don’t know what the message identifier was; so we can’t check any documentation. And we don’t know if any related messages were sent before the error that might have signaled a condition that resulted in the error.
Please provide more detail. It’s almost certain that an answer can be provided.
Tom
Compile with…
But the software tag says “interactive SQL”.
Tom
XLIB/”MD.ALTAD” might need to be “XLIB”/”MD.ALTAD” or “XLIB”.”MD.ALTAD”
phil
XLIB/”MD.ALTAD” might need to be…
I wouldn’t expect that to need a change in punctuation because the ‘not found’ message seems to indicate that it was parsed correctly. A punctuation change should be indicated by a SQL syntax error that pointed to a particular token. It wouldn’t get far enough actually to look for the object.
I would want to see the a sample of at least a couple of the fields listed from a DSPFFD XLIB/MD.ALTAD command. First, the command shouldn’t error out with the same ‘not found’ error. Second, the existence of a couple fields would be verified to show that it was a reasonable target for an INSERT statement.
Changing the syntax is a reasonable thing to try. I just wouldn’t expect it to make a difference. But it might.
Tom
GaryF
How did you resolve this issue?
Your answer might help the next person with a similar question..
Phil
Thanks to all of you for your comments and suggestions. Unfortunately, we couldn’t get it to work, so we ended up renaming the file to get rid of the period.