ETL integration not working

[DCIM_Support]

Hello

I'm trying to configure a ETL process in DCO. Looking at the logs in the server (etl.log) it seems to work fine, and the information is inserted correctly at the staging database. But when I go to the ETL Integration in the DCO, not appear any element. Looking into the error.log, I can see an error like this:

Caused by: java.lang.RuntimeException: Unable to add power path graph deletion handlers at com.apc.powerpath.server.SetupPowerPathGraphDeletionHandlersRequestHook.requestInitializing(SetupPowerPathGraphDeletionHandlersRequestHook.java:59) at com.apc.config.hibersync.server.support.RequestContext.initializeRequestHooks(RequestContext.java:221) ... 174 more Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection

But my integration is not about the power path.

It seems that some previous process about Power path occurs before the process of import of the information of my staging database to the DCO database, and it fails, therfore, all the processes after also fails.

Any suggestion about how to solve it?

(CID:126162670)

[DCIM_Support]

Hi Carlos

I hope I can be of assistamce

How are you adding the rows to the ETL Import DB table/s?  

http://sxwhelpcenter.ecostruxureit.com/display/DCIMDEVELOPER/ETL+-+Extract+Transform+Load#ETL-Extrac...

Which tables?    Have you checked the data in the tables?  Especially the state_flag column?  

ETL Import Database

How often are you refreshing the External Connection?   Manually?   

Regards

Ed

(CID:126162855)

[DCIM_Support]

Hello Ed, 

I'm using the configuration_item, network_connection and port_configuration tables, the state flag column remains in the "new" state after the import process, I also tried it in other DCO instance and there it works well. 

The process is configured to trigger once a day, but I have tried to start it manually and the error also occurs.

Looking at the ETL logs looks like the process was done, but looking at the error log, it seems like some problems happened with the database connection.

It's possible that all the importing process is scheduled in a batch process, and if one of them fails, all the following process also fails? Can I have access to the DCO database to see if some process about the power path was in a "deadlock" and it make all the other processes fail?

Regards.

(CID:126164165)

[DCIM_Support]

Any suggestion?

(CID:126166537)

[DCIM_Support]

Hi Carlos, apologies for my delayed response.  I understand you can add new rows to these tables but after the import process has completed, the state flag is still set to "new".  Are you setting the state_flag using upper case "NEW"?  I recall setting the state_flag to "new" (lower case) didnt work?  I could be mistaken but please try this just to be sure.  You can access the ETL Import DB using the command line or a GUI DB admin tool like PG Admin III. 

I recall the overall import log messages are in server.log and there is more detail in etl.log.  Not sure about this one but you will be able to tell by manually starting the import then checking which log files change,

Cheers

Ed

(CID:126167333)

[DCIM_Support]

Hello ed,

I checked the logs again and I found that all the connection errors begin when the process try to update a change ticket and founds a duplicate key, but my integration doesn't use the change ticket table. 

the error is the following:

2017-10-23 08:14:49,817 WARN [org.hibernate.util.JDBCExceptionReporter] (QuartzSharedThreadPool-0) SQL Error: 0, SQLState: 23503 2017-10-23 08:14:49,817 ERROR [org.hibernate.util.JDBCExceptionReporter] (QuartzSharedThreadPool-0) Batch entry 0 delete from change_ticket_status where change_ticket_status_id=0 and external_system_id='82bd5cd2-8c43-46ff-b237-b7db7971d5fa' was aborted. Call getNextException to see the cause. 2017-10-23 08:14:49,817 WARN [org.hibernate.util.JDBCExceptionReporter] (QuartzSharedThreadPool-0) SQL Error: 0, SQLState: 23503 2017-10-23 08:14:49,817 ERROR [org.hibernate.util.JDBCExceptionReporter] (QuartzSharedThreadPool-0) ERROR: update or delete on table "change_ticket_status" violates foreign key constraint "fk92c09359e36d603b" on table "etl_change_ticket"

It's possible to get the user/password of the isxo database in order to look at this tables and see if can I locate the error?

Regards.

(CID:126167469)

[DCIM_Support]

Carlos

do u still have your issue?

Do you have any StruxureWare Change licenses installed?  If so is it I ntegrated with a third party app?  

Are you able to login to a DCO server shell?  Or do you have a graphing cal RDBMS admin tool like PgadminIII?  Email me at etarento at gmaildotcom for some more ideas 

regards

Ed

(CID:126170412)

[DCIM_Support · Accepted solution]

Hello ed

As I suspected, the problem was that some information about another

ETL integration, that was removed time ago, remained in the database and it provoques that some delete procedures about change ticket fails, and it becomes in a cascade failure to all the ETL processes executed after that. 

I think that is a product error, and you must take it into account for further releases.

Thanks, best regards.

(CID:126174649)

[DCIM_Support]

This question is closed for comments. You're welcome to start a new topic if you have further comments on this issue.