Today I'm going to discuss about a vendor product - CA(previously Platinum)'s DB2 "Fast Unload". It is a high performance DB2 data unload utility. If you use Fast Unload at your shop, go ahead and read these tips:
Fast Unload is much faster than DB2's DSNTIAUL. One of the reasons is, it does not use DB2 SQL to fetch the data. Instead it fetches the data directly from the underlying VSAM dataset. But this does not happen always. Sometimes it may have to fetch the data from the table. This is controlled by the keyword "SQL-ACCESS".
SQL-ACCESS NONE:
When you specify "NONE" for SQL-ACCESS, it does not use SQL access. If the select statement
cannot be processed using VSAM or EXCP, an error message is issued & processing ends. This is the DEFAULT.
Note: You must specify NONE to unload data from Image Copies, Concurrent Copies & DSN1COPYs.
SQL-ACCESS ONLY:
When you specify this option, it uses only SQL access to unload the tablespaces. This option lets you use any valid SELECT statement. Only one SELECT statement is processed at a time. The statements using VSAM or EXCP are processed first, followed by the SQL ACCESS SELECT statements, which are processed in the order specified in the control statements. You cannot include an ORDER-CLUSTERED clause, or the PART or OBID parameters with this option. You must specify SQL-ACCESS ONLY to use TRIM with ASCII objects. With SQL-ACCESS ONLY, DB2 determines locking. To preserve data integrity, you must specify LOCKSIZE TABLE on the tablespace.
SQL-ACCESS EXTENSION:
When you specify "EXTENSION" for SQL-ACCESS, it uses EXCP or VSAM processing if possible; otherwise uses SQL access. If you specify SQL-ACCESS EXTENSION, only those SELECT statements that require SQL access use it; all other SELECT statements are multitasked & unloaded using VSAM or EXCP. If you specify SQL-ACCESS EXTENSION with the PART or OBID parameter & SQL-ACCESS is required, an error message is issued and processing terminates.
15 comments:
What version of FASTUNLOAD are you using? I tried the AUTO option and it is kicked out as an invalid option.
First of all, thanks for your comments and I sincerely apologize for the misinformation. There is no such option as SQL-ACCESS AUTO - I must have mistyped it.
Our current Fast Unload version is R11.5 SP1.
I'll fix the post with the correct information right away.
Once again, thanks for stopping by - Aji
I was searching for information on Platinum database technology and found your posting. Would you happen to know anything about exporting Platunum databases into Excel? We recently had to export some tables from Platinum into Microsoft Access for analysis, but now that the tables are in an Access database, we cannot make sense of the data - have you ever run across this or have any tips to understand the exported database tables now that they are in an Access database?
Hi,
When you say "exporting Platunum databases into Excel", do you mean exporting data extracted from DB2 tables to Excel? If so, please try OUTPUT-FORMAT COMMA-DELIMITED.
- Aji
Pardon me for ignorance about the Platinum system. I am encountering a situation with a legacy Platinum Accounting system (now Epicor). I am not sure if we are talking about the same Platinum system here. If so, could you please look into my predicament? I was able to extract all the tables in the Database into MS Access. Unfortunately, the names of the table and the field names within the tables are extremely cryptic and the data is essentially useless. Is there any standard mapping data which will help me identify the tables and fields I am looking for? I need to extract trial balance information by year. Please let me know if you could provide some guidance.
Swaminathan - I'm really sorry to say that I don't have any experience on Platinum Accounting system (Epicor). Platinum Fast Load that I've talked about in this post is a DB2 database utility.
If you have maintenance license with CA, you can open a ticket with and usually they are responsive.
-Aji
Hi Aji..
Nice blog. Do you have any info related to parameters used in FASTUNLOAD ?
I am using the below ones, issue here is char data types are downloaded with single quote at the begining and end. Is there any way to change ?
SHRLEVEL CHANGE
LOAD-CONTROL NONE
SQL-ACCESS ONLY
INPUT-FORMAT TABLE
OUTPUT-FORMAT COMMA-DELIMITED
COMMA ','
NULL-FIELD QUOTES
Vivek - Thanks for your comments. When you use OUTPUT-FORMAT COMMA-DELIMITED, the default is to display single quote (') for character fields. To remove quote, specify QUOTE NONE.
Also remember that there are other OUTPUT-FORMATs available that you can explore to see if it is useful for you.
Thanks.
Hi Aji,
Do you by any chance know how we can unload all the tables in a database to flat files.I mean can we generate a jcl for that using platinum , i know this can be done thorugh IBM ADBT tool. But my shop doesn't have that???
Nishant - Thanks for your comments. To unload data from all the tables in a database, use RC/Migrator to create a "Migration Strategy". Select the database you are interested in and put an "A" under the Tablespaces and Tables to "Explode" tables. Once the strategy is created, you can "Analyze" the strategy in either foreground mode or batch mode. When you do that, put a "Y" for UPDATE OPTIONS, it'll take you to another screen called "RC/M Strategy Analysis Options" where you have many options to choose. One of them is "Data Unload Options" under which you'll see "ALL ROWS" put a "Y" on this option and then analyze. After analysis is complete, you can create this migration strategy to create batch job.
Please let me know if any of these doesn't make sense to you.
HTH
Hi,
I am using FASTUNLOAD with OUTPUT-FORMAT COMMA-DELIMITED , but there is one coloumn in the unloaded data having comma in it.
So while converting it into excel by using delimiter as comma this coloumn is getting divided into two.
Is there any way with which i can remove the commas from this coloumn while unloading?
Aji, I am looking for a complete syntax to use IBN DB2 LISTDEF, TEMPLATE parameters to do UNLOAD and LOAD if possible. There are used in the OPTION CONTROL Statement as I read in the User Guide but I am unable find any example JCL that JCL and code that I can try to model and understand.
Is this something that you can be of assistance to me?
I will be very much grateful.
Thank you
Okanita,
I'll be glad to help you. I don't know your DB2 version. So I'll give you a link to V8 manual. Similar links can be found at IBM Information Center.
Here is the link: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.ugref%2Funldxmp.htm
Take a look at Example 8: "Unloading multiple table spaces by using LISTDEF" from the manual "DB2 UDB for z/OS Version 8 > DB2 reference information > DB2 utilities > DB2 online utilities > UNLOAD"
Here is the sample job from the above manual:
//SAMPJOB JOB ...
//STEP1 EXEC DSNUPROC,UID='SMPLUNLD',UTPROC='',SYSTEM='DSN'
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
LISTDEF UNLDLIST
INCLUDE TABLESPACE TDB1.TSP*
TEMPLATE UNLDDS DSN &USERID..SMPLUNLD.&TS.
UNIT SYSDA DISP (NEW,CATLG,CATLG) SPACE (2,1) CYL
TEMPLATE PUNCHDS DSN &USERID..SMPLPUNC.&TS.
UNIT SYSDA DISP (NEW,CATLG,CATLG) SPACE (1,1) CYL
UNLOAD LIST UNLDLIST
PUNCHDDN PUNCHDS -- TEMPLATE name
UNLDDN UNLDDS -- TEMPLATE name
Please let mw know if you need more information.
HTH,
Aji
hi,
i have multiple select statements in unload query, when i use CA utility to unload this file it creates VB format file but I want FB format,please suggest how we can enforce this
Sorry for the late reply. Did you mention DCB parameters for your SYSREC dataset? Something like this: DCB=(LRECL=nn,BLKSIZE=0,RECFM=FB). If not, please try this.
Post a Comment