#15075: TeraData -> Extraction & Scripting of Table with Primary Index not scripting correctly

ivangron reported Mar 14, 2017 · tariqrahiman last modified Mar 21, 2017

TeraData -> Extraction & Scripting of Table with Primary Index not scripting correctly

Dev	ivangron
QA	tariqrahiman

Priority	Major
Complexity	Unknown

Component	DB - Teradata
Version	18.0

Teradata allows for specifying a PRIMARY INDEX in the create table DDL statement. This primary index can be different then the primary key and needs to be scripted in the table statement. Currently we are not correctly extracting and scripting the primary index information. Example:

CREATE TABLE TEST_PKPI (
    PI_COL1 INTEGER NOT NULL, 
    PK_COL2 INTEGER NOT NULL, 
    PU_COL3 INTEGER NOT NULL,
    PU_COL4 INTEGER NOT NULL,
    PU_COL5 INTEGER NOT NULL,
    PRIMARY KEY (PK_COL2)
    ) UNIQUE PRIMARY INDEX _ads_index_01 (PI_COL1, PU_COL5)
GO
ALTER TABLE TEST_PKPI ADD CONSTRAINT _ADS_UKEY_IVAN UNIQUE (PU_COL3)
GO
CREATE INDEX TEST_PKPI_IDX_01("PU_COL5") ON "TEST_PKPI"
GO
CREATE INDEX TEST_PKPI_IDX_02("PU_COL3", "PU_COL4", "PU_COL5") ON "TEST_PKPI"
GO

The UNIQUE PRIMARY INDEX _ads_index_01 on columns PI_COL1 and PU_COL5 needs to be extracted and scripted with the table ddl statement and not as a index object. There is no support for a CREATE PRIMARY INDEX statement, instead this information must be provided in the create table statement or modified through the atler table statment.

11 attachments

C_Schema_script_Generator.png

Mar 15, 2017

95 KB
B_Index_Schema_treenode.png

Mar 15, 2017

132 KB
g_schema_script_gene_preview_Tab.png

Mar 15, 2017

158 KB
Create_Full_Extraction.png

Mar 15, 2017

180 KB
h_ER.png

Mar 15, 2017

142 KB
SQL_indexes.sql

Mar 15, 2017

1 KB
i_Schema-compare.png

Mar 15, 2017

163 KB
U_K_I_J_P_Q.png

Mar 15, 2017

273 KB
QB.png

Mar 15, 2017

116 KB
Verified_in_ADS-18.0.4-6.png

Mar 17, 2017

478 KB
Reopened.png

Mar 17, 2017

554 KB

All Comments (7) Change History

ivangron Mar 14, 2017

Extraction and Scripting fixes have been made so that :

a) script create table will correctly script the table with its primary index clause.

b) schema tree node will no longer offer scripting menus for primay indexes.

c) schema script generator will no longer show primary indexes as index objects since this is part of the table structure

d) extract will use IndexTypes of U & K from the Indices catalog table as constraints and IndexType not in (U,K,I,J,P,Q) as index objects. P&Q are extracted as an index that is used at the table storage level.

e) script index will no longer script an index DDL statement for IndexType P & Q for teradata create/drop indexes.

Committed revision: 53519

I have checked this revision in and will begin work on GUI support for this in the CREATE/ALTER table under the storage tab.

nileshhmali Mar 15, 2017 · (edited)

I have executed above scenarios in ADS-18.0.4

Pass scenarios:

a) script create table will correctly script the table with its primary index clause

=>Please refer screenshot

b) schema tree node will no longer offer scripting menus for primary indexes.

=>Please refer screenshot

c) schema script generator will no longer show primary indexes as index objects since this is part of the table structure

=>Please refer screenshot

//**************************************************************************************************************

@Ivan,

Please calrifly:

d)extract will use IndexTypes of U & K from the Indices catalog table as constraints and IndexType not in (U,K,I,J,P,Q) as index objects. P&Q are extracted as an index that is used at the table storage level.

