Joined: 11 Mar 2010 Posts: 59 Location: China,Shanghai
The table devide into 3 parts,a half month for one part.
When I load some data to part 3 for the empty table
job log description is:
DSNU241I -DSTA DSNURBDC - DICTIONARY WITH 4096 ENTRIES HAS BEEN SUCCESSFULLY BUILT FROM 12635 ROWS FOR TABLE SPACE TBSMDB.TSPERFD, PARTITION 3
after runstats,the information in catalog is:
Code:
SPACE COMPRESS PAGESAVE STATSTIME
+---------+---------+---------+---------+---------+--------
28800 Y 0 2010-08-24-16.51.00.173543
28800 Y 0 2010-08-24-16.51.00.173543
604800 Y 46 2010-08-24-16.51.00.173543
it means part 3 compression occured.
then I changed the partition key to load the same data to part 2.
jog log decription is:
Code:
DSNU650I -DSTA DSNURWI - INTO TABLE TBSM02.PERFDAT
DSNU650I -DSTA DSNURWI - (SYEAR CHAR,
DSNU650I -DSTA DSNURWI - SMONTH CHAR,
DSNU650I -DSTA DSNURWI - SDAY CHAR,
DSNU650I -DSTA DSNURWI - STIME TIME EXTERNAL,
DSNU650I -DSTA DSNURWI - ID INTEGER EXTERNAL,
DSNU650I -DSTA DSNURWI - VALUE FLOAT EXTERNAL,
DSNU650I -DSTA DSNURWI - ISHOLIDAY INTEGER EXTERNAL,
DSNU650I -DSTA DSNURWI - COMMENT VARCHAR)
DSNU395I DSNURPIB - INDEXES WILL BE BUILT IN PARALLEL, NUMBER OF TASKS = 4
DSNU304I -DSTA DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=378955 F
DSNU1147I -DSTA DSNURWT - (RE)LOAD PHASE STATISTICS - TOTAL NUMBER OF RECORDS LO
TBSMDB.TSPERFD
DSNU302I DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCE
DSNU300I DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:03
no compress statement.
after runstats,the information of catalog is:
Code:
+---------+---------+---------+---------+---------+--------
SPACE COMPRESS PAGESAVE STATSTIME
+---------+---------+---------+---------+---------+--------
28800 Y 0 2010-08-24-16.58.45.544445
604800 Y 0 2010-08-24-16.58.45.544445
604800 Y 46 2010-08-24-16.58.45.544445
PAGESAVE for part 2 is still 0.
Pls can anyone tell me something the reason?
Here is the SQL statement about create the table:
Code:
--------------------------------------
-- DATABASE=TBSMDB STOGROUP=SGDSTA01
-- TABLESPACE=TBSM02.TSPERFD
--------------------------------------
CREATE TABLESPACE TSPERFD
IN TBSMDB
USING STOGROUP SGDSTA01
PRIQTY 28800 SECQTY 576000
FREEPAGE 31 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
DSSIZE 4 G
NUMPARTS 3
BUFFERPOOL BP4
LOCKSIZE PAGE
LOCKMAX SYSTEM
CLOSE NO
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;
COMMIT;
-----------------------------------------------------------------
-- DATABASE=TBSMDB STOGROUP=SGDSTA01
-- TABLESPACE=TBSM02.TSPERFD
-- TABLE=TBSM02.PERFDAT
-----------------------------------------------------------------
CREATE TABLE TBSM02.PERFDAT
(SYEAR CHAR(4) FOR MIXED DATA NOT NULL,
SMONTH CHAR(2) FOR MIXED DATA NOT NULL,
SDAY CHAR(2) FOR MIXED DATA NOT NULL,
STIME TIME NOT NULL,
ID INTEGER NOT NULL,
"VALUE" REAL WITH DEFAULT NULL,
ISHOLIDAY INTEGER WITH DEFAULT 0,
"COMMENT" VARCHAR(20) FOR MIXED DATA
WITH DEFAULT NULL,
CONSTRAINT SYEAR
PRIMARY KEY (SYEAR,
SMONTH,
SDAY,
STIME,
ID))
IN TBSMDB.TSPERFD
PARTITION BY (SYEAR ASC,
SMONTH ASC,
SDAY ASC)
(PART 1 VALUES('2010', '08', '15'),
PART 2 VALUES('2010', '08', '31'),
PART 3 VALUES('2010', '09', '15'))
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
------------------------------------------------
-- DATABASE=TBSMDB
-- INDEX=TBSM02.IXPERFD ON TBSM02.PERFDAT
------------------------------------------------
CREATE UNIQUE INDEX TBSM02.IXPERFD
ON TBSM02.PERFDAT
(SYEAR ASC,
SMONTH ASC,
SDAY ASC,
STIME ASC,
ID ASC)
PARTITIONED
USING STOGROUP SGDSTA01
PRIQTY 28800 SECQTY 360000
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
(PART 1,
PART 2,
PART 3)
BUFFERPOOL BP4
CLOSE NO
COPY NO
DEFINE YES;
------------------------------------------------------------------
-- DATABASE=TBSMDB
-- INDEX=TBSM02.IXPERFDA ON TBSM02.PERFDAT (ALTERNATE INDEX)
------------------------------------------------------------------
CREATE INDEX TBSM02.IXPERFDA
ON TBSM02.PERFDAT
(ID ASC,
ISHOLIDAY ASC,
STIME ASC,
"VALUE" ASC)
PARTITIONED
USING STOGROUP SGDSTA01
PRIQTY 28800 SECQTY 360000
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
(PART 1,
PART 2,
PART 3)
BUFFERPOOL BP4
CLOSE NO
COPY NO
DEFINE YES;
Joined: 11 Mar 2010 Posts: 59 Location: China,Shanghai
yes,I have tried.
RESUME NO REPLACE will empty the huge table.
when I load date to part 3 using RESUME NO REPLACE,the job log is:
DSNU650I -DSTA DSNURWI - INTO TABLE TBSM02.PERFDAT
DSNU650I -DSTA DSNURWI - (SYEAR CHAR,
DSNU650I -DSTA DSNURWI - SMONTH CHAR,
DSNU650I -DSTA DSNURWI - SDAY CHAR,
DSNU650I -DSTA DSNURWI - STIME TIME EXTERNAL,
DSNU650I -DSTA DSNURWI - ID INTEGER EXTERNAL,
DSNU650I -DSTA DSNURWI - VALUE FLOAT EXTERNAL,
DSNU650I -DSTA DSNURWI - ISHOLIDAY INTEGER EXTERNAL,
DSNU650I -DSTA DSNURWI - COMMENT VARCHAR)
DSNU350I -DSTA DSNURRST - EXISTING RECORDS DELETED FROM TABLESPACE
DSNU395I DSNURPIB - INDEXES WILL BE BUILT IN PARALLEL, NUMBER OF TASKS = 4
DSNU241I -DSTA DSNURBDC - DICTIONARY WITH 4096 ENTRIES HAS BEEN SUCCESSFULLY BU
TBSMDB.TSPERFD, PARTITION 3
Now the part 3 compressed.
then I load data to part 2 using RESUME YES,the joblog is:
DSNU650I -DSTA DSNURWI - INTO TABLE TBSM02.PERFDAT
DSNU650I -DSTA DSNURWI - (SYEAR CHAR,
DSNU650I -DSTA DSNURWI - SMONTH CHAR,
DSNU650I -DSTA DSNURWI - SDAY CHAR,
DSNU650I -DSTA DSNURWI - STIME TIME EXTERNAL,
DSNU650I -DSTA DSNURWI - ID INTEGER EXTERNAL,
DSNU650I -DSTA DSNURWI - VALUE FLOAT EXTERNAL,
DSNU650I -DSTA DSNURWI - ISHOLIDAY INTEGER EXTERNAL,
DSNU650I -DSTA DSNURWI - COMMENT VARCHAR)
DSNU395I DSNURPIB - INDEXES WILL BE BUILT IN PARALLEL, NUMBER OF TASKS = 4
DSNU304I -DSTA DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=378955
DSNU1147I -DSTA DSNURWT - (RE)LOAD PHASE STATISTICS - TOTAL NUMBER OF RECORDS L
TBSMDB.TSPERFD
DSNU302I DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROC
DSNU300I DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:03
DSNU393I -DSTA DSNURBXA - SORTBLD PHASE STATISTICS - NUMBER OF KEYS=378955 FOR
DSNU393I -DSTA DSNURBXA - SORTBLD PHASE STATISTICS - NUMBER OF KEYS=378955 FOR
DSNU391I DSNURPTB - SORTBLD PHASE STATISTICS. NUMBER OF INDEXES = 2
DSNU392I DSNURPTB - SORTBLD PHASE COMPLETE, ELAPSED TIME = 00:00:01
DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0
No compression statement found.
after runstats, the catalog information is:
SPACE COMPRESS PAGESAVE STATSTIME
+---------+---------+---------+---------+---------+--------
28800 Y 0 2010-08-25-10.25.32.775382
604800 Y 0 2010-08-25-10.25.32.775382
604800 Y 46 2010-08-25-10.25.32.775382
Joined: 11 Mar 2010 Posts: 59 Location: China,Shanghai
Hello,Sushanth
Do you mean I can specify the part number in load statement like this:
LOAD DATA INDDN(SYSREC) FORMAT DELIMITED COLDEL ','
NOCOPYPEND LOG NO RESUME YES
DISCARDDN(SYSDISC)
INTO TABLE TBSM02.PERFDAT PART 2 REPLACE
(
SYEAR CHAR,
SMONTH CHAR,
SDAY CHAR,
STIME TIME EXTERNAL,
ID INTEGER EXTERNAL,
VALUE FLOAT EXTERNAL,
ISHOLIDAY INTEGER EXTERNAL,
COMMENT VARCHAR
)
Obviously,the compress dictionary will be built in this loading.
But it is not convenient for my requirements.I have to load the data every day,if use replace,the previous data will be cleared for one part;if change the JCL from REPLACE to RESUME YES according to the load date,it will be very complicated for my loading automation.
The core problem is: Why doesn't the table build the compress dictionary in loading the next part?
Joined: 11 Mar 2010 Posts: 59 Location: China,Shanghai
Need a PTF...
APAR status
Closed as program error.
Error description
Retrofit of V9 code to allow LOAD RESUME YES to build
compression dictionaries for physically empty partitions. APAR
PK70866 must also be applied.
Local fix
LOAD PART REPLACE or REORG PART, rather than LOAD RESUME YES
Problem summary
****************************************************************
* USERS AFFECTED: All DB2 for z/OS V8 utility users of LOAD *
* RESUME YES on a COMPRESS YES partitioned *
* table space *
****************************************************************
* PROBLEM DESCRIPTION: LOAD RESUME YES on a COMPRESS YES *
* partitioned table space does not build *
* compression dictionaries for logically *
* empty partitions even if the partition *
* was never populated with data *
****************************************************************
* RECOMMENDATION: Apply corrective PTF when available *
****************************************************************
LOAD RESUME YES on a COMPRESS YES partitioned table space does
not build compression dictionaries for logically empty
partitions even if the partition never contained data.
Prior to this fix, LOAD RESUME YES on a COMPRESS YES partitioned
table space would only build compression dictionaries for
physically empty (untouched) partitions. However, partitions
that never had any data LOADed or INSERTed into them may be
considered logically empty in cases where a prior LOAD reserved
space for a compression dictionary that was never built.
Problem conclusion
LOAD RESUME YES code has been modified to build compression
dictionaries on logically empty COMPRESS YES partitions where
possible. Specifically, when the HI-U-RBA divided by the
page size is less than 20 pages. If HI-U-RBA divided by
the page size is greater than or equal to 20 pages, a
LOAD PART REPLACE with DUMMY input is recommended on the
logically empty partition(s). This LOAD PART REPLACE with
DUMMY input will reduce the number of pages that are
preformatted, so the subsequent LOAD RESUME YES will be
able to check for logical empty since the HI-U-RBA divided
by page size will now be less than 20 pages.
Temporary fix
Comments
APAR Information
APAR numberPK84764
Reported component nameDB2 OS/390 & Z/
Reported component ID5740XYR00
Reported release810
StatusCLOSED PER
PENoPE
HIPERNoHIPER
Special AttentionNoSpecatt
Submitted date2009-04-15
Closed date2009-05-21
Last modified date2009-07-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following: UK46797
Modules/Macros
DSNPIPP DSNURWI
Fix information
Fixed component nameDB2 OS/390 & Z/
Fixed component ID5740XYR00
Applicable component levels
R810 PSY UK46797 UP09/06/05 P F906
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
