Does Optim have the following capability?

 

We have a system that has copies of another system's tables as they are needed for reference purposes.  We are currently archiving(via Optim) the 'source' system tables monthly.  When a row is archived and purged from the source system table we need to archive and purge the row from the 'copied table'.  We would like to use Optim to read the source system's archive files and pull out the ids of the archived rows and place them into a 'work/temp' table along with the actual table name as another column.  For instance, we have an Order table with OrderID as the unique key column.  If OrderID 10 and 15 are archived we would like to have a rows populated on the 'work' table as below.

 

The problem we are having is this.  For the Table name column in the work table how do we get it populated?  Hard code it somewhere within Optim?  Have Optim populate it from source table name somehow?

 

Table name	Unique ID (of row in source table)
Order	10
Order	15

 

Using the 'work' table we would then look at the Order table in the destination(copied) system and archive/purge the corresponding row.

We can restore the .af files to separate tables and use those to process the copied table archives but that seems to be a lot of work.  We are talking about over 100+ tables in the near future.

Thoughts and/or other ideas?

------------------------------
Phil Neff
------------------------------

#InfoSphereOptim
#Optim

​Hi,

Look a the ARCHIVE ACTIONS. One could setup, and then INSERT into a side table, the keys of rows being archived/deleted. And then use that side table to generate a row (PNS) list used with your other table for ARCHIVE/DELETE to keep them in sync.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.
------------------------------

When using the Archive Action functionality it looks like it only works on the 'start table'.  I am attaching a document with screen shots for what we need it to do.



Thanks,

Phil

------------------------------
Phil Neff
------------------------------

Original Message:
Sent: 09-28-2018 17:19
From: Gregory Czaja
Subject: Does Optim have the following capability?

​Hi,

Look a the ARCHIVE ACTIONS. One could setup, and then INSERT into a side table, the keys of rows being archived/deleted. And then use that side table to generate a row (PNS) list used with your other table for ARCHIVE/DELETE to keep them in sync.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.
------------------------------

​Hello Phil,

The ARCHIVE Actions can be defined on any table within the Access Definition.

Now, looking at your docs, I noticed that you are asking about the Distributed environment. The ARCHIVE Actions capability should be available there, as well.
In the z/OS environment, one can reference any table known to the attached DB2 subsystem. I do not know what are the AA limits, or additional capabilities, on the Distributed platform.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.
------------------------------

Original Message:
Sent: 10-08-2018 10:20
From: Phil Neff
Subject: Does Optim have the following capability?

When using the Archive Action functionality it looks like it only works on the 'start table'.  I am attaching a document with screen shots for what we need it to do.



Thanks,

Phil

------------------------------
Phil Neff
------------------------------


Original Message:
Sent: 09-28-2018 17:19
From: Gregory Czaja
Subject: Does Optim have the following capability?

​Hi,

Look a the ARCHIVE ACTIONS. One could setup, and then INSERT into a side table, the keys of rows being archived/deleted. And then use that side table to generate a row (PNS) list used with your other table for ARCHIVE/DELETE to keep them in sync.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.

Greg,

My Optim devleoper asked if you have an example on how this looks in Optim?

Thanks,

Phil

------------------------------
Phil Neff
------------------------------

Original Message:
Sent: 10-08-2018 13:13
From: Gregory Czaja
Subject: Does Optim have the following capability?

​Hello Phil,

The ARCHIVE Actions can be defined on any table within the Access Definition.

Now, looking at your docs, I noticed that you are asking about the Distributed environment. The ARCHIVE Actions capability should be available there, as well.
In the z/OS environment, one can reference any table known to the attached DB2 subsystem. I do not know what are the AA limits, or additional capabilities, on the Distributed platform.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.
------------------------------


Original Message:
Sent: 10-08-2018 10:20
From: Phil Neff
Subject: Does Optim have the following capability?

When using the Archive Action functionality it looks like it only works on the 'start table'.  I am attaching a document with screen shots for what we need it to do.



Thanks,

Phil

------------------------------
Phil Neff


Original Message:
Sent: 09-28-2018 17:19
From: Gregory Czaja
Subject: Does Optim have the following capability?

​Hi,

Look a the ARCHIVE ACTIONS. One could setup, and then INSERT into a side table, the keys of rows being archived/deleted. And then use that side table to generate a row (PNS) list used with your other table for ARCHIVE/DELETE to keep them in sync.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.

​Have a look at
https://www.ibm.com/support/knowledgecenter/SSC623_11.7.0/com.ibm.nex.optimz.common.doc/AccessDefs/opzcommon-r-archive_actions.html  and 
https://www.ibm.com/support/knowledgecenter/SSC623_11.7.0/com.ibm.nex.optimz.common.doc/TableMaps/opzcommon-r-archive_actionsa.html

