OWB 10gR2 - Query sul catalogo

Oggi rispolveriamo un altro dinosauro in via di estinzione OWB 10gR2 (Oracle Warehouse Builder) e vediamo alcune query da utilizzare per estrarre informazioni relativamente al runtime oppure alle locations.

Come per le query dei cursori visti qualche giorno fa io le elenco di seguito, poi le approfondite voi io non ve le spiego.

• Info Locations

SELECT location.name           NAME

      ,location.description 

      ,location.loctypeversion version

      ,location.loctype

      ,location.elementid

      ,prop.logicalname

      ,prop.value              VALUE

      ,prop.createdby

      ,row_number () over (partition by location.name order by location.name) num_rec

  FROM cmplogicallocation_v     location

      ,cmpstringpropertyvalue_v prop

 WHERE location.elementid = prop.propertyowner;

• Analisi Esecuzioni

select

 o.object_name "Object Name",

 o.object_type "Type",

 eo.created_on "Created On"

from all_rt_objects o,

  (select

     e.object_uoid as euoid,

     max(created_on) created_on

   from

     all_rt_audit_executions e

   group by e.object_uoid) eo

where

  eo.euoid=o.object_uoid order by object_name;

• Process Flows

select distinct object_name PROCESS_NAME from all_rt_tasks  where task_type='ProcessFlow' order by object_name;

• Process Flows Attivi

select p.execution_audit_id "Audit ID",c.task_name "Activity Name",

c.execution_audit_id "Activity ID", c.parent_execution_audit_id "Parent ID",

c.exec_location_name "Location", 

c.return_result "Result", ml.message_text "Audit Text"

from all_rt_audit_executions c, all_rt_audit_executions p,

all_rt_audit_exec_messages ml

where c.parent_execution_audit_id=p.execution_audit_id and

p.object_name=:PROCESS_NAME and

p.object_type='ProcessFlow' and

ml.execution_audit_id(+)=c.execution_audit_id

order by p.created_on DESC, c.execution_audit_id DESC;

• Process Flows Overview

select to_char(p.created_on, 'HH:MM:SS DD-MON-YY') "Exec Date",p.execution_audit_id "Audit ID", p.return_Result "Result"

from  all_rt_audit_executions p

where p.object_name=:PROCESS_NAME and

object_type='ProcessFlow' 

order by p.created_on DESC, p.execution_audit_id DESC;

• Parametri di esecuzione

select  execution_audit_id "Audit Id", parameter_name "Parameter", parameter_type "Type", value "Value" from all_rt_audit_execution_params

where execution_audit_id in (

  select distinct execution_audit_id from all_rt_audit_executions

  where object_name=:PROCESS_NAME and

  object_type='ProcessFlow')

order by execution_audit_id DESC, parameter_audit_id ASC;

• Mapping

select distinct object_name MAPPING_NAME from all_rt_tasks  where (object_type='SQLLoaderControlFile' or object_type='ABAPFile' or object_type = 'PLSQLMap' or object_type = 'Mapping') order by object_name;

• Throughput

select r.start_time startd, mr.map_name "Map Name"

, t.target_name "Target"

,r.step_id "Step"

, to_char(r.start_time,'DD-MON hh24:mi') "Exec Date"

, to_char(r.end_time,'DD-MON hh24:mi') "End"

, to_char(trunc(r.elapse_time/60))

|| 'm '

|| to_char(r.elapse_time - 60 * trunc(r.elapse_time/60))

|| 's' "Exec Time"

, substr(r.step_type,1,3) "Type"

, nvl(r.number_records_merged,0)+nvl(r.number_records_inserted,0)+nvl(r.number_records_updated,0) "No Records"

, decode( nvl(r.elapse_time,0)

, 0, 'N/A'

, to_char(trunc((nvl(r.number_records_merged,0)+nvl(r.number_records_inserted,0)+nvl(r.number_records_updated,0)) / r.elapse_time , 2))

) ||' Per Second' "Throughput"

from all_rt_audit_map_runs mr

, all_rt_audit_step_runs r

, all_rt_audit_step_run_targets t

where (mr.map_name=:MAPPING_NAME or substr(mr.map_name, 2, length(mr.map_name)-2) = :MAPPING_NAME)

and mr.map_run_id = r.map_run_id

and mr.map_run_id = t.map_run_id

and r.step_id = t.step_id

and r.run_status = 'COMPLETE'

order by startd DESC;

• OwbErr

SELECT ALL_IV_RUN_ERROR.ERROR_MESSAGE

, ALL_IV_MAP_RUN.MAP_NAME

, ALL_IV_RUN_ERROR.RUN_TARGET_NAME

, to_char(ALL_IV_MAP_RUN.START_TIME,'dd-mon-yyyy hh24:mi:ss')

, ALL_IV_MAP_RUN.NUMBER_ERRORS

, ALL_IV_MAP_RUN.NUMBER_RECORDS_DELETED

, ALL_IV_MAP_RUN.NUMBER_RECORDS_DISCARDED

, ALL_IV_MAP_RUN.NUMBER_RECORDS_INSERTED

, ALL_IV_MAP_RUN.NUMBER_RECORDS_SELECTED

, ALL_IV_MAP_RUN.NUMBER_RECORDS_UPDATED

FROM DC_PRIMO_LIV.ALL_IV_MAP_RUN ALL_IV_MAP_RUN

, DC_PRIMO_LIV.ALL_IV_RUN_ERROR ALL_IV_RUN_ERROR

WHERE ( ( ALL_IV_MAP_RUN.MAP_RUN_ID = ALL_IV_RUN_ERROR.MAP_RUN_ID ) ) 

AND ( UPPER(ALL_IV_MAP_RUN.MAP_NAME) LIKE UPPER('%DPPS%') )

-- AND (UPPER( ALL_IV_RUN_ERROR.ERROR_MESSAGE) LIKE '%ANALYZE%')

AND ( ALL_IV_MAP_RUN.START_TIME >= '27/04/2016' )

ORDER BY ALL_IV_MAP_RUN.START_TIME;

E potremmo continuare cosi per un bel pò.

Se a qualcuno possono servire basta che mi contatti e  mando l'xml da importare come Report di SqlDeveloper.

Di seguito un elenco delle query disponibili.

Ciao