Load with enforce constraints DB2 Z/OS V7

Amit Agarwal

Load with enforce constraints DB2 Z/OS V7
DB2 V7

Listers,

I have a table with 25 partitions and 6 indexes.
Tried to load the table(10,000 rows only) with enforce constraints and
statistics table(all) index(all). The job just hung on the build phase while
on the 3rd partition.

We tried a couple of things, like dropping all except partitioned indexes,
increasing or decreasing sortwork, nothing seemed to work. Then used ENFORCE
NO and removed the statistics, the job run fine.

I had never used ENFORCE CONSTRAINTS before, usually I have preferred to run
the check utility separately.

Just wondering if anyone has experienced problems with the ENFORCE
CONSTRAINTS option before.

Thanks
Amit Agarwal

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Bala

Re: Load with enforce constraints DB2 Z/OS V7
(in response to Amit Agarwal)
Amit,

Can you post the JCL that you used for LOADing ?




On Mon, Mar 10, 2008 at 2:29 PM, Amit Agarwal <[login to unmask email]> wrote:

> DB2 V7
>
> Listers,
>
> I have a table with 25 partitions and 6 indexes.
> Tried to load the table(10,000 rows only) with enforce constraints and
> statistics table(all) index(all). The job just hung on the build phase while
> on the 3rd partition.
>
> We tried a couple of things, like dropping all except partitioned indexes,
> increasing or decreasing sortwork, nothing seemed to work. Then used ENFORCE
> NO and removed the statistics, the job run fine.
>
> I had never used ENFORCE CONSTRAINTS before, usually I have preferred to
> run the check utility separately.
>
> Just wondering if anyone has experienced problems with the ENFORCE
> CONSTRAINTS option before.
>
> Thanks
> Amit Agarwal
>
> ------------------------------
>
> *IDUG 2008 - North America * May 18-22, 2008 * Dallas, TX, USA* < http://idug.org/lsna >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. *If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on Member
> Services < http://www.idug.org/lsms > *
>

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Amit Agarwal

Re: Load with enforce constraints DB2 Z/OS V7
(in response to Bala)
Here's the JCL. After I changed the load card to Enforce no and removed the
runstats, everything worked fine.

Thanks

//STEP02 EXEC PGM=DSNUTILB,

// COND=(4,LT),

// REGION=0M,

// PARM='DB2C,ITS0037D'

//SYSREC DD DISP=OLD,DSN=NPCAT.CLMSRCH.LOADFILE

//*

//SYSDISC DD DSN=NPCAT.ASAQ.CLMSRCH.DISCARD,

// DISP=(MOD,DELETE,CATLG),

// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)

//SYSERR DD DSN=NPCAT.ASAQ.CLMSRCH.SYSERR,

// DISP=(MOD,DELETE,CATLG),

// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)

//SYSMAP DD DSN=NPCAT.ASAQ.CLMSRCH.SYSMAP,

// DISP=(MOD,DELETE,CATLG),

// UNIT=SYSALLDA,SPACE=(CYL,(50,150),RLSE)

//SYSPRINT DD SYSOUT=*

//UTPRINT DD SYSOUT=*

//SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTOUT DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK06 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK07 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK08 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK09 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SYSIN DD *

LOAD DATA REPLACE

LOG NO
ENFORCE CONSTRAINTS

RUNSTATS TABLE(ALL) INDEX(ALL)

NOCOPYPEND

DISCARDS 1000

INTO TABLE ITSASAQ.CLM_SEARCH


