Question Copy replication trigger assignments.

ron

Member
OE 11.7.4 on GNU/Linux.

Hi DBA people!

I have a test DB set-up and working that has a set of write and delete replication triggers. In all cases the path names to the trigger files are "relative" and will, therefore get resolved via the PROPATH to be in a directory that is associated with this database (ie, "as usual").

I have just over 30 copies of this database used for development, testing, UAT and operations support. My challenge is to copy all the trigger assignments (ie what's in _File-Trig) from the database where they are set-up now -- into all the others.

I do have a script to remove triggers (used in cases where a DB with triggers is restored into an environment where the triggers are not required) -- but I don't have one to go the other way. Does anyone have one? Are there any particular issues I should be aware of in doing this?

Ron.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Caveat: I don't have any experience with replication triggers.

As @Cringer says, you could try to create an incremental .df between a DB with the triggers and one in which the triggers have been deleted. The trigger info is contained in a full .df but I don't know whether it is handled correctly by the incremental code. You would have to test it and find out. You might also have to massage the resulting .df if there are other schema differences between your two databases and you just want this .df to contain updates that add the triggers, especially if you plan to load the .df into many different databases that might be on different schema versions.

You could also try to treat it as data rather than schema. If you use the Data Dictionary dump feature and select the checkbox for "Show hidden tables", you can then select _file-trig from the list and create a .d file. Here is one I created from sports2020:

Code:
C:\db\12\s2k20>type _File-Trig.d
"CREATE" "sports2020trgs/crware.p" no ? 0 ?
"WRITE" "sports2020trgs/wrware.p" no ? 0 ?
"CREATE" "sports2020trgs/crsuppl.p" no ? 0 ?
"DELETE" "sports2020trgs/delsuppl.p" no ? 0 ?
"CREATE" "sports2020trgs/crpo.p" no ? 0 ?
"CREATE" "sports2020trgs/crref.p" no ? 0 ?
"CREATE" "sports2020trgs/crordl.p" no ? 0 ?
"DELETE" "sports2020trgs/delordl.p" no ? 0 ?
"WRITE" "sports2020trgs/wrordl.p" no ? 0 ?
"CREATE" "sports2020trgs/crinvtx.p" no ? 0 ?
"CREATE" "sports2020trgs/crinv.p" no ? 0 ?
"DELETE" "sports2020trgs/delinv.p" no ? 0 ?
"CREATE" "sports2020trgs/crlocdef.p" no ? 0 ?
"CREATE" "sports2020trgs/crord.p" no ? 0 ?
"DELETE" "sports2020trgs/delord.p" no ? 0 ?
"WRITE" "sports2020trgs/wrord.p" no ? 0 ?
"CREATE" "sports2020trgs/critem.p" no ? 0 ?
"DELETE" "sports2020trgs/delitem.p" no ? 0 ?
"WRITE" "sports2020trgs/writem.p" no ? 0 ?
"CREATE" "sports2020trgs/crbin.p" no ? 0 ?
"CREATE" "sports2020trgs/crcust.p" no ? 0 ?
"DELETE" "sports2020trgs/delcust.p" no ? 0 ?
"WRITE" "sports2020trgs/wrcust.p" no ? 0 ?
"CREATE" "sports2020trgs/cremp.p" no ? 0 ?
"WRITE" "sports2020trgs/wremp.p" no ? 0 ?
.
PSC
filename=_File-Trig
records=0000000000025
ldbname=s2k20
timestamp=2021/09/16-07:35:30
numformat=44,46
dateformat=mdy-1950
map=NO-MAP
cpstream=ISO8859-1
.
0000001122

I didn't test the load side. That's left as an experiment for the reader. :)

Finally, there's this approach: Progress KB - How to add replication triggers programatically

Note the warning however:
* * * WARNING * * *
Manually modifying the metaschema without using the provided Data Dictionary/Data Administration tools is potentially dangerous, and not supported. Always make sure there is a valid backup of the database before doing so.
 

ron

Member
Thank you very much for your very helpful and prompt responses!

The code in the KB Article is what I'd worked out as probably the right approach, but I was concerned that there might have been something missing. For sure -- between the two ideas of using an incremental df and using a program -- II I'm sure I can get this done now.

Once again ... thanks!

Ron.
 

ron

Member
Hello again DBA experts ....

I am progressing with the new system using replication triggers. So far -- it's all working very well.

