IBM Sterling Transformation Extender

 View Only

  • 1.  ITX DB Query Rollback

    Posted 13 days ago
    Reply

    Good day Team,

    Kindly assist on how to do rollback on manual DB Query in case it fails to execute..

    For example, for a DB Card that also does the insert, I'm able to rollback by selecting Burst as scope. If the DB card fails in any case, the map does rollback successfully.

    I have a requirement to also do the same on manual queries like below. Is there a command to add inside the DBQUERy function ?

    =DBQUERY( "INSERT INTO TRANSFERS VALUES ('" + MSG_ID Column:""TRANSFERS_2 + "','" + SENDER_BIC Column:""TRANSFERS_2 + "','" + RECEIVER_BIC Column:""TRANSFERS_2 + "',CURRENT_TIMESTAMP,'" + 
    MSG_FORMAT Column:""TRANSFERS_2 + "','" + ORIG_MSG_ID Column:""TRANSFERS_2 + "','" + SOURCE Column:""TRANSFERS_2 + "','" + DESTINATION Column:""TRANSFERS_2 + "','" + MSG_PHASE Column:""TRANSFERS_2 + "','" + MSG_STATUS Column:""TRANSFERS_2 + "',CURRENT_TIMESTAMP,'" + Tag21Ref + "','" + Transc'nRef#_16X General Line SubField:TransactionRef# #20 General Field:A_General_Information Sequence:Message MT202_COV Category2 Text Block:MT202 + "','" + SWIFT_REF Column:""TRANSFERS_2 + "','" + SNL_REF Column:""TRANSFERS_2 + "','" + PACKAGE(MT202) + "','" + FILLLEFT(WORD(Id:sequence:MktPrctc:sequence:AppHdr:global:AppHdr,",",2),"0",4) + FILLLEFT(WORD(Id:sequence:MktPrctc:sequence:AppHdr:global:AppHdr,",",3),"0",6) + "','" + LEFT(SENDER_BIC Column:""TRANSFERS_2,8) + "')")



    ------------------------------
    AMUKELANI SHIPAKULE
    ------------------------------


  • 2.  RE: ITX DB Query Rollback

    Posted 11 days ago
    Reply

    Hi!

    You're going to want to use the "second meaning" of the DBQUERY command in the reference guide to override the DB setting like you would at the card level.

    https://www.ibm.com/docs/en/ste/10.1?topic=functions-dbquery

    DBQUERY ( "SELECT * from PARTS" , "-DBTYPE ORACLE -CONNECT MyDB -USER janes" ) 

    If these options aren't available you might want to use the suggestion to use a RUN map to actually execute the query with these options at the card level. Otherwise depending on your database - you might want to just call a stored procedure instead of writing a direct sql query and call that instead so that you can include any rollback or error instructions directly in the stored procedure call vs the query inside your ITX map.  This would be my preference for several reasons, you can then update the stored procedure (as long as parameters don't change) without changing/recompiling the map.  You can work directly with the database to test rollback and error handling needs as well and it is often preferred from a security aspect as well since your ITX db user can then be granted very specific execute permissions to certain functions/ stored procedures as necessary.  Hope this helps you pick a path!

    Happy Mapping!



    ------------------------------
    Lisa Edwards
    Software Engineer / Subject Matter Expert
    Rainbow Data Systems, Inc
    ------------------------------

    Original Message


Related Content