SQL/MX Release 2.0 Best Practices

Data-loading Techniques and Considerations 37
Data-loading Techniques and Considerations
This section suggests procedures to load data from a flat file to SQL/MX tables. Your procedures may vary
depending on your circumstances.
Create the Table for the Final Destination of the SQL/MX
Table
SQL/MX tables cannot be renamed; if you want to partition SQL/MX tables, partition them initially. Do not
partition smaller tables that may be best served by a single drive’s cache.
Do not create the table along with its indexes. Load the data first, and create the indexes after you load
the data. The exception is when you need a unique index to create a data constraint prior to the data
load. However, note that load performance will be affected. Review how to create a primary key if the
primary key differs from the store-by value. To partition the primary key when it differs from the store-by
value, create the table with a unique index that has the values of the primary key, then issue the
command for the primary key. This method will reuse the value for the index and make that value the
primary key.
To use IMPORT, create or obtain a delimited file. Create the file with columns ordered in the same order
as the table that you will be populating. Choose a delimiter that is not in the data content of the file you
wish to import. Currently, a problem occurs in IMPORT when IMPORT encounters special data, even when
the value is flagged as a special character.
Use FTP to Send the File to the Desired Location
Verify that FTP is completed before starting the IMPORT. IMPORT does not issue a warning or error if the file
being sent is still open when IMPORT is issued or running. Neither FTP nor IMPORT fails if they both have the
file open. This situation can result in not loading all the data from the input file.
Import the File to the Table
Create indexes on the table, and partition the indexes if desired.
Select Guardian names from the SQL/MX catalog for your table or the tables you wish to reload.
FUP RELOAD the Guardian names of the table (include all partitions of a table and any indexes or index
partitions). You can run these commands concurrently; however, you should distribute the FUP RELOADs
across CPUs at a low rate to ensure that the reloads do not monopolize the system resources.
Select the count(*) Table for Read Uncommitted Access
For the UPDATE STATISTICS command, you need to know how many rows were loaded during the IMPORT
or the data insert. However, you do not need to select the count(*) table if you note how many rows
were loaded during the IMPORT or the data insert. UPDATE STATISTICS can obtain its own row count of the
table with its algorithms, although doing so is inefficient. Your statistics will run faster if you provide the row
count manually as part of the UPDATE STATISTICS command.
The suggested values for the syntax:
update statistics for table on every key sample random 10 percent, set rowcount
‘count from table’
If the table is medium to small in size, you may run statistics on all columns without sampling; however,
using the row count will increase performance.
No bulk load command is currently available; you must use an INSERT…SELECT statement. For parallelism,
you can use an INSERT with a SELECT FROM and describe the range and the data requested. From
NonStop SQL/MX SPR ABA and superseding SPRs, the INSERT…SELECT option works well. The other options