xquery
let $part1 := db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
INST_NAME
)
FROM SYSIBMADM.ENV_INST_INFO
')/text()
let $part2 := db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "state",
ROLLED_UP_ALERT_STATE_DETAIL
)
FROM table(
SELECT DISTINCT ROLLED_UP_ALERT_STATE, ROLLED_UP_ALERT_STATE_DETAIL
FROM table(health_db_info('''',-1)) as t
UNION
SELECT DISTINCT ROLLED_UP_ALERT_STATE, ROLLED_UP_ALERT_STATE_DETAIL
FROM table(health_cont_info('''',-1)) as t
UNION
SELECT DISTINCT ROLLED_UP_ALERT_STATE, ROLLED_UP_ALERT_STATE_DETAIL
FROM table(health_tbs_info('''',-1)) as t
UNION
SELECT DISTINCT ROLLED_UP_ALERT_STATE, ROLLED_UP_ALERT_STATE_DETAIL
FROM table(health_dbm_info(-1)) as t
) as temp
ORDER BY temp.ROLLED_UP_ALERT_STATE DESC
FETCH FIRST 1 ROWS ONLY
')/text()
let $part3 := db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
(sum(t1.pool_cur_size) + sum(t2.pool_cur_size))/1024.0/1024.0
)
FROM SYSIBMADM.SNAPAGENT_MEMORY_POOL as t1, SYSIBMADM.SNAPDB_MEMORY_POOL as t2
')/text()
let $part4 := db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
XMLATTRIBUTES
(
DB_SIZE/1024.0/1024.0 as "database_size",
DB_CAPACITY/1024.0/1024.0 as "database_capacity"
)
)
FROM SYSTOOLS.STMG_DBSIZE_INFO
')
return <DB2Health_Report schemaVersion="1.0" instance="{$part1}" database="SAMPLE"
highestAlertState="{$part2}" timestamp="">
<InfoSet memory_usage="{round($part3)}"
database_size="{round($part4/@database_size)}"
database_capacity="{round($part4/@database_capacity)}">
{
for $a in db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
XMLATTRIBUTES
(
snapshot_timestamp as "snapshot_timestamp",
db_status as "database_status",
last_backup as "last_backup",
rows_read as "rows_read",
(rows_inserted + rows_updated) as "rows_written"
)
)
FROM TABLE(SNAP_GET_DB_V91(''SAMPLE'', -2)) AS DB
')
return $a/@*
}
{
for $a in db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
XMLATTRIBUTES
(
HOST_NAME as "host_name",
TOTAL_MEMORY as "total_memory"
)
)
FROM SYSIBMADM.ENV_SYS_INFO
')
return $a/@*
}
</InfoSet>
<HIDefinitionSet>
{
for $def in db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "HIDefinition",
XMLATTRIBUTES
(
t1.ID as "hiIdentifier",
t1.NAME as "hiName",
t1.SHORT_DESCRIPTION as "hiShortDesc",
t1.LONG_DESCRIPTION as "hiLongDesc",
t1.TYPE as "hiType",
t1.FORMULA as "hiFormula",
t1.UNIT as "hiUnit"
),
XMLELEMENT
(
NAME "HISettings",
XMLATTRIBUTES
(
t2.SENSITIVITY as "sensitivity",
t2.EVALUATE as "evaluate",
t2.ALARM_THRESHOLD as "alarmThreshold",
t2.WARNING_THRESHOLD as "warningThreshold"
)
)
)
FROM table (health_get_ind_definition(''en_US'')) as t1,
table(
SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD,
WARNING_THRESHOLD
FROM table(HEALTH_GET_ALERT_CFG(''TS'', ''G'', '''',''''))
as t
UNION
SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD,
WARNING_THRESHOLD
FROM table(HEALTH_GET_ALERT_CFG(''TSC'', ''G'', '''',''''))
as t
UNION
SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD,
WARNING_THRESHOLD
FROM table(HEALTH_GET_ALERT_CFG(''DBM'', ''G'', '''',''''))
as t
UNION
SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD,
WARNING_THRESHOLD
FROM table(HEALTH_GET_ALERT_CFG(''DB'', ''G'', '''',''''))
as t
) as t2
WHERE t1.ID = t2.ID
')
return $def
}
</HIDefinitionSet>
<AlertSet>
{
for $alert in
db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "HealthAlert",
XMLATTRIBUTES
(
temp.HI_ID as "hiIdentifier",
temp.HI_ALERT_STATE_DETAIL as "hiAlertState",
temp.HI_VALUE as "hiValue",
temp.HI_TIMESTAMP as "hiTimestamp"
),
XMLELEMENT
(
NAME "DB2_Object",
XMLATTRIBUTES
(
temp.OBJECT_NAME as "name"
)
),
XMLELEMENT
(
NAME "HiFormulaValue",
temp.HI_FORMULA
)
)
FROM table(
SELECT HI_ID, DB_NAME as OBJECT_NAME,
HI_ALERT_STATE_DETAIL,HI_VALUE, HI_TIMESTAMP,
HI_FORMULA
FROM table(health_db_hi('''',-1)) as t
UNION
SELECT HI_ID, SERVER_INSTANCE_NAME as OBJECT_NAME,
HI_ALERT_STATE_DETAIL, HI_VALUE,HI_TIMESTAMP,
HI_FORMULA
FROM table(health_dbm_hi(-1)) as t
UNION
SELECT HI_ID, TABLESPACE_NAME as OBJECT_NAME,
HI_ALERT_STATE_DETAIL, HI_VALUE, HI_TIMESTAMP,
HI_FORMULA
FROM table(health_tbs_hi('''',-1)) as t
UNION
SELECT HI_ID, CONTAINER_NAME as OBJECT_NAME,
HI_ALERT_STATE_DETAIL, HI_VALUE, HI_TIMESTAMP,
HI_FORMULA
FROM table(health_cont_hi('''',-1)) as t
) as temp
')
return <HealthAlert>
{$alert/@*}
<DB2_Object name="{$alert/DB2_Object/@name}">
{
for $k in db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
XMLATTRIBUTES
(
temp.ID as "id",
temp.OBJECTTYPE as "type"
)
)
FROM table(
SELECT ID, OBJECTTYPE
FROM table(HEALTH_GET_ALERT_CFG(''DB'', ''G'',
'''', ''''))
as t
UNION
SELECT ID, OBJECTTYPE
FROM table(HEALTH_GET_ALERT_CFG(''DBM'', ''G'',
'''', ''''))
as t
UNION
SELECT ID, OBJECTTYPE
FROM table(HEALTH_GET_ALERT_CFG(''TS'', ''G'',
'''', ''''))
as t
UNION
SELECT ID, OBJECTTYPE
FROM table(HEALTH_GET_ALERT_CFG(''TSC'', ''G'',
'''', ''''))
as t
) as temp
')
where $k/@id = $alert/@hiIdentifier
return $k/@type
}
</DB2_Object>
{$alert/HiFormulaValue}
<HiAdditionalInfo>
{
for $d in
(
db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
XMLATTRIBUTES
(
t.HI_ID as "id",
t.TABLESPACE_NAME as "obj_nm"
),
XMLELEMENT
(
NAME "hiAdditionalInfo",
t.HI_ADDITIONAL_INFO
)
)
FROM table(health_tbs_hi('''',-1)) as t
'),
db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
XMLATTRIBUTES
(
t.HI_ID as "id",
t.CONTAINER_NAME as "obj_nm"
),
XMLELEMENT
(
NAME "hiAdditionalInfo",
t.HI_ADDITIONAL_INFO
)
)
FROM table(health_cont_hi('''',-1)) as t
'),
db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
XMLATTRIBUTES
(
t.HI_ID as "id",
t.DB_NAME as "obj_nm"
),
XMLELEMENT
(
NAME "hiAdditionalInfo",
t.HI_ADDITIONAL_INFO
)
)
FROM table(health_db_hi('''',-1)) as t
'),
db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "info",
XMLATTRIBUTES
(
t.HI_ID as "id",
t.SERVER_INSTANCE_NAME as "obj_nm"
)
XMLELEMENT
(
NAME "hiAdditionalInfo",
t.HI_ADDITIONAL_INFO
)
)
FROM table(health_dbm_hi(-1)) as t
')
)
where $d/@id = $alert/@hiIdentifier
and
$d/@obj_nm = $alert/DB2_Object/@name
return $d/hiAdditionalInfo/text()
}
</HiAdditionalInfo>
<alertHistory>
{
for $c in db2-fn:sqlquery('
SELECT
XMLELEMENT
(
NAME "alertHistoryData",
XMLATTRIBUTES
(
temp.HI_ID as "hiIdentifier",
temp.OBJECT_NAME as "object_name",
temp.HI_VALUE as "hiValue",
temp.HI_TIMESTAMP as "hiTimestamp",
temp.HI_ALERT_STATE_DETAIL as "hiAlertState",
temp.HI_FORMULA as "hiFormulaValue"
)
)
FROM table(
SELECT HI_ID, DB_NAME as OBJECT_NAME, HI_VALUE,
HI_TIMESTAMP,HI_ALERT_STATE_DETAIL,HI_FORMULA
FROM table(health_db_hi_his('''',-1)) as t
UNION
SELECT HI_ID, SERVER_INSTANCE_NAME as OBJECT_NAME,
HI_VALUE, HI_TIMESTAMP,HI_ALERT_STATE_DETAIL,
HI_FORMULA
FROM table(health_dbm_hi_his(-1)) as t
UNION
SELECT HI_ID, TABLESPACE_NAME as OBJECT_NAME,
HI_VALUE, HI_TIMESTAMP,HI_ALERT_STATE_DETAIL,
HI_FORMULA
FROM table(health_tbs_hi_his('''',-1)) as t
UNION
SELECT HI_ID, CONTAINER_NAME as OBJECT_NAME,HI_VALUE,
HI_TIMESTAMP, HI_ALERT_STATE_DETAIL,HI_FORMULA
FROM table(health_cont_hi_his('''',-1)) as t
) as temp
')
where $c/@hiIdentifier = $alert/@hiIdentifier and
$c/@object_name = $alert/DB2_Object/@name
return $c
}
</alertHistory>
</HealthAlert>
}
</AlertSet>
<ErrorSet>
<SOA_ERROR_CODE>0</SOA_ERROR_CODE>
<SOA_ERROR_MESSAGE>Processed Successfully</SOA_ERROR_MESSAGE>
</ErrorSet>
</DB2Health_Report>
|
