BMC Unload Plus is a high-performance DB2 UNLOAD utility (just like CA/Platinum FAST UNLOAD utility). The performance of this utility can be controlled by proper use of the keyword "DIRECT".
DIRECT YES: UNLOAD PLUS reads data directly from the table space data set or image copy data sets to unload the data, using a SELECT-like syntaxfor data selection. SELECT functionality is a limited subset of thefunctionality that is normally available in DB2 SQL. The benefit of the DIRECT YES mode is maximum performance when unloading large volumes of data.
DIRECT NO: UNLOAD PLUS processes the SELECT statement and reads the data usingDB2 dynamic SQL. This mode is not a high-performance solution for unloading large volumes of data. The benefit of the DIRECT NO mode is a full range of DB2 SQL SELECTfunctionality, including joined tables, subqueries, and so on. This functionality includes many of the features that are availablein UNLOAD PLUS including DB2 parallelism, data type conversions, output formatting.
28 comments:
Hi Aji,
While unloading data from DB2 z/OS using BMC Unload plus in FORMAT CSV, how do I make the utility specify a double character delimiter?
E.g. A source character field contains:
I am 6" feet tall
I want the bmc unload utility to unload it as:
"I am 6"" feet tall"
I am using the ENCLOSED BY '"' AND '"' options in the SYSIN DD card.
And I am loading to a UDB table on AIX.
Now, the BMC utility is unloading it as:
"I am 6" feet tall", so when I try to load this data in UDB, it loads only: I am 6.
Yeah, I have tried with different other character delimiters, but the source table has character columns containing every keyboard character as part of data itself, so some row or the other is getting truncated or rejected.
The only way is to make the BMC utility unload the data with double character delimiters.
E.g. if I specify ENLCOSEDBY '|' AND '|', and my source row contains the string:
abcd | efgh, then the unload utility should unload as:
|abcd || efgh| .
I have searched the BMC Unload Plus manual but couldnt find any such option, but I am thinking that since this seems to be a popular utility in the DB2 z/OS world, this problem must be quite common and there has to be a workaround.
Thanks,
Abhijit
Abhijit - Thanks for your comments. I don't know how to accomplish this - I'm sure it can be done, but I've not done it myself. I'll let you know if I find something. If you want you can send me your personal email id, so that I can update you.
Thanks for stopping by.
- Aji
Hi Aji,
wat if is am unloading the data & formatting it. Wat do i use Direct no/yes?
eg.
UNLOAD SHRLEVEL CHANGE
SELECT CUSTOMER_ID INTO CUSTOMER_ID INT EXTERNAL(10)
FROM x_table
HERE I AM FETCHING & THEN FORMATTING CUSTOMER-ID, WAT DO I USE?
Thanks for your comments.
Unloading the data and then formatting is not something related to the parameter DIRECT. When you specify DIRECT YES, Unload Plus will try to get data directly from the tablespace's underlying VSAM data set or image copy dataset (if you want to unload from image copy). For simple query such as the one in your example, DIRECT YES is the right parameter to use. For complex queries, that needs DB2's help, Unload Plus uses DIRECT NO. So go ahead and use DIRECT YES.
Hope I answered your question.
- Aji
Is there a way to put column headings on the unloaded fields
Hi,
This is in answer to the question "Is there a way to put column headings on the unloaded fields".
There is no direct way to do it. However, there are couple of ways you can achieve this.
1. Do a UNLOAD with the headings in one job step, like SELECT "heading 1" || "heading 2" and then in the next job step, do the actual data unload and use the same output dataset as in the previous step, but use DISP=MOD
2. Use BMC UNLOAD's multi-step feature, which will allow you to code both the above said SELECT statements in one step. But the outputs will go to different datasets. So you still need a second job step to concatenate these datasets.
HTH
Hi,
when the SQL contains only those columns which are part of Index, then DB2 doesn't need to access table space and retrieve all required data using Index space itself. This increases the performance. Do we have similar way in BMC UNLOAD PLUS as well? The columns that I unload are part of the same index.
Hi,
Thanks for your comments. As far as I understand, the way UNLOAD PLUS and other 3rd party tools (CA's FAST UNLOAD etc.) work is, they fetch the data directly from the underlying linear VSAM datasets for most of the straight forward data retrievals. However, there are some exceptions to this, such as complex queries. Complex queries still go through DB2 and they follow all DB2 optimizer choices. You might want to read BMC manuals to confirm under which circumstances the data retrieval cannot be done straight from the dataset.
HTH,
Aji
Do we have similar way in BMC UNLOAD PLUS as well? The columns that I unload are part of the same index.
I am using fast unload on a join of 2 tables. I'm selecting all but one of the columns on table-A, and just one column on table-B. Both tables have nullable data, including the one column I'm selecting from table-B. all of the null values are being returned correctly from table-A, but the data being returned from table-B does not include the null indicator. As a result, the file layout for this data is 2 bytes off. Is there a parameter that I'm missing? Thx
I'm using Fast Unload to unload data using a join of 2 tables. I'm selecting all but 1 column of tbl-1, and only 1 column from tbl-2. Both tables have nullable data. the resulting file correctly contains all of the null values from tbl-1, but is missing the null value from tbl-2. Do I need to add an additional parameter in order to pick up the missing null indicator?
Thx
dlm,
I'm not aware of any parameter that will restrict null indicator on one table while allowing the other. What kind of join method are you using? Can you post the Fast Unload control card here?
Thanks for your comments,
- Aji
(this isn't a complete list of the columns returned). PVNDR_NBR is the column in question. I think it has something to do with the PVNDR_NBR being on both tables. I suspect if I selected additional columns from MVNDR table, they would not return the null indicator either. I've also tried SQL-ACCESS EXTEND and got the same results. Thx.
FASTUNLOAD
LOAD-CONTROL NONE
OUTPUT-FORMAT FIXED
INPUT-FORMAT TABLE
DISPLAY-STATUS 10000
IO-BUFFERS 50
VSAM-BUFFERS 180
SHRLEVEL CHANGE
SQL-ACCESS ONLY
SELECT A.MVNDR_NBR
A.MER_DEPT_NBR
,A.FT2_FRT_TRM_CD
FT2_FRT_TRM_CD_NL
,A.FT2_EFF_MTH_NBR
FT2_EFF_MTH_NBR_NL
,B.PVNDR_NBR
PVNDR_NBR_NL <--- not returned
FROM MVNDR_MKT_DC A
,MVNDR B
WHERE A.MVNDR_NBR = B.MVNDR_NBR
AND A.MER_DEPT_NBR = B.MER_DEPT_NBR
(this isn't a complete list of the columns returned). PVNDR_NBR is the column in question. I think it has something to do with the PVNDR_NBR being on both tables. I suspect if I selected additional columns from MVNDR table, they would not return the null indicator either. I've also tried SQL-ACCESS EXTEND and got the same results. Thx.
FASTUNLOAD
LOAD-CONTROL NONE
OUTPUT-FORMAT FIXED
INPUT-FORMAT TABLE
DISPLAY-STATUS 10000
IO-BUFFERS 50
VSAM-BUFFERS 180
SHRLEVEL CHANGE
SQL-ACCESS ONLY
SELECT A.MVNDR_NBR
A.MER_DEPT_NBR
,A.FT2_FRT_TRM_CD
FT2_FRT_TRM_CD_NL
,A.FT2_EFF_MTH_NBR
FT2_EFF_MTH_NBR_NL
,B.PVNDR_NBR
PVNDR_NBR_NL <--- not returned
FROM MVNDR_MKT_DC A
,MVNDR B
WHERE A.MVNDR_NBR = B.MVNDR_NBR
AND A.MER_DEPT_NBR = B.MER_DEPT_NBR
dlm,
I'm not sure what could be wrong here, but did you try to use A.PVNDR_NBR_NL or B.PVNDR_NBR_NL?
- Aji
yes, but i got an error if i tried to qualify any of the null columns with 'A.' or 'B.'
Thx
Please let me know what to specify in the control card for it to read the inputdataset .
The option INFILE IMAGECOPY FULL 0 takes the details from SYSCOPY, which i do not want.
and i do not have access to tables.
please let me know of the syntax, Direct yes also doesnt help here
Sucharita,
Thanks for your comments.
You mentioned that you do not have access to the table. Do you mean, you don't even have SELECT access? If that is the case, you cannot read it through BMC UNLOAD PLUS as well. But it does check the authority for the table through DB2 catalog tables, before it unloads the data, you cannot circumvent that.
If this doesn't answer your question, please let me know.
- Aji
yeah.. we just have access to Imagecopy dataset and not the select access to tables.... therefore asking you if it can be done, without authorization to tables.
yes, i do not have select access on the table, but i was just given access to its imagecopy GDG.I was asked to download from that.
i tried the below :
unload tablespace name
infile imagecopy
direct yes
select * owner.tablename.
again another card was :
unload
infile filename
direct yes
select * owner.tablename.
everything gives an error select privilege not found, even if i remove the select statement, then also the same error. Given IMAGECOPY FULL 0 also gives the same error.please let me know if this can be really done or not?.
Sucharita,
"everything gives an error select privilege not found, even if i remove the select statement, then also the same error. Given IMAGECOPY FULL 0 also gives the same error.please let me know if this can be really done or not?."
Unfortunately, the way this tool works is, when you specify DIRECT YES, either you should have SELECT access to the table or your primary or secondary id have SYSADM or DBADM authority. If you do not have any of these authorities, you are out of luck.
Most probably that's the case with CA tool too.
When you specify DIRECT NO, it obviously goes through DB2 security, so you must have SELECT authority.
I would say, specify DIRECT YES and submit the job through operators who usually have admin authority.
HTH !!
- Aji
Hi Aji, I am trying to use BMC Unload with a GDG output, specifying GDGLIMIT (5). I specified DSNAME, but the utility did not create the GDG. How do I specify GDG please?
Thank you.
Huay,
I don't quite understand what you mean by "specifying GDGLIMIT (5)". Are you trying to create the GDG base with 5 generations while specifying this dataset for SYSREC? If so, that's not possible. You'll have to use an IDCAMS utility to create the GDG base first. Then you can specifiy it in the unload job.
HTH,
Aji
To put a header on you can use an initail step:
UNLOAD
DIRECT NO
CNTLCARDS BMCLOAD
CNTLDDN SYSCTL
FORMAT CSV ENCLOSEDBY '"' AND '"'
* SELECT CARD SHOULD FOLLOW *
SELECT 'Mnemonic'
,'Name'
,'Description'
FROM SYSIBM.SYSDUMMY1
//*
//BMCUNLD.SYSREC01 DD DSN=MY.REPORT.GDG(+1),
// DISP=(NEW,CATLG,DELETE),DATACLAS=TST010P5,
// DCB=MB.MODEL
Then in your unload of the actual data mod onto your MY.REPORT.GDG(+1) file.
You may run into some truncation of the data. If this is the case put one additional step at the end of the job that creates a blank row. Copy your first header stp and then just space out all of the header values and mod onto the end.
Great tip about adding header to UNLOAD PLUS data. Thank You !!
IHi, there is this requirement to unload data from a table, delimit it by ~ and connect direct it to a unix system. I am giving below while unloading from the table:
FORMAT CSV
DELIMITEDBY '~'
The problem is that, though the unload file is getting delimited correctly with ~ in mainframes, this ~ is not getting displayed correctly in the unix side (it is getting displayed as junk value). Please let me know what i could add along with the above lines, while unloading data from the table, so that the delimiter ~ gets recognised correctly in the unix system.
FORMAT CSV
DELIMITEDBY '~'
If my guess is correct, when you specify the above parameters, you see the desired output, however when you send the data to UNIX, you do not see the delimited value '~'.
In that case, the most probable cause is page set (CCSID) difference between Operating Systems.
Please check with your System Administrator about the CCSID that is specified for BMC Unload Plus. It should say something like "MIXED CCSID - Yes".
We do not have this product anymore in our shop, so I cannot test it.
Sorry for the inconvenience.
If changing the CCSID doesn't fix the problem, please let me know.
Thanks,
Aji
Hi,
Can we write discarded records to a dataset like discddn while unloading records from a db2 table using bmc unload utility? Can you please provide me the syntax for the same?
//Can we write discarded records to a dataset like discddn while unloading records from a db2 table using bmc unload utility? Can you please provide me the syntax for the same?//
Unload utility will not discard records. Only when you load data into a table, discard comes into picture. With BMC LOAD PLUS, you can redirect discarded records to SYSDISC dataset. The JCL statement looks like this:
//SYSDISC DD DSN=datasetname,other parms
Also, in the LOAD card (SYSIN), you should specify this:
SYSDISC YES
Hope This Helps.
Post a Comment