However -- we have "classic" AppServers connected and I see that it's necessary for the trigger code to also exist locally on the AppServer systems.

If the trigger code running on an AppServer executes DBTASKID(BUFFER db.table:DBNAME) to discover the current transaction ID -- will it succeed?

Are there any "gotchas" to watch out for with executing replication triggers on a remote system?

Ron.
 

TomBascom

Curmudgeon
That syntax is incorrect:

DBTASKID

Returns an INTEGER value that uniquely identifies a database's transaction.

Syntax

DBTASKID ( integer-expression|logical-name|alias )

integer-expression

The sequence number of a database the ABL session is connected to. For example, DBTASKID(1) returns information on the first database the ABL session is connected to, DBTASKID(2) returns information on the second database the ABL session is connected to, etc. If you specify a sequence number that does not correspond to a database the ABL session is connected to, the DBTASKID function returns the Unknown value (?).

logical-name or alias

A character expression that evaluates to the logical name or alias of a database that is connected to the current ABL session. If the character expression does not evaluate to the logical name or alias of a connected database, DBTASKID returns the Unknown value (?).

Transaction context does not cross the client <--> appServer boundary. So if you are asking if an appServer session will be aware of the transaction of a client that called it the answer is "no".

Personally, I would consider it a grave error to call an appServer when a transaction is active. Much like prompting the user with a transaction active. Transactions should be very tightly scoped and not depend on external events for their completion. (I'm sure you already know that and you're doing your best to deal with an existing application "as written". But I thought I would add the point just in case someone comes along and reads this without that understanding.)
 

ron

Member
Thank you Tom -- but are you saying that the client cannot discover the current transaction ID?
If a replication trigger executes on the client -- how can one know that the current transaction is completed and therefore operation is committed?

Ron.
 

TomBascom

Curmudgeon
A session can discover its own current transaction with dbtaskid(). If a client has a transaction active it might, for instance, have a dbtaskid() = 123. If it calls an app server while that transaction is still in scope the app server session could start a transaction of its own which would then get another dbtaskid(). Let's say dbtaskid() = 456. The app server could commit the data, complete its call and return and then the client could commit and complete without error. dbtask 456 would be committed first, dbtask 123 second. Or, the appserver could encounter an error, backout its transaction and return. Depending on how it returns status to the client the that client may, or may not, know that there was an error and even if it does know it could ignore it. Likewise, the appserver may commit the transaction and return and _then_ the client encounters an error and backs out its transaction. The appserver transaction is unaffected by this.

Transaction activity on the app server is NOT a sub-transaction. It is a complete stand-alone transaction.

Regarding: Can the appserver client *discover* the transaction# of the client that called it? If the appserver knows who the client was then, yes, the _Trans VST record belonging to that usr# has an _Trans-num field.

The tricky part is figuring out who called your appServer session. I don't think there is a generic method for doing that.
 

ron

Member
Thank you Tom -- but I'm still not clear about this.

On the Linux server where the DB resides the replication trigger captures details about the update and uses DBTASKID to get the current transaction#. Another process checks these "in abeyance" details and tests _Trans to see if the transaction has completed. If completed the change is committed and can be dealt-with.

I'm trying to get my head around the situation where a trigger executes on an AppServer -- which is on a Windows machine. Will the DBTASKID function return the transaction# that the server is using to embrace this activity that the client is causing? If it is then exactly the same trigger code will work on the AppServer client as is used on the server. But if not then how can I know when the client transaction is committed?
 

TomBascom

Curmudgeon
On the Linux server where the DB resides the replication trigger captures details about the update and uses DBTASKID to get the current transaction#. Another process checks these "in abeyance" details and tests _Trans to see if the transaction has completed. If completed the change is committed and can be dealt-with.

"Another process" is critical.

How did this other process discover the transaction# or usr# of the "in abeyance" details?

I'm trying to get my head around the situation where a trigger executes on an AppServer -- which is on a Windows machine. Will the DBTASKID function return the transaction# that the server is using to embrace this activity that the client is causing? If it is then exactly the same trigger code will work on the AppServer client as is used on the server. But if not then how can I know when the client transaction is committed?

On the appserver side when you call dbtaskid() it is going to report that appServer session's current trx# (if it has one active) or ?. It has no built-in awareness of the transaction state of the client who called it.

