Note: This mapping set was developed to serve as the basis of a best practice, used to export Tideway Foundation data to RDBMS. It is not intended to be the only best practice, nor does it cover all use cases.
The Extended RDB mapping set provides a set of mapping files for exporting the main Tideway Foundation 7.2 inferred nodes, except for Host Based Adapters (HBAs).
The extended RDB Mapping set is designed in such a way that it can be used to form a modular export. That is, the user can pick and match any of the mapping files within the mapping set to decide what they want to export. Each mapping file contains a main node and relevant relationships to other nodes. The exception to this (no relationships) is the Host mapping, as this is expected to form the basis of any export set.
As an example, the BAI mapping file exports to a BAI table, as well as to a BAI-to-Host relationships table. If you include only the Host and BAI mapping files then they will export all data related to both nodes and the relationship links. If you want to include SI in your exports, then this will export the SIs and relationships to BAIs and Hosts.
The idea behind this is to allow flexibility within the export schema while maintaining consistency. If a new node is to be added to the export, then the same process should be applied.
All mapping files will only export data that has been modified since the last export date. This functionality can be overridden by de-selecting the Export Changed Items flag when defining the Exporter.
The Extended RDB mapping set is made of the mapping files as described below.
Mapping File
Relationship
Fields
Output Table
bai.xml
BAIs, relationship to Hosts, Cyclic dependencies to: Contained BAIs, DependedOn BAIs
BAI: key, name, description, type, version Host: key Cyclic Dependency BAIs: related BAIKey
The following diagram provides a description of the target schema.
Configuration on the RDB Side
To create this schema, the Extended RDB mapping set is provided with SQL statements in each of the mapping files. For convenience, all of the SQL statements are shown below:
/*
drop table si_ci;
drop table si_bai_rel;
drop table si_host_rel;
drop table si_client_server_comms_rel;
drop table si_dependendon_si_rel;
drop table si_peer_to_peer_comms_rel;
drop table si_contained_si_rel;
drop table host_ci;
drop table host_networkinterface_ci;
drop table Virtualcontainer_ci;
drop table Virtualcontainer_VHost_Rel;
drop table Cluster_Host_Rel;
drop table Cluster_ci;
drop table package_ci;
drop table package_host_rel;
drop table patch_ci;
drop table patch_host_rel;
drop table file_ci;
drop table file_host_rel;
drop table file_si_rel;
drop table switch_ci;
drop table switch_host_rel;
drop table switch_interface_ci;
drop table bai_dependendon_bai_rel;
drop table bai_ci;
drop table bai_contained_bai_rel;
drop table bai_host_rel;
drop table manifest;
*/
create table manifest (
export_item varchar(50),
insert_count int,
ignore_count int,
fail_count int,
start_date timestamp,
complete_date timestamp
);
/* Tables related to the SI Mapping File */
/* Note that to use this SQL in MySQL, you will need to put the "release"
column in backticks, as the word "release" is reserved in MySQL SQL.
So, the line for release would be:
...
`release` varchar(100),
...
*/
create table si_ci (
si_key varchar(250) primary key,
si_name varchar(500),
type varchar (100),
version varchar(100),
product_version varchar(100),
release varchar(100),
edition varchar(100),
service_pack varchar(100),
build varchar(100),
patch varchar(100),
revision varchar(100),
si_created_date varchar(100),
si_modified_date varchar(100)
);
create table si_bai_rel (
si_key varchar(250) NOT NULL,
bai_key varchar(250) NOT NULL,
primary key (si_key, bai_key)
);
create table si_contained_si_rel (
si_key varchar (250) NOT NULL,
containedsi_key varchar(250) NOT NULL,
primary key (si_key, containedsi_key)
);
create table si_peer_to_peer_comms_rel (
si_key varchar (250) NOT NULL,
peersi_key varchar(250) NOT NULL,
primary key (si_key, peersi_key)
);
create table si_dependendon_si_rel (
si_key varchar (250) NOT NULL,
dependonsi_key varchar(250) NOT NULL,
primary key (si_key, dependonsi_key)
);
create table si_client_server_comms_rel (
si_key varchar (250) NOT NULL,
serversi_key varchar(250) NOT NULL,
primary key (si_key, serversi_key)
);
create table si_host_rel (
si_key varchar(250) NOT NULL,
host_key varchar(250) NOT NULL,
primary key (si_key, host_key)
);
/* Tables related to the Host mapping file */
create table host_ci (
host_key varchar(100) NOT NULL,
host_HostName varchar(100) NOT NULL,
host_Name varchar(100) ,
Description varchar(100),
Domain varchar(100),
Model varchar(100),
Serial varchar(100),
RAM integer,
Workgroup varchar(100),
Vendor varchar(100),
OS varchar(100),
OsVersion varchar(100),
Processor varchar(100),
host_created_date varchar(100),
host_modified_date varchar(100),
last_update_success timestamp,
primary key (host_key)
);
create table host_networkinterface_ci (
host_key varchar(100) NOT NULL,
interface_key varchar(100),
interface_Name varchar(100) NOT NULL,
interface_Netmask varchar(100) ,
interface_IPAddress varchar(100) ,
interface_Subnet varchar(20) ,
interface_speed varchar(100),
interface_duplex varchar(100),
interface_negotiation varchar(100),
primary key (host_key, interface_Name )
);
/* Tables related to Virtual Host Containers mapping file */
/* eg. VMWare, Solaris Zones */
create table Virtualcontainer_ci (
virtualisation_instance_key varchar(250) NOT NULL,
physical_host_key varchar(100) NOT NULL,
virtualisation_instance_name varchar(250) ,
virtualisation_product_name varchar(250) ,
virtualisation_product_version varchar(100) ,
virtualisation_product_last_update_success timestamp ,
virtualisation_product_Modified varchar(50) ,
virtualisation_product_Created varchar(50) ,
primary key (virtualisation_instance_key, physical_host_key )
);
create table Virtualcontainer_VHost_Rel (
virtualisation_instance_key varchar(250) NOT NULL,
virtual_host_key varchar(100) NOT NULL,
primary key (virtualisation_instance_key, virtual_host_key )
);
/* Tables related to Clusters and Clustered Hosts */
create table Cluster_ci (
cluster_key varchar(250) NOT NULL primary key,
cluster_name varchar(250) ,
cluster_type varchar(250) ,
cluster_version varchar(100) ,
cluster_last_update_success timestamp ,
cluster_Modified varchar(50) ,
cluster_Created varchar(50)
);
create table Cluster_Host_Rel (
cluster_key varchar(250) NOT NULL,
clustered_host_key varchar(100) NOT NULL,
primary key (cluster_key, clustered_host_key )
);
/* Tables related to Package mapping file */
create table package_ci (
package_key varchar(250) NOT NULL primary key,
package_name varchar(100),
package_version varchar(100),
package_os varchar(100),
package_revision varchar(100),
package_created_date varchar(100),
package_modified_date varchar(100)
);
create table package_host_rel (
host_key varchar(100) NOT NULL,
package_key varchar(200),
primary key (host_key, package_key )
);
/* Tables related to the Patch mapping file */
create table patch_ci (
patch_key varchar(250) NOT NULL primary key,
patch_name varchar(100),
patch_os varchar(100),
patch_created_date varchar(100),
patch_modified_date varchar(100)
);
create table patch_host_rel (
host_key varchar(100) NOT NULL,
patch_key varchar(100),
primary key (host_key, patch_key )
);
/* Tables related to the File mapping file */
create table file_ci (
file_key varchar(250) NOT NULL primary key,
file_path varchar(100),
file_size varchar(100),
file_md5sum varchar(100),
file_created_date varchar(100),
file_last_modified varchar(100)
);
create table file_host_rel (
host_key varchar(100) NOT NULL,
file_key varchar(100),
primary key (host_key, file_key )
);
create table file_si_rel (
file_key varchar(100) NOT NULL,
SI_key varchar(250) NOT NULL,
primary key (file_key, SI_key)
);
/* Tables related to the Switch mapping file */
create table switch_ci (
switch_name varchar(100) primary key,
description varchar(100),
status varchar(100),
model varchar(100),
ostype varchar(100),
osversion varchar(100)
);
create table switch_host_rel (
switch_name varchar (100) NOT NULL,
host_hostkey varchar (100) NOT NULL,
primary key (switch_name, host_hostkey)
);
create table switch_interface_ci (
switch_name varchar (100) NOT NULL,
port_key varchar(200) NOT NULL,
switch_port varchar (50),
interface_key varchar (250) NULL,
connected_ip_addr varchar(20),
connected_mac_addr varchar(20),
port_speed varchar(20),
port_duplex varchar(20),
port_negotiation varchar(50),
port_description varchar(300),
port_state varchar(20),
port_domain varchar(100),
port_vlan varchar(100),
port_vlan_id varchar(100),
primary key (switch_name, port_key)
);
/* Tables related to the BAI mapping file */
create table bai_ci (
bai_key varchar(100) primary key,
name varchar(100) ,
description varchar(100),
type varchar(100),
version varchar(100)
);
create table bai_host_rel (
bai_key varchar(100) NOT NULL,
host_key varchar (100) NOT NULL,
primary key (bai_key, host_key)
);
create table bai_dependendon_bai_rel (
bai_key varchar (100) NOT NULL,
dependedonbai_key varchar (100) NOT NULL,
primary key (bai_key, dependedonbai_key)
);
create table bai_contained_bai_rel (
bai_key varchar (250) NOT NULL,
containedbai_key varchar(250) NOT NULL,
primary key (bai_key, containedbai_key)
);
/*
truncate table si_ci;
truncate table si_bai_rel;
truncate table si_host_rel;
truncate table si_client_server_comms_rel;
truncate table si_dependendon_si_rel;
truncate table si_peer_to_peer_comms_rel;
truncate table si_contained_si_rel;
truncate table host_ci;
truncate table host_networkinterface_ci;
truncate table Virtualcontainer_ci;
truncate table Virtualcontainer_VHost_Rel;
truncate table Cluster_Host_Rel;
truncate table Cluster_ci;
truncate table package_ci;
truncate table package_host_rel;
truncate table patch_ci;
truncate table patch_host_rel;
truncate table file_ci;
truncate table file_host_rel;
truncate table file_si_rel;
truncate table switch_ci;
truncate table switch_host_rel;
truncate table switch_interface_ci;
truncate table bai_dependendon_bai_rel;
truncate table bai_ci;
truncate table bai_contained_bai_rel;
truncate table bai_host_rel;
truncate table manifest;
*/
Tideway Blog