On 3/11/08, Bala <[login to unmask email]> wrote:
>
> Amit,
>
> Can you post the JCL that you used for LOADing ?
>
>
>
>
> On Mon, Mar 10, 2008 at 2:29 PM, Amit Agarwal <[login to unmask email]>
> wrote:
>
> > DB2 V7
> >
> > Listers,
> >
> > I have a table with 25 partitions and 6 indexes.
> > Tried to load the table(10,000 rows only) with enforce constraints and
> > statistics table(all) index(all). The job just hung on the build phase while
> > on the 3rd partition.
> >
> > We tried a couple of things, like dropping all except partitioned
> > indexes, increasing or decreasing sortwork, nothing seemed to work. Then
> > used ENFORCE NO and removed the statistics, the job run fine.
> >
> > I had never used ENFORCE CONSTRAINTS before, usually I have preferred to
> > run the check utility separately.
> >
> > Just wondering if anyone has experienced problems with the ENFORCE
> > CONSTRAINTS option before.
> >
> > Thanks
> > Amit Agarwal
> >
> > ------------------------------
> >
> > *IDUG 2008 - North America * May 18-22, 2008 * Dallas, TX, USA* < http://idug.org/lsna >
> >
> > The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> > DB2-L list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> > * under the Listserv tab. While at the site, you can also access the
> > IDUG Online Learning Center, Tech Library and Code Place, see the latest
> > IDUG *conference information < http://www.idug.org/lsconf > *, and much
> > more. *If you have not yet signed up for Basic Membership in IDUG,
> > available at no cost, click on Member Services < http://www.idug.org/lsms >
> > *
> >
>
>
> ------------------------------
>
> *IDUG 2008 - India * 21-23 August 2008 * Bangalore, India* < http://idug.org/lsin >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. *If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on Member
> Services < http://www.idug.org/lsms > *
>

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

steve mallett

Re: Load with enforce constraints DB2 Z/OS V7
(in response to Amit Agarwal)
Amit,

The job probably only appears to go inactive upon entering SORT, especially if
there are a lot of rows and//or foreign keys involved,

HTH
regards,
Steve

On Tue, 11 Mar 2008 11:58:16 +0530, Amit Agarwal
<[login to unmask email]> wrote:

> Here's the JCL. After I changed the load card to Enforce no and removed the
>runstats, everything worked fine.
>
>Thanks
>
>//STEP02 EXEC PGM=DSNUTILB,
>
>// COND=(4,LT),
>
>// REGION=0M,
>
>// PARM='DB2C,ITS0037D'
>
>//SYSREC DD DISP=OLD,DSN=NPCAT.CLMSRCH.LOADFILE
>
>//*
>
>//SYSDISC DD DSN=NPCAT.ASAQ.CLMSRCH.DISCARD,
>
>// DISP=(MOD,DELETE,CATLG),
>
>// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
>
>//SYSERR DD DSN=NPCAT.ASAQ.CLMSRCH.SYSERR,
>
>// DISP=(MOD,DELETE,CATLG),
>
>// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
>
>//SYSMAP DD DSN=NPCAT.ASAQ.CLMSRCH.SYSMAP,
>
>// DISP=(MOD,DELETE,CATLG),
>
>// UNIT=SYSALLDA,SPACE=(CYL,(50,150),RLSE)
>
>//SYSPRINT DD SYSOUT=*
>
>//UTPRINT DD SYSOUT=*
>
>//SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTOUT DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK06 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK07 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK08 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK09 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SYSIN DD *
>
>LOAD DATA REPLACE
>
>LOG NO
>ENFORCE CONSTRAINTS
>
>RUNSTATS TABLE(ALL) INDEX(ALL)
>
>NOCOPYPEND
>
>DISCARDS 1000
>
>INTO TABLE ITSASAQ.CLM_SEARCH
>
>
>On 3/11/08, Bala <[login to unmask email]> wrote:
>>
>> Amit,
>>
>> Can you post the JCL that you used for LOADing ?
>>
>>
>>
>>
>> On Mon, Mar 10, 2008 at 2:29 PM, Amit Agarwal <[login to unmask email]>
>> wrote:
>>
>> > DB2 V7
>> >
>> > Listers,
>> >
>> > I have a table with 25 partitions and 6 indexes.
>> > Tried to load the table(10,000 rows only) with enforce constraints and
>> > statistics table(all) index(all). The job just hung on the build phase while
>> > on the 3rd partition.
>> >
>> > We tried a couple of things, like dropping all except partitioned
>> > indexes, increasing or decreasing sortwork, nothing seemed to work. Then
>> > used ENFORCE NO and removed the statistics, the job run fine.
>> >
>> > I had never used ENFORCE CONSTRAINTS before, usually I have
preferred to
>> > run the check utility separately.
>> >
>> > Just wondering if anyone has experienced problems with the ENFORCE
>> > CONSTRAINTS option before.
>> >
>> > Thanks
>> > Amit Agarwal
>> >
>> > ------------------------------
>> >
>> > *IDUG 2008 - North America * May 18-22, 2008 * Dallas, TX,
USA* < http://idug.org/lsna >
>> >
>> > The IDUG DB2-L Listserv is only part of your membership in IDUG. The
>> > DB2-L list archives, FAQ, and delivery preferences are at
*IDUG.ORG < http://www.idug.org/lsidug >
>> > * under the Listserv tab. While at the site, you can also access the
>> > IDUG Online Learning Center, Tech Library and Code Place, see the latest
>> > IDUG *conference information < http://www.idug.org/lsconf > *, and much
>> > more. *If you have not yet signed up for Basic Membership in IDUG,
>> > available at no cost, click on Member
Services < http://www.idug.org/lsms >
>> > *
>> >
>>
>>
>> ------------------------------
>>
>> *IDUG 2008 - India * 21-23 August 2008 * Bangalore,
India* < http://idug.org/lsin >
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
>> list archives, FAQ, and delivery preferences are at
*IDUG.ORG < http://www.idug.org/lsidug >
>> * under the Listserv tab. While at the site, you can also access the IDUG
>> Online Learning Center, Tech Library and Code Place, see the latest IDUG
*conference
>> information < http://www.idug.org/lsconf > *, and much more. *If you have
not
>> yet signed up for Basic Membership in IDUG, available at no cost, click on
Member
>> Services < http://www.idug.org/lsms > *
>>
>
>_________________________________________________________________
_____
>
>* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
>_________________________________________________________________
_____
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms
>

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Tejas Jadhav

