Working with IBM SMF Custom Tables

How to Load IBM SMF Data into User-Defined Tables

IBM SMF Custom Tables

SAS IT Resource Management provides support for more than 1,100 tables in the IBM SMF adapter. However, it does not support every table that MXG can produce from IBM SMF data. For those instances, you can extend the functionality of the IBM SMF staging transformation to load data into user-defined tables for which SAS IT Resource Management does not provide a template. These are called IBM SMF custom tables.

To create an IBM SMF custom table, you must create (or register) metadata about the physical MXG data set or table. You can use the New Table wizard to do so. The physical table must exist first and it must contain a column (variable) called DATETIME to enable it to be added to a user-defined table. If the default definition for the MXG data set does not contain DATETIME, it can be added simply by using MXG exits. This process is documented in the first step of the following task. Once the MXG data set is registered, it can be added as an output table to the IBM SMF staging transformation. It can also subsequently be used as input to an Aggregation transformation.

How to Load IBM SMF Data into User-Defined Tables

To load IBM SMF data into user-defined tables for which SAS IT Resource Management does not provide a template, perform the following steps:

If your MXG data set (with the DATETIME variable present) already exists as a physical table, skip this first step.
Otherwise, create it now using regular MXG coding techniques. If a DATETIME variable is not defined for the MXG data set, you must identify the variable that contains the datetime stamp for each event or interval that the data represents. Then, modify the EX exit member that contains the OUTPUT statement for that table to also contain an assignment statement of the form:
```
DATETIME=mxg_variable;
```
You must also add the DATETIME variable to the output data set's keep list. To do so, add it to the _Kdddddd. For example:
```
 %MACRO _KTY1415 DATETIME
%
```
The preceding example adds the DATETIME variable to the keep list for the TYPE1415 table. Look up the correct names for the EX member and the _Kdddddd macro for your data source in the MXG documentation.
Because you do not want the SAS IT Resource Management staging transformation to write to this copy of the MXG data set, make a copy of the physical MXG data set. You can put the copy in any writable library, such as the Staged library of your IT data mart.
Modify the MXG_PROPS_SMF format that maps the name of the tables to the MXG code that has to be executed. Locate the source for this format in your SASHELP.ITMS_FORMATS catalog. In the format, the value to be formatted is the MXG data set name, and the resulting label is a string of MXG code members and macro names. Add a new line to the format containing your MXG data set name and the name of the MXG code and macros using what is there as a template. Make sure that the text strings start in the same columns. When you execute PROC FORMAT, revise the LIBRARY= parameter in the PROC statement to ensure that the output catalog in the PROC FORMAT statement is pointing to FORMATS catalog in the Admin library. This revision of the LIBRARY= parameter creates the format in your Admin library that precedes the SASHELP.ITMS_FORMATS in your FMTSEARCH SAS option. Therefore, this format is used instead of the one supplied at installation time.
Note: Your own formats can be put in the FORMATS catalog in the Admin library or in your own catalog. If you use a different catalog, you need a LIBNAME statement for that library. You then need to add to the FMTSEARCH option to point to your formats. For example:
```
options insert=(fmtsearch=(mylib.formats);
```
(For information about the INSERT option, see SAS 9.4 System Options: Reference.)
The task of allocating the Admin library requires you to have Update access to it. The allocation can be achieved only when no other SAS IT Resource Management ETL jobs are running against this IT data mart. You must execute the following statement in a SAS session:
```
libname admin "path_of_Admin_library";  
```
where "path_of_ADMIN_library" can be found in your SAS IT Resource Management or SAS logs, or by querying the ADMIN library of your IT data mart within SAS IT Resource Management.
Start SAS Data Integration Studio and use the Register Tables wizard from the File menu to register your MXG data set as a user-defined table.

To register your MXG data set as a user-defined table, perform the following steps:
1. On the first page of the Register Tables wizard, select SAS as the source type.
2. In the Library field, select the library that contains your MXG data set. If you copied your MXG data set to a library that has already been defined in metadata, select the appropriate library from the SAS Library list. If you copied it to a library that has not yet been defined in metadata, and the library is not in the list, then you must define a new SAS Library in metadata. You can do so by selecting New.
3. On the following screen, select the MXG data sets that you want to import metadata from.
4. Choose a folder to store the metadata. It should be a folder in the same IT data mart that contains the job to which you want to add this table.
5. Select Finish on the confirmation screen to import the metadata. The MXG data sets should appear in the folder that you selected in the previous step.
  
  Note: A slightly different icon is associated with the table because it is not backed by a SAS IT Resource Management staged table template.
Add the new user-defined table as an extra output of the IBM SMF staging transformation in your existing jobs. To do so, perform the following steps:
1. Double-click the appropriate job. It appears on the Diagram tab of the Job Editor window.
2. In the Transformations tree of SAS IT Resource Management, expand the SAS IT Resource Management folder and locate the IBM SMF staging transformation. Drop that staging transformation onto the Job Editor window. The staging transformation appears in the process flow diagram for the job.
3. Right-click the IBM SMF staging transformation and select Add output.
4. Drag the new user-defined table from the IT data mart to the drop zone that now appears on the process flow diagram.
5. Save the job.
Run the job or deploy it for subsequent batch execution, as you would any other SAS IT Resource Management job.