Here's how this got started: Jorn, an IBM System z Information Management Technical Specialist, had delivered presentations on the DB2 Analytics Accelerator for z/OS to a number of organizations. In so doing, he would describe how the copying of DB2 tables into the Analytics Accelerator was performance-optimized in a couple of ways: 1) data is unloaded from the source DB2 tables in internal format, saving the CPU cycles that would otherwise be consumed in converting the data to external format; and 2) the unloaded DB2 data is transferred to the Analytics Accelerator by way of z/OS UNIX System Services (USS) pipes. [Pipes, also known as FIFO (first-in, first-out) files, are commonly used in UNIX environments. One process can read from a pipe as another process is writing data to the pipe, enabling a data load operation to run simultaneously with the unload operation that provides its input.] Following one of these presentations, someone asked Jorn a question: could data be moved from one DB2 for z/OS table to another in a similar fashion, with no data conversion and without the use of an intermediary data set that would force serialization of the load and unload tasks?
In considering this question, Jorn thought first about the cross-loader function of the DB2 LOAD utility. That could be used to get data from one table to another without the need for an "in-between" data set that would first be the output an UNLOAD and then the input to a LOAD. The cross-loader, however, can't be used with the FORMAT INTERNAL option, so while it would address the "no intermediate data set" stipulation, it would leave the "no data conversion" requirement unsatisfied.
It then occurred to Jorn that one could utilize, for a table-to-table data move in a DB2 for z/OS context, the same technique employed for copies of data into a DB2 Analytics Accelerator: combine the FORMAT INTERNAL option with a transference of data through a USS pipe. Jorn went looking for examples of this approach, and when he didn't find any that precisely fit the bill, he created his own UNLOAD and LOAD jobs to show how FORMAT INTERNAL and USS pipes can be used to avoid data conversion and the serializing effect of a traditional "in-between" data set. He ran these jobs on a DB2 subsystem that he uses for testing purposes, and they worked as expected. The jobs were submitted at the same time. The UNLOAD process waited for the LOAD process to open the pipe for reading, whereupon it commenced writing unloaded records to the "back" of the pipe, while the LOAD process read records from the "front" of the pipe. This is all done in memory -- there is no physical I/O involved.
Here is Jorn's UNLOAD job:
//UNLD1 EXEC DSNUPROC,SYSTEM=DB2A,
// LIB='DB2.V9R1.SDSNLOAD',
// UID=''
//SYSPUNCH DD DSN=ABC1234.DB2A.CNTL.DSN8D91A.DSN8S91E.PTALL,
// DISP=(,CATLG,DELETE),
// DCB=(LRECL=80,BLKSIZE=0,RECFM=FB,DSORG=PS),
// SPACE=(TRK,(5,5),RLSE),
// UNIT=SYSDA
//SYSABC DD PATH='/tmp/unload.pipe1',DSNTYPE=PIPE,
// LRECL=107,BLKSIZE=27998,RECFM=VB,
// PATHOPTS=(OCREAT),
// PATHMODE=(SIWUSR,SIRUSR),
// PATHDISP=(DELETE,DELETE)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
TEMPLATE UD
PATH('/tmp/unload.pipe1')
PATHDISP(KEEP,KEEP)
RECFM(VB) LRECL(00000049)
FILEDATA(BINARY)
UNLOAD TABLESPACE DSN06697.TEST UNLDDN(UD)
FORMAT INTERNAL
/*
And here is the control statement for the complementary LOAD job:
//DSNUPROC.SYSIN DD *
TEMPLATE D3RYKD6Z
PATH('/tmp/unload.pipe1')
PATHDISP(KEEP,KEEP) PATHOPTS(ORDONLY)
RECFM(VB) LRECL(00000049)
FILEDATA(BINARY)
LOAD DATA INDDN D3RYKD6Z LOG NO REPLACE
FORMAT INTERNAL
INTO TABLE ABC1234.TESTKOPI
In addition to unloading data from one DB2 for z/OS table and loading the data into another table, Jorn successfully tested some variations on the technique:
- He unloaded data from a table to a USS pipe, and sent that pipe to another system via FTP.
- He sent a file to a USS pipe via FTP, and loaded data into a table from the pipe.
In exploring the use of USS pipes in your DB2 for z/OS environment, you might find the following sources of additional information to be useful:
- DB2 for z/OS APAR PK70269. The fix for this APAR introduced DB2 for z/OS TEMPLATE support for USS files (these are associated PTFs for DB2 for z/OS versions 8 and 9 -- the functionality is part of the DB2 10 base code). The text of this APAR is quite informative.
- The IBM "red book" titled, "DB2 9 for z/OS: Using the Utilities Suite." Section 3.2.8 of this document explains the use of TEMPLATE with USS pipes. Section 7.17 covers unloading and loading data using USS pipes.
- The DB2 for z/OS Utility Guide and Reference contains supporting information in the section on TEMPLATE. Refer to the DB2 9 or the DB2 10 manual, depending on the DB2 release you're running.
We often think of DB2 for z/OS utilities as workhorses, and they are, but they are workhorses that are constantly being enhanced with new functionality, one example of which I've written about here (with, again, a tip of the hat to Jorn Thyssen). Consider how the combination of FORMAT INTERNAL and USS pipes could enhance the performance of UNLOAD and LOAD operations at your site.
Robert, how big was the test table, and how did the UNLOAD/LOAD/pipes scenario compare with Crossloader, from a CPU and elapsed time standpoint?
ReplyDeleteAlso, can you comment on how WLM handles the pacing of the UNLOAD and LOAD so that the USS pipe flows optimally, rather that having periodic surges? How does it compare with MVS Batchpipes?
I don't have the particulars concerning the tests that my colleague Jorn ran. I suspect that the table in question was relatively small - Jorn was interested in getting the JCL and the utility control statements right.
DeleteJorn didn't test the USS pipe scenario versus the cross-loader, because he was interested in using FORMAT INTERNAL, and that's an option that is incompatible with the cross-loader.
I don't have information on optimizing the flow of data through a USS pipe, nor do I have comparative information as it pertains to BatchPipes. If you end up doing some comparative performance testing yourself, I certainly would be interested in knowing of the results.
Robert
Robert, thanks for your answers. Nothing in the pipeline, so to speak, for me testing the scenarios in the short term.
DeleteSomething to keep in mind is that if Crossloader can exploit an access path other than TS scan to extract 20-10% or less of the rows (based on the V8 Performance Topic Redbook) then it is probably a very strong contender. And if your filtering requires more sophisticated predicates than the ones supported by UNLOAD WHEN then Crossloader is probably the way to go.
Robert, above, when I referred to the V8 Performance Topics Redbook, I was referring to the 2 test cases where DSNTIAUL with MRF beat both UNLOAD and HPU. I was making the assumption that since both DSNTIAUL and Crossloader open a DB2 cursor and can get similar access paths, it might mean that the DSNTIAUL tests might be indicators of what might be seen with Crossloader.
DeleteNo doubt, the cross-loader functionality of LOAD can be an excellent performer, particularly when the SELECT that qualifies rows to be loaded from one table into another filters out a large percentage of the source table's rows. In testing UNLOAD/LOAD using USS pipes, my colleague was looking at the situation in which all of a source table's rows are loaded into a target table.
DeleteRobert
From a tech bulletin I wrote a while back for my site:
Delete2. UNLOAD utility vs. DSNTIAUL sample program:
The DSNTIAUL sample program was introduced with the first release of DB2. The UNLOAD utility was not introduced until DB2 V7. Many existing process that were developed prior to V7 were developed using DSNTIAUL. The following documents the advantages (and disadvantages) of the UNLOAD utility.
Advantages:
Faster and cheaper (CPU) than DSNTIAUL.
...table omitted
In the test the V8 DSNTIAUL was approximately 4x more efficient than the V7. However, the UNLOAD utility was approximately 9x more efficient than the V7 DSNTIAUL and 2x more efficient than the V8 version. Please note that “mileage might varying” due to I/O placement and configuration.
UNLOAD can operate against a single partition or a group of partitions.
UNLOAD can unload data from a non-CONCURRENT COPY image copy.
UNLOAD can unload in delimited format.
UNLOAD can run when table is in ACCESS(UT) mode.
UNLOAD can run SHRLEVEL CHANGE ISO CS, SHRLEVEL CHANGE ISO CS, or SHRLEVEL REFERENCE to meet the currency needs of the application.
UNLOAD can prefetch up to 64 4K pages, versus DSNTIAUL’s maximum of 32 4K pages.
For large unloads, UNLOAD has the ability to restart at the last internal commit point and not re-unload the whole table. DSNTIAUL does not have this facility.
UNLOAD is not a sample program. Sample programs, such as DSNTIAUL, can be modified or removed by IBM with little notification to customers.
Disadvantages:
The physical order of the output rows matches the physical order in the DB2 table. There is no guarantee that the output is in clustering sequence. To guarantee this an external sort will have to be performed. With DSNTIAUL a “ORDER BY ” clause can be used to ensure proper order.
UNLOAD output file, SYSREC, is format VB, while DSNTIAUL’s format is FB. If there is post-extract processing on the output (e.g. via a COBOL or SAS program) this has to be taken into account.
UNLOAD cannot process rows from a JOIN that is either explicitly stated via SQL, or implicitly via a VIEW. DSNTIAUL can by using the ‘SQL’ execution parameter.
UNLOAD does not support the auto-incrementing of SYSREC’s data definition name (SYSREC01, SYSREC02,…) unless one uses DB2 TEMPLATEs.
UNLOAD’s use of the WHEN parameter is not capable of utilizing an index to optimize the associated access in support of applying the necessary predicates/filtering. As such, DSNTIAUL can outperform DB2 Unload in cases involving extremely high filtering against large table (tablespaces).
Michael, thank you for posting this information. I have some observations.
Delete1. You wrote, "UNLOAD can operate against a single partition or a group of partitions." This is true, but DSNTIAUL via the right leading partitioning key predicates can use limited partition scan, with the same result, but without having the user having to figure out the mapping between logical partition number and physical partition number for the data they want.
2. You wrote, "UNLOAD is not a sample program. Sample programs, such as DSNTIAUL, can be modified or removed by IBM with little notification to customers." This is true, but DSNTIAUL is a sample program, so customers get the source code, which they are free to copy and modify at will, so what IBM does subsequently is of no threat. Though, of course, is nice when IBM does things like have DSNTIAUL support MRF, which customers were free to use or not.
3. You wrote, "UNLOAD can prefetch up to 64 4K pages, versus DSNTIAUL’s maximum of 32 4K pages." This is true in many circumstances, but the maximum seq prefetch size for SQL, including DSNIAUL can be more than 32 4K pages for very large Buffer Pools (I don't remember the size).
4. You wrote, "UNLOAD’s use of the WHEN parameter is not capable of utilizing an index . . ."
This is true, but in addition, UNLOAD WHEN only supports a restricted set of simple predicates which may not meet the user filtering requirements. Regardless of access path.
As mentioned in a previous post, The V8 Performance Topics showed DSNTIAUL MRF beating HPU and UNLOAD in 2 test cases and suggested that at about 10-20%, or less, of rows extracted that DSNTIAUL could win.
I agree with the points you raise, I dumbed down the document a bit as its audience was my site's programmers. As to point 4 I sort of said it in a reversed way "As such, DSNTIAUL can outperform DB2 Unload in cases involving extremely high filtering against large table (tablespaces)."
DeleteMichael Harper, TD Bank
Michael, I understand. It is often very difficult to write for that audience. It is a difficult struggle balance length and simplicity against considerations and exceptions.
Deleteforgot to sign.... Michael Harper, TD Bank
ReplyDeleteRobert, your example have LRECL=107 (the row length) on the SYSABC DD, while the templates have an LRECL of 49. I am puzzled by this. I have a table with a row length of 68 and if I harden the FORMAT INTERNAL to a MVS file the ouput has a LRECL of 86 (VB).
ReplyDeleteMichael Harper, TD Bank
I'll run this by Jorn, and I'll let you know of his response.
DeleteRobert
Lab measurements show
ReplyDelete– 85% CPU & elapsed time reduction on UNLOAD
– 77% elapsed time, 56% CPU reduction on LOAD
using FORMAT INTERNAL
(taken from Haakon Robert's presentations)
I only experimented with small tables in order to build some working examples I passed on to my customer, so I haven't tested if performance is as good as we claim.
Jørn Thyssen
IBM Denmark
Hi Michael,
ReplyDeleteGood catch regarding the LRECL.
What I've found so far is that it looks like the dataset will have the same LRECL as if you had unloaded in normal external format, that is,
LRECL = 6 bytes + size of fields in external format
For example, if your table had timestamp(6) fields it will add 26 to LRECL instead of the expected 10 bytes.
I'll have to contact the lab to find out why that is the case.
Best regards,
Jørn Thyssen
IBM Denmark
Hi Michael,
ReplyDeleteUpdate from the lab:
FORMAT INTERNAL does not take into account the shorter length of internal format for, e.g., timestamps.
The first 6 bytes of internal format is an 6 byte data prefix, whereas the first bytes of normal format is a 2 byte OBID. So INTERNAL FORMAT will have four bytes longer LRECL.
What column types did your LRECL 68 vs 86 example have?
Best regards,
Jørn Thyssen
IBM Denmark
DB2 internal row len is 60 bytes (68 = rec len) includes 1 timestamp; 2 dec(5,1) fields - the rest are char, int or smallint.
DeleteDCB of UNLOAD FORMAT INTERNAL sysrec file - as set by UNLOAD command not by JCL - is VB 86 .
When I browse sysrec and turn COLS on data only goes to column 66 - 6 byte header+60 byte row.
Question is "What LRECL do I use on the PIPE JCL, and the PIPE TEMPLATE?"
Example above has LRECL 107 on PIPE JCL, *BUT* LRECL(00000049) on TEMPLATE. This is confusing.
For yucks sake I used 107/49 in a test. Made me popular with sysprog - ABND=04E-00C200F7.
I understand the LRECL 107 on the PIPE JCL as it is the DB2 record length of DSN8S91E, so for my test I should use 68.
But why LRECL(00000049) in the template?
Michael Harper, TD Bank
Hi Michael,
DeleteI am sorry, the 107/49 looks like a typo. The LRECL should be the same throughout the JCL.
Best regards,
Jørn Thyssen
IBM Denmark
Hi Jorn and Robert,
ReplyDeleteIn the comments is stated that you 'only experimented with small tables in order to build some working examples'. I wondered:
- What you meant with 'small' tables (in terms of kb and records)?
- Whether you have done further analysis on the performance?
Best regards,
Ludovic Janssens
Infocura
Hi Ludovic,
DeleteI only experimented with very small tables (rowlength ~ 100 bytes, #rows < 1.000) and unfortunately I have not had any time to do further performance analysis for larger tables.
Best regards,
Jørn Thyssen
Works for IBM Denmark. Views are personal.
Here is a simple working JCL.
ReplyDeleteYou need to pre-allocate USS Named Pipe (UNIXFILETYPE=FIFO). I allocated '/u/prems/TMP/MYPIPE' in ISHELL. (I like ISHELL since it is in ISPF).
Submit the LOAD job first. Followed by the UNLOAD job.
UNLOAD JCL
----------
//PREMUNLD JOB (PLS,81038),CLASS=A,MSGCLASS=H,MSGLEVEL=(1,1),
// REGION=0M,TIME=1440,NOTIFY=&SYSUID
//**************************************************************
//UNLD1 EXEC DSNUPROC,SYSTEM=DSNB,
//* LIB='DB2.V9R1.SDSNLOAD',
// UID=''
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
TEMPLATE UD
PATH('/u/prems/TMP/MYPIPE')
FILEDATA RECORD
UNLOAD TABLESPACE STUD00D2.DSN8S10P
FROM TABLE STUD00.PROJACT
UNLDDN(UD)
FORMAT INTERNAL
/*
LOAD JCL
--------
//PREMLOAD JOB (PLS,81038),CLASS=A,MSGCLASS=H,MSGLEVEL=(1,1),
// REGION=0M,TIME=1440,NOTIFY=&SYSUID
//**************************************************************
//LOAD1 EXEC DSNUPROC,SYSTEM=DSNB,
// UID=''
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
TEMPLATE LD
PATH('/u/prems/TMP/MYPIPE')
FILEDATA RECORD
LOAD DATA INDDN LD LOG NO RESUME NO NOCOPYPEND REPLACE
FORMAT INTERNAL
INTO TABLE PREMS.PROJACT01
Content of /u/prems/TMP directoy in ISHELL
------------------------------------------
EUID=400135 /u/prems/TMP/
Type Filename
_ Dir .
_ Dir ..
_ FIFO MYPIPE
_ File UNLOAD.PIPE1
Note the filetype of MYPIPE is FIFO.
P S Prem
Works for IBM Singapore. Views are personal.
Thanks very much for sharing this example.
DeleteRobert
I am loading a table using cross-loader function between two system in DB2 Zos. Both the tables has GENERATED ALWAYS as IDENTITY defined. I would like to know what is that the value of a Generated Always column be in TArget table will it be same as Source table or different
ReplyDeleteSorry about the delay in responding.
DeleteIf the column in the target table is defined with GENERATED ALWAYS then new values will be generated when rows are inserted into the table, regardless of the column's value in the source table. If the target table's column is defined with GENERATED BY DEFAULT, the supplied value (the one from the source table) will be accepted and used.
Hope this helps.
Robert
Hello,I used to dsnutilb to unload a table/tablespace.I get below error-
ReplyDeleteDSNU1218I -DB2X 276 02:58:15.66 DSNUULIA - LOGICAL RECORD LENGTH OF OUTPUT RECORD EXCEEDED THE LIMIT FOR TABLE .I tried using nopad option as well as per info msg suggestion but dint work either.can you help
Does the table in question have any LOB (CLOB or BLOB) or XML columns?
DeleteRobert
yes rob,it has lob.
DeleteOK, in that case you'll probably need to specify SPANNED YES in the utility control statement (for information about the SPANNED keyword, see https://www.ibm.com/docs/en/db2-for-zos/12?topic=unload-syntax-options-control-statement).
DeleteRobert
I gave spanned yes ,but still no luck.Im surprised that using dsntiaul worked .Not sure why inspite of specifying spanned keyword dsnutilb is not working for unload with lob's
DeleteDNUTILB should not be your focus. That's just the Db2 for z/OS program through which utilities are executed. Presumably, you executed an UNLOAD utility. What does the control statement for that unload utility look like?
Delete//SYSIN DD *
DeleteTEMPLATE UNLDDS DSN xxxxx
UNIT CTAPE BUFNO 60 RETPD 07 VOLCNT(99)
DISP (NEW,CATLG,DELETE) STACK YES TRTCH COMP
TEMPLATE PUNCHDS DSN xxxxxx
UNIT SYSDA DISP (NEW,CATLG,DELETE) SPACE (1,1) CYL
LISTDEF UNLDLIST
INCLUDE TABLESPACE xxxx
UNLOAD LIST UNLDLIST
PUNCHDDN PUNCHDS
UNLDDN UNLDDS
SHRLEVEL CHANGE ISOLATION CS
spanned yes
Please refer to the documentation concerning the SPANNED option for the UNLOAD utility, on this Db2 for z/OS documentation page: https://www.ibm.com/docs/en/db2-for-zos/12?topic=unload-syntax-options-control-statement. On that page, you will see that SPANNED YES is ignored if you use LIST in your UNLOAD control statement. You will also see that SPANNED YES is ignored if your UNLOAD control statement does not "include a field specification list that specifies all LOB and XML data at the end of the record."
DeleteRobert
Right.It worked.Thanks.
Delete