Statistic for graphing the return time of a Siebel Client query

This VA2 rule executes a SQL Query the same way a client would execute it from Siebel.  The SQL Generated is taken from a Siebel Client when SQL Logging was spooled, and then added to VA2 and executed every 5 minuets.  VA2 can be used to graph the timing of any ODBC compliant SQL query.

Query generation

Perl code for generating statistic:

use sqlanalyze;

my $sql =<<SQLEND;

SELECT

      T12.PR_POSTN_ID,

      T12.LAST_UPD,

      T12.PR_REP_SYS_FLG,

      T12.X_FORMS_DUE_DATE,

      T6.LAST_NAME,

      T12.X_AGENCY_ID,

      T8.UNDER_AGE_FLG,

      T2.FST_NAME,

      T9.X_ADDRESS_CHANGE,

      T9.MAIN_PH_NUM,

      T4.CREATED,

      T12.CREATED_BY,

      T7.ROW_ID,

      T4.MODIFICATION_NUM,

      T12.REFERRAL_NAME,

      T4.ATTRIB_14,

      T12.START_DT,

      T12.X_AGENCY_CODE,

      T8.CONFLICT_ID,

      T8.LAST_UPD_BY,

      T12.X_LOCK_REC__FLG,

      T12.PREMIUM_DT,

      T12.CONFLICT_ID,

      T8.PAR_ROW_ID,

      T4.PAR_ROW_ID,

      T3.PR_CON_ID,

      T10.ROW_ID,

      T12.PREMIUM,

      T12.PR_REP_MANL_FLG,

      T10.LAST_NAME,

      T10.X_MID_NAME,

      T12.INTEGRATION_ID,

      T8.CREATED,

      T12.CREATED,

      T12.OWNER_ACCNT_ID,

      T12.ASSET_NUM,

      T12.PR_GRP_OU_ID,

      T12.ROW_ID,

      T12.PR_CON_ID,

      T12.PR_DISCNT_ID,

      T9.ROW_ID,

      T9.GROUP_NAME,

      T12.RATE_STATE,

      T12.TYPE_CD,

      T12.REF_NUMBER_1,

      T12.X_NEW_PAYMT_ROW_CREATED,

      T12.X_FOLLOW_UP_REQUIRED_FLG,

      T12.X_INS_CARRIER_CODE,

      T12.X_POLICY_REN_FLAG,

      T4.LAST_UPD_BY,

      T12.X_FORMS_COMPLETED_DATE,

      T12.X_POLICY_HISTORY_FLAG,

      T12.X_MANUAL_ENTRY,

      T5.ROW_ID,

      T8.PEND_CANCEL_FLG,

      T12.X_AGENCY_NAME,

      T12.CURCY_CD,

      T5.ROW_ID,

      T12.X_CARRIER_ID,

      T8.MODIFICATION_NUM,

      T4.LAST_UPD,

      T12.MODIFICATION_NUM,

      T12.SUB_STATUS_CD,

      T2.LAST_NAME,

      T10.X_CREDIT_RPT_FLAG,

      T12.X_FORMS_RESEND_FLG,

      T12.TERM_CD,

      T9.NAME,

      T12.X_NEW_QUOTE_FLG,

      T1.LAST_NAME,

      T12.STATUS_CD,

      T11.NAME,

      T8.CREATED_BY,

      T8.LAST_UPD,

      T10.FST_NAME,

      T12.X_ALL_NAMED_INS,

      T11.X_CARRIER_SAP_CRC,

      T12.END_DT,

      T12.OWNER_CON_ID,

      T4.ATTRIB_40,

      T12.LAST_UPD_BY,

      T3.GROUP_NAME,

      T12.X_PIP_COV_PROG,

      T12.INCEPTION_DT,

      T5.NAMED_INS_FLG,

      T12.SUB_TYPE_CD,

      T8.ROW_ID,

      T4.CONFLICT_ID,

      T11.X_CARRIER_SAP_CRV,

      T4.CREATED_BY,

      T1.FST_NAME,

      T4.ROW_ID,

      T10.PER_TITLE_SUFFIX,

      T12.X_FORMS_COMPLETED_FLG,

      T3.MAIN_PH_FAX_NUM,

      T6.FST_NAME

   FROM

       SIEBEL.S_CONTACT T1,

       SIEBEL.S_EMPLOYEE T2,

       SIEBEL.S_ORG_GROUP T3,

       SIEBEL.S_ASSET_X T4,

       SIEBEL.S_ASSET_CON T5,

       SIEBEL.S_CONTACT T6,

       SIEBEL.S_ASSET_ORGGRP T7,

       SIEBEL.S_FN_ACCNT2_FNX T8,

       SIEBEL.S_ORG_GROUP T9,

       SIEBEL.S_CONTACT T10,

       SIEBEL.S_ORG_EXT T11,

       SIEBEL.S_ASSET T12

   WHERE

      T12.X_CARRIER_ID = T11.ROW_ID (+) AND

      T3.PR_CON_ID = T6.ROW_ID (+) AND

      T12.CREATED_BY = T2.ROW_ID (+) AND

      T12.PR_GRP_OU_ID = T3.ROW_ID (+) AND

      T12.OWNER_CON_ID = T1.ROW_ID (+) AND

      T12.ROW_ID = T4.PAR_ROW_ID (+) AND

      T12.ROW_ID = T8.PAR_ROW_ID (+) AND

      T12.PR_CON_ID = T10.ROW_ID (+) AND

      T12.PR_CON_ID = T5.CONTACT_ID (+) AND T12.ROW_ID = T5.ASSET_ID (+) AND

      T12.PR_GRP_OU_ID = T9.ROW_ID (+) AND

      T12.PR_GRP_OU_ID = T7.ORG_GROUP_ID (+) AND T12.ROW_ID = T7.ASSET_ID (+) AND

      (T12.SUB_TYPE_CD = 'MOTORCYCLE POLICY' AND (T12.STATUS_CD = 'QUOTE' OR T12.STATUS_CD = 'ACTIVE')) AND

      ((T12.STATUS_CD LIKE 'ac\%' OR T12.STATUS_CD LIKE 'Ac\%' OR T12.STATUS_CD LIKE 'aC\%' OR T12.STATUS_CD LIKE 'AC\%') AND UPPER(T12.STATUS_CD) = UPPER('ACTIVE'))

SQLEND

$retval = sqlanalyze->sqltimer($sql,$datasource{siebeldata});