Extracting Data

As a CA TPX administrator you want to create reports or update records for a specific subset of data. Use the EXTRACT statement to retrieve data from the CA TPX administration files and write it to an extract file. Use extract files to create reports and update records.
tpxsm
As a CA TPX administrator you want to create reports or update records for a specific subset of data. Use the EXTRACT statement to retrieve data from the CA TPX administration files and write it to an extract file. Use extract files to create reports and update records.
For more information about how to use extract files for updating and reporting, see Using Extract Files for Updating and Using Extract Files for Reporting.
You can also use extract files with report generators such as SAS. For information about the format of extract files, see Extract File Layout.
2
Extract File Contents
CA TPX writes the data that you specified in the EXTRACT statement to an extract file. You can pull the following types of records to an extract file:
  • User definitions
  • Profile definitions
  • Session definitions associated with users or profiles
  • Application definitions
  • Mail locators
User Definitions
When you extract user records, Batch uses the same process to determine the values of the user options fields as when the user signs on to CA TPX. Batch determines the values for fields by merging the values for the fields from the system, profile, and user levels during sign on.
CA TPX merges the values for the user parameters in the following order:
  1. At the 
    system level
    , the product gets the system default values from the SMRT and STXT tables. These values apply to all users.
  2. At the 
    profile level
    , the product gets the profile default values from the profiles assigned to the user. If more than one profile is assigned to the user then values from the first appropriate profile are used. Some values are defined at both the system level and profile level. If a profile value is different from the system value, the profile value overrides the system value.
  3. At the 
    user level
    , the product gets the user values assigned to the user by a user administrator or by the user during self-maintenance. If a user value is different from a value set at the profile or system level, the user value overrides the other values.
Batch uses this same process to construct a user definition. User options values in an extract file or report are the result of merging the system defaults, profile overrides, and user overrides for each field.
Profile Definitions
CA TPX does not merge profile definitions. The profile definition values in an extract file are the options assigned for that profile.
Session Definitions
Batch constructs session definitions in a similar way to user definitions. CA TPX gathers the values for application session definitions in the following order:
  1. At the 
    system level
    , the product gets the system default values from the system option table (the SMRT for CA TPX). These values apply to all users.
  2. At the 
    application level
    , the product gets the application default values defined for the particular application in the application characteristics table (the ACT for CA TPX). These values apply to all users using this application.
  3. At the 
    profile level
    , the product gets the profile session default values from whatever profiles are assigned to the user. If more than one profile session with the same ID is assigned to the user, values from the first profile are used. Values from subsequent profiles can override values from the first profile. Some values are defined at both the system level and profile level. If a profile value is different from the system value, the profile value overrides the system value.
  4. At the 
    user level
    , the product gets the user session values assigned to the user by a user administrator or by the user during self-maintenance. If a user value is different than a value set at the profile or system level, the user value overrides the other values.
Application Definitions
You can extract the application definition records and use them for reports, updating, or deleting.
Mail Locators
Mail locators create an association between a mail message text and the users who have that message in their mailbox. The message text exists once, but has a mail locator for each mailbox that contains the message.
You can extract the mail locators, and then use the DELETE MAILMESSAGE command to delete the locators. When a mail locator is deleted, the message is removed from that user's mailbox.
EXTRACT Statement Syntax
The EXTRACT statement has the following syntax:
->>-- EXTRACT -- GIVING(ddname) ---- USER -------------+- AND ALL SESSIONS -------->                                  |-- PROFILE -----+    |- AND MATCHING SESSIONS -|                                  |                     +- AND NO SESSIONS -------|                                  |-- MAILMESSAGE --------------------------------|                                  |-- USERLIST -----------------------------------|                                   +-- ACT ----------------------------------------+                                  |-----------------------------------------------+                                   |  |-------------+                              |                                  |  V             V                              |  ->---------------(--- variable( ---- value ----------)--------------------)-------><
If your batch job contains more than one EXTRACT statement, the extract file must specify the DISP=MOD parameter. DISP=MOD prevents each EXTRACT statement from overwriting the previous one.
EXTRACT Statement Operands
The EXTRACT statement has the following operands:
  • ddname
    Specifies the data definition name (DDname) of the output extract file. The data that is extracted from the administration database is written to this file.
    Because the program controls the DCB information, you do not need to specify it. The maximum record size that you can extract is equal to the size of the ADMIN2 file. To calculate the space requirements of the extract file, consider the record size, expected record count, and media type of the records you plan to extract. JCL controls the final disposition of the data set.
  • USER
    Specify USER to extract user records.
  • PROFILE
    Specify PROFILE to extract profile records.
  • MAILMESSAGE
    Specify MAILMESSAGE to extract mail locators.
  • USERLIST
    Specify USERLIST to extract user lists.
  • ACT
    Specify ACT to extract application definition records.
  • AND ALL SESSIONS
    Indicates that all the session records associated with each user or profile record are extracted, regardless of the extraction criteria that you specify. Sessions associated with a profile are extracted even if the session was previously deleted from the user. (Use the UENTDEL variable to determine if the session has been deleted.)
  • AND MATCHING SESSIONS
    Indicates that only sessions that match extraction criteria that contains a session value are extracted. If you specify AND MATCHING SESSIONS and the extraction criteria does not contain a session variable, no sessions are extracted.
  • AND NO SESSIONS
    None of the session records associated with each user or profile record are extracted, even if the extraction criteria contain a session variable.
  • variable
    Specifies the name of any valid user, profile, or session variable. These variables represent the fields on the online administration panels. You can specify as many variables as you need, but CA TPX only extracts a record if it matches all the criteria variables. For a list of the variables that you can use and the corresponding online administration fields, see Batch Variables.
    You can also use one of the following variables. These variables allow you to extract users, sessions, or profiles according to what component they are associated with. These variables can have a value of Y or N.  You can specify as many values as you need. Records are extracted if the specified variable contains one of the specified values. For more information on specifying variables and values, see Specify Extraction Criteria.
    • VUSR$TPX(
      value
      )
      Selects the user if the user has signon authority.
    • WUSR$TPX(
      value
      )
      Selects the profiles.
    • VSES$TPX(
      value
      )
      Selects the user sessions.
    • WSES$TPX(
      value
      )
      Selects the profile sessions.