A replication trigger runs within the context of the session that starts the transaction, so it has knowledge of the dbtaskid() it is running within. It is not running in any process context other than that of the session that triggered the transaction.

When you say "a trigger executes on an AppServer" what do you mean?

I take that to mean that something called an appServer and that appServer did something which updates the database. Therefore a transaction is created. The trigger code is going to be run by the appServer and within the context of the session that started the transaction. That trigger code will have the same dbtaskid() as the code which started the transaction. (Because they are all the same process and session.)

If, on the other hand, you mean that the trigger code is calling an appserver procedure then that appServer procedure will NOT have the same dbtaskid() as the caller.

Regarding the "Another process" again - If this other process is able to check _Trans then it seems like you must have coded an API of some sort to pass the usr# or similar information to it somehow. You need some way to communicate between processes. Writing to a text file, or a named pipe, or sockets, or a JMS queue or whatever.

For the sake of argument perhaps you wrote it to a text file and your "in abeyance" process reads that file to see which transactions it wants to look into. The local "dbtaskid()" would be just as meaningless to such a process as it is to a called appServer. But your code to check _Trans should be just as useful so long as you had a way to pass data between the processes.

On the other hand, if your "in abeyance" code running on Linux seems like it is getting the dbtaskid() *without* having it passed via some mechanism then I am afraid that you must be running that code within the context of the original transaction.

As for "how can I know when the client transaction is committed"? The active transaction will disappear but it could have just as easily been undone. That might, or might not, be important to what you are doing. I'm not sure. If you are writing these "in abeyance" details to a table in the same database and you are doing so within the context of the original transaction in question then the ability to FIND those details with an EXCLUSIVE-LOCK from the context of a different process demonstrates that the transaction committed.

Are you sure that using CDC wouldn't be a whole lot simpler? ;)
 

ron

Member
CDC was considered at length but it caused problems for us that I won't expand on here.

If a process local to the server updates the DB a repl trigger executes. Details of the update go into a table A that only holds "in abeyance" data. A separate record is created in table B holding the transaction# and a rowid pointer to the record in A. (It doesn't store the usr#)

A "chaser" program scans table B fairly constantly and for each record it tests whether the transaction# still exists in _Trans. If it does then the record is skipped. Otherwise it moves the data in A to another table where it gets stored as a committed change of data.

The above has been tested on the Linux server where the DB resides and works quite well.

We also have two Windows machines each hosting AppServers that connect to the database on the Linux machine. The AppServers deal with queries from external clients.

If code on an AppServer updates the DB the Progress docs say the trigger will execute on the client. So -- there has to be a copy of the trigger code on the AppServer.

I'm trying to work out whether the existing trigger code logic that works locally where the DB resides will also work on the AppServer. So far -- being able to identify the transaction# using DBTASKID is the only thing that is obvious as being a problem -- which is why I asked the question.
 

TomBascom

Curmudgeon
Ok, that's clearer to me!

Yes, dbtaskid() will uniquely identify an active transaction, from within that transaction's scope, whether it originates in a "normal" session or an appServer. If an appServer session does something that causes a write trigger to execute and that trigger code then invokes your code that writes the additional details then it will work no differently than a classic character client, a batch job, or a GUI connection.

The only time it will NOT work as you describe is when two distinct processes are involved. In that case the dbtaskid() code is no longer running within the scope of the transaction of interest.

The db engine does not run triggers, clients do. Trigger code, and anything that it might reference or run, has to be available to the client. Whether that client is local or remote to the db and whether the client is prowin*, _progres, _proapsv, or whatever.

To determine if the transaction has committed the "chaser" should attempt to get the additional data with an exclusive lock. This will only work if the transaction has committed. If the transaction is not yet committed the chaser will either queue for the lock or, if you use NO-WAIT NO-ERROR, return without getting the record. (This is the same as what you would need to do if you were processing CDC change records.)
 

ron

Member
In summary, Tom, I believe you are saying that getting the current transaction# and later testing for its existence in _Trans will not work when the update is caused by a remote connection. I don't really understand why that is so -- but I do appreciate that being able to re-access the record that was created/updated with EXCL lock would certainly indicate that the update action was committed. I will have to do some experimenting. :cool:
 

TomBascom

Curmudgeon
Sorry for the confusion, I’m not trying to say there is a difference between local and remote behavior for _Trans. Testing _Trans is going to work the same regardless of whether the transaction is local or remote.

