In our business we have a requirement to have the EDI data visible to the customer service reps.
To this end we have created a web page modeled after the WmTNWeb -> Analysis -> Transactions that has additional attribute selections in the query. This is working fine for us up to this point.
We now have to start archiving our data to get the old data out of the transaction tables and into the archive tables to improve processing performance.
The problem we have is that once the EDI data is in the archive tables it is no longer available to be queried by the customer service reps.
We have a business requirement to have the EDI data available for 2 years for the customer service reps.
Has anyone created services like “createDocumentQuery” and “documentQuery” in the wm.tn.query folder that can query the archive tables?
Our thought is to create views that are a UNION of the transaction table and matching archive table. Then have a new “documentQuery” service query the new views to be able to get data from both tables.
We would add appropriate indexes to the archive table to improve query response.
CreateDocumentQuery and documentQuery are used to query the data from the active tables and you cannot customize them to get the data from the archived tables.AFAIK, you are in the right direction with your thought and will work.
Thanks for the replies and encouragement that we are on the right track.
Now all we need it to find a java wizard that can create the two services. I realize it will not be able to use the BizDocStore object as that is tied to the active tables but will need to simulate that working with the new views.
Unfortunately our in-house java expertise is limited hence my posting here to see if anyone else has done this.
Perhaps you could work with one your DB developers to write stored procedures to do this. Then you can invoke the stored procs from IS using JDBC services and not need to write any Java services.
Just a follow up: Has anyone written anything in all these years of TN’s existence to access the archive tables so that the old bizdocs can be viewed? If so, care to share any ‘lessons learned’ with all of us since these tables don’t have primary/foreign keys, etc?
I’ve been thinking about this issue some more. Ideally we should be using TN built-in services rather than going directly to the db. This database is proprietary and can change (in fact from looking at the 6.5 docs there have been changes made to the TN database tables). I’m looking at using the TN view and delete services to manage the archiving myself based off of the main TN tables.
I just checked the Advantage site, and there is a utility you mentioned to view archived tables using the JDBC adapter and a browser, under the ‘Samples’ section.