SQL/MP Installation and Management Guide

Reorganizing Tables and Maintaining Data
HP NonStop SQL/MP Installation and Management Guide—523353-004
8-11
Examples of Loading Tables
Loading Data From an Enscribe File
This example loads data into a table from an Enscribe file. The LOAD command
includes the MOVE option to match fields to columns because DELIV_DATE is missing
from the source file and the field names are not the same as the column names. The
layout of the Enscribe file $ENSC.SALES.ORDERS precedes the commands that
create and load the new table, $VOL1.SALES.ORDERS:
* Record Layout for $ENSC.SALES.ORDERS
01 ORDERS.
02 ORDER-NUM PIC 9(6).
02 ORDERED-DATE PIC S9(6) COMP.
02 SALESMAN PIC 9(4).
02 CUSTOMER-NUMBER PIC 9(4).
>> CREATE TABLE $VOL1.SALES.ORDERS
+> (ORDERNUM PIC 9(6) NO DEFAULT NOT NULL,
+> ORDER_DATE PIC S9(6) COMP DEFAULT SYSTEM NOT
NULL,
+> DELIV_DATE PIC S9(6) COMP DEFAULT SYSTEM NOT
NULL,
+> SALESREP PIC 9(4) DEFAULT SYSTEM,
+> CUSTNUM PIC 9(4) DEFAULT SYSTEM NOT NULL,
+> PRIMARY KEY (ORDERNUM))
+> CATALOG $VOL1.SALES
+> EXTENT(1000,100);
--- SQL operation complete.
>> LOAD $ENSC.SALES.ORDERS, $VOL1.SALES.ORDERS,
+> SCRATCH $TEMP.SCRATCH.JUNK
+> SLACK 50
+> SOURCEDICT $ENSC.SALES SOURCEREC ORDERREC
+> MOVE (ORDER-NUM TO ORDERNUM, ORDERED-DATE TO ORDER_DATE,
+> SALESMAN TO SALESREP, CUSTOMER-NUMBER TO CUSTNUM);
This example loads data into a table from an Enscribe file. Some of the source numeric
fields contain spaces, which are allowed in Enscribe files but are not allowed in SQL
tables. The REPLACE SPACES WITH ZEROS option specifies converting numeric
decimal fields from blanks to zeros. Because the MOVEBYORDER option is the
default, the fields involved in the transfer from the source file must be compatible with
the data types and order of the receiving columns in the target table.
>> LOAD $ENSC.SALES.ORDERS, $VOL1.SALES.ORDERS,
+> REPLACE SPACES WITH ZEROES
+> SOURCEDICT $ENSC.SALES SOURCEREC ORDERREC;