Write SUCCESS flag to Database After Successful DB Output

Discussions dedicated to share knowledge and experience with IBM MDM plug-in in CloverETL

listsos
Posts: 9
Joined: Fri Aug 18, 2017 5:51 pm

Write SUCCESS flag to Database After Successful DB Output

Postby listsos » Wed Dec 06, 2017 8:16 pm

I have a reporting database that is updated using the DB_OUTPUT_TABLE, using an 'Insert Into'. Records are retrieved using a MEMGET and inserted into the table using the above writer.

After the above DB_OUTPUT_TABLE writer job is finished, I would like to update another table with either a 'SUCCESS' or 'FAILURE' flag.

I have tried creating another DB_OUTPUT_TABLE job that contains the query 'UPDATE STATUS_TABLE
set STATUS = 'SUCCESS';' however, it doesn't write once the job is finished.

I am using the same metadata connector to the second DBOutputTable, not sure if that is the problem.

See attached image for a screenshot of the job. The highlighted writer is the newly added one that should be updating the status flag in STATUS_TABLE.

Thanks in advance.
Attachments
test.png
test.png (46.17 KiB) Viewed 333 times

vazquezrosariop
Posts: 131
Joined: Mon Feb 29, 2016 5:33 pm

Re: Write SUCCESS flag to Database After Successful DB Output

Postby vazquezrosariop » Thu Dec 07, 2017 6:17 pm

Hi listsos,

The output port you are utilizing to set the flag is only returns rejected records, which works well for the Failure Flag. Now to set the Success flag, I would recommend using the second output port and set a returning statement to output a record inserted. It's also imported to note that the second out will return a value regardless if the record was added. To resolve this, I would recommend filtering any null values from the return record.

exampleDB.PNG
exampleDB.PNG (21.84 KiB) Viewed 325 times
---
Pedro Vazquez Rosario
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

listsos
Posts: 9
Joined: Fri Aug 18, 2017 5:51 pm

Re: Write SUCCESS flag to Database After Successful DB Output

Postby listsos » Thu Dec 07, 2017 8:12 pm

Thanks for your reply!

I have moved the SUCCESS job the Port 1. I don't have any metadata selected for the connection as the SUCCESS job will simply be running "UPDATE STATUS_TABLE set STATUS = 'SUCCESS" presuming the graph completes successfully.

When running the job, it now errors stating "No metadata and no metadata stub defined for edge". What should be included in the Metadata, no data is really being passed along to the second database write.

listsos
Posts: 9
Joined: Fri Aug 18, 2017 5:51 pm

Re: Write SUCCESS flag to Database After Successful DB Output

Postby listsos » Fri Dec 08, 2017 2:40 pm

Also note that the first DB Output Table job (after the MEMGET) will add thousands of records. I simply want to add a single SUCCESS or FAILURE flag to another database table when the graph completes.

bartonv
Posts: 56
Joined: Wed May 03, 2017 12:10 pm

Re: Write SUCCESS flag to Database After Successful DB Output

Postby bartonv » Mon Dec 18, 2017 10:20 am

Hello Listsos,
the DBOutputTable is capable of returning records that were successfully inserted into a database, as well as returning those that failed. You could take advantage of one of these features to achieve the desired result. Attached is an example solution of how this can be approached. The main idea of the graph is that the query (which is updating the status table) is stored in a dictionary as a single string value and is set to "UPDATE STATUS_TABLE set STATUS = 'SUCCESS'" by default. If no record fails in the process of inserting data into the database, the DBOutputTable component will not send any records on the port 0, thus not affecting the query in the dictionary. On the contrary, if there was even a single record that failed during the inserting process, it will result in changing the dictionary value to "UPDATE STATUS_TABLE set STATUS = 'FAIL'". In the next phase, there is a DBExecute component that will run the query in the dictionary and update the status table respectively.
Kind regards,
Attachments
updatingStatusTable.grf
(4.92 KiB) Downloaded 11 times
---
Vladimir Barton
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support


cron