Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Mar 10
    2008

    Load with enforce constraints DB2 Z/OS V7

    Amit Agarwal
    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
    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
    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
    [eds]
    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
    [IBM India Pvt. Ltd.]
    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
    [PBCSD]
    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
    [National bank of Belgium NV.]

    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
    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
    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

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact