Handling tables with reserved words/keywords in Informatica

[sutaramit]

Hi All,
I am currently facing issues where table names are keywords/reserved words.

Database : DB2
Table Names : User, Template.

I have overriden the select query in "Source Qualifier" so while selecting data from source. Till this step everthing works fine while running the workflow.

But when we try to load the data in Target Informatica throws following error.

1. [IBM][CLI Driver][DB2/NT] SQL0204N "DB2ADMIN.USER" is an undefined name SQLSTATE = 42704

I checked in target db, user table is present under DB2ADMIN schema.

2. I tried adding a Update Strategy Transformation in the mapping, so that we can override the SQL in target. After adding the manual query also Informatica is not able to load data.
Following is the error we are facing after adding the overridden query.

Thread: WRITER_1_*_1
Process ID: 32415
Message Code: WRT_8118
Message:
ERROR: Target table [user] does not allow UPDATE
Row # [1] in bad file

3. Template table is a system table as well and when we try to add a custom query in Source Qualifier we are getting "Table space access is not allowed" error, following are the details.

Severity: ERROR
Timestamp: 1/20/2011 10:33:14 AM
Node: node01_PODETLSAPP01
Thread: READER_1_1_1
Process ID: 12348
Message Code: RR_4035
Message: SQL Error [
Database driver error...
Function Name : Fetch
SQL Stmt : SELECT DB2ADMIN."TEMPLATE".CREATE_TIME, DB2ADMIN."TEMPLATE".CLSID, DB2ADMIN."TEMPLATE".UPDATE_USER_ID, DB2ADMIN."TEMPLATE".UPDATE_TIME, DB2ADMIN."TEMPLATE".CREATE_USER_ID, DB2ADMIN."TEMPLATE".ELEMENT_NAME, DB2ADMIN."TEMPLATE".XML
FROM
DB2ADMIN."TEMPLATE"
Native error code = -290
DB2 Fatal Error
[IBM][CLI Driver][DB2/NT] SQL0290N Table space access is not allowed. SQLSTATE=55039
sqlstate = 55039
].

Please help. As i am stuck because of these 3 issues from 3-4 days.

Thanks
Amit

 

[rasETL]

sutaramit...

I strongly recommend you change the names of your Tables. Using/Naming any object with Reserved/Key words is an extremely bad idea, not only in Informatica, but for any System Development.