(I think maybe the confusion arose when I was thinking you were creating the “chaser” records in a separate context from the triggers.)

The issue that I am pointing out is that, regardless of how _Trans came to be, it’s existence can only tell you that the the transaction is still active or that it has ended. By itself, that can’t tell you if the transaction was committed or rolled back.

However, if the “chaser” record is only ever created within the transaction of interest then the combination of its existence along with the lack of a corresponding _Trans record should work to confirm that a transaction completed.

To be really sure you need a multi-step process:
  1. Read the “chaser” to get the transaction info
  2. Test for _Trans
  3. If _Trans does NOT exist re-read the chaser (it may have been rolled back between steps 1 and 2)
Or you could simply EXCLUSIVE-LOCK the chaser. One step vs three.

You should also be aware that you might miss changes if your 4gl code uses DISABLE TRIGGERS or if you allow updates via SQL (SQL activity does not fire 4GL triggers).
 

ron

Member
Thank you Tom -- I think you have clarified the situation.

If a roll-back occurs the "A" and "B" records I mentioned will get undone -- so the absence of a _Trans record for the transaction is sufficient to believe that the update has been committed.

Therefore, it appears that the triggers written to work locally on the server should also work when copied onto the AppServer Windows machines.

We have a number of processes that access the target database with SQL, but all of them are read-only.

This project is the first time triggers have been implemented on this system so it is unlikely that any code contains "DISABLE TRIGGERS" commands -- but I will scan the source to ensure that is the case.
 

TomBascom

Curmudgeon
I'm probably flogging a dead horse but supposing that your chaser looks something like this:

Code:
/* chaser.p
 */

do while true:

  find next change_details no-lock no-error.

  if available change_details then
    do:
      find _trans no-lock where _trans._trans-num = change_details.transNum no-lock no-error.
      if available _trans then
        do:
          /* transaction is still active, skip this one */
        end.
       else
        do:
           /* transaction has ended, process the change details */
        end.
    end.

  pause 0.1.

end.

There is a small, but real, chance that the transaction gets rolled back and the change_details are invalid anywhere between the "find next change_details" and "if available _trans".

It's not hugely likely but it can happen. Whether or not it matters depends on what actions you need to take when you think you have found a change and how you handle errors.

I will also note that a FIND of a VST record using a WHERE clause that doesn't use an equality match on that VST's "id" field (_Trans-Id in this case) will be a table scan. VSTs are in memory and not latched so it isn't the end of the world but it is much less efficient. The number of _Trans records is the size of your "-n" startup parameter plus one.
 

ron

Member
I don't understand the problem. If the _Trans record for this transaction is available the current record is skipped. The only way I could envision a problem is for the _Trans record to be "wrongly not available" -- and I can't see how that could happen.

The need for a table scan on _Trans is a bit unfortunate, but not a significant concern. All of Area 6 is in -B2, so it is all memory-resident.
 

TomBascom

Curmudgeon
If the transaction was actually rolled back, rather than committed, and you act on some data that you initially found in the hypothetical "FIND change_details" then you will be acting on an event that didn't happen.

For instance...

Suppose you are tracking warehouse inventory and your details record says that 100 cases of beer were delivered. But as the truck was being unloaded the crew notes that it is all broken bottles and cancels that update AT JUST THE WRONG INSTANT while your chaser has seen the note that says "increment cases of beer by 100" (found because you're doing a dirty NO-LOCK read) just before the UNDO removing it. The record remains available in the 4gl session buffer and you see that there is no more _Trans record. So you increment the beer inventory by 100 cases.

Oops.

Likely? Not very. But it is possible that something like that can happen. If the actions that you are taking based on the change records are not critically sensitive to such errors then it's no big deal. It's also preventable if you re-read the change record. But if you trust that original record, base the "committed" decision solely on _Trans going away, and if the actions are sensitive to an error like that then you will eventually have such a mistake to deal with.
 

ron

Member
Yes -- but ...

Using the example I mentioned: "If a process local to the server updates the DB a repl trigger executes. Details of the update go into a table A that only holds "in abeyance" data. A separate record is created in table B holding the transaction# and a rowid pointer to the record in A. (It doesn't store the usr#)"

The "pointer" record in table B is within the same transaction as the update. If it is possible to access the B record I believe it either means the transaction is still active -- or it has been committed. I can't see how there is a chance of any problem.
 
Top