2

I am just trying to figure some ways to do manage archiving of some/most of our Application data within the database and wondering if something like this would be possible:

  1. Archive anything with a status of resolved, and a data updated over 3 years
  2. Move MOST, but not all that data to the archive database, and replace the values within the current production database with a “SymLink” that points to the archived database?

Here is a simplified example:

#######################################################################################################
###                                         Active_Prod                                             ### 
####################################################################################################### 
#  ALIASAPPTYPE     ALIASAPPREASON  PZINSKEY                PZPVSTREAM                                #
#  App_Type_1234    New Enrollee    132387Something6357997  <SYMLINKED to Archive_Prod.pzpvstream>    #
#                                                                                                     #
#                                                                                                     #
#######################################################################################################
###                                     Archive_Prod                                                ###
#######################################################################################################
#  ALIASAPPTYPE     ALIASAPPREASON  PZINSKEY                PZPVSTREAM                                #
#  App_Type_1234    New Enrollee    132387Something6357997  [BLOB Data]                               #
#######################################################################################################

So the query for select * from Active_Prod would return the following results:

ALIASAPPTYPE    ALIASAPPREASON  PZINSKEY                PZPVSTREAM
App_Type_1234   New Enrollee    132387Something6357997  [BLOB Data]

We would not be concerned with updating or inserting data as the Archive_Prod database would be set to read only anyway. My thinking here is we could drastically reduce the Active DB2 instance by archiving most of the date (The bulk of the data resides in the BLOB anyway), but keep the “Key” fields in the “Active” database for speedier lookups. But by creating a symlink of the data, we can improve the performance of PEGA, and improve the backup/restore times by dramatically reducing the overall size of the database.

1

You can achieve something like this using a federated view. After configuring the federated data source as described in the manual, you would create a nickname for the archive table

CREATE NICKNAME Archive_Prod FOR archive_server.archive_schema.Archive_Prod;

After that you would create a view joining the two tables:

CREATE VIEW v_active_prod AS 
  SELECT local.aliasapptype, local.aliasappreason, local.pzinskey, remote.pzpvstream
  FROM Active_Prod local
  INNER JOIN Archive_Prod remote
  ON (local.aliasapptype, local.aliasappreason, local.pzinskey) 
   = (remote.aliasapptype, remote.aliasappreason, remote.pzinskey)

assuming aliasapptype, aliasappreason, pzinskey is your primary key.

If you want to fetch your BLOBs regardless of where they reside, you may want to choose an outer join instead:

CREATE VIEW v_active_prod AS 
  SELECT local.aliasapptype, local.aliasappreason, local.pzinskey, 
         COALESCE(local.pzpvstream, remote.pzpvstream)
  FROM Active_Prod local
  LEFT JOIN Archive_Prod remote
  ON (local.aliasapptype, local.aliasappreason, local.pzinskey) 
   = (remote.aliasapptype, remote.aliasappreason, remote.pzinskey)

Don't expect performance to be stellar though when reading BLOBs from a federated data source.

  • It should probably be a left outer join, not an inner join. The archive might not contain all the data, some might be in the active database only and an inner join like this would filter them out. – Andriy M Jul 20 '16 at 16:16
  • @AndriyM We don't know the OP's data model so we can only guess (they said they "keep the 'Key' fields in the 'Active' database"), but agree, outer join might make sense. – mustaccio Jul 20 '16 at 18:44
  • That is correct, All searchable vields would remain in the Active_Prod database and only a common index field and the BLOB would remain in the Archive_Prod database. I will pass this on to our DBA's to see if this is something worth looking into, thanks for the links and the examples folks. – AIT Jul 21 '16 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.