Second run of the archive specification

Specify r again as a line command in front of your archive specification on the panel in Figure 7-24 to run the already completed specification.

Figure 7-25 Data Archive Expert’s presentation of a confirmation panel

Figure 7-26 shows how to change the row filter to archive the line items with a shipment date before March 1992.

Figure 7-26 Changing the row filter before staring the second run

Now specify r for RUN on the command line and press Enter. The result of this second run of the specification is shown in Figure 7-27.

Figure 7-27 Result of the second archive specification run

So, the archive specification run did work! Our source table eliminated 5112 rows.

7.5 Result

Let us look at what has been achieved and how this is documented.

With F3 you go back to the panel in Figure 7-28.

Figure 7-28 Request for an historical overview about the specification

SpecifyH for History on this panel; the Data Archive Expert shows you the actions that have been taken for this archive specification SCENARIO1; see Figure 7-29.

AHXV11 --- Archive Run Statistics--- Archive run successful Command ==> Scroll ===> CSR Archive specification : SCENARIO1 DB2 system . : DB2G Creator . . . : PAOLOR1 Description : Archive old LINEITEM rows to a file (incl. deletion) Row filter : L_SHIPDATE < '1992-03-01' Run . . . . : 2 ===============================================================================

Source table: LINEITEM Creator: PAOLOR1 Del: 5112 Target : PAOLOR1.S00085.V0001.D2003310.T041852 Ins: 5112 ******************************* Bottom of data ********************************

AHXV11 --- Archive Specifications List --- Row 1 of 1 Command ===> Scroll ===> CSR Primary commands are: DB2 system : DB2G FI - Filter archive specification list User ID . : PAOLOR1 RE - Refresh archive specification list Time . . . : 23:20 Line commands are:

N - New C - Copy D - Delete R - Run B - Browse details E - Edit H - History of runs I - Info summary F - Table archive to file T - Define retrieve specification L - List retrieve specifications Cmd Name Description Creator Updated State Type --- --- --- --- --- --- ---- H SCENARIO1 Archive old LINEITEM PAOLOR1 2003-11-05 COM FILE ******************************* Bottom of data ********************************

Figure 7-29 Data Archive Expert’s overview about an archive specification

Please note that the row filter of the archive definition reflects the row filter of the last run.

With s you can see the details of a previous run again (see Figure 7-30).

Figure 7-30 DAE’s details about a specific run of an archive specification

Now let us check whether the archive specification run has really completed, in other words, whether the archived rows have been deleted from the original LINEITEM table. We issued a SELECT COUNT, and Figure 7-31 shows that everything has worked properly.

AHXV11 --- Archive Specification History --- Row 1 of 3 Command ===> Scroll ===> CSR Specification name: SCENARIO1 Creator: PAOLOR1 Description: Archive old LINEITEM rows to a file (incl. deletion) Line commands are:

S - Show statistics Cmd Run State Run by Date Row filter --- --- --- --- --- 0 Defined L_SHIPDATE < '1992-03-01' s 1 Complete PAOLOR1 2003-11-05-23.12.59 L_SHIPDATE < '1992-02-01' 2 Complete PAOLOR1 2003-11-05-23.18.29 L_SHIPDATE < '1992-03-01' ******************************* Bottom of data ********************************

Archive specification : SCENARIO1 DB2 system . : DB2G Creator . . . : PAOLOR1 Description : Archive old LINEITEM rows to a file (incl. deletion) Row filter : L_SHIPDATE < '1992-02-01'_______________________________________

_______________________________________________________________________________

_______________________________________________________________________________

_______________________________________________________________________________

Run . . . . : 1 ===============================================================================

Source table: LINEITEM Creator: PAOLOR1 Del: 0 Target : PAOLOR1.S00085.V0001.D2003310.T041310 Ins: 0 ******************************* Bottom of data ********************************

Figure 7-31 Test if inactive rows have been removed

Furthermore, if you view the contents of the dataset, you can see that the second archive specification run has stored 5112 rows (Figure 7-32), and we assumed for the moment that these are the correct ones. In scenario 2, we also checked that the correct rows are archived.

Figure 7-32 Browse data set containing archived rows

7.6 Considerations

We list here some considerations related to the experience gained in running this scenario.

7.6.1 Intermediate table used when archiving to file

Internally, Data Archive Expert does not just unload the data from the source table through the UNLOAD utility. Instead, Data Archive Expert first inserts the rows to be archived into an intermediate table, then unloads the rows from that table, and finally deletes the rows in the source table. Here are the reasons why does Data Archive Expert works that way:

---+---+---+---+---+---+

SELECT COUNT(*) FROM PAOLOR1.LINEITEM WHERE L_SHIPDATE < '1992-03-01' ; ---+---+---+---+---+---+

---+---+---+---+---+---+

0 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---+---+---+---+---+---+