Re: Load with enforce constraints DB2 Z/OS V7
(in response to steve mallett)
Dear Friend,
Why don't u slice the files into 2999 recs/file. approx. and run parallel
LOAD. with same option.



On Tue, Mar 11, 2008 at 1:53 PM, Steve <[login to unmask email]> wrote:

> Amit,
>
> The job probably only appears to go inactive upon entering SORT,
> especially if
> there are a lot of rows and//or foreign keys involved,
>
> HTH
> regards,
> Steve
>
> On Tue, 11 Mar 2008 11:58:16 +0530, Amit Agarwal
> <[login to unmask email]> wrote:
>
> > Here's the JCL. After I changed the load card to Enforce no and removed
> the
> >runstats, everything worked fine.
> >
> >Thanks
> >
> >//STEP02 EXEC PGM=DSNUTILB,
> >
> >// COND=(4,LT),
> >
> >// REGION=0M,
> >
> >// PARM='DB2C,ITS0037D'
> >
> >//SYSREC DD DISP=OLD,DSN=NPCAT.CLMSRCH.LOADFILE
> >
> >//*
> >
> >//SYSDISC DD DSN=NPCAT.ASAQ.CLMSRCH.DISCARD,
> >
> >// DISP=(MOD,DELETE,CATLG),
> >
> >// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
> >
> >//SYSERR DD DSN=NPCAT.ASAQ.CLMSRCH.SYSERR,
> >
> >// DISP=(MOD,DELETE,CATLG),
> >
> >// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
> >
> >//SYSMAP DD DSN=NPCAT.ASAQ.CLMSRCH.SYSMAP,
> >
> >// DISP=(MOD,DELETE,CATLG),
> >
> >// UNIT=SYSALLDA,SPACE=(CYL,(50,150),RLSE)
> >
> >//SYSPRINT DD SYSOUT=*
> >
> >//UTPRINT DD SYSOUT=*
> >
> >//SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTOUT DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTWK06 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTWK07 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTWK08 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SORTWK09 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
> >
> >//SYSIN DD *
> >
> >LOAD DATA REPLACE
> >
> >LOG NO
> >ENFORCE CONSTRAINTS
> >
> >RUNSTATS TABLE(ALL) INDEX(ALL)
> >
> >NOCOPYPEND
> >
> >DISCARDS 1000
> >
> >INTO TABLE ITSASAQ.CLM_SEARCH
> >
> >
> >On 3/11/08, Bala <[login to unmask email]> wrote:
> >>
> >> Amit,
> >>
> >> Can you post the JCL that you used for LOADing ?
> >>
> >>
> >>
> >>
> >> On Mon, Mar 10, 2008 at 2:29 PM, Amit Agarwal <[login to unmask email]>
> >> wrote:
> >>
> >> > DB2 V7
> >> >
> >> > Listers,
> >> >
> >> > I have a table with 25 partitions and 6 indexes.
> >> > Tried to load the table(10,000 rows only) with enforce constraints
> and
> >> > statistics table(all) index(all). The job just hung on the build
> phase while
> >> > on the 3rd partition.
> >> >
> >> > We tried a couple of things, like dropping all except partitioned
> >> > indexes, increasing or decreasing sortwork, nothing seemed to work.
> Then
> >> > used ENFORCE NO and removed the statistics, the job run fine.
> >> >
> >> > I had never used ENFORCE CONSTRAINTS before, usually I have
> preferred to
> >> > run the check utility separately.
> >> >
> >> > Just wondering if anyone has experienced problems with the ENFORCE
> >> > CONSTRAINTS option before.
> >> >
> >> > Thanks
> >> > Amit Agarwal
> >> >
> >> > ------------------------------
> >> >
> >> > *IDUG 2008 - North America * May 18-22, 2008 * Dallas, TX,
> USA* < http://idug.org/lsna >
> >> >
> >> > The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> >> > DB2-L list archives, FAQ, and delivery preferences are at
> *IDUG.ORG < http://www.idug.org/lsidug >
> >> > * under the Listserv tab. While at the site, you can also access the
> >> > IDUG Online Learning Center, Tech Library and Code Place, see the
> latest
> >> > IDUG *conference information < http://www.idug.org/lsconf > *, and much
> >> > more. *If you have not yet signed up for Basic Membership in IDUG,
> >> > available at no cost, click on Member
> Services < http://www.idug.org/lsms >
> >> > *
> >> >
> >>
> >>
> >> ------------------------------
> >>
> >> *IDUG 2008 - India * 21-23 August 2008 * Bangalore,
> India* < http://idug.org/lsin >
> >>
> >> The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> DB2-L
> >> list archives, FAQ, and delivery preferences are at
> *IDUG.ORG < http://www.idug.org/lsidug >
> >> * under the Listserv tab. While at the site, you can also access the
> IDUG
> >> Online Learning Center, Tech Library and Code Place, see the latest
> IDUG
> *conference
> >> information < http://www.idug.org/lsconf > *, and much more. *If you have
> not
> >> yet signed up for Basic Membership in IDUG, available at no cost, click
> on
> Member
> >> Services < http://www.idug.org/lsms > *
> >>
> >
> >_________________________________________________________________
> _____
> >
> >* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
> >_________________________________________________________________
> _____
> >
> >The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> DB2-L
> list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug
> under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG
> conference information and much more. If you have not yet signed up for
> Basic Membership in IDUG, available at no cost, click on Member Services
> at
> http://www.idug.org/lsms
> >
>
> ______________________________________________________________________
>
> * IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
> ______________________________________________________________________
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> DB2-L list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
> can also access the IDUG Online Learning Center, Tech Library and Code
> Place, see the latest IDUG conference information and much more. If you
> have not yet signed up for Basic Membership in IDUG, available at no cost,
> click on Member Services at http://www.idug.org/lsms
>

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

