Data Archival extraction syntax

, , ,
1

There is a pdf file that comes with the product which is a basic primer on how to code the extraction syntax. It has a few examples following each keyword but I don’t see an exact representation of what I want to do, which is to transfer data under certain conditions to an archive database (not a vault) and simultaneously remove the transferred records from the source file.

My question is… do both of these do this, or are they somehow different (test is being started now to find out myself but it will take a while). Or… will neither one work as desired?


EXTRACT Dev_fin_trans_xref(Dev_fin_trans_xref.AI < 731945)
{
   TRANSFER Dev_fin_trans_xref[*] TO Archive_fin_trans_xref;
   REMOVE Dev_fin_trans_xref;
}

vs


EXTRACT Dev_fin_trans_xref(*)
{
   IF(Dev_fin_trans_xref.AI < 731945)
   {
      TRANSFER Dev_fin_trans_xref[*] TO Archive_fin_trans_xref;
      REMOVE Dev_fin_trans_xref;
   }
}

Thanks in advance!

2

Hi.
Both these should do what you want. The difference will be in which Adabas commands are generated to select the records from the Soure File.
The first will use L3s where possible (assuming field AI is a descriptor) whereas the second will result in every record in the Source File having to be read.

3

Thanks, Geoff! I am glad this product is able to issue commands based on a descriptor, and I will code things that way.

I can see down the road, though, that some criteria might make use of superdescriptors. I assume I can make use of superdescriptors just by referencing the shortname of the super in the same way I reference AI in this example.

What I still don’t find in this is any kind of robust ability to dynamically use variables to allow the rule to always look n years back.

For example, something like:

DEFINE DATA LOCAL
1 #KEY_DATE(D)
1 #DATE(A8)
1 REDEFINE #DATE
  2 #YEAR(A4)
  2 REDEFINE #YEAR
     3 #YEAR_N(N4)
  2 #MMDD(A4)
END_DEFINE
*
MOVE EDITED *DATX (EM=YYYY) TO #YEAR
SUBTRACT 7 FROM #YEAR_N 
MOVE '0101' TO #MMDD
MOVE EDITED #DATE TO #KEY_DATE (EM=YYYYMMDD)
*
EXTRACT production_order_file(production_order_file.order_date < #KEY_DATE)
   {
       TRANSFER production_order_file(*) to archive_order_file;
       REMOVE production_order_file;
   }

Does such functionality exist in some syntax that does like this pseudo-Natural example portrays, or will I always have to modify date-based extraction syntax as each year or other period passes?

4

Hi Brian,
Yes, super-descriptors can be used in the same way.

You can use the TODAY() function for establishing write-once date-based extraction syntax. This function is described in chapter 10 of the pdf file on Extraction Syntax.
Using your pseudo-Natural example, the EXTRACT statement would be something like:

EXTRACT production_order_file(production_order_file.order_date < TODAY(-7Y,“YYYYMMDD”) )

This example will extract records whose order_date is older than ‘today’ minus 7 years.

You could then schedule this to run as often as you like or perhaps just once a year on 1st Jan.

5

I must have an old pdf (it is dated 2015-11-20)… chapter 10 is where the reserved symbols are located, and my pdf document doesn’t include the word TODAY in it. This is good info though… I will give that syntax a try.

Thanks for your help!

-Brian

6

The TODAY() function was introduced in ADR_1.6.1.2 and above.
The latest patch on Empower currently is ADR_1.6.1.3.

If you are running ADR_1.6.1.1 then you will need to apply the (latest) patch to use TODAY().

7

Thanks, Geoff.

I have just now applied ADR_1.6.1.3 (which includes COR 8.2.6.3) and will give it a try.

-Brian

8

We are on v1.6.1.3 now so the TODAY functionality exists. However, I still am not sure how to apply it to our specific needs. Our requirements are to run in the middle of the month (e.g., 10th workday or 15th calendar day) to remove anything older than the 1st of the same month three years prior (e.g., if I run on 15 Oct 2016, data older than 1 Oct 2013 should be removed).

So, the functionality supports this syntax:

EXTRACT PEOPLE( PEOPLE.AH < TODAY(-3Y,'YYYY-MM-DD') )
{
    ARCHIVE MOVE PEOPLE [*];
}

However, such syntax will subtract 3 from the current year leaving the values of month and date alone. I need DD to become 01 regardless what date it is run as well as subtract 3 from the year. Is this supported by the enhancements that brought the TODAY function?

Thanks in advance!

-Brian

9

This is possible if you are using user-defined formats. Specifying a literal of ‘01’ instead of ‘DD’ will give you what you want.
Using your example:
EXTRACT PEOPLE( PEOPLE.AH < TODAY(-3Y,‘YYYY-MM-01’) )
{
ARCHIVE MOVE PEOPLE [*];
}

Unfortunately this is not currently possible if you are using any of the Natural date/time variables or Adabas date/time edit masks
for example:
EXTRACT PEOPLE( PEOPLE.AH < TODAY(-3Y,DATX) )
{
ARCHIVE MOVE PEOPLE [*];
}

If it is necessary to provide such support for these types of date formats then I would suggest to raise a Brainstorm request or Support Issue so proper consideration can then be given to the requirement.