Follow me

Most Recent Post

Upgrade 101

Please read the article posted on Medium using the link below: Upgrade 101

Popular Posts this year

Wednesday, April 18, 2018

Using Oracle's Flashback Query Concepts in JDA/RedPrairie Implementations

Oracle offers a very powerful concept through its Flashback Technology (https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#g1026131).  This article is solely focused on how to take advantage of this in your JDA/ RedPrairie implementations that are on Oracle Databases.

A common problem that all of us face in triaging a JDA/ RedPrairie issue is how to recreate it.  Most of the issue post-production are data related and recreating them is tedious, especially in production.  It could be how a pick release did not function as expected, a list generated incorrectly, a hop criteria not fulfilled or a a number of other business rules that are solely data dependent and change state very quickly in the course of transactions in the JDA/ RedPrairie system. 

How often do we wish to have a picture of the data, just before the problem occurred.  We resort to looking at Daily Transactions for clues or simply put in new data to see if the issue would re-occur and hope that the trace will capture it.

Welcome to Oracle's Flashback Technology.

Using something as follows is actually possible:

[select * from pckwrk_view 
 AS OF TIMESTAMP TO_TIMESTAMP ('2018-04-20 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
 where ship_id='MYSHIPMENT']


  • The above query has the additional criteria for AS OF TIMESTAMP
  • This Date/Time will be in the past to see the state of data as of that date
  • The rest of statement will allow for normal WHERE criteria etc.
  • Since it is MOCA, you are allowed to pipe into compound statements all, using same AS OF TIMESTAMP to see data from multiple tables (like PCKMOVs for the above PCKWRKs)
I am sure, by this time, excitement level is building and ideas coming to mind as to how to utilize this feature.  Just be mindful that this is not something that has an unlimited range.   Based on how you set this up, the window of your historic data viewing may be limited.  However, knowing that this option is available and it can be setup to my specifications is a blessing nonetheless.

The above article from Oracle explains in full detail how to use this and how to set this up.  I will encourage all to utilize this and make sure that if you are using Oracle as the database, to turn this feature on.

Some other benefits that can be achieved:
  • Recovering deleted data (or updated to incorrect values)
  • Recreating an issue
  • Recovering Dropped Tables
  • Creating a chronological view of changes to data (great for auditing or providing some type of Audit Trail to someone) - This will employ the VERSIONS BETWEEN SCN feature
  • Creating a Flashback Data Archive - No longer do we need to add code for tracking transactional changes to tables for Audit reasons
    • A Flashback Data Archive is useful for compliance and audit reports
    • Possible post-go-live and during Hypercare, many critical transactional tables can be in this archive so longer data retention is available
    • Options such as QUOTA and RETENTION (time limit) can be specified for how long this data is retained
In today's world where disk is cheap, I would encourage all to utilize this feature to the best possible way.  I am sure once it is enabled, there will be a time when you would be glad that you had it enabled.  The features have been available since Oracle 9i.  However, the technology has improved from being solely a DBA function to be available as a User function (based on your rights).  So please investigate your database version to see what extent of features are available for your implementation.  

Also, at this time, I have not found anything similar in SQL Server.

2 comments:

  1. Really useful! Could provide great value when finding root cause is difficult.

    ReplyDelete
  2. What is the shortcut to ad RF messaging to a user?

    ReplyDelete