My company uses Tivoli for asset tracking - with DB2 as the repository. In my opinion, the Tivoli tables were not set up correctly as they have one primary key - computer_sys_id that links the tables together but, I believe there was a one-to-one assumption made throughout. In my situation, Tivoli does not identify when a NIC is disconnected - instead, it will list the same IP Address for all network cards in that one computer when two ip addresses are not found. All tables contain the computer_sys_id but neither the net_adapter nor the ip_addr tables have any key between them to where one can match the ip address to the specific adapter card. If the computer is using both primary and secondary ip addresses (two active nics), I am unable to identify which ip address is assigned to which network card.

net_adapter = computer_sys_id, mac, adapter_type, etc.
ip_addr = computer_sys_id, ip_address, dnsinfo, etc.

Currently, if I create a query using just the net_adapter and ip_addr tables, I will get up to 4 records for those that have two network cards listed. My work around for this is that I filter all Token Ring adapters from the net_adapter table (an assumption on my part that all comm w/Tivoli is via ethernet), combine the net_adapter and ip_addr tables and then run a distinct query against my results. This still produces less than perfect results but I can usually walk my way through why.

My question - to those that follow my explanation above: Could this be done in one query or is it best to run seperate queries (basing each on the previous) to gather my end results?

I need to provide this information to a web page.

computer table - computer_sys_id, machine type, model, serial number, location
net_adapter - computer_sys_id, mac
ip_addr - computer_sys_id, ip_address