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
 

 

loading a partitioned table

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

Senior Member


Joined: 01 Sep 2006
Posts: 2019
Location: Silicon Valley

PostPosted: Sat Oct 18, 2014 6:20 am    Post subject: loading a partitioned table
Reply with quote

It lets me unload in one utility statement.

But when I try to load again, it makes me specify a partition number:
Code:
LOAD DATA RESUME YES SORTKEYS 8 FORMAT INTERNAL ENFORCE NO           
 -   INTO TABLE PEDRO.ZZZZZZ  DISCARDDN(DISCARDN) PART 1 INDDN SYSREC

I specified a template for DISCARDN.

When I do a load, I get a bunch of these:
Code:
RECORD (nn) FOR PARTITION 1 WILL BE DISCARDED DUE TO PART OR HIGH KEY VALUE

It seems like I have to load each partition individually. It is going to read my SYSREC file multiple times. Is there a way to load all partitions at once?
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Sat Oct 18, 2014 9:41 pm    Post subject:
Reply with quote

What utility you are using ?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Oct 20, 2014 3:08 pm    Post subject:
Reply with quote

Hi Pedro,

You are trying to load at partition level(ie., when you specify INDDN after INTO TABLE statement)
Code:

LOAD DATA RESUME YES SORTKEYS 8 FORMAT INTERNAL ENFORCE NO           
 -   INTO TABLE PEDRO.ZZZZZZ  DISCARDDN(DISCARDN) PART 1 INDDN SYSREC

To load all the partitions at once when you don't know in which partition data will go into, load at table level and concatenate the input datasets in SYSREC. Like below for example.

Code:
//S1    EXEC PGM=DSNUTILB,                                   
//   PARM=(DB2D,'DBATEST',''),
//   REGION=500M                                               
//STEPLIB  DD DISP=SHR,DSN=SYSDB2.DB2D.SDSNLOAD               
//SYSPRINT DD SYSOUT=*                                       
//SYSUDUMP DD SYSOUT=*                                       
//UTPRINT  DD SYSOUT=*                                       
//SYSOUT   DD SYSOUT=*                                       
//SYSREC00 DD DISP=SHR,DSN=TEST.DBA.WEB.APP.SNAP1
//         DD DISP=SHR,DSN=TEST.DBA.WEB.APP.SNAP2
//         DD DISP=SHR,DSN=TEST.DBA.WEB.APP.SNAP3
//SYSDISC  DD DUMMY                                           
//SYSUT1   DD UNIT=DASD,SPACE=(CYL,(200,200))                 
//SORTOUT  DD UNIT=DASD,SPACE=(CYL,(200,200))                 
//SYSMAP   DD UNIT=DASD,SPACE=(CYL,(200,200))                 
//SYSERR   DD UNIT=DASD,SPACE=(CYL,(200,200))                 
//SYSIN    DD *                                               

LOAD DATA REPLACE LOG NO NOCOPYPEND DISCARDS 1   
 INDDN SYSREC00
 SORTDEVT SYSDA SORTNUM 24                       
 SORTKEYS 1000000000                             
 UNICODE CCSID(00367,01208,01200)               
 STATISTICS  TABLE (ALL) INDEX (ALL) KEYCARD     
 INTO TABLE                                     
 "SUSHANTH".                                     
 "TTEST_WEB_SNAPSHOTS"
 ( "SNAPSHOT_ID"   POSITION(  00003:00006) INTEGER
....
....


Thanks,
Sushanth
Back to top
View user's profile Send private message
Pedro

Senior Member


Joined: 01 Sep 2006
Posts: 2019
Location: Silicon Valley

PostPosted: Tue Oct 21, 2014 1:09 am    Post subject: Reply to: loading a partitioned table
Reply with quote

Sushanth, your example does not include DISCARDDN, which is a big part of my problem. When you specify DISCARDDN, then it requires 'PART n' to be specified. I guess you are supposed to use a different DISCARDDN for each partition.

I was expecting that a TEMPLATE would resolve this problem, but it does not.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Oct 21, 2014 3:13 am    Post subject:
Reply with quote

Pedro,
I think one of the following may get a solution to your problem,
1)Will below multiple steps does what you ask?

LOAD DATA INDDN SYSREC LOG NO
INTO TABLE PEDRO.ZZZZZZ PART 1 REPLACE
LOAD DATA INDDN SYSREC2 LOG NO
INTO TABLE PEDRO.ZZZZZZ PART 2 REPLACE
...
....

So This will do it in one job .

2) Use OVERRIDE(IDENTITY) to overcome the error.
3) Or Do not specify PART and then by default it will load everything.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Oct 21, 2014 3:08 pm    Post subject:
Reply with quote

Hi Pedro,

I tried below it worked. I am working on DB2 V10 and this table has 10 partitions. Below example is a LOAD RESUME, table has already some millions of data and data in the input file might contain duplicates which are already existing in the table and in the load process i want that to be discarded as well.

Code:
//JSTEP020 EXEC @IBMLOAD,SYSTEM=db2d,UID=SUSHANTH,SIZE=0M,           
//             UTPROC='RESTART(CURRENT)',                           
//             SYSIN=NULLFILE,INDSN=NULLFILE,WORKSP1=350,WORKSP2=250,
//             ERRSP1=350,WORKSP2=250                               
//STEP010.SYSREC DD DISP=SHR,                                       
//    DSN=SUSHANTH.TEST.WEBAPP.B00001.R13                 
//    DD DISP=SHR,                                                   
//    DSN=SUSHANTH.TEST.WEBAPP.B00002.R13                 
//    DD DISP=SHR,                                             
//    DSN=SUSHANTH.TEST.WEBAPP.B00003.R13         
//    DD DISP=SHR,                                                 
//    DSN=SUSHANTH.TEST.WEBAPP.B00010.R13             
//STEP010.SORTOUT DD VOL=(,,,40)                                       
//STEP010.SYSUT1 DD VOL=(,,,40)                                         
//STEP010.SYSERR DD DSN=SUSHANTH.TEST.WEBAPP.SYSERR,           
//             DISP=(MOD,DELETE,CATLG),                                 
//             SPACE=(CYL,(350,250),RLSE),UNIT=DASD,                   
//             VOL=(,,,59)                                             
//STEP010.SYSIN DD *                                                   
TEMPLATE SDISC                                                         
     DSN 'SUSHANTH.TEST.&TS..SYSDISC'                               
     DISP (NEW,CATLG,CATLG)                                             
     MAXPRIME 500                                                       
                                                                       
LOAD DATA RESUME YES LOG NO DISCARDS 0                                 
 DISCARDDN SDISC                                                       
 SORTDEVT SYSDA SORTNUM 24                                             
 SORTKEYS 50000                                                         
 UNICODE CCSID(00367,01208,01200)                                       
INTO TABLE                               
"SUSHANTH".                               
"WEBAPP"               
( "SNAPSHOT_ID"                           
 POSITION(  00003:00006) INTEGER         
, "INTERNAL_CUST_ID"                     
 POSITION(  00007:00010) INTEGER         

Thanks,
Sushanth
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 Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm
No new posts Difference space showed for TS and it... autobox DB2 1 Thu Apr 14, 2016 1:07 pm


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