Building the DB2 Health Monitor Sample Application for PHP, Part 2: DB2 pureXML or DOM? You decide

Return to article

XQuery
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>

Return to article