How do i load data from one table to a duplicate in SQL/MP?

[JohnBresina]

I know the what the table/partition specifications are and have built a copy of the table.  Now trying to load the data from the original table to the copy.  when i run the load, partition 0 gets data but all other partitions are empty.  I am confused on figuring out the Firstkey parameters.

[Suman_1978]

Hi,

Was there any error code or message that was displayed?

There are couple of articles for your help.
https://support.hpe.com/hpesc/public/docDisplay?docLocale=en_US&docId=c02132125
https://support.hpe.com/hpesc/public/docDisplay?docLocale=en_US&docId=c02148175
https://support.hpe.com/hpesc/public/docDisplay?docLocale=en_US&docId=c02132113

Thank You!
I work with HPE but opinions expressed here are mine.
HPE Tech Tips videos on How To and Troubleshooting topics

[JohnBresina]

@Suman_1978 

I have looked at those documents.  However that really doesn't explain or help.  

Here is what the definition for the primary key is:

PRIMARY KEY ( PART_ID ASC
, RTLR_ID ASC
, STORE_ID ASC
, TERM_ID ASC
, TRAN_SEQ_NUM ASC
, TRAN_DATE DESC
, TRAN_TIME DESC
)

example of the 1st secondary partition

PARTITION ( $STR44.JTT10S36.S36ORD
CATALOG $STR1.SQ94LIB
EXTENT (5000, 5000) MAXEXTENTS 915 FORMAT 2
FIRST KEY (10)

example of the load statement i am trying to use for primary  and 1st secondary partition

LOAD $STR41.PTT10S36.S36ORD,$STR41.JTT10S36.S36ORD,EMPTYOK,SORTED,PARTONLY,MOVEB
YNAME,SLACK 5;
LOAD $STR41.PTT10S36.S36ORD,$STR44.JTT10S36.S36ORD,EMPTYOK,SORTED,PARTONLY,
FIRST KEY (10) ,MOVEBYNAME,SLACK 5;

[Vinky_99]

---

@JohnBresina wrote:

I know the what the table/partition specifications are and have built a copy of the table.  Now trying to load the data from the original table to the copy.  when i run the load, partition 0 gets data but all other partitions are empty.  I am confused on figuring out the Firstkey parameters.

---

Good day!

When you create a duplicate table and want to load data from the original table into the copy, you need to ensure that the data is distributed correctly across the partitions in the copy. To do this, you typically use the FIRSTKEY and FIRSTINDEX options when defining your partitions and when performing the load operation.

Here's a step-by-step guide on how to load data from one table to a duplicate in SQL/MP:

1. Create a Duplicate Table>> Ensure that you have already created a duplicate table with the same structure as the original table. You can create the table using the CREATE TABLE statement.

2. Partition the Duplicate Table>> If the original table is partitioned, you should partition the duplicate table in a similar way. Use the same partitioning criteria (e.g., a range of values) or follow the original table's partitioning logic.

For example, if the original table is partitioned by date, you should create partitions in the duplicate table with the same date ranges.

3. Load Data>> When loading data from the original table into the duplicate table, use the FIRSTKEY and FIRSTINDEX options to specify how data should be distributed across partitions in the duplicate table. These options are crucial for ensuring data is correctly loaded into all partitions.

The FIRSTKEY parameter defines the partitioning key column that determines how data is distributed across partitions. This column's values in the original table should match the values in the duplicate table's partitioning key column.

The FIRSTINDEX parameter specifies the target partition number in the duplicate table. It should correspond to the partition number in the original table for that data.

For example, if the original table is partitioned by date, and you are copying data for a specific date range, you would specify the appropriate date in the FIRSTKEY and the corresponding partition number in FIRSTINDEX.

Here's an example of a LOAD statement with FIRSTKEY and FIRSTINDEX:

LOAD FROM original_table
INSERT INTO duplicate_table
WITH (FIRSTKEY=date_column, FIRSTINDEX=3)
WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';

In this example, we are loading data from the original table to the duplicate table, and we specify that the date_column is the partitioning key. We want the data to go into partition 3 of the duplicate table. Adjust the values accordingly based on your specific partitioning scheme.

By correctly setting the FIRSTKEY and FIRSTINDEX parameters, you ensure that data is distributed across partitions in the duplicate table as it was in the original table.

Hope this helps! Let me know...