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.
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] )
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 an 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.
For example, you decided to modify the following:
The reorganization consists of:
- Create a temporary table with the new attribute name length
- Copy records from the old table to the new temporary one
- Rename the table to the new name
- Update table integrity restrictions
- 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 has to navigate itself