RDF System Management Manual

NonStop SQL/MX and RDF
HP NonStop RDF System Management Manual—524388-003
15-16
Consideration for Creating Backup Tables
Consideration for Creating Backup Tables
Currently, you cannot use the CREATE LIKE statement to create backup or temporary
tables because CREATE LIKE does not preserve the original Guardian file names that
are essential for RDF.
At some point in the future when ANSI names are supported, CREATE LIKE will be a
viable means of creating backup or temporary tables, but until then the following
discussion has the utmost significance. NonStop SQL/MX will store data in rows on
disk in a private format, which does not necessarily correspond to the sequence in
which the user specifies columns on CREATE TABLE. As an example, the following
CREATE TABLE statement:
create table t
(colx varchar(100),
coly int);
will cause rows to be laid out on disk in the following format:
<row header> <coly-data> <colx-data>
Consider the following two statements, which will produce a logically equivalent table:
create table t
(colx varchar(100));
alter table t add column coly int;
The above two statements will produce the following row layout on disk:
<row header> <colx-data> <coly-data>
Applying audit generated for one row layout to a table that has a different row layout
will not work well.
Therefore, users must create backup tables in exactly the same way that the primary
tables were created. If you have never added any additional columns to your primary
table after it was created, use the CREATE LIKE statement. Of course, you must have
registered your catalogs first (see Step 3 of Creating NonStop SQL/MX Primary and
Backup Databases from Scratch).
If you have added columns to your primary table after it was created, you must take
particular care when creating the backup table, and you should not use the CREATE
LIKE statement. Rather, use the SHOWDDL command to see the order in which
additional columns were added. You must then create the table on the backup system
in the same way in which the primary table was created, and then add the additional
columns to the backup table in the same order that they were added to the primary
table.