Genexus

The cases requiring the creation of a temporary table when executing a database reorganization are those that cannot be solved only with SQL statements. The generator used to run the database reorganization will create and execute a conversion program during the reorganization; these reorganizations are detailed here.

You will identify these kind of database reorganizations in the IAR (Impact Analysis Report); for these cases a temporary table will be created and a GeneXus program or SQL statement will be used to copy the data to the new structures.

Each table conversion will generate a <TableName>conversion file detailing the following:

CREATE TABLE [GXA0001] ( .... )  Run conversion program for table <TableName>  DROP TABLE [<TableName>]  CALL sp_rename('[GXA0001]', '<TableName>')  ALTER TABLE [<TableName>] ....

Note: in this case the database reorganization sample is associated to SQLServer, you will notice some differences when executing with other DBMS.
In short the database reorganization does the following:

• A temporary table GXA0001 is created with the new table structure.
• A conversion program is executed to populate the temporary structure.
• The old table "Tablename" is deleted.
• The temporary table is renamed with the correct "TableName".
• The table restrictions are set.

The generator will create and execute the conversion program during the database reorganization using the following pattern for each table:

<TableName>conversion.cs for C# generator.
<TableName>conversion.java for Java generator.
<TableName>conversion.rb for Ruby generator.

Samples

1. Adding a BLOB attribute to a transaction (using SQLServer until version X Evolution 2 upgrade 3)

Note: Since version X Evolution 2 Upgrade 3, this reorg has been optimized, and an "Alter table" is used instead of creating a temporal table (see SAC #32631 for more information) 

CREATE TABLE [GXA0001] (    [Transaction1Id]       SMALLINT     NOT NULL,    [Transaction1Num]   SMALLINT     NOT NULL,    [Transaction1Blob]     VARBINARY(MAX)     NOT NULL)    Run conversion program for table Transaction1    DROP TABLE [Transaction1]    CALL sp_rename('[GXA0001]', 'Transaction1')    ALTER TABLE [Transaction1]  ADD     PRIMARY KEY ( [Transaction1Id] )

2. Adding a Longvarchar attribute to a transaction (using SQLServer until version X Evolution 2 Upgrade 3)

Note: Since version X Evolution 2 Upgrade 3, this reorg has been optimized, and an "Alter table" is used instead of creating a temporal table (see SAC #32631 for more information) 

CREATE TABLE [GXA0001] (    [Transaction1Id]     SMALLINT     NOT NULL,    [Transaction1Num]    SMALLINT     NOT NULL,    [Transaction1LongVC] VARCHAR(MAX)     NOT NULL)    INSERT INTO [GXA0001]             ([Transaction1Id],              [Transaction1Num],              [Transaction1LongVC])  SELECT [Transaction1Id],         [Transaction1Num],         ''  FROM   [Transaction1] T1    DROP TABLE [Transaction1]    DROP TABLE [Transaction1]    CALL sp_rename('[GXA0001]', 'Transaction1')    ALTER TABLE [Transaction1]  ADD     PRIMARY KEY ( [Transaction1Id] )

3. Altering the key of the table, when any other field accepts nulls of the DBMS.

When the key of the table Transaction1 wants to be modified (for example changing a N(5) to a N(6)), an alter table cannot be performed. In this case, a temporary table is created, and the data is copied to the temporary table in an optimized way:

INSERT INTO GXA0001 (a, b, c) SELECT a, b, c FROM Transaction1

If the table Transaction1 has any attribute which has Nullable property - Attribute = TRUE, this optimization cannot be done so the copy is done defining a cursor for querying the data from one table (Transaction1) and another cursor for inserting in the other table.

4. The size of a character attribute is changed to a minor one

For example if there is an attribute, and its type is Char(60), and the reorg consists in changing it to Char(40), a temporary table is created. The script is as follows:

CREATE TABLE [GXA0001] (    [TrnCharactersId]          SMALLINT     NOT NULL,    [TrnCharactersChar60To40]  CHAR(40)     NOT NULL)    Run conversion program for table TrnCharacters  DROP TABLE [TrnCharacters]  CALL sp_rename('[GXA0001]', 'TrnCharacters')  ALTER TABLE [TrnCharacters]   ADD     PRIMARY KEY ( [TrnCharactersId] )

Note: This case was optimized since Salto Beta 1 version.

Others

• Changing the property nullable from yes to no
• Case of Informix  - when the rgz0005 is given
• Case of Oracle  - when more than a table is navigated to load data of a table
• Case of Informix - when the table to be loaded have to navigate itself