Help!

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.

Message is:

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".
SQLSTATE=25000
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.

Thanks,

J.C.

I did some searching and found some help for you
http://www.developer.ibm.com/tech/fa...re,00.html#q33

I just found some reference.

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?

Thanks,

J.C.

Hi,

Open DB2 Command Window and write command:
and for RC2
Hope this helps,
Grofaty

juan_carlos,

I created a new nickname. How do I see it or import it to my Data Warehouse Center Session?

You will probably need to create 'federated view'.
Sample: create federated view schema.viewname as select * from schema.nickname
Now you should be able to include 'view' in Data Warehouse Center.

Please let me know if this works!

Hope this helps,
Grofaty

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.

Kind Regards,

J.C.

Hi,

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.

Hope this helps,
Grofaty

Grofaty,

OS is Windows NT version 2000 fixpack 4.
DB2 Version is Level v8.1.7.664

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 v8.1.7.664. 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.

Kind Regards,

J.C.

Grofaty, please see below.

J.C.

Current log# 1 seq# 169919 mem# 0: G:\ORACLE\ORADATA\WHSD\REDO01.LOG
Thread 1 advanced to log sequence 169920
Current log# 2 seq# 169920 mem# 0: G:\ORACLE\ORADATA\WHSD\REDO02.LOG
Thread 1 advanced to log sequence 169921
Current log# 3 seq# 169921 mem# 0: G:\ORACLE\ORADATA\WHSD\REDO03.LOG
Thread 1 cannot allocate new log, sequence 169922
Checkpoint not complete
Current log# 3 seq# 169921 mem# 0: G:\ORACLE\ORADATA\WHSD\REDO03.LOG
Thread 1 advanced to log sequence 169922
Current log# 1 seq# 169922 mem# 0: G:\ORACLE\ORADATA\WHSD\REDO01.LOG
Thread 1 advanced to log sequence 169923
Current log# 2 seq# 169923 mem# 0: G:\ORACLE\ORADATA\WHSD\REDO02.LOG
Thread 1 advanced to log sequence 169924
Current log# 3 seq# 169924 mem# 0: G:\ORACLE\ORADATA\WHSD\REDO03.LOG
Thread 1 cannot allocate new log, sequence 169925
Checkpoint not complete

Hi,

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.

J.C.

Hi,

Open DB2 Command Window and execute:

Syntax: DWC0+ERROR_CODE+E
c:\>db2 ? DWC03142E

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
error occurred.

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.

Grofaty

Just to inform,

Apparently the metadata got corrupted during the migration to the version 8 of DB2.

After the migration to DB2 8, we also noted that the oracle target is now recognized in DW Center as a DB2 Windows DB, instead of a Federated DB.

We created a new federated db to Oracle target and some nicknames, but the problem remains.

Any hints?

J.C.

The process to load data from DB2 to Oracle is unsupported by IBM DW Center...we moved back to version 7 using data joiner...it is unsupported also but at least is running successfully.

Case is closed...I think...thanks for your help!!

J.C.