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.


    Jan 21
    2000

    DB2 High Performance Unload - Select Option

    David Stritzinger
    There has been some discussion on the list the last few months about
    IBM's DB2 High Performance Unload, and just this week we received the tape
    and installed it. Our environment is DB2 5.1, OS/390 2.6, and SAP R/3. We
    have started to do some initial testing and we are currently hitting some
    issues I was hoping someone else may have been able to figure out.

    Our first test was to unload 10,000,000 sequential rows from a 137M+
    row table. We ran this test and the results were very impressive. This job
    took about 21 minutes. The unload did not run under the DB2 subsystem. It
    went against the VSAM files directly. The output from this job is below:

    UNLOAD TABLESPACE ZZNBU1A.ZZNBU1A

    DB2 YES

    QUIESCE YES

    SELECT * FROM SAPR3.ZZNBU1A

    OUTMAXROWS 10000000

    OUTDDN(UNLDDN1)

    DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DB2UNLOAD

    DSNU050I DSNUGUTC - QUIESCE TABLESPACE ZZNBU1A.ZZNBU1A

    DSNU477I -DBUB DSNUQUIA - QUIESCE SUCCESSFUL FOR TABLESPACE ZZNBU1A.ZZNBU1A

    DSNU474I -DBUB DSNUQUIA - QUIESCE AT RBA 05DE832E4ADC AND AT LRSN
    05DE832E4ADC
    DSNU475I DSNUQUIB - QUIESCE UTILITY COMPLETE, ELAPSED TIME= 00:00:00

    DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

    TABLESPACE UNLOAD STATISTICS UT2911 INFOTEL LOAD /
    REORG
    * TABLE * PART NO. * ROWS READ * ROWS KEPT * IX SCAN *

    * * * * * *

    * ZZNBU1A * 0 * 10000689 * 10000689 *
    0 % *
    * INVALID ROWS .................* 0 *
    0 * *
    * TABLESPACE TOTAL .............* 10000689 * 10000689 * 0 % *
    NUMBER OF PAGES READ ... 817818
    NUMBER OF PAGES IN ERROR 0

    The Unload utility allows for a SELECT statement with a WHERE, an
    ORDER BY, and a FORMAT clause. The FORMAT allows for the output to be in
    either DSNTIAUL, DELIMITED, VARIABLE, or USER format. Our next test was to
    add a WHERE and an ORDER BY clause. The output is below:

    UNLOAD TABLESPACE ZZNBU1A.ZZNBU1A

    DB2 YES LOCK NO

    QUIESCE YES

    SELECT * FROM SAPR3.ZZNBU1A

    WHERE RCLNT = '010' AND

    GL_SIRID BETWEEN '000000000000000001' AND '000000000032048949'

    ORDER BY RCLNT,GL_SIRID WITH UR;

    FORMAT DSNTIAUL

    OUTDDN(UNLDDN1)

    LOADDDN SYSPUNCH

    INZSE224 WARNING : 'SELECT * FROM SAPR3.ZZNBU1A WHERE RCLNT = '010'
    AND
    DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DB2UNLOAD

    DSNU050I DSNUGUTC - QUIESCE TABLESPACE ZZNBU1A.ZZNBU1A

    DSNU477I -DBUB DSNUQUIA - QUIESCE SUCCESSFUL FOR TABLESPACE ZZNBU1A.ZZNBU1A

    DSNU474I -DBUB DSNUQUIA - QUIESCE AT RBA 05DE83311802 AND AT LRSN
    05DE83311802
    DSNU475I DSNUQUIB - QUIESCE UTILITY COMPLETE, ELAPSED TIME= 00:00:00

    DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

    EXTR0000 :SELECT * FROM SAPR3.ZZNBU1A WHERE RCLNT = '010' AND
    GL_SIR
    EXTR0000 : BETWEEN '000000000000000001' AND '000000000032048949' ORDER
    BY
    EXTR0000 :LNT,GL_SIRID WITH UR; FORMAT DSNTIAUL

    INZR3000 :NUMBER OF ROWS EXTRACTED COPIED ON UNLDDN1 : 10000000


    This test ran, but took almost 2 hours due to the fact that the
    unload used DB2 to do the extract. (This is the same amount of time as it
    took to run the DSNTIAUL utility).

    There is an option in the High Performance Utility of DB2 Yes / No.
    The above example ran with this set to DB2 Yes, which tells the utility that
    it can use DB2 to do the extract if it can not handle the SELECT statement.
    When we run the same input with DB2 No, we encountered a variety of errors
    which stopped the utility from running. We tried only one comparison in the
    WHERE clause, eliminated the where clause all together and kept the ORDER
    BY, kept the WHERE and eliminated the ORDER BY..... It seems like nothing we
    tried allowed the utility to use any kind of WHERE or ORDER BY and still not
    use DB2 for the extract.

    Below are some of the error output messages:

    UNLOAD TABLESPACE ZZNBU1A.ZZNBU1A
    DB2 NO LOCK NO
    QUIESCE YES
    SELECT * FROM SAPR3.ZZNBU1A
    WHERE RCLNT = '010' AND
    GL_SIRID BETWEEN '000000000000000001' AND '000000000032048949'
    ORDER BY RCLNT,GL_SIRID;
    FORMAT DSNTIAUL
    OUTDDN(UNLDDN1)
    LOADDDN SYSPUNCH
    INZDL104 ERROR : INCORRECT TYPE
    INZDL105 ERROR : ERRONEOUS SEARCH CONDITION FOR SAPR3.ZZNBU1A TABLE


    UNLOAD TABLESPACE ZZNBU1A.ZZNBU1A
    DB2 NO LOCK NO
    QUIESCE YES
    SELECT * FROM SAPR3.ZZNBU1A
    ORDER BY 1,2 WITH UR;
    OUTDDN(UNLDDN1)
    FORMAT DSNTIAUL
    LOADDDN SYSPUNCH
    INZSE159 ERROR : WITHIN LIST OF OPERANDS - KEYWORD ORDER BY

    The Unload User Guide states that if the DB2 option is No then,
    "SELECT statements not supported by DB2 Unload will not be processed by
    DB2". It also has "DB2 Unload supports more complex WHERE clauses if DB2 YES
    is coded in the unload block. In this case, the extraction is done by DB2".

    I guess my questions are:

    1) Has anyone else tied the Unload with a SELECT and a WHERE
    and ORDER BY clause and got it to work with DB2 set to NO?

    2) How simple of a SELECT statement must we have so that the
    Unload load utility will not have to use DB2 to do the extract?

    I am sorry for the length is this message, but I wanted to show some
    of the test we have attempted.


    TIA,
    Dave
    Stritzinger

    [login to unmask email]



    Burgess Evans
    [Auditor of State of Ohio]
    Hi All,

    I was just reading about IBM's DB2 High Performance Unload and I was wondering if anyone is using it. We have been looking for an easy way for our programming staff to populate test tables with current production data or old production data from a specific image copy. Up until now I have done this for them using DSN1COPY.

    If you are using the product, do you know whether or not it will let you unload data from any image copy taken on one subsystem and then use the resulting data to load into an identical table in another subsystem thus eliminating the need to do DSN1COPY?

    Are there any "Gotchas" to be aware of when using this product?

    Thank you for your help,
    Burgess Evans, DBA



    [login to unmask email]
    Burgess,

    We have the product in house, and I have played with it a little. You should
    have no problem unloading from an image copy on one subsystem and loading into
    another. It just creates a sequential unload file, just as if you were running
    DSNTIAUL against the live tablespace.

    The only real restriction that I have found in using the product (compared to
    DSNTIAUL) is that you can only unload from one tablespace at a time . . . you
    cannot unload a join of more than one table, even if both tables reside in the
    same tablespace. But for straight forward unload processing, it works very
    nicely and efficiently.

    Bill Gallagher, DBA
    Phoenix Home Life
    Enfield, CT





    Burgess Evans <[login to unmask email]> on 02/15/2000 04:27:44 PM

    Please respond to DB2 Data Base Discussion List <[login to unmask email]>


    To: [login to unmask email]
    cc: (bcc: BILL GALLAGHER/Phoenix Home Life Mutual Insurance)
    bcc: BILL GALLAGHER/Phoenix Home Life Mutual Insurance
    Subject: DB2 High Performance Unload



    Hi All,

    I was just reading about IBM's DB2 High Performance Unload and I was wondering
    if anyone is using it. We have been looking for an easy way for our programming
    staff to populate test tables with current production data or old production
    data from a specific image copy. Up until now I have done this for them using
    DSN1COPY.

    If you are using the product, do you know whether or not it will let you unload
    data from any image copy taken on one subsystem and then use the resulting data
    to load into an identical table in another subsystem thus eliminating the need
    to do DSN1COPY?

    Are there any "Gotchas" to be aware of when using this product?

    Thank you for your help,
    Burgess Evans, DBA








    INFO_SYSTEMS
    I haven't used IBM's Unlaod but I do use Infotel's Unload. You can unload
    from an image copy and reload this data into another system.

    Donald Jackson
    310-605-3458
    -----Original Message-----
    From: Burgess Evans <[login to unmask email]>
    Newsgroups: bit.listserv.db2-l
    To: [login to unmask email] <[login to unmask email]>
    Date: Tuesday, February 15, 2000 1:29 PM
    Subject: DB2 High Performance Unload


    Hi All,

    I was just reading about IBM's DB2 High Performance Unload and I was
    wondering if anyone is using it. We have been looking for an easy way for
    our programming staff to populate test tables with current production data
    or old production data from a specific image copy. Up until now I have done
    this for them using DSN1COPY.

    If you are using the product, do you know whether or not it will let you
    unload data from any image copy taken on one subsystem and then use the
    resulting data to load into an identical table in another subsystem thus
    eliminating the need to do DSN1COPY?

    Are there any "Gotchas" to be aware of when using this product?

    Thank you for your help,
    Burgess Evans, DBA

    ================






    Andy Hunt
    David Stritzinger (another contributor to this list) did find slow response for
    certain 'unload' SQL. He raised the problem with IBM and determined the
    following,

    We have two open issues with IBM on this product:
    >
    > 1) To get the "high performance" out of the unload, the Where and
    > Order By clauses must reference FIXED length columns. The unload can NOT
    > deal with VARCHAR without using DB2 to do the extract. IBM can back and
    > confirmed this "feature" and have created APAR PQ35117 to resolve this
    > issue.
    >
    > 2) The unload can only work with EBCDIC tables, it can not handle
    > ASCII defined tables AT ALL. We were able to run an unload with no Select
    > on an ASCII defined table and it did create an output file, but IBM stated
    > that the comparison with ASCII is more complex and the output files can be
    > wrong.
    >

    The target date for PQ35117 is 7th April 2000.
    Kind Regards,
    Andy Hunt - Scottish And Southern Energy.




    Burgess Evans <[login to unmask email]> on 15/02/2000 21:27:44

    Please respond to DB2 Data Base Discussion List <[login to unmask email]>

    To: [login to unmask email]
    cc: (bcc: Andy Hunt/HAV/SSE)
    Subject: DB2 High Performance Unload



    Hi All,

    I was just reading about IBM's DB2 High Performance Unload and I was wondering
    if anyone is using it. We have been looking for an easy way for our programming
    staff to populate test tables with current production data or old production
    data from a specific image copy. Up until now I have done this for them using
    DSN1COPY.

    If you are using the product, do you know whether or not it will let you unload
    data from any image copy taken on one subsystem and then use the resulting data
    to load into an identical table in another subsystem thus eliminating the need
    to do DSN1COPY?

    Are there any "Gotchas" to be aware of when using this product?

    Thank you for your help,
    Burgess Evans, DBA











    **********************************************************************
    The information in this E-Mail is confidential and may be legally
    privileged. It may not represent the views of Scottish and Southern
    Energy plc.
    It is intended solely for the addressees. Access to this E-Mail by
    anyone else is unauthorised. If you are not the intended recipient,
    any disclosure, copying, distribution or any action taken or omitted
    to be taken in reliance on it, is prohibited and may be unlawful.
    Any unauthorised recipient should advise the sender immediately of
    the error in transmission.

    Scottish Hydro-Electric and Southern Electric are trading names of
    Scottish and Southern Energy Group
    **********************************************************************



    Frank D Marchese
    Hi folks - I am intrigued reading about IBM's hiperf unload. We use current
    DSNTIAUL heavily and would love something that could be significantly
    quicker. We are DB2 V5 OS/390.
    Is this an add'l cost product ? Is it GA right now ? Anyone out there know ?
    Is it DB2 release dependent ? TIA

    -----Original Message-----
    From: Andy Hunt [mailto:[login to unmask email]
    Sent: Wednesday, February 16, 2000 3:47 AM
    To: [login to unmask email]
    Subject: Re: DB2 High Performance Unload


    David Stritzinger (another contributor to this list) did find slow response
    for
    certain 'unload' SQL. He raised the problem with IBM and determined the
    following,

    We have two open issues with IBM on this product:
    >
    > 1) To get the "high performance" out of the unload, the Where and
    > Order By clauses must reference FIXED length columns. The unload can NOT
    > deal with VARCHAR without using DB2 to do the extract. IBM can back and
    > confirmed this "feature" and have created APAR PQ35117 to resolve this
    > issue.
    >
    > 2) The unload can only work with EBCDIC tables, it can not handle
    > ASCII defined tables AT ALL. We were able to run an unload with no Select
    > on an ASCII defined table and it did create an output file, but IBM stated
    > that the comparison with ASCII is more complex and the output files can be
    > wrong.
    >

    The target date for PQ35117 is 7th April 2000.
    Kind Regards,
    Andy Hunt - Scottish And Southern Energy.




    Burgess Evans <[login to unmask email]> on 15/02/2000 21:27:44

    Please respond to DB2 Data Base Discussion List <[login to unmask email]>

    To: [login to unmask email]
    cc: (bcc: Andy Hunt/HAV/SSE)
    Subject: DB2 High Performance Unload



    Hi All,

    I was just reading about IBM's DB2 High Performance Unload and I was
    wondering
    if anyone is using it. We have been looking for an easy way for our
    programming
    staff to populate test tables with current production data or old production
    data from a specific image copy. Up until now I have done this for them
    using
    DSN1COPY.

    If you are using the product, do you know whether or not it will let you
    unload
    data from any image copy taken on one subsystem and then use the resulting
    data
    to load into an identical table in another subsystem thus eliminating the
    need
    to do DSN1COPY?

    Are there any "Gotchas" to be aware of when using this product?

    Thank you for your help,
    Burgess Evans, DBA











    **********************************************************************
    The information in this E-Mail is confidential and may be legally
    privileged. It may not represent the views of Scottish and Southern
    Energy plc.
    It is intended solely for the addressees. Access to this E-Mail by
    anyone else is unauthorised. If you are not the intended recipient,
    any disclosure, copying, distribution or any action taken or omitted
    to be taken in reliance on it, is prohibited and may be unlawful.
    Any unauthorised recipient should advise the sender immediately of
    the error in transmission.

    Scottish Hydro-Electric and Southern Electric are trading names of
    Scottish and Southern Energy Group
    **********************************************************************








    Andy Hunt
    I believe it is available (at additional cost) - program number 5697-F44. V1.1.0
    supports DB2 V3 onwards.
    Kind Regards,
    Andy Hunt - Scottish And Southern Energy




    "Marchese, Frank D, CSCIO" <[login to unmask email]> on 16/02/2000 13:36:33

    Please respond to DB2 Data Base Discussion List <[login to unmask email]>

    To: [login to unmask email]
    cc: (bcc: Andy Hunt/HAV/SSE)
    Subject: Re: DB2 High Performance Unload



    Hi folks - I am intrigued reading about IBM's hiperf unload. We use current
    DSNTIAUL heavily and would love something that could be significantly
    quicker. We are DB2 V5 OS/390.
    Is this an add'l cost product ? Is it GA right now ? Anyone out there know ?
    Is it DB2 release dependent ? TIA

    -----Original Message-----
    From: Andy Hunt [mailto:[login to unmask email]
    Sent: Wednesday, February 16, 2000 3:47 AM
    To: [login to unmask email]
    Subject: Re: DB2 High Performance Unload


    David Stritzinger (another contributor to this list) did find slow response
    for
    certain 'unload' SQL. He raised the problem with IBM and determined the
    following,

    We have two open issues with IBM on this product:
    >
    > 1) To get the "high performance" out of the unload, the Where and
    > Order By clauses must reference FIXED length columns. The unload can NOT
    > deal with VARCHAR without using DB2 to do the extract. IBM can back and
    > confirmed this "feature" and have created APAR PQ35117 to resolve this
    > issue.
    >
    > 2) The unload can only work with EBCDIC tables, it can not handle
    > ASCII defined tables AT ALL. We were able to run an unload with no Select
    > on an ASCII defined table and it did create an output file, but IBM stated
    > that the comparison with ASCII is more complex and the output files can be
    > wrong.
    >

    The target date for PQ35117 is 7th April 2000.
    Kind Regards,
    Andy Hunt - Scottish And Southern Energy.




    Burgess Evans <[login to unmask email]> on 15/02/2000 21:27:44

    Please respond to DB2 Data Base Discussion List <[login to unmask email]>

    To: [login to unmask email]
    cc: (bcc: Andy Hunt/HAV/SSE)
    Subject: DB2 High Performance Unload



    Hi All,

    I was just reading about IBM's DB2 High Performance Unload and I was
    wondering
    if anyone is using it. We have been looking for an easy way for our
    programming
    staff to populate test tables with current production data or old production
    data from a specific image copy. Up until now I have done this for them
    using
    DSN1COPY.

    If you are using the product, do you know whether or not it will let you
    unload
    data from any image copy taken on one subsystem and then use the resulting
    data
    to load into an identical table in another subsystem thus eliminating the
    need
    to do DSN1COPY?

    Are there any "Gotchas" to be aware of when using this product?

    Thank you for your help,
    Burgess Evans, DBA











    **********************************************************************
    The information in this E-Mail is confidential and may be legally
    privileged. It may not represent the views of Scottish and Southern
    Energy plc.
    It is intended solely for the addressees. Access to this E-Mail by
    anyone else is unauthorised. If you are not the intended recipient,
    any disclosure, copying, distribution or any action taken or omitted
    to be taken in reliance on it, is prohibited and may be unlawful.
    Any unauthorised recipient should advise the sender immediately of
    the error in transmission.

    Scottish Hydro-Electric and Southern Electric are trading names of
    Scottish and Southern Energy Group
    **********************************************************************
















    **********************************************************************
    The information in this E-Mail is confidential and may be legally
    privileged. It may not represent the views of Scottish and Southern
    Energy plc.
    It is intended solely for the addressees. Access to this E-Mail by
    anyone else is unauthorised. If you are not the intended recipient,
    any disclosure, copying, distribution or any action taken or omitted
    to be taken in reliance on it, is prohibited and may be unlawful.
    Any unauthorised recipient should advise the sender immediately of
    the error in transmission.

    Scottish Hydro-Electric and Southern Electric are trading names of
    Scottish and Southern Energy Group
    **********************************************************************




    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