Insert error

jshurak · Post by **jshurak** » Tue Jul 18, 2006 8:49 am

I have a job that is creating a table on an DB2 AS400. It creates the table, but bombs when trying to load the table. The error message is as follows

[DataStage][SQL Client][ODBC][IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - NM070999 in FDLIBFDR not valid for operation.

NM99 = "541490081100299713513 HAL ZZ 2012 0810 B"

urshit_1983 · Post by **urshit_1983** » Tue Jul 18, 2006 11:22 am

Which driver you are using for this ? ODBC or DB2 ?

jshurak · Post by **jshurak** » Tue Jul 18, 2006 12:29 pm

ODBC, sorry meant to mention that

DSguru2B · Post by **DSguru2B** » Tue Jul 18, 2006 12:36 pm

Do a google on SQL7008. At some places it says you need to jounal a couple of enteries and at some places it says commit more often. Investigate into it.

jshurak · Post by **jshurak** » Wed Jul 19, 2006 8:12 am

I spoke with our DB2 guy and he is very sure that journaling is not an issue. (I brought it into the conversation a few times)
Ok so I'm having problems now with authority issues. The program creates the table, then bombs on a select statement. I'm not sure why it runs a select statement, I would think it would run an insert statement. anyway the error i receive is as follows:

Project:Development (dwetl2)
Job name:SOL_Load
Event #:875
Timestamp:7/19/2006 9:59:47 AM
Event type:Fatal
User:ACSNT\W0013107
Message:
SOL_Load..AS400_Table_Creation_Stage.IDENT1: |SOL_Load..AS400_Table_Creation_Stage.DSLink1: DSD.BCIOpenW call to SQLPrepare failed.
SELECT NM99 FROM NM071999
SQLSTATE=42000, DBMS.CODE=-551
[DataStage][SQL Client][ODBC][IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0551 - Not authorized to object NM071999 in W0013107 type *FILE.|

What I don't understand is why i receive this message. The user has the proper authority and created the table, so why is it not authorized to object this table??

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jul 19, 2006 8:17 am

If creating or altering a table with referential constraints, this message (SQLCODE) can indicate that the user does not have the REFERENCES privilege to create or drop a FOREIGN KEY.

DSguru2B · Post by **DSguru2B** » Wed Jul 19, 2006 8:30 am

With your id, try to manually create a table and insert a single record via command line. See if that works?

jshurak · Post by **jshurak** » Wed Jul 19, 2006 9:47 am

This table has no referencial constraints. I was able to create the table and load a test record into it via my personal user login. I was able to create the table with the production login but received the SQL0551 error when trying to load it. I checked the permissions and the production user has every permission available. It looks like it is creating the table and giving ownership to someone else.

DSguru2B · Post by **DSguru2B** » Wed Jul 19, 2006 1:24 pm

Sounds really silly, but there is something else thats going on at the back end. Maybe you can get your dba involved and try to find out whats going on. You can also explicity give the select permissiong to the production id by the GRANT command.
GRANT SELECT ON <tablename> TO <productionid>