William Gannon

Re: Load with enforce constraints DB2 Z/OS V7
(in response to Tejas Jadhav)
Amit,



To verify Constraints DB2 has to do lookups on your defined foreign keys
- Have you verified that your Foreign Keys are backed by matching
indexes on the reference tables ?





William B. Gannon
- IBM Certified Solutions Expert
- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
Office (561) 434-8318

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Amit Agarwal
Sent: Tuesday, March 11, 2008 2:28 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Load with enforce constraints DB2 Z/OS V7



Here's the JCL. After I changed the load card to Enforce no and removed
the runstats, everything worked fine.



Thanks



//STEP02 EXEC PGM=DSNUTILB,

// COND=(4,LT),

// REGION=0M,

// PARM='DB2C,ITS0037D'

//SYSREC DD DISP=OLD,DSN=NPCAT.CLMSRCH.LOADFILE

//*

//SYSDISC DD DSN=NPCAT.ASAQ.CLMSRCH.DISCARD,

// DISP=(MOD,DELETE,CATLG),

// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)

//SYSERR DD DSN=NPCAT.ASAQ.CLMSRCH.SYSERR,

// DISP=(MOD,DELETE,CATLG),

// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)

//SYSMAP DD DSN=NPCAT.ASAQ.CLMSRCH.SYSMAP,

// DISP=(MOD,DELETE,CATLG),

// UNIT=SYSALLDA,SPACE=(CYL,(50,150),RLSE)

//SYSPRINT DD SYSOUT=*