File Edit Edit_Settings Menu Utilities Compilers Test Help sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss VIEW PAOLOR1.S00085.V0001.D2003310.T041852 Columns 00001 00072 Command ===> Scroll ===> CSR

****** ***************************** Top of Data ******************************

000001 Ï$ Q á [ :÷ ööòRF1992-02-101992-04-011992-02-14DELIVE 000002 Ú à 1 Ôe :÷RF1992-02-261992-04-111992-03-01NONE 000003 g àL¶W rª éÔeRF1992-02-241992-04-201992-03-06TAKE B

... etc.

005108 a Z_ Þ äêÀu :÷ç RF1992-02-251992-04-211992-03-14NONE 005109 eA 9 à ï éÔe 5CRF1992-02-211992-04-091992-03-21NONE 005110 eA )· à<óC rª :÷AF1992-02-091992-04-241992-03-07NONE 005111 iB ¥d N àZû Ôe éÔeRF1992-02-271992-04-261992-03-21TAKE B 005112 iB Ïð á I¸ 5B±RF1992-02-121992-04-211992-02-27NONE

****** **************************** Bottom of Data ****************************

򐂰 Your row filter might be too complicated for the WHEN clause of the UNLOAD utility, whereas the WHERE clause of the SELECT can handle your row filter specification. So, Data Archive Expert first stores all rows that satisfy the row filer in the intermediate table, then it can unload from this table without worrying about the WHEN clause.

򐂰 After the UNLOAD, Data Archive Expert must ensure that only those rows are deleted from the source table that have been unloaded. If Data Archive Expert applies the same row filter for the deletion as it did for the inserting into the intermediate table when accessing the source table again, it could delete more (in the meantime, inserted or updated) rows than it had unloaded, so you would loose these data. Therefore, Data Archive Expert deletes exactly those rows from the source table it can find in the intermediate table.

7.6.2 File archives require a complete run

Of course, the intermediate table is dropped after the file archive specification run. As a consequence, Data Archive Expert cannot split the work to be done into two steps: First, unloading the rows to a data set, and some time later deleting the rows from the source table.

The intermediate table is needed for this deletion.

On the panel in Figure 7-6 on page 99 you specified:

Complete archive run (delete source data)? ==> Y (Yes/No)

If you only want to archive rows into data sets without deleting them from the original table, then you may specify N, but this is meaningless, as the whole run only consists of the first step anyway.

But if you want to also delete the rows in the original table, it now becomes clear that you cannot specify N in that case. Note, you are dealing with file archives, specifying N for table archives is fine. Archive scenario 3 shows among other things how to split the archive run into two steps when archiving to tables.

7.6.3 Names of the archive data sets

The two archive runs in this scenario have created the two datasets:

PAOLOR1.S00085.V0001.D2003310.T041310 PAOLOR1.S00085.V0001.D2003310.T041852

You can derive from these names the default naming convention Data Archive Expert uses for the archive data sets:

򐂰 S00085 is the specification number in the system (as we have seen no other specifications, other users must have defined some specifications already).

򐂰 For the time being, V0001 is constant.

(This may change to reflect the version, that is, the number of the specification run.)

򐂰 D2003310 is the Julian date.

Attention: Data Archive Expert lets you specify a file archive with an awkward combination:

1. Complete run = N and 2. Delete rule for a table = Y

In this case, 1) overrules 2), so no rows are deleted from the original table.

򐂰 T041310 is the time.

In particular, data set names for different tables cannot be distinguished by a table number, they just differ because of the (date and) time qualifiers.

7.6.4 How to find describing information to a given archive data set

So, if you received a data set name and want to know whether you still need that data set, it is not an easy task to find the related archive specification, or even the related table in this specification whose data is stored in that data set. You have to dig into Data Archive Expert’s metadata tables and look there for your specification number, in this case for 85. In Data Archive Expert’s SYSTOOLS.AHXSPECS table, you might find something like Example 7-1.

Example 7-1 Specifications in Data Archive Expert’s metadata table SPECID SPECTYPE SPECNAME

--- --- 3 ARCHIVE IVP No Grouper 4 RETRIEVE Retrieve w/o Groupr 5 RETRIEVE IVP W/O Grouper #2 6 ARCHIVE IVPARCHIVE ...

76 OFFARCH1 COPY2TAPE3 77 OFFARCH1 LINEITE1 85 OFFARCH1 SCENARIO1

From that you know the specification name SCENARIO1. In Data Archive Expert you can then find out what has been archived into the data set.

More examples for querying the metadata can be found in Chapter 12., “Additional archive considerations” on page 233.

7.6.5 Recommendation for data set names

As can be seen from the last section, using Data Archive Expert’s default names is not optimal from a maintenance perspective; for instance, you might not have the authorization to access Data Archive Expert’s metadata tables. Therefore, you should use templates for your file archives, as explained in Chapter 10., “Scenario 4: Archiving from RI related tables and deleting from them” on page 191.

Chapter 8.

Scenario 2: Archiving from a