Define the archive specification

Let us start easily, so that you do not have to worry about things that distract your attention from the main path when using the DB2 Data Archive Expert for z/OS tool for the first time:

򐂰 Use the easiest, walk through the panels to get the job done, that is:

– No space considerations – No security considerations

By specifying 1 on the previous panel, and pressing Enter, you will see the panel in Figure 7-5 to start your first project.

AHXV11 --- IBM DB2 Data Archive Expert for Settings not modified Select Archive Expert Action ==> 1 DB2 system : DB2G Schema : SYSTOOLS User ID : PAOLOR1 0 View and set Archive Expert settings Time : 22:40 1 Work with archive specifications 2 Work with retrieve specifications X Exit IBM* Licensed Materials - Property of IBM 5655-I95 (c) Copyright IBM Corp. 2003 All Rights Reserved.

*Trademark of International Business Machines

Figure 7-5 Data Archive Expert’s Archive Specification List panel

As there is no previous archive specification yet, Data Archive Expert provides the entry

<empty>. Specify N for NEW as the line command in front of this entry. If there were already specifications in the Data Archive Expert system, you could specify the N in front of any of those specifications. Press Enter to proceed to the next panel shown in Figure 7-6.

Figure 7-6 Archive Specification Definition

On this panel, some values are already provided by default, but you must or should specify:

򐂰 The name of the archive specification you are going to define, here SCENARIO1

򐂰 A description for your new archive specification

򐂰 Y for completing the archive run, that is, no pause between the two steps: Copying rows from the source tables to the archive tables or files, and then deleting the same rows from the source tables

򐂰 Y for performing some detection that is not discussed here. For more information on this, see Chapter 12., “Additional archive considerations” on page 233.

Now you must perform three steps (called

definition activities

1. Activity 1: Specifying the archiving source tables, called the archive unit 2. Either activity 2 or 3: Specifying the archiving target, either tables or datasets

In this scenario 1, we performed activity 3 rather than 2, as we wanted to archive into a data set rather than into a table.

3. Activity 4: Saving your definition

Let us finally start with activity 1! Press Enter for the first pull-down menu in Figure 7-7.

Attention_1: Data Archive Expert is case sensitive! It takes your input as it is, so you might prefer to specify the name of the specification in uppercase, as it is done in the figure, although you can work with lower case specification names. Using uppercase is especially important when dealing with DB2 objects like DB2 tables, as many of your DB2 for z/OS data management programs and tools are not able to handle lower case table names.

Attention_2: If you also want to delete the rows in the source table as part of your archive process and if you want to archive to a file (rather than to another table), you cannot split the archiving process into two separate steps: First copying some rows into a file (or data set) and afterwards, after a pause, deleting these rows from the source table

Attention_3: Although you have now specified to delete the rows in the source table after they had been archived, you must specify this deletion request in a later panel again, otherwise the deletion does not take place. Why? The Y on this panel only indicates that you request the deletion to happen right after the copying of the rows; in a later panel you must specify on which specific tables you want the deletion to be performed. As we deal with only one table in this scenario, the reason for requesting the deletion twice is not obvious yet.

Attention_4: In the Specification List panel (see Figure 7-19 on page 108) only the first 21 characters of your description are displayed. For future selections make sure that the distinctive part of your description is already mentioned in these first 21 characters (in contrast to the description you see in this example).

Figure 7-7 Data Archive Expert’s pull-down menu for specifying starting table

In this first pull-down menu, you provide the name of the table from which you want to archive some rows, in our case LINEITEM. The qualifier already defaults to your user ID, which is OK in this scenario.

As you have specified the correct table name already, you do not want a list of all tables with names starting with LINEITEM. Therefore, you specify N for whether to provide a table selection list.

Press Enter for the pull-down menu in Figure 7-8.

Attention again: Data Archive Expert is case sensitive! As your table probably has an uppercase name in your DB2 for z/OS catalog, your input in this pull-down menu should better be in uppercase, otherwise, DB2 issues the message: No tables were found.

AHXV11 --- Archive Specification Definition Command ==>

Archive specification:

Name . . . .==> SCENARIO1 DB2 system . : DB2G Creator . . . .==> Essssssss Specify Starting Point Table ssssssssN Description . .==> e e on) Complete archive ru e Command ==> e Perform orphan row/ e e e Provide table selection list? ==> N (Y/N) e Select an archive def e e e Table name . ==> LINEITEM e 1. Define archive e Creator . . ==> PAOLOR1 e 2. Define table t e Database . . ==> % e 3. Define data se e DB2 system . . : DB2G e 4. Save archive s e e e ( % or blank indicates all ) e e e e e DssssssssssssssssssssssssssssssssssssssssssssssM

Figure 7-8 Data Archive Expert’s pull-down menu for finding related tables

On this second pull-down menu you are asked whether you want Data Archive Expert to look for tables that are referentially connected to your LINEITEM table, as you may want to archive some of their data together with the LINEITEM rows you want to archive. As this is not the case in our first scenario, enter N and press Enter for the panel in Figure 7-9.

Figure 7-9 Archive Unit Definition

Attention (repetition): On this panel you see that the DEL rule for table LINEITEM is set to N per default. As you want to delete the inactive rows from this table once they have been archived, you must request to change this rule by specifying r as the line command in front of this table.

Now press Enter for the panel in Figure 7-10.

Figure 7-10 Data Archive Expert’s pull-down menu for specifying rules

In this pull-down menu you specify Y for deleting the rows in the original source table after they have been archived. Then press Enter again, now you can observe that the DEL rule has changed to Y for your table, as shown in Figure 7-11.

Figure 7-11 Request to specify a row filter

Up to now you have specified which tables are subject of you archive specification, and whether archived rows should be deleted.

Now you proceed in your archive specification from the table level to the row level. In other words, you can specify how to determine which rows are considered inactive and should be archived. This is done in Data Archive Expert through a so called row filter, which Data Archive Expert converts to a WHERE clause when it accesses DB2. To let Data Archive Expert generate such a WHERE clause, specifyW as line command in front of your table, and press Enter for the panel in Figure 7-12.

Figure 7-12 Data Archive Expert’s panel for specifying a row filter - Part 1

Press F8 to see the table columns shown in Figure 7-13. We were interested in L_SHIPDATE.

Now specify your archiving criteria, here: L_SHIPDATE < '1992-02-01' to archive all rows from January 1992. In our test data there were no rows for the years before 1992.

AHXV11 --- Starting Point Table Row Filter --- Row 1 of 16 Command ==> Scroll ===> CSR Archive specification : SCENARIO1 DB2 system: DB2G Starting point table: LINEITEM Creator . . . : PAOLOR1 Row filter ==> _______________________________________________________________

_______________________________________________________________________________

_______________________________________________________________________________

_______________________________________________________________________________

Columns Num Type Length Scale --- --- --- --- --- L_ORDERKEY 1 INTEGER 4 0 L_PARTKEY 2 INTEGER 4 0 L_SUPPKEY 3 INTEGER 4 0 L_LINENUMBER 4 INTEGER 4 0 L_QUANTITY 5 INTEGER 4 0 L_EXTENDEDPRICE 6 FLOAT 4 0 L_DISCOUNT 7 FLOAT 4 0 L_TAX 8 FLOAT 4 0 L_RETURNFLAG 9 CHAR 1 0 L_LINESTATUS 10 CHAR 1 0

Figure 7-13 Data Archive Expert’s panel for specifying a row filter - Part 2

Press Enter to return to the panel in Figure 7-14, which displays the first part of your selection criteria for this table.

Figure 7-15 Request to define data set targets

As definition activity 1. Define Archive Unit is now completed, you can proceed to the second activity for your archive specification. As this scenario deals with data set archives (rather than table archives, which are discussed in the next scenario) enter 3 on the panel, and press Enter, which leads you to the panel in Figure 7-16.

Figure 7-16 Selecting Data Archive Expert’s default data set generation

In a production environment, the usage of templates (option 3) is recommended. How to use templates is explained in “File archive in batch using a template” on page 51.

But here, as this is an introductory scenario, accept the default 1 on the panel and let Data Archive Expert create a data set for you according to its own default naming and space conventions. You can see the data set name Data Archive Expert generates in Figure 7-22 on page 109.

Pressing Enter just gives you the acknowledging message shown in Figure 7-17.

Figure 7-17 Data Archive Expert’s confirmation of data set specifications F3 brings you back to the definition activity menu shown in Figure 7-18.

Figure 7-18 Request to save the archive specification

Now you can proceed to the last activity. Select 4 and press Enter. You will see the confirmation message on the panel in Figure 7-19.

Figure 7-19 Data Archive Expert’s confirmation message ‘Specification saved’

You have now created a new archive specification SCENARIO1. This new archive

specification is only defined (State = DEF), but not yet completed (that is, not yet executed), and it is going to archive to a data set (Type = FILE) rather than to a table.