//UTPRINT DD SYSOUT=*

//SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTOUT DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK06 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK07 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK08 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SORTWK09 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)

//SYSIN DD *

LOAD DATA REPLACE

LOG NO

ENFORCE CONSTRAINTS



RUNSTATS TABLE(ALL) INDEX(ALL)

NOCOPYPEND

DISCARDS 1000

INTO TABLE ITSASAQ.CLM_SEARCH



On 3/11/08, Bala <[login to unmask email]> wrote:

Amit,



Can you post the JCL that you used for LOADing ?







On Mon, Mar 10, 2008 at 2:29 PM, Amit Agarwal <[login to unmask email]>
wrote:





DB2 V7



Listers,



I have a table with 25 partitions and 6 indexes.

Tried to load the table(10,000 rows only) with enforce
constraints and statistics table(all) index(all). The job just hung on
the build phase while on the 3rd partition.



We tried a couple of things, like dropping all except
partitioned indexes, increasing or decreasing sortwork, nothing seemed
to work. Then used ENFORCE NO and removed the statistics, the job run
fine.



I had never used ENFORCE CONSTRAINTS before, usually I have
preferred to run the check utility separately.



Just wondering if anyone has experienced problems with the
ENFORCE CONSTRAINTS option before.



Thanks

Amit Agarwal






________________________________


IDUG 2008 - North America * May 18-22, 2008 * Dallas, TX, USA
< http://idug.org/lsna >

The IDUG DB2-L Listserv is only part of your membership in IDUG.
The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed
up for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >



________________________________

IDUG 2008 - India * 21-23 August 2008 * Bangalore, India
< http://idug.org/lsin >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed
up for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >



________________________________

IDUG 2008 - India * 21-23 August 2008 * Bangalore, India
< http://idug.org/lsin >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed
up for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >



-----------------------------------------
Under Florida law, e-mail addresses are public records. If you do
not want your e-mail address released in response to a public
records request, do not send electronic mail to this entity.
Instead, contact this office by phone or in writing.

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Patrick Steurs

Re: Load with enforce constraints DB2 Z/OS V7
(in response to William Gannon)

Amit,

Is your table compressed or using a long varchar ?
This produces sometimes a problem in the sort-routine.

greetings,

Patrick Steurs
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Steve
Sent: dinsdag 11 maart 2008 9:24
To: [login to unmask email]
Subject: Re: [DB2-L] Load with enforce constraints DB2 Z/OS V7

Amit,

The job probably only appears to go inactive upon entering SORT,
especially if
there are a lot of rows and//or foreign keys involved,

HTH
regards,
Steve

On Tue, 11 Mar 2008 11:58:16 +0530, Amit Agarwal
<[login to unmask email]> wrote:

> Here's the JCL. After I changed the load card to Enforce no and
removed the
>runstats, everything worked fine.
>
>Thanks
>
>//STEP02 EXEC PGM=DSNUTILB,
>
>// COND=(4,LT),
>
>// REGION=0M,
>
>// PARM='DB2C,ITS0037D'
>
>//SYSREC DD DISP=OLD,DSN=NPCAT.CLMSRCH.LOADFILE
>
>//*
>
>//SYSDISC DD DSN=NPCAT.ASAQ.CLMSRCH.DISCARD,
>
>// DISP=(MOD,DELETE,CATLG),
>
>// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
>
>//SYSERR DD DSN=NPCAT.ASAQ.CLMSRCH.SYSERR,
>
>// DISP=(MOD,DELETE,CATLG),
>
>// UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
>
>//SYSMAP DD DSN=NPCAT.ASAQ.CLMSRCH.SYSMAP,
>
>// DISP=(MOD,DELETE,CATLG),
>
>// UNIT=SYSALLDA,SPACE=(CYL,(50,150),RLSE)
>
>//SYSPRINT DD SYSOUT=*
>
>//UTPRINT DD SYSOUT=*
>
>//SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTOUT DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK06 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK07 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK08 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SORTWK09 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
>//SYSIN DD *
>
>LOAD DATA REPLACE
>
>LOG NO
>ENFORCE CONSTRAINTS
>
>RUNSTATS TABLE(ALL) INDEX(ALL)
>
>NOCOPYPEND
>
>DISCARDS 1000
>
>INTO TABLE ITSASAQ.CLM_SEARCH
>
>
>On 3/11/08, Bala <[login to unmask email]> wrote:
>>
>> Amit,
>>
>> Can you post the JCL that you used for LOADing ?
>>
>>
>>
>>
>> On Mon, Mar 10, 2008 at 2:29 PM, Amit Agarwal <[login to unmask email]>
>> wrote:
>>
>> > DB2 V7
>> >
>> > Listers,
>> >
>> > I have a table with 25 partitions and 6 indexes.
>> > Tried to load the table(10,000 rows only) with enforce constraints
and
>> > statistics table(all) index(all). The job just hung on the build
phase while
>> > on the 3rd partition.
>> >
>> > We tried a couple of things, like dropping all except partitioned
>> > indexes, increasing or decreasing sortwork, nothing seemed to work.
Then
>> > used ENFORCE NO and removed the statistics, the job run fine.
>> >
>> > I had never used ENFORCE CONSTRAINTS before, usually I have
preferred to
>> > run the check utility separately.
>> >
>> > Just wondering if anyone has experienced problems with the ENFORCE
>> > CONSTRAINTS option before.
>> >
>> > Thanks
>> > Amit Agarwal
>> >
>> > ------------------------------
>> >
>> > *IDUG 2008 - North America * May 18-22, 2008 * Dallas, TX,
USA* < http://idug.org/lsna >
>> >
>> > The IDUG DB2-L Listserv is only part of your membership in IDUG.
The
>> > DB2-L list archives, FAQ, and delivery preferences are at
*IDUG.ORG < http://www.idug.org/lsidug >
>> > * under the Listserv tab. While at the site, you can also access
the
>> > IDUG Online Learning Center, Tech Library and Code Place, see the
latest
>> > IDUG *conference information < http://www.idug.org/lsconf > *, and
much
>> > more. *If you have not yet signed up for Basic Membership in IDUG,
>> > available at no cost, click on Member
Services < http://www.idug.org/lsms >
>> > *
>> >
>>
>>
>> ------------------------------
>>
>> *IDUG 2008 - India * 21-23 August 2008 * Bangalore,
India* < http://idug.org/lsin >
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L
>> list archives, FAQ, and delivery preferences are at
*IDUG.ORG < http://www.idug.org/lsidug >
>> * under the Listserv tab. While at the site, you can also access the
IDUG
>> Online Learning Center, Tech Library and Code Place, see the latest
IDUG
*conference
>> information < http://www.idug.org/lsconf > *, and much more. *If you
have
not
>> yet signed up for Basic Membership in IDUG, available at no cost,
click on
Member
>> Services < http://www.idug.org/lsms > *
>>
>
>_________________________________________________________________
_____
>
>* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
>_________________________________________________________________
_____
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG

Online Learning Center, Tech Library and Code Place, see the latest IDUG

conference information and much more. If you have not yet signed up for

Basic Membership in IDUG, available at no cost, click on Member Services
at
http://www.idug.org/lsms
>

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