=>I have Created tables using U,K,I,J,P,Q.(Please refer attached .sql file)

Observation:

U, K, I,J,P,Q indexes DDL are not generated in schema script generator but Created indexes are displayed in Index tree node,Please refer screenshot

Is it expected?

Please handle below scenarios:

g)Incorrect script is generated in Schema script generated preview tab

=>Please refer screenshot

h)Unique primary index not extracted in ER Modeler

=>Please refer screenshot

i)Unique primary index not extracted in Schema compare

=>Please refer screenshot

j)IndexTypes (U,K,I,J,P,Q) are not extracted in Query Builder->Table->Indexes tree node

=>Please refer screenshot

ivangron Mar 16, 2017

Create table will now have the following options in the storage tab for teradata 13+ under the General section:

[Primary Index Columns] : If columns are selected, this option will build the primary index column string list.

[Unique Primary Index] : If set on and primary index columns are selected, this will set the UNIQUE flag for the primary index.

Both of these options are only enabled when the [No Primay Index] option is set off.

GUI is also available in ERM. The primary index will not be displayed in the ERM index node though, even though the primary index will continue to display in the schema tree index node.

Committed revision: 53540

nileshhmali Mar 17, 2017 · (edited)

Verified in ADS-18.0.4-6

As per Ivan's comment "Primary Index Columns" and "Unique Primary Index" option are available in Create table GUI->Storage->General and ERM

Please refer screenshot

nileshhmali Mar 17, 2017 · (edited)

@Ivan,

Please handle below scenario:

Tested On below OS and ADS Build

Product: Aqua Data Studio

Version: 18.0.4-6

Build #: 53542

Build Date: 2017-Mar-16 01:31:42 PM

Operating Environment: Linux (4.4.0-64-generic, amd64) / UTF-8 / en / US / Oracle Corporation 1.8.0_102-b14

Memory: Max=704,643,072; Total=370,671,616; Free=262,641,832; CPUs=8

Operating Environment: Windows 7 (6.1, amd64)

Operating Environment: Windows 7 (6.1, amd64) / Cp1252 / en / IN / Oracle Corporation 1.8.0_121-b13
Memory: Max=704,643,072; Total=373,293,056; Free=187,815,936; CPUs=8

Operating Environment: Mac OS X (10.12.3, x86_64) / UTF-8 / en / US / Oracle Corporation 1.8.0_102-b14
Memory: Max=704,643,072; Total=436,207,616; Free=256,268,048; CPUs=4

Primary index, unique primary index are not extracted for script object to window as->CREATE(Full)

Connect Teradata-15.0

Create table with primary index, unique primary index

CREATE SET TABLE Test_table ,

NO FALLBACK,

LOG,

DEFAULT MERGEBLOCKRATIO,

CHECKSUM = DEFAULT (

c1 VARCHAR(25) CHARACTER SET LATIN,

c2 VARCHAR(25) CHARACTER SET LATIN

)

PRIMARY INDEX _ADS_INDEX_1 (c1, c2)

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, REFERENCES, CREATE TRIGGER, DROP TRIGGER, DUMP, RESTORE, DROP TABLE, STATISTICS ON test_user.Test_table TO DBC WITH GRANT OPTION

CREATE SET TABLE test_table2 ,

NO FALLBACK,

LOG,

DEFAULT MERGEBLOCKRATIO,

CHECKSUM = DEFAULT (

c1 VARCHAR(25) CHARACTER SET LATIN,

c2 VARCHAR(25) CHARACTER SET LATIN

)

UNIQUE PRIMARY INDEX _ADS_INDEX_1 (c1, c2)

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, REFERENCES, CREATE TRIGGER, DROP TRIGGER, DUMP, RESTORE, DROP TABLE, STATISTICS ON test_user.test_table2 TO DBC WITH GRANT OPTION

Right click on Test_table or test_table2

Select Script object to window as->CREATE(FULL)

Create script is generated on QA

Actual Result:

You can see that primary index, unique primary index are not extracted.

Expected Result:

primary index and unique primary index should be extracted for CREATE(FULL).

Observation:

1)I have selected both table "test_table2" and "test_table"

Select Script object to window as->CREATE

=>primary index and unique primary indexes are extracted successfully.

2)I have selected both table "test_table2" and "test_table" and select Compare option

=>primary index and unique primary indexes are extracted successfully in tab compare

Please refer screenshot

@Ivan,

Please handle below scenario:

Tested On below OS and ADS Build

Product: Aqua Data Studio

Version: 18.0.4-6

Build #: 53542

Build Date: 2017-Mar-16 01:31:42 PM

Operating Environment: Linux (4.4.0-64-generic, amd64) / UTF-8 / en / US / Oracle Corporation 1.8.0_102-b14

Memory: Max=704,643,072; Total=370,671,616; Free=262,641,832; CPUs=8

Operating Environment: Windows 7 (6.1, amd64)

Operating Environment: Windows 7 (6.1, amd64) / Cp1252 / en / IN / Oracle Corporation 1.8.0_121-b13
Memory: Max=704,643,072; Total=373,293,056; Free=187,815,936; CPUs=8

Operating Environment: Mac OS X (10.12.3, x86_64) / UTF-8 / en / US / Oracle Corporation 1.8.0_102-b14
Memory: Max=704,643,072; Total=436,207,616; Free=256,268,048; CPUs=4

Primary index, unique primary index are not extracted for script object to window as->CREATE(Full)

Connect Teradata-15.0

Create table with primary index, unique primary index

CREATE SET TABLE Test_table ,

NO FALLBACK,

LOG,

DEFAULT MERGEBLOCKRATIO,

CHECKSUM = DEFAULT (

c1 VARCHAR(25) CHARACTER SET LATIN,

c2 VARCHAR(25) CHARACTER SET LATIN

)

PRIMARY INDEX _ADS_INDEX_1 (c1, c2)

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, REFERENCES, CREATE TRIGGER, DROP TRIGGER, DUMP, RESTORE, DROP TABLE, STATISTICS ON test_user.Test_table TO DBC WITH GRANT OPTION

CREATE SET TABLE test_table2 ,

NO FALLBACK,

LOG,

DEFAULT MERGEBLOCKRATIO,

CHECKSUM = DEFAULT (

c1 VARCHAR(25) CHARACTER SET LATIN,

c2 VARCHAR(25) CHARACTER SET LATIN

)

UNIQUE PRIMARY INDEX _ADS_INDEX_1 (c1, c2)

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, REFERENCES, CREATE TRIGGER, DROP TRIGGER, DUMP, RESTORE, DROP TABLE, STATISTICS ON test_user.test_table2 TO DBC WITH GRANT OPTION

Right click on Test_table or test_table2

Select Script object to window as->CREATE(FULL)

Create script is generated on QA

Actual Result:

You can see that primary index, unique primary index are not extracted.

Expected Result:

primary index and unique primary index should be extracted for CREATE(FULL).

Observation:

1)I have selected both table "test_table2" and "test_table"

Select Script object to window as->CREATE

=>primary index and unique primary indexes are extracted successfully.

2)I have selected both table "test_table2" and "test_table" and select Compare option

=>primary index and unique primary indexes are extracted successfully in tab compare

Please refer screenshot

ivangron Mar 17, 2017 · (edited)

Script create full action was passing include supporting index flag incorrectly for teradata. Issue fixed with Committed revision: 53548

and a fix for the scripting syntax with Committed revision: 53551

This was fixed and checked into 18 and 19. The revision numbers above are for 18

tariqrahiman Mar 17, 2017

Verified in ADS v 18.0.5-1

Search Tips

Aqua Data Studio / nhilam

TeraData -> Extraction & Scripting of Table with Primary Index not scripting correctly

11 attachments

Issue #15075

Completion

Search Tips

Aqua Data Studio / nhilam

Title

TeraData -> Extraction & Scripting of Table with Primary Index not scripting correctly

11 attachments

Issue #15075

Completion