All Forums Marketing & CRM
wannabet 1 post Joined 04/08
14 Apr 2008
how tos of CRM ios schd jobs

Does anyone have a script that can be used to purge old CRM job executions form the IOS_SCHD_JOBS, IOS_SCHD_TASK, and IOS_SCHD_TASKS tables. Our environment is getting pretty crowded after over a year of testing and production job executions. So crowded that when my users login to CRM they hang at the processing completed jobs step of the login.

jrs 3 posts Joined 03/07
17 Apr 2008

There is a script defined the the maintenance guide in section 12.3.8.9 (TCRM5201_MaintOp.pdf)The taskbroker has an automatic option defined on page 4-11 of the same manual : "To delete jobs and tasks"

jrs 3 posts Joined 03/07
17 Apr 2008

Here is something that you are probably already doing, but is worth mentioning. Collecting stats on the tables used by the Task broker is also important. I don't recommend doing this while the task broker is up and running, because this can impact task broker polling issues.COLLECT STATS ON IOS_APP COLUMN APP_ID;COLLECT STATS ON IOS_APP COLUMN DIV_ID;COLLECT STATS ON IOS_APP INDEX (DIV_ID ,APP_ID);COLLECT STATS ON IOS_APP_SVR_REG COLUMN APP_SERV_ID;COLLECT STATS ON IOS_APP_SVR_REG COLUMN APP_SERV_TYP;COLLECT STATS ON IOS_APP_SVR_REG INDEX (APP_SERV_ID ,APP_SERV_TYP);COLLECT STATS ON IOS_PARENT_TASK COLUMN DIV_ID;COLLECT STATS ON IOS_PARENT_TASK COLUMN JOB_ID;COLLECT STATS ON IOS_PARENT_TASK COLUMN PARENT_TASK_ID;COLLECT STATS ON IOS_PARENT_TASK COLUMN TASK_ID;COLLECT STATS ON IOS_PARENT_TASK INDEX (JOB_ID ,TASK_ID ,DIV_ID ,PARENT_TASK_ID);COLLECT STATS ON IOS_SCHD_JOBS COLUMN (JOB_ID,DIV_ID) ;COLLECT STATS ON IOS_SCHD_JOBS COLUMN DIV_ID;COLLECT STATS ON IOS_SCHD_JOBS COLUMN JOB_ID;COLLECT STATS ON IOS_SCHD_JOBS INDEX (JOB_ID ,DIV_ID);COLLECT STATS ON IOS_SCHD_JOB_DAYS COLUMN DIV_ID;COLLECT STATS ON IOS_SCHD_JOB_DAYS COLUMN JOB_DT;COLLECT STATS ON IOS_SCHD_JOB_DAYS COLUMN JOB_ID;COLLECT STATS ON IOS_SCHD_JOB_DAYS COLUMN XCPT_CD;COLLECT STATS ON IOS_SCHD_JOB_DAYS INDEX (JOB_ID ,DIV_ID ,XCPT_CD ,JOB_DT);COLLECT STATS ON IOS_SCHD_TASK COLUMN (JOB_ID,TASK_ID,DIV_ID) ;COLLECT STATS ON IOS_SCHD_TASK COLUMN (SCHD_TASK_STAT_CD) ;COLLECT STATS ON IOS_SCHD_TASK COLUMN DIV_ID;COLLECT STATS ON IOS_SCHD_TASK COLUMN JOB_ID;COLLECT STATS ON IOS_SCHD_TASK COLUMN TASK_ID;COLLECT STATS ON IOS_SCHD_TASK INDEX (JOB_ID ,TASK_ID ,DIV_ID);COLLECT STATS ON IOS_SCHD_TASKS COLUMN (JOB_ID,DIV_ID) ;COLLECT STATS ON IOS_SCHD_TASKS COLUMN (JOB_ID,DIV_ID,TASK_ID,SUBMITTED_TIME) ;COLLECT STATS ON IOS_SCHD_TASKS COLUMN (JOB_ID,TASK_ID,DIV_ID) ;COLLECT STATS ON IOS_SCHD_TASKS COLUMN DIV_ID;COLLECT STATS ON IOS_SCHD_TASKS COLUMN JOB_ID;COLLECT STATS ON IOS_SCHD_TASKS COLUMN SUBMITTED_TIME;COLLECT STATS ON IOS_SCHD_TASKS COLUMN TASK_ID;COLLECT STATS ON IOS_SCHD_TASKS INDEX (JOB_ID ,TASK_ID ,DIV_ID ,SUBMITTED_TIME);COLLECT STATS ON IOS_JOB_RUNS COLUMN DIV_ID;COLLECT STATS ON IOS_JOB_RUNS COLUMN JOB_ID;COLLECT STATS ON IOS_JOB_RUNS COLUMN SUBMITTED_TIME;COLLECT STATS ON IOS_JOB_RUNS INDEX (JOB_ID ,DIV_ID ,SUBMITTED_TIME);COLLECT STATS ON IOS_SQL_ALL_PARAMS COLUMN DIV_ID;COLLECT STATS ON IOS_SQL_ALL_PARAMS COLUMN PARAM_ORD;COLLECT STATS ON IOS_SQL_ALL_PARAMS COLUMN PARAM_VAL_CD;COLLECT STATS ON IOS_SQL_ALL_PARAMS COLUMN SEL_TYP_CD;COLLECT STATS ON IOS_SQL_ALL_PARAMS INDEX (DIV_ID ,SEL_TYP_CD ,PARAM_ORD ,PARAM_VAL_CD);COLLECT STATS ON IOS_SQL_FLDS COLUMN SQL_FLD_ID;COLLECT STATS ON IOS_SQL_FLDS COLUMN SQL_ID;COLLECT STATS ON IOS_SQL_FLDS INDEX (SQL_ID ,SQL_FLD_ID);COLLECT STATS ON IOS_SQL_MAST INDEX (SQL_ID);COLLECT STATS ON IOS_SQL_PARAMS COLUMN PARAM_ID;COLLECT STATS ON IOS_SQL_PARAMS COLUMN SQL_ID;COLLECT STATS ON IOS_SQL_PARAMS INDEX (SQL_ID ,PARAM_ID);COLLECT STATS ON IOS_SQL_QRY COLUMN DIV_ID;COLLECT STATS ON IOS_SQL_QRY COLUMN ORD_ID;COLLECT STATS ON IOS_SQL_QRY COLUMN TASK_ID;COLLECT STATS ON IOS_SQL_QRY INDEX (TASK_ID ,DIV_ID ,ORD_ID);COLLECT STATS ON IOS_SQL_RUN_PARAMS COLUMN DIV_ID;COLLECT STATS ON IOS_SQL_RUN_PARAMS COLUMN LINK_ID;COLLECT STATS ON IOS_SQL_RUN_PARAMS COLUMN PARAM_ID;COLLECT STATS ON IOS_SQL_RUN_PARAMS COLUMN STAT_DET_ID;COLLECT STATS ON IOS_SQL_RUN_PARAMS COLUMN STAT_MAST_ID;COLLECT STATS ON IOS_SQL_RUN_PARAMS INDEX (DIV_ID ,LINK_ID ,STAT_MAST_ID ,STAT_DET_ID ,PARAM_ID);COLLECT STATS ON IOS_STATUS_DET COLUMN DIV_ID;COLLECT STATS ON IOS_STATUS_DET COLUMN LINK_ID;COLLECT STATS ON IOS_STATUS_DET COLUMN STAT_DET_ID;COLLECT STATS ON IOS_STATUS_DET COLUMN STAT_MAST_ID;COLLECT STATS ON IOS_STATUS_DET INDEX (DIV_ID ,LINK_ID ,STAT_MAST_ID ,STAT_DET_ID);COLLECT STATS ON IOS_STATUS_DET INDEX (STAT_DET_ID);COLLECT STATS ON IOS_STATUS_MAST COLUMN DIV_ID;COLLECT STATS ON IOS_STATUS_MAST COLUMN LINK_ID;COLLECT STATS ON IOS_STATUS_MAST COLUMN STAT_MAST_ID;COLLECT STATS ON IOS_STATUS_MAST INDEX (DIV_ID ,LINK_ID ,STAT_MAST_ID);COLLECT STATS ON IOS_TASK_BROKER_POLL COLUMN EXEC_TIME;COLLECT STATS ON IOS_TASK_BROKER_POLL COLUMN IP_ADDRESS;COLLECT STATS ON IOS_TASK_BROKER_POLL INDEX (IP_ADDRESS ,EXEC_TIME);COLLECT STATS ON IOS_TASK_SVR_ASGN COLUMN (JOB_ID,DIV_ID,TASK_ID,SUBMITTED_TIME) ;COLLECT STATS ON IOS_TASK_SVR_ASGN COLUMN DIV_ID;COLLECT STATS ON IOS_TASK_SVR_ASGN COLUMN JOB_ID;COLLECT STATS ON IOS_TASK_SVR_ASGN COLUMN SUBMITTED_TIME;COLLECT STATS ON IOS_TASK_SVR_ASGN COLUMN TASK_ID;COLLECT STATS ON IOS_TASK_SVR_ASGN INDEX (DIV_ID ,JOB_ID ,TASK_ID ,SUBMITTED_TIME);COLLECT STATS ON IOS_TASK_TYPS INDEX (TASK_TYP_ID);COLLECT STATS ON IOS_SYS_BATCH_SCHD COLUMN DIV_ID;COLLECT STATS ON IOS_SYS_BATCH_SCHD COLUMN SCHD_TYP_CD;COLLECT STATS ON IOS_SYS_BATCH_SCHD INDEX (DIV_ID ,SCHD_TYP_CD);

You must sign in to leave a comment.