Saturday, April 6, 2024

SMF Records for Db2 extract using REXX 


/* REXX program to parse SMF record type 100 and load data to Db2 table */


/* Define Db2 connection details (replace with your actual values) */

"/*+ db2 connection details */"

    userid 'YOUR_DB2_USER'

    password 'YOUR_DB2_PASSWORD'

    schema 'YOUR_SCHEMA'

    /* Replace above with your Db2 connection information */


/* Define sample SMF record type 100 data (replace with actual data) */

smf_data = "100  DB2     D                 BUFPOOL          NAME=BP0     READS=1000 WRIT

ES=200         FREE=8000  ..."  /* Truncated data for brevity */


/* Split SMF record into fields based on positions (adjust based on actual format) */

/* Field positions might vary depending on Db2 version and SMF configuration */

record_type = smf_data substring 1 3

subsystem_id = smf_data substring 5 8

source = smf_data substring 10 1

statistic_type = smf_data substring 12 4

statistic_name = translate(smf_data substring 17 14, 'NAME=', '')

data_value1 = smf_data substring 32 5

data_value2 = smf_data substring 38 5

/* ... Add additional data_value fields based on your SMF record format ... */


/* Build dynamic SQL INSERT statement */

insert_sql = "INSERT INTO YOUR_TABLE (RECORD_TYPE, SUBSYSTEM_ID, SOURCE, STATISTIC_TYPE, STATISTIC_NAME, DATA_VALUE1, DATA_VALUE2, /* Add more columns as needed */) VALUES (" ||

             record_type || ',' ||

             '"' || subsystem_id || '"' || ',' ||

             '"' || source || '"' || ',' ||

             '"' || statistic_type || '"' || ',' ||

             '"' || statistic_name || '"' || ',' ||

             data_value1 || ',' ||

             data_value2 || /* Add more comma-separated values for additional data fields */

             ')'


/* Connect to Db2 */

call sql CONNECT TO USER userid password password schema schema


/* Execute SQL statement to insert data */

call sql EXECUTE IMMEDIATE insert_sql


/* Disconnect from Db2 */

call sql DISCONNECT DATABASE


/* Informational message */

say 'SMF record 100 data parsed and loaded successfully!'