Archives of the TeradataForum

Message Posted: Tue, 03 May 2005 @ 20:16:11 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

We have a set of Tpump processes that read from MQ Series and write to the database. Right now we have 5 tpump processe, broken out by type of data (address, account, customer). Due to high volumes, our source has informed us that they are going to expand from 5 to 10 queues - meaning we will now have 10 tpumps instead of 5. With the original set of queues, there was no overlap of data. All addresses came through queue #1, accounts in queue #2, and customer was partitioned into the 3 remaining queues (ex. A-J, K-S, and T-Z). With the new queues, we will now have accounts coming from 2 places and they will overlap. Account 1234 could come through either queue. That means that every time we upsert a record, we need to determine if the message we are processing is newer than the data in the table:

     if record exists
     then
             if input is newer than current
             then
                     update
             else
                     skip message (no update, no insert)
             end-if
     else
             insert
     end-if

Any suggestions on how to handle this? As far as I can tell, it doesn't look like if-then-else type logic is available within the DML, only in the support environemnt (before the .BEGIN LOAD and after the E.ND LOAD). And even though all the DML's get converted into macros by the system, tpump won't let me use the ABORT command.

Current DML:

     .DML LABEL MyInsert
     DO INSERT FOR MISSING UPDATE ROWS;
     update mydb.mytable set
               account_nm = :in_acct_nm
             , update_ts = :in_update_ts
     where
               account_id = :in_acct_id
     ;
     insert into mydb.mytable (
               account_id = :in_acct_id
             , account_nm = :in_acct_nm
             , update_ts = :in_update_ts
     )
     ;

Any help would be most appreciated! Thanks, in advance, for your help!

Brad.

	<Prev	Next>		<<First	<Prev	Next>	Last>>