-----------------------------------------
Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message should not be
construed as binding on the part of the National Bank of Belgium
(NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content
of this message, or part thereof, is private by nature or does not
fall within the professional scope of its author."

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Bala

Re: Load with enforce constraints DB2 Z/OS V7
(in response to Patrick Steurs)
I guess the problem is because of huge sort work files defined by SORTWKnn
files. Try removing all of them and let DB2 decide itself or try coding a
SORTDEVT SYSDA SORTNUM n where n is 8 or 16 or 32... Also, try to include
SORTKEYS keyword to enable parallel tasks while reloading.

On Tue, Mar 11, 2008 at 2:28 AM, Amit Agarwal <[login to unmask email]> wrote:

> Here's the JCL. After I changed the load card to Enforce no and removed
> the runstats, everything worked fine.
>
> Thanks
>
> //STEP02 EXEC PGM=DSNUTILB,
>
> // COND=(4,LT),
>
> // REGION=0M,
>
> // PARM='DB2C,ITS0037D'
>
> //SYSREC DD DISP=OLD,DSN=NPCAT.CLMSRCH.LOADFILE
>
> //*
>
> //SYSDISC DD DSN=NPCAT.ASAQ.CLMSRCH.DISCARD,
>
> // DISP=(MOD,DELETE,CATLG),
>
> // UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
>
> //SYSERR DD DSN=NPCAT.ASAQ.CLMSRCH.SYSERR,
>
> // DISP=(MOD,DELETE,CATLG),
>
> // UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
>
> //SYSMAP DD DSN=NPCAT.ASAQ.CLMSRCH.SYSMAP,
>
> // DISP=(MOD,DELETE,CATLG),
>
> // UNIT=SYSALLDA,SPACE=(CYL,(50,150),RLSE)
>
> //SYSPRINT DD SYSOUT=*
>
> //UTPRINT DD SYSOUT=*
>
> //SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTOUT DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK06 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK07 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK08 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK09 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SYSIN DD *
>
> LOAD DATA REPLACE
>
> LOG NO
> ENFORCE CONSTRAINTS
>
> RUNSTATS TABLE(ALL) INDEX(ALL)
>
> NOCOPYPEND
>
> DISCARDS 1000
>
> INTO TABLE ITSASAQ.CLM_SEARCH
>
>
> On 3/11/08, Bala <[login to unmask email]> wrote:
>
> > Amit,
> >
> > Can you post the JCL that you used for LOADing ?
> >
> >
> >
> >
> > On Mon, Mar 10, 2008 at 2:29 PM, Amit Agarwal <[login to unmask email]>
> > wrote:
> >
> > > DB2 V7
> > >
> > > Listers,
> > >
> > > I have a table with 25 partitions and 6 indexes.
> > > Tried to load the table(10,000 rows only) with enforce constraints and
> > > statistics table(all) index(all). The job just hung on the build phase while
> > > on the 3rd partition.
> > >
> > > We tried a couple of things, like dropping all except partitioned
> > > indexes, increasing or decreasing sortwork, nothing seemed to work. Then
> > > used ENFORCE NO and removed the statistics, the job run fine.
> > >
> > > I had never used ENFORCE CONSTRAINTS before, usually I have preferred
> > > to run the check utility separately.
> > >
> > > Just wondering if anyone has experienced problems with the ENFORCE
> > > CONSTRAINTS option before.
> > >
> > > Thanks
> > > Amit Agarwal
> > >
> > > ------------------------------
> > >
> > > *IDUG 2008 - North America * May 18-22, 2008 * Dallas, TX, USA* < http://idug.org/lsna >
> > >
> > > The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> > > DB2-L list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> > > * under the Listserv tab. While at the site, you can also access the
> > > IDUG Online Learning Center, Tech Library and Code Place, see the latest
> > > IDUG *conference information < http://www.idug.org/lsconf > *, and much
> > > more. *If you have not yet signed up for Basic Membership in IDUG,
> > > available at no cost, click on Member Services < http://www.idug.org/lsms >
> > > *
> > >
> >
> >
> > ------------------------------
> >
> > *IDUG 2008 - India * 21-23 August 2008 * Bangalore, India* < http://idug.org/lsin >
> >
> > The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> > DB2-L list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> > * under the Listserv tab. While at the site, you can also access the
> > IDUG Online Learning Center, Tech Library and Code Place, see the latest
> > IDUG *conference information < http://www.idug.org/lsconf > *, and much
> > more. *If you have not yet signed up for Basic Membership in IDUG,
> > available at no cost, click on Member Services < http://www.idug.org/lsms >
> > *
> >
>
>
> ------------------------------
>
> *IDUG 2008 - India * 21-23 August 2008 * Bangalore, India* < http://idug.org/lsin >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. *If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on Member
> Services < http://www.idug.org/lsms > *
>

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Amit Agarwal

Re: Load with enforce constraints DB2 Z/OS V7
(in response to Bala)
Bill,

We received this code from a vendor, and the one foreign key(this is a
parent) does not have a matching index. However, there are 6 other indexes
on this table, including the partitioning key.

Additionally, this table is compressed.

We tried to reduce the sortworks, same problem.

However, they have changed the code to removed runstats and enforce
constraints for the table and everything fine.


Thank you everyone, for all your comments.

Amit Agarwal



On 3/11/08, Bill Gannon <[login to unmask email]> wrote:
>
> Amit,
>
>
>
> To verify Constraints DB2 has to do lookups on your defined foreign keys -
> Have you verified that your Foreign Keys are backed by matching indexes on
> the reference tables ?
>
>
>
>
>
> * William B. Gannon ** *
> - IBM Certified Solutions Expert
> - IBM Certified Database Administrator
> DB2 Universal Database V8.1 for zOS
> Office (561) 434-8318
> ------------------------------
>
> *From:* DB2 Data Base Discussion List [mailto:[login to unmask email] *On
> Behalf Of *Amit Agarwal
> *Sent:* Tuesday, March 11, 2008 2:28 AM
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] Load with enforce constraints DB2 Z/OS V7
>
>
>
> Here's the JCL. After I changed the load card to Enforce no and removed
> the runstats, everything worked fine.
>
>
>
> Thanks
>
>
>
> //STEP02 EXEC PGM=DSNUTILB,
>
> // COND=(4,LT),
>
> // REGION=0M,
>
> // PARM='DB2C,ITS0037D'
>
> //SYSREC DD DISP=OLD,DSN=NPCAT.CLMSRCH.LOADFILE
>
> //*
>
> //SYSDISC DD DSN=NPCAT.ASAQ.CLMSRCH.DISCARD,
>
> // DISP=(MOD,DELETE,CATLG),
>
> // UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
>
> //SYSERR DD DSN=NPCAT.ASAQ.CLMSRCH.SYSERR,
>
> // DISP=(MOD,DELETE,CATLG),
>
> // UNIT=SYSALLDA,SPACE=(CYL,(1,1),RLSE)
>
> //SYSMAP DD DSN=NPCAT.ASAQ.CLMSRCH.SYSMAP,
>
> // DISP=(MOD,DELETE,CATLG),
>
> // UNIT=SYSALLDA,SPACE=(CYL,(50,150),RLSE)
>
> //SYSPRINT DD SYSOUT=*
>
> //UTPRINT DD SYSOUT=*
>
> //SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTOUT DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK06 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK07 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK08 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SORTWK09 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
>
> //SYSIN DD *
>
> LOAD DATA REPLACE
>
> LOG NO
>
> ENFORCE CONSTRAINTS
>
>
>
> RUNSTATS TABLE(ALL) INDEX(ALL)
>
> NOCOPYPEND
>
> DISCARDS 1000
>
> INTO TABLE ITSASAQ.CLM_SEARCH
>
>
>
> On 3/11/08, *Bala* <[login to unmask email]> wrote:
>
> Amit,
>
>
>
> Can you post the JCL that you used for LOADing ?
>
>
>
>
>
>
>
> On Mon, Mar 10, 2008 at 2:29 PM, Amit Agarwal <[login to unmask email]>
> wrote:
>
> DB2 V7
>
>
>
> Listers,
>
>
>
> I have a table with 25 partitions and 6 indexes.
>
> Tried to load the table(10,000 rows only) with enforce constraints and
> statistics table(all) index(all). The job just hung on the build phase while
> on the 3rd partition.
>
>
>
> We tried a couple of things, like dropping all except partitioned indexes,
> increasing or decreasing sortwork, nothing seemed to work. Then used ENFORCE
> NO and removed the statistics, the job run fine.
>
>
>
> I had never used ENFORCE CONSTRAINTS before, usually I have preferred to
> run the check utility separately.
>
>
>
> Just wondering if anyone has experienced problems with the ENFORCE
> CONSTRAINTS option before.
>
>
>
> Thanks
>
> Amit Agarwal
>
>
>
> ------------------------------
>
> *IDUG 2008 - North America * May 18-22, 2008 * Dallas, TX, USA* < http://idug.org/lsna >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. *If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on Member
> Services < http://www.idug.org/lsms > *
>
>
> ------------------------------
>
> *IDUG 2008 - India * 21-23 August 2008 * Bangalore, India* < http://idug.org/lsin >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. *If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on Member
> Services < http://www.idug.org/lsms > *
>
>
> ------------------------------
>
> *IDUG 2008 - India * 21-23 August 2008 * Bangalore, India* < http://idug.org/lsin >
> * *
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. *If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on Member
> Services < http://www.idug.org/lsms > *
>
> ------------------------------
>
> *Under Florida law, e-mail addresses are public records. If you do not
> want your e-mail address released in response to a public records request,
> do not send electronic mail to this entity. Instead, contact this office by
> phone or in writing. *
>
> ------------------------------
>
> *IDUG 2008 - India * 21-23 August 2008 * Bangalore, India* < http://idug.org/lsin >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. *If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on Member
> Services < http://www.idug.org/lsms > *
>

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms