Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problem about compression in loading to new part

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Victor Niu

New User


Joined: 11 Mar 2010
Posts: 59
Location: China,Shanghai

PostPosted: Tue Aug 24, 2010 2:36 pm    Post subject: Problem about compression in loading to new part
Reply with quote

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;                                                   
           
  COMMIT;
"Code'd"
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Aug 24, 2010 6:22 pm    Post subject:
Reply with quote

Hi Niu,

Can you show us your load statements which was used during both the loads ?

Sushanth
Back to top
View user's profile Send private message
Victor Niu

New User


Joined: 11 Mar 2010
Posts: 59
Location: China,Shanghai

PostPosted: Wed Aug 25, 2010 6:48 am    Post subject:
Reply with quote

Data for part 2 like this:

2010,08,12,00:00:00,21581,18.5,0,N/A
2010,08,12,00:00:00,21582,4.3,0,N/A
2010,08,12,00:00:00,21583,0.2,0,N/A
2010,08,12,00:00:00,21584,2.3,0,N/A
2010,08,12,00:00:00,21585,9.9,0,N/A
2010,08,12,00:00:00,21586,4.8,0,N/A
2010,08,12,00:00:00,21587,316.8,0,N/A
2010,08,12,00:00:00,21588,2295.0,0,N/A
2010,08,12,00:00:00,21589,0.0,0,N/A
2010,08,12,00:00:00,21590,0.0,0,N/A
2010,08,12,00:00:00,21591,0.7,0,N/A
2010,08,12,00:00:00,21592,0.4,0,N/A
2010,08,12,00:00:00,21593,0.0,0,N/A
2010,08,12,00:00:00,21594,1.1,0,N/A
2010,08,12,00:00:00,21595,0.0,0,N/A
2010,08,12,00:00:00,21596,0.0,0,N/A
2010,08,12,00:00:00,21597,0.0,0,N/A
2010,08,12,00:00:00,21598,0.0,0,N/A
2010,08,12,00:00:00,21599,18.0,0,N/A
2010,08,12,00:00:00,21600,17.0,0,N/A
2010,08,12,00:00:00,21601,8.0,0,N/A
2010,08,12,00:00:00,21602,0.0,0,N/A
2010,08,12,00:00:00,21603,0.0,0,N/A
2010,08,12,00:00:00,21604,14.0,0,N/A
2010,08,12,00:00:00,21605,14.0,0,N/A
2010,08,12,00:00:00,21606,0.0,0,N/A
2010,08,12,00:00:00,21607,19.0,0,N/A
.......

Data for part 3 like this:

2010,09,06,00:00:00,21581,18.5,0,N/A
2010,09,06,00:00:00,21582,4.3,0,N/A
2010,09,06,00:00:00,21583,0.2,0,N/A
2010,09,06,00:00:00,21584,2.3,0,N/A
2010,09,06,00:00:00,21585,9.9,0,N/A
2010,09,06,00:00:00,21586,4.8,0,N/A
2010,09,06,00:00:00,21587,316.8,0,N/A
2010,09,06,00:00:00,21588,2295.0,0,N/A
2010,09,06,00:00:00,21589,0.0,0,N/A
2010,09,06,00:00:00,21590,0.0,0,N/A
2010,09,06,00:00:00,21591,0.7,0,N/A
2010,09,06,00:00:00,21592,0.4,0,N/A
2010,09,06,00:00:00,21593,0.0,0,N/A
2010,09,06,00:00:00,21594,1.1,0,N/A
2010,09,06,00:00:00,21595,0.0,0,N/A
2010,09,06,00:00:00,21596,0.0,0,N/A
2010,09,06,00:00:00,21597,0.0,0,N/A
2010,09,06,00:00:00,21598,0.0,0,N/A
2010,09,06,00:00:00,21599,18.0,0,N/A
2010,09,06,00:00:00,21600,17.0,0,N/A
2010,09,06,00:00:00,21601,8.0,0,N/A
2010,09,06,00:00:00,21602,0.0,0,N/A
2010,09,06,00:00:00,21603,0.0,0,N/A
2010,09,06,00:00:00,21604,14.0,0,N/A
2010,09,06,00:00:00,21605,14.0,0,N/A
2010,09,06,00:00:00,21606,0.0,0,N/A
2010,09,06,00:00:00,21607,19.0,0,N/A

DB2 is V8.
Back to top
View user's profile Send private message
Victor Niu

New User


Joined: 11 Mar 2010
Posts: 59
Location: China,Shanghai

PostPosted: Wed Aug 25, 2010 6:50 am    Post subject:
Reply with quote

Load statement is:

LOAD DATA INDDN(SYSREC) FORMAT DELIMITED COLDEL ','
NOCOPYPEND LOG NO RESUME YES
DISCARDDN(SYSDISC)
INTO TABLE TBSM02.PERFDAT
(
SYEAR CHAR,
SMONTH CHAR,
SDAY CHAR,
STIME TIME EXTERNAL,
ID INTEGER EXTERNAL,
VALUE FLOAT EXTERNAL,
ISHOLIDAY INTEGER EXTERNAL,
COMMENT VARCHAR
)
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Aug 25, 2010 7:12 am    Post subject:
Reply with quote

Niu,

I think you must do a LOAD RESUME NO REPLACE to build dictionary.

Try and let us know.

Sushanth
Back to top
View user's profile Send private message
Victor Niu

New User


Joined: 11 Mar 2010
Posts: 59
Location: China,Shanghai

PostPosted: Wed Aug 25, 2010 7:58 am    Post subject:
Reply with quote

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

the same result.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Aug 25, 2010 8:21 am    Post subject:
Reply with quote

Niu,

Can i know the reason for running the load with RESUME YES on partition 2.

Sushanth
Back to top
View user's profile Send private message
Victor Niu

New User


Joined: 11 Mar 2010
Posts: 59
Location: China,Shanghai

PostPosted: Wed Aug 25, 2010 9:12 am    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Aug 25, 2010 5:12 pm    Post subject:
Reply with quote

Niu,

LOAD RESUME YES will build compression dictionaries only for empty table
spaces.

Try running DSN1COMP on your tablespace dataset, to see the space savings.

Sushanth
Back to top
View user's profile Send private message
Victor Niu

New User


Joined: 11 Mar 2010
Posts: 59
Location: China,Shanghai

PostPosted: Thu Aug 26, 2010 1:57 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Aug 26, 2010 4:38 pm    Post subject:
Reply with quote

Quote:
Last modified date 2009-07-01

wow, you're a little behind with maintenance
Back to top
View user's profile Send private message
Victor Niu

New User


Joined: 11 Mar 2010
Posts: 59
Location: China,Shanghai

PostPosted: Fri Aug 27, 2010 11:19 am    Post subject:
Reply with quote

The PTF level is RSU0903 now.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm
No new posts Problem with GETMAIN command amitc23 CICS 6 Thu Sep 01, 2016 1:01 pm
No new posts TXT2PDF problem - printing @ in PDF vipul65 All Other Mainframe Topics 9 Thu Aug 25, 2016 12:48 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us