DB2 High Performance Unload - Select Option

David Stritzinger

DB2 High Performance Unload - Select Option
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

DB2 High Performance Unload
(in response to David Stritzinger)
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]

Re: DB2 High Performance Unload
(in response to Burgess Evans)
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

Re: DB2 High Performance Unload
(in response to BILL_GALLAGHER@PHL.COM)
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

Re: DB2 High Performance Unload
(in response to INFO_SYSTEMS)
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

Re: DB2 High Performance Unload
(in response to Andy Hunt)
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

Re: DB2 High Performance Unload
(in response to Frank D Marchese)
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
**********************************************************************