which have some rudimentary examples.

This is using the z/OS ISPF interface. Almost identical process would be available for the Distributed platforms.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.
------------------------------

Original Message:
Sent: 10-09-2018 15:13
From: Phil Neff
Subject: Does Optim have the following capability?

Greg,

My Optim devleoper asked if you have an example on how this looks in Optim?

Thanks,

Phil

------------------------------
Phil Neff
------------------------------


Original Message:
Sent: 10-08-2018 13:13
From: Gregory Czaja
Subject: Does Optim have the following capability?

​Hello Phil,

The ARCHIVE Actions can be defined on any table within the Access Definition.

Now, looking at your docs, I noticed that you are asking about the Distributed environment. The ARCHIVE Actions capability should be available there, as well.
In the z/OS environment, one can reference any table known to the attached DB2 subsystem. I do not know what are the AA limits, or additional capabilities, on the Distributed platform.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.


Original Message:
Sent: 10-08-2018 10:20
From: Phil Neff
Subject: Does Optim have the following capability?

When using the Archive Action functionality it looks like it only works on the 'start table'.  I am attaching a document with screen shots for what we need it to do.



Thanks,

Phil

------------------------------
Phil Neff


Original Message:
Sent: 09-28-2018 17:19
From: Gregory Czaja
Subject: Does Optim have the following capability?

​Hi,

Look a the ARCHIVE ACTIONS. One could setup, and then INSERT into a side table, the keys of rows being archived/deleted. And then use that side table to generate a row (PNS) list used with your other table for ARCHIVE/DELETE to keep them in sync.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.

Thanks Greg.  Does that mean we need a 'side table' for each table being archived/deleted?  We were hoping that we could create a side table that contained the key and a column with the table name.  Then we would only have 1 table.

Thanks,

Phil

------------------------------
Phil Neff
------------------------------

Original Message:
Sent: 10-08-2018 10:20
From: Phil Neff
Subject: Does Optim have the following capability?

When using the Archive Action functionality it looks like it only works on the 'start table'.  I am attaching a document with screen shots for what we need it to do.



Thanks,

Phil

------------------------------
Phil Neff
------------------------------


Original Message:
Sent: 09-28-2018 17:19
From: Gregory Czaja
Subject: Does Optim have the following capability?

​Hi,

Look a the ARCHIVE ACTIONS. One could setup, and then INSERT into a side table, the keys of rows being archived/deleted. And then use that side table to generate a row (PNS) list used with your other table for ARCHIVE/DELETE to keep them in sync.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.

​The ACTIONs can execute SQL statements referencing any table. It can be the same table for all defined actions, irrespective of the table to which the ACTION has been attached.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.
------------------------------

Original Message:
Sent: 10-09-2018 08:13
From: Phil Neff
Subject: Does Optim have the following capability?

Thanks Greg.  Does that mean we need a 'side table' for each table being archived/deleted?  We were hoping that we could create a side table that contained the key and a column with the table name.  Then we would only have 1 table.

Thanks,

Phil

------------------------------
Phil Neff
------------------------------


Original Message:
Sent: 10-08-2018 10:20
From: Phil Neff
Subject: Does Optim have the following capability?

When using the Archive Action functionality it looks like it only works on the 'start table'.  I am attaching a document with screen shots for what we need it to do.



Thanks,

Phil

------------------------------
Phil Neff


Original Message:
Sent: 09-28-2018 17:19
From: Gregory Czaja
Subject: Does Optim have the following capability?

​Hi,

Look a the ARCHIVE ACTIONS. One could setup, and then INSERT into a side table, the keys of rows being archived/deleted. And then use that side table to generate a row (PNS) list used with your other table for ARCHIVE/DELETE to keep them in sync.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.

​Hi Phil,

Archive actions do work with other tables that are not the 'start table' on distributed platform. Please look at the attached document.

Thanks,
Sanjay

------------------------------
Sanjay Marwah
------------------------------

Original Message:
Sent: 10-08-2018 10:20
From: Phil Neff
Subject: Does Optim have the following capability?

When using the Archive Action functionality it looks like it only works on the 'start table'.  I am attaching a document with screen shots for what we need it to do.



Thanks,

Phil

------------------------------
Phil Neff
------------------------------


Original Message:
Sent: 09-28-2018 17:19
From: Gregory Czaja
Subject: Does Optim have the following capability?

​Hi,

Look a the ARCHIVE ACTIONS. One could setup, and then INSERT into a side table, the keys of rows being archived/deleted. And then use that side table to generate a row (PNS) list used with your other table for ARCHIVE/DELETE to keep them in sync.

------------------------------
Greg Czaja
Optim for z/OS Development
Unicom Systems Inc.