Automatic update of RDBMS data when database dictionary records change

The HPE Service Manager server can push changes you make in the database dictionary to your back-end RDBMS. If you provide the Service Manager server with table create-alter-drop-rights to the RDBMS, it automatically adds or updates any tables, columns, or indexes needed in the RDBMS. If you do not provide the Service Manager server table create-alter-drop-rights to the RDBMS, HPE recommends you enable exporting your database dictionary changes as DDL. This allows you to make the necessary changes to the database dictionary and then have your RDBMS administrator review and implement the DDL on the RDBMS.

To trigger the automatic update of RDBMS data, you add or update a database dictionary record and save it. When you save the database dictionary record, Service Manager consults the mapping format for your RDBMS to create the corresponding tables, columns, and indexes required by your addition or change. Service Manager then connects to the RDBMS with the user account you provided in the configuration file and attempts to add or update the database. If the add or update is successful, you will see the updated mapping for the table, column, or index after you restart Service Manager. If the add or update fails, the Service Manager server changes the database dictionary mappings to map to null tables and writes an error message in the Service Manager log file.

For example, suppose the Service Manager administrator creates a new database dictionary called testtable. The testtable database dictionary consists of two fields: a number field called test.id and a character field called test.name. The administrator makes the test.id field the unique key. After saving the new database dictionary record, the administrator views the database dictionary record to see how they are mapped to tables and columns in the RDBMS. On a Microsoft SQL Server, the testtable database dictionary might map to the following tables and columns.

Sample database dictionary for the test.id number field

Database dictionary object	Value if RDBMS table create-alter-drop-rights granted	Value if RDBMS table create-alter-drop-rights denied
Database dictionary field name	test.id	test.id
Database dictionary data type	number	number
SQL column name	TEST_ID	TEST_ID
SQL data type	FLOAT	FLOAT
SQL alias	m1	N-m1
SQL table name	TESTTABLEM1	NULLTABLE

Sample database dictionary for the test.name character field

Database dictionary object	Value if RDBMS table create-alter-drop-rights granted	Value if RDBMS table create-alter-drop-rights denied
Database dictionary field name	test.name	test.name
Database dictionary data type	character	character
SQL column name	TEST_NAME	TEST_NAME
SQL data type	VARCHAR(60)	VARCHAR(60)
SQL alias	m1	N-m1
SQL table name	TESTTABLEM1	NULLTABLE

When Service Manager has table create-alter-drop-rights to the RDBMS, the database dictionary record contains mapping information to the actual tables and column names. When Service Manager does not have table create-alter-drop-rights to the RDBMS, the database dictionary record contains mapping information to the proposed table and column names. The RDBMS administrator can use Service Manager’s generated DDL to create the proposed table and column names. Until the actual tables and columns exist, the database dictionary record points to a null table mapping. All null table aliases start with an "N-" prefix, and the SQL table name maps to the value NULLTABLE. After the RDBMS administrator has created the tables and columns for the database dictionary record, the Service Manager administrator can use the Activate Nulltable Rows option to update the SQL mappings to their new values. Service Manager will use the column names listed in the database dictionary record to match it to the proper table name in the RDBMS.