#14362: DB2 iSeries -> Compare Issues

ivangron reported 2016-04-02T16:09:19Z · last modified 2016-04-11T14:33:33Z

DB2 iSeries -> Compare Issues

Dev	ivangron
QA	tariqrahiman

Priority	Low
Complexity	Unknown

Component	Tools - Compare - Schema
Version	17.0

All the following problems are for DB2 iSeries database :

1. When we compare two models (or two schemas or a model with a schema), even if we ‘unselect’ ‘Include schema name’, the schema is included in the comparison and therefore, all objects show differences.

2. During the generation of constraints in the script, instead of generation ‘ALTER TABLE <child table>’ the tool uses the parent name.

In this case, the tool should have generated ‘ALTER TABLE EMISSION_PRIMES.

3. When we compare a model to a schema, the record format (RCDFMT) is not retrieved from the schema. So for each table with the ‘record format name’ attribute, the compare tool generates an error.

4. Again during the comparison of a model with a schema, if the ‘column heading’ of a column is absent, the tool will not see that there is a value in the ‘column text’ property of the column and will then indicate that it’s different from the one in the schema.

5 attachments

Verified_Scenario-01.png

2016-04-06T06:03:09Z

171 KB
Verified_Scenario-03_Schema_Compare.png

2016-04-06T06:03:09Z

179 KB
Verified_Scenario-02.png

2016-04-06T06:03:09Z

329 KB
Verified_Scenario-03_ER.png

2016-04-06T06:03:09Z

163 KB
Verified_Scenario-04.png

2016-04-06T06:03:10Z

163 KB

All Comments (8) Change History

ivangron 2016-04-02T16:13:13Z

1.) When we compare two models (or two schemas or a model with a schema), even if we 'unselect' 'include schema name,' the schema is included in the comparison and therefor, all objects show differences.

The constraint's on the object where still scripting the schemas causing the compare's to set a state of change. This is now fixed.

ivangron 2016-04-02T16:18:06Z

2.) During the generation of the constraint in the script, instead of the generation of 'ALTER TABLE <child table>,' the tool uses the parent name.

There was an issue where the relationship was getting assigned to both the target and destination objects as constraints. This was causing issues with the object scripting. Relationships will now correctly only assigned a constraint to the object that needs to script it.

THIS CHANGE AFFECTS ALL DATABASE MODELS, NOT JUST DB2 iSeries.

ivangron 2016-04-02T16:46:52Z

3. When we compare a model to a schema, the record format (RCDFMT) is not retrieved from the schema. So for each table with the 'record format name' attribute, the compare tool generates an error.

This option is only available on the create. The system catalog tables do not persist this information. Due to this we do not extract this information. Because of this you may create an ER Model that does contain information that will compare changes if you compare against a database with the objects created. There is no fix that we can provide for this unless the database vendor enhances the system catalog tables.

ivangron 2016-04-04T19:49:34Z · (edited)

4. During the comparison of a model with a schema, if the 'column heading' of a column is absent, the tool will not see that there is a value in the 'column text' property of the column and will then indicate that it's different from the one in the schema.

The code block that decides scripting of [Column Text] was dependant on the [Column Heading] option not been empty. This will no longer be the case and the text will always script if set.

ivangron 2016-04-02T17:25:25Z · (edited)

Fixes have been made for the issues that could be fixed. The 3rd issue we most live with until the catalog tables are enhanced to provide the needed information for extraction.

This issue requires testing the ERM compare with all database types. Below are scripts I used to test on DB2 iSeries, that create 3 tables with constraints on 2 different schemas.

CREATE SCHEMA DB_LEFT

CREATE SCHEMA DB_RIGHT

CREATE TABLE DB_LEFT.ADDRESS (ADDRESSID INTEGER NOT NULL, CONSTRAINT DB_LEFT.PKEY PRIMARY KEY(ADDRESSID)) NOT LOGGED INITIALLY VOLATILE CARDINALITY RCDFMT "RCDFMT"

LABEL ON COLUMN "DB_LEFT"."ADDRESS"."ADDRESSID" TEXT IS 'ADDRESSID text'

CREATE TABLE DB_LEFT.COMPANY (COMPANYID INTEGER NOT NULL, ADDRESS INTEGER NOT NULL, CONSTRAINT DB_LEFT.COMPANY_PKEY PRIMARY KEY(COMPANYID))

CREATE TABLE DB_LEFT.CUSTOEMR (ROWID INTEGER NOT NULL, ADDRESS INTEGER NOT NULL, COMPANY INTEGER NOT NULL, CONSTRAINT DB_LEFT.CUSTOMER_PKEY PRIMARY KEY(ROWID))

ALTER TABLE DB_LEFT.COMPANY ADD CONSTRAINT DB_LEFT.COMPANY_FKEY FOREIGN KEY(ADDRESS) REFERENCES DB_LEFT.ADDRESS(ADDRESSID)

ALTER TABLE DB_LEFT.CUSTOEMR ADD CONSTRAINT DB_LEFT.CUSTOMER_FKEY1 FOREIGN KEY(COMPANY) REFERENCES DB_LEFT.COMPANY(COMPANYID)

ALTER TABLE DB_LEFT.CUSTOEMR ADD CONSTRAINT DB_LEFT.CUSTOMER_FKEY FOREIGN KEY(ADDRESS) REFERENCES DB_LEFT.ADDRESS(ADDRESSID)

CREATE TABLE DB_RIGHT.ADDRESS (ADDRESSID INTEGER NOT NULL, CONSTRAINT DB_RIGHT.PKEY PRIMARY KEY(ADDRESSID)) NOT LOGGED INITIALLY VOLATILE CARDINALITY RCDFMT "RCDFMT"

LABEL ON COLUMN "DB_RIGHT"."ADDRESS"."ADDRESSID" TEXT IS 'ADDRESSID text'

CREATE TABLE DB_RIGHT.COMPANY (COMPANYID INTEGER NOT NULL, ADDRESS INTEGER NOT NULL, CONSTRAINT DB_RIGHT.COMPANY_PKEY PRIMARY KEY(COMPANYID))

CREATE TABLE DB_RIGHT.CUSTOEMR (ROWID INTEGER NOT NULL, ADDRESS INTEGER NOT NULL, COMPANY INTEGER NOT NULL, CONSTRAINT DB_RIGHT.CUSTOMER_PKEY PRIMARY KEY(ROWID))

ALTER TABLE DB_RIGHT.COMPANY ADD CONSTRAINT DB_RIGHT.COMPANY_FKEY FOREIGN KEY(ADDRESS) REFERENCES DB_RIGHT.ADDRESS(ADDRESSID)

ALTER TABLE DB_RIGHT.CUSTOEMR ADD CONSTRAINT DB_RIGHT.CUSTOMER_FKEY1 FOREIGN KEY(COMPANY) REFERENCES DB_RIGHT.COMPANY(COMPANYID)

ALTER TABLE DB_RIGHT.CUSTOEMR ADD CONSTRAINT DB_RIGHT.CUSTOMER_FKEY FOREIGN KEY(ADDRESS) REFERENCES DB_RIGHT.ADDRESS(ADDRESSID)