I'm getting the following error when loading data from DB2 staging tables to Oracle Target Database. It was working properly in version 7 of DB2 Data Warehouse Center. Now I have version DB2 v8.1.7664.
DWC07356E An agent's processing of a command of type
"fetchAndInsertX" failed for edition "95,347" of step "Insert
PCIA Inventory Transactions into Oracle".
[IBM][CLI Driver][DB2/NT] SQL30090N Operation invalid for
application execution environment. Reason code = "22".
RC = 7356 RC2 = 8535
I would kike to know, where within the tool I can find Message Reference to see what the response is for RC2 = 8535.
SQL30090N Operation invalid for application execution environment. Reason
code = reason-code Explanation: The operation is invalid for the application execution environment. For example, an operation might be invalid for applications that have special restrictions on statements or APIs - Applications such as those that operate in an XA Distributed Transaction Processing environment, such as CICS; those that operate with CONNECT type 2 connection settings; or those that use federated system functionality to update multiple heterogeneous data sources. The operation was rejected.
The user response for code 22 should be:
22 A federated insert, update, or delete
operation is invalid in an application
execution environment where a
1 SAVEPOINT is in effect
2 scrollable cursor is used
3 target view contains multiple tables or nicknames
4 trigger is in effect.
I created a new nickname. How do I see it or import it to my Data Warehouse Center Session?
Do you think I'll be able to insert records to the target table through that federated view?
After we upgraded to the new version (8.) the steps we had loading data to fact tables came up with the errors. On the other hand, dimension and junk-reference dimensions are being loaded successfully. I wonder if the metadata after upgrading was corrupted in some extent.
Still figuring out. I'll let you know my findings...any feedback is appreciated.
Please write your operating system type, version and fixpack and db2 version and fixpack. You get get more info about that at Must Read before.
If you get any error message please post the error number and error description. Also post short description what you have done to get error.
It is little bit strange you get error when inserting into fact table nickname and no error when inserting into dimension table nickname. Look at the source database. What is the source database type and version (Is it DB2?). Look at the source database messages. Is there any error related to "log full"? This could be the problem. Please check and reply to me.
OS is Windows NT version 2000 fixpack 4.
DB2 Version is Level v22.214.171.1244
The target database is Oracle (8.1.7)
In Oracle we see the alert.log has errors with regard to the redo log (checkpoint errors, etc.)
Indeed, the fact tables are the ones not being loaded with the "fetch and insert" error. I can see samples of the data in Oracle, through the nickname. The problem is when inserting new records to these specific tables.
When we were using the DB2 version 7 all ETL process were running successfully. This problem started after upgrading to DB2 Level v126.96.36.1994. All the Extraction and Transformatoin part of the process is ok...loading to oracle (insert, update) is being our issue for the facts tables.
I even tried to load only 200 records to the target table and the same error came up.
Where should I be able to find the secondary return code recommended action? No I'm getting a different error with the loading to a dimension table. DWC07006E with RC2 = 3142 but don't know were to find the recommended action suggested for these codes.
DWC03142E "<object-type>" object: "<object-name>" was not found
in the Data Warehouse Center control database.
Explanation: If you were trying to open a Data Warehouse Center
from a list, another user might have deleted the entity you were
trying to open. Otherwise, an internal Data Warehouse Center
User Response: Refresh the list and open the entity again. If
the same error occurs, record all details of this error message
and contact IBM Software Support with the information.
When did error happened: in v7 everything was ok, in v8 the error? There was some change in warehouse code in v8. I have similar problems accessing db2 on hosts. On db2 host must be installed store procedure server and one specific store procedure must be run. Maybe you have similar problem. I am not familar with Oracle, so I don't know what is exact problem.